Nutri Capture 백엔드 - Room 가상 테이블 선언

#1 개요

#1-1 채팅 UI 구현을 위한 첫 걸음

'채팅 UI'에서 표시할 데이터를 하나의 레코드로서 가지는 가상 테이블을 선언한다.

 

#1-2 가상 테이블 (SQLite)

SELECT day_table.day_id AS day_id,
       meal_table.meal_id AS meal_id,
       day_table.day_date AS day_date, 
       meal_table.meal_time AS meal_time,
       meal_table.meal_name AS meal_name,
       /* NutritionInfo의 Column 시작 */
       meal_table.overeating_excess AS overeating_excess,
       meal_table.refined_sugar_excess AS refined_sugar_excess,
       meal_table.refined_grain_excess AS refined_grain_excess,
       meal_table.flour_excess AS flour_excess,
       meal_table.fiber_quality AS fiber_quality,
       meal_table.protein_quality AS protein_quality,
       meal_table.sodium_excess AS sodium_excess
       /* NutritionInfo의 Column 끝 */
FROM day_table
INNER JOIN meal_table ON meal_table.day_id = day_table.day_id
ORDER BY day_table.day_date DESC,
         meal_table.meal_time DESC,
         meal_table.meal_id DESC

채팅 UI는 가장 최근의 식단부터 내림차순으로 보여준다. 따라서, Query문에 있는 3개의 정렬 기준을 전부 내림차순(DESC)으로 두었다.

 

#2 코드 - 가상 테이블

#2-1 가상 테이블 (Room)

// package com.example.nutri_capture_new.db

import androidx.room.ColumnInfo
import androidx.room.DatabaseView
import androidx.room.Embedded
import java.time.LocalDate
import java.time.LocalTime

/*
Day 테이블의 dayId와 Day의 자식인 Meal 테이블의 dayId가 같은(==) 조건으로 INNER JOIN하되,
첫째로는 Day.date에 대해 내림차순,
둘째로는 Meal.time에 대해 내림차순,
셋째로는 Meal.mealId에 대해 내림차순으로 정렬
*/
@DatabaseView("""
    SELECT day_table.day_id AS day_id,
           meal_table.meal_id AS meal_id,
           day_table.day_date AS day_date, 
           meal_table.meal_time AS meal_time,
           meal_table.meal_name AS meal_name,
           /* NutritionInfo의 Column 시작 */
           meal_table.overeating_excess AS overeating_excess,
           meal_table.refined_sugar_excess AS refined_sugar_excess,
           meal_table.refined_grain_excess AS refined_grain_excess,
           meal_table.flour_excess AS flour_excess,
           meal_table.fiber_quality AS fiber_quality,
           meal_table.protein_quality AS protein_quality,
           meal_table.sodium_excess AS sodium_excess
           /* NutritionInfo의 Column 끝 */
    FROM day_table
    INNER JOIN meal_table ON meal_table.day_id = day_table.day_id
    ORDER BY day_table.day_date DESC,
             meal_table.meal_time DESC,
             meal_table.meal_id DESC
""")
data class DayMealView(
    @ColumnInfo(name = "day_id")
    val dayId: Long,

    @ColumnInfo(name = "meal_id")
    val mealId: Long,

    @ColumnInfo(name = "day_date")
    var date: LocalDate,

    @ColumnInfo(name = "meal_time")
    var time: LocalTime,

    @ColumnInfo(name = "meal_name")
    var name: String,

    @Embedded
    val nutritionInfo: NutritionInfo
)

"db" 패키지에 두었다.

 

#2-2 MainDatabase.kt

// package com.example.nutri_capture_new.db

...

@Database(
    entities = ...,
    views = [DayMealView::class],
    version = ...
)
...
abstract class MainDatabase : RoomDatabase() {

    ...
}

방금 선언한 가상 테이블을 Room이 인식할 수 있게 views 프로퍼티에 추가해준다.

 

#3 코드 - 가상 테이블의 레코드 확인

#3-1 DAO (MainDAO.kt)

...

@Dao
interface MainDAO {
    ...

    @Query("SELECT * FROM DayMealView")
    suspend fun getAllMeals(): List<DayMealView>
}

 

#3-2 Repository (MainRepository.kt)

...

class MainRepository(private val dao: MainDAO) {
    ...

    suspend fun getAllMeals(): List<DayMealView> {
        return dao.getAllMeals()
    }
}

 

#3-3 ViewModel (NutrientViewModel.kt)

// package com.example.nutri_capture_new.nutrient

...
import android.util.Log

class NutrientViewModel(private val repository: MainRepository) : ViewModel() {
    // (1) 화면 표시용 State
    ...

    // (2) ViewModel용 내부 변수
    ...

    // (3) View에서 받아 처리할 이벤트
    ...

    // (4) View로부터 받은 이벤트 처리
    ...

