properly upgrading a table in sqlite

Question

I have an app that has several tables in the DB. I what to upgrade 2 of the tables as i'm adding an extra field to them both. I want the other tables to remain the same.

So far i have overridden the onUpgrade method of the SQLiteOpenHelper class. The 2 tables i want to ugrade are the TRANSACTIONS table and the COMPANYID table. I do this in the onUpgrade by re-naming the tables, re-creating them with the new field, then copying the old data in.

This works fine.

The problem i am having is the other tables do not exist when i install the upgrade. I'm not sure how the upgrade works but i don't explicitly call onCreate from within onUpgrade once i've changed the tables i need.

I have tried calling onCreate from within onUpgrade but then i get an error stating the TRANSACTIONS table already exists, which i expect as i've just created it in onUpgrade.

How should this be done correctly? Should i call onCreate from within onUpgrade but do a test to see if the table already exist when onCreate executes?

NB i am incrementing the version number correctly.

Thanks in advance, Matt.

private class DBHelper extends SQLiteOpenHelper {

        // database name and version number
        public static final String DB_NAME = "carefreemobiledb.db";
        public static final int DB_VERSION = 54;

        // table names
        public static final String TABLETRANSACTIONS = "transactions";
        public static final String TABLECARER = "carer";
        public static final String TABLETRANSACTIONSMAP = "transactionsmap";
        public static final String TABLEPHONE = "phone";
        public static final String TABLECOMPANYID = "companyid";
        public static final String TABLEBACKGROUNDSERVICES = "backgroundservices";
        public static final String TABLEMESSAGE = "message";
        public static final String TABLEDUPLICATETRANSACTIONS = "tableduplicatetransactions";
        public static final String TABLECACHEDROTA = "tablecachedrota";
        public static final String TABLEUSERS = "users";

        public DBHelper() {
            super(context, DB_NAME, null, DB_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            String sqlToCreateTransactionsTable = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s INT, %s TEXT, %s TEXT, %s TEXT )",
                            TABLETRANSACTIONS, C_ID, C_TYPE, C_COMPANY_ID,
                            C_PERSON_ID, C_NAME, C_TAG_ID, C_STATUS,
                            C_TAG_SCAN_TIME, C_TAG_SENTSERVER_TIME,
                            C_TRANSACTIONS_LATITUDE, C_TRANSACTIONS_LONGITUDE,
                            C_DRIVER);

            db.execSQL(sqlToCreateTransactionsTable);
            Log.e(TAG, "oncreate " + sqlToCreateTransactionsTable);

