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

1. View Slide

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

   View Slide

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
   

   View Slide

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

   View Slide

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

   View Slide

6. SQLCipher and Room
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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

   View Slide

10. A problem found during use
    

    View Slide

11. Library versions during problem
    Library name Version
    Room 2.3.0
    SQLCipher 4.4.3
    

    View Slide

12. The cause of the SQLCipher problem
    

    View Slide

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
    １
    １
    １
    １
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

26. The cause of the Room problem
    

    View Slide

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
    

    View Slide

28. The cause of the Room problem
    

    View Slide

29. The cause of the Room problem
    

    View Slide

30. The cause of the Room problem
    

    View Slide

31. The cause of the Room problem
    

    View Slide

32. The cause of the Room problem
    

    View Slide

33. The cause of the Room problem
    

    View Slide

34. The cause of the Room problem
    

    View Slide

35. The cause of the Room problem
    

    View Slide

36. The cause of the Room problem
    

    View Slide

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
    
    

    View Slide

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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

51. Use @RawQuery instead of @Query
    

    View Slide

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
    

    View Slide

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
    

    View Slide

54. SQLCipher and CoreData
    

    View Slide

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

    View Slide

56. Using a Custom NSPersistentStore
    

    View Slide

57. Using a Custom NSPersistentStore
    

    View Slide

58. Using a Custom NSPersistentStore
    

    View Slide

59. Using a Custom NSPersistentStore
    

    View Slide

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

    View Slide

61. View Slide

62. Setup
    

    View Slide

63. Setup
    

    View Slide

64. Setup
    

    View Slide

65. Setup
    

    View Slide

66. Creating a New Database
    

    View Slide

67. Creating a New Database
    

    View Slide

68. Creating a New Database
    

    View Slide

69. Creating a New Database
    

    View Slide

70. Creating Entity Tables
    

    View Slide

71. Creating Entity Tables
    

    View Slide

72. Creating Entity Tables
    

    View Slide

73. Creating Entity Tables
    

    View Slide

74. Creating Entity Tables
    

    View Slide

75. Creating Entity Tables
    

    View Slide

76. Creating Relationship Tables
    

    View Slide

77. Creating Relationship Tables
    

    View Slide

78. Creating Relationship Tables
    

    View Slide

79. Creating Indexes
    

    View Slide

80. Creating Indexes
    

    View Slide

81. Creating Indexes
    

    View Slide

82. Creating Indexes
    

    View Slide

83. Converting NSManagedObject into
    NSManagedObjectID
    

    View Slide

84. Converting NSManagedObject into
    NSManagedObjectID
    

    View Slide

85. Converting NSManagedObject into
    NSManagedObjectID
    

    View Slide

86. Converting NSManagedObject into
    NSManagedObjectID
    

    View Slide

87. Fetching and Saving
    

    View Slide

88. Fetching
    

    View Slide

89. Fetch NSManagedObjectIDs
    

    View Slide

90. Fetch NSManagedObjectIDs
    

    View Slide

91. Fetch NSManagedObjectIDs
    

    View Slide

92. Fetch NSManagedObjectIDs
    

    View Slide

93. Saving
    

    View Slide

94. Saving
    

    View Slide

95. Insertions
    

    View Slide

96. Insertions
    

    View Slide

97. Insertions
    

    View Slide

98. Insertions
    

    View Slide

99. Loading Attributes
    

    View Slide

100. Querying Attributes
     

     View Slide

101. Querying Attributes
     

     View Slide

102. Querying Attributes
     

     View Slide

103. Loading Relationships
     

     View Slide

104. toOne Relationships
     

     View Slide

105. toOne Relationships
     

     View Slide

106. toMany Relationships
     

     View Slide

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

     View Slide

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

     View Slide

109. Thank you
     

     View Slide