Android and SQLLite - getting data onto the device

The previous two posts have focused on getting data onto an SQLLite database from an XML file. The next step is actually loading the data onto an android device. I've run across a blog entry which discusses this exact problem at http://tinyurl.com/mvtwqm. There's another blog entry which refers to the first one at http://tinyurl.com/42tlcxm, which changes it to apply to databases (raw files) greater then 1 mb. That post also mentions a StackOverflow entry at http://tinyurl.com/6c9njto.

Let's check if my file is over one mb - each record is probably around than 100 bytes, and there are 8 or 9k entries - call it 10k x 100, hmm, it'll be close. How do look at the physical file itself? The ".databases" command in sqlite3 doesn't show my jlpt database. If I go to RazorSQL, I don't see where the physical file is. But, mac's finder to the rescue - there is 664 KB jlpt file in my home directory, dated yesterday evening. That's got to be it.

The SO entry discusses two options - package the database file in your raw file, then copy it with code, or run SQL statement to insert all your data. Seeing as the second option will take quite some time, I'll go with the first. It appears that it's still going to take a while, which is a concern; but it's only on the first time running the app. Also, you're duplicating your data; but I think that would be the case either way, because the insert statements would contain the data.

So, given the TDD concept of doing the simplest possible thing first, my goal is now to simply implement the code in the ReignDesign blog at http://tinyurl.com/mvtwqm to create a database on my Android device.

Here's the first, section, with my comments between /* */.

"Most all of the Android examples and tutorials out there assume you want to create and populate your database at runtime and not to use and access an independent, preloaded database with your Android application.

The method I'm going to show you takes your own SQLite database file from the "assets" folder and copies into the system database path of your application so the SQLiteDatabase API can open and access it normally.

1. Preparing the SQLite database file.

Assuming you already have your sqlite database created, we need to do some modifications to it.
If you don't have a sqlite manager I recommend you to download the opensource SQLite Database Browser available for Win/Linux/Mac.

/* I'm using RazorSQL for now */

Open your database and add a new table called "android_metadata", you can execute the following SQL statement to do it:

CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US')

/* done */

Now insert a single row with the text 'en_US' in the "android_metadata" table:

INSERT INTO "android_metadata" VALUES ('en_US')

/* done */

Then, it is necessary to rename the primary id field of your tables to "_id" so Android will know where to bind the id field of your tables.
You can easily do this with SQLite Database Browser by pressing the edit table button Edit Table, then selecting the table you want to edit and finally selecting the field you want to rename

After renaming the id field of all your data tables to "_id" and adding the "android_metadata" table, your database it's ready to be used in your Android application."

/* actually, I don't have an ID field. I'll need to insert it and populate it. No, actually I do. Somewhere along the line, possibly in the insert to the database, a "row_id" was added, probably by the sql generated during the import of the csv. So, I will just rename this to _id. Well, not so fast. The row_id field shows up in the edit table, but doesn't show up as a header on a select statement. Will renaming internally mess something up, if I can even do it? Should I add I separate column? It seems safer to me to create a separate column. Not much extra space will be taken up, and I can always delete the one of the two once I know more about what's going on.


Ok, I added the column as an integer, taking the default length. Now I need to populate it. Actually, according to this link, http://www.sqlite.org/autoinc.html, if I create the column as "integer primary key", it becomes an alias for row_id, which it turns it is automatically added in sqllite. ALTER TABLE all_words ADD "_id3" INTEGER PRIMARY KEY doesn't work, however, on the message that you can't add a primary key column.

Hmm...unfortunately, I added column "_id2" and forgot to add the PRIMARY KEY, so now I'm stuck with this field. Unfortunately, sqllite doesn't have the drop column feature. Ok, there is a solution in the faq:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

Well, I don't want code the create table statement. Well, first let's see if there's a way I can copy from the row_id field to the _id field. A quick google shows this:

"UPDATE Table1 SET Column2 = Column1"

So, let's try: UPDATE all_words SET _id = rowid


Ok, that worked. So, let's rename the table, then recreate the table via import, then copy the data from the current table.

Ok, the import csv create this.

CREATE TABLE all_words
(level INTEGER,
number INTEGER,
kanji TEXT(50),
hiragana TEXT(100),
english TEXT(100),
"_id" INTEGER NOT NULL,
PRIMARY KEY ("_id"))

Now, I just need to copy from the old table to the new....

INSERT INTO all_words SELECT level,number,kanji,hiragana,english, _id FROM all_words_to_drop;

It seems to have worked. select count(1) from all_words shows 8449 records are there, and select * from all_words shows me my fields.

Now let's drop the backup table. DROP TABLE all_words_to_drop and we're done.

Whew. Well, that one innocuous instruction, to rename the primary field consumed about an hours worth of work, partially because I didn't have one to start out, and also because of the need to recreate the table due to erroneously adding a column. I'm going to take a break and pick it up again on my next post.

*/