            String sqlToCreateCarerTable = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT)",
                            TABLECARER, C_ID_INDEX, C_CARER_ID, C_COMP_ID,
                            C_CARER_FIRSTNAME, C_CARER_LASTNAME, C_PASSWORD,
                            C_DATE_TIME);

            db.execSQL(sqlToCreateCarerTable);
            Log.e(TAG, "oncreate " + sqlToCreateCarerTable);

            String sqlToCreateTransactionsmapTable = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT)",
                            TABLETRANSACTIONSMAP, C_ID, C_TYPE, C_COMPANY_ID,
                            C_PERSON_ID, C_NAME, C_TAG_ID, C_STATUS,
                            C_TAG_SCAN_TIME, C_LONGITUDE, C_LATITUDE);

            db.execSQL(sqlToCreateTransactionsmapTable);
            Log.e(TAG, "oncreate " + sqlToCreateTransactionsmapTable);

            String sqlToCreatePhone = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT)",
                            TABLEPHONE, C_ID_PHONE, C_PHONE_NAME,
                            C_PHONE_NUMBER);

            db.execSQL(sqlToCreatePhone);
            Log.e(TAG, "oncreate " + sqlToCreatePhone);

            String sqlToCreateUsers = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT)",
                            TABLEUSERS, C_ID_USERS, C_USER_NAME, C_USER_ID);

            db.execSQL(sqlToCreateUsers);
            Log.e(TAG, "oncreate " + sqlToCreateUsers);

            String sqlToCreateMessage = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT)",
                            TABLEMESSAGE, C_ID_MESSAGE, C_MESSAGE_GUID,
                            C_MESSAGE_TEXT, C_MESSAGE_CREATED_AT,
                            C_MESSAGE_SENDER, C_MESSAGE_REPLIED,
                            C_MESSAGE_SEEN, C_MESSAGE_DISPLAYED,
                            C_MESSAGE_REPLY_MESSAGE, C_MESSAGE_IS_STANDALONE);

            db.execSQL(sqlToCreateMessage);
            Log.e(TAG, "oncreate " + sqlToCreateMessage);

            String sqlToCreateBackgroundServices = String.format(
                    "create table %s ( %s INTEGER primary key, %s TEXT)",
                    TABLEBACKGROUNDSERVICES, C_ID_BACKGROUNDSERVICES,
                    C_BACKGROUNDSERVICES_HAVE_RUN_ONCE);

            db.execSQL(sqlToCreateBackgroundServices);
            Log.e(TAG, "oncreate " + sqlToCreateBackgroundServices);

            String sqlToCreateCompanyId = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s TEXT)", TABLECOMPANYID,
                            C_ID_COMPANY_ID_OUTSIDE_APP_PURPOSES,
                            C_COMPANY_ID_OUTSIDE_APP_PURPOSES,
                            C_COMPANY_TAG_TOUCH_INTERVAL,
                            C_COMPANY_SAVE_PASSWORD,
                            C_COMPANY_ALLOW_CHANGE_USER,
                            C_COMPANY_DISPLAY_ROTA_DETAILS,
                            C_COMPANY_DISPLAY_CLIENT_PHONENUMBER,
                            C_COMPANY_DISPLAY_CLIENTKEYSAFE,
                            C_COMPANY_DISPLAY_DOUBLEUP_CARER,
                            C_COMPANY_DISPLAY_CONTACTS, C_COMPANY_AUTOLOGOUT,
                            C_COMPANY_DISPLAY_NOTES, C_COMPANY_DISPLAY_MEDS,
                            C_COMPANY_ROTALOGOUT,
                            C_COMPANY_DISPLAY_ACTUAL_TIME,
                            C_COMPANY_QRCODEENABLED, C_COMPANY_MANUAL_INPUT,
                            C_COMPANY_ROTAS_ONLY, C_COMPANY_COMPNAME,
                            C_COMPANY_COMP_URL, C_COMPANY_CALC_GPS_COORDS,
                            C_COMPANY_SYSTEM_EXTERNAL_TIME_TOLERANCE,
                            C_COMPANY_CARER_REPORTING,
                            C_COMPANY_SHOW_DIRECTIONS, C_COMPANY_LONE_WORKER,
                            C_COMPANY_SHOW_DELETE_BUTTON,
                            C_COMPANY_CARER_MESSAGING,
                            C_COMPANY_CANNOT_SIGN_IN_INTERVAL,
                            C_FORMATTED_TAGS_ONLY, C_LOG_TO_SDCARD,
                            C_SHOWGENERALNOTES, C_SHOWENTRYMETHOD,
                            C_PROMPTFORDRIVER);

            db.execSQL(sqlToCreateCompanyId);
            Log.e(TAG, "oncreate " + sqlToCreateCompanyId);

            String sqlToCreateDuplicateTransactions = String.format(
                    "create table %s ( %s INTEGER primary key, %s TEXT)",
                    TABLEDUPLICATETRANSACTIONS, C_ID_DUPLICATETRANSACTIONS,
                    C_DUPLICATETRANSACTIONS);

            db.execSQL(sqlToCreateDuplicateTransactions);
            Log.e(TAG, "oncreate " + sqlToCreateDuplicateTransactions);

            String sqlToCreateCachedRota = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT)",
                            TABLECACHEDROTA, C_ID_CACHED_ROTA,
                            C_CACHED_ROTA_DATE, C_CACHED_ROTA);

            db.execSQL(sqlToCreateCachedRota);
            Log.e(TAG, "oncreate " + sqlToCreateCachedRota);

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {


            //copy transaction data

            db.execSQL("ALTER TABLE "+TABLETRANSACTIONS+" RENAME TO Old"+TABLETRANSACTIONS);

            String sqlToCreateTransactionsTable = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s INT, %s TEXT, %s TEXT, %s TEXT )",
                            TABLETRANSACTIONS, C_ID, C_TYPE, C_COMPANY_ID,
                            C_PERSON_ID, C_NAME, C_TAG_ID, C_STATUS,
                            C_TAG_SCAN_TIME, C_TAG_SENTSERVER_TIME,
                            C_TRANSACTIONS_LATITUDE, C_TRANSACTIONS_LONGITUDE, C_DRIVER);

            db.execSQL(sqlToCreateTransactionsTable);
            Log.e(TAG, "onUpgrade " + sqlToCreateTransactionsTable);


            db.execSQL("INSERT INTO " + TABLETRANSACTIONS + " (" + C_ID+", " + C_TYPE +", " + C_COMPANY_ID+", " + C_PERSON_ID + ",  " + C_NAME+",  " + C_TAG_ID+", " + C_STATUS + ", " +C_TAG_SCAN_TIME +", " +
                C_TAG_SENTSERVER_TIME +", "+ C_TRANSACTIONS_LATITUDE+", " +C_TRANSACTIONS_LONGITUDE +" ) SELECT " +C_ID +", " +C_TYPE+ ", " +C_COMPANY_ID+", " +
                C_PERSON_ID+",  " +C_NAME+",  " + C_TAG_ID+", " +C_STATUS+", "+C_TAG_SCAN_TIME+", "+C_TAG_SENTSERVER_TIME+", "+ C_TRANSACTIONS_LATITUDE+", "+C_TRANSACTIONS_LONGITUDE+" FROM Old"+TABLETRANSACTIONS);

            db.execSQL("DROP TABLE Old"+TABLETRANSACTIONS);
            Log.e(TAG, " SQLiteOpenHelper onUpgrade: finished copying old data to new transaction table");






            //copy company option table data

            db.execSQL("ALTER TABLE "+TABLECOMPANYID+" RENAME TO Old"+TABLECOMPANYID);

            String sqlToCreateCompanyId = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s TEXT)", TABLECOMPANYID,
                            C_ID_COMPANY_ID_OUTSIDE_APP_PURPOSES,
                            C_COMPANY_ID_OUTSIDE_APP_PURPOSES,
                            C_COMPANY_TAG_TOUCH_INTERVAL,
                            C_COMPANY_SAVE_PASSWORD,
                            C_COMPANY_ALLOW_CHANGE_USER,
                            C_COMPANY_DISPLAY_ROTA_DETAILS,
                            C_COMPANY_DISPLAY_CLIENT_PHONENUMBER,
                            C_COMPANY_DISPLAY_CLIENTKEYSAFE,
                            C_COMPANY_DISPLAY_DOUBLEUP_CARER,
                            C_COMPANY_DISPLAY_CONTACTS, C_COMPANY_AUTOLOGOUT,
                            C_COMPANY_DISPLAY_NOTES, C_COMPANY_DISPLAY_MEDS,
                            C_COMPANY_ROTALOGOUT,
                            C_COMPANY_DISPLAY_ACTUAL_TIME,
                            C_COMPANY_QRCODEENABLED, C_COMPANY_MANUAL_INPUT,
                            C_COMPANY_ROTAS_ONLY, C_COMPANY_COMPNAME,
                            C_COMPANY_COMP_URL, C_COMPANY_CALC_GPS_COORDS,
                            C_COMPANY_SYSTEM_EXTERNAL_TIME_TOLERANCE,
                            C_COMPANY_CARER_REPORTING,
                            C_COMPANY_SHOW_DIRECTIONS, C_COMPANY_LONE_WORKER,
                            C_COMPANY_SHOW_DELETE_BUTTON,
                            C_COMPANY_CARER_MESSAGING,
                            C_COMPANY_CANNOT_SIGN_IN_INTERVAL,
                            C_FORMATTED_TAGS_ONLY, C_LOG_TO_SDCARD,
                            C_SHOWGENERALNOTES, C_SHOWENTRYMETHOD,
                            C_PROMPTFORDRIVER);

            db.execSQL(sqlToCreateCompanyId);
            Log.e(TAG, "onUpgrade " + sqlToCreateCompanyId);

            db.execSQL("INSERT INTO " + TABLECOMPANYID + " ("  +  C_ID_COMPANY_ID_OUTSIDE_APP_PURPOSES + "," +
                    C_COMPANY_ID_OUTSIDE_APP_PURPOSES +"," +
                    C_COMPANY_TAG_TOUCH_INTERVAL +"," +
                    C_COMPANY_SAVE_PASSWORD +"," +
                    C_COMPANY_ALLOW_CHANGE_USER +"," +
                    C_COMPANY_DISPLAY_ROTA_DETAILS +"," +
                    C_COMPANY_DISPLAY_CLIENT_PHONENUMBER +"," +
                    C_COMPANY_DISPLAY_CLIENTKEYSAFE +"," +
                    C_COMPANY_DISPLAY_DOUBLEUP_CARER +"," +
                    C_COMPANY_DISPLAY_CONTACTS+"," + 
                    C_COMPANY_AUTOLOGOUT +"," +
                    C_COMPANY_DISPLAY_NOTES+"," +
                    C_COMPANY_DISPLAY_MEDS +"," +
                    C_COMPANY_ROTALOGOUT +"," +
                    C_COMPANY_DISPLAY_ACTUAL_TIME +"," +
                    C_COMPANY_QRCODEENABLED+"," +
                    C_COMPANY_MANUAL_INPUT +"," +
                    C_COMPANY_ROTAS_ONLY+"," +
                    C_COMPANY_COMPNAME +"," +
                    C_COMPANY_COMP_URL+"," +
                    C_COMPANY_CALC_GPS_COORDS +"," +
                    C_COMPANY_SYSTEM_EXTERNAL_TIME_TOLERANCE +"," +
                    C_COMPANY_CARER_REPORTING +"," +
                    C_COMPANY_SHOW_DIRECTIONS+"," +
                    C_COMPANY_LONE_WORKER +"," +
                    C_COMPANY_SHOW_DELETE_BUTTON +"," +
                    C_COMPANY_CARER_MESSAGING +"," +
                    C_COMPANY_CANNOT_SIGN_IN_INTERVAL +"," +
                    C_FORMATTED_TAGS_ONLY+"," +
                    C_LOG_TO_SDCARD +"," +
                    C_SHOWGENERALNOTES+"," +
                    C_SHOWENTRYMETHOD  +" ) SELECT " + C_ID_COMPANY_ID_OUTSIDE_APP_PURPOSES + "," +
                            C_COMPANY_ID_OUTSIDE_APP_PURPOSES +"," +
                            C_COMPANY_TAG_TOUCH_INTERVAL +"," +
                            C_COMPANY_SAVE_PASSWORD +"," +
                            C_COMPANY_ALLOW_CHANGE_USER +"," +
                            C_COMPANY_DISPLAY_ROTA_DETAILS +"," +
                            C_COMPANY_DISPLAY_CLIENT_PHONENUMBER +"," +
                            C_COMPANY_DISPLAY_CLIENTKEYSAFE +"," +
                            C_COMPANY_DISPLAY_DOUBLEUP_CARER +"," +
                            C_COMPANY_DISPLAY_CONTACTS+"," +
                            C_COMPANY_AUTOLOGOUT +"," +
                            C_COMPANY_DISPLAY_NOTES+"," +
                            C_COMPANY_DISPLAY_MEDS +"," +
                            C_COMPANY_ROTALOGOUT +"," +
                            C_COMPANY_DISPLAY_ACTUAL_TIME +"," +
                            C_COMPANY_QRCODEENABLED+"," +
                            C_COMPANY_MANUAL_INPUT +"," +
                            C_COMPANY_ROTAS_ONLY+"," +
                            C_COMPANY_COMPNAME +"," +
                            C_COMPANY_COMP_URL+"," +
                            C_COMPANY_CALC_GPS_COORDS +"," +
                            C_COMPANY_SYSTEM_EXTERNAL_TIME_TOLERANCE +"," +
                            C_COMPANY_CARER_REPORTING +"," +
                            C_COMPANY_SHOW_DIRECTIONS+"," +
                            C_COMPANY_LONE_WORKER +"," +
                            C_COMPANY_SHOW_DELETE_BUTTON +"," +
                            C_COMPANY_CARER_MESSAGING +"," +
                            C_COMPANY_CANNOT_SIGN_IN_INTERVAL +"," +
                            C_FORMATTED_TAGS_ONLY+"," +
                            C_LOG_TO_SDCARD +"," +
                            C_SHOWGENERALNOTES+"," +
                            C_SHOWENTRYMETHOD     +" FROM Old"+TABLECOMPANYID);


            db.execSQL("DROP TABLE Old"+TABLECOMPANYID);
            Log.e(TAG, " SQLiteOpenHelper onUpgrade: finished copying old data to new companyoptions table");

            this.onCreate(db);


        }//end of onUpgrade

    }

