About Affinity between SQLCipher and Room/CoreData

Slide 1

Slide 1 text

No content

Slide 2

Slide 2 text

Agenda - Introductions - Why? - What is SQLCipher? - Android & Room Integration - iOS & CoreData Integration

Slide 3

Slide 3 text

Yuta Takanashi - Android Engineer - Joined LINE in 2020 @shiita_0903 [email protected] - iOS Engineer - Joined LINE in 2017 @brevansio [email protected] Bruce Evans

Slide 4

Slide 4 text

Why? - E2EE (LINE Letter Sealing) covers in-transit data but not local data - User Privacy - Legal Issues

Slide 5

Slide 5 text

What is SQLCipher? https://github.com/sqlcipher/sqlcipher

Slide 6

Slide 6 text

SQLCipher and Room

Slide 7

Slide 7 text

Using SQLite Database in Android - Provide an abstraction layer over SQLite - Less boilerplate code by code generation - Room is strongly recommended instead of low-level API High-level API(AndroidX Room) Low-level API(BOESPJEEBUBCBTFTRMJUF) - Powerful API - Require a great deal of time and effort to use

Slide 8

Slide 8 text

Slide 9

Slide 9 text

Using SQLCipher with Room Just pass 4VQQPSU'BDUPSZ to PQFO)FMQFS'BDUPSZ on 3PPN%BUBCBTF#VJMEFS

Slide 10

Slide 10 text

A problem found during use

Slide 11

Slide 11 text

Library versions during problem Library name Version Room 2.3.0 SQLCipher 4.4.3

Slide 12

Slide 12 text

The cause of the SQLCipher problem

Slide 13

Slide 13 text

Classes in SQLCipher - Encapsulates compilation of SQL statement and release of the compiled statement object OFUTRMDJQIFSEBUBCBTF42-JUF$PNQJMFE4RM TRMJUF@TUNU - An object in native code - Represents a single SQL statement OFUTRMDJQIFSEBUBCBTF42-JUF4UBUFNFOU - A pre-compiled statement that can be reused - The statement can return 1x1 result sets 42-JUF$PNQJMFE4RM 42-JUF4UBUFNFOU TRMJUF@TUNU １１１１

Slide 14

Slide 14 text

The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql

Slide 15

Slide 15 text

The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql

Slide 16

Slide 16 text

The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql

Slide 17

Slide 17 text

The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql

Slide 18

Slide 18 text

The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql

Slide 19

Slide 19 text

