Android Database Schema Testing

Should I really do this?

Just before all the hype about MVP, RxJava and Firebase, testing was a hot topic in the Android community. While separating framework from application logic is mostly the solution to go, sometimes there are cases when only framework can provide the data we need.

To continue last topic about preparing database for tests, today I want to concentrate on a not so usual problem:

How would I test database structure?

I need to be honest with you, I am not really sure if there is a big value in doing that, but as a curious creature, I always want to check what is possible to achieve.

Android does not provide much information about table structures. SQLite, on the other hand, gives us PRAGMA Statements:

The PRAGMA statement is an SQL extension specific to SQLite and used to modify the operation of the SQLite library or to query the SQLite library for internal (non-table) data.

There is a long list of PRAGMA Statements for SQLite (not all of them are available on each Android versions).

Table Info PRAGMA (PRAGMA table_info) statement lets us query data related to table structure. Following piece of documentation, describes what is possible to get out of the table_info pragma:

This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The “pk” column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.

PRAGMA Statement, like any other sql statement, can be executed as a query on database. Example of output, for tbl_wallet table, looks like following:

sqlite> pragma table_info(“tbl_wallet”);
0|uuid|TEXT|0||1
1|name|TEXT|1||0

Table_info PRAGMA Statement returns data in format: position| name | type | Nullable | Default value | Primary Key.

As we can see, that first column, UUID, is a Primary Key without default value which allows storing TEXT values.

Similarly, name column, which allows TEXT values, is not a Primary Key and cannot have null values, neither has a default value.

To create this structure, following sql command was used. Notice, that uuid column, was not marked as a NOT NULL, which is easily accepted by SQLite databases (how to deal with it, will be described with following articles).

CREATE TABLE tbl_wallet(uuid TEXT PRIMARY KEY, name TEXT NOT NULL);

As said before, PRAGMA Statements can be executed as a query on database. By using SQLiteDatabase::rawQuery(pragmaStatement) method we get access to data listed above.

Following code retrieves only column names. Except name, possible options are:

  • type, gives type of column (one of INTEGER, REAL, TEXT, BLOB, NULL)
  • pk, whether or not column is Primary Key
  • notnull, whether field can contain null values

Testing is trivial again. Following test checks if table tbl_wallet contains all necessary columns.

@Test
public void walletsTableHasAllColumns() {
assertThat(listColumnNames(db, "tbl_wallet"))
.containsExactly("uuid", "name");
}
// Implementation of PRAGMA table_info for column names
List<String> listColumnNames(SQLiteDatabase db, String table) {
Cursor c = null;
try {
c = db.rawQuery("pragma table_info(" + table + ");", null);

List<String> columns = new ArrayList<>();
while (c != null && c.moveToNext()) {
columns.add(c.getString(c.getColumnIndex("name")));
}

c.close();
return columns;
} catch (Exception ignored) {
return Collections.emptyList();
}
}

Similarly, we can follow with other properties. TableDescription is wrapper around those informations. For more details, check following gist.

@Test
public void walletTableHasCorrectUuidColumn() {
TableDescription uuidColumn = getColumnDetails(db, "tbl_wallet", "uuid");

assertThat(uuidColumn.name).isEqualTo("uuid");
assertThat(uuidColumn.type).isEqualTo("TEXT");
assertThat(uuidColumn.isNotNull).isTrue();
assertThat(uuidColumn.isPk).isTrue();
}

@Test
public void walletTableHasCorrectNameColumn() {
TableDescription nameColumn = getColumnDetailsOrNull(db, "tbl_wallet", "name");
    assertThat(nameColumn.name).isEqualTo("name");
assertThat(nameColumn.type).isEqualTo("TEXT");
assertThat(nameColumn.isNotNull).isTrue();
assertThat(nameColumn.isPk).isFalse();
}

Check out documentation about PRAGMA Statements, to find out more use cases to cover with tests. Some useful examples could be:

  • user_version
The user_version pragma will to get or set the value of the user-version integer at offset 60 in the database header.
@Test
public void currentDatabaseVersion() {
assertThat(getCurrentDatabaseVersion(db))
.isEqualTo(42);
}
// Implementation of PRAGMA user_version
int getCurrentDatabaseVersion(SQLiteDatabase db) {
Cursor c = db.rawQuery("pragma user_version;", null);
return c.moveToNext() ? c.getInt(0) : -1;
}
  • foreign_key_list
This pragma returns one row for each foreign key constraint created by a REFERENCES clause in the CREATE TABLE statement of table “table-name”.
@Test
public void currentDatabaseVersion() {
assertThat(getFks(db, "tbl_transaction"))
.isEqualTo(Pair.create("wallet", "uuid");
}
// Implementation of PRAGMA foreign_key_list
Pair<String, String> getCurrentDatabaseVersion(SQLiteDatabase db, String table) {
Cursor c = db.rawQuery("pragma foreign_key_list(" + table + ");", null);

if (!c.moveToNext()) {
return null;
}

// c[2] -> Referenced table
// c[3] -> key
// c[4] -> Referenced key
return Pair.create(c.getString(2), c.getString(4));
}

Note that some of PRAGMA Statements can be not available. Different versions of Android bounds different versions of SQLite database (check Android docs).