When does SQLiteOpenHelper onCreate() / onUpgrade() run?
Solution 1
SQLiteOpenHelper
onCreate()
and onUpgrade()
callbacks are invoked when the database is actually opened, for example by a call to getWritableDatabase()
. The database is not opened when the database helper object itself is created.
SQLiteOpenHelper
versions the database files. The version number is the int
argument passed to the constructor. In the database file, the version number is stored in PRAGMA user_version
.
onCreate()
is only run when the database file did not exist and was just created. If onCreate()
returns successfully (doesn't throw an exception), the database is assumed to be created with the requested version number. As an implication, you should not catch SQLException
s in onCreate()
yourself.
onUpgrade()
is only called when the database file exists but the stored version number is lower than requested in the constructor. The onUpgrade()
should update the table schema to the requested version.
When changing the table schema in code (onCreate()
), you should make sure the database is updated. Two main approaches:
-
Delete the old database file so that
onCreate()
is run again. This is often preferred at development time where you have control over the installed versions and data loss is not an issue. Some ways to delete the database file:Uninstall the application. Use the application manager or
adb uninstall your.package.name
from the shell.Clear application data. Use the application manager.
-
Increment the database version so that
onUpgrade()
is invoked. This is slightly more complicated as more code is needed.For development time schema upgrades where data loss is not an issue, you can just use
execSQL("DROP TABLE IF EXISTS <tablename>")
in to remove your existing tables and callonCreate()
to recreate the database.For released versions, you should implement data migration in
onUpgrade()
so your users don't lose their data.
Solution 2
To further add missing points here, as per the request by Jaskey
Database version is stored within the SQLite
database file.
catch is the constructor
SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)
So when the database helper constructor is called with a name
(2nd param), platform checks if the database exists or not and if the database exists, it gets the version information from the database file header and triggers the right call back
As already explained in the older answer, if the database with the name doesn't exists, it triggers onCreate
.
Below explanation explains onUpgrade
case with an example.
Say, your first version of application had the DatabaseHelper
(extending SQLiteOpenHelper
) with constructor passing version as 1
and then you provided an upgraded application with the new source code having version passed as 2
, then automatically when the DatabaseHelper
is constructed, platform triggers onUpgrade
by seeing the file already exists, but the version is lower than the current version which you have passed.
Now say you are planing to give a third version of application with db version as 3
(db version is increased only when database schema is to be modified). In such incremental upgrades, you have to write the upgrade logic from each version incrementally for a better maintainable code
Example pseudo code below:
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
switch(oldVersion) {
case 1:
//upgrade logic from version 1 to 2
case 2:
//upgrade logic from version 2 to 3
case 3:
//upgrade logic from version 3 to 4
break;
default:
throw new IllegalStateException(
"onUpgrade() with unknown oldVersion " + oldVersion);
}
}
Notice the missing break
statement in case 1
and 2
. This is what I mean by incremental upgrade.
Say if the old version is 2
and new version is 4
, then the logic will upgrade the database from 2
to 3
and then to 4
If old version is 3
and new version is 4
, it will just run the upgrade logic for 3
to 4
Solution 3
onCreate()
When we create DataBase at a first time (i.e Database is not exists)
onCreate()
create database with version which is passed inSQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)
onCreate()
method is creating the tables you’ve defined and executing any other code you’ve written. However, this method will only be called if the SQLite file is missing in your app’s data directory (/data/data/your.apps.classpath/databases
).This method will not be called if you’ve changed your code and relaunched in the emulator. If you want
onCreate()
to run you need to use adb to delete the SQLite database file.
onUpgrade()
-
SQLiteOpenHelper
should call the super constructor. - The
onUpgrade()
method will only be called when the version integer is larger than the current version running in the app. - If you want the
onUpgrade()
method to be called, you need to increment the version number in your code.
Solution 4
May be I am too late but I would like to share my short and sweet answer. Please check Answer for a same problem. It will definitely help you. No more deep specifications.
If you are confident about syntax for creating table, than it may happen when you add new column in your same table, for that...
1) Uninstall from your device and run it again.
OR
2) Setting -> app -> ClearData
OR
3) Change DATABASE_VERSION
in your "DatabaseHandler" class (If you have added new column than it will upgrade automatically)
public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
OR
4) Change DATABASE_NAME
in your "DatabaseHandler" class (I faced same problem. But I succeed by changing DATABASE_NAME
.)
Solution 5
Points to remember when extending SQLiteOpenHelper
-
super(context, DBName, null, DBversion);
- This should be invoked first line of constructor - override
onCreate
andonUpgrade
(if needed) -
onCreate
will be invoked only whengetWritableDatabase()
orgetReadableDatabase()
is executed. And this will only invoked once when aDBName
specified in the first step is not available. You can add create table query ononCreate
method - Whenever you want to add new table just change
DBversion
and do the queries inonUpgrade
table or simply uninstall then install the app.
Related videos on Youtube
laalto
I like questions and answers that promote learning and understanding. As a consequence, I value less questions that are just fix-this-for-me dumps or answers that are just code to be copied without learning anything in the process.
Updated on February 02, 2021Comments
-
laalto about 3 years
I have created my tables in my
SQLiteOpenHelper
onCreate()
but receiveSQLiteException: no such table
or
SQLiteException: no such column
errors. Why?
NOTE:
(This is the amalgamated summary of tens of similar questions every week. Attempting to provide a "canonical" community wiki question/answer here so that all those questions can be directed to a good reference.)
-
laalto about 10 years@Ndupza This isn't an actual problem of mine, just fed up writing the same answer/comment for the Nth time.
-
-
bCliks over 9 years@Laalto //data migration in onUpgrade()// Can you please explain about this.
-
laalto over 9 years@bala Not in the scope of this question/answer. If you have a question, feel free to post it as a question.
-
JaskeyLam over 9 years"onUpgrade() is only called when the database file exists but the stored version number is lower than requested in constructor. " Would you please explain for this a little bit more? Do you mean the when the client create a DBHelper, it should know the db version? How can the client pass a right db version without knowing the db ?
-
laalto over 9 years@Jaskey The version number is for your code i.e. what schema version the code expects to run against. If the file is older (from a previous version of your app), it needs to be upgraded.
-
JaskeyLam over 9 yearsSo, I need to hard code the DB VERSION in the SQLiteHelper each time I modify the schema, so that when the old app runs and get the db connection and find it is old, and then onUpgrade will be trgiigered instead of onCreate, is this right?
-
laalto over 9 yearsWhen you update your database schema to an incompatible version then yes, you need to update the version number to distinguish incompatible database files.
onCreate()
is never triggered if the database already exists. -
JaskeyLam over 9 yearsThank you ! This make sense to me. Please verify if I understand well.So we need to do 1. every time we update the schema, modify the DB_VERSION variable(hard code). 2. In
onUpdate()
, check every old version and do proper data migration . And then when a user update their app(they have old db files),onUpgrade
will be Triggered, and if the user is newly install,onCreate()
is triggered. -
joe p over 9 yearsI think you want your switch(newVersion) to be switch(oldVersion) instead. You may also want to verify that newVersion is 4 (and not 5, or 3; because your logic is assuming the new version should be 4).As it is, if the old version is 2 and new version is 5, you'll hit the case 4: and upgrade from 3 to 4 (which should probably not be expected behavior).
-
Aun over 9 yearsright - typo.. but if new version is 5 -> then it will always throw IllegalStateException and developer will be fixing it by adding case 5..
-
Paramvir Singh about 9 yearsWhat if the user upgrades his app from version 2 to 3 only? In that case also, all the cases upto case 4 will run.
-
Mohammed H about 9 years@param user cannot do that. He can upgrade 2 to latest (here 4) only.
-
abarisone almost 9 yearsCould you please elaborate more your answer adding a little more description about the solution you provide?
-
Roger Huang about 7 yearsonUpgrade method is called when the database version increases, not when the number of column is changed. Ref: developer.android.com/reference/android/database/sqlite/…, int, int)
-
Behzad over 6 yearsI have my own DB and using SQLiteAssetHelper class. So, I did create the DB by sql script before and the db was created. By using the SQLiteAssetHelper it couldn't copy the DB until uninstalling the app form the emulator or device, because it was a db with the same version.
-
LordWabbit almost 4 yearsOn a side note, when onUpgrade is called (actually it looks like before) the PRAGMA user_version is set to the newVersion, so even if nothing is done in onUpgrade it will only fire ONCE. Afterwards it will consider the database upgraded because the version number in code will match the user_version in the database.