The cause of the SQLCipher problem SFMFBTF4RM4UBUFNFOU is called when 42-JUF4UBUFNFOU is closed (42-JUF4UBUFNFOU manages 42-JUF$PNQJMFE4RM) GJOBMJ[F in 42-JUF$PNQJMFE4RM is called

Slide 20

Slide 20 text

The cause of the SQLCipher problem SFMFBTF4RM4UBUFNFOU is called when 42-JUF4UBUFNFOU is closed (42-JUF4UBUFNFOU manages 42-JUF$PNQJMFE4RM) GJOBMJ[F in 42-JUF$PNQJMFE4RM is called

Slide 21

Slide 21 text

The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql

Slide 22

Slide 22 text

The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql

Slide 23

Slide 23 text

The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql

Slide 24

Slide 24 text

The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql

Slide 25

Slide 25 text

The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql

Slide 26

Slide 26 text

The cause of the Room problem

Slide 27

Slide 27 text

The cause of the Room problem Generated code from DAO function with @Query - INSERT/UPDATE/DELETE query - Pass a collection of parameters to a query

Slide 28

Slide 28 text

The cause of the Room problem

Slide 29

Slide 29 text

The cause of the Room problem

Slide 30

Slide 30 text

The cause of the Room problem

Slide 31

Slide 31 text

The cause of the Room problem

Slide 32

Slide 32 text

The cause of the Room problem

Slide 33

Slide 33 text

The cause of the Room problem

Slide 34

Slide 34 text

The cause of the Room problem

Slide 35

Slide 35 text

The cause of the Room problem

Slide 36

Slide 36 text

The cause of the Room problem

Slide 37

Slide 37 text

Summary of causes 42-JUF$PNQJMFE4RM that isn't closed is released in GJOBMJ[F - DB lock can't be acquired in time - Some generated code by Room doesn't call DMPTF

Slide 38

Slide 38 text

Handling in LINE Android application We considered two workarounds for usage of Room - Use low-level API instead of Room - Use @RawQuery instead of @Query

Slide 39

Slide 39 text

Use low-level API instead of Room Generated code has problem, so replace it with low-level API

Slide 40

Slide 40 text

Use @RawQuery instead of @Query Generated code with @RawQuery calls close()

Slide 41

Slide 41 text

Use @RawQuery instead of @Query Generated code with @RawQuery calls close()

Slide 42

Slide 42 text

Use @RawQuery instead of @Query Generated code with @RawQuery calls close()

Slide 43

Slide 43 text

Use @RawQuery instead of @Query Generated code with @RawQuery calls close()

Slide 44

Slide 44 text

Use @RawQuery instead of @Query The parameter of the function with @RawQuery

Slide 45

Slide 45 text

Use @RawQuery instead of @Query The parameter of the function with @RawQuery

Slide 46

Slide 46 text

Use @RawQuery instead of @Query The parameter of the function with @RawQuery

Slide 47

Slide 47 text

Use @RawQuery instead of @Query The parameter of the function with @RawQuery

Slide 48

Slide 48 text

Use @RawQuery instead of @Query The return value of the function with @RawQuery

Slide 49

Slide 49 text

Use @RawQuery instead of @Query The return value of the function with @RawQuery

Slide 50

Slide 50 text

Use @RawQuery instead of @Query The return value of the function with @RawQuery

Slide 51

Slide 51 text

Use @RawQuery instead of @Query

Slide 52

Slide 52 text

Handling in LINE Android application We considered two workarounds for usage of Room - Use low-level API instead of Room - Use @RawQuery instead of @Query

Slide 53

Slide 53 text

Summary of Android side - Specific conditions on Room and SQLCipher cause an exception - We decided to use a workaround with @RawQuery - We should use room better than low-level API

Slide 54

Slide 54 text

SQLCipher and CoreData

Slide 55

Slide 55 text

CoreData Structure /4.BOBHFE0CKFDU$POUFYU /41FSTJTUFOU4UPSF$PPSEJOBUPS /41FSTJTUFOU4UPSF

Slide 56

Slide 56 text

Using a Custom NSPersistentStore

Slide 57

Slide 57 text

Using a Custom NSPersistentStore

Slide 58

Slide 58 text

Using a Custom NSPersistentStore

Slide 59

Slide 59 text

Using a Custom NSPersistentStore

Slide 60

Slide 60 text

Importing SQLCipher - Must add ALL the linker flags - MUST be linked instead of SQLite

Slide 61

Slide 61 text

No content

Slide 62

Slide 62 text

Setup

Slide 63

Slide 63 text

Setup

Slide 64

Slide 64 text

Setup

Slide 65

Slide 65 text

Setup

Slide 66

Slide 66 text

Creating a New Database

Slide 67

Slide 67 text

Creating a New Database

Slide 68

Slide 68 text

Creating a New Database

Slide 69

Slide 69 text

Creating a New Database

Slide 70

Slide 70 text

Creating Entity Tables

Slide 71

Slide 71 text

Creating Entity Tables

Slide 72

Slide 72 text

Creating Entity Tables

Slide 73

Slide 73 text

Creating Entity Tables

Slide 74

Slide 74 text

Creating Entity Tables

Slide 75

Slide 75 text

Creating Entity Tables

Slide 76

Slide 76 text

Creating Relationship Tables

Slide 77

Slide 77 text

Creating Relationship Tables

Slide 78

Slide 78 text

Creating Relationship Tables

Slide 79

Slide 79 text

Creating Indexes

Slide 80

Slide 80 text

Creating Indexes

Slide 81

Slide 81 text

Creating Indexes

Slide 82

Slide 82 text

Creating Indexes

Slide 83

Slide 83 text

Converting NSManagedObject into NSManagedObjectID

Slide 84

Slide 84 text

Converting NSManagedObject into NSManagedObjectID

Slide 85

Slide 85 text

Converting NSManagedObject into NSManagedObjectID

Slide 86

Slide 86 text

Converting NSManagedObject into NSManagedObjectID

Slide 87

Slide 87 text

Fetching and Saving

Slide 88

Slide 88 text

Fetching

Slide 89

Slide 89 text

Fetch NSManagedObjectIDs

Slide 90

Slide 90 text

Fetch NSManagedObjectIDs

Slide 91

Slide 91 text

Fetch NSManagedObjectIDs

Slide 92

Slide 92 text

Fetch NSManagedObjectIDs

Slide 93

Slide 93 text

Saving

Slide 94

Slide 94 text

Saving

Slide 95

Slide 95 text

Insertions

Slide 96

Slide 96 text

Insertions

Slide 97

Slide 97 text

Insertions

Slide 98

Slide 98 text

Insertions

Slide 99

Slide 99 text

Loading Attributes

Slide 100

Slide 100 text

Querying Attributes

Slide 101

Slide 101 text

Querying Attributes

Slide 102

Slide 102 text

Querying Attributes

Slide 103

Slide 103 text

Loading Relationships

Slide 104

Slide 104 text

toOne Relationships

Slide 105

Slide 105 text

toOne Relationships

Slide 106

Slide 106 text

toMany Relationships

Slide 107

Slide 107 text

iOS Summary - Setup - Database Creation - Converting IDs - Fetching and Saving

Slide 108

Slide 108 text

Summary - Introductions - Why? - What is SQLCipher - Android & Room Integration - iOS & CoreData Integration

Slide 109

Slide 109 text

Thank you