Data model on Android

 Jerzy Chalupski chalup, futuresimple  [email protected] Data model on
Android Base CRM http://porcupineprogrammer.blogspot.com/ 

Data model on Android 61 releases 200+ migrations 50 models
~90 relations ~2 years on Google Play ~100k LOC

futuresimple chalup

UI + Services ContentProvider Database

UI + Services ContentProvider Database BOILERPLATE UNFRIENDLY APIS

UI + Services ContentProvider Database BOILERPLATE BOILERPLATE UNFRIENDLY APIS

UI + Services ContentProvider Database BOILERPLATE BOILERPLATE UNFRIENDLY APIS MIGRATIONS
BOILERPLATE

UI + Services ContentProvider Database BOILERPLATE UNFRIENDLY APIS

public class Address { public Long id; public String region;
public String zip; public String country; public String city; public String street; } @Override public ContentValues getContentValues() { ContentValues values = new ContentValues(); values.put(Addresses.ID, id); values.put(Addresses.REGION, region); values.put(Addresses.ZIP, zip); values.put(Addresses.COUNTRY, country); values.put(Addresses.CITY, city); values.put(Addresses.STREET, street); return values; } POJOs with android.content.*

public class Address { public Long id; public String region;
public String zip; public String country; public String city; public String street; } public static Address getObjectFromCursor(Cursor c) { Address object = new Address(); int cId = c.getColumnIndex(Addresses.ID); object.id = c.isNull(cId) ? null : c.getLong(cId); object.region = c.getString(c.getColumnIndex(Addresses.REGION)); object.zip = c.getString(c.getColumnIndex(Addresses.ZIP)); object.country = c.getString(c.getColumnIndex(Addresses.COUNTRY)); object.city = c.getString(c.getColumnIndex(Addresses.CITY)); object.street = c.getString(c.getColumnIndex(Addresses.STREET)); return object; } POJOs with android.content.*

MINI QUIZ TIME! What’s wrong with this code? Cursor c
= /* some valid cursor */; boolean isDirty = c.getBoolean(c.getColumnIndex("is_dirty"));

MINI QUIZ TIME! What’s wrong with this code? “Cannot resolve
method Cursor.getBoolean(int)” Cursor c = /* some valid cursor */; boolean isDirty = c.getBoolean(c.getColumnIndex("is_dirty"));

MINI QUIZ TIME! ContentValues values = new ContentValues(); values.put("is_dirty", true);
What’s wrong with this code? “Cannot resolve method Cursor.getBoolean(int)” Cursor c = /* some valid cursor */; boolean isDirty = c.getBoolean(c.getColumnIndex("is_dirty"));

MINI QUIZ TIME! ContentValues values = new ContentValues(); values.put("is_dirty", true);
boolean isDirty = c.getInt(c.getColumnIndex("is_dirty")) == 1; What’s wrong with this code? “Cannot resolve method Cursor.getBoolean(int)” Cursor c = /* some valid cursor */; boolean isDirty = c.getBoolean(c.getColumnIndex("is_dirty"));

public class Address { @Column(Addresses.ID) public Long id; @Column(Addresses.REGION) public
String region; @Column(Addresses.ZIP) public String zip; @Column(Addresses.COUNTRY) public String country; @Column(Addresses.CITY) public String city; @Column(Addresses.STREET) public String street; } MicroOrm microOrm = new MicroOrm(); Address address = microOrm.fromCursor(cursor, Address.class); ContentValues values = microOrm.toContentValues(address); POJOs with MicroOrm

 chalup/microorm POJOs with MicroOrm List<Address> addresses = Lists.newArrayList(); if
(c != null && c.moveToFirst()) { do { addresses.add(Address.getObjectFromCursor(c)); } while (c.moveToNext()); } BEFORE: AFTER: List<Address> addresses = microOrm.listFromCursor(c, Address.class);

MINI QUIZ TIME! How many parameters there is in ContentResolver’s
query method? resolver.query(uri, ?);

MINI QUIZ TIME! How many parameters there is in ContentResolver’s
query method? resolver.query(uri, null, null, null, null);

MINI QUIZ TIME! resolver.query(uri, null, null, null, null); resolver.query(uri, null,
null, null, null, null); // API 16 How many parameters there is in ContentResolver’s query method?

