Room Database Migration Utility

__Usage.java

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

DatabaseMigration.java

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.sqlite.db.SupportSQLiteDatabase;

/**
 * Utility class to alter tables during a database migration
 */
@RequiresApi(21)
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) {
		Map<String, ColumnDefinition> existingColumns = readExistingColumns(database, tableName);
		Map<String, String> existingIndices = readExistingIndices(database, tableName);

		List<ColumnDefinition> columns = createNewTable(database, existingColumns);

		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 Map<String, ColumnDefinition> readExistingColumns(SupportSQLiteDatabase database, String tableName) {
		Map<String, ColumnDefinition> columns = new HashMap<>();
		try (Cursor cursor = database.query("PRAGMA table_info('" + tableName + "')", null)) {
			int idxName = cursor.getColumnIndex("name");
			int idxType = cursor.getColumnIndex("type");
			int idxNotNull = cursor.getColumnIndex("notnull");
			int idxPrimaryKeyPosition = cursor.getColumnIndex("pk");
			int idxDefaultValue = cursor.getColumnIndex("dflt_value");

			while (cursor.moveToNext()) {
				String name = cursor.getString(idxName);
				String type = cursor.getString(idxType);
				boolean notNull = cursor.getInt(idxNotNull) != 0;
				int primaryKeyPosition = cursor.getInt(idxPrimaryKeyPosition);
				String defaultValue = cursor.getString(idxDefaultValue);
				columns.put(name, new ColumnDefinition(name, type, notNull, primaryKeyPosition, defaultValue));
			}
		}
		return columns;
	}

	private List<ColumnDefinition> createNewTable(SupportSQLiteDatabase database, Map<String, ColumnDefinition> existingColumns) {
		List<ColumnDefinition> retval = new ArrayList<>();
		StringBuilder createTableBuilder = new StringBuilder();
		createTableBuilder.append("CREATE TABLE `__new_").append(tableName).append("` (");
		boolean first = true;
		for (ColumnDefinition column : existingColumns.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.isNotNull, 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" : "");
		}
	}
}