I'm getting this error when loading my app:
2024-10-29 13:29:19.532 3647-3854 SQLiteLog org.druidanet.druidnet E (1)
2024-10-29 13:29:19.727 3647-3647 AndroidRuntime org.druidanet.druidnet E FATAL EXCEPTION: main
Process: org.druidanet.druidnet, PID: 3647
android.database.sqlite.SQLiteException: no such column: TRUE (Sqlite code 1): , while compiling: SELECT common_name, plantId, image_path, latin_name,language FROM PlantView ORDER BY common_name, (OS error - 88:Socket operation on non-socket)
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:925)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:536)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:603)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:63)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1397)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1367)
at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.query(FrameworkSQLiteDatabase.kt:156)
at androidx.room.RoomDatabase.query(RoomDatabase.kt:484)
at androidx.room.util.DBUtil.query(DBUtil.kt:75)
at org.druidanet.druidnet.data.PlantDAO_Impl$1.call(PlantDAO_Impl.java:47)
at org.druidanet.druidnet.data.PlantDAO_Impl$1.call(PlantDAO_Impl.java:43)
at androidx.room.CoroutinesRoom$Companion$createFlow$1$1$1.invokeSuspend(CoroutinesRoom.kt:129)
at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:108)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1162)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:636)
at java.lang.Thread.run(Thread.java:784)
I tried with a physical tablet with Oreo (API level 26) and virtual machine with same API level and I get this crash. However, in many other devices, like Android 11 (API level 30) it works seamlessly.
I created the database using Room and an asset to initialize it:
@Database(entities = [PlantEntity::class, UsageEntity::class, NameEntity::class, ConfusionEntity::class],
views =[PlantView::class],
version = 1)
abstract class AppDatabase: RoomDatabase() {
abstract fun plantDao(): PlantDAO
companion object {
@Volatile
private var INSTANCE: AppDatabase? = null
fun getDatabase(context: Context): AppDatabase {
return INSTANCE ?: synchronized(this) {
Room.databaseBuilder(
context,
AppDatabase::class.java,
"druid_database"
)
// Wipes and rebuilds instead of migrating if no Migration object.
.fallbackToDestructiveMigration()
.createFromAsset("databases/druid_database.db")
.build()
.also {
INSTANCE = it
}
}
}
}
}
I just discovered the bug!
Older versions of SQLite (previous to API level 30) don't support TRUE / FALSE as synonyms to 1 / 0.
When creating the SQL View to do the queries into, I was using the keyword TRUE instead of 1:
@DatabaseView("SELECT p.plantId, p.latin_name, n.common_name, n.language, p.image_path" +
" FROM Plant p JOIN Name n ON p.plantId = n.plantId WHERE isDisplayName = TRUE"
)
which works in modern versions of SQLite but it does not work in older versions.
Replacing TRUE by 1 does the trick!
PD: kudos to ChatGPT which helped me to locate the bug!