Upgrade to Pro — share decks privately, control downloads, hide ads and more …

About Affinity between SQLCipher and Room/CoreData

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

LINE DEVDAY 2021
PRO

November 11, 2021
Tweet

More Decks by LINE DEVDAY 2021

Other Decks in Technology

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
    [email protected]
    - iOS Engineer
    - Joined LINE in 2017
    @brevansio
    [email protected]
    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