Tuning your SQLite with SQLDelight & SQLBrite: Part 1
In Lalafo for Android we use SQLite database, because it’s fast, flexible and it’s SQL. However, if you want to use SQLite, then you have to deal with Cursor , CursorValues and write your SQL statement in Java code.
This a lot of boilerplate code! Also, an SQL statement in Java code is difficult to read, maintain, and easy to break. Thanks to the guys from Square, we have a solution to the problems described above.
SQLBrite
SQLBrite requires knowledge in RxJava, because it’s based on it. If you’re not familiar with RxJava, then first read about Rx.
SQLBrite is a lightweight wrapper around
SQLiteOpenHelperandContentResolverwhich introduces reactive stream semantics to queries.
This means - that we can make our database observable. So, whenever we change a database table’s data set (insert, update or delete row) we get notified that the data set has been changed. There’s no magic, under the hood it’s implementation of Event bus pattern, which is based on RxJava.
Setup SQLBrite
or you can use ContentProvider
All queries will be executed by Scheduler that you pass in the method by default.
Query
The BriteDatabase.createQuery() is the wrap method of SQLiteDatabase.rawQuery(). It adds an additional parameter, table name(s). In Event bus pattern, you subscribe to some event, in SQLBrite you subscribe to a particular table(s). That’s why you need to specify the table(s). When you change table’s data set, SQLBrite will recognize in what table it was changed, then check if this table has subscribers and simply rerun the SQL query for each subscriber and call onNext() with the new query result. Note that each query Observable will remain subscribed (until unsubscribed).
Insert, Update, Delete
BriteDatabas wrap all SQLiteDatabase methods you need to insert, update and delete data.
As you can see the first parameter is also table name (you can specify only one table name). When the data was successfully changed BriteDatabase send a trigger to subscribers. Note if you want that your subscribers get the notification about data changed, you need to change your data through BriteDatabase, not SQLiteDatabase.
SQLBrite also provides a wrapper for the transaction. It does all that do SQLiteDatabase transaction API. However, the main idea of this wrapper is to prevent sending triggers to subscribers when you change data in a transaction. The trigger will be sent only after the transaction has ended.
Under the hood
How do trigger works?
As I said above, everything based on the Event bus pattern. The core of SQLBrite is PublishSubject<Set<String>> triggers. It only emits table name(s). When you call a method to change a data set in the table, the table name (that you passed as a parameter) posts to the PublishSubject.
How do query and subscriptions work?
All stuff happens in the code below.
tableFilter —the set of tables that we pass when creating query observable and when the trigger happens we filter only those tables that we have subscribed;
.map(query) — map observable to query type. Every time, when we get notified, the observable returns the same query object;
.startWith(query) — emit query when we subscribe to observable;
.observeOn(scheduler) — this is a scheduler that you pass when setup SQLBrite;
.compose(queryTransformer) — this transformer you also can set when doing a setup in SQLBrite. It’s useful when you need custom logging;
.doOnSubscribe(this.ensureNotInTransaction); — we cannot subscribe to an observable in a transaction;
Also SQLBrite provide operators to map Cursor
mapToList() — for a list of rows (if your query result is empty use defaultIfEmpty() or switchIfEmpty() operators to emit default value if need);
mapToOne() — for single row;
mapToOneOrDefault() — if the result is empty (0 rows) emit default value;
mapToOptional() — like mapToOne(), but return Optional<T> ;
Thanks for reading. Stay tuned! Next time we’ll talk about type safety, compiler errors for SQL statement and other cool features of SQLDelight.
