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

Transcript

1. Don’t fear SQL A better way to store and handle data with SQLBrite & SQLDelight Leandro Favarin
2. SQLite
3. SQLite in Android
4. SQLite in Android – pros • Default choice for persisting relational data
5. SQLite in Android – pros • Default choice for persisting relational data • Powerful
6. SQLite in Android – pros • Default choice for persisting relational data • Powerful • Efficient
7. SQLite in Android – pros • Default choice for persisting relational data • Powerful • Efficient • Fast
8. SQLite in Android – pros • Default choice for persisting relational data • Powerful • Efficient • Fast • Stable
9. SQLite in Android – pros • Default choice for persisting relational data • Powerful • Efficient • Fast • Stable • Flexible
10. SQLite in Android – cons • It’s hard!
11. SQLite in Android – cons • It’s hard! • Cursors
12. SQLite in Android – cons • It’s hard! • Cursors • Verboseness
13. SQLite in Android – cons • It’s hard! • Cursors • Verboseness • No type-safety
14. ORM libraries
15. What is ORM? Technique for converting data between incompatible type systems in OO languages
16. What is ORM? Technique for converting data between incompatible type systems in OO languages … basically: Converting between complex data types to "primitives"
17. 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
18. ORM – cons • New APIs to be learned
19. ORM – cons • New APIs to be learned • Hide many powerful SQL capabilities
20. ORM – cons • New APIs to be learned • Hide many powerful SQL capabilities • Decreased performance
21. ORM – cons • New APIs to be learned • Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers
22. ORM – cons • New APIs to be learned • Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers • Lock-in effect
23. SQLDelight
24. SQLDelight • Generates Java models from your SQL `CREATE TABLE` statements
25. SQLDelight • Generates Java models from your SQL `CREATE TABLE` statements • Type-safe APIs to read & write the rows of your tables
26. 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
27. SQLDelight – IntelliJ plugin Provides language-level features for .sq files, including:
28. SQLDelight – IntelliJ plugin Provides language-level features for .sq files, including: • Syntax highlighting
29. SQLDelight – IntelliJ plugin Provides language-level features for .sq files, including: • Syntax highlighting • Refactoring/Find usages
30. SQLDelight – IntelliJ plugin Provides language-level features for .sq files, including: • Syntax highlighting • Refactoring/Find usages • Code autocompletion
31. SQLDelight – IntelliJ plugin Provides language-level features for .sq files, including: • Syntax highlighting • Refactoring/Find usages • Code autocompletion • Generate Model files after edits
32. 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
33. 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
34. SQLDelight – keep in mind • The file structure of your project is crucial for this to work
35. 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
36. SQLDelight – example app/src/main/ - java/ - com/example/project/models/ - Book.java …
37. SQLDelight – example app/src/main/ - java/ - com/example/project/models/ - Book.java ... - sqldelight/ - com/example/project/models/ - Book.sq ...
38. 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 (?, ?, ?);
39. SQLDelight – implementation @AutoValue public abstract class Book implements BookModel { }
40. SQLDelight – implementation @AutoValue public abstract class Book implements BookModel { public static final Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new); }
41. 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(); }
42. 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(); ... }
43. 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) ); } }
44. SQLDelight – … generates: public SqlDelightStatement selectAll() { return new SqlDelightStatement("" + "SELECT *\n" + "FROM book", new String[0], Collections.<String>singleton("book")); }
45. 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); } }
46. SQLDelight – BookRepo.java List<Book> getAllBooks(SQLiteDatabase db) { SqlDelightStatement query = Book.FACTORY.selectAll(); try (Cursor c = db.rawQuery(query.statement, query.args)) { } }
47. 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; } }
48. SQLDelight – projections, joins Each SELECT statement will have:
49. SQLDelight – projections, joins Each SELECT statement will have: • an interface
50. SQLDelight – projections, joins Each SELECT statement will have: • an interface • mapper
51. SQLDelight – projections, joins Each SELECT statement will have: • an interface • mapper • method on the factory to create a new instance of the mapper
52. SQLDelight – types • Identical to SQLite column definitions
53. SQLDelight – types • Identical to SQLite column definitions • Plus extra column constraint for Java type
54. 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 );
55. 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 );
56. 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 );
57. 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); } }
58. SQLDelight – enums • Includes a ColumnAdapter for storing an enum as TEXT
59. 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); }
60. SQLDelight – enums import com.leandrofavarin.books.entities.Book; CREATE TABLE book ( genre TEXT AS Book.Genre );
61. SQLDelight – views • Same treatment in generated code as tables with their own model interface
62. 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;
63. 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 { } }
64. Kotlin?
65. SQLDelight – kotlin • Mostly just works!
66. SQLDelight – kotlin • … mostly just works! • Have to redundantly declare getters
67. 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 (?, ?, ?);
68. 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 }
69. SQLDelight – migrations • Least supported part of SQLDelight
70. SQLDelight – migrations • Least supported part of SQLDelight • Likely to be implemented as .sqm files
71. SQLDelight – migrations • Least supported part of SQLDelight • Likely to be implemented as .sqm files • Solution: write strings in the open helper
72. SQLBrite
73. SQLBrite • Wrapper around SQLiteOpenHelper which introduces reactive stream semantics to SQL operations
74. SQLBrite • Wrapper around SQLiteOpenHelper and ContentResolver which introduces reactive stream semantics to SQL operations
75. 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
76. 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
77. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .build();
78. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message -> Timber.tag("Database").v(message)) .build();
79. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder()
 .logger(message -> Timber.tag("Database").v(message))
 .queryTransformer()
 .build();
80. SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .logger(message -> Timber.tag("Database").v(message)) .build(); BriteDatabase db = sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io());
81. 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);
82. 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);
83. 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(?, ?)
84. 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 }); }
85. 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 }
86. 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 }
87. SQLDelight & SQLBrite
88. 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 = ?;
89. SQLDelight & SQLBrite @AutoValue public abstract class Book implements BookModel {
 Factory<Book> FACTORY = new Factory <>(AutoValue_Book ::new);
 
 Mapper<Book> ROW_MAPPER = FACTORY.selectAllMapper();
 }
90. 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);
 }
91. 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);
 }
 }
92. 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);
 }
 }
93. Powerful combination
94. Review
95. Review – pros
96. Review – pros • Type-safe marshalling & unmarshalling
97. Review – pros • Type-safe marshalling & unmarshalling • Compile-time SQL validation
98. Review – pros • Type-safe marshalling & unmarshalling • Compile-time SQL validation • SQL autocompletion
99. Review – pros • Type-safe marshalling & unmarshalling • Compile-time SQL validation • SQL autocompletion • Immutability
100. Review – pros • Type-safe marshalling & unmarshalling • Compile-time SQL validation • SQL autocompletion • Immutability • All SQLite feature-set at your disposal
101. Review – pros • Type-safe marshalling & unmarshalling • Compile-time SQL validation • SQL autocompletion • Immutability • All SQLite feature-set at your disposal • Real-time UI updates
102. Review – cons (Jun 2017)
103. Review – cons (Jun 2017) • SQLBrite latest release only works with RxJava 1
104. Review – cons (Jun 2017) • SQLBrite latest release only works with RxJava 1 (RxJava 2 support is imminent, available on SNAPSHOT)
105. 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
106. Questions?
107. Thanks. @leandrofavarin www.leandrofavarin.com