SQLBrite & SQLDelight 2016/09/28

@ara_tack FURYU Corporation. About me

Android୺຤಺ͷ σʔλϕʔε؅ཧ

SQLBrite & SQLDelight

SQLBrite • SQLiteOpenHelperΛϥοϓ͢Δ • ΫΤϦΛrx.Observableͱͯ͠ߪಡͰ͖Δ • ςʔϒϧมߋΛ௨஌ͯ͘͠ΕΔ

SQLDelight • ςʔϒϧ࡞੒ͷSQL͔ΒϞσϧΛੜ੒͢Δ • Ϟσϧ͸JavaͷInterface • Ϟσϧ͕ܕ҆શͳಡΈ/ॻ͖ͷAPIΛఏڙ͢Δ

Philosophy • SQLBrite͸௨஌ͷ࢓૊ΈΛఏڙ͢Δ͚ͩ • SQLDelight͸SQLΛ࢖͍΍͘͢͢Δ͚ͩ • SQLͷར༻Λલఏͱ͍ͯ͠Δ • ORM͡Όͳ͍

Jake͸ORM͕ݏ͍ʁ ORMs are pretty much terrible on every platform. With undefined or ambiguous semantics around subsequent queries they are a hotspot for performance and memory problems. We'll never write an ORM. https://www.reddit.com/r/androiddev/comments/48yieg/ anyone_use_sqlbrite_andor_sqldelight/

࢖͍ํ αϯϓϧίʔυ https://github.com/takuaraki/BriteAndDelight

SqlBrite sqlBrite = SqlBrite.create(); BriteDatabase db = sqlBrite.wrapDatabaseHelper(helper, Schedulers.io()); db.createQuery("users", "SELECT * FROM users").subscribe(new Action1() { @Override public void call(Query query) { // ॳճߪಡ࣌ͱςʔϒϧมߋ(insert,update,delete)࣌ʹݺ͹ΕΔ // query.run()Ͱ"SELECT * FROM users"ͷ࣮ߦ݁Ռ͕ಘΒΕΔ Cursor cursor = query.run(); } }); SQLBrite

db.createQuery("users", "SELECT * FROM users”) .mapToList(new Func1() { @Override public User call(Cursor cursor) { long id = cursor.getLong(0); String name = cursor.getString(1); return new User(id, name); } }).subscribe(new Action1>() { @Override public void call(List users) { // ϢʔβʔϦετͷදࣔͳͲ } }); SQLBrite

SQLDelight CREATE TABLE user (
 _id INTEGER NOT NULL PRIMARY KEY,
 first_name TEXT NOT NULL,
 last_name TEXT NOT NULL
 ); select_all:
 SELECT * FROM user; ྫ) User.sq

User.sq → UserModel.java • ֤छఆ਺: ςʔϒϧ໊ɺΧϥϜ໊ɺSQLจ • Mapper: Cursor→ModelͷϚούʔ • Marshal: ContentValuesͷϏϧμʔ

֤छఆ਺ String TABLE_NAME = "user";
 
 String _ID = "_id";
 String FIRST_NAME = "first_name";
 String LAST_NAME = "last_name";
 
 String CREATE_TABLE = ""
 + "CREATE TABLE user (\n"
 + " _id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n"
 + " first_name TEXT NOT NULL,\n"
 + " last_name TEXT NOT NULL\n"
 + “)"; String SELECT_ALL = ""
 + "SELECT * FROM user";

Mapper final class Mapper implements RowMapper {
 private final Factory userModelFactory;
 
 public Mapper(Factory userModelFactory) {
 this.userModelFactory = userModelFactory;
 }
 
 @Override
 public T map(@NonNull Cursor cursor) {
 return userModelFactory.creator.create(
 cursor.getLong(0),
 cursor.getString(1),
 cursor.getString(2)
 );
 }
 }

Marshal final class Marshal {
 protected final ContentValues contentValues = new ContentValues();
 
 Marshal(@Nullable UserModel copy) {
 if (copy != null) {
 this._id(copy._id());
 this.first_name(copy.first_name());
 this.last_name(copy.last_name());
 }
 }
 
 public ContentValues asContentValues() {
 return contentValues;
 }
 
 public Marshal _id(long _id) {
 contentValues.put(_ID, _id);
 return this;
 } // ҎԼলུ
 }

@AutoValue
 public abstract class User implements UserModel, Parcelable { public static final Factory FACTORY = new Factory<>(AutoValue_User::new);
 
 public static final Func1 MAPPER = cursor -> new Mapper<>(FACTORY).map(cursor); 
 } Modelͷར༻

// SELECT(TABLE.NAME, SELECT_ALL, MapperΛར༻) db.createQuery(User.TABLE_NAME, User.SELECT_ALL)
 .mapToList(User.MAPPER)
 .subscribeOn(Schedulers.io())
 .observeOn(AndroidSchedulers.mainThread())
 .subscribe(users -> { userAdapter.set(users); }); // INSERT(TABLE_NAME, MarshalΛར༻) db.insert(User.TABLE_NAME, User.FACTORY.marshal()
 .first_name(“taku”)
 .last_name(“araki”)
 .asContentValues()); Modelͷར༻

SQLBrite & SQLDelight ͷಛ௃ • ςʔϒϧఆ͕ٛSQLͰͰ͖Δ • AutoValueͱͷ૬ੑ͕͍͍ • RxJavaͱͷ૬ੑ͕͍͍