Open pecet86 opened 4 years ago
experiencing the same issue, insert into main.table(....) select .... from slave.table
does not work.
will probably try to fix it myself, open a pull request.
can't figure out where is the bug and even how to replicate it (sometimes it works, sometimes it doesn't). I resorted to system sqlite to clone the table, then use requery sqlite for all other things.
Any solution to this? I tried to use sqlite-android but am facing this issue consistently on my project.
Tested with Room version 2.5.1 and sqlite-android version 3.42.0.
I found a workaround. I'm using Room primarily to access databases. When I need ATTACH to work properly, I found out that if I close room database and then open new database connection with SQLiteDatabase.openDatabase
from sqlite-android, then everything works. After I have done needed actions, I close that db and reset room database in database container.
So something like this works:
roomDb.close()
val newDb =
SQLiteDatabase.openDatabase(application.getDatabasePath(dbFileName).absolutePath, null, SQLiteDatabase.OPEN_READWRITE)
newDb.use { db -> db.run {
execSQL("ATTACH DATABASE '${patchDbFile.absolutePath}' AS patch")
// Do queries here
execSQL("DETACH DATABASE patch")
}}
resetRoomDb()
Furthermore, I noticed that just closing and opening db does not work, if I use Room to initialize my DB.
So this does not:
roomDb.close()
val newDb = Room.databaseBuilder(
application, MyDatabase::class.java, filename
)
.allowMainThreadQueries()
.addMigrations(*myMigrations)
.openHelperFactory(RequerySQLiteOpenHelperFactory())
.build()
newDb.openHelper.writableDatabase.use { db -> db.run {
execSQL("ATTACH DATABASE '${patchDbFile.absolutePath}' AS patch")
// Do queries here
execSQL("DETACH DATABASE patch")
}}
resetRoomDb()
Tested also that it is not directly related to usage of RequerySQliteOpenHelperFactory. This works too:
roomDb.close()
val fac = RequerySQLiteOpenHelperFactory()
val cb = object: SupportSQLiteOpenHelper.Callback(1) {
override fun onCreate(db: SupportSQLiteDatabase) {}
override fun onUpgrade(db: SupportSQLiteDatabase, oldVersion: Int, newVersion: Int) {}
}
val facConfig = SupportSQLiteOpenHelper.Configuration.builder(application)
.name(filename)
.callback(cb)
.build();
val helper = fac.create(facConfig)
helper.writableDatabase.use { db -> db.run {
execSQL("ATTACH DATABASE '${patchDbFile.absolutePath}' AS patch")
// Do queries here
execSQL("DETACH DATABASE patch")
}}
resetRoomDb()
Concluding there's something different how Room initializes the DB that triggers this to fail.
I found out that if you use TRUNCATE (instead of AUTOMATIC or WRITE_AHEAD_LOGGING) journal mode when building Room database, everything works.
Room.databaseBuilder(
application, MyDatabase::class.java, filename
)
.allowMainThreadQueries()
.addMigrations(*myMigrations)
.setJournalMode(RoomDatabase.JournalMode.TRUNCATE)
.openHelperFactory(RequerySQLiteOpenHelperFactory())
.build()
Hello I found an interesting bug that does not occur on the standard android library.
The point is that when I attach the second base, only main SELECT works, but not sub SELECT