Database (part one)

It’s an open secret that almost every Android application stores something in a database. Nearly always it is SQLite.
In my practice I’ve tried many ways to work with sqlite in Android: it was some ORM and active-record libraries, ContentProvider’s, even some wrappers for native .so libraries. But with none of them I was satisfied.

This post is outdated! I understand a some problems related to described approach. So I’m going to publish second part with corrections when I figure out how to do it right.

What’s wrong with them?

The main reason why I don’t tolerate ORM is that it brings some dirt into app architecture: it makes me think in data domain instead of object domain. Also it’s hard to maintain in future: object behaviour changes cause database schema changes and vice versa.
I know that I’m not alone, many people took a stand against ORM:

ContentProvider with CursorLoader looks better, but it brings raw data directly to UI layer that is not really good.

It’s important to remember that sqlite database is nothing more than just a data store, this is not an object store and there is not always possible to map table columns to object state.

Android framework API

Android framework provides us with a database layer API.
Let’s look at the official implementation guide: that is not a code that I’d like to see in my applications. Just look at this:

You may find it helpful to create a companion class, known as a contract class

A contract class is a container for constants that define names for URIs, tables, and columns. The contract class allows you to use the same constants across all the other classes in the same package.

It’s a public class inflated with public constants only; a class without state and behaviour.

Later they suggest implementing a helper class to maintain a database. I’d prefer to avoid this kind of classes, but it’s a necessary evil - this class is an entry point for android database object.

To get something from database via Android framework you should query a cursor:

final SQLiteDatabase db = sqliteHelper.getReadableDatabase();
final Cursor cursor = db.query(
  distinct,
  table,
  columns,
  selection,
  selectionArgs,
  groupBy,
  having,
  orderBy,
  limit
);

Now it’s more interesting. Method query returns us Cursor object.

Pros and cons:
Cursor is a good abstraction for database record in my opinion.
Also it can be decorated with a built-in class CursorWrapper.

On the other hand query method is overcomplicated: we have to pass loads of arguments to build a query.
Besides if we don’t need to group results or set a limit we are passing null-s as arguments - this is not developer-friendly.
Common query may look like:

db.query(
  false,
  "people",
  new String[]{"id", "name"},
  null,
  null,
  null,
  null,
  null,
  null
);

In summary:

  • we have to use SQLiteOpenHelper
  • Cursor object is OK
  • Everything else can be object oriented

How do I see it

Let’s try to implement a really simple object with a state, fetched from a database
E.g. a person with a name:

public interface Person {

  long id();

  String name();
}

If I need to access person by id I’d like to see this code:

final Person person = new PersonById(database, id);

To find people by name I’d use this:

final List<Person> people = new PeopleWithName(
  database,
  name
);

And so on.

I prefer to use a new object for a new need. For me it’s looks much better than repository pattern because of semantics: repository is an aggregation of procedures to fetch data from the source when PersonById is just a person with specified id.
Also it’s not clear what is behavior of repository is. Repository behaves like a data-labourer: it can save data, fetch by id, delete if needed, update rows in table, fetch collection of data, ordered by name, group by name and repair the primus many other things. Honestly, I think that main reason why repository pattern is so popular is that it’s easy to deal with ORM frameworks via repository, like a mediator between objects and data structures.

Implementation

First of all we need to describe a person for a Cursor:

final class CursorPerson implements Person {

  private final long id;
  private final String name;

  CursorPerson(final Cursor cur) {
    this.id = cur.getInt(cur.getColumnIndex("id"));
    this.name = cur.getString(cur.getColumnIndex("name"));
  }

  public int id() {
    return this.id;
  }

  public String name() {
    return this.name;
  }
}

Now we have a Cursor adapter, what’s next?
It’s time to make a query to database and fetch person’s state from cursor with this adapter.
Our PersonById can be implemented as decorator for person:

public interface Person {

  ...

  /**
   * Default decorator.
   */
  abstract class Wrap implements Person {

    private final Person origin;

    protected Wrap(final Person origin) {
      this.origin = origin;
    }

    public final long id() {
      return this.origin.id();
    }

    public final String name() {
      return this.origin.name();
    }
  }
}

final class PersonById extends Person.Wrap {
  PersonById(
    final SQLiteDatabase db
    final long id
  ) {
    super(
      new CursorPerson(
        db.query(
          // Many args
        )
      )
    );
  }
}

Now it’s almost good. The only problem with this class is db.query() method: we need to pass unused arguments and copy-paste duplicated code (like table, columns) over similar objects. To solve this issue I’ve started new project: QueryLite. It makes all query work more simple and fluent. Also it allows me to encapsulate sqlite tables and queries with decorators.

Sharing public constants (like table name, columns) is not a good idea if we want to go OO way, so let’s define few objects to encapsulate them and compose more complex objects later:

final class PeopleTable extends Table.Wrap {

  private static final String NAME = "people";

  PeopleTable(SQLiteDatabse db) {
    super(
      new TableSqlite(
        db,
        PeopleTable.NAME
      )
    );
  }
}

final class PeopleQuery extends Query.Wrap {
  PeopleQuery(SQLiteDatabse db) {
    super(
      new Select(
        "id",
        "name"
      ).from(
        new PeopleTable(db)
      )
    );
  }
}

And finally make PersonById as decorator for CursorPerson:

public final class PersonById extends Person.Wrap {
  public PersonById(SQLiteDatabase db, long id) {
    super(
      new CursorPerson(
        new CursorQuery(
          new PeopleQuery(db)
            .where("id = ?", id)
        )
      )
    );
  }
}

And similar for PeopleWithName:

public final class PeopleWithName extends ListWrap<Person> {
  public PeopleWithName(SQLiteDatabase db, String name) {
    super(
      new ListIterable(
        new CursorIterable(
          new CursorQuery(
            new PeopleQuery(db)
              .where(
                "name LIKE ?",
                String.format("%%%s%%", name)
              )
          ),
          CursorPerson::new
        )
      )
    );
  }
}

ListWrap is a List decorator here,
ListIterable is a List with Iterable as source,
CursorIterable - an Iterable with Cursor as source, and mapping as second argument.

Conclusion

This way we make objects with encapsulated state fetched from database as composition of decorators. It’s easy to maintain and refactor them:

  • Person object and person table are not interrelated: now they can be modified individually.
  • If Person object is changed or database schema is changed we don’t need to modify PersonById and PeopleWithName, only CursorPerson.
  • It’s very easy to change data fetching behaviour. E.g. if we want to make our PersonById lazy-loaded we can make LazyCursorPerson that will access cursor data in methods instead of constructor.
  • You should have no difficulty in changing object composition: e.g. to make our lazy-loaded PersonById cacheable we can wrap in with caching decorator:
     super(
       new CachedPerson(
         new LazyCursorPerson(
           new CursorQuery(db)
             .where("id = ?", id)
         )
       )
     );
    
Written on April 12, 2017