Android - Sqlite - Detect if new version not found

Question

Updated: Bref: The answer of Kuffs is simple and correct. The answer of MikeT is a interesting approach.

Old version: I'm working on an android app. I need to know if there are a new sql version or not. If my db is up to date, I will trigger an async action. But how can I trigger my async action?

Currently, I have my own flag onNewVersion who is set to true in onUpgrade(). Then I do the check in onOpen(). Are there any other methods?

@override
onUpgrade(...) {
    onNewVersion = true;
    ...
}

@override
onOpen(...) {
    if (onNewVersion == FALSE)
        triggerAction();
}

Thanks.

Answer 1

Check onUpgrade

Called when the database needs to be upgraded. The implementation should use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 5) {
         db.execSQL("ALTER_TABLE_STATEMENT");
    }
    if (oldVersion < 6) {
         db.execSQL("ALTER_TABLE_STATEMENT2");
    }
}

Answer 2

You can set a flag inside your onUpgrade method of your SQLiteOpenHelper.

If the flag is not set by the time you get your reference to the OpenHelper then the database was already current and did not need updating.

Answer 3

Are there any other methods?

Yes, I personally use a sort of pseudo/wanted/desired schema and in short compare this against what actually exists columns, tables, indexes being created(tables indexes)/added(columns) as required.

As such I don't use versions and onUpgrade is empty.

for example these are the core methods in my SQLiteOpenhelper subclass :-

    @Override
    public void onCreate(SQLiteDatabase db) {
        usable = this.onExpand(db,false);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldversion, int newversion) {
    }

    public boolean onExpand(SQLiteDatabase db, boolean buildandexpand) {
        boolean rv = true;
        if (db == null) {
            db = instance.getWritableDatabase();
        }
        // Is the Database definition valid to use?
        if (DBConstants.cardonsier.isDBDatabaseUsable()) {
            ArrayList<String> buildsql = DBConstants.cardonsier.generateDBBuildSQL(db);
            // Anything to build
            if (!buildsql.isEmpty()) {
                // YES so build the Database
                DBConstants.cardonsier.actionDBBuildSQL(db);
            }
            if (buildandexpand) {
                ArrayList<String> altersql = DBConstants.cardonsier.generateDBAlterSQL(db);
                if (!altersql.isEmpty()) {
                    DBConstants.cardonsier.actionDBAlterSQL(db);
                }
            }
        }
        else {
            rv = false;
        }
        return rv;
    }

DBConstants.cardoniser refers to the DBDatabase object defined as :-

    static final DBDatabase cardonsier = new DBDatabase(DATABASENAME,
            new ArrayList<>(Arrays.asList(
                    DBCardsTableConstants.CARDS,
                    DBUsertableConstants.USERS,
                    DBCardUserLinkTableConstants.CARDUSERLINKS,
                    DBPreftableConstants.PREFS,
                    DBPayeestableContants.PAYEES,
                    DBCategoriestableConstants.CATEGORIES,
                    DBCardCategoryLinkTableConstants.CARDCATEGORYLINKS,
                    DBCardPayeeLinkTableConstants.CARDPAYEELINKS,
                    DBTransactionsTableConstants.TRANSACTIONS,
                    DBCardTypesTableConstants.CARDTYPES
            ))
    );

i.e. the list of DBTable objects, they including DBColumn objects e.g :-

static final DBTable CARDS = new DBTable(CARDS_TABLE,
        new ArrayList<>(Arrays.asList(
                CARDID,
                CARDOWNER,
                CARDNAME,
                CARDTYPEREF,
                CARDNUMBER,
                CARDCVVCODE,
                CARDPIN,
                CARDNOTES,
                CARDEXPIRYDATE,
                CARDNAMEONCARD
        ))
);

Columns being defined like :-

static final DBColumn CARDID = new DBColumn(true);
static final DBColumn CARDOWNER = new DBColumn(
        CARDOWNER_COL,
        SQLINTEGER,
        false,
        "0"
);
static final DBColumn CARDNAME = new DBColumn(
        CARDNAME_COL,
        SQLTEXT,
        false,
        ""
);
static final DBColumn CARDTYPEREF = new DBColumn(
        CARDTYPEREF_COL,
        SQLINTEGER,
        false,
        "0"
);

Note! the first column CARDID, uses a shortcut constructor for standard _id columns.

To add a new column it's simply a matter of defining the DBColumn and including the DBColumn in the DBTable definition. The onExpand method will add the column via, in the case of a new column, the DBDatabase's actionDBAlterSQL method. New tables require the above plus the inclusion of the table in the DBDatabase definition and are applied by onExpand via the actionDBBuildSQL method.

onExpand is also called when the App(s) is started. However, with true being passed so that the actionDBAlterSQL method is called.

As an example adding :-

private static final String CARDCOLOUR_COL = "cardcolour";
..........
static final DBColumn CARDCOLOUR = new DBColumn(
        CARDCOLOUR_COL,
        SQLINTEGER,
        false,
        Long.toString(0x00_00_00_00_00L)
        // Flag, alpha, red, green, blue
        // Flag used to inidcate if colour has been set
);

and then adding CARDCOLOUR to the DBTable definition as per :-

static final DBTable CARDS = new DBTable(CARDS_TABLE,
        new ArrayList<>(Arrays.asList(
                CARDID,
                CARDOWNER,
                CARDNAME,
                CARDTYPEREF,
                CARDNUMBER,
                CARDCVVCODE,
                CARDPIN,
                CARDNOTES,
                CARDEXPIRYDATE,
                CARDNAMEONCARD,
                CARDCOLOUR     //<<<<<<<<<<
        ))
);

Results in (note logging exists when developing) :-

09-22 08:30:26.802 2713-2713/? D/DBEXPAND: Expanding Database Schema Usability=true
09-22 08:30:26.803 2713-2713/? D/DBEXPAND: Build SQL is as follows:-
09-22 08:30:26.803 2713-2713/? D/DBEXPAND: ALTERSQL is as folows:-
09-22 08:30:26.807 2713-2713/? D/DBEXPAND: ALterSQL Line=    ALTER TABLE cards ADD COLUMN cardcolour INTEGER  DEFAULT 0  ;

The resultant already populated table then looks like :-

Using ALTER TABLE with a DEFAULT value (4th parameter of DBColumn (1st is column name, 2nd Type, 3rd true to include in primary index)) populates the new column for existing rows.