Tuning your SQLite with SQLDelight & SQLBrite - Mkhytar Mkhoian

Transcript

1. Mkhytar Mkhoian Lalafo
2. The problem static final String CREATE_CATEGORIES_TABLE = "create table " + Category.TABLE + "(" + COLUMN_ID + " integer primary key autoincrement, " + Category.COL_NAME + " text, " + Category.COL_COLOR + " integer, " + Category.COL_CREATE_BY_USER + " integer DEFAULT 0 " + ");"; cursor.getString(cursor.getColumnIndexOrThrow(columnName)); ContentValues values = new ContentValues(); values.put(COL_NAME, name);
3. The problem private static String LIST_QUERY(String selection) { String query = "SELECT *, COUNT(i." + ListItem.COL_STATUS + ") " + List.COL_SIZE + ", SUM(i." + ListItem.COL_STATUS + ") " + List.COL_BOUGHT_COUNT + " FROM " + List.TABLE + " s" + " LEFT OUTER JOIN " + ListItem.TABLE + " i" + " ON s." + List.COL_ID + " = i." + ListItem.COL_PARENT_LIST_ID; if (selection == null) { return query + " GROUP BY s." + List.COL_ID; } return query + " WHERE " + selection + " GROUP BY s." + List.COL_ID; }
4. Features: • RxJava • Immutability • Type Safety • Compiler Errors • SQLite features
5. Features: • RxJava - SQLBrite • Immutability • Type Safety • Compiler Errors • SQLite features
6. Setup SQLBrite SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message -> Log.d("Database", message)) // Optional .queryTransformer(queryTransformer) // Optional .build(); SQLiteOpenHelper openHelper = new DBHelper(context); BriteDatabase db = sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io());
7. Setup SQLBrite SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message -> Log.d("Database", message)) // Optional .queryTransformer(queryTransformer) // Optional .build(); SQLiteOpenHelper openHelper = new DBHelper(context); BriteDatabase db = sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io());
8. Example Observable<SqlBrite.Query> users = db.createQuery("users", "SELECT * FROM users"); users.subscribe(new Action1<SqlBrite.Query>() { @Override public void call(SqlBrite.Query query) { Cursor cursor = query.run(); // parse data... } });
9. Example Observable<SqlBrite.Query> users = db.createQuery("users", "SELECT * FROM users"); users.subscribe(new Action1<SqlBrite.Query>() { @Override public void call(SqlBrite.Query query) { Cursor cursor = query.run(); // parse data... } }); db.createQuery("users", "SELECT * FROM users") .mapToList(Users.MAPPER) .observeOn(AndroidSchedulers.mainThread()) .subscribe();
10. How does it works under the hood DatabaseQuery query = new DatabaseQuery(tableFilter, sql, args); final Observable<Query> queryObservable = triggers .filter(tableFilter) // Only trigger on tables we care about. .map(query) // DatabaseQuery maps to itself to save an allocation. .onBackpressureLatest() // Guard against uncontrollable frequency of upstream emissions. .startWith(query) .observeOn(scheduler) .compose(queryTransformer) // Apply the user's query transformer. .onBackpressureLatest() // Guard against uncontrollable frequency of scheduler executions. .doOnSubscribe(ensureNotInTransaction); return new QueryObservable(new Observable.OnSubscribe<Query>() { @Override public void call(Subscriber<? super Query> subscriber) { queryObservable.unsafeSubscribe(subscriber); } });
11. How does it works under the hood DatabaseQuery query = new DatabaseQuery(tableFilter, sql, args); final Observable<Query> queryObservable = triggers .filter(tableFilter) // Only trigger on tables we care about. .map(query) // DatabaseQuery maps to itself to save an allocation. .onBackpressureLatest() // Guard against uncontrollable frequency of upstream emissions. .startWith(query) .observeOn(scheduler) .compose(queryTransformer) // Apply the user's query transformer. .onBackpressureLatest() // Guard against uncontrollable frequency of scheduler executions. .doOnSubscribe(ensureNotInTransaction); return new QueryObservable(new Observable.OnSubscribe<Query>() { @Override public void call(Subscriber<? super Query> subscriber) { queryObservable.unsafeSubscribe(subscriber); } });
12. How does it works under the hood DatabaseQuery query = new DatabaseQuery(tableFilter, sql, args); final Observable<Query> queryObservable = triggers .filter(tableFilter) // Only trigger on tables we care about. .map(query) // DatabaseQuery maps to itself to save an allocation. .onBackpressureLatest() // Guard against uncontrollable frequency of upstream emissions. .startWith(query) .observeOn(scheduler) .compose(queryTransformer) // Apply the user's query transformer. .onBackpressureLatest() // Guard against uncontrollable frequency of scheduler executions. .doOnSubscribe(ensureNotInTransaction); return new QueryObservable(new Observable.OnSubscribe<Query>() { @Override public void call(Subscriber<? super Query> subscriber) { queryObservable.unsafeSubscribe(subscriber); } });
13. How does it works under the hood DatabaseQuery query = new DatabaseQuery(tableFilter, sql, args); final Observable<Query> queryObservable = triggers .filter(tableFilter) // Only trigger on tables we care about. .map(query) // DatabaseQuery maps to itself to save an allocation. .onBackpressureLatest() // Guard against uncontrollable frequency of upstream emissions. .startWith(query) .observeOn(scheduler) .compose(queryTransformer) // Apply the user's query transformer. .onBackpressureLatest() // Guard against uncontrollable frequency of scheduler executions. .doOnSubscribe(ensureNotInTransaction); return new QueryObservable(new Observable.OnSubscribe<Query>() { @Override public void call(Subscriber<? super Query> subscriber) { queryObservable.unsafeSubscribe(subscriber); } });
14. How does it works under the hood DatabaseQuery query = new DatabaseQuery(tableFilter, sql, args); final Observable<Query> queryObservable = triggers .filter(tableFilter) // Only trigger on tables we care about. .map(query) // DatabaseQuery maps to itself to save an allocation. .onBackpressureLatest() // Guard against uncontrollable frequency of upstream emissions. .startWith(query) .observeOn(scheduler) .compose(queryTransformer) // Apply the user's query transformer. .onBackpressureLatest() // Guard against uncontrollable frequency of scheduler executions. .doOnSubscribe(ensureNotInTransaction); return new QueryObservable(new Observable.OnSubscribe<Query>() { @Override public void call(Subscriber<? super Query> subscriber) { queryObservable.unsafeSubscribe(subscriber); } });
15. How does it works under the hood DatabaseQuery query = new DatabaseQuery(tableFilter, sql, args); final Observable<Query> queryObservable = triggers .filter(tableFilter) // Only trigger on tables we care about. .map(query) // DatabaseQuery maps to itself to save an allocation. .onBackpressureLatest() // Guard against uncontrollable frequency of upstream emissions. .startWith(query) .observeOn(scheduler) .compose(queryTransformer) // Apply the user's query transformer. .onBackpressureLatest() // Guard against uncontrollable frequency of scheduler executions. .doOnSubscribe(ensureNotInTransaction); return new QueryObservable(new Observable.OnSubscribe<Query>() { @Override public void call(Subscriber<? super Query> subscriber) { queryObservable.unsafeSubscribe(subscriber); } });
16. How does it works under the hood DatabaseQuery query = new DatabaseQuery(tableFilter, sql, args); final Observable<Query> queryObservable = triggers .filter(tableFilter) // Only trigger on tables we care about. .map(query) // DatabaseQuery maps to itself to save an allocation. .onBackpressureLatest() // Guard against uncontrollable frequency of upstream emissions. .startWith(query) .observeOn(scheduler) .compose(queryTransformer) // Apply the user's query transformer. .onBackpressureLatest() // Guard against uncontrollable frequency of scheduler executions. .doOnSubscribe(ensureNotInTransaction); return new QueryObservable(new Observable.OnSubscribe<Query>() { @Override public void call(Subscriber<? super Query> subscriber) { queryObservable.unsafeSubscribe(subscriber); } });
17. Use transactions to prevent large changes to the data from spamming your subscribers BriteDatabase.Transaction transaction = db.newTransaction(); try { db.insert("users", createUser("Mkhytar Mkhoian")); db.insert("users", createUser("Artem Dudinskyi")); db.insert("users", createUser("Andrii Rakhimov")); transaction.markSuccessful(); } finally { transaction.end(); }
18. SQLBrite also support observing a query on another app’s content provider BriteContentResolver resolver = sqlBrite.wrapContentProvider(contentResolver, Schedulers.io()); Observable<SqlBrite.Query> query = resolver.createQuery(/*...*/);
19. Philosophy SqlBrite’s only responsibility is to be a mechanism for coordinating and composing the notification of updates to tables such that you can update queries as soon as data changes.
20. Features: • RxJava - SQLBrite • Immutability • Type Safety • Compiler Errors • SQLite features
21. Features: • RxJava - SQLBrite • Immutability - AutoValue • Type Safety • Compiler Errors • SQLite features
22. Features: • RxJava - SQLBrite • Immutability - AutoValue • Type Safety - SQLDelight • Compiler Errors - SQLDelight • SQLite features - SQLDelight
24. Package structure
25. CREATE TABLE images ( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ad_id INTEGER NOT NULL, url TEXT, size TEXT, image_id TEXT ); saveImage: INSERT INTO images(ad_id, url, size, image_id) VALUES (?, ?, ?, ?); getAllImages: SELECT * FROM images; SQL statements
26. CREATE TABLE images ( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ad_id INTEGER NOT NULL, url TEXT, size TEXT, image_id TEXT ); saveImage: INSERT INTO images(ad_id, url, size, image_id) VALUES (?, ?, ?, ?); getAllImages: SELECT * FROM images; SQL statements
27. Java model @AutoValue public abstract class Image implements ImagesModel { public static final Factory<Image> FACTORY = new Factory<>(new Creator<Image>() { @Override public Image create(long _id, long ad_id, @Nullable String url, @Nullable String size, @Nullable String image_id) { return new AutoValue_Image(_id, ad_id, url, size, image_id); } }); }
28. Java model @AutoValue public abstract class Image implements ImagesModel { public static final Factory<Image> FACTORY = new Factory<>(new Creator<Image>() { @Override public Image create(long _id, long ad_id, @Nullable String url, @Nullable String size, @Nullable String image_id) { return new AutoValue_Image(_id, ad_id, url, size, image_id); } }); }
29. Java model @AutoValue public abstract class Image implements ImagesModel { public static final Factory<Image> FACTORY = new Factory<>(new Creator<Image>() { @Override public Image create(long _id, long ad_id, @Nullable String url, @Nullable String size, @Nullable String image_id) { return new AutoValue_Image(_id, ad_id, url, size, image_id); } }); }
30. Java model @AutoValue public abstract class Image implements ImagesModel { public static final Factory<Image> FACTORY = new Factory<>(new Creator<Image>() { @Override public Image create(long _id, long ad_id, @Nullable String url, @Nullable String size, @Nullable String image_id) { return new AutoValue_Image(_id, ad_id, url, size, image_id); } }); }
31. Java model @AutoValue public abstract class Image implements ImagesModel { public static final Factory<Image> FACTORY = new Factory<>(new Creator<Image>() { @Override public Image create(long _id, long ad_id, @Nullable String url, @Nullable String size, @Nullable String image_id) { return new AutoValue_Image(_id, ad_id, url, size, image_id); } }); }
32. Java model @AutoValue public abstract class Image implements ImagesModel { ... public static final RowMapper<Image> ALL_IMAGES_MAPPER = FACTORY.getAllImagesMapper(); public static final RowMapper<Image> IMG_BY_ID_MAPPER = FACTORY.getImagesByIdMapper(); }
33. Cursor mapper cursor.getString(cursor.getColumnIndexOrThrow(columnName));
34. Cursor mapper final class Mapper<T extends ImagesModel> implements RowMapper<T> { private final Factory<T> imagesModelFactory; public Mapper(Factory<T> imagesModelFactory) { this.imagesModelFactory = imagesModelFactory; } @Override public T map(@NonNull Cursor cursor) { return imagesModelFactory.creator.create( cursor.getLong(0), cursor.getLong(1), cursor.isNull(2) ? null : cursor.getString(2), cursor.isNull(3) ? null : cursor.getString(3), cursor.isNull(4) ? null : cursor.getString(4) ); } }
35. Example of insertion saveImage: INSERT INTO images(ad_id, url) VALUES (?, ?);
36. Example of insertion final class SaveImage extends SqlDelightCompiledStatement.Insert { public SaveImage(SQLiteDatabase database) { super("images", database.compileStatement("" + "INSERT INTO images(ad_id, url)\n" + "VALUES (?, ?)")); } public void bind(long ad_id, @Nullable String url) { program.bindLong(1, ad_id); if (url == null) { program.bindNull(2); } else { program.bindString(2, url); } } } saveImage: INSERT INTO images(ad_id, url) VALUES (?, ?);
37. Example of insertion saveImage: INSERT INTO images(ad_id, url) VALUES (?, ?); Image.SaveImage saveImage = new ImagesModel.SaveImage(db); saveImage.bind(10, "url"); saveImage.program.executeInsert();
38. Example of query getImagesById: SELECT * FROM images WHERE ad_id = ? ORDER BY _id ASC; public SqlDelightStatement getImagesById(long ad_id) { List<String> args = new ArrayList<String>(); StringBuilder query = new StringBuilder(); query.append("SELECT *\n" + "FROM images\n" + "WHERE ad_id = "); query.append(ad_id); query.append("\n" + "ORDER BY _id ASC"); return new SqlDelightStatement(query.toString(), args.toArray(new String[args.size()]), Collections.<String>singleton("images")); }
39. Example of query getImagesById: SELECT * FROM images WHERE ad_id = ? ORDER BY _id ASC; SqlDelightStatement query = Image.FACTORY.getImagesById(10); Cursor cursor = db.rawQuery(query.statement, query.args); Image.FACTORY.getImagesByIdMapper().map(cursor);
40. Sets of values also can be passed as an argument getImagesById: SELECT * FROM images WHERE ad_id IN ? or WHERE ad_id IN :param_name ORDER BY _id ASC;
41. Sets of values also can be passed as an argument getImagesById: SELECT * FROM images WHERE ad_id IN ? or WHERE ad_id IN :param_name ORDER BY _id ASC;
42. Sets of values also can be passed as an argument getImagesById: SELECT * FROM images WHERE ad_id IN ? or WHERE ad_id IN :param_name ORDER BY _id ASC;
43. Sets of values also can be passed as an argument getImagesById: SELECT * FROM images WHERE ad_id IN ? or WHERE ad_id IN :param_name ORDER BY _id ASC; SqlDelightStatement query = Image.FACTORY.getImagesById(new long[]{10, 20}); Cursor cursor = db.rawQuery(query.statement, query.args);
44. Query with join selectAll: SELECT * FROM ads JOIN images ON ads.ad_id = images.ad_id; interface SelectAllModel<T1 extends AdModel, T2 extends ImagesModel> { @NonNull T1 ads(); @NonNull T2 images(); }
45. Query with join @AutoValue public static abstract class AdWithImages implements SelectAllModel<Ad, Image> { } public static final RowMapper<AdWithImages> SELECT_ALL_MAPPER = FACTORY.selectAllMapper( new SelectAllCreator<Ad, Image, AdWithImages>() { @Override public AdWithImages create(@NonNull Ad ads, @NonNull Image images) { return new AutoValue_Ad_AdWithImages(ads, images); } }, Image.FACTORY);
46. Support imports like Java in .sq files import com.justplay1.sqldelight.models.Ad.Status; import java.util.Calendar; CREATE TABLE ads ( _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ad_id INTEGER NOT NULL UNIQUE ON CONFLICT IGNORE, title TEXT, description TEXT, price TEXT, status TEXT AS Status NOT NULL, create_time INTEGER AS Calendar NOT NULL, is_favorite INTEGER AS Boolean NOT NULL DEFAULT 0 );
47. Support imports like Java in .sq files public final class DateAdapter implements ColumnAdapter<Calendar, Long> { @Override public Long encode(@NonNull Calendar date) { return date.getTimeInMillis(); } @Override @NonNull public Calendar decode(Long data) { Calendar calendar = Calendar.getInstance(); calendar.setTimeInMillis(data); return calendar; } }
48. Support imports like Java in .sq files @AutoValue public abstract class Ad implements AdModel { public enum Status { ACTIVE, DEACTIVATED } static final DateAdapter DATE_ADAPTER = new DateAdapter(); static final EnumColumnAdapter<Status> STATUS_ADAPTER = EnumColumnAdapter.create(Status.class); public static final Factory<Ad> FACTORY = new Factory<>(new Creator<Ad>() { @Override public Ad create(long _id, long ad_id, @Nullable String title, @NonNull Status status, @NonNull Calendar create_time, boolean is_favorite) { return new AutoValue_Ad(_id, ad_id, title, status, create_time, is_favorite); } }, STATUS_ADAPTER, DATE_ADAPTER); }
49. Support imports like Java in .sq files @AutoValue public abstract class Ad implements AdModel { public enum Status { ACTIVE, DEACTIVATED } static final DateAdapter DATE_ADAPTER = new DateAdapter(); static final EnumColumnAdapter<Status> STATUS_ADAPTER = EnumColumnAdapter.create(Status.class); public static final Factory<Ad> FACTORY = new Factory<>(new Creator<Ad>() { @Override public Ad create(long _id, long ad_id, @Nullable String title, @NonNull Status status, @NonNull Calendar create_time, boolean is_favorite) { return new AutoValue_Ad(_id, ad_id, title, status, create_time, is_favorite); } }, STATUS_ADAPTER, DATE_ADAPTER); }
50. Support to pass any type into query from java CREATE TABLE ads ( status TEXT AS Status NOT NULL ); getAdsByStatus: SELECT * FROM ads WHERE status = ?; SqlDelightStatement query = Ad.FACTORY.getAdsByStatus(Ad.Status.ACTIVE); Cursor cursor = db.rawQuery(query.statement, query.args);
51. Support to pass any type into query from java CREATE TABLE ads ( status TEXT AS Status NOT NULL ); getAdsByStatus: SELECT * FROM ads WHERE status = ?; SqlDelightStatement query = Ad.FACTORY.getAdsByStatus(Ad.Status.ACTIVE); Cursor cursor = db.rawQuery(query.statement, query.args);
52. SQLDelight with SQLBrite private BriteDatabase db; private Observable<Ad> getAd() { SqlDelightStatement statement = Ad.FACTORY.getAdsByStatus(Ad.Status.ACTIVE); return db.createQuery(statement.tables, statement.statement, statement.args) .mapToOne(Ad.ADS_BY_STATUS_MAPPER::map); }
53. • Syntax highlighting • Refactoring/Find usages • Code autocompletion • Generate Model files after edits • Right click to copy as valid SQLite • Compiler errors in IDE click through to file Intellij IDEA plugin
54. Conclusion • RxJava, Immutability, Type Safety, Compiler Errors, SQLite features • Shared language • Tight coupling with views (MVVM) • Easier code review • Confidence writing SQL
55. Links SQLDelight - https://github.com/square/sqldelight SQLBrite - https://github.com/square/sqlbrite
56. facebook.com/mkhytar.mkhoian mkhytar.mkhoian@gmail.com