Database Implementation

[IanTDuncan]

API - Database Implementation

---

Description:

---

Implement databases within the code and then use them to reduce API calls and move variables around.

Current Database:

Steps to Reproduce:

---

1. Pull From Database Branch
2. Run the app
3. Under App Inspection find Database Manager
4. Use database manager to keep track of data in database

Expected vs. Actual Behavior:

---

1. Expected Behavior: Database Implemented Correctly and Code works seamlessly
2. Actual Behavior: Database Implemented Correctly and Code works seamlessly

Code Snippets:

---

MainMenuActivity.kt Code Addition:

val dbHelper = DatabaseHelper(this)
        // Check if the device has an associated ID in the database
        val deviceId = DeviceIdManager.getDeviceId(this)
        if (deviceId == null) {
            // If not, generate a new ID and store it in SharedPreferences
            val newDeviceId = DeviceIdManager.generateDeviceId()
            DeviceIdManager.saveDeviceId(this, newDeviceId)
        }

DeviceIDManager.kt:

package com.example.mealtime1.DatabaseFiles

import android.content.Context
import java.util.UUID

object DeviceIdManager {

    private const val PREF_NAME = "MyAppPrefs"
    private const val DEVICE_ID_KEY = "deviceId"

    fun getDeviceId(context: Context): String {
        // Try to retrieve the device ID from SharedPreferences
        val sharedPreferences = context.getSharedPreferences(PREF_NAME, Context.MODE_PRIVATE)
        var deviceId = sharedPreferences.getString(DEVICE_ID_KEY, null)

        // If device ID doesn't exist, generate a new one and save it in SharedPreferences
        if (deviceId == null) {
            deviceId = generateDeviceId()
            sharedPreferences.edit().putString(DEVICE_ID_KEY, deviceId).apply()
        }

        return deviceId!!
    }

    fun generateDeviceId(): String {
        // Generate a unique GUID (UUID) as the device ID
        return UUID.randomUUID().toString()
    }

    fun saveDeviceId(context: Context, deviceId: String) {
        val sharedPreferences = context.getSharedPreferences(PREF_NAME, Context.MODE_PRIVATE)
        sharedPreferences.edit().putString(DEVICE_ID_KEY, deviceId).apply()
    }
}

Updated DatabaseHelper Class:

package com.example.mealtime1.DatabaseFiles

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.icu.math.BigDecimal
import com.example.mealtime1.IngredientCost
import com.example.mealtime1.Meal

class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    private val context: Context = context

    companion object {
        const val DATABASE_NAME = "MealTimeDB"
        const val DATABASE_VERSION = 1
    }

    // Device Table
    private val createTableDevice = """
    CREATE TABLE Device (
        deviceID TEXT PRIMARY KEY
    );
    """

    // Results Table
    private val createTableResults = """
    CREATE TABLE Results (
        mealID INTEGER PRIMARY KEY AUTOINCREMENT,
        mealName TEXT,
        mealPic BLOB,
        mealNutritionLabel BLOB
    );
    """

    // ShoppingList Table
    private val createTableIngredient  = """
    CREATE TABLE Ingredient (
        mealID INTEGER PRIMARY KEY,
        IngredientName TEXT,
        PricePerUnit REAL,
        FOREIGN KEY (mealID) REFERENCES Results (mealID)
    );
    """

    fun getMealIds(): IntArray {
        val mealIds = mutableListOf<Int>()
        val db = this.readableDatabase
        val query = "SELECT mealID FROM Results"
        val cursor: Cursor = db.rawQuery(query, null)
        if (cursor.moveToFirst()) {
            do {
                val mealId = cursor.getInt(cursor.getColumnIndex("mealID"))
                mealIds.add(mealId)
            } while (cursor.moveToNext())
        }
        cursor.close()
        return mealIds.toIntArray()
    }

    fun hasResults(): Boolean {
        val db = this.readableDatabase
        val query = "SELECT * FROM Results LIMIT 1"
        val cursor = db.rawQuery(query, null)
        val hasResults = cursor.count > 0
        cursor.close()
        return hasResults
    }

    fun getSavedMeals(): List<Meal> {
        val meals = mutableListOf<Meal>()
        val db = this.readableDatabase
        val query = "SELECT * FROM Results"
        val cursor: Cursor = db.rawQuery(query, null)
        if (cursor.moveToFirst()) {
            do {
                val mealName = cursor.getString(cursor.getColumnIndex("mealName"))
                val mealPic = cursor.getString(cursor.getColumnIndex("mealPic"))
                val mealID = cursor.getInt(cursor.getColumnIndex("mealID"))
                meals.add(Meal(mealName, mealPic, mealID))
            } while (cursor.moveToNext())
        }
        cursor.close()
        return meals
    }

    fun insertMeal(meal: Meal) {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put("mealName", meal.name)
            put("mealPic", meal.imageUrl)
            put("mealID", meal.id)
        }
        db.insert("Results", null, values)
    }

    fun insertNutritionLabel(mealId: Int, nutritionLabel: ByteArray) {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put("mealNutritionLabel", nutritionLabel)
        }
        db.update(
            "Results",
            values,
            "mealID=?",
            arrayOf(mealId.toString())
        )
    }

    fun insertIngredient(mealId: Int, ingredientName: String, pricePerUnit: android.icu.math.BigDecimal) {
        val db = this.writableDatabase
        val values = ContentValues().apply {
            put("mealID", mealId)
            put("IngredientName", ingredientName)
            put("PricePerUnit", pricePerUnit.toDouble())
        }
        db.insert("Ingredient", null, values)
    }

    fun getIngredientsForMeals(mealIds: IntArray): List<IngredientCost> {
        val ingredients = mutableListOf<IngredientCost>()
        val db = this.readableDatabase
        mealIds.forEach { mealId ->
            val query = "SELECT * FROM Ingredient WHERE mealID = ?"
            val cursor: Cursor = db.rawQuery(query, arrayOf(mealId.toString()))
            if (cursor.moveToFirst()) {
                do {
                    val ingredientName = cursor.getString(cursor.getColumnIndex("IngredientName"))
                    val pricePerUnit = cursor.getDouble(cursor.getColumnIndex("PricePerUnit"))
                    ingredients.add(IngredientCost(ingredientName, BigDecimal(pricePerUnit)))
                } while (cursor.moveToNext())
            }
            cursor.close()
        }
        return ingredients
    }

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(createTableDevice)
        db.execSQL(createTableResults)
        db.execSQL(createTableIngredient)

        // Store the device ID when the database is first created
        val deviceId = DeviceIdManager.getDeviceId(context)
        val values = ContentValues().apply {
            put("deviceID", deviceId)
        }
        db.insert("Device", null, values)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS User;")
        db.execSQL("DROP TABLE IF EXISTS Device;")
        db.execSQL("DROP TABLE IF EXISTS MealPlan;")
        db.execSQL("DROP TABLE IF EXISTS Results;")
        db.execSQL("DROP TABLE IF EXISTS ShoppingList;")
        db.execSQL("DROP TABLE IF EXISTS Ingredient;")
        onCreate(db)
    }
}

