Tweet
Tweet

Slide 1

Slide 1 text

Don’t fear SQL A better way to store and handle data with SQLBrite & SQLDelight Leandro Favarin

Slide 2

Slide 2 text

SQLite

Slide 3

Slide 3 text

SQLite in Android

Slide 4

Slide 4 text

SQLite in Android – pros • Default choice for persisting relational data

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

SQLite in Android – cons • It’s hard!

Slide 11

Slide 11 text

SQLite in Android – cons • It’s hard! • Cursors

Slide 12

Slide 12 text

SQLite in Android – cons • It’s hard! • Cursors • Verboseness

Slide 13

Slide 13 text

SQLite in Android – cons • It’s hard! • Cursors • Verboseness • No type-safety

Slide 14

Slide 14 text

ORM libraries

Slide 15

Slide 15 text

What is ORM? Technique for converting data between incompatible type systems in OO languages

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

ORM – cons • New APIs to be learned

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

ORM – cons • New APIs to be learned • Hide many powerful SQL capabilities • Decreased performance • Hardly allow optimizations from consumers • Lock-in effect

Slide 23

Slide 23 text

SQLDelight

Slide 24

Slide 24 text

SQLDelight • Generates Java models from your SQL `CREATE TABLE` statements

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

SQLDelight – IntelliJ plugin Provides language-level features for .sq files, including:

Slide 28

Slide 28 text

SQLDelight – IntelliJ plugin Provides language-level features for .sq files, including: • Syntax highlighting

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

SQLDelight – example app/src/main/ - java/ - com/example/project/models/ - Book.java …

Slide 37

Slide 37 text

SQLDelight – example app/src/main/ - java/ - com/example/project/models/ - Book.java ... - sqldelight/ - com/example/project/models/ - Book.sq ...

Slide 38

Slide 38 text

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 (?, ?, ?);

Slide 39

Slide 39 text

SQLDelight – implementation @AutoValue public abstract class Book implements BookModel { }

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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(); ... }

Slide 43

Slide 43 text

SQLDelight – … generates: class Mapper implements RowMapper { @Override public T map(@NonNull Cursor cursor) { return bookModelFactory.creator.create( cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getLong(3) ); } }

Slide 44

Slide 44 text

SQLDelight – … generates: public SqlDelightStatement selectAll() { return new SqlDelightStatement("" + "SELECT *\n" + "FROM book", new String[0], Collections.singleton("book")); }

Slide 45

Slide 45 text

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); } }

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

SQLDelight – BookRepo.java List getAllBooks(SQLiteDatabase db) { SqlDelightStatement query = Book.FACTORY.selectAll(); try (Cursor c = db.rawQuery(query.statement, query.args)) { List result = new ArrayList <>(c.getCount()); while (c.moveToNext()) { result.add(Book.ROW_MAPPER.map(c)); } return result; } }

Slide 48

Slide 48 text

SQLDelight – projections, joins Each SELECT statement will have:

Slide 49

Slide 49 text

SQLDelight – projections, joins Each SELECT statement will have: • an interface

Slide 50

Slide 50 text

SQLDelight – projections, joins Each SELECT statement will have: • an interface • mapper

Slide 51

Slide 51 text

SQLDelight – projections, joins Each SELECT statement will have: • an interface • mapper • method on the factory to create a new instance of the mapper

Slide 52

Slide 52 text

SQLDelight – types • Identical to SQLite column definitions

Slide 53

Slide 53 text

SQLDelight – types • Identical to SQLite column definitions • Plus extra column constraint for Java type

Slide 54

Slide 54 text

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 );

Slide 55

Slide 55 text

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 );

Slide 56

Slide 56 text

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 );

Slide 57

Slide 57 text

SQLDelight – custom classes class ZonedDateTimeColumnAdapter implements ColumnAdapter { 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); } }

Slide 58

Slide 58 text

SQLDelight – enums • Includes a ColumnAdapter for storing an enum as TEXT

Slide 59

Slide 59 text

SQLDelight – enums @AutoValue public abstract class Book implements BookModel { public enum Genre { ROMANCE, ADVENTURE, SELF_HELP, BIOGRAPHY, FICTION, DRAMA } ColumnAdapter GENRE_ADAPTER = EnumColumnAdapter.create(Genre.class); Factory FACTORY = new Factory <>(new Creator <>() {}, GENRE_ADAPTER); }

