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

Embracing SQL Without Abstraction

Alec Strong
September 30, 2016

Embracing SQL Without Abstraction

The story of how Square came to write SQLBrite and SQLDelight and the lessons we learned along the way.

Video: https://www.youtube.com/watch?v=ksgmm8VolT4

Alec Strong

September 30, 2016
Tweet

More Decks by Alec Strong

Other Decks in Programming

Transcript

  1. What we want: • RxJava • Immutability • Type Safety

    • Compiler Errors • SQLite features
  2. What we’re given: • Android’s SQLite bindings • SQLiteOpenHelper •

    Cursor • SQLiteDatabase • ContentValues • ContentProvider/ContentResolver
  3. What we’re given: db.rawQuery("SELECT * FROM user WHERE name IN

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

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

    
 " WHERE " + User.NAME + " IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" });
  6. Research • Reflection • Synchronous • Lack of Type Safety

    • Underpowered Query APIs • Missing SQLite features
  7. 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();
 }
  8. Scaffold @Table(User.TABLE_NAME)
 public interface User {
 } public static final

    class ScUser implements User {
 public static Func1<Cursor, List<User>> mapRow();
 public static Func1<Cursor, User> single();
 }
  9. 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());
 }
 }
  10. Observable<ScaffoldCursor> 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=?;
  11. Observable<ScaffoldCursor> 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=?;
  12. Observable<ScaffoldCursor> 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');
  13. 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
  14. Not Scaffold • Need to address type safety • IntColumn,

    StringColumn • How do you model scope
  15. 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.
 );
  16. SQLBrite • Won’t do perfect emission • False positives •

    But how do we know when a table is updated? • Require table names
  17. @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);
  18. 
 public static final Func1<Cursor, User> MAPPER = cursor ->

    {
 return new AutoValue_User(
 cursor.getLong(cursor.getColumnIndexOrThrow(USER_ID)),
 cursor.getString(cursor.getColumnIndexOrThrow(NAME)),
 cursor.getString(cursor.getColumnIndexOrThrow(PHOTO_URL))
 );
 };
  19. 
 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
  20. @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);
  21. @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);
  22. 
 public abstract long userId();
 public abstract String name();
 public

    abstract String photoUrl();
 public abstract Calendar birthDate();

  23. 
 public static final Func1<Cursor, User> MAPPER = cursor ->

    {
 return new AutoValue_User(
 cursor.getLong(cursor.getColumnIndexOrThrow(USER_ID)),
 cursor.getString(cursor.getColumnIndexOrThrow(NAME)),
 cursor.getString(cursor.getColumnIndexOrThrow(PHOTO_URL))
 );
 };
  24. 
 public static final Func1<Cursor, User> 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
 );
 };
  25. 
 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
  26. 
 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;

  27. SQLBrite Observable<List<User>> 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<User> users = new ArrayList<>();
 try {
 users.add(User.MAPPER.call(cursor));
 } finally {
 cursor.close();
 } return users;
 });
  28. SQLBrite Observable<List<User>> users = db.createQuery(User.TABLE_NAME,
 "SELECT * FROM " +

    User.TABLE_NAME +
 " WHERE " + User.NAME + " IN (?, ?, ?)",
 new String[] { "John", "Nelson", "Logan" })
 .mapToList(User.MAPPER);
  29. @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);

  30. 
 public abstract long userId();
 public abstract String name();
 public

    abstract String photoUrl();
 public abstract Calendar birthDate();
  31. 
 @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();
  32. 
 
 public static final Func1<Cursor, User> 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
 );
 };

  33. 
 
 public static final Func1<Cursor, User> MAPPER =
 cursor

    -> User_Mapper.map(cursor, AutoValue_User::new); 

  34. 
 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;
 }
 

  35. @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<Cursor, User> MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new); 
 public static final class Marshal extends User_Marshal<Marshal> { }
  36. MarshalMappers • Uses reflection for custom types • Still has

    the AutoValue “fourth wall” • No real direction
  37. 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
  38. @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
 );
  39. @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<Cursor, User> MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new);
 
 public static final class Marshal extends User_Marshal<Marshal> { }
 } CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT NOT NULL
 );
  40. @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<Cursor, User> MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new);
 
 public static final class Marshal extends User_Marshal<Marshal> { }
 } CREATE TABLE user (
 user_id INTEGER PRIMARY KEY AUTOINCREMENT,
 name TEXT NOT NULL,
 photo_url TEXT NOT NULL,
 birth_date TEXT NOT NULL
 );
  41. @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<Cursor, User> MAPPER =
 cursor -> User_Mapper.map(cursor, AutoValue_User::new);
 
 public static final class Marshal extends User_Marshal<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
 );
  42. @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> { 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
 );
  43. 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');
  44. Type Safety select_squares:
 SELECT *
 FROM user
 WHERE name IN

    ('John', 'Nelson', 'Logan'); • Already know table schemas • NoColumnDefinition name
  45. A Proper IDE select_squares:
 SELECT *
 FROM user
 WHERE |

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

    IN ('John', 'Nelson', 'Logan'); • IntelliJ features • Enables fast query development |
  47. 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
  48. 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
  49. 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
  50. 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
  51. select_squares:
 SELECT name, photo_url
 FROM user
 WHERE name IN (?,

    ?, ?); What’s next? (?, ?, ?) public SqlDelightStatement select_squares( String name1, String name2, String name3);
  52. 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);
  53. 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);
  54. • Migration Verification • Testing support • Kotlin What’s next?

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


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