Embracing SQL
 Without Abstraction Alec Strong

Cursor returned zero rows SELECT knowledge
 FROM alec
 WHERE venue='Droidcon'
 LIMIT 40min;

What we want: • RxJava • Immutability • Type Safety • Compiler Errors • SQLite features

What we’re given: • Android’s SQLite bindings • SQLiteOpenHelper • Cursor • SQLiteDatabase • ContentValues • ContentProvider/ContentResolver

What we’re given: db.rawQuery("SELECT * FROM user", new String[0]);

What we’re given: db.rawQuery("SELECT * FROM user WHERE name IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" });

What we’re given: db.rawQuery("SELECT * FROM " + User.TABLE_NAME + " WHERE name IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" });

What we’re given: db.rawQuery("SELECT * FROM " + User.TABLE_NAME + 
 " WHERE " + User.NAME + " IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" });

What we want: RxJava Immutability Type Safety Compiler Errors SQLite features

What did we do: • Research

Research • Reflection • Synchronous • Lack of Type Safety • Underpowered Query APIs • Missing SQLite features

Scaffold @Table(User.TABLE_NAME)
 public interface User {
 String TABLE_NAME = "user";
 
 String USER_ID = "user_id";
 String NAME = "name";
 String PHOTO_URL = "photo_url";
 
 @PrimaryKey @Column(USER_ID) @AutoIncrement @NotNull
 String lookupKey();
 
 @NotNull @Column(NAME)
 String name();
 
 @NotNull @Column(PHOTO_URL)
 String photoUrl();
 }

Scaffold @Table(User.TABLE_NAME)
 public interface User {
 } public static final class ScUser implements User {
 public static Func1> mapRow();
 public static Func1 single();
 }

Scaffold @Database (
 tables = {
 User.class
 },
 fileName = "my_database.db",
 version = 1,
 authority = BuildConfig.PACKAGE_NAME + ".data.db.Provider"
 )
 public final class MyDatabase extends ScaffoldDatabase {
 public MyDatabase(Context context) {
 super(context.getContentResolver());
 }
 }

Scaffold Observable user = myDatabase.createQuery(
 Query.from(User.class).where(EQ(User.NAME, "John")))
 .map(ScUser.single());

Scaffold Observable payments = myDatabase.createQuery(Query
 .from(Payment.class)
 .join(Customer.class)
 .on(EQ(Customer.TOKEN, Payment.CUSTOMER_TOKEN)))
 .join(User.class)
 .on(EQ(Customer.USER_ID, User.USER_ID))
 .where(EQ(Payment.TOKEN, paymentToken));

Observable payments = myDatabase.createQuery(Query
 .from(Payment.class)
 .join(Customer.class)
 .on(EQ(Customer.TOKEN, Payment.CUSTOMER_TOKEN)))
 .join(User.class)
 .on(EQ(Customer.USER_ID, User.USER_ID))
 .where(EQ(Payment.TOKEN, paymentToken)); SELECT *
 FROM payment
 JOIN customer USING (customer_token)
 JOIN user USING (user_id)
 WHERE token=?;

Observable payments = myDatabase.createQuery(Query
 .from(Payment.class)
 .join(Customer.class)
 .on(EQ(Customer.TOKEN, Payment.CUSTOMER_TOKEN)))
 .join(User.class, JoinType.LEFT)
 .on(EQ(Customer.USER_ID, User.USER_ID))
 .where(EQ(Payment.TOKEN, paymentToken)); SELECT *
 FROM payment
 JOIN customer USING (customer_token)
 LEFT JOIN user USING (user_id)
 WHERE token=?;

Observable payments = myDatabase.createQuery(Query
 .from(Payment.class)
 .join(Customer.class)
 .on(EQ(Customer.TOKEN, Payment.CUSTOMER_TOKEN)))
 .join(User.class, JoinType.LEFT)
 .on(EQ(Customer.USER_ID, User.USER_ID))
 .where(EQ(Payment.TOKEN, paymentToken));
 .where(AND(
 EQ(Payment.TOKEN, paymentToken),
 OR(EQ(User.USER_ID, null), EQ(User.NAME, “Logan”))
 )); SELECT *
 FROM payment
 JOIN customer USING (customer_token)
 LEFT JOIN user USING (user_id)
 WHERE token=?;
 AND (user.used_id IS NULL OR user.name = 'Logan');

