BuildTask for SilverStripe to remove obsolete tables, columns and indexes from the database

gistfile1.php

<?php
/**
 * Remove old tables, columns, and indexes from a SilverStripe database.
 *
 * Define your obsolete tables, columns and indexes in {@link $deleted_tables},
 * {@link deleted_columns} and {@link deleted_indexes} and these will be deleted
 * from the database.
 *
 * In addition to that, it will automatically remove any tables and columns prefixed with "_obsolete".
 */
class CleanupDatabaseTask extends BuildTask {

	/**
	 * If any of these tables are found in the database, they will be removed.
	 * @var array
	 */
	private static $deleted_tables = array(
		'SomeOldTable'
	);

	/**
	 * These columns should be deleted. * key indicates any table with columns listed in the array
	 * value should be removed. If the key is a specific table, only columns listed in the array
	 * for that table will be removed.
	 *
	 * @var array
	 */
	private static $deleted_columns = array(
		'*' => array('SomeOldColumn'),
		'SomeOldTable' => array('Status', 'Version')
	);

	/**
	 * If any of these indexes are found in any tables, they will be removed.
	 * @var array
	 */
	private static $deleted_indexes = array(
		'SomeOldIndex'
	);

	protected function log($message) {
		echo $message . PHP_EOL;
	}

	protected function execute($sql) {
		if(!empty($_REQUEST['dryrun'])) {
			$this->log(sprintf('DRY RUN: Not running query: %s', $sql));
			return true;
		}

		DB::query($sql);
		$this->log(sprintf('INFO: Successfully executed SQL: %s', $sql));
		return true;
	}

	public function run($request) {
		global $databaseConfig;
		$database = $databaseConfig['database'];

		$this->log(sprintf('=== Using database %s ===', $database));

		if(!empty($_REQUEST['dryrun'])) {
			$this->log('=== Running in dry run mode. SQL will be displayed, but not executed ===');
		}

		if(!Director::is_cli()) {
			$this->log('ERROR: Please run this task under the command line');
			return;
		}

		if(empty($_REQUEST['flush'])) {
			$this->log('ERROR: Please run flush=1 to ensure manifest is up to date');
			return;
		}

		foreach($this->config()->deleted_tables as $tableName) {
			if(!DB::query(sprintf('SHOW TABLES LIKE \'%s\'', $tableName))->value()) {
				$this->log(sprintf('INFO: Table %s was not found in database %s. Skipping', $tableName, $database));
				continue;
			}

			$this->execute(sprintf('DROP TABLE "%s"', $tableName));
		}

		$obsoleteTables = DB::query(sprintf(
			'SHOW TABLES WHERE Tables_in_%s REGEXP \'_obsolete|_copy|_backup\'', $database
		));

		foreach($obsoleteTables as $table) {
			$tableName = $table[sprintf('Tables_in_%s', $database)];
			$this->execute(sprintf('DROP TABLE "%s"', $tableName));
		}

		foreach(DB::query(sprintf('SHOW TABLES FROM "%s"', $database)) as $table) {
			$tableName = $table[sprintf('Tables_in_%s', $database)];

			// search through indexes, remove indexes marked for deletion
			foreach(DB::query(sprintf('SHOW INDEXES FROM "%s"', $tableName)) as $index) {
				if(in_array($index['Key_name'], $this->config()->deleted_indexes)) {
					$this->execute(sprintf('DROP INDEX "%s" ON "%s"', $index['Key_name'], $tableName));
				}
			}

			// remove obsolete prefixed columns
			foreach(DB::query(sprintf('SHOW COLUMNS FROM "%s" WHERE "Field" LIKE \'_obsolete%%\'', $tableName)) as $column) {
				$this->execute(sprintf('ALTER TABLE "%s" DROP COLUMN "%s"', $tableName, $column['Field']));
			}

			// remove columns marked for deletion
			foreach($this->config()->deleted_columns as $key => $columnNameArr) {
				// if the definitions were for a specific table that we're currently not processing
				if($key !== '*' && $key !== $tableName) {
					continue;
				}

				foreach($columnNameArr as $columnName) {
					foreach(DB::query(sprintf('SHOW COLUMNS FROM "%s" WHERE "Field" = \'%s\'', $tableName, $columnName)) as $column) {
						$this->execute(sprintf('ALTER TABLE "%s" DROP COLUMN "%s"', $tableName, $column['Field']));
					}
				}
			}
		}

		$this->log('Done');
	}

}