"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()); } }
User.USER_ID)) .where(EQ(Payment.TOKEN, paymentToken)); SELECT * FROM payment JOIN customer USING (customer_token) JOIN user USING (user_id) WHERE token=?;
.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=?;
.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');
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. );
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);
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);
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);
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);
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> { }
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 );
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 );
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 );
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 );
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 );
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');
'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
'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
'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
= :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);