null, null, null, null); // API 16 How many parameters there is in ContentResolver’s query method? What about SQLiteDatabase? db.query(table, ?);

null, null, null, null); // API 16 How many parameters there is in ContentResolver’s query method? What about SQLiteDatabase? db.query(table, null, null, null, null, null, null); db.query(table, null, null, null, null, null, null, null); db.query(true, table, null, null, null, null, null, null, null); db.query(true, table, null, null, null, null, null, null, null, null);

ContentResolver API resolver.query(uri, null, null, null, null); resolver.query(uri, null, "is_dirty
= ?", new String[] { String.valueOf(1) }, null ); ContentValues values = new ContentValues(); values.put("is_dirty", true); resolver.update(uri, values, null, null);

 futuresimple/android-db-commons ProviderAction.query(uri).perform(resolver); FluentCursor fluentCursor = ProviderAction .query(uri) .where("is_dirty =
?", 1) .perform(resolver); fluentCursor.toFluentIterable(new Function<Cursor, T>() { /* ... */ }); ProviderAction .update(uri) .value("is_dirty", true) .perform(resolver); ProviderAction API

@Override public Loader<Cursor> onCreateLoader(int id, Bundle args) { CursorLoader loader
= new CursorLoader(getActivity()); loader.setUri(Contacts.CONTENT_URI); loader.setSelection("is_dirty = ?"); loader.setSelectionArgs(new String[] { String.valueOf(1) }); return loader; } @Override public void onLoadFinished(Loader<Cursor> loader, Cursor c) { if (c != null && c.moveToFirst()) { do { // interesting code } while (c.moveToNext()); } } CursorLoader API

CursorLoaderBuilder API @Override public Loader<Cursor> onCreateLoader(int id, Bundle args) {
return CursorLoaderBuilder .forUri(Contacts.CONTENT_URI) .where("is_dirty = ?", 1) .build(getActivity()); }  futuresimple/android-db-commons

 futuresimple/android-db-commons @Override public Loader<List<Long>> onCreateLoader(int id, Bundle args) {
return CursorLoaderBuilder .forUri(Contacts.CONTENT_URI) .transform(new Function<Cursor, Long>() { /* ... */ }) .build(getActivity()); } @Override public Loader<Long> onCreateLoader(int id, Bundle args) { return CursorLoaderBuilder .forUri(Contacts.CONTENT_URI) .wrap(new Function<Cursor, Long>() { /* ... */ }) .build(getActivity()); } CursorLoaderBuilder API

UI + Services ContentProvider Database MIGRATIONS BOILERPLATE

BACKEND MOBILE

BACKEND MOBILE 250k items?

BACKEND MOBILE ?

BACKEND MOBILE

BACKEND MOBILE OFFLINE MODE = DATA MIGRATIONS

SQLite’s ALTER TABLE no ALTER COLUMN no DROP COLUMN no
touching of PRIMARY KEY no ADD/DROP/ALTER CONSTRAINT

CREATE TABLE users ( _id INTEGER PRIMARY KEY, email TEXT,
first_name TEXT, last_name TEXT ); CREATE TABLE users ( _id INTEGER PRIMARY KEY, email TEXT, - first_name TEXT, - last_name TEXT + name TEXT ); SQLite’s ALTER TABLE ?

first_name TEXT, last_name TEXT ); CREATE TABLE users ( _id INTEGER PRIMARY KEY, email TEXT, - first_name TEXT, - last_name TEXT + name TEXT ); SQLite’s ALTER TABLE ALTER TABLE users RENAME TO tmp;

first_name TEXT, last_name TEXT ); CREATE TABLE users ( _id INTEGER PRIMARY KEY, email TEXT, - first_name TEXT, - last_name TEXT + name TEXT ); SQLite’s ALTER TABLE ALTER TABLE users RENAME TO tmp; CREATE TABLE users ( _id INTEGER PRIMARY KEY, email TEXT, name TEXT );

first_name TEXT, last_name TEXT ); CREATE TABLE users ( _id INTEGER PRIMARY KEY, email TEXT, - first_name TEXT, - last_name TEXT + name TEXT ); SQLite’s ALTER TABLE ALTER TABLE users RENAME TO tmp; CREATE TABLE users ( _id INTEGER PRIMARY KEY, email TEXT, name TEXT ); INSERT INTO users (_id, email) SELECT _id, email FROM tmp;

first_name TEXT, last_name TEXT ); CREATE TABLE users ( _id INTEGER PRIMARY KEY, email TEXT, - first_name TEXT, - last_name TEXT + name TEXT ); SQLite’s ALTER TABLE ALTER TABLE users RENAME TO tmp; CREATE TABLE users ( _id INTEGER PRIMARY KEY, email TEXT, name TEXT ); INSERT INTO users (_id, email) SELECT _id, email FROM tmp; DROP TABLE tmp;

 futuresimple/android-schema-utils Sane migrations API CREATE TABLE users ( _id
INTEGER PRIMARY KEY, email TEXT, first_name TEXT, last_name TEXT ); CREATE TABLE users ( _id INTEGER PRIMARY KEY, email TEXT, - first_name TEXT, - last_name TEXT + name TEXT ); TableMigration migration = TableMigration .of(Tables.USERS) .to(CREATE_TABLE_USERS) .withMapping(/* ... */) .build(); mMigrationsHelper.performMigrations(db, migration);

CREATE TABLE users ( _id INTEGER PRIMARY KEY, email TEXT
); Migrations gotcha v1

); + first_name TEXT, + last_name TEXT Migrations gotcha v1 v5

); + first_name TEXT, + last_name TEXT TableMigration .of(Tables.USERS) .to(CREATE_TABLE_USERS) .build(); Migrations gotcha v1 v5

); - email TEXT + email TEXT NOT NULL + first_name TEXT, + last_name TEXT TableMigration .of(Tables.USERS) .to(CREATE_TABLE_USERS) .build(); Migrations gotcha v1 v5 v9

); - email TEXT + email TEXT NOT NULL + first_name TEXT, + last_name TEXT TableMigration .of(Tables.USERS) .to(CREATE_TABLE_USERS) .build(); db.execSQL( ”DELETE FROM users” + ”WHERE email IS NULL” ); TableMigration .of(Tables.USERS) .to(CREATE_TABLE_USERS) .build(); Migrations gotcha v1 v5 v9

); - email TEXT + email TEXT NOT NULL + first_name TEXT, + last_name TEXT TableMigration .of(Tables.USERS) .to(CREATE_TABLE_USERS) .build(); db.execSQL( ”DELETE FROM users” + ”WHERE email IS NULL” ); TableMigration .of(Tables.USERS) .to(CREATE_TABLE_USERS) .build(); Migrations gotcha v1 v5 v9 HAVE TO USE v5 SCHEMA

