I have set up a SQLite db in an Android app but to focus on the immeidate problem will only reference the Users table. In DatabaseContract the Users table is defined as:
public final class DatabaseContract {
private DatabaseContract() {}
public static class Users implements BaseColumns {
public static final String TABLE_NAME = "users";
public static final String COLUMN_USER_ID = "_id";
public static final String COLUMN_USER_NAME = "user_name";
public static final String COLUMN_PASSWORD = "password";
public static final String COLUMN_EMAIL = "email";
public static final String COLUMN_DISPLAY_NAME = "display_name";
}
...rest of tables in db
}
In DatabaseHelper class this is Users table:
private static final String SQL_CREATE_USERS_TABLE =
"CREATE TABLE " + DatabaseContract.Users.TABLE_NAME + " (" +
DatabaseContract.Users.COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
DatabaseContract.Users.COLUMN_USER_NAME + " TEXT NOT NULL, " +
DatabaseContract.Users.COLUMN_PASSWORD + " TEXT NOT NULL, " +
DatabaseContract.Users.COLUMN_EMAIL + " TEXT, " +
DatabaseContract.Users.COLUMN_DISPLAY_NAME + " TEXT)";
In my AddUserActivity class run this method addUserToDatabase(String name, String email, String password, String displayName) which seems to work however I wish to get the auto-generated 'id' from the Users table and then retrieve the 'id' here:
// Check if the insertion was successful
if (newRowId != -1) {
// User was added successfully
Toast.makeText(this, "User added successfully!", Toast.LENGTH_SHORT).show();
// Get the user_id of the newly added user
int user_id = getUserId(db, name);
...
Here is the method getUserId(db, name):
private int getUserId(SQLiteDatabase db, String name) {
int user_id = -1; // Default value if no user is found
// Define the columns you want to retrieve
String[] projection = {
DatabaseContract.Users.COLUMN_USER_ID
};
// Define the selection based on the user name
String selection = DatabaseContract.Users.COLUMN_USER_NAME + " = ?";
String[] selectionArgs = { name };
// Define the sort order (if needed)
String sortOrder = null;
Cursor cursor = db.query(
DatabaseContract.Users.TABLE_NAME,
projection,
selection,
selectionArgs,
null,
null,
sortOrder
);
if (cursor.moveToFirst()) {
// Get the user_id from the cursor
int usersIdIndex = cursor.getColumnIndexOrThrow(DatabaseContract.Users.COLUMN_USER_ID);
user_id = cursor.getInt(usersIdIndex);
}
// Close the cursor (don't close the database here)
cursor.close();
return user_id;
}
Then I create an intent to pass this data to the WelcomeActivity page:
...
// Create an Intent to navigate to WelcomeActivity
Intent intent = new Intent(AddUserActivity.this, WelcomeActivity.class);
int testNumber = 777;
// Pass user data and user_id as extras in the Intent
intent.putExtra("USER_ID", user_id);//testNumber
intent.putExtra("USER_NAME", name);
intent.putExtra("USER_EMAIL", email);
intent.putExtra("USER_DISPLAY_NAME", displayName);
// Start the WelcomeActivity
startActivity(intent);
// Close this activity
finish();
At this point the program crashes with this error:
E/SQLiteLog: (1) no such column: _id in "SELECT _id FROM users WHERE user_name = ?"
D/AndroidRuntime: Shutting down VM
E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.enetapplications.songtracker, PID: 5937
android.database.sqlite.SQLiteException: no such column: _id (code 1 SQLITE_ERROR[1]): , while compiling: SELECT _id FROM users WHERE user_name = ?
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1478)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:916)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:63)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:2088)
at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1935)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1806)
at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1974)
at com.enetapplications.songtracker.AddUserActivity.getUserId(AddUserActivity.java:124)
at com.enetapplications.songtracker.AddUserActivity.addUserToDatabase(AddUserActivity.java:84)
at com.enetapplications.songtracker.AddUserActivity.access$400(AddUserActivity.java:20)
at com.enetapplications.songtracker.AddUserActivity$1.onClick(AddUserActivity.java:58)
at android.view.View.performClick(View.java:7792)
at android.widget.TextView.performClick(TextView.java:16112)
at android.view.View.performClickInternal(View.java:7769)
at android.view.View.access$3800(View.java:910)
at android.view.View$PerformClick.run(View.java:30218)
at android.os.Handler.handleCallback(Handler.java:938)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loopOnce(Looper.java:226)
at android.os.Looper.loop(Looper.java:313)
at android.app.ActivityThread.main(ActivityThread.java:8663)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:567)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1135)
I am new to SQLite thus getting accommodated to SQLite - but very comfortable with standard SQL/MySQL. My tendency would be to name the key to the Users table as user_id however it seems that the SQLite standard is to keep the key to the table as _id
Thanks in advance for reviewing and commenting!
best, Brian
This was a very odd problem
No it is not, it is how it should be. Unlike Activities etc where onCreate is frequently called a Database is a persistent entity, it would be useless otherwise (at least for it's intended purpose). The data stored within needs to kept irrespective of the state of the device or App (unless uninstalled when the user has chosen to not want the data any longer). Furthermore the data may well have to be kept when an App is updated (which may or may not involve a change to the database's schema).
Simply put the onCreate method reflects the fact that it is called only when the database is created.
If changes are then needed then that is when onUpdate comes into play (or the seldom used onDownGrade). These both being driven by the database version, as is passed when construction an class that extends SQLiteOpenHelper.
I kept getting errors and couldn't undo the origenal db configuration.
As per the comment. The SQLiteOpenHelper's (and therefore a class that extends SQLiteOpenHelper) onCreate method is called just once for the lifetime of the database. As such any changes to the code, typically where the tables are initially created, will not then be reflected as the code does not run.
If developing the App before it has been published and the data held by the database can be lost then all that is needed is for the database to be deleted. This can be achieved by uninstalling the App. As there is then no database the onCreate will then be invoked.
If the App has been published and or the data needs to be retained then the use of the databases version and the onUpdate method can be used to migrate the database from a lower version.
The solution was to change the db name - just this addition '2': private static final String DATABASE_NAME = "song_tracker2.db";
This is because you have now specified another database as the database to be used and hence the onCreate method has been invoked and the schema is as expected. However, the original database will still exist and waste some storage.
Demo Migration
The following is a working demonstration of how you could utilise the onUpgrade method to introduce the change (even though just uninstalling the App would have sufficed as you were happy to utilise another database).
What the demo does do is show how data can be retained when utilising the onUpgrade method. It shows a basic way of applying virtually any change to a table. In this case changing the primary key's column name from users_id to _id.
So consider the following for the DatabaseHelper class (obviously you will have your own):-
class DatabaseHelper extends SQLiteOpenHelper {
private static final String SQL_CREATE_USERS_TABLE =
"CREATE TABLE " + DatabaseContract.Users.TABLE_NAME + " (" +
DatabaseContract.Users.COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
DatabaseContract.Users.COLUMN_USER_NAME + " TEXT NOT NULL, " +
DatabaseContract.Users.COLUMN_PASSWORD + " TEXT NOT NULL, " +
DatabaseContract.Users.COLUMN_EMAIL + " TEXT, " +
DatabaseContract.Users.COLUMN_DISPLAY_NAME + " TEXT)";
private static volatile DatabaseHelper instance;
private DatabaseHelper(Context context) {
super(context, DatabaseContract.DATABASE_NAME, null, DatabaseContract.DATABASE_VERSION);
}
public static DatabaseHelper getInstance(Context context) {
if (instance == null) {
instance = new DatabaseHelper(context);
}
return instance;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(SQL_CREATE_USERS_TABLE);
// .... other tables
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (newVersion <= 2) {migrateDBFromVersion1ToVersion2(db);}
}
private void migrateDBFromVersion1ToVersion2(SQLiteDatabase db) {
final String ORIGINAL_PREFIX = "original_";
db.beginTransaction();
try {
db.execSQL("DROP TABLE IF EXISTS " + ORIGINAL_PREFIX + DatabaseContract.Users.TABLE_NAME);
db.execSQL(
"ALTER TABLE " + DatabaseContract.Users.TABLE_NAME +
" RENAME TO " + ORIGINAL_PREFIX + DatabaseContract.Users.TABLE_NAME
);
db.execSQL(SQL_CREATE_USERS_TABLE);
db.execSQL("INSERT INTO " + DatabaseContract.Users.TABLE_NAME +
" SELECT * FROM " + ORIGINAL_PREFIX + DatabaseContract.Users.TABLE_NAME);
db.execSQL("DROP TABLE IF EXISTS " + ORIGINAL_PREFIX + DatabaseContract.Users.TABLE_NAME);
db.setTransactionSuccessful();
} catch (SQLiteException e) {
e.printStackTrace();
//....
}
db.endTransaction();
}
public void logSchema() {
Cursor csr = instance.getWritableDatabase().rawQuery("SELECT * FROM sqlite_master;", null);
Log.i("DBVERSION","Database version is " + instance.getWritableDatabase().getVersion() +
"; Path to database is " + instance.getWritableDatabase().getPath());
DatabaseUtils.dumpCursor(csr);
csr.close();
}
}
The important factors in the above are:-
the coding within the onUpgrade method, which will if the upgrade is from a version less than 2 to version 2 invoke the migrateDBFromVersion1ToVersion2
the basic process followed in migrateDBFromVersion1ToVersion2 that is to:-
onCreate method, so that new App installs have the same schema)The logSchema method which is akin to your getTableMetaData method.
The DatabaseContract class then comes into play as the driver, it being changed to include the database name and the version as per:-
public final class DatabaseContract {
private DatabaseContract() {}
public static final String DATABASE_NAME = "song_tracker.db";
public static final int DATABASE_VERSION = 1;
public static class Users implements BaseColumns {
public static final String TABLE_NAME = "users";
//public static final String COLUMN_USER_ID = "_id"; /* Version 2 */
public static final String COLUMN_USER_ID = "users_id"; /* Version 1 */
public static final String COLUMN_USER_NAME = "user_name";
public static final String COLUMN_PASSWORD = "password";
public static final String COLUMN_EMAIL = "email";
public static final String COLUMN_DISPLAY_NAME = "display_name";
}
// ...rest of tables in db
}
Obviously something needs to be done in an activity so:-
public class MainActivity extends AppCompatActivity {
DatabaseHelper dbHelper;
@SuppressLint("Range")
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper = DatabaseHelper.getInstance(this);
if (DatabaseContract.DATABASE_VERSION == 1) {
SQLiteDatabase db = dbHelper.getReadableDatabase();
ContentValues cv = new ContentValues();
cv.put(DatabaseContract.Users.COLUMN_DISPLAY_NAME,"Fred");
cv.put(DatabaseContract.Users.COLUMN_EMAIL,"fred.bloggs.nomail.moc");
cv.put(DatabaseContract.Users.COLUMN_PASSWORD,"0987654321");
cv.put(DatabaseContract.Users.COLUMN_USER_NAME,"USER00001");
db.insert(DatabaseContract.Users.TABLE_NAME,null,cv);
}
dbHelper.logSchema();
Cursor csr = dbHelper.getWritableDatabase().query(
DatabaseContract.Users.TABLE_NAME,
null,null,null,null,null,null
);
while (csr.moveToNext()) {
Log.i(
"USERDATA",
"User Name is " + csr.getString(csr.getColumnIndex(DatabaseContract.Users.COLUMN_USER_NAME))
+ " Email is " + csr.getString(csr.getColumnIndex(DatabaseContract.Users.COLUMN_EMAIL))
// ....
);
}
csr.close();
}
}
So this will:-
Result/Test 1 - Fresh Install of the App at Version 1
The log includes:-
2023-09-14 07:53:24.666 I/DBVERSION: Database version is 1; Path to database is /data/user/0/a.a.so77093284javasqlitemigration/databases/song_tracker.db
2023-09-14 07:53:24.666 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@98fd0f5
2023-09-14 07:53:24.667 I/System.out: 0 {
2023-09-14 07:53:24.667 I/System.out: type=table
2023-09-14 07:53:24.667 I/System.out: name=android_metadata
2023-09-14 07:53:24.667 I/System.out: tbl_name=android_metadata
2023-09-14 07:53:24.667 I/System.out: rootpage=3
2023-09-14 07:53:24.667 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
2023-09-14 07:53:24.667 I/System.out: }
2023-09-14 07:53:24.667 I/System.out: 1 {
2023-09-14 07:53:24.667 I/System.out: type=table
2023-09-14 07:53:24.667 I/System.out: name=users
2023-09-14 07:53:24.668 I/System.out: tbl_name=users
2023-09-14 07:53:24.668 I/System.out: rootpage=4
2023-09-14 07:53:24.668 I/System.out: sql=CREATE TABLE users (users_id INTEGER PRIMARY KEY AUTOINCREMENT, user_name TEXT NOT NULL, password TEXT NOT NULL, email TEXT, display_name TEXT)
2023-09-14 07:53:24.668 I/System.out: }
2023-09-14 07:53:24.668 I/System.out: 2 {
2023-09-14 07:53:24.668 I/System.out: type=table
2023-09-14 07:53:24.668 I/System.out: name=sqlite_sequence
2023-09-14 07:53:24.668 I/System.out: tbl_name=sqlite_sequence
2023-09-14 07:53:24.668 I/System.out: rootpage=5
2023-09-14 07:53:24.668 I/System.out: sql=CREATE TABLE sqlite_sequence(name,seq)
2023-09-14 07:53:24.668 I/System.out: }
2023-09-14 07:53:24.668 I/System.out: <<<<<
2023-09-14 07:53:24.669 I/USERDATA: User Name is USER00001 Email is fred.bloggs.nomail.moc
So the users table has
Result/Test2 - App just rerun with changed code
For this test 3 lines of code are changed:-
public static final int DATABASE_VERSION = 2; (was 1)public static final String COLUMN_USER_ID = "_id"; /* Version 2 */ (was commented out)//public static final String COLUMN_USER_ID = "users_id"; /* Version 1 */ (was not commented out)The Log now includes:-
2023-09-14 08:03:06.896 I/DBVERSION: Database version is 2; Path to database is /data/user/0/a.a.so77093284javasqlitemigration/databases/song_tracker.db
2023-09-14 08:03:06.896 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@98fd0f5
2023-09-14 08:03:06.897 I/System.out: 0 {
2023-09-14 08:03:06.897 I/System.out: type=table
2023-09-14 08:03:06.897 I/System.out: name=android_metadata
2023-09-14 08:03:06.897 I/System.out: tbl_name=android_metadata
2023-09-14 08:03:06.897 I/System.out: rootpage=3
2023-09-14 08:03:06.897 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
2023-09-14 08:03:06.897 I/System.out: }
2023-09-14 08:03:06.897 I/System.out: 1 {
2023-09-14 08:03:06.897 I/System.out: type=table
2023-09-14 08:03:06.897 I/System.out: name=original_users
2023-09-14 08:03:06.897 I/System.out: tbl_name=original_users
2023-09-14 08:03:06.897 I/System.out: rootpage=4
2023-09-14 08:03:06.898 I/System.out: sql=CREATE TABLE "original_users" (users_id INTEGER PRIMARY KEY AUTOINCREMENT, user_name TEXT NOT NULL, password TEXT NOT NULL, email TEXT, display_name TEXT)
2023-09-14 08:03:06.898 I/System.out: }
2023-09-14 08:03:06.898 I/System.out: 2 {
2023-09-14 08:03:06.898 I/System.out: type=table
2023-09-14 08:03:06.898 I/System.out: name=sqlite_sequence
2023-09-14 08:03:06.898 I/System.out: tbl_name=sqlite_sequence
2023-09-14 08:03:06.898 I/System.out: rootpage=5
2023-09-14 08:03:06.898 I/System.out: sql=CREATE TABLE sqlite_sequence(name,seq)
2023-09-14 08:03:06.898 I/System.out: }
2023-09-14 08:03:06.898 I/System.out: 3 {
2023-09-14 08:03:06.898 I/System.out: type=table
2023-09-14 08:03:06.898 I/System.out: name=users
2023-09-14 08:03:06.899 I/System.out: tbl_name=users
2023-09-14 08:03:06.899 I/System.out: rootpage=6
2023-09-14 08:03:06.899 I/System.out: sql=CREATE TABLE users (_id INTEGER PRIMARY KEY AUTOINCREMENT, user_name TEXT NOT NULL, password TEXT NOT NULL, email TEXT, display_name TEXT)
2023-09-14 08:03:06.899 I/System.out: }
2023-09-14 08:03:06.899 I/System.out: <<<<<
2023-09-14 08:03:06.900 I/USERDATA: User Name is USER00001 Email is fred.bloggs.nomail.moc
i.e:-
The Database version (actual as extracted from the database) has increased to 2.
The users table's columns are now"-
As the execution of the final DROP TABLE original_users was commented out, the now not needed original_users table still exists and it has the users_id column as opposed to the new _id column.
Result/Test3 Fresh Install - Version 2 code
The App is uninstalled and then run the log includes:-
2023-09-14 08:12:01.230 I/DBVERSION: Database version is 2; Path to database is /data/user/0/a.a.so77093284javasqlitemigration/databases/song_tracker.db
2023-09-14 08:12:01.230 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@98fd0f5
2023-09-14 08:12:01.230 I/System.out: 0 {
2023-09-14 08:12:01.230 I/System.out: type=table
2023-09-14 08:12:01.230 I/System.out: name=android_metadata
2023-09-14 08:12:01.230 I/System.out: tbl_name=android_metadata
2023-09-14 08:12:01.230 I/System.out: rootpage=3
2023-09-14 08:12:01.231 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
2023-09-14 08:12:01.231 I/System.out: }
2023-09-14 08:12:01.231 I/System.out: 1 {
2023-09-14 08:12:01.231 I/System.out: type=table
2023-09-14 08:12:01.231 I/System.out: name=users
2023-09-14 08:12:01.231 I/System.out: tbl_name=users
2023-09-14 08:12:01.231 I/System.out: rootpage=4
2023-09-14 08:12:01.231 I/System.out: sql=CREATE TABLE users (_id INTEGER PRIMARY KEY AUTOINCREMENT, user_name TEXT NOT NULL, password TEXT NOT NULL, email TEXT, display_name TEXT)
2023-09-14 08:12:01.231 I/System.out: }
2023-09-14 08:12:01.231 I/System.out: 2 {
2023-09-14 08:12:01.231 I/System.out: type=table
2023-09-14 08:12:01.231 I/System.out: name=sqlite_sequence
2023-09-14 08:12:01.231 I/System.out: tbl_name=sqlite_sequence
2023-09-14 08:12:01.231 I/System.out: rootpage=5
2023-09-14 08:12:01.231 I/System.out: sql=CREATE TABLE sqlite_sequence(name,seq)
2023-09-14 08:12:01.231 I/System.out: }
2023-09-14 08:12:01.231 I/System.out: <<<<<