Android upgrading DB version and adding new table

Question

I ve already created sqlite tables for my app  but now I want to add a new table to the database   I changed the DB version as below  private static final int DATABASE VERSION   2    and Added string to create table  private static final String DATABASE CREATE color        CREATE TABLE IF NOT EXISTS files color text  incident id text      onCreate and onUpgrade as below    Override     public void onCreate SQLiteDatabase database            database execSQL DATABASE CREATE incident           database execSQL DATABASE CREATE audio           database execSQL DATABASE CREATE video           database execSQL DATABASE CREATE image                Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion              drop table and add new tables when version 2 released          db execSQL DATABASE CREATE color            But for some reason the new table is not being created  What am I doing wrong

User · Answer

Your code looks correct   My suggestion is that the database already thinks it s upgraded  If you executed the project after incrementing the version number  but before adding the execSQL call  the database on your test device emulator may already believe it s at version 2   A quick way to verify this would be to change the version number to 3 -- if it upgrades after that  you know it was just because your device believed it was already upgraded

User · Answer

Handling database versions is very important part of application development  I assume that you already have class AppDbHelper extending SQLiteOpenHelper  When you extend it you will need to implement onCreate and onUpgrade method    When onCreate and onUpgrade methods called   onCreate called when app newly installed  onUpgrade called when app updated    Organizing Database versions I manage versions in a class methods  Create implementation of interface Migration  E g  For first version create MigrationV1 class  second version create MigrationV1ToV2  these are my naming convention         public interface Migration           void run SQLiteDatabase db    create tables  alter tables         Example migration   public class MigrationV1ToV2 implements Migration        public void run SQLiteDatabase db             create new tables           alter existing tables add column  add remove constraint            etc                 Using Migration classes   onCreate  Since onCreate will be called when application freshly installed  we also need to execute all migrations database version updates   So onCreate will looks like this   public void onCreate SQLiteDatabase db           Migration mV1 new MigrationV1             put your first database schema in this class         mV1 run db           Migration mV1ToV2 new MigrationV1ToV2            mV1ToV2 run db             other migration if any       onUpgrade  This method will be called when application is already installed and it is updated to new application version  If application contains any database changes then put all database changes in new Migration class and increment database version    For example  lets say user has installed application which has database version 1  and now database version is updated to 2 all schema updates kept in MigrationV1ToV2   Now when application upgraded  we need to upgrade database by applying database schema changes in MigrationV1ToV2 like this   public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion        if  oldVersion  lt  2              means old version is 1         Migration migration   new MigrationV1ToV2            migration run db             if  oldVersion  lt  3              means old version is 2              Note  All upgrades  mentioned in onUpgrade  in to database schema should be executed in onCreate

User · Answer

1  About onCreate   and onUpgrade    onCreate     is called whenever the app is freshly installed   onUpgrade is called whenever the app is upgraded and launched and the database version is not the same     2  Incrementing the db version  You need a constructor like   MyOpenHelper Context context       super context   dbname   null  2      2 is the database version     IMPORTANT  Incrementing the app version alone is not enough for onUpgrade to be called   3  Don t forget your new users   Don t forget to add  database execSQL DATABASE CREATE color     to your onCreate   method as well or newly installed apps will lack the table   4   How to deal with multiple database changes over time  When you have successive app upgrades  several of which have database upgrades  you want to be sure to check oldVersion   onUpgrade SQLiteDatabase db  int oldVersion  int newVersion       switch oldVersion       case 1         db execSQL DATABASE CREATE color             we want both updates  so no break statement here       case 2         db execSQL DATABASE CREATE someothertable             This way when a user upgrades from version 1 to version 3  they get both updates   When a user upgrades from version 2 to 3  they just get the revision 3 update     After all  you can t count on 100  of your user base to upgrade each time you release an update   Sometimes they skip an update or 12     5   Keeping your revision numbers under control while developing  And finally    calling   adb uninstall  lt yourpackagename gt    totally uninstalls the app   When you install again  you are guaranteed to hit onCreate which keeps you from having to keep incrementing the database version into the stratosphere as you develop

