Problems at the datawarehouse

So, I have been coding a lot lately. Specially last weekend. I would love to buy few more hours so I could be focusing for what I really like, but I just can’t afford not to go sleep. Otherwise I turn into useless zombie and my brains are needed in the day time too.

I have a simple screen to which I can draw the letters of a static word. Then I have some simple very ugly buttons so I can do some simple actions like check the word player is suggesting if it’s valid. In order to check the validity I was thinking to have a local database of words.

For some time I considered other options like if I should check the word by using some remote call to some (hopefully free) service in the internet. The problem with this solution is that when ever player is playing the network connection is required, this makes offline play impossible. Other thing I found out is that there is not that many free online services from which you could check your word against. Wiktionary could be one. Then there was some with fees.

Since I chose to go with the local database, I am going to need a database implementation. Of course for a simple word list function I could just have a file and create a good indexing system and fast search algorithm but since databases are invented I decided not to reinvent the wheel. I did some search in the internet and found out that SQLite is popular option and it seems that Android SDK has API accessing SQLite databases. Neat!

Downloading and “installing” the database was simple. But problems arouse when I had to create the database and have it part of my game.  Android requires some specific stuff to the tables that are created. One is ANDROID_METADATA table and special notation of id columns.

If you know some SQL then creation of the tables and columns is very easy.  This is what you need:

CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US');
INSERT INTO "android_metadata" VALUES ('en_US');

If you are going to use cursors for reading your tables then I suggest you create an id column for your tables. This id column should be named “_id” so that cursor will understand it (otherwise you might get an Exception). Here’s how you create it:

CREATE TABLE myexampletable (
description TEXT

All this SQL can be executed in SQLite console. Then you have your database created but empty. If you want some content you need to populate it, like a list of words 😉

Even though I thought I had it complete when deploying the database to virtual device sometimes when the database structure was changed I ended up in strange errors. Workaround for this problem was to delete the file from the virtual device before redeploying the game. This didn’t happen in the real Android device.

I am using currently SQLiteOpenHelper for reading the database and it works just great. It writes the database in the first run to Androids inner file system (from the application package) so the first run takes a bit longer depending on your DB size. Next thing I need is a word combination algorithm…


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s