SELECT___SQLDelight_JOIN_Multiplatform.pdf

Transcript

1. SELECT * SQLDelight JOIN Multiplatform Rakhimov Andrii Lead Android Engineer at Lalafo andrii.rakhimov@gmail.com
2. Agenda ➔ Our story towards SQLite ➔ Why SQLDelight? ➔ Demo ➔ Future ➔ Winner
3. Story
4. Story Categories
5. Story Categories Countries
6. Story Internal Analytics Categories Countries
7. Story Internal Analytics Categories Countries Favorite Ads
8. Story Internal Analytics Categories Countries Chats Messages Favorite Ads
9. Forever...
10. Shared Preferences <?xml version='1.0' encoding='utf-8' standalone='yes' ?> <map> <string name="countries"> [{"id":1,"name":"Azərbaycan","code":"AZ","default_language":"az_AZ","alias":"azerbaijan","d omain":"lalafo.az","time_zone":"Asia/Baku"...}]</string> <boolean name="innertube_safety_mode_enabled" value="false" /> <long name="delayed_event_last_dispatch_time_ms" value="1551718329606" /> </map>
11. Shared Preferences + Elementary
12. Shared Preferences + Elementary - Slow, rewrites file upon insert
13. Shared Preferences + Elementary - Slow, rewrites file upon insert - Manual sorting and filtering of data
14. Shared Preferences + Elementary - Slow, rewrites file upon insert - Manual sorting and filtering of data - Data duplication
15. Shared Preferences + Elementary - Slow, rewrites file upon insert - Manual sorting and filtering of data - Data duplication - Threading
16. What to choose? ORMs Object Databases Room SQLDelight Realm ?
17. Why SQLDelight?
18. Typesafe
19. Schema generation and types CREATE TABLE some_types ( some_long INTEGER NOT NULL PRIMARY KEY, -- Stored as INTEGER in db, retrieved as Long some_double REAL, -- Stored as REAL in db, retrieved as Double some_string TEXT, -- Stored as TEXT in db, retrieved as String some_blob BLOB, -- Stored as BLOB in db, retrieved as ByteArray some_int INTEGER AS Int, -- Stored as INTEGER in db, retrieved as Int some_short INTEGER AS Short, -- Stored as INTEGER in db, retrieved as Short some_float REAL AS Float -- Stored as REAL in db, retrieved as Float );
20. Custom Column Types import ...Message; CREATE TABLE messages( _id TEXT NOT NULL PRIMARY KEY, message BLOB AS Message ); class SerializeAdapter<T : Any> : ColumnAdapter<T, ByteArray> { override fun decode(bytes: ByteArray) = SerializeUtils.deserialize(bytes) as T override fun encode(t: T): ByteArray = SerializeUtils.serialize(t) } val db = DroidconDb.invoke( AndroidSqliteDriver(helper), Messages.Adapter(SerializeAdapter<Message>()) )
21. Enums Import ....SendStatus; CREATE TABLE messages( _id TEXT NOT NULL PRIMARY KEY, status TEXT AS SendStatus ); val db = DroidconDb.invoke( AndroidSqliteDriver(helper), Messages.Adapter(EnumColumnAdapter()) )
22. SQL first
23. SQLite statements getMessages: SELECT * FROM messages WHERE chat_id = ? ORDER BY creation_time DESC LIMIT 60 val messages: List<Messages> = droidconDb.messagesQueries .getMessages(chatId) .executeAsList()
24. Simplicity
25. SQLite statements removeMessage: DELETE FROM messages WHERE _id = ?; droidconDb.messagesQueries.removeMessage(messageId)
26. Rx/coroutines support
27. Rx val messagesStream = droidconDb.messagesQueries .getMessages(chatId) .asObservable(Schedulers.io()) .mapToList()
28. Coroutines val messagesStream = droidconDb.messagesQueries .getMessages(chatId) .asFlow() .mapToList(Dispatchers.IO)
29. Other features
30. Migrations DROP TABLE IF EXISTS chats; DROP TABLE IF EXISTS messages; CREATE TABLE chats( thread_id TEXT NOT NULL PRIMARY KEY, updated INTEGER NOT NULL, ... ); CREATE TABLE messages( _id TEXT NOT NULL PRIMARY KEY, creation_time INTEGER NOT NULL, ... );
31. Migrations
32. Android Paging countMessages: SELECT count(*) FROM messages; getMessages: SELECT * FROM messages LIMIT :limit OFFSET :offset; val messagesQueries = droidconDb.messagesQueries val dataSource = QueryDataSourceFactory( queryProvider = messagesQueries::getMessage, countQuery = messagesQueries.countMessages() ).create()
33. Setup in real app @Provides @Singleton static SupportSQLiteOpenHelper provideSQLiteOpenHelper(App context) { SupportSQLiteOpenHelper.Configuration configuration = SupportSQLiteOpenHelper.Configuration.builder(context) .name(DbCallback.DB_NAME) .callback(new DbCallback(context)) .build(); SupportSQLiteOpenHelper.Factory factory = new FrameworkSQLiteOpenHelperFactory(); return factory.create(configuration); } @Provides @Singleton static DroidconDb provideDb(SupportSQLiteOpenHelper helper) { return DroidconDb.Companion.invoke( new AndroidSqliteDriver(helper), new Categories.Adapter(new SerializeAdapter<>()), ... new Messages.Adapter(new EnumColumnAdapter<>(SendStatus.values()) )...
34. IDE support / Full Kotlin
35. Demo
36. SQLDelight internals
37. SQLDelight internals
38. Room internals
39. Room internals
40. Multiplatform
41. • Proper SQLite abstraction
42. • Proper SQLite abstraction
43. Proper SQLite abstraction
44. SQLDriver Proper SQLite abstraction
45. Cool! Proper SQLite abstraction
46. Proper SQLite abstraction ➔ Extensible, i.e. can easily swap platform or Android SQLite implementation
47. Proper SQLite abstraction ➔ Extensible, i.e. can easily swap platform or Android SQLite implementation ➔ Unit-Testable
48. Driver val driver: SqlDriver = AndroidSqliteDriver(Database.Schema, context, "test.db")
49. Multiplatform Schema & Statements Generated Code SqlDriver AndroidSqlDriver NativeSqlDriver
50. Multiplatform val driver: SqlDriver = NativeSqliteDriver(Database.Schema, "test.db")
51. What about production?
52. 1 year/6 projects https://medium.com/@icerock
53. Why SQLDelight? ➔ Typesafe ➔ SQLite first ➔ Simplicity ➔ Rx/coroutines support ➔ IDE support ➔ Full Kotlin ➔ Multiplatform
54. Future
55. Other SQL dialects
56. Take responsibility •
57. Healthy competition creates better products
62. Fragments
63. Fragments @SuppressWarnings("deprecation") Object getLastCustomNonConfigurationInstance()
64. For us SQL-first, full Kotlin and multiplatform are big advantages. What about You?
65. Thank you! Questions and answers Rakhimov Andrii Lead Android Engineer at Lalafo andrii.rakhimov@gmail.com https://github.com/ar-g