Scaffold • Many flaws • QueryBuilder • Closed API • Too busy as a library • RxJava, ContentProvider, QueryBuilder, SQLiteOpenHelper, Cursor -> Object mapping, Object -> ContentValues Marshaling • Prone to bugs • Inner queries and joins

What we want: RxJava Immutability Type Safety Compiler Errors SQLite features

Not Scaffold • Need to address type safety • IntColumn, StringColumn • How do you model scope

Not Scaffold • Need to address type safety • IntColumn, StringColumn • How do you model scope SELECT *
 FROM customer
 WHERE customer_name = (
 SELECT customer_name -- Works fine, variable is scoped in.
 FROM user
 ORDER BY customer_name -- No such column found.
 );

Putting Away The Scaffolding

What we want: RxJava Immutability Type Safety Compiler Errors SQLite features

What we want: RxJava

SQLBrite • Won’t do perfect emission • False positives • But how do we know when a table is updated? • Require table names

@AutoValue public abstract class User {
 public static final String TABLE_NAME = "user";
 
 public static final String USER_ID = "user_id";
 public static final String NAME = "name";
 public static final String PHOTO_URL = "photo_url";
 
 public static final String CREATE_TABLE = format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL);


 public abstract long userId();
 public abstract String name();
 public abstract String photoUrl();



 public static final Func1 MAPPER = cursor -> {
 return new AutoValue_User(
 cursor.getLong(cursor.getColumnIndexOrThrow(USER_ID)),
 cursor.getString(cursor.getColumnIndexOrThrow(NAME)),
 cursor.getString(cursor.getColumnIndexOrThrow(PHOTO_URL))
 );
 };


 public static final class Builder {
 private final ContentValues contentValues = new ContentValues();
 
 public Builder userId(long userId) {
 contentValues.put(USER_ID, userId);
 return this;
 }A
 
 public Builder name(String name) {
 contentValues.put(NAME, name);
 return this;
 }A
 
 public Builder photoUrl(String photoUrl) {
 contentValues.put(PHOTO_URL, photoUrl);
 return this;
 }A
 
 public ContentValues build() {
 return contentValues;
 }A
 }A
 }A

@AutoValue public abstract class User {
 public static final String TABLE_NAME = "user";
 
 public static final String USER_ID = "user_id";
 public static final String NAME = "name";
 public static final String PHOTO_URL = "photo_url";
 
 public static final String CREATE_TABLE = format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL);

@AutoValue public abstract class User {
 public static final String TABLE_NAME = "user";
 
 public static final String USER_ID = "user_id";
 public static final String NAME = "name";
 public static final String PHOTO_URL = "photo_url";
 public static final String BIRTH_DATE = “birth_date";
 
 
 public static final String CREATE_TABLE = format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL,” //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL, BIRTH_DATE);


 public abstract long userId();
 public abstract String name();
 public abstract String photoUrl();



 public abstract long userId();
 public abstract String name();
 public abstract String photoUrl();
 public abstract Calendar birthDate();



 public static final Func1 MAPPER = cursor -> {
 return new AutoValue_User(
 cursor.getLong(cursor.getColumnIndexOrThrow(USER_ID)),
 cursor.getString(cursor.getColumnIndexOrThrow(NAME)),
 cursor.getString(cursor.getColumnIndexOrThrow(PHOTO_URL))
 );
 };


 public static final Func1 MAPPER = cursor -> {
 Calendar calendar = Calendar.getInstance();
 calendar.setTimeInMillis( cursor.getLong(cursor.getColumnIndexOrThrow(BIRTH_DATE)));
 return new AutoValue_User(
 cursor.getLong(cursor.getColumnIndexOrThrow(USER_ID)),
 cursor.getString(cursor.getColumnIndexOrThrow(NAME)),
 cursor.getString(cursor.getColumnIndexOrThrow(PHOTO_URL)),
 calendar
 );
 };


 public static final class Builder {
 private final ContentValues contentValues = new ContentValues();
 
 public Builder userId(long userId) {
 contentValues.put(USER_ID, userId);
 return this;
 }A
 
 public Builder name(String name) {
 contentValues.put(NAME, name);
 return this;
 }A
 
 public Builder photoUrl(String photoUrl) {
 contentValues.put(PHOTO_URL, photoUrl);
 return this;
 }A
 
