About Affinity between SQLCipher and Room/CoreData

Bruce Evans
LINE / LINE Communication service development team / Software Engineer
Yuta Takanashi
LINE / LINE Communication service development team / Software Engineer

https://linedevday.linecorp.com/2021/ja/sessions/127
https://linedevday.linecorp.com/2021/en/sessions/127
https://linedevday.linecorp.com/2021/ko/sessions/127

Transcript

2. Agenda - Introductions - Why? - What is SQLCipher? - Android & Room Integration - iOS & CoreData Integration
3. Yuta Takanashi - Android Engineer - Joined LINE in 2020 @shiita_0903 yuta.takanashi@linecorp.com - iOS Engineer - Joined LINE in 2017 @brevansio bruce.evans@linecorp.com Bruce Evans
4. Why? - E2EE (LINE Letter Sealing) covers in-transit data but not local data - User Privacy - Legal Issues
5. What is SQLCipher? https://github.com/sqlcipher/sqlcipher
6. SQLCipher and Room
7. 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
8. 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
9. Using SQLCipher with Room Just pass 4VQQPSU'BDUPSZ to PQFO)FMQFS'BDUPSZ on 3PPN%BUBCBTF#VJMEFS
10. A problem found during use
11. Library versions during problem Library name Version Room 2.3.0 SQLCipher 4.4.3
12. The cause of the SQLCipher problem
13. 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 １ １ １ １
14. The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql
15. The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql
16. The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql
17. The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql
18. The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql
19. 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
20. 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
21. The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql
22. The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql
23. The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql
24. The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql
25. The cause of the SQLCipher problem net.sqlcipher.database.SQLiteCompiledSql
26. The cause of the Room problem
27. 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
28. The cause of the Room problem
29. The cause of the Room problem
30. The cause of the Room problem
31. The cause of the Room problem
32. The cause of the Room problem
33. The cause of the Room problem
34. The cause of the Room problem
35. The cause of the Room problem
36. The cause of the Room problem
37. 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
38. 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
39. Use low-level API instead of Room Generated code has problem, so replace it with low-level API
40. Use @RawQuery instead of @Query Generated code with @RawQuery calls close()
41. Use @RawQuery instead of @Query Generated code with @RawQuery calls close()
42. Use @RawQuery instead of @Query Generated code with @RawQuery calls close()
43. Use @RawQuery instead of @Query Generated code with @RawQuery calls close()
44. Use @RawQuery instead of @Query The parameter of the function with @RawQuery
45. Use @RawQuery instead of @Query The parameter of the function with @RawQuery
46. Use @RawQuery instead of @Query The parameter of the function with @RawQuery
47. Use @RawQuery instead of @Query The parameter of the function with @RawQuery
48. Use @RawQuery instead of @Query The return value of the function with @RawQuery
49. Use @RawQuery instead of @Query The return value of the function with @RawQuery
50. Use @RawQuery instead of @Query The return value of the function with @RawQuery
51. Use @RawQuery instead of @Query
52. 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
53. 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
54. SQLCipher and CoreData
55. CoreData Structure /4.BOBHFE0CKFDU$POUFYU /41FSTJTUFOU4UPSF$PPSEJOBUPS /41FSTJTUFOU4UPSF
56. Using a Custom NSPersistentStore
57. Using a Custom NSPersistentStore
58. Using a Custom NSPersistentStore
59. Using a Custom NSPersistentStore
60. Importing SQLCipher - Must add ALL the linker flags - MUST be linked instead of SQLite
62. Setup
63. Setup
64. Setup
65. Setup
66. Creating a New Database
67. Creating a New Database
68. Creating a New Database
69. Creating a New Database
70. Creating Entity Tables
71. Creating Entity Tables
72. Creating Entity Tables
73. Creating Entity Tables
74. Creating Entity Tables
75. Creating Entity Tables
76. Creating Relationship Tables
77. Creating Relationship Tables
78. Creating Relationship Tables
79. Creating Indexes
80. Creating Indexes
81. Creating Indexes
82. Creating Indexes
83. Converting NSManagedObject into NSManagedObjectID
84. Converting NSManagedObject into NSManagedObjectID
85. Converting NSManagedObject into NSManagedObjectID
86. Converting NSManagedObject into NSManagedObjectID
87. Fetching and Saving
88. Fetching
89. Fetch NSManagedObjectIDs
90. Fetch NSManagedObjectIDs
91. Fetch NSManagedObjectIDs
92. Fetch NSManagedObjectIDs
93. Saving
94. Saving
95. Insertions
96. Insertions
97. Insertions
98. Insertions
99. Loading Attributes
100. Querying Attributes
101. Querying Attributes
102. Querying Attributes
103. Loading Relationships
104. toOne Relationships
105. toOne Relationships
106. toMany Relationships
107. iOS Summary - Setup - Database Creation - Converting IDs - Fetching and Saving
108. Summary - Introductions - Why? - What is SQLCipher - Android & Room Integration - iOS & CoreData Integration
109. Thank you