Share
Embed
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
Transcript
77 slides
-
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 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" }); -
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<Cursor, List<User>> mapRow(); public static Func1<Cursor, User> 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<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)); -
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=?; -
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=?; -
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'); -
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 -
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. ); -
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<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)) ); }; -
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<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)) ); }; -
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 ); }; -
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<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; }); -
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); -
SQLBrite • Solved Rx problem • Requires table name and
has false positives -
@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<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 ); }; -
public static final Func1<Cursor, User> 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; } -
@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> { } -
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<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 ); -
@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 ); -
@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 ); -
@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 ); -
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'); -
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