How do I use "sqlcipher-android" and "spatia-room" correctly?

[YJChen8688]

@johan12345 @dalgarins Hi!
if I encrypt the database directly with "sqlcipher-android":
implementation "net.zetetic:sqlcipher-android:4.5.6@aar"

           fun getInstance(context: Context): AppDatabase {
                val tempInstance = INSTANCE
                if (tempInstance != null) {
                    return tempInstance
                }
    
                synchronized(this) {
    
                    System.loadLibrary("sqlcipher")
                    val password = "Password1!"
                    val databaseFile: File = context.getDatabasePath(DB_NAME)
                    val factory = SupportOpenHelperFactory(password.toByteArray(StandardCharsets.UTF_8))
    
                    val instance = SpatiaRoom.databaseBuilder(
                        context.applicationContext,
                        AppDatabase::class.java,
                        databaseFile.absolutePath
                    ).addCallback(object : Callback() {
                        override fun onCreate(db: SupportSQLiteDatabase) {
                            // Initialize Spatialite
                            db.query("SELECT InitSpatialMetaData();").moveToNext()
                            // Room already creates a BLOB column for the geometry, so we need to use
                            // RecoverGeometryColumn to correctly initialize Spatialite's metadata
                            db.query("SELECT RecoverGeometryColumn('geo_posts', 'location', 4326, 'POINT', 'XY');")
                                .moveToNext()
                            // create a spatial index (optional)
                            db.query("SELECT CreateSpatialIndex('geo_posts', 'location');")
                                .moveToNext()
                        }
                    })
                     //  sqlcipher-android 
                        .openHelperFactory(factory)
                        .build()
    
                    INSTANCE = instance
                    return instance
                }
            }

I get the following error:
ndroid.database.sqlite.SQLiteException: no such function: InitSpatialMetaData (code 1): , while compiling: SELECT InitSpatialMetaData(); at net.zetetic.database.sqlcipher.SQLiteConnection.nativePrepareStatement(Native Method) at net.zetetic.database.sqlcipher.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:973) at net.zetetic.database.sqlcipher.SQLiteConnection.prepare(SQLiteConnection.java:537) at net.zetetic.database.sqlcipher.SQLiteSession.prepare(SQLiteSession.java:592) at net.zetetic.database.sqlcipher.SQLiteProgram.<init>(SQLiteProgram.java:64) at net.zetetic.database.sqlcipher.SQLiteQuery.<init>(SQLiteQuery.java:43) at net.zetetic.database.sqlcipher.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:68) at net.zetetic.database.sqlcipher.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1546) at net.zetetic.database.sqlcipher.SQLiteDatabase.query(SQLiteDatabase.java:1149) at co.anbora.labs.spatia.builder.SpatiaBuilder$roomBuilder$1.onCreate(SpatiaBuilder.kt:34) at co.anbora.labs.spatiaroom.data.AppDatabase_Impl$1.onCreate(AppDatabase_Impl.java:58) at androidx.room.RoomOpenHelper.onCreate(RoomOpenHelper.kt:79) at net.zetetic.database.sqlcipher.SupportHelper$1.onCreate(SupportHelper.java:22) at net.zetetic.database.sqlcipher.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:398) at net.zetetic.database.sqlcipher.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:278) at net.zetetic.database.sqlcipher.SupportHelper.getWritableDatabase(SupportHelper.java:60) at androidx.room.RoomDatabase.inTransaction(RoomDatabase.kt:632) at androidx.room.RoomDatabase.assertNotSuspendingTransaction(RoomDatabase.kt:451) at co.anbora.labs.spatiaroom.data.dao.PostsDao_Impl.insertPosts(PostsDao_Impl.java:95) at co.anbora.labs.spatiaroom.FirstFragment$onViewCreated$1.invokeSuspend(FirstFragment.kt:50) at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33) at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:106) at kotlinx.coroutines.internal.LimitedDispatcher$Worker.run(LimitedDispatcher.kt:115) at kotlinx.coroutines.scheduling.TaskImpl.run(Tasks.kt:100) at kotlinx.coroutines.scheduling.CoroutineScheduler.runSafely(CoroutineScheduler.kt:584) at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.executeTask(CoroutineScheduler.kt:793) at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.runWorker(CoroutineScheduler.kt:697) at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.run(CoroutineScheduler.kt:684) Suppressed: kotlinx.coroutines.internal.DiagnosticCoroutineContextException: [StandaloneCoroutine{Cancelling}@247126f, Dispatchers.IO]
How do I use "sqlcipher-android" and "spatia-room" correctly?
Thanks!

[dalgarins]

@YJChen8688 this issue is because you are overriding the spatialite helper factory in this line,
.openHelperFactory(factory) if you need that line of code the library won't work, because the library is using his own openHelperFactory inside spatiaBuilderClass: .openHelperFactory(SpatiaHelperFactory()).

I think you could have two possible solutions to use spatialite in your application:

• create a spatialite db, manually from your laptop and use method createFromAsset to use this db with spatialite installed, after that use your library sqlcipher, I haven't tested, I don't know if that will work.
• other solution and the best will be create your own openHelperFactory, try to look QueryInterceptorOpenHelperFactory from android source code, to implement something similar, a helper factory with a delegate factory inside.

[YJChen8688]

@YJChen8688 this issue is because you are overriding the spatialite helper factory in this line, .openHelperFactory(factory) if you need that line of code the library won't work, because the library is using his own openHelperFactory inside spatiaBuilderClass: .openHelperFactory(SpatiaHelperFactory()).

I think you could have two possible solutions to use spatialite in your application:

• create a spatialite db, manually from your laptop and use method createFromAsset to use this db with spatialite installed, after that use your library sqlcipher, I haven't tested, I don't know if that will work.
• other solution and the best will be create your own openHelperFactory, try to look QueryInterceptorOpenHelperFactory from android source code, to implement something similar, a helper factory with a delegate factory inside.

I did study up and do have some idea as to what it is. Still, I have been battling this for few evenings now.
Thank you for the suggestion, I look into it again, I think it was a challenge for me!
thanks again!

[dalgarins]

@YJChen8688 let me know if you find a solution, and please share if you have one, I would like to see how you fix your issue.

[dalgarins]

@YJChen8688 did you find a solution for your problem?

[YJChen8688]

@YJChen8688 did you find a solution for your problem?
I am deeply appreciative of your attention to this issue. However, it is with sincere regret that I must inform you that my recent schedule has been overwhelmingly busy, leaving no additional time to devote to thoroughly investigating and resolving this matter.