Reading SQL from file and SQLiteOpenHelper

Question

I need to include an existing SQLiteDatabase in my Android app and I also want the ability to download and install a new db. I did some research and my first working solution came from here. I didn't like that solution, for one thing, it assumes that the database will always be in a fixed path and other oddities.

So, rather than putting the existing database file in the assets, I exported the database to a SQL file, read it in, and then in the onCreate() method of my SQLiteOpenHelper, I call a new method updateDatabase with an open DataInputStream with the file data. I ran into a few issues, which I think I solved, but I'm sure I didn't think of all the issues, which are:

1. When SQLiteOpenHelper's onCreate method is called, the database has been created, it is open, and inTransaction() is true. As a result, if the imported sql file includes BEGIN TRANSACTION, an exception is thrown and if the sql String contains statements creating 'android_metadata' yet another exception. So, I added a simple search using String.contains() looking for these keywords, and set a boolean doExecute to false to avoid executing them. So a question is, is there a better SQL class or method to filter this, or even a better regexp method?

2. Similar issue with having unexpected line breaks in the SQL file. I read the file with readLine() and to look for line breaks, I simply use String.trim() on the line, then check for endsWith(";"). This puts some constraints on my input file, like not having multiple statements on a single line. So, is there a better way to pre-process SQL from a file?

Here's the code I use to create my db after I've gotten a DataInputStream from the assets resource or from a download:

    public boolean updateDatabase(DataInputStream inStream, SQLiteDatabase db, boolean doClear) throws Error {
    String sqlStatement = null;
    boolean result = true;
    boolean inOnCreate = true;
    boolean wasInTransaction;

    if(doClear) dropDatabase();

    // if called from onCreate() db is open and inTransaction, else getWritableDatabase()
    if(db == null) {
        inOnCreate = false;
        db = this.getWritableDatabase();
    }

    wasInTransaction = db.inTransaction();  // see NB below

    boolean doExecute;
    try {
        while ((sqlStatement = inStream.readLine()) != null) {
            // trim, so we can look for ';'
            sqlStatement.trim();
            if(!sqlStatement.endsWith(";")) {
                continue;   // line breaks in file, get whole statement
            }

            // NB - my file (exported from SQLite Database Browser starts with "BEGIN TRANSACTION;". 
            // executing this throws SQLiteException: cannot start a transaction within a transaction
            // According to SQLiteDatabase doc for beginTransaction(), "Transactions can be nested"
            // so this is a problem
            // but... possibly it is an "exclusive transaction" ?
            doExecute = true;
            if(wasInTransaction) {
                // don't execute BEGIN TRANSACTION; or COMMIT;
                if((sqlStatement.contains("BEGIN" ) || sqlStatement.contains("begin" )) &&
                        (sqlStatement. contains("TRANSACTION") || sqlStatement.contains("transaction" ))) {
                    doExecute = false;
                }
                if(sqlStatement.contains("COMMIT") || sqlStatement.contains("commit")) {
                    doExecute = false;
                }
            }   // inTransaction
            // this statement could be in older databases, but this scheme doesn't need, can't have it
            if(sqlStatement.contains("android_metadata")) {
                doExecute = false;
            }
            if(doExecute) {
                try {
                    db.execSQL(sqlStatement);
                } catch (SQLException e) {
                    throw(new Error("Error executing SQL " + sqlStatement));
                }   // try/catch
            }   // doExecute
        }   // while()
    } catch (IOException e) {
        result = false; // which won't matter if we throw 
        throw(new Error("Error reading  " + DB_SQL));
    } 

    if(!inOnCreate) {
        db.close();
    }

    return result;
}

Answer 1

Wouldn't want to have you cop out early on such ambitious and elegant implementations, but if you have your database already made and checked with a database browser and all, have you considered SQLite Asset Helper? If your main issue was being forced to use the asset folder, this method lib let's you use a file from any specified directory. Moreover, it allows for handling the raw .db file. Worth checking out.