英文:
SQLite Kotlin issue - no database
问题
我试图在AndroidStudio中创建一个基于boardgamegeek APIs的应用程序，但出现了一个未知原因导致我的数据库创建时没有列。以下是逻辑日志：
(1) no such table: games in "INSERT INTO games(release_year,bgg_id,game_title,thumbnail,original_title) VALUES (?,?,?,?,?)"
实现数据库和插入方法的类：
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) {
//... （代码太长，无法完全显示）
}
}
插入方法被从另一个类调用，但在这里并非如此，因为表是从onCreate方法中创建的空表。
我尝试过多次使其工作，包括：
1. 从模拟器文件中手动删除数据库
2. 将DATABASE_VERSION从1增加到2
3. 放弃字符串插值并将其替换为纯文本
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)"
4. 对代码进行重新格式化
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)
}
// 剩余代码无变化
}
另一个想法是程序可能无法看到该数据库，但将其创建为空使我放弃了它。
提前感谢您提供任何解决我的问题的提示。
英文:
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:
- Manual database removal from emulator files
- DATABASE_VERSION increase from 1 to 2
- Droping string interpolation and replacing it with plain text
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)"
- Following code reformat:
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.
答案1
得分: 1
以下是您要翻译的部分：
你的问题并不是没有数据库，而是数据库没有games表格。这可能是因为实际上存在一个数据库（你可能希望跳到最后）。
演示
首先是一个用于显示存在的表格的函数：-
@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()
}
使用您的代码的一个变种，绕过您的addDataFromXml函数，而是使用一个不从文件中获取数据的固定/单行插入函数，如下所示：-
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
}
onCreate和onUpgrade函数稍作修改，包括调用showSQLiteMaster函数，如下所示：-
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)
}
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)
}
一个Activity（MainActivity）如下：-
class MainActivity : AppCompatActivity() {
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
DatabaseInit(this).addTest(this)
}
}
然后，如果没有数据库存在且版本为1，则日志包括：-
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)
- 正如您所看到的，这导致了onCreate方法被调用，并且表格被创建，插入行后不会消失。
如果再次运行，即如果数据库存在，则日志包括：-
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)
- 即onCreate没有被调用，这是预期的。
如果版本增加到2，则：-
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
<details>
<summary>英文:</summary>
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 **addDataFromXml**function 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)
- This, as you can see, results in the onCreate method being invoked and that the table is created and that it does not disappear when inserting the row.
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)
- i.e. **onCreate** has not be called as would be expected.
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)
- As can be seen **onUpgrade** has been invoked, which then invokes **onCreate**
**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).
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论