SQLITE - setting the version of a new database

Question

I have used this example to create a database that I copy over on a fresh install

http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/

Problem I have now is I have now done some updates to the database and I asummed I also do this updates to the master file within the build so the users always get the most update version on new install

I have a different class that deals with all the db calls query statements.

I have set this line

private static final int DATABASE_VERSION = 5;

On a fresh install now the database is copied over correctly but when the query class calls the databasehelper again the onupgrade() method is called and tries to update to the latest version and the app crashes as it is trying to do upgrades that cannot be done

I was under the understanding that the following set the database version on fresh installs or is this wrong. If so how do I set the database version for new installs

public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

just for completness here is a sample of the onupgrade()

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

        dbUpgrade = new DatabaseUpgrades(context);

        Log.d(DEBUG_TAG, "Calling onupgrade db");
        Log.d(DEBUG_TAG + " : " + DatabaseHelper.class.getName(),
                "Upgrading database from version " + oldVersion + " to "
                    + newVersion);

        if (oldVersion == 1) {
            Log.d(DEBUG_TAG, "Updating to Version 2");
            dbUpgrade.upgradeDB2(db);
            oldVersion++;
        }
}

Question what version is the new database set to on a fresh install and how can you overwrite it if it is not version 5

Thanks

Answer 1

This is correct:

public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, DATABASE_VERSION);
        /* ... */
}

I put below getWritableDatabase() source code and as you see it won't call onUpgrade() unless the version in the db file is not equal to current new version which you pass in the constructor(version != mNewVersion line). So either your database file is not overwritten or version number in your new database is wrong. Please check it.

/**
 * Create and/or open a database that will be used for reading and writing.
 * Once opened successfully, the database is cached, so you can call this
 * method every time you need to write to the database.  Make sure to call
 * {@link #close} when you no longer need it.
 *
 * <p>Errors such as bad permissions or a full disk may cause this operation
 * to fail, but future attempts may succeed if the problem is fixed.</p>
 *
 * @throws SQLiteException if the database cannot be opened for writing
 * @return a read/write database object valid until {@link #close} is called
 */
public synchronized SQLiteDatabase getWritableDatabase() {
    if (mDatabase != null && mDatabase.isOpen() && !mDatabase.isReadOnly()) {
        return mDatabase;  // The database is already open for business
    }

    if (mIsInitializing) {
        throw new IllegalStateException("getWritableDatabase called recursively");
    }

    // If we have a read-only database open, someone could be using it
    // (though they shouldn't), which would cause a lock to be held on
    // the file, and our attempts to open the database read-write would
    // fail waiting for the file lock.  To prevent that, we acquire the
    // lock on the read-only database, which shuts out other users.

    boolean success = false;
    SQLiteDatabase db = null;
    if (mDatabase != null) mDatabase.lock();
    try {
        mIsInitializing = true;
        if (mName == null) {
            db = SQLiteDatabase.create(null);
        } else {
            db = mContext.openOrCreateDatabase(mName, 0, mFactory);
        }

        int version = db.getVersion();
        if (version != mNewVersion) {
            db.beginTransaction();
            try {
                if (version == 0) {
                    onCreate(db);
                } else {
                    onUpgrade(db, version, mNewVersion);
                }
                db.setVersion(mNewVersion);
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }
        }

        onOpen(db);
        success = true;
        return db;
    } finally {
        mIsInitializing = false;
        if (success) {
            if (mDatabase != null) {
                try { mDatabase.close(); } catch (Exception e) { }
                mDatabase.unlock();
            }
            mDatabase = db;
        } else {
            if (mDatabase != null) mDatabase.unlock();
            if (db != null) db.close();
        }
    }
}

EDIT
You can check version in your new database file with the following query:

PRAGMA user_version;

It should return 5 in your case.