Environment Details:

---

Operating System: Windows 11 Kotlin Version: 1.5.21 Android Studio Iguana 2023.2.1 Android Gradle Plugin Version: 8.3.0 Gradle Version: 8.4

For Issue Resolution:

---

Implement databases

Current Status:

---

IN-PROGRESS

---

[aaleksandraristic]

Here's the code for the tables mentioned above:

• Tables User and MealPlan already exist but since we changed a lot of things here's the code for everything...

'

package com.example.mealtime1

import android.content.ContentValues import android.content.Context import android.database.sqlite.SQLiteDatabase import android.database.sqlite.SQLiteOpenHelper

class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

companion object {
    const val DATABASE_NAME = "MealTimeDB"
    const val DATABASE_VERSION = 1
}

// User Table
private val createTableUser = """
CREATE TABLE User (
    userID INTEGER PRIMARY KEY AUTOINCREMENT
);

"""

// MealPlan Table
private val createMealPlan  = """
CREATE TABLE MealPlan  (
    mealPlanID INTEGER PRIMARY KEY AUTOINCREMENT,
    userID INTEGER,
    mealID INTEGER, 
    FOREIGN KEY (userID) REFERENCES User(UserID),
    FOREIGN KEY (mealID) REFERENCES Results(mealID)
);

"""

// Results Table
private val createTableResults = """
CREATE TABLE Results (
    mealID INTEGER PRIMARY KEY AUTOINCREMENT,
    mealName TEXT,
    mealPic TEXT, 
    mealNutritionLabel TEXT
);

"""

// ShoppingList Table
private val createTableShoppingList  = """

CREATE TABLE ShoppingList ( listID INTEGER PRIMARY KEY AUTOINCREMENT, mealID INTEGER, mealPlanID INTEGER, IngrediantName TEXT, Quantity INTEGER, Unit TEXT, PricePerUnit INTEGER, FOREIGN KEY (mealID) REFERENCES Results (mealID), FOREIGN KEY (mealPlanID) REFERENCES MealPlan(mealPlanID) ); """

override fun onCreate(db: SQLiteDatabase) {
    db.execSQL(createTableUser)
    db.execSQL(createMealPlan)
    db.execSQL(createTableResults)
    db.execSQL(createTableShoppingList)
}

override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
    db.execSQL("DROP TABLE IF EXISTS User;")
    db.execSQL("DROP TABLE IF EXISTS MealPlan;")
    db.execSQL("DROP TABLE IF EXISTS Results;")
    db.execSQL("DROP TABLE IF EXISTS ShoppingList;")
    onCreate(db)
}

}

'

[aaleksandraristic]

@IanTDuncan SQLite doesn't contain arrays or lists, so I guess what we can do is just adding meals to the same mealPlanID?

[IanTDuncan]

@aaleksandraristic Thank you very much!

[aaleksandraristic]

@IanTDuncan Please let me know how it works and if something needs to be changed or added

[IanTDuncan]

@aaleksandraristic Alright I'll make sure to keep you updated.