User · Answer

jkschneider s answer is right  However there is a better approach    Write the needed changes in an sql file for each update as described in the link https   riggaroo co za android-sqlite-database-use-onupgrade-correctly   from 1 to 2 sql  ALTER TABLE books ADD COLUMN book rating INTEGER    from 2 to 3 sql  ALTER TABLE books RENAME TO book information    from 3 to 4 sql  ALTER TABLE book information ADD COLUMN calculated pages times rating INTEGER  UPDATE book information SET calculated pages times rating    book pages   book rating      These  sql files will be executed in onUpgrade   method according to the version of the database   DatabaseHelper java  public class DatabaseHelper extends SQLiteOpenHelper        private static final int DATABASE VERSION   4       private static final String DATABASE NAME    database db       private static final String TAG   DatabaseHelper class getName         private static DatabaseHelper mInstance   null      private final Context context       private DatabaseHelper Context context            super context  DATABASE NAME  null  DATABASE VERSION           this context   context             public static synchronized DatabaseHelper getInstance Context ctx            if  mInstance    null                mInstance   new DatabaseHelper ctx getApplicationContext                       return mInstance              Override     public void onCreate SQLiteDatabase db            db execSQL BookEntry SQL CREATE BOOK ENTRY TABLE              The rest of your create scripts go here                Override     public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion            Log e TAG   Updating table from     oldVersion     to     newVersion              You will not need to modify this unless you need to do some android specific things             When upgrading the database  all you need to do is add a file to the assets folder and name it             from 1 to 2 sql with the version that you are upgrading to as the last version          try               for  int i   oldVersion  i  lt  newVersion    i                    String migrationName   String format  from  d to  d sql   i   i   1                    Log d TAG   Looking for migration file      migrationName                   readAndExecuteSQLScript db  context  migrationName                           catch  Exception exception                Log e TAG   Exception running upgrade script    exception                          Override     public void onDowngrade SQLiteDatabase db  int oldVersion  int newVersion                private void readAndExecuteSQLScript SQLiteDatabase db  Context ctx  String fileName            if  TextUtils isEmpty fileName                 Log d TAG   SQL script file name is empty                return                     Log d TAG   Script found  Executing               AssetManager assetManager   ctx getAssets            BufferedReader reader   null           try               InputStream is   assetManager open fileName               InputStreamReader isr   new InputStreamReader is               reader   new BufferedReader isr               executeSQLScript db  reader             catch  IOException e                Log e TAG   IOException    e             finally               if  reader    null                    try                       reader close                      catch  IOException e                        Log e TAG   IOException    e                                                         private void executeSQLScript SQLiteDatabase db  BufferedReader reader  throws IOException           String line          StringBuilder statement   new StringBuilder            while   line   reader readLine       null                statement append line               statement append   n                if  line endsWith                         db execSQL statement toString                     statement   new StringBuilder                                      An example project is provided in the same link also   https   github com riggaroo AndroidDatabaseUpgrades

User · Answer

You can use SQLiteOpenHelper s onUpgrade method  In the onUpgrade method  you get the oldVersion as one of the parameters   In the onUpgrade use a switch and in each of the cases use the version number to keep track of the current version of database   It s best that you loop over from oldVersion to newVersion  incrementing version by 1 at a time and then upgrade the database step by step  This is very helpful when someone with database version 1 upgrades the app after a long time  to a version using database version 7 and the app starts crashing because of certain incompatible changes   Then the updates in the database will be done step-wise  covering all possible cases  i e  incorporating the changes in the database done for each new version and thereby preventing your application from crashing   For example   public void onUpgrade SQLiteDatabase db  int oldVersion  int newVersion        switch  oldVersion        case 1          String sql    ALTER TABLE     TABLE SECRET     ADD COLUMN      name of column to be added      INTEGER           db execSQL sql           break       case 2          String sql    SOME QUERY           db execSQL sql           break

[android] Android: upgrading DB version and adding new table

Examples related to android

Examples related to database

Examples related to sqlite