.

[edit1]

private class DBHelper extends SQLiteOpenHelper {

        // database name and version number
        public static final String DB_NAME = "carefreemobiledb.db";
        public static final int DB_VERSION = 54;

        // table names
        public static final String TABLETRANSACTIONS = "transactions";
        public static final String TABLECARER = "carer";
        public static final String TABLETRANSACTIONSMAP = "transactionsmap";
        public static final String TABLEPHONE = "phone";
        public static final String TABLECOMPANYID = "companyid";
        public static final String TABLEBACKGROUNDSERVICES = "backgroundservices";
        public static final String TABLEMESSAGE = "message";
        public static final String TABLEDUPLICATETRANSACTIONS = "tableduplicatetransactions";
        public static final String TABLECACHEDROTA = "tablecachedrota";
        public static final String TABLEUSERS = "users";

        public DBHelper() {
            super(context, DB_NAME, null, DB_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            Log.e(TAG, "SQLiteOpenHelper oncreate ");

            String sqlToCreateTransactionsTable = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s INT, %s TEXT, %s TEXT, %s TEXT )",
                            TABLETRANSACTIONS, C_ID, C_TYPE, C_COMPANY_ID,
                            C_PERSON_ID, C_NAME, C_TAG_ID, C_STATUS,
                            C_TAG_SCAN_TIME, C_TAG_SENTSERVER_TIME,
                            C_TRANSACTIONS_LATITUDE, C_TRANSACTIONS_LONGITUDE,
                            C_DRIVER);