MIGRATIONS == OLD SCHEMAS

MINI QUIZ TIME! How would you keep old db schemas?

CREATE_TABLE_X_r1500

CREATE_TABLE_X_r1500 NO DIFFS

CREATE_TABLE_X_r1500 List of upgrades NO DIFFS

CREATE_TABLE_X_r1500 List of upgrades NO CURRENT SCHEMA NO DIFFS

CREATE_TABLE_X_r1500 List of upgrades Current schema with list of downgrades NO CURRENT SCHEMA NO DIFFS

CREATE_TABLE_X_r1500 List of upgrades Current schema with list of downgrades NO DIFFS NO CURRENT SCHEMA UNINTUITIVE

Sane(ish?) schema API private static final Schemas SCHEMA = Schemas.Builder
.currentSchema(10, new TableDefinition(Tables.ADDRESSES, new AddColumn(Addresses.REGION, "TEXT"), new AddColumn(Addresses.ZIP, "TEXT"), new AddColumn(Addresses.COUNTRY, "TEXT"), new AddColumn(Addresses.CITY, "TEXT"), new AddColumn(Addresses.STREET, "TEXT ") ) ) .upgradeTo(7, clear(Tables.ADDRESSES)) .downgradeTo(4, new TableDowngrade(Tables.ADDRESSES, new DropColumn(Addresses.REGION) ) ) .downgradeTo(2, dropTable(Tables.ADDRESSES)) .build();  futuresimple/android-schema-utils

Look ma, no boilerplate! @Override public void onCreate(SQLiteDatabase db) {
Schema currentSchema = SCHEMAS.getCurrentSchema(); for (String table : currentSchema.getTables()) { db.execSQL(currentSchema.getCreateTableStatement(table)); } } public void onUpgrade(final SQLiteDatabase db, int oldVersion, int newVersion) { SCHEMAS.upgrade(oldVersion, mContext, db); }  futuresimple/android-schema-utils

