房间创建带索引的表

Question

我正在迁移我的房间数据库。我想添加新表。所以我像这样创建了 Entry 类：

@Entity(foreignKeys = {@ForeignKey(entity = Project.class,
    parentColumns = "projectId",
    childColumns = "projectId",
    onDelete = ForeignKey.CASCADE)},
    indices = {
            @Index(name = "projectId_index", value = {"projectId"})
    })
public class ProjectDimension {
    @PrimaryKey(autoGenerate = true)
    private long dimensionId;
    @ColumnInfo
    private long projectId;
    @ColumnInfo
    private String name;
    @ColumnInfo
    private String value;
    // getters and setters here...
}

那么我的道是这样的：

@Dao
public interface ProjectDimensionDao {
    @Query("SELECT * FROM ProjectDimension")
    Single<List<ProjectDimension>> getAll();
    @Query("SELECT * FROM ProjectDimension WHERE projectId = :projectId")
    Single<List<ProjectDimension>> getByProject(long projectId);
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    long insert(ProjectDimension projectDimension);
    @Delete
    void delete(ProjectDimension projectDimension);
}

最后在我的数据库类上：

@Database(entities = {
    Contact.class,
    ContactEmail.class,
    ContactPhone.class,
    Monitoring.class,
    Organization.class,
    OrgEmail.class,
    OrgPhone.class,
    Project.class,
    ProjectContact.class,
    ProjectLocation.class,
    ProjectDimension.class
}, version = 2)
public abstract class MonitoringDatabase extends RoomDatabase {
    private static MonitoringDatabase instance;
    // other Data Access Objects (DAO) here...
    public abstract ProjectDimensionDao projectDimensionDao();
    public static MonitoringDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(context.getApplicationContext(),
                    MonitoringDatabase.class, "monitoring-database")
                        .addMigrations(MIGRATION_1_2)
                        .build();
        }
        return instance;
    }
    /**
     * Upgrade database from version 1 to 2.
     * Details: Added new table named ProjectDimension
     */
    private static final Migration MIGRATION_1_2 = new Migration(1, 2) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // create ProjectDimension table
            database.execSQL("CREATE TABLE `ProjectDimension` (`dimensionId` INTEGER, `projectId` INTEGER, " +
                    "`name` TEXT, `value` TEXT, " +
                    "PRIMARY KEY(`dimensionId`), " +
                    "FOREIGN KEY(`projectId`) REFERENCES `Project`(`projectId`) ON DELETE CASCADE)");
        }
    };
    public static void destroyInstance() {
        instance = null;
    }
}

运行它后，我收到如下所示的错误：

Expected:
TableInfo{name='ProjectDimension', columns={name=Column{name='name', type='TEXT', notNull=false, primaryKeyPosition=0}, value=Column{name='value', type='TEXT', notNull=false, primaryKeyPosition=0}, projectId=Column{name='projectId', type='INTEGER', notNull=true, primaryKeyPosition=0}, dimensionId=Column{name='dimensionId', type='INTEGER', notNull=true, primaryKeyPosition=1}}, foreignKeys=[ForeignKey{referenceTable='Project', onDelete='CASCADE', onUpdate='NO ACTION', columnNames=[projectId], referenceColumnNames=[projectId]}], indices=[Index{name='projectId_index', unique=false, columns=[projectId]}]}
Found:
TableInfo{name='ProjectDimension', columns={name=Column{name='name', type='TEXT', notNull=false, primaryKeyPosition=0}, value=Column{name='value', type='TEXT', notNull=false, primaryKeyPosition=0}, projectId=Column{name='projectId', type='INTEGER', notNull=false, primaryKeyPosition=0}, dimensionId=Column{name='dimensionId', type='INTEGER', notNull=false, primaryKeyPosition=1}}, foreignKeys=[ForeignKey{referenceTable='Project', onDelete='CASCADE', onUpdate='NO ACTION', columnNames=[projectId], referenceColumnNames=[projectId]}], indices=null}

我认为问题出在我的 CREATE 语句中。我一直在寻找合适的SQL查询，但仍然找不到一些。有人帮忙！

Answer 1

您有 2 个错误，一个关于 NOT NULL 列属性，另一个关于外键。

以下是区别：

预期：

projectId notNull=true

dimensionId notNull=true

foreignKeys indices=[Index{name='projectId_index'， unique=false， columns=[projectId]}]}

发现

projectId notNull=false

dimensionId notNull=false

外键索引=空

我可以帮助解决第一个问题，在您的实体中，long类型不能为空。将其更改为Long以使其正常工作。

对于第二个，将类似这样的内容添加到您的迁移函数中

database.execSQL("CREATE INDEX projectId_index ON Project (projectId)");

Answer 2

您需要将

projectId 作为@nonNull注释。