How do I handle multiple database tables cleanly in Android?

Question

I'm trying to create an Android app that can handle a history of played games and their scores, but as I expand from the Notepad tutorial, this is turning into a rat's nest of repeated statements like this:

private static final String GAME_TABLE_CREATE =
"create table " + GAME_TABLE + " (_id integer primary key autoincrement, " 
+ "title text not null);";
private static final String PLAY_TABLE_CREATE =
    "create table " + PLAY_TABLE + " (_id integer primary key autoincrement, " 
    + "game_id integer, game_date date);";
private static final String PLAYER_TABLE_CREATE =
    "create table " + PLAYER_TABLE + " (_id integer primary key autoincrement, " 
    + "name text not null);";
private static final String SCORE_TABLE_CREATE =
    "create table " + SCORE_TABLE + " (_id integer primary key autoincrement, " 
    + "game_id int, player_id int, score int);";

   ...

public void onCreate(SQLiteDatabase db) {
db.execSQL(GAME_TABLE_CREATE);
db.execSQL(PLAY_TABLE_CREATE);
db.execSQL(PLAYER_TABLE_CREATE);
db.execSQL(SCORE_TABLE_CREATE);
}

This seems like a nightmare for readability and maintainability. Any advice on how to better manage multiple SQL tables, and turn these kinds of lists into nice clean loops? I was thinking about trying to do it via resource string-arrays, but haven't been able to figure out how to manage that.

score 0 · Answer 1 · edited May 23 '17 at 11:56

0

I haven't had to deal with this in the Android world but have in the past in the Web world. I'd said for starters you may want to look at an ORM for managing you statements. There seems to be some pointers here that might help.

edited May 23 '17 at 11:56

Community

1
1

answered May 14 '11 at 02:09

dbryson

6,047
2
20
20

score 0 · Answer 2 · edited May 23 '17 at 11:56

I manage this by turning off formatting in Eclipse (you must be using Helios or higher) by using the statement //formatter: off (and turning it on after). This SO question will give you more information on that.

I also place all my initial SQL statements (CREATE, Triggers, etc.) inside a static String array. This way all your statements can be run with this code in your onCreate:

public static final String[] SQL_CREATE_TABLES = new String[] { TABLE_1, TABLE_2};

@Override
public void onCreate(SQLiteDatabase db) {
     for (String sql : SQL_CREATE_TABLES)
         db.execSQL(sql);
}

The SQL statements could also be placed in the strings.xml resource file, but I don't think that would help maintain readability or maintainability.

score 0 · Answer 3 · answered Aug 23 '11 at 07:50

I ran into this same issue when first starting out, managing the create tables as statements inside the Android app became really cumbersome, especially trying to update the schemas as development progressed. What worked really well for me was simply placing a copy of my database in the assets folder and then copy the database to the data folder. The first time your DBAdapter starts you can check if the database already exists, if it doesn't you can then copy your database over. This is also handy where tables need to be prepopulated with data, or pre loaded with data for testing. It made life so much easier to manage the database schemas in a normal/GUI based database tool. There are lots of Android copy database code examples, a simple Google search should set you in the right direction. Hope this helps, Cheers,

How do I handle multiple database tables cleanly in Android?

3 Answers3