MealTimeDB - SQLite

[aaleksandraristic]

MealTimeDB Setup

Description:

The creationg of the SQLite database - MealTimeDB. The class DatabaseHelper is used to manage database creation and version management. The reason for creating a database and tables inside the DatabaseHelper class is to encapsulate the logic related to database setup, version management, and table creation within a single class.

Steps to Reproduce:

1. plugin all the dependencies
2. Create a DatabseHelper
3. Create tables
4. Create values.
5. Insert values

Expected vs. Actual Behavior:

Expected Behavior: All tables and fields are created in the database and ready to be tested out. Actual Behavior: No problems occur.

Code Snippets:

` 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

// healthConcerns contains allergies, abnormalities, health problems... private val createTableUser = """ CREATE TABLE User ( userID INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, password TEXT NOT NULL, email TEXT NOT NULL, age INTEGER, gender TEXT, height REAL, weight REAL, alergies TEXT ); """

// Ingredients Table - store all the nutrition facts?
private val createTableIngredients = """
CREATE TABLE Ingredients (
    ingredientID INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    calories REAL NOT NULL,
    protein REAL NOT NULL,
    carbs REAL NOT NULL,
    fats REAL NOT NULL,
    category_id INTEGER,
    FOREIGN KEY (category_id) REFERENCES FoodCategories(categoryId)
);

"""

// NOT FOR NOW !!!

// GroceryList Table private val createTableGroceryList = """ CREATE TABLE GroceryList ( groceryListId INTEGER PRIMARY KEY AUTOINCREMENT, userId INTEGER, ingredientId INTEGER, quantity INTEGER, FOREIGN KEY (userId) REFERENCES User(userId), FOREIGN KEY (ingredientId) REFERENCES Ingredients(ingredientId) ); """

// MealRecipe Table
private val createTableMealRecipe = """
CREATE TABLE MealRecipe (
    mealId INTEGER PRIMARY KEY AUTOINCREMENT,
    userId INTEGER,  
    name TEXT,
    description TEXT,
    calories REAL,
    protein REAL,
    carbs REAL,
    fats REAL,
    quantity TEXT,
    instructions TEXT,
    FOREIGN KEY (userId) REFERENCES User(userId)
);

"""

// MealIngredient Table
private val createTableMealIngredient = """

CREATE TABLE MealIngredient ( mealId INTEGER, ingredientId INTEGER, PRIMARY KEY (mealId, ingredientId), FOREIGN KEY (mealId) REFERENCES MealRecipe(mealId), FOREIGN KEY (ingredientId) REFERENCES Ingredients(ingredientId) ); """

// Returning to function

// DailyIntake Table private val createTableDailyIntake = """ CREATE TABLE DailyIntake ( dailyIntakeId INTEGER PRIMARY KEY AUTOINCREMENT, userId INTEGER NOT NULL, mealId INTEGER NOT NULL, totalCalories REAL, totalProtein REAL, totalCarbs REAL, totalFats REAL, dayTime DATE NOT NULL, FOREIGN KEY (userId) REFERENCES User(userId), FOREIGN KEY (mealId) REFERENCES Meal(mealId) ); """

// LogInHistory Table
private val createTableLogInHistory = """
CREATE TABLE LogInHistory (
    logInId INTEGER PRIMARY KEY AUTOINCREMENT,
    userId INTEGER NOT NULL,
    logInTime DATE NOT NULL,
    FOREIGN KEY (userId) REFERENCES User(userId)
);

"""

// FoodCategories Table
private val createTableFoodCategories = """
CREATE TABLE FoodCategories (
    categoryId INTEGER PRIMARY KEY,
    categoryName TEXT
);

"""

// UserPreferences Table - do we want to keep it?
private val createTableUserPreferences = """
CREATE TABLE UserPreferences (
    preferenceId INTEGER PRIMARY KEY AUTOINCREMENT,
    userId INTEGER,
    prefferedDiet TEXT,
    preferredCalories REAL,
    preferredProtein REAL,
    preferredCarbs REAL,
    preferredFats REAL,
    FOREIGN KEY (userId) REFERENCES User(userId)
);

"""

//Spoonacular Table
private val createTableSpoonacularAccount = """
    CREATE TABLE SpoonacularAccount (
        username TEXT PRIMARY KEY,
        password TEXT NOT NULL,
        hash TEXT NOT NULL,
        userID INTEGER,
        FOREIGN KEY (userID) REFERENCES User(userID)
    );

"""

override fun onCreate(db: SQLiteDatabase) {
    db.execSQL(createTableUser)
    db.execSQL(createTableIngredients)
    db.execSQL(createTableGroceryList)
    db.execSQL(createTableDailyIntake)
    db.execSQL(createTableLogInHistory)
    db.execSQL(createTableFoodCategories)
    db.execSQL(createTableUserPreferences)
    db.execSQL(createTableMealRecipe)
    db.execSQL(createTableMealIngredient)
    db.execSQL(createTableSpoonacularAccount)
}

override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
    db.execSQL("DROP TABLE IF EXISTS User;")
    db.execSQL("DROP TABLE IF EXISTS Ingredients;")
    db.execSQL("DROP TABLE IF EXISTS GroceryList;")
    db.execSQL("DROP TABLE IF EXISTS DailyIntake;")
    db.execSQL("DROP TABLE IF EXISTS LogInHistory;")
    db.execSQL("DROP TABLE IF EXISTS FoodCategories;")
    db.execSQL("DROP TABLE IF EXISTS UserPreferences;")
    db.execSQL("DROP TABLE IF EXISTS MealRecipe;")
    db.execSQL("DROP TABLE IF EXISTS MealIngredient")
    db.execSQL("DROP TABLE IF EXISTS SpoonacularAccount")
    onCreate(db)
}

// Method to insert sample user data
fun insertSampleUser(
    username: String,
    password: String,
    email: String,
    age: Int,
    gender: String,
    height: Double,
    weight: Double,
    allergies: String
) {
    val db = this.writableDatabase
    val values = ContentValues().apply {
        put("username", username)
        put("password", password)
        put("email", email)
        put("age", age)
        put("gender", gender)
        put("height", height)
        put("weight", weight)
        put("allergies", allergies)
    }
    db.insert("User", null, values)
    db.close()
}

}

'

Environment Details:

Operating System: Android Firebase SDK Version: 23.0.0 Kotlin Version: 1.5.21 Android Studio Version: 4.2.1 Gradle Version: 7.0.2 SQLite3 3.45.1.

Investigation:

Proposed Changes: No significant issues were found during the investigation.

Outcome:

Current Status: Closed

[aaleksandraristic]

@BeepDroid Hey Kerry, here is the creation of the database and tables. What do you think, does it look okay? I am planning to start working on a logic and procedures that are part of the database.

[BeepDroid]

MealTimeDB Setup

---

Description:

---

Adding some code for putting in values to a table and sending queries to the SQL Lite database.

Steps to Reproduce:

---

1. Create a DatabseHelper object in the activity class you need to insert values from.
2. Create values.
3. Use insert method to insert values and query method to pull from the database if need to,

Expected vs. Actual Behavior:

---

Expected Behavior: Uploading and managing data for the database and tables.

Code Snippets:

val dbHelper = MealTimeDB(this)

`val db = dbHelper.writableDatabase