 public ContentValues build() {
 return contentValues;
 }A
 }A
 }A


 public Builder userId(long userId) {
 contentValues.put(USER_ID, userId);
 return this;
 }A
 
 public Builder name(String name) {
 contentValues.put(NAME, name);
 return this;
 }A
 
 public Builder photoUrl(String photoUrl) {
 contentValues.put(PHOTO_URL, photoUrl);
 return this;
 }A
 
 public Builder birthDate(Calendar birthDate) {
 contentValues.put(BIRTH_DATE, birthDate.getTimeInMillis());
 return this;
 }
 
 public ContentValues build() {
 return contentValues;


SQLBrite Observable> users = db.createQuery(User.TABLE_NAME,
 "SELECT * FROM " + User.TABLE_NAME + 
 " WHERE " + User.NAME + " IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" })
 .map(query -> {
 Cursor cursor = query.run();
 List users = new ArrayList<>();
 try {
 users.add(User.MAPPER.call(cursor));
 } finally {
 cursor.close();
 } return users;
 });

SQLBrite Observable> users = db.createQuery(User.TABLE_NAME,
 "SELECT * FROM " + User.TABLE_NAME +
 " WHERE " + User.NAME + " IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" })
 .mapToList(User.MAPPER);

SQLBrite • Solved Rx problem • Requires table name and has false positives

MarshalMappers • Aimed at limiting boilerplate • AutoValue extension • Short life span

No content

@AutoValue public abstract class User {
 public static final String TABLE_NAME = "user";
 
 public static final String USER_ID = "user_id";
 public static final String NAME = "name";
 public static final String PHOTO_URL = "photo_url";
 public static final String BIRTH_DATE = "birth_date";
 
 public static final String CREATE_TABLE = format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL, BIRTH_DATE);



 public abstract long userId();
 public abstract String name();
 public abstract String photoUrl();
 public abstract Calendar birthDate();


 @Column(USER_ID) public abstract long userId();
 @Column(NAME) public abstract String name();
 @Column(PHOTO_URL) public abstract String photoUrl();
 @Column(value = BIRTH_DATE, marshalMapper = CalendarMarshalMapper.class)
 public abstract Calendar birthDate();


 
 public static final Func1 MAPPER = cursor -> {
 Calendar calendar = Calendar.getInstance();
 calendar.setTimeInMillis( cursor.getLong(cursor.getColumnIndexOrThrow(BIRTH_DATE)));
 return new AutoValue_User(
 cursor.getLong(cursor.getColumnIndexOrThrow(USER_ID)),
 cursor.getString(cursor.getColumnIndexOrThrow(NAME)),
 cursor.getString(cursor.getColumnIndexOrThrow(PHOTO_URL)),
 calendar
 );
 };



 
 public static final Func1 MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new); 



 public static final class Builder {
 private final ContentValues contentValues = new ContentValues();
 
 public Builder userId(long userId) {
 contentValues.put(USER_ID, userId);
 return this;
 }
 
 public Builder name(String name) {
 contentValues.put(NAME, name);
 return this;
 }
 
 public Builder photoUrl(String photoUrl) {
 contentValues.put(PHOTO_URL, photoUrl);
 return this;
 }
 
 public Builder birthDate(Calendar birthDate) {
 contentValues.put(BIRTH_DATE, birthDate.getTimeInMillis());
 return this;
 }
 



 public static final class Marshal extends User_Marshal { }

@AutoValue public abstract class User {
 public static final String TABLE_NAME = "user";
 
 public static final String USER_ID = "user_id";
 public static final String NAME = "name";
 public static final String PHOTO_URL = "photo_url";
 public static final String BIRTH_DATE = "birth_date";
 
 public static final String CREATE_TABLE = format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL, BIRTH_DATE);
 
 @Column(USER_ID) public abstract long userId();
 @Column(NAME) public abstract String name();
 @Column(PHOTO_URL) public abstract String photoUrl();
 @Column(value = BIRTH_DATE, marshalMapper = CalendarMarshalMapper.class)
 public abstract Calendar birthDate(); 
 public static final Func1 MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new); 
 public static final class Marshal extends User_Marshal { }

MarshalMappers • Uses reflection for custom types • Still has the AutoValue “fourth wall” • No real direction

The Turning Point • IntelliJ plugin vs Annotation Processor • Schema cannot be in java • Why not just use SQL? • Only additional information needed for java creation is type

@AutoValue public abstract class User {
 public static final String TABLE_NAME = "user";
 
 public static final String USER_ID = "user_id";
 public static final String NAME = "name";
 public static final String PHOTO_URL = "photo_url";
 public static final String BIRTH_DATE = “birth_date"; 
 