            db.execSQL(sqlToCreateTransactionsTable);
            Log.e(TAG, "oncreate " + sqlToCreateTransactionsTable);

            String sqlToCreateCarerTable = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT)",
                            TABLECARER, C_ID_INDEX, C_CARER_ID, C_COMP_ID,
                            C_CARER_FIRSTNAME, C_CARER_LASTNAME, C_PASSWORD,
                            C_DATE_TIME);

            db.execSQL(sqlToCreateCarerTable);
            Log.e(TAG, "oncreate " + sqlToCreateCarerTable);

            String sqlToCreateTransactionsmapTable = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT)",
                            TABLETRANSACTIONSMAP, C_ID, C_TYPE, C_COMPANY_ID,
                            C_PERSON_ID, C_NAME, C_TAG_ID, C_STATUS,
                            C_TAG_SCAN_TIME, C_LONGITUDE, C_LATITUDE);

            db.execSQL(sqlToCreateTransactionsmapTable);
            Log.e(TAG, "oncreate " + sqlToCreateTransactionsmapTable);

            String sqlToCreatePhone = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT)",
                            TABLEPHONE, C_ID_PHONE, C_PHONE_NAME,
                            C_PHONE_NUMBER);

            db.execSQL(sqlToCreatePhone);
            Log.e(TAG, "oncreate " + sqlToCreatePhone);

            String sqlToCreateUsers = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT)",
                            TABLEUSERS, C_ID_USERS, C_USER_NAME, C_USER_ID);

            db.execSQL(sqlToCreateUsers);
            Log.e(TAG, "oncreate " + sqlToCreateUsers);

            String sqlToCreateMessage = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT)",
                            TABLEMESSAGE, C_ID_MESSAGE, C_MESSAGE_GUID,
                            C_MESSAGE_TEXT, C_MESSAGE_CREATED_AT,
                            C_MESSAGE_SENDER, C_MESSAGE_REPLIED,
                            C_MESSAGE_SEEN, C_MESSAGE_DISPLAYED,
                            C_MESSAGE_REPLY_MESSAGE, C_MESSAGE_IS_STANDALONE);

            db.execSQL(sqlToCreateMessage);
            Log.e(TAG, "oncreate " + sqlToCreateMessage);

            String sqlToCreateBackgroundServices = String.format(
                    "create table %s ( %s INTEGER primary key, %s TEXT)",
                    TABLEBACKGROUNDSERVICES, C_ID_BACKGROUNDSERVICES,
                    C_BACKGROUNDSERVICES_HAVE_RUN_ONCE);

            db.execSQL(sqlToCreateBackgroundServices);
            Log.e(TAG, "oncreate " + sqlToCreateBackgroundServices);

            String sqlToCreateCompanyId = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT, %s TEXT,"
                            + " %s TEXT, %s TEXT, %s TEXT)", TABLECOMPANYID,
                            C_ID_COMPANY_ID_OUTSIDE_APP_PURPOSES,
                            C_COMPANY_ID_OUTSIDE_APP_PURPOSES,
                            C_COMPANY_TAG_TOUCH_INTERVAL,
                            C_COMPANY_SAVE_PASSWORD,
                            C_COMPANY_ALLOW_CHANGE_USER,
                            C_COMPANY_DISPLAY_ROTA_DETAILS,
                            C_COMPANY_DISPLAY_CLIENT_PHONENUMBER,
                            C_COMPANY_DISPLAY_CLIENTKEYSAFE,
                            C_COMPANY_DISPLAY_DOUBLEUP_CARER,
                            C_COMPANY_DISPLAY_CONTACTS, C_COMPANY_AUTOLOGOUT,
                            C_COMPANY_DISPLAY_NOTES, C_COMPANY_DISPLAY_MEDS,
                            C_COMPANY_ROTALOGOUT,
                            C_COMPANY_DISPLAY_ACTUAL_TIME,
                            C_COMPANY_QRCODEENABLED, C_COMPANY_MANUAL_INPUT,
                            C_COMPANY_ROTAS_ONLY, C_COMPANY_COMPNAME,
                            C_COMPANY_COMP_URL, C_COMPANY_CALC_GPS_COORDS,
                            C_COMPANY_SYSTEM_EXTERNAL_TIME_TOLERANCE,
                            C_COMPANY_CARER_REPORTING,
                            C_COMPANY_SHOW_DIRECTIONS, C_COMPANY_LONE_WORKER,
                            C_COMPANY_SHOW_DELETE_BUTTON,
                            C_COMPANY_CARER_MESSAGING,
                            C_COMPANY_CANNOT_SIGN_IN_INTERVAL,
                            C_FORMATTED_TAGS_ONLY, C_LOG_TO_SDCARD,
                            C_SHOWGENERALNOTES, C_SHOWENTRYMETHOD,
                            C_PROMPTFORDRIVER);

            db.execSQL(sqlToCreateCompanyId);
            Log.e(TAG, "oncreate " + sqlToCreateCompanyId);

            String sqlToCreateDuplicateTransactions = String.format(
                    "create table %s ( %s INTEGER primary key, %s TEXT)",
                    TABLEDUPLICATETRANSACTIONS, C_ID_DUPLICATETRANSACTIONS,
                    C_DUPLICATETRANSACTIONS);

            db.execSQL(sqlToCreateDuplicateTransactions);
            Log.e(TAG, "oncreate " + sqlToCreateDuplicateTransactions);

            String sqlToCreateCachedRota = String
                    .format("create table %s ( %s INTEGER primary key, %s TEXT, %s TEXT)",
                            TABLECACHEDROTA, C_ID_CACHED_ROTA,
                            C_CACHED_ROTA_DATE, C_CACHED_ROTA);

            db.execSQL(sqlToCreateCachedRota);
            Log.e(TAG, "oncreate " + sqlToCreateCachedRota);

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

            Log.e(TAG, "SQLiteOpenHelper onUpgrade ");

            if (oldVersion < 54) {

                // do upgrade from 52/53 to 54


                //copy transaction data

                db.execSQL("ALTER TABLE "+TABLETRANSACTIONS+" ADD " + C_DRIVER + " TEXT");

                Log.e(TAG, "Just added driver column to Transactions table");

                db.execSQL("ALTER TABLE "+TABLECOMPANYID+" ADD " + C_PROMPTFORDRIVER + " TEXT");

                Log.e(TAG, "Just added promptfordriver column to companyid table");

            }



        }//end of onUpgrade

    }

