I am developing a mobile application in Android Studio. I have a "BreakfastDatabaseHelper" class for an SQLite database. Initially, I created the "breakfast" and "calories_summary" tables and methods for them there.
package com.example.calorieapp.ui.dashboard;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Locale;
public class BreakfastDatabaseHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "breakfast_database";
private static final int DATABASE_VERSION = 2;
static final String TABLE_BREAKFAST = "breakfast";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_PRODUCT_NAME = "product_name";
public static final String COLUMN_GRAMS = "grams";
public static final String COLUMN_CALORIES = "calories";
public static final String COLUMN_PROTEIN = "protein";
public static final String COLUMN_FAT = "fat";
public static final String COLUMN_CARBOHYDRATE = "carbohydrate";
public static final String COLUMN_DATE = "date";
// SQL query to create the breakfast table
private static final String CREATE_BREAKFAST_TABLE = "CREATE TABLE " + TABLE_BREAKFAST + " (" +
COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
COLUMN_PRODUCT_NAME + " TEXT, " +
COLUMN_GRAMS + " REAL, " +
COLUMN_CALORIES + " REAL, " +
COLUMN_PROTEIN + " REAL, " +
COLUMN_FAT + " REAL, " +
COLUMN_CARBOHYDRATE + " REAL, " +
COLUMN_DATE + " TEXT);";
static final String TABLE_CALORIES_SUMMARY = "calories_summary";
public static final String COLUMN_DATE_SUMMARY = "date_summary";
public static final String COLUMN_TOTAL_CALORIES = "total_calories";
// SQL query to create the calories_summary table
// SQL query to create the calories_summary table
private static final String CREATE_CALORIES_SUMMARY_TABLE = "CREATE TABLE " + TABLE_CALORIES_SUMMARY + " (" +
COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
COLUMN_DATE_SUMMARY + " TEXT, " +
COLUMN_TOTAL_CALORIES + " REAL);";
static final String TABLE_PROTEIN_SUMMARY = "protein_summary";
public static final String COLUMN_DATE_SUMMARY_PROTEIN = "date_summary_protein";
public static final String COLUMN_TOTAL_PROTEIN = "total_protein";
// SQL query to create the calories_summary table
// SQL query to create the calories_summary table
private static final String CREATE_PROTEIN_SUMMARY_TABLE = "CREATE TABLE " + TABLE_PROTEIN_SUMMARY + " (" +
COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
COLUMN_DATE_SUMMARY_PROTEIN + " TEXT, " +
COLUMN_TOTAL_PROTEIN + " REAL);";
public BreakfastDatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_BREAKFAST_TABLE);
db.execSQL(CREATE_CALORIES_SUMMARY_TABLE);
db.execSQL(CREATE_PROTEIN_SUMMARY_TABLE);
Log.d("BreakfastDatabaseHelper", "Tables created: breakfast, calories_summary");
}
public void updateCaloriesSummary(String date) {
// Выполняем запрос для получения суммы калорий по выбранной дате с округлением до сотых
String query = "SELECT ROUND(SUM(" + COLUMN_CALORIES + "), 2) FROM " + TABLE_BREAKFAST +
" WHERE " + COLUMN_DATE + " = ?";
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(query, new String[]{date});
double totalCalories = 0;
// Если есть результат, переходим к первой записи
if (cursor.moveToFirst()) {
totalCalories = cursor.getDouble(0);
}
cursor.close();
// Теперь вставляем или обновляем данные в таблице calories_summary
ContentValues values = new ContentValues();
values.put(COLUMN_DATE_SUMMARY, date);
values.put(COLUMN_TOTAL_CALORIES, totalCalories);
db = this.getWritableDatabase();
db.replace(TABLE_CALORIES_SUMMARY, null, values);
db.close();
}
public double getTotalCaloriesSummary(String date) {
// Выполняем запрос для получения суммы калорий из таблицы calories_summary по выбранной дате
String query = "SELECT " + COLUMN_TOTAL_CALORIES + " FROM " + TABLE_CALORIES_SUMMARY +
" WHERE " + COLUMN_DATE_SUMMARY + " = ?" +
" ORDER BY " + COLUMN_ID + " DESC"; // Упорядочиваем по убыванию id
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(query, new String[]{date});
double totalCalories = 0;
// Если есть результат, переходим к первой записи
if (cursor.moveToFirst()) {
totalCalories = cursor.getDouble(0);
}
cursor.close();
db.close();
return totalCalories;
}
public void updateProteinSummary(String date) {
// Выполняем запрос для получения суммы калорий по выбранной дате с округлением до сотых
String query = "SELECT ROUND(SUM(" + COLUMN_PROTEIN + "), 2) FROM " + TABLE_BREAKFAST +
" WHERE " + COLUMN_DATE + " = ?";
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(query, new String[]{date});
double totalProtein = 0;
// Если есть результат, переходим к первой записи
if (cursor.moveToFirst()) {
totalProtein = cursor.getDouble(0);
}
cursor.close();
// Теперь вставляем или обновляем данные в таблице protein_summary
ContentValues values = new ContentValues();
values.put(COLUMN_DATE_SUMMARY_PROTEIN, date);
values.put(COLUMN_TOTAL_PROTEIN, totalProtein);
db = this.getWritableDatabase();
db.replace(TABLE_PROTEIN_SUMMARY, null, values);
db.close();
}
public double getTotalProteinSummary(String date) {
// Выполняем запрос для получения суммы калорий из таблицы protein_summary по выбранной дате
String query = "SELECT " + COLUMN_TOTAL_PROTEIN + " FROM " + TABLE_PROTEIN_SUMMARY +
" WHERE " + COLUMN_DATE_SUMMARY_PROTEIN + " = ?" +
" ORDER BY " + COLUMN_ID + " DESC"; // Упорядочиваем по убыванию id
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(query, new String[]{date});
double totalProtein = 0;
// Если есть результат, переходим к первой записи
if (cursor.moveToFirst()) {
totalProtein = cursor.getDouble(0);
}
cursor.close();
db.close();
return totalProtein;
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.d("BreakfastDatabaseHelper", "Upgrading database from version " + oldVersion + " to " + newVersion);
// Handle database upgrades if needed
}
}
Now I want to create a table "protein_summary". I do it by analogy "calories_summary", add the line db.execSQL(CREATE_PROTEIN_SUMMARY_TABLE) to the onCreate method, but the table is not created. What could this be related to?
The onCreate method is only called when the database does not exist. The typical way to amend the schema is to utilise the onUpgrade method. The onUpgrade method is called/invoked when the version number is increased.
onCreate method appears to always be called, but as a database persists then it is only created the once for the App (unless deleted). Underlying is a file, if the file exists then the database exists.So you need to increase the version number DATABASE_VERSION (typically by increasing it by 1) and then have the onUpgrade method create the table noting that you should cater for the respective upgrade by testing the oldVersion and newVersion.
You should also update the onCreate to also create the amended schema so that a new installation creates the correct schema for the version.
If you use CREATE TABLE IF NOT EXISTS .... rather than CREATE TABLE .... then you can have the onUpgrade call the onCreate and so only the missing table(s) get created.
As a Demonstration based upon the code in the question.
Consider the following changes
private static final int DATABASE_VERSION = 1;
and:-
private static final String CREATE_BREAKFAST_TABLE = "CREATE TABLE IF NOT EXISTS " + ....
private static final String CREATE_CALORIES_SUMMARY_TABLE = "CREATE TABLE IF NOT EXISTS " + ....
private static final String CREATE_PROTEIN_SUMMARY_TABLE = "CREATE TABLE IF NOT EXISTS " + ....
And JUST FOR EASE of the DEMO:-
if (DATABASE_VERSION > 1) db.execSQL(CREATE_PROTEIN_SUMMARY_TABLE);
db.execSQL(CREATE_PROTEIN_SUMMARY_TABLE);And lastly:-
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.d("BreakfastDatabaseHelper", "Upgrading database from version " + oldVersion + " to " + newVersion);
// Handle database upgrades if needed
if (oldVersion < 2) {
onCreate(db);
}
}
Now with the following Activity code:-
public class MainActivity extends AppCompatActivity {
BreakfastDatabaseHelper dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper = new BreakfastDatabaseHelper(this);
SQLiteDatabase db = dbHelper.getWritableDatabase();
Log.d("DBVERSION","Database version is " + db.getVersion());
Cursor csr = db.rawQuery("SELECT * FROM sqlite_master",null);
DatabaseUtils.dumpCursor(csr);
csr.close();
}
}
The the log shows:-
2024-03-23 12:41:58.636 D/BreakfastDatabaseHelper: Tables created: breakfast, calories_summary
2024-03-23 12:41:58.638 D/DBVERSION: Database version is 1
2024-03-23 12:41:58.638 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8962cfb
2024-03-23 12:41:58.639 I/System.out: 0 {
2024-03-23 12:41:58.640 I/System.out: type=table
2024-03-23 12:41:58.640 I/System.out: name=android_metadata
2024-03-23 12:41:58.640 I/System.out: tbl_name=android_metadata
2024-03-23 12:41:58.640 I/System.out: rootpage=3
2024-03-23 12:41:58.640 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
2024-03-23 12:41:58.640 I/System.out: }
2024-03-23 12:41:58.640 I/System.out: 1 {
2024-03-23 12:41:58.640 I/System.out: type=table
2024-03-23 12:41:58.640 I/System.out: name=breakfast
2024-03-23 12:41:58.640 I/System.out: tbl_name=breakfast
2024-03-23 12:41:58.640 I/System.out: rootpage=4
2024-03-23 12:41:58.640 I/System.out: sql=CREATE TABLE breakfast (_id INTEGER PRIMARY KEY AUTOINCREMENT, product_name TEXT, grams REAL, calories REAL, protein REAL, fat REAL, carbohydrate REAL, date TEXT)
2024-03-23 12:41:58.640 I/System.out: }
2024-03-23 12:41:58.640 I/System.out: 2 {
2024-03-23 12:41:58.641 I/System.out: type=table
2024-03-23 12:41:58.641 I/System.out: name=sqlite_sequence
2024-03-23 12:41:58.641 I/System.out: tbl_name=sqlite_sequence
2024-03-23 12:41:58.641 I/System.out: rootpage=5
2024-03-23 12:41:58.641 I/System.out: sql=CREATE TABLE sqlite_sequence(name,seq)
2024-03-23 12:41:58.641 I/System.out: }
2024-03-23 12:41:58.641 I/System.out: 3 {
2024-03-23 12:41:58.641 I/System.out: type=table
2024-03-23 12:41:58.641 I/System.out: name=calories_summary
2024-03-23 12:41:58.641 I/System.out: tbl_name=calories_summary
2024-03-23 12:41:58.641 I/System.out: rootpage=6
2024-03-23 12:41:58.641 I/System.out: sql=CREATE TABLE calories_summary (_id INTEGER PRIMARY KEY AUTOINCREMENT, date_summary TEXT, total_calories REAL)
2024-03-23 12:41:58.641 I/System.out: }
2024-03-23 12:41:58.641 I/System.out: <<<<<
i.e. just the original 2 tables
With just the single change:-
private static final int DATABASE_VERSION = 2;
And the App rerun, then the log:-
2024-03-23 12:55:42.359 D/BreakfastDatabaseHelper: Upgrading database from version 1 to 2
2024-03-23 12:55:42.360 D/BreakfastDatabaseHelper: Tables created: breakfast, calories_summary
2024-03-23 12:55:42.362 D/DBVERSION: Database version is 2
2024-03-23 12:55:42.362 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8962cfb
2024-03-23 12:55:42.363 I/System.out: 0 {
2024-03-23 12:55:42.363 I/System.out: type=table
2024-03-23 12:55:42.364 I/System.out: name=android_metadata
2024-03-23 12:55:42.364 I/System.out: tbl_name=android_metadata
2024-03-23 12:55:42.364 I/System.out: rootpage=3
2024-03-23 12:55:42.364 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
2024-03-23 12:55:42.364 I/System.out: }
2024-03-23 12:55:42.364 I/System.out: 1 {
2024-03-23 12:55:42.364 I/System.out: type=table
2024-03-23 12:55:42.365 I/System.out: name=breakfast
2024-03-23 12:55:42.365 I/System.out: tbl_name=breakfast
2024-03-23 12:55:42.365 I/System.out: rootpage=4
2024-03-23 12:55:42.365 I/System.out: sql=CREATE TABLE breakfast (_id INTEGER PRIMARY KEY AUTOINCREMENT, product_name TEXT, grams REAL, calories REAL, protein REAL, fat REAL, carbohydrate REAL, date TEXT)
2024-03-23 12:55:42.365 I/System.out: }
2024-03-23 12:55:42.365 I/System.out: 2 {
2024-03-23 12:55:42.365 I/System.out: type=table
2024-03-23 12:55:42.365 I/System.out: name=sqlite_sequence
2024-03-23 12:55:42.365 I/System.out: tbl_name=sqlite_sequence
2024-03-23 12:55:42.365 I/System.out: rootpage=5
2024-03-23 12:55:42.366 I/System.out: sql=CREATE TABLE sqlite_sequence(name,seq)
2024-03-23 12:55:42.366 I/System.out: }
2024-03-23 12:55:42.366 I/System.out: 3 {
2024-03-23 12:55:42.366 I/System.out: type=table
2024-03-23 12:55:42.366 I/System.out: name=calories_summary
2024-03-23 12:55:42.366 I/System.out: tbl_name=calories_summary
2024-03-23 12:55:42.366 I/System.out: rootpage=6
2024-03-23 12:55:42.366 I/System.out: sql=CREATE TABLE calories_summary (_id INTEGER PRIMARY KEY AUTOINCREMENT, date_summary TEXT, total_calories REAL)
2024-03-23 12:55:42.366 I/System.out: }
2024-03-23 12:55:42.366 I/System.out: 4 {
2024-03-23 12:55:42.366 I/System.out: type=table
2024-03-23 12:55:42.366 I/System.out: name=protein_summary
2024-03-23 12:55:42.366 I/System.out: tbl_name=protein_summary
2024-03-23 12:55:42.366 I/System.out: rootpage=7
2024-03-23 12:55:42.367 I/System.out: sql=CREATE TABLE protein_summary (_id INTEGER PRIMARY KEY AUTOINCREMENT, date_summary_protein TEXT, total_protein REAL)
2024-03-23 12:55:42.367 I/System.out: }
2024-03-23 12:55:42.367 I/System.out: <<<<<
i.e. the 3 tables now exist.
CREATE TABLE IF NOT EXISTS .... for the 2 original tables would be noops as the tables exist.Perhaps note the very first line, output according to your original code from the onUpgrade method.
Also note that accidentally (on purpose) the logged output from onCreate was not changed to reflect the new table. Thus the benefit of actually ascertaining what is via the query on sqlite_master (the database's schema).