Writing Migrations
A Migration itself has only one required method: migrate(). You are given a SupportSQLiteDatabase, which we saw SupportSQLiteDatabase in the chapter on the support database API. You can use the SupportSQLiteDatabase to execute whatever SQL statements you need to change the database schema to what you need.
The Migration constructor takes two parameters: the old schema version number and the new schema version number. Hence, the recommended pattern is to use anonymous inner classes, where you can provide the migrate() method to use for migrating the schema between that particular pair of schema versions.
To determine what needs to be done, you need to examine that schema JSON and determine what is different between the old and the new. Someday, we may get some tools to help with this. For now, you are largely stuck “eyeballing” the SQL. You can then craft the ALTER TABLE or other statements necessary to change the schema, much as you might have done in onUpgrade() of a SQLiteOpenHelper.
For example, the Trips/RoomMigrations sample project has a FROM_1_TO_2 migration:
static final Migration FROM_1_TO_2=new Migration(1,2) {
@Override
public void migrate(SupportSQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS `lodgings` (`id` TEXT NOT NULL, `title` TEXT, `duration` INTEGER NOT NULL, `priority` INTEGER, `startTime` INTEGER, `creationTime` INTEGER, `updateTime` INTEGER, `address` TEXT, `tripId` TEXT, PRIMARY KEY(`id`), FOREIGN KEY(`tripId`) REFERENCES `trips`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )");
db.execSQL("CREATE INDEX `index_lodgings_tripId` ON `lodgings` (`tripId`)");
db.execSQL("CREATE TABLE IF NOT EXISTS `flights` (`id` TEXT NOT NULL, `title` TEXT, `duration` INTEGER NOT NULL, `priority` INTEGER, `startTime` INTEGER, `creationTime` INTEGER, `updateTime` INTEGER, `departingAirport` TEXT, `arrivingAirport` TEXT, `airlineCode` TEXT, `flightNumber` TEXT, `seatNumber` TEXT, `tripId` TEXT, PRIMARY KEY(`id`), FOREIGN KEY(`tripId`) REFERENCES `trips`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )");
db.execSQL("CREATE INDEX `index_flights_tripId` ON `flights` (`tripId`)");
}
};
Here, we create two tables and two indexes in migrate(). The SQL is mostly copied from the 2.json file, representing the schema for version 2:
{
"formatVersion": 1,
"database": {
"version": 2,
"identityHash": "69efe3a24b62764afa37e5eb0f162fd9",
"entities": [
{
"tableName": "trips",
"createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` TEXT NOT NULL, `title` TEXT, `duration` INTEGER NOT NULL, `priority` INTEGER, `startTime` INTEGER, `creationTime` INTEGER, `updateTime` INTEGER, PRIMARY KEY(`id`))",
"fields": [
{
"fieldPath": "id",
"columnName": "id",
"affinity": "TEXT",
"notNull": true
},
{
"fieldPath": "title",
"columnName": "title",
"affinity": "TEXT",
"notNull": false
},
{
"fieldPath": "duration",
"columnName": "duration",
"affinity": "INTEGER",
"notNull": true
},
{
"fieldPath": "priority",
"columnName": "priority",
"affinity": "INTEGER",
"notNull": false
},
{
"fieldPath": "startTime",
"columnName": "startTime",
"affinity": "INTEGER",
"notNull": false
},
{
"fieldPath": "creationTime",
"columnName": "creationTime",
"affinity": "INTEGER",
"notNull": false
},
{
"fieldPath": "updateTime",
"columnName": "updateTime",
"affinity": "INTEGER",
"notNull": false
}
],
"primaryKey": {
"columnNames": [
"id"
],
"autoGenerate": false
},
"indices": [],
"foreignKeys": []
},
{
"tableName": "lodgings",
"createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` TEXT NOT NULL, `title` TEXT, `duration` INTEGER NOT NULL, `priority` INTEGER, `startTime` INTEGER, `creationTime` INTEGER, `updateTime` INTEGER, `address` TEXT, `tripId` TEXT, PRIMARY KEY(`id`), FOREIGN KEY(`tripId`) REFERENCES `trips`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )",
"fields": [
{
"fieldPath": "id",
"columnName": "id",
"affinity": "TEXT",
"notNull": true
},
{
"fieldPath": "title",
"columnName": "title",
"affinity": "TEXT",
"notNull": false
},
{
"fieldPath": "duration",
"columnName": "duration",
"affinity": "INTEGER",
"notNull": true
},
{
"fieldPath": "priority",
"columnName": "priority",
"affinity": "INTEGER",
"notNull": false
},
{
"fieldPath": "startTime",
"columnName": "startTime",
"affinity": "INTEGER",
"notNull": false
},
{
"fieldPath": "creationTime",
"columnName": "creationTime",
"affinity": "INTEGER",
"notNull": false
},
{
"fieldPath": "updateTime",
"columnName": "updateTime",
"affinity": "INTEGER",
"notNull": false
},
{
"fieldPath": "address",
"columnName": "address",
"affinity": "TEXT",
"notNull": false
},
{
"fieldPath": "tripId",
"columnName": "tripId",
"affinity": "TEXT",
"notNull": false
}
],
"primaryKey": {
"columnNames": [
"id"
],
"autoGenerate": false
},
"indices": [
{
"name": "index_lodgings_tripId",
"unique": false,
"columnNames": [
"tripId"
],
"createSql": "CREATE INDEX `index_lodgings_tripId` ON `${TABLE_NAME}` (`tripId`)"
}
],
"foreignKeys": [
{
"table": "trips",
"onDelete": "CASCADE",
"onUpdate": "NO ACTION",
"columns": [
"tripId"
],
"referencedColumns": [
"id"
]
}
]
},
{
"tableName": "flights",
"createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`id` TEXT NOT NULL, `title` TEXT, `duration` INTEGER NOT NULL, `priority` INTEGER, `startTime` INTEGER, `creationTime` INTEGER, `updateTime` INTEGER, `departingAirport` TEXT, `arrivingAirport` TEXT, `airlineCode` TEXT, `flightNumber` TEXT, `seatNumber` TEXT, `tripId` TEXT, PRIMARY KEY(`id`), FOREIGN KEY(`tripId`) REFERENCES `trips`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )",
"fields": [
{
"fieldPath": "id",
"columnName": "id",
"affinity": "TEXT",
"notNull": true
},
{
"fieldPath": "title",
"columnName": "title",
"affinity": "TEXT",
"notNull": false
},
{
"fieldPath": "duration",
"columnName": "duration",
"affinity": "INTEGER",
"notNull": true
},
{
"fieldPath": "priority",
"columnName": "priority",
"affinity": "INTEGER",
"notNull": false
},
{
"fieldPath": "startTime",
"columnName": "startTime",
"affinity": "INTEGER",
"notNull": false
},
{
"fieldPath": "creationTime",
"columnName": "creationTime",
"affinity": "INTEGER",
"notNull": false
},
{
"fieldPath": "updateTime",
"columnName": "updateTime",
"affinity": "INTEGER",
"notNull": false
},
{
"fieldPath": "departingAirport",
"columnName": "departingAirport",
"affinity": "TEXT",
"notNull": false
},
{
"fieldPath": "arrivingAirport",
"columnName": "arrivingAirport",
"affinity": "TEXT",
"notNull": false
},
{
"fieldPath": "airlineCode",
"columnName": "airlineCode",
"affinity": "TEXT",
"notNull": false
},
{
"fieldPath": "flightNumber",
"columnName": "flightNumber",
"affinity": "TEXT",
"notNull": false
},
{
"fieldPath": "seatNumber",
"columnName": "seatNumber",
"affinity": "TEXT",
"notNull": false
},
{
"fieldPath": "tripId",
"columnName": "tripId",
"affinity": "TEXT",
"notNull": false
}
],
"primaryKey": {
"columnNames": [
"id"
],
"autoGenerate": false
},
"indices": [
{
"name": "index_flights_tripId",
"unique": false,
"columnNames": [
"tripId"
],
"createSql": "CREATE INDEX `index_flights_tripId` ON `${TABLE_NAME}` (`tripId`)"
}
],
"foreignKeys": [
{
"table": "trips",
"onDelete": "CASCADE",
"onUpdate": "NO ACTION",
"columns": [
"tripId"
],
"referencedColumns": [
"id"
]
}
]
}
],
"setupQueries": [
"CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)",
"INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, \"69efe3a24b62764afa37e5eb0f162fd9\")"
]
}
}In the JSON, the createSql properties have the table name as a template-style macro (${TABLE_NAME}), which you will need to replace with the actual table name. The backticks are supported in SQLite as they are in MySQL, and since they cause no harm here, usually it is simpler just to leave them in there.
Prev Table of Contents Next
This book is licensed under the Creative Commons Attribution-ShareAlike 4.0 International license.