check this http://stackoverflow.com/a/14419630/1329126 – Sankar V Oct 30 '13 at 14:20 — Sankar V, Oct 30 '13 at 14:20

323go · Answer 1 · 2013-10-30T18:05:08.000

1

I generally run database scripts (more on that below), but if you strictly stick to the SQLiteOpenHelper pattern, then you would be better off wrapping each version in its own method, say, createVersion0, upgradeVersion1, updgradeVersion2, etc. Your onCreate would then look like this:

    @Override
    public void onCreate(SQLiteDatabase db) {
        createVersion0( db );
        upgradeVersion1( db );
        upgradeVersion2( db );
        ....
    }

And you would change youd onUpgrade accordingly.

I generally run db scripts, where those scripts are in assets, and the onCreate and onUpgrade just invoke an applyScripts method to bring the db up to speed. The nice thing about that is that you can use ALTER TABLE .... ADD COLUMN .... statements, which is much cleaner than dropping and recreating each table, especially if you like the data that's in them.

edited Oct 30 '13 at 18:05

answered Oct 30 '13 at 14:16

323go

14,143
6
33
41

ok thanks. i'm still a little unsure on how the upgrade process works and which methods are called. I think that oncreate is called first unless the DB version number is higher than previous. in this case onUpgrade is called first? Looking at your answer above how would the onUpgrade look. Could you also explain which methods are called in which order. Thanks – turtleboy Oct 31 '13 at 09:17
i've edited the original post, this is what i have now. It seems to work upgrading from version 53 to 54. it works on a fresh install on version 54. i've added the 2 new fields C_DRIVER and C_PROMPT_FOR_DRIVER to the sql statements in onCreate. i then did a check in onUpgrade to see if version is 53 to 54. Have i done this correctly? – turtleboy Oct 31 '13 at 11:07

score 0 · Answer 2 · answered Oct 30 '13 at 14:14

0

I would create an extra method for creating new tables and call it from onUpgrade and onCreate.

About upgrading existing ones: SQLite supports ALTER TABLE ... ADD COLUMN ...! Wouldn't it make existing table upgrade much easier???

answered Oct 30 '13 at 14:14

LS_ᴅᴇᴠ

10,823
1
23
46

properly upgrading a table in sqlite

2 Answers2