Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Don't fear SQL: A better way to store and handl...

Don't fear SQL: A better way to store and handle data with SQLBrite & SQLDelight (GDG Berlin Android Jun-2017)

Leandro Favarin

June 28, 2017
Tweet

More Decks by Leandro Favarin

Other Decks in Programming

Transcript

  1. Don’t fear SQL A better way to store and handle

    data with SQLBrite & SQLDelight Leandro Favarin
  2. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful • Efficient
  3. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful • Efficient • Fast
  4. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful • Efficient • Fast • Stable
  5. SQLite in Android – pros • Default choice for persisting

    relational data • Powerful • Efficient • Fast • Stable • Flexible
  6. SQLite in Android – cons • It’s hard! • Cursors

    • Verboseness • No type-safety
  7. What is ORM? Technique for converting data between incompatible type

    systems in OO languages … basically: Converting between complex data types to "primitives"
  8. What is ORM? Technique for converting data between incompatible type

    systems in OO languages … basically: Converting between complex data types to "primitives" Examples: greenDAO, OrmLite, Realm
  9. ORM – cons • New APIs to be learned •

    Hide many powerful SQL capabilities
  10. ORM – cons • New APIs to be learned •

    Hide many powerful SQL capabilities • Decreased performance
  11. ORM – cons • New APIs to be learned •

    Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers
  12. ORM – cons • New APIs to be learned •

    Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers • Lock-in effect
  13. SQLDelight • Generates Java models from your SQL `CREATE TABLE`

    statements • Type-safe APIs to read & write the rows of your tables
  14. SQLDelight • Generates Java models from your SQL `CREATE TABLE`

    statements • Type-safe APIs to read & write the rows of your tables • Keep your SQL statements together, organized, and easy to access from Java
  15. SQLDelight – IntelliJ plugin Provides language-level features for .sq files,

    including: • Syntax highlighting • Refactoring/Find usages
  16. SQLDelight – IntelliJ plugin Provides language-level features for .sq files,

    including: • Syntax highlighting • Refactoring/Find usages • Code autocompletion
  17. SQLDelight – IntelliJ plugin Provides language-level features for .sq files,

    including: • Syntax highlighting • Refactoring/Find usages • Code autocompletion • Generate Model files after edits
  18. SQLDelight – IntelliJ plugin Provides language-level features for .sq files,

    including: • Syntax highlighting • Refactoring/Find usages • Code autocompletion • Generate Model files after edits • Right click to copy as valid SQLite
  19. SQLDelight – IntelliJ plugin Provides language-level features for .sq files,

    including: • 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
  20. SQLDelight – keep in mind • The file structure of

    your project is crucial for this to work
  21. SQLDelight – keep in mind • The file structure of

    your project is crucial for this to work • SQLDelight files must be in a folder matching the file structure of the java project
  22. SQLDelight – example app/src/main/ - java/ - com/example/project/models/ - Book.java

    ... - sqldelight/ - com/example/project/models/ - Book.sq ...
  23. SQLDelight – Book.sq CREATE TABLE book ( _id INTEGER NOT

    NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT NOT NULL, num_chapters INTEGER ); selectAll: SELECT * FROM book; insertRow: INSERT INTO book(title, author, num_chapters) VALUES (?, ?, ?);
  24. SQLDelight – implementation @AutoValue public abstract class Book implements BookModel

    { public static final Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new); }
  25. SQLDelight – implementation @AutoValue public abstract class Book implements BookModel

    { public static final Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new); public static final Mapper<Book> ROW_MAPPER = FACTORY.selectAllMapper(); }
  26. SQLDelight – … generates: public interface BookModel { String TABLE_NAME

    = "book"; String _ID = "_id"; String TITLE = "title"; String AUTHOR = "author"; String NUM_CHAPTERS = "num_chapters"; String CREATE_TABLE = "" + "CREATE TABLE book (\n" + " _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" + " title TEXT NOT NULL,\n" + " author TEXT NOT NULL,\n" + " num_chapters INTEGER\n" + ")"; long _id(); @NonNull String title(); @NonNull String author(); @Nullable Long num_chapters(); ... }
  27. SQLDelight – … generates: class Mapper<T extends BookModel> implements RowMapper<T>

    { @Override public T map(@NonNull Cursor cursor) { return bookModelFactory.creator.create( cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getLong(3) ); } }
  28. SQLDelight – … generates: public SqlDelightStatement selectAll() { return new

    SqlDelightStatement("" + "SELECT *\n" + "FROM book", new String[0], Collections.<String>singleton("book")); }
  29. SQLDelight – … generates: class InsertRow extends SqlDelightCompiledStatement.Insert { public

    InsertRow(SQLiteDatabase database) { super("book", database.compileStatement("" + "INSERT INTO book(title, author, num_chapters)\n" + "VALUES (?, ?, ?)")); } public void bind(@NonNull String title, @NonNull String author, long num_chapters) { program.bindString(1, title); program.bindString(2, author); program.bindLong(3, num_chapters); } }
  30. SQLDelight – BookRepo.java List<Book> getAllBooks(SQLiteDatabase db) { SqlDelightStatement query =

    Book.FACTORY.selectAll(); try (Cursor c = db.rawQuery(query.statement, query.args)) { } }
  31. SQLDelight – BookRepo.java List<Book> getAllBooks(SQLiteDatabase db) { SqlDelightStatement query =

    Book.FACTORY.selectAll(); try (Cursor c = db.rawQuery(query.statement, query.args)) { List<Book> result = new ArrayList <>(c.getCount()); while (c.moveToNext()) { result.add(Book.ROW_MAPPER.map(c)); } return result; } }
  32. SQLDelight – projections, joins Each SELECT statement will have: •

    an interface • mapper • method on the factory to create a new instance of the mapper
  33. SQLDelight – types CREATE TABLE some_types ( some_long INTEGER, --

    Stored as INTEGER, retrieved as Long some_double REAL, -- Stored as REAL, retrieved as Double some_string TEXT, -- Stored as TEXT, retrieved as String some_blob BLOB, -- Stored as BLOB, retrieved as byte[] some_int INTEGER AS Integer, -- Stored as INTEGER, retrieved as Integer some_short INTEGER AS Short, -- Stored as INTEGER, retrieved as Short some_float REAL AS Float -- Stored as REAL, retrieved as Float );
  34. SQLDelight – booleans CREATE TABLE some_types ( some_long INTEGER, --

    Stored as INTEGER, retrieved as Long some_double REAL, -- Stored as REAL, retrieved as Double some_string TEXT, -- Stored as TEXT, retrieved as String some_blob BLOB, -- Stored as BLOB, retrieved as byte[] some_int INTEGER AS Integer, -- Stored as INTEGER, retrieved as Integer some_short INTEGER AS Short, -- Stored as INTEGER, retrieved as Short some_float REAL AS Float, -- Stored as REAL, retrieved as Float some_boolean INTEGER AS Boolean DEFAULT 0 -- Stored as INTEGER, retrieved as Boolean );
  35. SQLDelight – custom classes import org.threeten.bp.ZonedDateTime; CREATE TABLE book (

    _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT, publishedAt TEXT AS ZonedDateTime );
  36. SQLDelight – custom classes class ZonedDateTimeColumnAdapter implements ColumnAdapter<ZonedDateTime, String> {

    static final DateTimeFormatter FORMATTER = DateTimeFormatter.ISO_OFFSET_DATE_TIME; @NonNull @Override public ZonedDateTime decode(String databaseValue) { return FORMATTER.parse(databaseValue, ZonedDateTime.FROM); } @Override public String encode(@NonNull ZonedDateTime value) { return FORMATTER.format(value); } }
  37. SQLDelight – enums @AutoValue public abstract class Book implements BookModel

    { public enum Genre { ROMANCE, ADVENTURE, SELF_HELP, BIOGRAPHY, FICTION, DRAMA } ColumnAdapter<Genre, String> GENRE_ADAPTER = EnumColumnAdapter.create(Genre.class); Factory<Book> FACTORY = new Factory <>(new Creator <>() {}, GENRE_ADAPTER); }
  38. SQLDelight – views • Same treatment in generated code as

    tables with their own model interface
  39. SQLDelight – views CREATE TABLE book ( _id INTEGER NOT

    NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT NOT NULL ); listing_view: CREATE VIEW listing AS SELECT coalesce(title,'') || coalesce(author,'') AS identification FROM book; selectListing: SELECT * FROM listing;
  40. SQLDelight – views @AutoValue public abstract class Book implements BookModel

    { Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new); RowMapper<Listing> ROW_MAPPER = FACTORY.selectListingMapper(AutoValue_Book_Listing ::new); @AutoValue static abstract class Listing implements ListingModel { } }
  41. SQLDelight – kotlin CREATE TABLE book ( _id INTEGER NOT

    NULL PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT, edition INTEGER AS Integer NOT NULL ); selectAll: SELECT * FROM book; insertRow: INSERT INTO book(title, author, edition) VALUES (?, ?, ?);
  42. SQLDelight – kotlin data class Book( val _id: Long, val

    title: String, val author: String?, val edition: Int ) : BookModel { companion object { val FACTORY = BookModel.Factory( ::Book) val SELECT_ALL_MAPPER = FACTORY.selectAllMapper() } override fun _id() = _id override fun title() = title override fun author() = author override fun edition() = edition }
  43. SQLDelight – migrations • Least supported part of SQLDelight •

    Likely to be implemented as .sqm files • Solution: write strings in the open helper
  44. SQLBrite • Wrapper around SQLiteOpenHelper and ContentResolver which introduces reactive

    stream semantics to SQL operations • Updates to the specified table(s) will trigger additional notifications for as long as you remain subscribed to the publisher
  45. SQLBrite • Wrapper around SQLiteOpenHelper and ContentResolver which introduces reactive

    stream semantics to SQL operations • Updates to the specified table(s) will trigger additional notifications for as long as you remain subscribed to the publisher • SQLBrite's coordinates and composes update notifications to tables such that you can update queries as soon as data changes
  46. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder()
 .logger(message ->

    Timber.tag("Database").v(message))
 .queryTransformer()
 .build();
  47. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message ->

    Timber.tag("Database").v(message)) .build(); BriteDatabase db = sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io());
  48. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message ->

    Timber.tag("Database").v(message)) .build(); BriteDatabase db = sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io()); db.setLoggingEnabled(boolean);
  49. SQLBrite – content resolvers SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message

    -> Timber.tag("Database").v(message)) .build(); BriteContentResolver db = sqlBrite.wrapContentProvider( contentResolver, Schedulers.io()); db.setLoggingEnabled(boolean);
  50. SQLBrite – powerful logging QUERY (0ms) tables: [book] sql: SELECT

    * FROM book WHERE title = ?1 AND author = ?2 args: ["1984","Fitzgerald"] EXECUTE SQLiteProgram: INSERT INTO book(title, author) VALUES(?, ?)
  51. SQLBrite – BookRepo.java Observable<List<Book >> getAllBooks(BriteDatabase db) { QueryObservable books

    = db.createQuery("book", "SELECT * FROM book"); books.subscribe(query -> { Cursor run = query.run(); // parse data }); }
  52. SQLBrite – BookRepo.java Observable<List<Book >> getAllBooks(BriteDatabase db) { QueryObservable books

    = db.createQuery("book", "SELECT * FROM book"); AtomicInteger queries = new AtomicInteger(); books.subscribe(query -> queries.getAndIncrement()); System.out.println("Queries: " + queries.get()); // Prints 1 db.insert("book", createBook("1984", "George Orwell")); db.insert("book", createBook("The Great Gatsby", "Fitzgerald")); System.out.println("Queries: " + queries.get()); // Prints 3 }
  53. SQLBrite – BookRepo.java Observable<List<Book >> getAllBooks(BriteDatabase db) { QueryObservable books

    = db.createQuery("book", "SELECT * FROM book"); AtomicInteger queries = new AtomicInteger(); books.subscribe(query -> queries.getAndIncrement()); System.out.println("Queries: " + queries.get()); // Prints 1 BriteDatabase.Transaction transaction = db.newTransaction(); try { db.insert("book", createBook("1984", "George Orwell")); db.insert("book", createBook("The Great Gatsby", "Fitzgerald")); transaction.markSuccessful(); } finally { transaction.end(); } System.out.println("Queries: " + queries.get()); // Prints 2 }
  54. SQLDelight & SQLBrite CREATE TABLE book (
 _id INTEGER NOT

    NULL PRIMARY KEY AUTOINCREMENT,
 title TEXT NOT NULL,
 author TEXT NOT NULL
 );
 
 selectAll:
 SELECT *
 FROM book;
 
 insertRow:
 INSERT INTO book(title, author)
 VALUES (?, ?);
 
 deleteByTitle:
 DELETE FROM book
 WHERE title = ?;
  55. SQLDelight & SQLBrite @AutoValue public abstract class Book implements BookModel

    {
 Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new);
 
 Mapper<Book> ROW_MAPPER = FACTORY.selectAllMapper();
 }
  56. SQLDelight & SQLBrite Observable<List<Book >> getAllBooksStream() {
 SqlDelightStatement query =

    Book.FACTORY.selectAll();
 return db.createQuery(query.tables, query.statement, query.args)
 .mapToList(Book.ROW_MAPPER ::map);
 }
  57. SQLDelight & SQLBrite public class BookRepository {
 private final BriteDatabase

    db;
 private final Book.DeleteByTitle deleteByTitle;
 
 public BookRepository(BriteDatabase db) {
 this.db = db;
 insertRow = new Book.InsertRow(db.getWritableDatabase());
 }
 
 public void insert(Book book) {
 insertRow.bind(book.title(), book.author());
 long result = db.executeInsert(insertRow.table, insertRow.program);
 }
 }
  58. SQLDelight & SQLBrite public class BookRepository {
 private final BriteDatabase

    db;
 private final Book.InsertRow insertRow;
 private final Book.DeleteByTitle deleteByTitle;
 
 public BookRepository(BriteDatabase db) {
 this.db = db;
 insertRow = new Book.InsertRow(db.getWritableDatabase());
 deleteByTitle = new Book.DeleteByTitle(db.getWritableDatabase());
 }
 
 public void insert(Book book) {
 insertRow.bind(book.title(), book.author());
 long result = db.executeInsert(insertRow.table, insertRow.program);
 }
 
 public void delete(Book book) {
 deleteByTitle.bind(book.title());
 long result = db.executeUpdateDelete(deleteByTitle.table, deleteByTitle.program);
 }
 }
  59. Review – pros • Type-safe marshalling & unmarshalling • Compile-time

    SQL validation • SQL autocompletion • Immutability
  60. Review – pros • Type-safe marshalling & unmarshalling • Compile-time

    SQL validation • SQL autocompletion • Immutability • All SQLite feature-set at your disposal
  61. Review – pros • Type-safe marshalling & unmarshalling • Compile-time

    SQL validation • SQL autocompletion • Immutability • All SQLite feature-set at your disposal • Real-time UI updates
  62. Review – cons (Jun 2017) • SQLBrite latest release only

    works with RxJava 1 (RxJava 2 support is imminent, available on SNAPSHOT)
  63. Review – cons (Jun 2017) • SQLBrite latest release only

    works with RxJava 1 (RxJava 2 support is imminent, available on SNAPSHOT) • Migrations are mostly done manually