 public static final String CREATE_TABLE = String.format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL, BIRTH_DATE);
 
 @Column(USER_ID) public abstract long userId();
 @Column(NAME) public abstract String name();
 @Column(PHOTO_URL) public abstract String photoUrl();
 CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT NOT NULL
 );

@AutoValue public abstract class User {
 public static final String CREATE_TABLE = format("CREATE TABLE %s (" //
 + "%s INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL," //
 + "%s TEXT NOT NULL" //
 + ")", TABLE_NAME, USER_ID, NAME, PHOTO_URL, BIRTH_DATE);
 
 @Column(USER_ID) public abstract long userId();
 @Column(NAME) public abstract String name();
 @Column(PHOTO_URL) public abstract String photoUrl();
 @Column(value = BIRTH_DATE, marshalMapper = CalendarMarshalMapper.class)
 public abstract Calendar birthDate();
 
 public static final Func1 MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new);
 
 public static final class Marshal extends User_Marshal { }
 } CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT NOT NULL
 );

@AutoValue public abstract class User {
 public abstract long userId();
 public abstract String name();
 public abstract String photoUrl();
 public abstract Calendar birthDate(); 
 public static final Func1 MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new);
 
 public static final class Marshal extends User_Marshal { }
 } CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT NOT NULL
 );

@AutoValue public abstract class User {
 public abstract long userId();
 public abstract String name();
 public abstract String photoUrl();
 public abstract Calendar birthDate(); 
 public static final Func1 MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new);
 
 public static final class Marshal extends User_Marshal { }
 } CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT AS java.util.Calendar NOT NULL
 );

@AutoValue public abstract class User implements UserModel {
 public static final UserMapper MAPPER = new UserMapper( AutoValue_User::new, CALENDAR_ADAPTER);
 
 public static final class Marshal extends UserMarshal { Marshal() {
 super(CALENDAR_ADAPTER);
 } }
 } CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT AS java.util.Calendar NOT NULL
 );

CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT AS java.util.Calendar NOT NULL
 ); select_squares:
 SELECT *
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan');

What we want: Immutability Type Safety Compiler Errors SQLite features

What we want: Immutability Type Safety Compiler Errors SQLite features

Type Safety select_squares:
 SELECT *
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); • Already know table schemas • NoColumnDefinition name

A Proper IDE select_squares:
 SELECT *
 FROM user
 WHERE | IN ('John', 'Nelson', 'Logan'); • IntelliJ features |

A Proper IDE select_squares:
 SELECT *
 FROM user
 WHERE | IN ('John', 'Nelson', 'Logan'); • IntelliJ features • Enables fast query development |

Projections select_squares:
 SELECT *
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); • SELECT * SQL Anti-pattern SELECT * select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); SELECT name, photo_url

Projections select_squares:
 SELECT *
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); • SELECT * SQL Anti-pattern • Generate mapper per query SELECT * select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); SELECT name, photo_url

Projections select_squares:
 SELECT *
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); • SELECT * SQL Anti-pattern • Generate mapper per query • Creates strong bond between view and query SELECT * select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); SELECT name, photo_url

select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name IN ('John', 'Nelson', 'Logan'); Benefits • Shared Language • Confidence writing SQL • Tight coupling with views • Easier code review

select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name IN (?, ?, ?); What’s next? (?, ?, ?) public SqlDelightStatement select_squares( String name1, String name2, String name3);

select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name IN ?; What’s next? ? public SqlDelightStatement select_squares( String name1, String name2, String name3); public SqlDelightStatement select_squares(String[] name);

What’s next? select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name = :name OR last_name = :name; name = :name OR last_name = :name public SqlDelightStatement select_squares( String name1, String name2, String name3); public SqlDelightStatement select_squares(String[] name); public SqlDelightStatement select_squares(String name);

• Migration Verification • Testing support • Kotlin What’s next? select_squares:
 SELECT *
 FROM user
 WHERE name = :name OR last_name = :name;

SQLBrite select_squares:
 SELECT *
 FROM user
 WHERE name = :name OR last_name = :name; user

• Table names known SQLBrite select_squares:
 SELECT *
 FROM user
 WHERE name = :name OR last_name = :name; user

• Table names known • So are filters SQLBrite select_squares:
 SELECT *
 FROM user
 WHERE name = :name OR last_name = :name; user

first_name last_name email github twitter melee_tag password Alec Strong [email protected] AlecStrong @Strongolopolis RewinD hunter2 contact_info