Make squidb use a prepopulated database

[iambibhas]

This is not really an issue. I want to provide a prepopulated database with my app and want squidb to use it. What would be the best way to achieve that? Anyway I can override the db creation part of squidb and provide a copying method instead?

[sbosley]

There's no built-in hook in squidb for this, but it's still achievable. In your SquidDatabase subclass, let's say you've implemented getName() to return the value "mydatabase.db". What you would do is simply on first app startup copy the prepopulated database file to the path returned by context.getDatabasePath("mydatabase.db"). As long as you do this step before calling any SquidDatabase methods, squidb will see that the database file already exists and skip the database creation flow, simply opening the existing file.

There are a couple caveats to beware of with this approach. Under the hood, android adds a few hidden tables and columns to the database containing things like version info. If those tables and columns aren't present in the prepopulated database file, I don't know exactly how android would handle that -- it might not notice they're missing until much later, so things like the versioning and upgrade flows could break down. I would highly recommend creating your prepopulated database programatically, using a dummy android app implemented with squidb, so that you can be sure that this prepopulated master db was created with all the hidden tables and metadata that android would use when setting up a database.

The second caveat is that you would still have to write your squidb model specs to correspond exactly to the schema of your prepopulated database. If you use the above suggestion to make a prepopulated database using a dummy app you would presumably have already done this step as a part of that process, which is another reason I'd recommend that approach. Unfortunately squidb can't do this step for you; it would be kind of a cool feature if it could but it would involve writing what would probably need to be a pretty robust SQL parser, so it's not something that's on our radar right now.

Hope that helps!

[sbosley]

Actually one other alternative -- if it's only a small amount of data that needs to be prepopulated (say, a few rows in a few tables), you could simply override the onTablesCreated() hook and write code there to insert the data you want to be prepopulated. This is a much simpler approach, it just doesn't scale as well if the db to be prepopulated is huge.

[iambibhas]

Thanks. I've already created the db programmatically and using squidb to access it right now, so I have those internally created tables. The fact is that I've filled up this db with a lot of data and I'll need to ship it with the db now. So what you said is achievable. I've copied the db out of the device and put it in the assets directory. I'll simply just copy it inside getName().

I understand why it's out of squidb's plan right now. But maybe you could provide a verbosely named variable, like PREPOPULATED_DB_PATH and just simply copy it internally just after calling getName() maybe. Well, I don't know if squidb should deal with this problem, as your actual task is to communicate with the db. But this would be a welcome addition. But yes, there are unknown variables at play, like you mentioned about the tables Android create in the db. So, this is just a suggestion.

[sbosley]

Great, sounds like you're on the right track. Don't do the actual copying of the file in getName() though -- getName() may be called at times other than first db creation. I'd recommend doing the copying in Application.onCreate(), and then setting a boolean flag in the shared prefs or something so that it can only ever occur once.

Someday I'll dig into the android framework a bit more to figure out exactly what additional metadata is created to see whether or not there are any feasible things we could do to make this easier, but for now, glad that this will work for you!

[iambibhas]

Oh, thanks for mentioning that.

Just wanted to mention this for reference, in my db, there is just one extra table called android_metadata that has just one TEXT column locale. Right now there is just one record - en_US.

[sbosley]

Cool, thanks for that. This stackoverflow post jogged my memory that the database version is stored using the user_version pragma, so it would be elsewhere in the database file, rather than a table. That will also be helpful if we ever decide to implement hooks for this someday.

[iambibhas]

@sbosley another question, how would I go about creating a clean new fresh db from inside the SquidDatabase subclass(just in case the copying fails)? I read somewhere that calling getReadableDatabase() creates a new db if it doesn't exist. But it doesn't seem to be available inside this class.

[iambibhas]

I found the getDatabase() function. Can I just do this?

acquireNonExclusiveLock();
try {
    getDatabase();
} finally {
    releaseNonExclusiveLock();
}

[jdkoren]

@iambibhas Calling getDatabase() should be all you have to do. Android's SQLiteOpenHelper (which we encapsulate) handles creating a new database if the file with the proper name/path cannot be found. Just make sure that file does not exist if your copying fails.

[jdkoren]

@iambibhas Sorry, let me amend my previous comment: the first call to getDatabase() should handle creating a database if one does not exist yet, and getDatabase() is called in every method that reads from or writes to the database, so you shouldn't have to do anything special besides using your instance of SquidDatabase normally.