Room Create Table with Index

Question

I am migrating my Room database. I want to add new table. So I created the Entry class like this:

@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...
}

Then my Dao looks like this:

@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);
}

Then lastly on my database class:

@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;
    }
}

After I run it, I got an error that looks like this:

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}

I think the problem is in my CREATE statement. I've been searching for the proper SQL query for this but still failed to find some. Somebody help!

Answer 1

You have 2 errors, one regarding the NOT NULL column property, the other regarding foreign keys.

Here are the differences :

Expected :

projectId notNull=true

dimensionId notNull=true

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

Found

projectId notNull=false

dimensionId notNull=false

foreignKeys indices=null

I can help with the first problem, in your Entity, the type long can't be nullable. Change it to Long to make it work.

For the second one, add something like this to your migrate function

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