UI + Services ContentProvider Database MIGRATIONS BOILERPLATE

Obvious index is obvious db.execSQL("CREATE TABLE " + Tables.RAW_CONTACTS +
" (" + // ... RawContacts.CONTACT_ID + " INTEGER REFERENCES contacts(_id)," + // ... ");"); db.execSQL("CREATE INDEX raw_contacts_contact_id_index ON " + Tables.RAW_CONTACTS + " (" + RawContacts.CONTACT_ID + ");");

“This would be easy if we knew the relations between
our models...”

Thneed public static final ModelGraph<ModelInterface> MODEL_GRAPH = ModelGraph .of(ModelInterface.class) .identifiedByDefault().by(ModelColumns.ID)
.where() .the(CONTACT).references(USER).by(Contacts.USER_ID) .the(CONTACT).groupsOther().by(Contacts.CONTACT_ID) .the(DEAL).references(CONTACT).by(Deals.CONTACT_ID) .the(DEAL).references(USER).by(Deals.USER_ID)  chalup/thneed

Thneed public static final ModelGraph<ModelInterface> MODEL_GRAPH = ModelGraph .of(ModelInterface.class) .identifiedByDefault().by(ModelColumns.ID)
.where() .the(CONTACT).references(USER).by(Contacts.USER_ID) .the(CONTACT).groupsOther().by(Contacts.CONTACT_ID) .the(DEAL).references(CONTACT).by(Deals.CONTACT_ID) .the(DEAL).references(USER).by(Deals.USER_ID) public class ModelGraph<TModel> { public void accept(ModelVisitor<? super TModel> visitor); public void accept(RelationshipVisitor<? super TModel> visitor); }  chalup/thneed

 futuresimple/android-autoindexer public class AutoIndexer { public static Collection<SqliteIndex> generateIndexes(ModelGraph<~>
graph); public static String getCreateStatement(SqliteIndex index); } AutoIndexer

 futuresimple/android-autoindexer public class AutoIndexer { public static Collection<SqliteIndex> generateIndexes(ModelGraph<~>
graph); public static String getCreateStatement(SqliteIndex index); } private static void createIndexes(SQLiteDatabase db) { FluentIterable<SqliteIndex> indexes = FluentIterable .from(AutoIndexer.generateIndexes(MODEL_GRAPH)) .filter(Predicates.not(AutoIndexer.isIndexOnColumn(ModelColumns.ID))) .filter(Predicates.not(AutoIndexer.isIndexOnColumn(BaseColumns._ID))); for (SqliteIndex index : indexes) { db.execSQL(AutoIndexer.getCreateStatement(index)); } } AutoIndexer

MINI QUIZ TIME! What are the issues related to schema
generation?

Autogenerate? Autodestruct. sqlite> select * from sqlite_master limit 10; type
name tbl_name rootpage sql ---------- ---------------- ---------------- ---------- ------------------------------------------- table android_metadata android_metadata 3 CREATE TABLE android_metadata (locale TEXT) table stages stages 4 CREATE TABLE stages (_id INTEGER PRIMARY KE index sqlite_autoindex stages 5 table sqlite_sequence sqlite_sequence 6 CREATE TABLE sqlite_sequence(name,seq) table sources sources 7 CREATE TABLE sources (id INTEGER PRIMARY KE table contacts contacts 8 CREATE TABLE contacts (_id INTEGER PRIMARY index sqlite_autoindex contacts 9 table deals deals 10 CREATE TABLE deals (_id INTEGER PRIMARY KEY index sqlite_autoindex deals 11 table notes notes 12 CREATE TABLE notes (_id INTEGER PRIMARY KEY

Autogenerate? Autodestruct. SQLiteMaster.dropIndexes(db); SQLiteMaster.dropTriggers(db); SQLiteMaster.dropViews(db); List<SQLiteSchemaPart> parts = SQLiteMaster.getSQLiteSchemaParts(db); sqlite>
select * from sqlite_master limit 10; type name tbl_name rootpage sql ---------- ---------------- ---------------- ---------- ------------------------------------------- table android_metadata android_metadata 3 CREATE TABLE android_metadata (locale TEXT) table stages stages 4 CREATE TABLE stages (_id INTEGER PRIMARY KE index sqlite_autoindex stages 5 table sqlite_sequence sqlite_sequence 6 CREATE TABLE sqlite_sequence(name,seq) table sources sources 7 CREATE TABLE sources (id INTEGER PRIMARY KE table contacts contacts 8 CREATE TABLE contacts (_id INTEGER PRIMARY index sqlite_autoindex contacts 9 table deals deals 10 CREATE TABLE deals (_id INTEGER PRIMARY KEY index sqlite_autoindex deals 11 table notes notes 12 CREATE TABLE notes (_id INTEGER PRIMARY KEY  futuresimple/sqlitemaster

 futuresimple/sqlitemaster Autogenerate? Autodestruct. SQLiteMaster.dropIndexes(db); SQLiteMaster.dropTriggers(db); SQLiteMaster.dropViews(db); List<SQLiteSchemaPart> parts =
SQLiteMaster.getSQLiteSchemaParts(db); sqlite> select * from sqlite_master limit 10; type name tbl_name rootpage sql ---------- ---------------- ---------------- ---------- ------------------------------------------- table android_metadata android_metadata 3 CREATE TABLE android_metadata (locale TEXT) table stages stages 4 CREATE TABLE stages (_id INTEGER PRIMARY KE index sqlite_autoindex stages 5 table sqlite_sequence sqlite_sequence 6 CREATE TABLE sqlite_sequence(name,seq) table sources sources 7 CREATE TABLE sources (id INTEGER PRIMARY KE table contacts contacts 8 CREATE TABLE contacts (_id INTEGER PRIMARY index sqlite_autoindex contacts 9 table deals deals 10 CREATE TABLE deals (_id INTEGER PRIMARY KEY index sqlite_autoindex deals 11 table notes notes 12 CREATE TABLE notes (_id INTEGER PRIMARY KEY

Forger<ModelInterface> forger; Upload upload = forger .iNeed(Upload.class) .with(Uploads.IS_CACHED, true) .in(mContentResolver);
Byproduct: Forger  futuresimple/forger

Attachment attachment = forger .iNeed(Attachment.class) .relatedTo(upload) .in(mContentResolver); Byproduct: Forger  futuresimple/forger

Forger<ModelInterface> forger; Attachment attachment = forger .iNeed(Attachment.class) // .relatedTo(upload) .in(mContentResolver);
Byproduct: Forger  futuresimple/forger

Attachment attachment = forger .iNeed(Attachment.class) .relatedTo(upload) .in(mContentResolver); Byproduct: Forger  futuresimple/forger

Forger<ModelInterface> forger = FORGER .inContextOf(User.class).in(mContentResolver); Upload upload = forger .iNeed(Upload.class)
.with(Uploads.IS_CACHED, true) .in(mContentResolver); Attachment attachment = forger .iNeed(Attachment.class) .relatedTo(upload) .in(mContentResolver); Byproduct: Forger  futuresimple/forger

 futuresimple/forger @Test public void shouldNotifyAboutReadyShareRequest() throws Exception { FORGER
.inContextOf(User.class).in(mContentResolver) .inContextOf(Contact.class).in(mContentResolver) .inContextOf(Upload.class).in(mContentResolver) .inContextOf(Attachment.class).in(mContentResolver) .inContextOf(ShareDocumentRequest.class).in(mContentResolver) .iNeed(ShareDocumentList.class).in(mContentResolver); testSubject.notifyReadyShareRequests(); verify(mNotificationManager).notify(anyInt(), any(Notification.class)); } Byproduct: Forger

UI + Services ContentProvider Database BOILERPLATE

Work in progress! AutoProvider Uri matching/building, CRUD basic implementation... ...or
something completely replacing ContentProvider

Work in progress! AutoProvider TableJoiner Uri matching/building, CRUD basic implementation...
...or something completely replacing ContentProvider The SQLite helper to end all SQLite helpers!

Work in progress! AutoProvider TableJoiner Cerberus Uri matching/building, CRUD basic
implementation... ...or something completely replacing ContentProvider The SQLite helper to end all SQLite helpers! Attaches itself to database and executes EXPLAIN QUERY PLAN for each query.

Thanks.

Data model on Android

Data model on Android

Transcript

More Decks by Jerzy Chalupski

Other Decks in Programming

Featured