SELECT___SQLDelight_JOIN_Multiplatform.pdf

Slide 1

Slide 1 text

SELECT * SQLDelight JOIN Multiplatform Rakhimov Andrii Lead Android Engineer at Lalafo [email protected]

Slide 2

Slide 2 text

Agenda ➔ Our story towards SQLite ➔ Why SQLDelight? ➔ Demo ➔ Future ➔ Winner

Slide 3

Slide 3 text

Story

Slide 4

Slide 4 text

Story Categories

Slide 5

Slide 5 text

Story Categories Countries

Slide 6

Slide 6 text

Story Internal Analytics Categories Countries

Slide 7

Slide 7 text

Story Internal Analytics Categories Countries Favorite Ads

Slide 8

Slide 8 text

Story Internal Analytics Categories Countries Chats Messages Favorite Ads

Slide 9

Slide 9 text

Forever...

Slide 10

Slide 10 text

Shared Preferences [{"id":1,"name":"Azərbaycan","code":"AZ","default_language":"az_AZ","alias":"azerbaijan","d omain":"lalafo.az","time_zone":"Asia/Baku"...}]

Slide 11

Slide 11 text

Shared Preferences + Elementary

Slide 12

Slide 12 text

Shared Preferences + Elementary - Slow, rewrites ﬁle upon insert

Slide 13

Slide 13 text

Shared Preferences + Elementary - Slow, rewrites ﬁle upon insert - Manual sorting and ﬁltering of data

Slide 14

Slide 14 text

Shared Preferences + Elementary - Slow, rewrites ﬁle upon insert - Manual sorting and ﬁltering of data - Data duplication

Slide 15

Slide 15 text

Shared Preferences + Elementary - Slow, rewrites ﬁle upon insert - Manual sorting and ﬁltering of data - Data duplication - Threading

Slide 16

Slide 16 text

What to choose? ORMs Object Databases Room SQLDelight Realm ?

Slide 17

Slide 17 text

Why SQLDelight?

Slide 18

Slide 18 text

Typesafe

Slide 19

Slide 19 text

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_ﬂoat REAL AS Float -- Stored as REAL in db, retrieved as Float );

Slide 20

Slide 20 text

Custom Column Types import ...Message; CREATE TABLE messages( _id TEXT NOT NULL PRIMARY KEY, message BLOB AS Message ); class SerializeAdapter : ColumnAdapter { 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()) )

Slide 21

Slide 21 text

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()) )

Slide 22

Slide 22 text

SQL ﬁrst

Slide 23

Slide 23 text

SQLite statements getMessages: SELECT * FROM messages WHERE chat_id = ? ORDER BY creation_time DESC LIMIT 60 val messages: List = droidconDb.messagesQueries .getMessages(chatId) .executeAsList()

Slide 24

Slide 24 text

Simplicity

Slide 25

Slide 25 text

SQLite statements removeMessage: DELETE FROM messages WHERE _id = ?; droidconDb.messagesQueries.removeMessage(messageId)

Slide 26

Slide 26 text

Rx/coroutines support

Slide 27

Slide 27 text

Rx val messagesStream = droidconDb.messagesQueries .getMessages(chatId) .asObservable(Schedulers.io()) .mapToList()

Slide 28

Slide 28 text

Coroutines val messagesStream = droidconDb.messagesQueries .getMessages(chatId) .asFlow() .mapToList(Dispatchers.IO)

Slide 29

Slide 29 text

Other features

Slide 30

Slide 30 text

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, ... );

Slide 31

Slide 31 text

Migrations

Slide 32

Slide 32 text

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()

Slide 33

Slide 33 text

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()) )...

Slide 34

Slide 34 text

IDE support / Full Kotlin

Slide 35

Slide 35 text

Demo

Slide 36

Slide 36 text

SQLDelight internals

Slide 37

Slide 37 text

SQLDelight internals

Slide 38

Slide 38 text

Room internals

Slide 39

Slide 39 text

Room internals

Slide 40

Slide 40 text

Multiplatform

Slide 41

Slide 41 text

● Proper SQLite abstraction

Slide 42

Slide 42 text

● Proper SQLite abstraction

Slide 43

Slide 43 text

Proper SQLite abstraction

Slide 44

Slide 44 text

SQLDriver Proper SQLite abstraction

Slide 45

Slide 45 text

Cool! Proper SQLite abstraction

Slide 46

Slide 46 text

Proper SQLite abstraction ➔ Extensible, i.e. can easily swap platform or Android SQLite implementation

Slide 47

Slide 47 text

Proper SQLite abstraction ➔ Extensible, i.e. can easily swap platform or Android SQLite implementation ➔ Unit-Testable

Slide 48

Slide 48 text

Driver val driver: SqlDriver = AndroidSqliteDriver(Database.Schema, context, "test.db")

Slide 49

Slide 49 text

Multiplatform Schema & Statements Generated Code SqlDriver AndroidSqlDriver NativeSqlDriver

Slide 50

Slide 50 text

Multiplatform val driver: SqlDriver = NativeSqliteDriver(Database.Schema, "test.db")

Slide 51

Slide 51 text

What about production?

Slide 52

Slide 52 text

1 year/6 projects https://medium.com/@icerock

Slide 53

Slide 53 text

Why SQLDelight? ➔ Typesafe ➔ SQLite ﬁrst ➔ Simplicity ➔ Rx/coroutines support ➔ IDE support ➔ Full Kotlin ➔ Multiplatform

Slide 54

Slide 54 text

Future

Slide 55

Slide 55 text

Other SQL dialects

Slide 56

Slide 56 text

Take responsibility ●

Slide 57

Slide 57 text

Healthy competition creates better products

Slide 58

Slide 58 text

No content

Slide 59

Slide 59 text

No content

Slide 60

Slide 60 text

No content

Slide 61

Slide 61 text

No content

Slide 62

Slide 62 text

Fragments

Slide 63

Slide 63 text

Fragments @SuppressWarnings("deprecation") Object getLastCustomNonConﬁgurationInstance()

Slide 64

Slide 64 text

For us SQL-ﬁrst, full Kotlin and multiplatform are big advantages. What about You?

Slide 65

Slide 65 text

Thank you! Questions and answers Rakhimov Andrii Lead Android Engineer at Lalafo [email protected] https://github.com/ar-g