预打包的数据库具有无效的架构错误 [英] Pre-packaged database has an invalid schema error

问题描述

我正在基于一个旧的Android项目构建一个Android应用程序.在我的新应用程序中，我正在使用Room.我必须使用第一个项目中使用的相同数据库.此外，我已经使用com.amitshekhar.android:debug-db库从第一个项目中提取了数据库.获取数据库文件后，我想在Room中打开它.

我正在建立这样的数据库:

  Room.databaseBuilder(androidContext()，Database :: class.java，"database.db").createFromAsset("database.db").建造() 

当前我正在使用此createFromAsset()方法，尽管稍后我将使用createFromFile()方法，因为应该从服务器下载数据库.

但是我得到了java.lang.IllegalStateException:预打包的数据库具有无效的架构之所以会发生这种情况，是因为数据库中存在Room不支持的几种数据类型，例如NVARCHAR(200)，DATE或bit.

我知道Room仅使用五种Sql类型，但我不知道如何更改它，以便Room可以使用上述方法打开这种数据库.

问题是如何将NVARCHAR(200)，DATE或bit转换为Room支持的数据类型?

解决方案

您必须将数据库转换为使用Room支持并与实体匹配的特定列类型关联.

对于 NVARCHAR(200)，您需要 TEXT 将NVARCHAR(200)替换为将列定义为字符串的实体.

对于 DATE ，如果您使用的是基于字符串的日期，则取决于Entity定义.YYYY-MM-DD hh:mm:ss，则实体"应为String，列亲和力为 TEXT .如果将日期存储为时间戳，则实体应为长且列亲和性 INTEGER .

答案在这里

希望列/文本是不言自明的.这基于定义的列类型(可能与所使用的类型不同).例如浮动点(显示第5行)，您会认为这是真实的.但是，根据派生的类型相似性，已应用了第一个规则(如果类型包含INT，则为INTEGER).

根据 SQLite版本3-3.1中的数据类型的规则.列亲和力的确定.

根据我在房间方面的有限经验，

NUMERIC 不是它使用的类型，因此应始终将其更改为其他类型之一.

I'm building an Android application based on an old Android project. In my new application I'm using Room. I have to use the same database that is used in the first project. Furthermore, I've extracted the database from the first project using com.amitshekhar.android:debug-db library. After obtaining the database file I would like to open it with the Room.

I am building database like this:

Room.databaseBuilder(
            androidContext(),
            Database::class.java, "database.db"
        ).createFromAsset("database.db")
            .build()

Currently I'm using this createFromAsset() method, although later I would use the createFromFile() method, as my database should be downloaded from the server.

But I'm getting the java.lang.IllegalStateException: Pre-packaged database has an invalid schema This happens because there are several datatypes in the database that are not supported in Room such as NVARCHAR(200), DATE or bit.

I'm aware that Room is using only five Sql types, but I do not know how to change this so that Room can open this kind of database using above mentioned methods.

The problem is how to convert NVARCHAR(200), DATE or bit into datatypes that are supported by Room?

解决方案

You have to convert the database to use specific column type affinities that are supported by Room and that match the entities.

For NVARCHAR(200) you need to have TEXT replace NVARCHAR(200) with the Entity defining the column as a String.

For DATE it depends upon the Entity definition if you are using String based dates e.g. YYYY-MM-DD hh:mm:ss then the Entity should be String and the column affinity TEXT. If storing the date as a timestamp then the Entity should be long and the column affinity INTEGER.

The answer here Can't migrate a table to Room do to an error with the way booleans are saved in Sqlite does a conversion to change BOOL's to INTEGER.

You could adapt this (although I would be cautious with DATE) to suit.

Additional

You may find the following to be of use. You run it against the pre-existing database in your favourite SQLite Manager tool.

WITH potentialRoomChanges AS (
    SELECT sm.name AS tablename, pti.name AS columnname, pti.type, dflt_value, pk,
        CASE 
            WHEN instr(upper(pti.type),'INT') THEN 'INTEGER'
            WHEN instr(upper(pti.type),'CHAR') OR instr(upper(pti.type),'CLOB') OR instr(upper(pti.type),'TEXT') THEN 'TEXT'
            WHEN instr(upper(pti.type),'BLOB') THEN 'BLOB'
            WHEN instr(upper(pti.type),'REAL') OR instr(upper(pti.type),'FLOA') OR instr(upper(pti.type),'DOUB') THEN 'REAL'
            ELSE 'NUMERIC'
        END AS roomtype ,
        CASE WHEN pti.[notnull] THEN 'Investigate NOT NULL USE' END AS nnindicator,
        sql
    FROM sqlite_master AS sm JOIN pragma_table_info(sm.name) AS pti
    WHERE 
        sm.type = 'table' 
        AND sm.name NOT LIKE 'sqlite_%' 
        AND sm.name <> 'android_metadata' 
        AND (
            upper(pti.type) <> roomtype 
            OR instr(roomtype,'NUMERIC') 
            OR nnindicator IS NOT NULL
            OR dflt_value IS NOT NULL
            OR pk > 0
        )
    ORDER BY sm.name,pti.cid
)
SELECT tablename, columnname, type, roomtype, 
CASE WHEN upper(type) <> upper(roomtype) THEN 'Investigate TYPE should be ' ||roomtype END AS typechange_notes,
CASE WHEN roomtype = 'NUMERIC' THEN 'Investigate NUMERIC' END AS numeric_notes, 
CASE WHEN dflt_value IS NOT NULL THEN 'Investigate DEFAULT VALUE of '||dflt_value END AS default_notes,
CASE WHEN pk > 0 THEN 'Investigate PRIMARY KEY inclusion' END AS primarykey_notes,
nnindicator AS notnull_notes 
FROM potentialRoomChanges
;

Example output :-

Hopefully the columns/text are self-explanatory. This is based upon the column types defined (which may differ from the type used). e.g. FLOATING POINT (5th row shown) you would think would be REAL. However according to the derived type affinity the first rule (if the type includes INT it is INTEGER) has been applied.

Rules as per Datatypes In SQLite Version 3 - 3.1. Determination Of Column Affinity.

NUMERIC from my limited experience with room isn't a type that it uses, so it should always be changed to one of the other types.

这篇关于预打包的数据库具有无效的架构错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！