Android room报错java.lang.IllegalStateException: Pre-packaged database has an invalid schema

之前在涉及城市列表的地方一直用的GreenDao来查询显示，最近有在学习jatpack，从而入手了room数据库，按照流程一切操作好之后，发现报了以下错误：

 java.lang.IllegalStateException: Pre-packaged database has an invalid schema: AREA_CITY(com.ssh.sh_recruit.room.bean.AreaCity).
     Expected:
    TableInfo{name='AREA_CITY', columns={LevelType=Column{name='LevelType', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, ID=Column{name='ID', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='null'}, Name=Column{name='Name', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, ParentId=Column{name='ParentId', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}
     Found:
    TableInfo{name='AREA_CITY', columns={LevelType=Column{name='LevelType', type='CHAR(6)', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, ID=Column{name='ID', type='CHAR(8)', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, Name=Column{name='Name', type='CHAR(16)', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, ParentId=Column{name='ParentId', type='CHAR(8)', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=null}
        at androidx.room.RoomOpenHelper.checkIdentity(RoomOpenHelper.java:163)
        at androidx.room.RoomOpenHelper.onOpen(RoomOpenHelper.java:135)
        at androidx.sqlite.db.framework.FrameworkSQLiteOpenHelper$OpenHelper.onOpen(FrameworkSQLiteOpenHelper.java:142)
        at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:266)
        at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:163)

问题分析
我的database创建方式：

     Room.databaseBuilder(MApp.instance,AppDataBase::class.java,"area.db")
            .createFromAsset("area_db.db")
            .build()

对应数据库表AREA_CITY 的实体类

@Parcelize
@Entity(tableName = "AREA_CITY")
data class AreaCity(
@PrimaryKey
var ID:String="",
var ParentId:String? ="",
var Name:String?="",
var LevelType:String?=""
): Parcelable

再回过头来查看报错日志，大致意思是实体类对应的表结构，与实际在数据库中的表结构不一致，例如：所期望的type是TEXT，而实际都是CHAR类型，ID的primaryKeyPosition=1需要有主键约束，而实际primaryKeyPosition=0

解决思路
在数据库中创建一张空的类型匹配的AREA_CITY表（可能有人疑惑为啥不直接在原有表上修改类型，因为表的主键确定了就不能修改了，所以我这里重新创建一张新的表），然后将原来旧的表数据导入到新的表中，使用的工具SQLiteExpertProfessional，来修改数据库

1. 修改旧的表名留作备份源

   ALTER TABLE "AREA_CITY" RENAME TO "AREA_CITY_TEMP"
   

2. 创建一张新表AREA_CITY

   CREATE TABLE "AREA_CITY" (
   "ID"  TEXT PRIMARY KEY,
   "ParentId" TEXT,
   "Name"  TEXT,
   "LevelType" TEXT);
   

3. 将原始数据备份到AREA_CITY表

   INSERT INTO "AREA_CITY" ("ID", "ParentId","Name","LevelType") SELECT "ID", "ParentId","Name","LevelType" FROM "AREA_CITY_TEMP";
   

到此问题就解决了

参考链接