I am trying to create boardgamegeek APIs based app in AndroidStudio, however from unknown reason I'm getting my database created with no columns. Here's Logical log:
(1) no such table: games in "INSERT INTO games(release_year,bgg_id,game_title,thumbnail,original_title) VALUES (?,?,?,?,?)"
Class which implements database + the insertion method:
class DatabaseInit(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
companion object {
val DATABASE_VERSION = 2
val DATABASE_NAME = "boardgames.db"
// Tabela z informacjami o grach
val TABLE_GAMES = "games"
val COLUMN_GAME_TITLE = "game_title"
val COLUMN_ORIGINAL_TITLE = "original_title"
val COLUMN_RELEASE_YEAR = "release_year"
val COLUMN_BGG_ID = "bgg_id"
val COLUMN_TYPE = "type"
val COLUMN_THUMBNAIL = "thumbnail"
}
override fun onCreate(db: SQLiteDatabase) {
db.execSQL("DROP TABLE IF EXISTS `$TABLE_GAMES`")
val CREATE_TABLE_GAMES = "CREATE TABLE $TABLE_GAMES(" +
"$COLUMN_GAME_TITLE TEXT," +
"$COLUMN_ORIGINAL_TITLE TEXT," +
"$COLUMN_RELEASE_YEAR INTEGER," +
"$COLUMN_BGG_ID INTEGER PRIMARY KEY," +
"$COLUMN_TYPE INTEGER," +
"$COLUMN_THUMBNAIL BLOB)"
db.execSQL(CREATE_TABLE_GAMES)
}
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
db.execSQL("DROP TABLE IF EXISTS $TABLE_GAMES")
onCreate(db)
}
fun addDataFromXml(context: Context) {
val xmlFile = File(context.filesDir, "XML/dane.xml")
val xmlInputStream = FileInputStream(xmlFile)
val xmlParser = Xml.newPullParser()
xmlParser.setInput(xmlInputStream, null)
var eventType = xmlParser.eventType
var gameId = ""
var gameType = -1
var gameTitle = ""
var releaseYear = ""
var thumbnail = ""
while (eventType != XmlPullParser.END_DOCUMENT) {
if (eventType == XmlPullParser.START_TAG && xmlParser.name == "item") {
gameId = xmlParser.getAttributeValue(null, "objectid")
val subtype = xmlParser.getAttributeValue(null, "subtype")
gameType = when (subtype) {
"boardgame" -> 1
"boardgameexpansion" -> 0
else -> -1
}
} else if (eventType == XmlPullParser.START_TAG && xmlParser.name == "name") {
gameTitle = xmlParser.nextText()
} else if (eventType == XmlPullParser.START_TAG && xmlParser.name == "yearpublished") {
releaseYear = xmlParser.nextText()
} else if (eventType == XmlPullParser.START_TAG && xmlParser.name == "thumbnail") {
thumbnail = xmlParser.nextText()
} else if (eventType == XmlPullParser.END_TAG && xmlParser.name == "item") {
// Dodawanie danych do bazy danych
val values = ContentValues().apply {
put(COLUMN_GAME_TITLE, gameTitle)
put(COLUMN_ORIGINAL_TITLE, "")
put(COLUMN_RELEASE_YEAR, releaseYear.toInt())
put(COLUMN_BGG_ID, gameId.toInt())
put(COLUMN_THUMBNAIL, thumbnail)
}
val db = writableDatabase
db.insert(TABLE_GAMES, null, values)
db.close()
}
eventType = xmlParser.next()
}
xmlInputStream.close()
}
}
The insertion method is being called from another class, however that is not the case here since the table is being created empty from onCreate method.
The following permissions have been added to androidmanifest:
<uses-permission android:name="android.permission.INTERNET" /> <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>
I've done several attempts to make it work which include:
val CREATE_TABLE_GAMES = "CREATE TABLE $TABLE_GAMES(" +
"$COLUMN_GAME_TITLE TEXT," +
"$COLUMN_ORIGINAL_TITLE TEXT," +
"$COLUMN_RELEASE_YEAR INTEGER," +
"$COLUMN_BGG_ID INTEGER PRIMARY KEY," +
"$COLUMN_TYPE INTEGER," +
"$COLUMN_THUMBNAIL BLOB)"
class DatabaseInit(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {
companion object {
const val DATABASE_VERSION = 1
const val DATABASE_NAME = "boardgames.db"
const val TABLE_GAMES = "games"
const val COLUMN_GAME_TITLE = "game_title"
const val COLUMN_ORIGINAL_TITLE = "original_title"
const val COLUMN_RELEASE_YEAR = "release_year"
const val COLUMN_BGG_ID = "bgg_id"
const val COLUMN_TYPE = "type"
const val COLUMN_THUMBNAIL = "thumbnail"
private const val CREATE_TABLE_GAMES = "CREATE TABLE $TABLE_GAMES (" +
"$COLUMN_GAME_TITLE TEXT," +
"$COLUMN_ORIGINAL_TITLE TEXT," +
"$COLUMN_RELEASE_YEAR INTEGER," +
"$COLUMN_BGG_ID INTEGER PRIMARY KEY," +
"$COLUMN_TYPE INTEGER," +
"$COLUMN_THUMBNAIL BLOB)"
}
override fun onCreate(db: SQLiteDatabase) {
db.execSQL(CREATE_TABLE_GAMES)
}
//remaining code without changes
My another idea was that the programs cannot see that database, however creating it empty make me leave it.
Thanks in advance for any tips how to resolve my issue.
You issue is not that there is no database, but rather that the database does not have the games table. This is likely because there is in fact a database (you may wish to skip to the end).
Demo
First a function to show the table(s) that exist:-
@SuppressLint("Range")
fun showSQLiteMaster(tagSuffix: String, db: SQLiteDatabase) {
Log.d(TAG + tagSuffix,"Showing database schema")
val csr = db.rawQuery("SELECT * FROM sqlite_master",null)
while (csr.moveToNext()) {
Log.d(TAG+tagSuffix,"\nTable is ${csr.getString(csr.getColumnIndex("name"))} SQL is ${csr.getString(csr.getColumnIndex("sql"))}" )
}
csr.close()
}
Using a derivation of your code, that bypasses your addDataFromXmlfunction and instead uses a fixed/single row insertion function that does not get data from a file, as per:-
fun addTest(context: Context) {
Log.d(TAG,"AddTest Invoked. DB Version is ${writableDatabase.version}")
val values = ContentValues().apply {
put(COLUMN_GAME_TITLE, "TheGame001")
put(COLUMN_ORIGINAL_TITLE, "")
put(COLUMN_RELEASE_YEAR, 2020)
put(COLUMN_BGG_ID, 1)
put(COLUMN_THUMBNAIL, ByteArray(0))
}
val db = writableDatabase
db.insert(TABLE_GAMES, null, values)
showSQLiteMaster("_ADDTEST",db)
//db.close() inadvisable to close the database as each time it is re-opened there is an overhead
}
The onCreate and onUpgrade funtions are slightly modified to include invoking the showSQLiteMaster function as per:-
override fun onCreate(db: SQLiteDatabase) {
Log.d(TAG,"Oncreate Invoked. Version is ${db.version}")
db.execSQL("DROP TABLE IF EXISTS `$TABLE_GAMES`")
val CREATE_TABLE_GAMES = "CREATE TABLE $TABLE_GAMES(" +
"$COLUMN_GAME_TITLE TEXT," +
"$COLUMN_ORIGINAL_TITLE TEXT," +
"$COLUMN_RELEASE_YEAR INTEGER," +
"$COLUMN_BGG_ID INTEGER PRIMARY KEY," +
"$COLUMN_TYPE INTEGER," +
"$COLUMN_THUMBNAIL BLOB)"
db.execSQL(CREATE_TABLE_GAMES)
showSQLiteMaster("_ONCRT", db)
}
and :-
override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
Log.d(TAG,"OnUpgrade Invoked. FromVersion is ${oldVersion} ToVersion is ${newVersion} DBVersions is ${db.version}")
db.execSQL("DROP TABLE IF EXISTS $TABLE_GAMES")
onCreate(db)
}
An Activity (MainActivity) being:-
class MainActivity : AppCompatActivity() {
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
DatabaseInit(this).addTest(this)
}
}
Then when no database exists and the version is 1, then the log includes:-
2023-06-05 12:44:39.488 D/DBINFO: Oncreate Invoked. Version is 0
2023-06-05 12:44:39.489 D/DBINFO_ONCRT: Showing database schema
2023-06-05 12:44:39.490 D/DBINFO_ONCRT: Table is android_metadata SQL is CREATE TABLE android_metadata (locale TEXT)
2023-06-05 12:44:39.490 D/DBINFO_ONCRT: Table is games SQL is CREATE TABLE games(game_title TEXT,original_title TEXT,release_year INTEGER,bgg_id INTEGER PRIMARY KEY,type INTEGER,thumbnail BLOB)
2023-06-05 12:44:39.492 D/DBINFO: AddTest Invoked. DB Version is 1
2023-06-05 12:44:39.493 D/DBINFO_ADDTEST: Showing database schema
2023-06-05 12:44:39.493 D/DBINFO_ADDTEST: Table is android_metadata SQL is CREATE TABLE android_metadata (locale TEXT)
2023-06-05 12:44:39.493 D/DBINFO_ADDTEST: Table is games SQL is CREATE TABLE games(game_title TEXT,original_title TEXT,release_year INTEGER,bgg_id INTEGER PRIMARY KEY,type INTEGER,thumbnail BLOB)
If run again, i.e. the database exists then the log includes:-
2023-06-05 12:48:36.318 D/DBINFO: AddTest Invoked. DB Version is 1
2023-06-05 12:48:36.320 D/DBINFO_ADDTEST: Showing database schema
2023-06-05 12:48:36.322 D/DBINFO_ADDTEST: Table is android_metadata SQL is CREATE TABLE android_metadata (locale TEXT)
2023-06-05 12:48:36.322 D/DBINFO_ADDTEST: Table is games SQL is CREATE TABLE games(game_title TEXT,original_title TEXT,release_year INTEGER,bgg_id INTEGER PRIMARY KEY,type INTEGER,thumbnail BLOB)
If the Version is increased to 2 then:-
2023-06-05 12:50:37.349 D/DBINFO: OnUpgrade Invoked. FromVersion is 1 ToVersion is 2 DBVersions is 1
2023-06-05 12:50:37.350 D/DBINFO: Oncreate Invoked. Version is 1
2023-06-05 12:50:37.350 D/DBINFO_ONCRT: Showing database schema
2023-06-05 12:50:37.351 D/DBINFO_ONCRT: Table is android_metadata SQL is CREATE TABLE android_metadata (locale TEXT)
2023-06-05 12:50:37.351 D/DBINFO_ONCRT: Table is games SQL is CREATE TABLE games(game_title TEXT,original_title TEXT,release_year INTEGER,bgg_id INTEGER PRIMARY KEY,type INTEGER,thumbnail BLOB)
2023-06-05 12:50:37.352 D/DBINFO: AddTest Invoked. DB Version is 2
2023-06-05 12:50:37.353 D/DBINFO_ADDTEST: Showing database schema
2023-06-05 12:50:37.354 D/DBINFO_ADDTEST: Table is android_metadata SQL is CREATE TABLE android_metadata (locale TEXT)
2023-06-05 12:50:37.354 D/DBINFO_ADDTEST: Table is games SQL is CREATE TABLE games(game_title TEXT,original_title TEXT,release_year INTEGER,bgg_id INTEGER PRIMARY KEY,type INTEGER,thumbnail BLOB)
The likely Issue/Possible Fix
From the above it would seem if something untoward is happening. However, what may well be happening is that AutoBackup has backed up a database that is devoid of the games table and is restoring the database. As such onCreate will not be called and the thus the table will not exist.
I would suggest modifying the manifest to include android:allowBackup="false" as per https://developer.android.com/guide/topics/data/autobackup#EnablingAutoBackup
You would have to delete the existing database (via file explorer or by uninstalling the app).