CODEWITHSUNDEEP
androidsqliteschema
Codium
Codium

Reputation: 3240

Storing application database schema

In example apps database is in most cases single table, so db schema is stored in static variable.

Storing large schema in seperate file is more friendly for me.

How can I do that? I thought about using resources (R.strings.db_schema) but probably there is a better way.

Could somebody give me any advice?

Upvotes: 2

Views: 528

Answers (2)

Guillaume
Guillaume

Reputation: 22822

The way I do is to have a class per table, named after the table with "Table" suffix (e.g. PlayerTable or EventTable).

These classes contain all the static variable for the table name and all the field names, and they also contain two static methods:

public static void onCreate(SQLiteDatabase database)
public static void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion)

So that my SQLiteOpenHelper can just call all of them, without having hundreds of static variables with all the fields and create queries. E.g:

@Override
public void onCreate(SQLiteDatabase database) {
    PlayerTable.onCreate(database);
    EventTables.onCreate(database);
    ..... any other table you have .....
}

This class is then injected into all my data access objects (select / update / insert queries). For them I have dedicated classes that contain all my methods, by functionality (e.g. EventHandlingDAO for all the queries that deal with event handling).

And finally, theses DAO are injected into the activities that need them, when needed.

EDIT: A few more details about my code:

My main objects are the DAO (data access objects), in which I have methods like:

// in EventHandlingDAO:
public void addEvent(Event event) {
    SQLiteDatabase database = databaseHelper.getWritableDatabase();
    try {
        database.execSQL("INSERT INTO " + EventTable.EVENT_TABLE_NAME + " (...."); // list of fields and values
    } finally {
        database.close();
    }
}

public List<Event> getAllEvents() {
    final List<Event> result = new ArrayList<Event>();
    SQLiteDatabase database = databaseHelper.getReadableDatabase();
    try {
        final Cursor cursor = database.rawQuery("SELECT " + EventTable.KEY_NAME + ", " + EventTable.KEY_DATE_AS_STRING + " FROM " + EventTable.TABLE_NAME, null); 
        cursor.moveToFirst();
        // ... rest of the logic, that iterates over the cursor, creates Event objects from the cursor columns and add them to the result list
        return result;

    } finally {
        database.close();
    }
}

So in that DAO, I have my databaseHelper object, which instanciates my class that extends SQLiteOpenHelper with the methods I talked about above.

And of course, I have interfaces to all my DAO, so that I can inject a Stub or mocked implementation in my tests (or experiment with different implementations if I want to try another solution based on SharedPreference for example)

And the code for my PlayerTable table:

public static void onCreate(SQLiteDatabase database) {
    database.execSQL(TABLE_CREATE); // TABLE_CREATE is my "CREATE TABLE..." query
}

public static void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
    // A bit blunt, that destroys the data unfortunately, I'll think about doing something more clever later ;)
    database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(database);
}

Upvotes: 1

Kaediil
Kaediil

Reputation: 5535

You could put the schema data in a raw file under res/raw. Then you can just load and parse that file the first time.

Upvotes: 1

Related Questions