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
Outline
Slide 3
Slide 3 text
Outline
1. SQLite
2. ORM libraries
3. SQLDelight
4. SQLBrite
5. SQLDelight & SQLBrite
6. Room
7. Review
Slide 4
Slide 4 text
Outline
1. SQLite
2. ORM libraries
3. SQLDelight
4. SQLBrite
5. SQLDelight & SQLBrite
6. Room
7. Review
Slide 5
Slide 5 text
SQLite
Slide 6
Slide 6 text
SQLite in Android
Slide 7
Slide 7 text
SQLite in Android – pros
• Default choice for persisting relational data
Slide 8
Slide 8 text
SQLite in Android – pros
• Default choice for persisting relational data
• Powerful (cover incredibly amount of use cases)
Slide 9
Slide 9 text
SQLite in Android – pros
• Default choice for persisting relational data
• Powerful (cover incredibly amount of use cases)
• Efficient
Slide 10
Slide 10 text
SQLite in Android – pros
• Default choice for persisting relational data
• Powerful (cover incredibly amount of use cases)
• Efficient
• Fast
Slide 11
Slide 11 text
SQLite in Android – pros
• Default choice for persisting relational data
• Powerful (cover incredibly amount of use cases)
• Efficient
• Fast
• Stable
Slide 12
Slide 12 text
SQLite in Android – pros
• Default choice for persisting relational data
• Powerful (cover incredibly amount of use cases)
• Efficient
• Fast
• Stable
• Cross-platform
Slide 13
Slide 13 text
SQLite in Android – cons
• It’s hard, because:
• Cursors
• Verboseness
• No type-safety
Implemented as an interface on top of a native SQLite bundle
Slide 14
Slide 14 text
SQLite in Android – cons
Example of query from developer.android.com:
Slide 15
Slide 15 text
SQLite in Android – example
SQLiteDatabase db = mDbHelper.getReadableDatabase();
// Define a projection that specifies which columns from the database
// you will actually use after this query.
String[] projection = {
FeedEntry._ID,
FeedEntry.COLUMN_NAME_TITLE,
FeedEntry.COLUMN_NAME_SUBTITLE
};
// Filter results WHERE "title" = 'My Title'
String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
String[] selectionArgs = { "My Title" };
// How you want the results sorted in the resulting Cursor
String sortOrder =
FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";
Cursor cursor = db.query(
FeedEntry.TABLE_NAME, // The table to query
projection, // The columns to return
selection, // The columns for the WHERE clause
selectionArgs, // The values for the WHERE clause
null, // don't group the rows
null, // don't filter by row groups
sortOrder // The sort order
);
Slide 16
Slide 16 text
SQLite in Android – example
// Filter results WHERE "title" = 'My Title'
String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
String[] selectionArgs = { "My Title" };
// How you want the results sorted in the resulting Cursor
String sortOrder =
FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";
Slide 17
Slide 17 text
SQLite in Android – example
// Filter results WHERE "title" = 'My Title'
String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
String[] selectionArgs = { "My Title" };
// How you want the results sorted in the resulting Cursor
String sortOrder =
FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";
Slide 18
Slide 18 text
SQLite in Android – example
// Filter results WHERE "title" = 'My Title'
String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
String[] selectionArgs = { "My Title" };
// How you want the results sorted in the resulting Cursor
String sortOrder =
FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";
Slide 19
Slide 19 text
SQLite in Android – example
SQLiteDatabase db = mDbHelper.getReadableDatabase();
// Define a projection that specifies which columns from the database
// you will actually use after this query.
String[] projection = {
FeedEntry._ID,
FeedEntry.COLUMN_NAME_TITLE,
FeedEntry.COLUMN_NAME_SUBTITLE
};
// Filter results WHERE "title" = 'My Title'
String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
String[] selectionArgs = { "My Title" };
// How you want the results sorted in the resulting Cursor
String sortOrder =
FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";
Cursor cursor = db.query(
FeedEntry.TABLE_NAME, // The table to query
projection, // The columns to return
selection, // The columns for the WHERE clause
selectionArgs, // The values for the WHERE clause
null, // don't group the rows
null, // don't filter by row groups
sortOrder // The sort order
);
Slide 20
Slide 20 text
SQLite in Android – example
SQLiteDatabase db = mDbHelper.getReadableDatabase();
// Define a projection that specifies which columns from the database
// you will actually use after this query.
String[] projection = {
FeedEntry._ID,
FeedEntry.COLUMN_NAME_TITLE,
FeedEntry.COLUMN_NAME_SUBTITLE
};
// Filter results WHERE "title" = 'My Title'
String selection = FeedEntry.COLUMN_NAME_TITLE + " = ?";
String[] selectionArgs = { "My Title" };
// How you want the results sorted in the resulting Cursor
String sortOrder =
FeedEntry.COLUMN_NAME_SUBTITLE + " DESC";
Cursor cursor = db.query(
FeedEntry.TABLE_NAME, // The table to query
projection, // The columns to return
selection, // The columns for the WHERE clause
selectionArgs, // The values for the WHERE clause
null, // don't group the rows
null, // don't filter by row groups
sortOrder // The sort order
);
List result = new ArrayList <>();
while(cursor.moveToNext()) {
long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID));
String title = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE));
String subtitle = cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE));
DbItem item = new DbItem(itemId, title, subtitle);
result.add(item);
}
cursor.close();
Slide 21
Slide 21 text
SQLite in Android – example
List result = new ArrayList <>();
while(cursor.moveToNext()) {
long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID));
String title =
cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE));
String subtitle =
cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE));
DbItem item = new DbItem(itemId, title, subtitle);
result.add(item);
}
cursor.close();
Slide 22
Slide 22 text
SQLite in Android – example
List result = new ArrayList <>();
while(cursor.moveToNext()) {
long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID));
String title =
cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE));
String subtitle =
cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE));
DbItem item = new DbItem(itemId, title, subtitle);
result.add(item);
}
cursor.close();
Slide 23
Slide 23 text
SQLite in Android – example
List result = new ArrayList <>();
while(cursor.moveToNext()) {
long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID));
String title =
cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE));
String subtitle =
cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE));
DbItem item = new DbItem(itemId, title, subtitle);
result.add(item);
}
cursor.close();
Slide 24
Slide 24 text
SQLite in Android – example
List result = new ArrayList <>();
while(cursor.moveToNext()) {
long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID));
String title =
cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE));
String subtitle =
cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE));
DbItem item = new DbItem(itemId, title, subtitle);
result.add(item);
}
cursor.close();
Slide 25
Slide 25 text
SQLite in Android – example
List result = new ArrayList <>();
while(cursor.moveToNext()) {
long itemId = cursor.getLong(cursor.getColumnIndexOrThrow(FeedEntry._ID));
String title =
cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_TITLE));
String subtitle =
cursor.getString(cursor.getColumnIndexOrThrow(FeedEntry.COLUMN_NAME_SUBTITLE));
DbItem item = new DbItem(itemId, title, subtitle);
result.add(item);
}
cursor.close();
Slide 26
Slide 26 text
SQLite in Android
Certainly there has to be a better way
Slide 27
Slide 27 text
Outline
1. SQLite
2. ORM libraries
3. SQLDelight
4. SQLBrite
5. SQLDelight & SQLBrite
6. Room
7. Review
Slide 28
Slide 28 text
ORM libraries
Slide 29
Slide 29 text
ORM libraries
What is ORM?
Slide 30
Slide 30 text
What is ORM?
Technique for converting data between incompatible type
systems in OO languages
… basically: Converting between complex data types to
“primitives"
Wraps a SQL source
Examples: greenDAO, OrmLite, Cupboard, Core Data (iOS)
Slide 31
Slide 31 text
ORM – cons
• New APIs to be learned
Slide 32
Slide 32 text
ORM – cons
• New APIs to be learned
• Hide many powerful SQL capabilities
Slide 33
Slide 33 text
ORM – cons
• New APIs to be learned
• Hide many powerful SQL capabilities
• Decreased performance
Slide 34
Slide 34 text
ORM – cons
• New APIs to be learned
• Hide many powerful SQL capabilities
• Decreased performance
• Hardly allow optimizations from consumers
Slide 35
Slide 35 text
ORM – cons
• New APIs to be learned
• Hide many powerful SQL capabilities
• Decreased performance
• Hardly allow optimizations from consumers
• Objects are usually mutable
Slide 36
Slide 36 text
ORM – cons
• New APIs to be learned
• Hide many powerful SQL capabilities
• Decreased performance
• Hardly allow optimizations from consumers
• Objects are usually mutable
• Lock-in effect
Slide 37
Slide 37 text
SQL should be embraced,
not hidden
Slide 38
Slide 38 text
Outline
1. SQLite
2. ORM libraries
3. SQLDelight
4. SQLBrite
5. SQLDelight & SQLBrite
6. Room
7. Review
Slide 39
Slide 39 text
SQLDelight
Slide 40
Slide 40 text
SQLDelight
• Empower SQL(ite) by embracing it
• Type-safe APIs to read & write the rows of your tables
• Organizes your SQL statements
• Easy to access from Java
Slide 41
Slide 41 text
SQLDelight – how it works
• Generates code from SQL `CREATE TABLE` statements
• Create special files containing all SQL-related content
• Extension: .sq
• Runs on compile-time
Slide 42
Slide 42 text
SQLDelight – build.gradle
buildscript {
repositories {
mavenCentral()
}
dependencies {
classpath 'com.squareup.sqldelight:gradle-plugin:0.6.1'
}
}
apply plugin: 'com.squareup.sqldelight'
Slide 43
Slide 43 text
SQLDelight – IntelliJ plugin
Slide 44
Slide 44 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 45
Slide 45 text
SQLDelight – IntelliJ plugin
Slide 46
Slide 46 text
SQLDelight – keep in mind
• The file structure of your project is crucial for this to work
• .sq files must be in a folder matching the directory structure
Slide 47
Slide 47 text
SQLDelight
example
Slide 48
Slide 48 text
SQLDelight – example
app/src/main/
- java/
- com/example/project/models/
- Book.java
...
Slide 49
Slide 49 text
SQLDelight – example
app/src/main/
- java/
- com/example/project/models/
- Book.java
...
- sqldelight/
- com/example/project/models/
- Book.sq
...
Slide 50
Slide 50 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
);
Slide 51
Slide 51 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;
Slide 52
Slide 52 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 53
Slide 53 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 54
Slide 54 text
SQLDelight – … generates:
long _id();
@NonNull String title();
@NonNull String author();
@Nullable Long num_chapters();
Slide 55
Slide 55 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 56
Slide 56 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)
);
}
}
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 57
Slide 57 text
SQLDelight – Book.java
@AutoValue public abstract class Book implements BookModel {
}
Slide 58
Slide 58 text
SQLDelight – Book.java
@AutoValue public abstract class Book implements BookModel {
public static final Factory FACTORY = new
Factory <>(AutoValue_Book ::new);
}
Slide 59
Slide 59 text
SQLDelight – Book.java
@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 60
Slide 60 text
SQLDelight – BookRepo.java
List getAllBooks(SQLiteDatabase db) {
SqlDelightStatement query = Book.FACTORY.selectAll();
try (Cursor c = db.rawQuery(query.statement, query.args)) {
}
}
Slide 61
Slide 61 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 62
Slide 62 text
SQLDelight – projections, joins
Each SELECT statement will have:
• an interface
• mapper
• method to create a new instance of the mapper
Slide 63
Slide 63 text
SQLDelight – types
• Identical to SQLite column definitions
• Plus extra column constraint for Java type
Slide 64
Slide 64 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 65
Slide 65 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 66
Slide 66 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 67
Slide 67 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);
}
}
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 68
Slide 68 text
SQLDelight – enums
• Includes a built-in ColumnAdapter for storing an enum as TEXT
Slide 69
Slide 69 text
SQLDelight – views
• Same treatment in generated code as tables with their own
model interface
Slide 70
Slide 70 text
Does it work
with Kotlin?
Slide 71
Slide 71 text
SQLDelight – Kotlin
• Mostly works!
Slide 72
Slide 72 text
SQLDelight – Kotlin
• Mostly works!
• .. just have to redundantly declare getters
Slide 73
Slide 73 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 74
Slide 74 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
}
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 75
Slide 75 text
SQLDelight – migrations
• Least supported part of SQLDelight
• Library has potential to execute migrations for you
• Likely to be implemented using specific files .sqm (issue #89)
• Today’s solution: write strings in your SQLiteOpenHelper
… or
• Create a migration .sq with unused `CREATE TABLE` statement
• Write your migration like any SQL statement
• Cons: Unused generated code
Slide 76
Slide 76 text
Outline
1. SQLite
2. ORM libraries
3. SQLDelight
4. SQLBrite
5. SQLDelight & SQLBrite
6. Room
7. Review
Slide 77
Slide 77 text
SQLBrite
Slide 78
Slide 78 text
SQLBrite
• Wrapper around SQLiteOpenHelper which introduces
reactive stream semantics to SQL operations
Slide 79
Slide 79 text
SQLBrite
• Wrapper around SQLiteOpenHelper and ContentResolver
which introduces reactive stream semantics to SQL operations
Slide 80
Slide 80 text
SQLBrite
• Wrapper around SQLiteOpenHelper and ContentResolver
which introduces reactive stream semantics to SQL operations
• Updates to the subscribed table(s) will trigger notifications
Slide 81
Slide 81 text
SQLBrite – build.gradle
compile ‘com.squareup.sqlbrite2:sqlbrite:2.0.0’
Slide 82
Slide 82 text
SQLBrite – usage
SqlBrite sqlBrite = new SqlBrite.Builder()
.build();
Slide 83
Slide 83 text
SQLBrite – usage
SqlBrite sqlBrite = new SqlBrite.Builder()
.logger(message -> Timber.tag("Database").v(message))
.build();
Slide 84
Slide 84 text
SQLBrite – usage
SqlBrite sqlBrite = new SqlBrite.Builder()
.logger(message -> Timber.tag("Database").v(message))
.queryTransformer()
.build();
Slide 85
Slide 85 text
SQLBrite – usage
SqlBrite sqlBrite = new SqlBrite.Builder()
.logger(message -> Timber.tag("Database").v(message))
.build();
BriteDatabase db =
sqlBrite.wrapDatabaseHelper(openHelper, Schedulers.io());
Slide 86
Slide 86 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 87
Slide 87 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 88
Slide 88 text
SQLBrite – powerful logging
QUERY (0ms)
tables: [book]
sql: SELECT *
FROM book
WHERE title = ?1 AND author = ?2
args: ["1984","George Orwell"]
EXECUTE
SQLiteProgram: INSERT INTO
book(title, author)
VALUES(?, ?)
Slide 89
Slide 89 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 90
Slide 90 text
SQLBrite – BookRepo.java
BriteDatabase db = // ...;
QueryObservable books = db.createQuery("book", "SELECT * FROM book");
books.subscribe(query -> /* ... */);
Slide 91
Slide 91 text
SQLBrite – BookRepo.java
BriteDatabase db = // ...;
QueryObservable books = db.createQuery("book", "SELECT * FROM book");
books.subscribe(query -> /* ... */);
db.insert("book", Book.create("1984", "George Orwell"));
db.delete("book", "title = ?", "The Great Gatsby");
db.update("book", ...);
BriteDatabase db = // ...;
QueryObservable books = db.createQuery("book", "SELECT * FROM book");
books.subscribe(query -> /* ... */);
db.insert("book", Book.create("1984", "George Orwell"));
db.delete("book", "title = ?", "The Great Gatsby");
db.update("book", ...);
Will emit updates to subscribers
Slide 92
Slide 92 text
SQLBrite – transactions
• Can be used to group changes and be notified only once
• transaction.markSuccessful() then triggers emission
Slide 93
Slide 93 text
Does it work
with Kotlin?
Slide 94
Slide 94 text
SQLBrite – Kotlin
• Yes!
• Kotlin extensions methods are also provided
Slide 95
Slide 95 text
SQLBrite – build.gradle
compile ‘com.squareup.sqlbrite2:sqlbrite:2.0.0’
Slide 96
Slide 96 text
SQLBrite – build.gradle
compile ‘com.squareup.sqlbrite2:sqlbrite:2.0.0’
compile ‘com.squareup.sqlbrite2:sqlbrite-kotlin:2.0.0’
Slide 97
Slide 97 text
SQLBrite – Kotlin module
Provides mapTo* extension functions:
• Observable.mapToOne()
• Observable.mapToOneOrDefault()
• Observable.mapToOptional()
• Observable.mapToList()
Slide 98
Slide 98 text
Outline
1. SQLite
2. ORM libraries
3. SQLDelight
4. SQLBrite
5. SQLDelight & SQLBrite
6. Room
7. Review
Slide 99
Slide 99 text
SQLDelight & SQLBrite
Slide 100
Slide 100 text
SQLDelight & SQLBrite
CREATE TABLE book (
_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
title TEXT,
author TEXT,
publishedAt TEXT AS ZonedDateTime
);
selectByPublishedAt:
SELECT *
FROM book
WHERE publishedAt = ?;
insertRow:
INSERT INTO book(title, author, publishedAt)
VALUES (?, ?, ?);
Slide 101
Slide 101 text
SQLDelight & SQLBrite
@AutoValue public abstract class Book implements BookModel {
static Factory FACTORY = new Factory <>(AutoValue_Book ::new,
new ZonedDateTimeColumnAdapter());
static Mapper ROW_MAPPER = FACTORY.selectByPublishedAtMapper();
}
Slide 102
Slide 102 text
SQLDelight & SQLBrite
Observable getBookByPublishedDate(BriteDatabase db,
ZonedDateTime publishedAt) {
SqlDelightStatement query =
Book.FACTORY.selectByPublishedAt(publishedAt);
return db.createQuery(query.tables, query.statement, query.args)
.mapToOne(Book.ROW_MAPPER ::map);
}
Observable getBookByPublishedDate(BriteDatabase db,
ZonedDateTime publishedAt) {
SqlDelightStatement query =
Book.FACTORY.selectByPublishedAt(publishedAt);
return db.createQuery(query.tables, query.statement, query.args)
.mapToOne(Book.ROW_MAPPER ::map);
}
Observable getBookByPublishedDate(BriteDatabase db,
ZonedDateTime publishedAt) {
SqlDelightStatement query =
Book.FACTORY.selectByPublishedAt(publishedAt);
return db.createQuery(query.tables, query.statement, query.args)
.mapToOne(Book.ROW_MAPPER ::map);
}
Slide 103
Slide 103 text
How would it look like using
traditional Android APIs?
Slide 104
Slide 104 text
Traditional Android APIs
Book getBookByPublishedDate(SQLiteOpenHelper openHelper,
ZonedDateTime publishedAt) {
SQLiteDatabase db = openHelper.getReadableDatabase();
String[] projection = {
Book.TITLE,
Book.AUTHOR,
Book.PUBLISHEDAT
};
String selection = Book.PUBLISHEDAT + " = ?";
String[] selectionArgs = {FORMATTER.format(publishedAt)};
Cursor cursor = db.query(
Book.TABLE_NAME,
projection,
selection,
selectionArgs,
null,
null,
null);
Book book = null;
if (cursor.moveToNext()) {
String title = cursor.getString(cursor.getColumnIndexOrThrow(Book.TITLE));
String author = cursor.getString(cursor.getColumnIndexOrThrow(Book.AUTHOR));
String publishedAtStr = cursor.getString(cursor.getColumnIndexOrThrow(Book.PUBLISHEDAT));
ZonedDateTime parsed = DateTimeFormatter.ISO_OFFSET_DATE_TIME.parse(publishedAtStr,
ZonedDateTime.FROM);
book = Book.create(title, author, parsed);
}
cursor.close();
return book;
}
Slide 105
Slide 105 text
Traditional Android APIs
Book getBookByPublishedDate(SQLiteOpenHelper openHelper,
ZonedDateTime publishedAt) {
SQLiteDatabase db = openHelper.getReadableDatabase();
String[] projection = {
Book.TITLE,
Book.AUTHOR,
Book.PUBLISHEDAT
};
String selection = Book.PUBLISHEDAT + " = ?";
String[] selectionArgs = {FORMATTER.format(publishedAt)};
Cursor cursor = db.query(
Book.TABLE_NAME,
projection,
selection,
selectionArgs,
null,
null,
null);
Book book = null;
if (cursor.moveToNext()) {
String title = cursor.getString(cursor.getColumnIndexOrThrow(Book.TITLE));
String author = cursor.getString(cursor.getColumnIndexOrThrow(Book.AUTHOR));
String publishedAtStr = cursor.getString(cursor.getColumnIndexOrThrow(Book.PUBLISHEDAT));
ZonedDateTime parsed = DateTimeFormatter.ISO_OFFSET_DATE_TIME.parse(publishedAtStr,
ZonedDateTime.FROM);
book = Book.create(title, author, parsed);
}
cursor.close();
return book;
}
Book getBookByPublishedDate(SQLiteOpenHelper openHelper,
ZonedDateTime publishedAt) {
SQLiteDatabase db = openHelper.getReadableDatabase();
String[] projection = {
Book.TITLE,
Book.AUTHOR,
Book.PUBLISHEDAT
};
String selection = Book.PUBLISHEDAT + " = ?";
String[] selectionArgs = {FORMATTER.format(publishedAt)};
Cursor cursor = db.query(
Book.TABLE_NAME,
projection,
selection,
selectionArgs,
null,
null,
null);
Book book = null;
if (cursor.moveToNext()) {
String title = cursor.getString(cursor.getColumnIndexOrThrow(Book.TITLE));
String author = cursor.getString(cursor.getColumnIndexOrThrow(Book.AUTHOR));
String publishedAtStr = cursor.getString(cursor.getColumnIndexOrThrow(Book.PUBLISHEDAT));
ZonedDateTime parsed = DateTimeFormatter.ISO_OFFSET_DATE_TIME.parse(publishedAtStr,
ZonedDateTime.FROM);
book = Book.create(title, author, parsed);
}
cursor.close();
return book;
}
Slide 106
Slide 106 text
SQLDelight & SQLBrite
public class BookRepository {
private final BriteDatabase db;
private final Book.InsertRow insertRow;
public BookRepository(BriteDatabase db) {
this.db = db;
insertRow = new Book.InsertRow(db.getWritableDatabase());
}
public void insert(Book book) {
insertRow.bind(book.title(), book.author());
db.executeInsert(insertRow.table, insertRow.program);
}
}
public class BookRepository {
private final BriteDatabase db;
private final Book.InsertRow insertRow;
public BookRepository(BriteDatabase db) {
this.db = db;
insertRow = new Book.InsertRow(db.getWritableDatabase());
}
public void insert(Book book) {
insertRow.bind(book.title(), book.author());
db.executeInsert(insertRow.table, insertRow.program);
}
}
public class BookRepository {
private final BriteDatabase db;
private final Book.InsertRow insertRow;
public BookRepository(BriteDatabase db) {
this.db = db;
insertRow = new Book.InsertRow(db.getWritableDatabase());
}
public void insert(Book book) {
insertRow.bind(book.title(), book.author());
db.executeInsert(insertRow.table, insertRow.program);
}
}
Slide 107
Slide 107 text
SQLDelight & SQLBrite
• SQLDelight and SQLBrite are highly optimized
• Lightweight (~160 methods + generated code)
Slide 108
Slide 108 text
Powerful combination
Slide 109
Slide 109 text
Outline
1. SQLite
2. ORM libraries
3. SQLDelight
4. SQLBrite
5. SQLDelight & SQLBrite
6. Room
7. Review
Slide 110
Slide 110 text
Room
Slide 111
Slide 111 text
Room (briefly)
Slide 112
Slide 112 text
Room
• Released this year at Google I/O
• Database part of Architecture Components
• Aims for the same goal as to SQLDelight & SQLBrite
Slide 113
Slide 113 text
Room
• Avoids boilerplate code
• Compile-time checked SQL queries
• Allows asynchronous queries
Aims for the same goal as to SQLDelight & SQLBrite:
More information:
Data Persistence In Android: There’s Room For Improvement
Florina Muntenescu
Slide 114
Slide 114 text
Outline
1. SQLite
2. ORM libraries
3. SQLDelight
4. SQLBrite
5. SQLDelight & SQLBrite
6. Room
7. Review
Slide 115
Slide 115 text
Review
Slide 116
Slide 116 text
Review
• Type-safe marshalling &
unmarshalling
• Compile-time SQL validation
• SQL autocompletion
• Incentivizes immutability
• All SQLite feature-set at
your disposal
• Migration process is not
ideal
SQLDelight
Slide 117
Slide 117 text
Review
• Type-safe marshalling &
unmarshalling
• Compile-time SQL validation
• SQL autocompletion
• Incentivizes immutability
• All SQLite feature-set at
your disposal
• Migration process is not
ideal
SQLDelight
• Content providers support
• Real-time UI updates
• Agnostic to object mapper
or SQL source
SQLBrite
Slide 118
Slide 118 text
Questions?
Slide 119
Slide 119 text
Thanks. @leandrofavarin
www.leandrofavarin.com