Skip to content

Instantly share code, notes, and snippets.

@hameno
Forked from ChristophKaser/DatabaseMigration.java
Created July 26, 2019 10:30
Show Gist options
  • Save hameno/28bf02004c6c2a1b89b0e02cd8169c4a to your computer and use it in GitHub Desktop.
Save hameno/28bf02004c6c2a1b89b0e02cd8169c4a to your computer and use it in GitHub Desktop.
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