SELECT___SQLDelight_JOIN_Multiplatform.pdf

 SELECT___SQLDelight_JOIN_Multiplatform.pdf

E3b64bb9c6c3768e669c6e85df693c9e?s=128

Andrii Rakhimov

September 25, 2019
Tweet

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

  58. None
  59. None
  60. None
  61. None
  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