Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Room Database Migration Utility
DatabaseMigration.alterTable("user")
.addColumn("rowid", "INTEGER", true, 1, null)
.makeNotNull("type")
.makeNotNull("longitude")
.removeColumn("dummy")
.changeColumn("name", "text", true, false)
.renameIndex("old_name", "new_name")
.removeIndex("index_to_remove")
.addIndex("index_to_add", "CREATE INDEX index_to_add ON user(type)")
.setPrimaryKey("dummy", "name")
.execute(database);
package cc.gunz.mobilecatalog.database.base;
import android.database.Cursor;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Set;
import androidx.annotation.NonNull;
import androidx.annotation.Nullable;
import androidx.annotation.RequiresApi;
import androidx.room.util.TableInfo;
import androidx.sqlite.db.SupportSQLiteDatabase;
/**
* Utlity class to alter tables during a database migration
*/
@RequiresApi(20)
public class DatabaseMigration {
private final String tableName;
private final Set<String> columnsToRemove = new HashSet<>();
private final Map<String, ColumnDefinition> columnsToChange = new HashMap<>();
private List<String> primaryKeyOverride = null;
private final Map<String, Boolean> columnsToChangeNullability = new HashMap<>();
private final List<ColumnDefinition> columnsToAdd = new ArrayList<>();
private final Set<String> indicesToRemove = new HashSet<>();
private final Map<String, String> indicesToAdd = new HashMap<>();
private final Map<String, String> indicesToRename = new HashMap<>();
private boolean makeAllNotNull = false;
public static DatabaseMigration alterTable(String tablename) {
return new DatabaseMigration(tablename);
}
private DatabaseMigration(String tableName) {
this.tableName = tableName;
}
/**
* Adds a new column
*
* @param columnName name of the column to add
* @param datatype sqlite datatype
* @param isNotNull whether this column is "not null"
* @param primaryKeyPosition position of this column in the primary key, 0 for columns that are not part of the primary key
* @param defaultValueExpr SQL expression to define the new contents of the column. if null, the column will not be filled with data
*/
public DatabaseMigration addColumn(String columnName, String datatype, boolean isNotNull, int primaryKeyPosition, @Nullable String defaultValueExpr) {
columnsToAdd.add(new ColumnDefinition(columnName, datatype, isNotNull, primaryKeyPosition, defaultValueExpr));
return this;
}
public DatabaseMigration addRowId() {
return addColumn("rowid", "INTEGER", true, 1, null);
}
/**
* Changes the datatype of a column while keeping its contents the same
*
* @param columnName name of the column to change
* @param datatype sqlite datatype
* @param isNotNull whether this column is "not null"
* @param primaryKeyPosition position of this column in the primary key, 0 for columns that are not part of the primary key
*/
public DatabaseMigration changeColumn(String columnName, String datatype, boolean isNotNull, int primaryKeyPosition) {
return changeColumn(columnName, datatype, isNotNull, primaryKeyPosition, "`" + columnName + "`");
}
/**
* Changes the datatype of a column
*
* @param columnName name of the column to change
* @param datatype sqlite datatype
* @param isNotNull whether this column is "not null"
* @param primaryKeyPosition position of this column in the primary key, 0 for columns that are not part of the primary key
* @param valueExpr SQL expression to define the new contents of the column. if null, the column will not be filled with data
*/
public DatabaseMigration changeColumn(String columnName, String datatype, boolean isNotNull, int primaryKeyPosition, @Nullable String valueExpr) {
columnsToChange.put(columnName.toLowerCase(Locale.US), new ColumnDefinition(columnName, datatype, isNotNull, primaryKeyPosition, valueExpr));
return this;
}
/**
* Changes a column definition to be NOT NULL
*/
public DatabaseMigration makeNotNull(String... columnNames) {
for (String columnName : columnNames) {
columnsToChangeNullability.put(columnName.toLowerCase(Locale.US), Boolean.FALSE);
}
return this;
}
/**
* Changes a column definition to be nullable
*/
public DatabaseMigration makeNullable(String... columnNames) {
for (String columnName : columnNames) {
columnsToChangeNullability.put(columnName.toLowerCase(Locale.US), Boolean.TRUE);
}
return this;
}
/**
* Changes every column to be nonnull except columns explicitly mentioned in makeNullable
*/
public DatabaseMigration makeAllNotNull() {
makeAllNotNull = true;
return this;
}
/**
* Changes the primary key of the table to the specified array
*/
public DatabaseMigration setPrimaryKey(String... primaryKeyColumns) {
primaryKeyOverride = Arrays.asList(primaryKeyColumns);
return this;
}
/**
* removes a column from the table
*
* @param columnName the column to remove
*/
public DatabaseMigration removeColumn(String columnName) {
this.columnsToRemove.add(columnName.toLowerCase(Locale.US));
return this;
}
/**
* removes an index from the table
*
* @param indexName the index to remove
*/
public DatabaseMigration removeIndex(String indexName) {
this.indicesToRemove.add(indexName.toLowerCase(Locale.US));
return this;
}
/**
* add an index to the table or changes an existing one
*
* @param indexName the name of index to add or change
* @param sql the complete sql to create the index
*/
public DatabaseMigration addIndex(String indexName, String sql) {
this.indicesToAdd.put(indexName.toLowerCase(Locale.US), sql);
return this;
}
/**
* rename an index
*
* @param oldName the name of index to rename
* @param newName the new name of the index
*/
public DatabaseMigration renameIndex(String oldName, String newName) {
this.indicesToRename.put(oldName.toLowerCase(Locale.US), newName);
return this;
}
public void execute(SupportSQLiteDatabase database) {
TableInfo existingTableInfo = TableInfo.read(database, tableName);
Map<String, String> existingIndices = readExistingIndices(database, tableName);
List<ColumnDefinition> columns = createNewTable(database, existingTableInfo);
copyValuesToNewTable(database, columns);
database.execSQL("DROP TABLE `" + tableName + "`");
database.execSQL("ALTER TABLE `__new_" + tableName + "` RENAME TO `" + tableName + "`");
createIndices(database, existingIndices);
}
private Map<String, String> readExistingIndices(SupportSQLiteDatabase database, String tableName) {
try (Cursor cursor = database.query("SELECT name, sql FROM sqlite_master WHERE type='index' AND tbl_name = ?", new String[]{tableName})) {
if (cursor.getCount() == 0)
return Collections.emptyMap();
Map<String, String> retval = new LinkedHashMap<>(cursor.getCount());
if (cursor.moveToFirst()) {
do {
String indexName = cursor.getString(0);
String sql = cursor.getString(1);
if (indexName.startsWith("sqlite_autoindex_")) {
continue;
}
if (sql == null) {
if (!indicesToRemove.contains(indexName.toLowerCase(Locale.US))) {
throw new UnsupportedOperationException("Could not get index sql for " + indexName);
}
continue;
}
retval.put(indexName, sql);
} while (cursor.moveToNext());
}
return retval;
}
}
private List<ColumnDefinition> createNewTable(SupportSQLiteDatabase database, TableInfo existingTableInfo) {
List<ColumnDefinition> retval = new ArrayList<>();
StringBuilder createTableBuilder = new StringBuilder();
createTableBuilder.append("CREATE TABLE `__new_").append(tableName).append("` (");
boolean first = true;
for (TableInfo.Column column : existingTableInfo.columns.values()) {
if (!columnsToRemove.contains(column.name.toLowerCase(Locale.US))) {
if (!first) {
createTableBuilder.append(", ");
}
first = false;
ColumnDefinition definition = columnsToChange.get(column.name.toLowerCase(Locale.US));
if (definition == null) {
definition = new ColumnDefinition(column.name, column.type, column.notNull, column.primaryKeyPosition, "`" + column.name + "`");
}
Boolean newNullability = columnsToChangeNullability.get(column.name.toLowerCase(Locale.US));
if (newNullability == null && makeAllNotNull) {
newNullability = false;
}
if (newNullability != null) {
definition = definition.changeIsNotNull(!newNullability);
}
createTableBuilder.append(definition.getColumnDefinition());
retval.add(definition);
}
}
for (ColumnDefinition columnEntry : columnsToAdd) {
if (!first) {
createTableBuilder.append(", ");
}
first = false;
createTableBuilder.append(columnEntry.getColumnDefinition());
retval.add(columnEntry);
}
List<String> primaryKeyColumns;
if (primaryKeyOverride != null) {
primaryKeyColumns = primaryKeyOverride;
} else {
List<ColumnDefinition> primaryKeyColumnsDef = new ArrayList<>();
for (ColumnDefinition d : retval) {
if (d.primaryKeyPosition != 0) {
primaryKeyColumnsDef.add(d);
}
}
Collections.sort(primaryKeyColumnsDef, (c1, c2) -> Integer.compare(c1.primaryKeyPosition, c2.primaryKeyPosition));
primaryKeyColumns = new ArrayList<>(primaryKeyColumnsDef.size());
for (ColumnDefinition columnDefinition : primaryKeyColumnsDef) {
primaryKeyColumns.add(columnDefinition.name);
}
}
if (!primaryKeyColumns.isEmpty()) {
createTableBuilder.append(", PRIMARY KEY (");
first = true;
for (String col : primaryKeyColumns) {
if (!first) {
createTableBuilder.append(", ");
}
first = false;
createTableBuilder.append(col);
}
createTableBuilder.append(')');
}
createTableBuilder.append(')');
database.execSQL(createTableBuilder.toString());
return retval;
}
private void copyValuesToNewTable(SupportSQLiteDatabase database, List<ColumnDefinition> columns) {
StringBuilder insertBuilder = new StringBuilder();
StringBuilder selectBuilder = new StringBuilder();
insertBuilder.append("INSERT INTO `__new_").append(tableName).append("`(");
boolean first = true;
for (ColumnDefinition column : columns) {
if (column.valueExpr != null) {
if (!first) {
insertBuilder.append(", ");
selectBuilder.append(", ");
}
first = false;
insertBuilder.append(column.name);
selectBuilder.append(column.valueExpr);
}
}
insertBuilder.append(") SELECT ").append(selectBuilder).append(" FROM ").append(tableName);
database.execSQL(insertBuilder.toString());
}
private void createIndices(SupportSQLiteDatabase database, Map<String, String> existingIndices) {
for (Map.Entry<String, String> indexEntry : existingIndices.entrySet()) {
if (indicesToRemove.contains(indexEntry.getKey().toLowerCase(Locale.US)) || indicesToAdd.containsKey(indexEntry.getKey().toLowerCase(Locale.US))) {
continue;
}
String sql = indexEntry.getValue();
String newName = indicesToRename.get(indexEntry.getKey().toLowerCase(Locale.US));
if (newName != null) {
sql = sql.replaceFirst("(?i)^(CREATE\\s+(?:UNIQUE\\s+)?INDEX\\s+)[`']?[_\\w]+[`']?\\b", "$1`" + newName + "`");
}
database.execSQL(sql);
}
for (String sql : indicesToAdd.values()) {
database.execSQL(sql);
}
}
private static class ColumnDefinition {
final String name;
final String type;
final boolean isNotNull;
final int primaryKeyPosition;
private final @Nullable String valueExpr;
public ColumnDefinition(String name, String type, boolean isNotNull, int primaryKeyPosition, @Nullable String valueExpr) {
this.name = name;
this.type = type;
this.isNotNull = isNotNull;
this.primaryKeyPosition = primaryKeyPosition;
this.valueExpr = valueExpr;
}
public ColumnDefinition changeIsNotNull(boolean isNotNull) {
String newValueExpr = this.valueExpr;
if (isNotNull && newValueExpr != null) {
String defaultVal = "0";
if ("text".equalsIgnoreCase(type)) {
defaultVal = "''";
}
newValueExpr = "coalesce(" + this.valueExpr + ", " + defaultVal + ")";
}
return new ColumnDefinition(name, type, isNotNull, primaryKeyPosition, newValueExpr);
}
@NonNull
public String getColumnDefinition() {
return name + " " + type + (isNotNull ? " NOT NULL" : "");
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.