    // (5) DayMealView 작동 확인용 로그
    fun log() {
        viewModelScope.launch {
            Log.i("interfacer_han", repository.getAllMeals().toString())
        }
    }
}

Repository에 접근 가능한 ViewModel에 Log를 출력하는 함수를 만든다. 이 함수를 ViewModel에 접근 가능한 View에서 호출(#3-4 참조)할 것이다.

 

#3-4 View (NutrientScreen.kt)

...

@Composable
fun NutrientScreen(
    ...
) {
    LaunchedEffect(key1 = true) {
        // DayMealView 작동 확인용 로그
        viewModel.log()

        ...
    }

    ...
}

 

#4 완성된 앱

#4-1 스크린샷

로그 메시지는 앱 시작과 동시에 출력되므로, 위와 같은 상태를 만들고 앱을 재시작하면 11일인 Meal 3개ㆍ12일인 Meal 2개ㆍ13일인 Meal 1개ㆍ14일인 Meal 4개가 출력될 것이다 (#4-2 참조).

 

#4-2 로그 메시지

[
DayMealView(
dayId=4, mealId=10, date=2024-11-14, time=07:48:11.077044, name=자동 생성된 Meal, nutritionInfo=NutritionInfo( overeatingExcess=0, refinedSugarExcess=0, refinedGrainExcess=0, flourExcess=0, fiberQuality=0, proteinQuality=0, sodiumExcess=0 )
),

DayMealView(
dayId=4, mealId=9, date=2024-11-14, time=07:48:10.831407, name=자동 생성된 Meal, nutritionInfo=NutritionInfo( overeatingExcess=0, refinedSugarExcess=0, refinedGrainExcess=0, flourExcess=0, fiberQuality=0, proteinQuality=0, sodiumExcess=0 )
),

DayMealView(
dayId=4, mealId=8, date=2024-11-14, time=07:48:10.634030, name=자동 생성된 Meal, nutritionInfo=NutritionInfo( overeatingExcess=0, refinedSugarExcess=0, refinedGrainExcess=0, flourExcess=0, fiberQuality=0, proteinQuality=0, sodiumExcess=0 )
),

DayMealView(
dayId=4, mealId=7, date=2024-11-14, time=07:48:10.453062, name=자동 생성된 Meal, nutritionInfo=NutritionInfo( overeatingExcess=0, refinedSugarExcess=0, refinedGrainExcess=0, flourExcess=0, fiberQuality=0, proteinQuality=0, sodiumExcess=0 )
),

DayMealView(
dayId=3, mealId=6, date=2024-11-13, time=07:48:09.646245, name=자동 생성된 Meal, nutritionInfo=NutritionInfo( overeatingExcess=0, refinedSugarExcess=0, refinedGrainExcess=0, flourExcess=0, fiberQuality=0, proteinQuality=0, sodiumExcess=0 )
),

DayMealView(
dayId=2, mealId=5, date=2024-11-12, time=07:48:08.852377, name=자동 생성된 Meal, nutritionInfo=NutritionInfo( overeatingExcess=0, refinedSugarExcess=0, refinedGrainExcess=0, flourExcess=0, fiberQuality=0, proteinQuality=0, sodiumExcess=0 ) ),

DayMealView(
dayId=2, mealId=4, date=2024-11-12, time=07:48:08.591206, name=자동 생성된 Meal, nutritionInfo=NutritionInfo( overeatingExcess=0, refinedSugarExcess=0, refinedGrainExcess=0, flourExcess=0, fiberQuality=0, proteinQuality=0, sodiumExcess=0 )
),

DayMealView(
dayId=1, mealId=3, date=2024-11-11, time=07:48:07.773742, name=자동 생성된 Meal, nutritionInfo=NutritionInfo( overeatingExcess=0, refinedSugarExcess=0, refinedGrainExcess=0, flourExcess=0, fiberQuality=0, proteinQuality=0, sodiumExcess=0 )
),

DayMealView(
dayId=1, mealId=2, date=2024-11-11, time=07:48:07.481481, name=자동 생성된 Meal, nutritionInfo=NutritionInfo( overeatingExcess=0, refinedSugarExcess=0, refinedGrainExcess=0, flourExcess=0, fiberQuality=0, proteinQuality=0, sodiumExcess=0 )
),

DayMealView(
dayId=1, mealId=1, date=2024-11-11, time=07:48:07.159289, name=자동 생성된 Meal, nutritionInfo=NutritionInfo( overeatingExcess=0, refinedSugarExcess=0, refinedGrainExcess=0, flourExcess=0, fiberQuality=0, proteinQuality=0, sodiumExcess=0 )
)
]

내림차순으로 정렬된 레코드가 잘 출력된다.

 

#4-3 이 게시글 시점의 Commit

GitHub - Kanmanemone/nutri-capture-new

 

#4-4 본 프로젝트의 가장 최신 Commit

GitHub - Kanmanemone/nutri-capture-new