// Example: Insert data into a table val values = ContentValues() values.put("name", "John Doe") db.insert("your_table_name", null, values)

// Example: Query data from a table val cursor = db.rawQuery("SELECT * FROM your_table_name", null) while (cursor.moveToNext()) { val name = cursor.getString(cursor.getColumnIndex("name")) // Process the data as needed }

// Close the database connections when done cursor.close() db.close() `

Environment Details:

---

Operating System: Android Firebase SDK Version: 23.0.0 Kotlin Version: 1.5.21 Android Studio Version: 4.2.1 Gradle Version: 7.0.2 SQLite3 3.45.1.

Current Status:

In-Progress

@Cade5480 For your backend development as well

[aaleksandraristic]

New code that I added:

' // Method to insert sample user data fun insertSampleUser( username: String, password: String, email: String, age: Int, gender: String, height: Double, weight: Double, allergies: String ) { val db = this.writableDatabase val values = ContentValues().apply { put("username", username) put("password", password) put("email", email) put("age", age) put("gender", gender) put("height", height) put("weight", weight) put("allergies", allergies) } db.insert("User", null, values) db.close() } '

Class MainActivity: ....(rest of the code) override fun onCreate(savedInstanceState: Bundle?) { super.onCreate(savedInstanceState) setContentView(R.layout.activity_login)

    val dbHelper = DatabaseHelper(this)

    val context = ApplicationProvider.getApplicationContext<DatabaseHelper>()
    val DatabaseHelper: DatabaseHelper = DatabaseHelper(context)
    val db = DatabaseHelper.writableDatabase

    // Insert sample user data
    dbHelper.insertSampleUser("aalex", "asd", "alex@gmail.com", 24, "Female", 5.4, 140.0, "None")

....(rest of the code)