Slide 60

Slide 60 text

SQLDelight – enums import com.leandrofavarin.books.entities.Book; CREATE TABLE book ( genre TEXT AS Book.Genre );

Slide 61

Slide 61 text

SQLDelight – views • Same treatment in generated code as tables with their own model interface

Slide 62

Slide 62 text

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;

Slide 63

Slide 63 text

SQLDelight – views @AutoValue public abstract class Book implements BookModel { Factory FACTORY = new Factory <>(AutoValue_Book ::new); RowMapper ROW_MAPPER = FACTORY.selectListingMapper(AutoValue_Book_Listing ::new); @AutoValue static abstract class Listing implements ListingModel { } }

Slide 64

Slide 64 text

Kotlin?

Slide 65

Slide 65 text

SQLDelight – kotlin • Mostly just works!

Slide 66

Slide 66 text

SQLDelight – kotlin • … mostly just works! • Have to redundantly declare getters

Slide 67

Slide 67 text

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 (?, ?, ?);

Slide 68

Slide 68 text

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 }

Slide 69

Slide 69 text

SQLDelight – migrations • Least supported part of SQLDelight

Slide 70

Slide 70 text

SQLDelight – migrations • Least supported part of SQLDelight • Likely to be implemented as .sqm files

Slide 71

Slide 71 text

SQLDelight – migrations • Least supported part of SQLDelight • Likely to be implemented as .sqm files • Solution: write strings in the open helper

Slide 72

Slide 72 text

SQLBrite

Slide 73

Slide 73 text

SQLBrite • Wrapper around SQLiteOpenHelper which introduces reactive stream semantics to SQL operations

Slide 74

Slide 74 text

SQLBrite • Wrapper around SQLiteOpenHelper and ContentResolver which introduces reactive stream semantics to SQL operations

Slide 75

Slide 75 text

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

Slide 76

Slide 76 text

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

Slide 77

Slide 77 text

SQLBrite – usage SqlBrite sqlBrite = new SqlBrite.Builder() .build();

Slide 78

Slide 78 text

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

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

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

Slide 81

Slide 81 text

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);

Slide 82

Slide 82 text

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);

Slide 83

Slide 83 text

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(?, ?)

Slide 84

Slide 84 text

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

Slide 85

Slide 85 text

SQLBrite – BookRepo.java Observable> 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 }

Slide 86

Slide 86 text

SQLBrite – BookRepo.java Observable> 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 }

Slide 87

Slide 87 text

SQLDelight & SQLBrite

Slide 88

Slide 88 text

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 = ?;

Slide 89

Slide 89 text

SQLDelight & SQLBrite @AutoValue public abstract class Book implements BookModel {
 Factory FACTORY = new Factory <>(AutoValue_Book ::new);
 
 Mapper ROW_MAPPER = FACTORY.selectAllMapper();
 }

Slide 90

Slide 90 text

SQLDelight & SQLBrite Observable> getAllBooksStream() {
 SqlDelightStatement query = Book.FACTORY.selectAll();
 return db.createQuery(query.tables, query.statement, query.args)
 .mapToList(Book.ROW_MAPPER ::map);
 }

Slide 91

Slide 91 text

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);
 }
 }

Slide 92

Slide 92 text

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);
 }
 }

Slide 93

Slide 93 text

Powerful combination

Slide 94

Slide 94 text

Review

Slide 95

Slide 95 text

Review – pros

Slide 96

Slide 96 text

Review – pros • Type-safe marshalling & unmarshalling

Slide 97

Slide 97 text

Review – pros • Type-safe marshalling & unmarshalling • Compile-time SQL validation

Slide 98

Slide 98 text

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

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

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

Slide 102

Slide 102 text

Review – cons (Jun 2017)

Slide 103

Slide 103 text

Review – cons (Jun 2017) • SQLBrite latest release only works with RxJava 1

Slide 104

Slide 104 text

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

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

Questions?

Slide 107

Slide 107 text

Thanks. @leandrofavarin www.leandrofavarin.com