java - 如何解决预填充数据库上涉及 'notNull' 和 'primaryKeyPosition' 的 Room 无效架构错误？

Question

我的问题:我正在转换现有的 Java 桌面应用程序，并且正在学习 Android Room 开发，并通过 SQLite Studio 创建了一个预填充的 SQLite 数据库，其中包含多对多联接表(即 Author_By_Source)。该数据库和表用于现有的 Java 桌面应用程序。我正在尝试解决“notNull”和“primaryKeyPosition”属性之间的无效架构不匹配问题。我无法更新这两个属性。这是我尝试解决 AuthorID 字段的错误部分，但同一错误消息中的 SourceID 也存在相同的差异。因此这两个字段需要相同的分辨率:

    Expected:
    TableInfo{name='Author_By_Source', columns={AuthorID=Column{name='AuthorID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, ...
    Found:
    TableInfo{name='Author_By_Source', columns={AuthorID=Column{name='AuthorID', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=0, defaultValue='null'}, ...

我尝试过的:我尝试使用 SQLite Studio 界面来构建和构建数据库，并编辑“Author_By_Source”表的表结构。我将每个字段设置为“Not Null”，但当我重新运行应用程序时，它不会反射(reflect)为“true”。我还没有确定如何指定primaryKeyPosition。我尝试了相反的方法，更彻底地阅读房间注释的选项和方法，但没有成功。我在整个项目中阅读了一些 Room 文档，并访问了一些论坛和教程，但没有找到任何解决这一具体问题的内容。

我正在尝试做什么:我正在尝试使用包含多个表的预填充数据库，其中一些表具有用于多对多关系的连接表。我希望能得到一些指导。

下面是我如何注释我的 AuthorBySource 类，这可能会有所帮助:

 @Entity(tableName = "Author_By_Source", primaryKeys = {"AuthorID", "SourceID"},  foreignKeys = {
        @ForeignKey(entity = Authors.class, parentColumns = "AuthorID", childColumns = "AuthorID"),
        @ForeignKey(entity = Sources.class, parentColumns = "SourceID", childColumns = "SourceID")},
        indices = {@Index("AuthorID"), @Index("SourceID")})
public class AuthorBySource {
    @ColumnInfo(name = "AuthorID")
    private int authorID;
    @ColumnInfo(name = "SourceID")
    private int sourceID;

Answer 1

每次进行更改时，您是否都会重新复制 Assets 文件并卸载应用程序？如果没有并且这不能解决问题，那么:-

如果您编译房间代码，请查看 your_database_class_impl 中的 java(生成)(其中 your_database_class 是您的 @Database 类名称)和方法 createAllTables。其中有用于创建数据库的 SQL(您可以忽略 room_master SQL)。

例如(这基于您的实体和最小引用表):-

  @Override
  public void createAllTables(SupportSQLiteDatabase _db) {
    _db.execSQL("CREATE TABLE IF NOT EXISTS `Authors` (`AuthorID` INTEGER, `AuthorName` TEXT, PRIMARY KEY(`AuthorID`))");
    _db.execSQL("CREATE TABLE IF NOT EXISTS `Sources` (`SourceID` INTEGER, `SourceName` TEXT, PRIMARY KEY(`SourceID`))");
    _db.execSQL("CREATE TABLE IF NOT EXISTS `Author_By_Source` (`AuthorID` INTEGER NOT NULL, `SourceID` INTEGER NOT NULL, PRIMARY KEY(`AuthorID`, `SourceID`), FOREIGN KEY(`AuthorID`) REFERENCES `Authors`(`AuthorID`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`SourceID`) REFERENCES `Sources`(`SourceID`) ON UPDATE NO ACTION ON DELETE NO ACTION )");
    _db.execSQL("CREATE INDEX IF NOT EXISTS `index_Author_By_Source_AuthorID` ON `Author_By_Source` (`AuthorID`)");
    _db.execSQL("CREATE INDEX IF NOT EXISTS `index_Author_By_Source_SourceID` ON `Author_By_Source` (`SourceID`)");
    _db.execSQL("CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)");
    _db.execSQL("INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, '953f8512db886fb7d206fa561a7117c4')");
  }

您需要相应地转换数据库，即按照定义，因此例如您可以在 SQLite Studio 中使用上述内容

/* Just once */
PRAGMA foreign_keys = 0;

/* Per table */
DROP TABLE IF EXISTS Author_By_Source_new;
DROP TABLE IF EXISTS Author_By_Source_original;
CREATE TABLE IF NOT EXISTS Author_By_Source_new copy_generated_sql_for_the_column_definitions_etc;
DELETE FROM Author_By_Source_new;
INSERT INTO Author_By_Source_new SELECT * FROM Author_By_Source;
ALTER TABLE Author_By_Source RENAME TO Author_By_Source_original;
ALTER TABLE Author_By_Source_new RENAME TO Author_By_Source;
/* IF HAPPY THEN DO */
-- DROP TABLE IF EXISTS Author_By_Source_original; /* NOTE TURNED OFF */

...... do the equivalent for all tables


/* Just once */
PRAGMA foreign_keys = 1;
PRAGMA foreign_key_check;
PRAGMA integrity_check; 

• copy_ generated_sql_for_the_column_definitions_etc 应相应地替换为生成的 SQL 中的列定义部分(或复制整个语句并更改表名称)。
• 请注意 INSERT INTO ....语句假定新表和原始表的列顺序相同，否则使用 INSERT INTO (column_list)....其中列是根据生成的 SQL 例如INSERT INTO Author_By_Source_new (AuthorID,SourceID) SELECT * FROM Author_By_Source;

• CREATE TABLE SQL 将是(基于上面生成的 SQL)

  • CREATE TABLE IF NOT EXISTS `Author_By_Source_new` (`AuthorID` INTEGER NOT NULL, `SourceID` INTEGER NOT NULL, PRIMARY KEY(`AuthorID`, `SourceID`), FOREIGN KEY(`AuthorID`) REFERENCES `Authors`(`AuthorID`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`SourceID`) REFERENCES `Sources`(`SourceID`) ON UPDATE NO ACTION ON DELETE NO ACTION );
  • 注意表名称已更改，即后缀为_new

示例

这是一个基于您的代码中可用内容的示例。

原始预填充数据库

数据库是使用 SQLite 管理工具 (Navicat) 生成的:-

DROP TABLE IF EXISTS Author_By_Source;
DROP TABLE IF EXISTS Authors;
DROP TABLE IF EXISTS Sources;
CREATE TABLE IF NOT EXISTS Authors (AuthorID INTEGER PRIMARY KEY, AuthorName TEXT);
CREATE TABLE IF NOT EXISTS Sources (SourceID INTEGER PRIMARY KEY, SourceName TEXT);
/* Create the table so that it would cause issues in Room  */
CREATE TABLE IF NOT EXISTS Author_By_Source (
    AuthorId INTEGER, SourceId INTEGER, 
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID), 
    FOREIGN KEY (SourceID) REFERENCES Sources(SourceID)
);

INSERT INTO Authors (AuthorName) VALUES ('Fred'),('Mary'),('Joan'),('Bert'),('Alan');
INSERT INTO Sources (SourceName) VALUES ('S1'),('S2'),('S3'),('S4'),('S5');
INSERT INTO Author_By_Source VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(1,4),(3,4),(5,2);

SELECT AuthorName, SourceName 
    FROM Author_By_Source 
        JOIN Authors ON Author_By_Source.AuthorID = Authors.AuthorID
        JOIN Sources ON Author_By_Source.SourceID = Sources.SourceID
;

运行后:-

AuthorName  SourceName
Fred        S1
Mary        S2
Joan        S3
Bert        S4
Alan        S5
Fred        S4
Joan        S4
Alan        S2

正如预期的那样，尝试使用此结果:-

java.lang.RuntimeException: Unable to start activity ComponentInfo{a.a.so59756782javaroomprepopulatedconversion/a.a.so59756782javaroomprepopulatedconversion.MainActivity}: java.lang.IllegalStateException: Pre-packaged database has an invalid schema: Authors(a.a.so59756782javaroomprepopulatedconversion.Authors).
 Expected:
TableInfo{name='Authors', columns={AuthorName=Column{name='AuthorName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}, AuthorID=Column{name='AuthorID', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}}, foreignKeys=[], indices=[]}
 Found:
TableInfo{name='Authors', columns={AuthorID=Column{name='AuthorID', type='INTEGER', affinity='3', notNull=false, primaryKeyPosition=1, defaultValue='null'}, AuthorName=Column{name='AuthorName', type='TEXT', affinity='2', notNull=false, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}

转换

根据答案，以下内容用于转换数据库:-

BEGIN TRANSACTION;
DROP TABLE IF EXISTS Author_By_Source;
DROP TABLE IF EXISTS Authors;
DROP TABLE IF EXISTS Sources;
CREATE TABLE IF NOT EXISTS `Authors` (`AuthorID` INTEGER NOT NULL, `AuthorName` TEXT, PRIMARY KEY(`AuthorID`));
CREATE TABLE IF NOT EXISTS `Sources` (`SourceID` INTEGER NOT NULL, `SourceName` TEXT, PRIMARY KEY(`SourceID`));
/* Create the table so that it would cause issues in Room  */
CREATE TABLE IF NOT EXISTS Author_By_Source (
    AuthorId INTEGER, SourceId INTEGER, 
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID), 
    FOREIGN KEY (SourceID) REFERENCES Sources(SourceID)
);

INSERT INTO Authors (AuthorName) VALUES ('Fred'),('Mary'),('Joan'),('Bert'),('Alan');
INSERT INTO Sources (SourceName) VALUES ('S1'),('S2'),('S3'),('S4'),('S5');
INSERT INTO Author_By_Source VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(1,4),(3,4),(5,2);

SELECT AuthorName, SourceName 
    FROM Author_By_Source 
        JOIN Authors ON Author_By_Source.AuthorID = Authors.AuthorID
        JOIN Sources ON Author_By_Source.SourceID = Sources.SourceID
;   

/* Just once */
PRAGMA foreign_keys = 0;

/* Per table */
DROP TABLE IF EXISTS Author_By_Source_new;
DROP TABLE IF EXISTS Author_By_Source_original;
CREATE TABLE IF NOT EXISTS `Author_By_Source_new` 
    (
        `AuthorID` INTEGER NOT NULL, 
        `SourceID` INTEGER NOT NULL, 
        PRIMARY KEY(`AuthorID`, `SourceID`), 
        FOREIGN KEY(`AuthorID`) REFERENCES `Authors`(`AuthorID`) ON UPDATE NO ACTION ON DELETE NO ACTION , 
        FOREIGN KEY(`SourceID`) REFERENCES `Sources`(`SourceID`) ON UPDATE NO ACTION ON DELETE NO ACTION 
    );
DELETE FROM Author_By_Source_new;
INSERT INTO Author_By_Source_new SELECT * FROM Author_By_Source;
ALTER TABLE Author_By_Source RENAME TO Author_By_Source_original;
ALTER TABLE Author_By_Source_new RENAME TO Author_By_Source;
/* IF HAPPY THEN DO */
DROP TABLE IF EXISTS Author_By_Source_original;

-- ...... do the equivalent for all tables

CREATE INDEX IF NOT EXISTS `index_Author_By_Source_SourceID` ON `Author_By_Source` (`SourceID`);
CREATE INDEX IF NOT EXISTS `index_Author_By_Source_AuthorID` ON `Author_By_Source` (`AuthorID`);

COMMIT;

/* Just once */
PRAGMA foreign_keys = 1;
PRAGMA foreign_key_check;
PRAGMA integrity_check;
SELECT sql FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite%';

SELECT AuthorName, SourceName 
    FROM Author_By_Source 
        JOIN Authors ON Author_By_Source.AuthorID = Authors.AuthorID
        JOIN Sources ON Author_By_Source.SourceID = Sources.SourceID
;

数据库已关闭，检查时间戳和大小，然后数据库文件替换 Assets 文件夹中的现有文件。该应用程序已卸载，然后在 MainActivity 中使用以下内容重新运行:-

    myDB = Room.databaseBuilder(this,MyAppDatabase.class,"mydb")
            .createFromAsset("mydb")
            .allowMainThreadQueries()
            .build();
    myDB.getOpenHelper().getWritableDatabase();

    List<AuthorWithSources> authorWithSourcesList = myDB.allDao().getAllAuthorsWithSources();
    for (AuthorWithSources aws: authorWithSourcesList) {
        Log.d("AUTHORSOURCEINFO","Author = " + aws.authors.getAuthorName() + "Source = " + aws.sources.getSourceName());
    }
}

• MyAppDatabase 是 @Database 类。
• allowMainThreadQueries 用于方便起见。

• myDB.getOpenHelper().getWritableDatabase();用于强制立即打开数据库并因此复制数据库。

• getAllAuthorsWithSources()正如它所说，使用
@Query("SELECT * FROM Author_By_Source")
List<AuthorWithSources> getAllAuthorsWithSources();

AuthorWithSources 是:-

public class AuthorWithSources {

    @Embedded
    AuthorBySource authorBySource;
    @Relation(entity = Authors.class,parentColumn = "AuthorID",entityColumn = "AuthorID")
    Authors authors;
    @Relation(entity = Sources.class,parentColumn = "SourceID",entityColumn = "SourceID")
    Sources sources;
}

结果

没有错误并且在日志中:-

2020-01-16 10:09:23.623 D/AUTHORSOURCEINFO: Author = FredSource = S1
2020-01-16 10:09:23.623 D/AUTHORSOURCEINFO: Author = MarySource = S2
2020-01-16 10:09:23.623 D/AUTHORSOURCEINFO: Author = JoanSource = S3
2020-01-16 10:09:23.623 D/AUTHORSOURCEINFO: Author = BertSource = S4
2020-01-16 10:09:23.623 D/AUTHORSOURCEINFO: Author = AlanSource = S5
2020-01-16 10:09:23.623 D/AUTHORSOURCEINFO: Author = FredSource = S4
2020-01-16 10:09:23.623 D/AUTHORSOURCEINFO: Author = JoanSource = S4
2020-01-16 10:09:23.623 D/AUTHORSOURCEINFO: Author = AlanSource = S2

替代方法

有一个工具，作为一个应用程序，可以转换数据库以供 Room 使用，并为实体/Dao 生成 java 代码。

该工具的详细信息请参见 RoomExistingSQLiteDBConverter

使用该工具和

• 将生成的java源代码复制到项目中
• 访问每个文件来解析导入(假设输入了正确的包名称，否则还必须调整或添加包)，然后
• 在项目中创建 Assets 文件夹，然后
• 复制生成的数据库

• 最后创建代码来使用数据库，例如

  soanswersDatabase = Room.databaseBuilder(this,SoanswersDatabase.class,SoanswersDatabase.DBNAME)
          .allowMainThreadQueries()
          .createFromAsset(SoanswersDatabase.DBNAME)
          .build();
  List<Author_By_Source> authorBySourceList = soanswersDatabase.getAuthor_By_SourceDao().getEveryAuthor_By_Source();
  for (Author_By_Source abs: authorBySourceList) {
      Log.d("AUTHORSOURCEINFO",
              "AuthorReference = " + String.valueOf(abs.getAuthorId())
              + " Sourcereference = " + String.valueOf(abs.getSourceId())
      );
  }
  

• 即上面是唯一输入的代码。结果:-

2020-01-16 10:46:27.907 D/AUTHORSOURCEINFO: AuthorReference = 1 Sourcereference = 1
2020-01-16 10:46:27.907 D/AUTHORSOURCEINFO: AuthorReference = 2 Sourcereference = 2
2020-01-16 10:46:27.907 D/AUTHORSOURCEINFO: AuthorReference = 3 Sourcereference = 3
2020-01-16 10:46:27.907 D/AUTHORSOURCEINFO: AuthorReference = 4 Sourcereference = 4
2020-01-16 10:46:27.907 D/AUTHORSOURCEINFO: AuthorReference = 5 Sourcereference = 5
2020-01-16 10:46:27.907 D/AUTHORSOURCEINFO: AuthorReference = 1 Sourcereference = 4
2020-01-16 10:46:27.907 D/AUTHORSOURCEINFO: AuthorReference = 3 Sourcereference = 4
2020-01-16 10:46:27.907 D/AUTHORSOURCEINFO: AuthorReference = 5 Sourcereference = 2