我正在尝试使用现有的SQLite数据库，我的Kotlin应用程序下载该数据库，然后使用Room打开该数据库。但是，我认为在原始SQLite中有一个日期类型的字段有问题。
SQLite数据库：
CREATE TABLE `sets` (
id INTEGER PRIMARY KEY AUTOINCREMENT,
baseSetSize INTEGER,
block TEXT,
booster TEXT,
cardsphereSetId INTEGER,
code TEXT(8) UNIQUE NOT NULL,
isFoilOnly INTEGER NOT NULL DEFAULT 0,
isForeignOnly INTEGER NOT NULL DEFAULT 0,
isNonFoilOnly INTEGER NOT NULL DEFAULT 0,
isOnlineOnly INTEGER NOT NULL DEFAULT 0,
isPartialPreview INTEGER NOT NULL DEFAULT 0,
keyruneCode TEXT,
mcmId INTEGER,
mcmIdExtras INTEGER,
mcmName TEXT,
mtgoCode TEXT,
name TEXT,
parentCode TEXT,
releaseDate DATE,
sealedProduct TEXT,
tcgplayerGroupId INTEGER,
totalSetSize INTEGER,
type TEXT
)My Room实体类：
@Entity(tableName = "sets", indices = [Index(value = ["code"], unique = true)])
data class Sets(
@PrimaryKey @ColumnInfo(name = "id") val id: Int?,
@ColumnInfo(name = "baseSetSize") val baseSetSize: Int?,
@ColumnInfo(name = "block") val block: String?,
@ColumnInfo(name = "booster") val booster: String?,
@ColumnInfo(name = "cardsphereSetId") val cardsphereSetId: Int?,
@ColumnInfo(name = "code") val code: String,
@ColumnInfo(name = "isFoilOnly", defaultValue = "0") val isFoilOnly: Int,
@ColumnInfo(name = "isForeignOnly", defaultValue = "0") val isForeignOnly: Int,
@ColumnInfo(name = "isNonFoilOnly", defaultValue = "0") val isNonFoilOnly: Int,
@ColumnInfo(name = "isOnlineOnly", defaultValue = "0") val isOnlineOnly: Int,
@ColumnInfo(name = "isPartialPreview", defaultValue = "0") val isPartialPreview: Int,
@ColumnInfo(name = "keyruneCode") val keyruneCode: String?,
@ColumnInfo(name = "mcmId") val mcmId: Int?,
@ColumnInfo(name = "mcmIdExtras") val mcmIdExtras: Int?,
@ColumnInfo(name = "mcmName") val mcmName: String?,
@ColumnInfo(name = "mtgoCode") val mtgoCode: String?,
@ColumnInfo(name = "name") val name: String?,
@ColumnInfo(name = "parentCode") val parentCode: String?,
@ColumnInfo(name = "releaseDate") val releaseDate: Date?,
@ColumnInfo(name = "sealedProduct") val sealedProduct: String?,
@ColumnInfo(name = "tcgplayerGroupId") val tcgplayerGroupId: Int?,
@ColumnInfo(name = "totalSetSize") val totalSetSize: Int?,
@ColumnInfo(name = "type") val type: String?
)和我的数据库类：
@Database(
version = 1,
exportSchema = false,
entities = [
Cards::class,
Sets::class]
)
@TypeConverters(Converters::class)
abstract class PrintingDatabase : RoomDatabase() {
abstract fun cardsDAO(): CardsDAO
abstract fun setsDAO(): SetsDAO
companion object {
// Singleton prevents multiple instances of database opening at the same time.
@Volatile
private var INSTANCE: PrintingDatabase? = null
fun getInstance(context: Context): PrintingDatabase {
val dbfile = File(context.dataDir.absolutePath + "/" + MTGJsonWorker.MTGJSON_PRINTING_FILE)
return INSTANCE ?: synchronized(this) {
val instance = Room.databaseBuilder(
context.applicationContext,
PrintingDatabase::class.java,
"printing_database"
)
.createFromFile(dbfile)
.fallbackToDestructiveMigration()
.build()
INSTANCE = instance
instance
}
}
}
}最后是Converters类：
object Converters {
@TypeConverter
fun fromTimestamp(value: Long): Date? {
return value?.let { Date(it) }
}
@TypeConverter
fun dateToTimestamp(date: Date?): Long? {
return date?.time
}
}在CoroutineWorker中，我有一个创建数据库示例的方法，在这里我得到一个失败：
java.util.concurrent.ExecutionException: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: sets(net.redlightning.mtgdeck.thirdparty.mtgjson.database.printing.schema.Sets).
Expected:
TableInfo{name='sets', columns={sealedProduct=Column{name='sealedProduct', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, cardsphereSetId=Column{name='cardsphereSetId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, code=Column{name='code', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, isFoilOnly=Column{name='isFoilOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, releaseDate=Column{name='releaseDate', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isOnlineOnly=Column{name='isOnlineOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, keyruneCode=Column{name='keyruneCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, mcmIdExtras=Column{name='mcmIdExtras', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, tcgplayerGroupId=Column{name='tcgplayerGroupId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, type=Column{name='type', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, booster=Column{name='booster', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, mtgoCode=Column{name='mtgoCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, baseSetSize=Column{name='baseSetSize', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isPartialPreview=Column{name='isPartialPreview', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, parentCode=Column{name='parentCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isForeignOnly=Column{name='isForeignOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, isNonFoilOnly=Column{name='isNonFoilOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, name=Column{name='name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, mcmName=Column{name='mcmName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, block=Column{name='block', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, totalSetSize=Column{name='totalSetSize', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, mcmId=Column{name='mcmId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[Index{name='index_sets_code', unique=true, columns=[code], orders=[ASC]}]}
Found:
TableInfo{name='sets', columns={sealedProduct=Column{name='sealedProduct', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, cardsphereSetId=Column{name='cardsphereSetId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, code=Column{name='code', type='TEXT(8)', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, isFoilOnly=Column{name='isFoilOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, releaseDate=Column{name='releaseDate', type='DATE', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isOnlineOnly=Column{name='isOnlineOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, keyruneCode=Column{name='keyruneCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, mcmIdExtras=Column{name='mcmIdExtras', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, tcgplayerGroupId=Column{name='tcgplayerGroupId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, type=Column{name='type', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, booster=Column{name='booster', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, mtgoCode=Column{name='mtgoCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, baseSetSize=Column{name='baseSetSize', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isPartialPreview=Column{name='isPartialPreview', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, parentCode=Column{name='parentCode', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, isForeignOnly=Column{name='isForeignOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, isNonFoilOnly=Column{name='isNonFoilOnly', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='0'}, name=Column{name='name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, mcmName=Column{name='mcmName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, block=Column{name='block', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, totalSetSize=Column{name='totalSetSize', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, mcmId=Column{name='mcmId', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[Index{name='index_sets_code', unique=true, columns=[code], orders=[ASC]}]}当我进行文本比较以查看差异是什么时，我只发现：在Expect中，列code的类型是'TEXT'，而Found的类型是'TEXT(8)'，我认为这是很好的。
否则，预期列releaseDate具有类型INTEGER和亲和性3，而在发现它具有DATE和亲和性1时，我认为这就是问题所在：
releaseDate=Column{name='releaseDate', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}VS
releaseDate=Column{name='releaseDate', type='DATE', affinity='1', notNull=false, primaryKeyPosition=0, defaultValue='null'}我曾尝试修改Converter类，更改Room实体中字段的typeAffinity属性，将其切换为String?或Int?，等等，但到目前为止还没有成功。
我的Converters类中是否缺少正确处理导入的SQLite DB中的DATE的内容？有没有更好的方式在我的实体中声明它？我怎样才能让Room正确地打开它？
1条答案
按热度按时间2mbi3lxu1#
当我进行文本比较以查看差异是什么时，我只发现：在Expect中，列代码的类型是‘Text’，而Found的类型是‘Text(8)’，我认为这是好的。
我认为这并不好，因为Room会将文本(8)视为非文本。
否则Expect具有类型为INTEGER且关联为3的列RELEASeDate，而在Found中具有具有关联1的DATE，我认为这就是问题所在：
我相信，这将是第二个问题，但肯定是一个问题。
我的Converters类中是否缺少正确处理导入的SQLite DB中的日期的内容？
有没有更好的方式在我的实体中声明它？
我怎样才能让Room正确地打开它？
Room对其期望的内容非常具体，原始(预打包的)数据库必须满足这些期望。它预计列类型为以下类型之一：-
因此，您需要相应地转换预打包的数据库。因此，CODE和RELEASeDate两列都需要更改。
需要将code列类型更改为Text，将RelaseDate列更改为整数类型。
您可以通过SQLite工具(例如，用于SQLite、DBeaver、DB浏览器(SQLite)、SQLiteStudio的Navate)将更改应用到预打包的数据库。
或者，您可以使用prePackagedDatabaseCallback。
您可以通过执行以下命令进行更改：-
备注
Room在编译项目后生成Java代码。此代码可以在Android Studio中的Android View中找到(生成的Java代码)。
其中一个类将与带有**_impl后缀的@数据库注解类相同(在本例中为
PrintingDatabase_Impl)。在该类中将有一个名为
createAllTables**的方法，该方法为每个表(@Entity注解类)包含一条语句来创建表。复制并粘贴相应的SQL语句将确保根据Room的预期创建表。这里解释了预打包的数据库回调How do I use Room's prepackagedDatabaseCallback?