SquiDB: a SQLite layer for Android - Jonathan Koren, Yahoo!

As presented in DroidCon Tel Aviv 2015
http://il.droidcon.com

1. SquiDB: Android SQLite Management
DroidCon Tel Aviv 2015

2. What is SquiDB?

3. SQLite with Android
string theory

4. SQLiteDatabase
public Cursor query(String table, String[] columns, !
String selection, String[] selectionArgs,!
String groupBy, String having, String orderBy,!
String limit)!
public Cursor rawQuery(String sql, String[] sqlArgs)!
!
public long insert(String table, String!
nullColumnHack, ContentValues values)!
public int update(String table, ContentValues values,!
String selection, String[] selectionArgs)!
public int delete(String table, String selection,!
String[] selectionArgs)!
!
public void execSQL(String sql, String[] sqlArgs)!

5. SQLiteOpenHelper

6. SQLiteDatabase

7. SQLite with SquiDB
bring back the fun

8. Models
! Models generated at compile time
› Define model using minimalist “spec” class
› <type>Property for each member field (column)
› Getters and setters for each property
! Internally represented by ContentValues
› Models are Parcelable!
! Multiple sets of values
› Tracks deltas; update only saves changed (written) values
› Data source ordering

9. Model Specs

10. Sample Model

11. FilmDatabase
film_roles!
!
_id (PK)!
film_id (FK)!
actor_id (FK)!
role!
actors!
!
_id (PK)!
name!
birthdate!
gender!
!
films!
!
_id (PK)!
title!
description!
photo_url!
year!
rating!
is_favorite!
index films.year!
index films.rating!

12. Sample Database

13. DatabaseDao
! Controls access to database
› CRUD operations
› Transaction management
! fetch(), persist(), and delete() single records
! update() and deleteWhere() for multiple records
! count() for number of matching rows
! query() for cursors

14. DatabaseDao

15. DatabaseDao

16. Queries
! Object-oriented builder classes
! Supports almost the entire SELECT grammar
› Joins, nested queries, compound operators
› Functions and other complex expressions
! Automatic placeholders and argument binding
! Reusable
› Cached compilation
› Use atomics to change arguments between executions

17. Queries
! Select Fields, Properties, and Functions
› Query.select(Film.PROPERTIES); // all Film properties!
› Query.select(Film.ID, Film.TITLE); // just these ones!
› Query.select(Function.max(Film.RATING)); // function!
› Query.select() // select * -- until you selectMore()!
!
! Predicates formed using Criterions
› Film.IS_FAVORITE.isTrue() // from a Property!
› Film.YEAR.between(2010, 2015).and(Film.RATING.gte(4.0))!
› Criterion.all, Criterion.none // pre-packaged!
› Criterion.not(Film.TITLE.like("L%"))!

18. Queries

19. Queries

20. Queries

21. SquidCursor
! Parameterized CursorWrapper
! Read values using Property
› String title = cursor.get(Film.TITLE);!
! Populate models easily
› Film film = new Film(cursor); // must be at valid position!
› film.readPropertiesFromCursor (cursor);!
! Model reusability
› Film film = new Film();!
SquidCursor<Film> c = dao.query(Film.class, query);!
for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {!
film.readPropertiesFromCursor (c);!
doSomethingWithFilm(film);!
}!

22. Other useful bits
! SquidCursorAdapter
› Base list adapter backed by a SquidCursor
! SquidCursorLoader
› Loader for querying DatabaseDao in the background
! UriNotifier
› Send URI notifications when write operations happen

23. Advanced features
! Other SQLite statements
› Inserts, updates, deletes, triggers
! Models based on SQLite Views
! Utilities for building ContentProviders
› Public contract vs. private schema
› Protect against SQL injection
! Code generator plugins
› Use other Java types in your model specs

24. Questions?
answers
?

25. Get started with SquiDB
Source:
github.com/yahoo/squidb

Wiki:
github.com/yahoo/squidb/wiki