compare two schemas.
| public compare ( SchemaInterface $before, SchemaInterface $after ) | ||
| $before | SchemaInterface | schema before |
| $after | SchemaInterface | new schema |
public function execute()
{
$formatter = new \CLIFramework\Formatter();
$options = $this->options;
$logger = $this->logger;
$connectionManager = \LazyRecord\ConnectionManager::getInstance();
$dsId = $this->getCurrentDataSourceId();
$conn = $connectionManager->getConnection($dsId);
$driver = $connectionManager->getQueryDriver($dsId);
$this->logger->info('Performing Comparison...');
$parser = TableParser::create($driver, $conn);
$existingTables = $parser->getTables();
$tableSchemas = $parser->getDeclareSchemaMap();
$found = false;
$comparator = new Comparator();
foreach ($tableSchemas as $table => $currentSchema) {
$this->logger->debug("Checking table {$table}");
$ref = new ReflectionObject($currentSchema);
$filepath = $ref->getFilename();
$filepath = substr($filepath, strlen(getcwd()) + 1);
if (in_array($table, $existingTables)) {
$before = $parser->reverseTableSchema($table);
$diffs = $comparator->compare($before, $currentSchema);
if (count($diffs)) {
$found = true;
$printer = new ComparatorConsolePrinter($diffs);
$printer->beforeName = $table . ":data source [{$dsId}]";
$printer->afterName = $table . ':' . $filepath;
$printer->output();
}
} else {
$msg = sprintf("+ table %-20s %s", "'" . $table . "'", $filepath);
echo $formatter->format($msg, 'green'), "\n";
$a = isset($tableSchemas[$table]) ? $tableSchemas[$table] : null;
$diff = $comparator->compare(new DeclareSchema(), $currentSchema);
foreach ($diff as $diffItem) {
echo " ", $diffItem->toColumnAttrsString(), "\n";
}
$found = true;
}
}
if (!$found) {
$this->logger->info("No diff found");
}
}
public function testBasicComparison()
{
$before = new DeclareSchema();
$before->column('same');
$before->column('changed')->varchar(20);
$before->column('removed')->boolean();
$after = new DeclareSchema();
$after->column('same');
$after->column('changed')->varchar(30);
$after->column('added')->varchar(10);
$comparator = new Comparator();
$diffs = $comparator->compare($before, $after);
foreach ($diffs as $diff) {
$this->assertInstanceOf('LazyRecord\\Schema\\Comparator\\ColumnDiff', $diff);
}
$firstDiff = $diffs[0];
$this->assertEquals('changed', $firstDiff->name);
$this->assertEquals('M', $firstDiff->flag);
$secondDiff = $diffs[1];
$this->assertEquals('removed', $secondDiff->name);
$this->assertEquals('D', $secondDiff->flag);
$thirdDiff = $diffs[2];
$this->assertEquals('added', $thirdDiff->name);
$this->assertEquals('A', $thirdDiff->flag);
/**
* this can't work with posix (color output)
*/
# $this->expectOutputRegex('/^= same/sm');
# $this->expectOutputRegex('/^= changed/sm');
# $this->expectOutputRegex('/^- removed/sm');
/*
ob_start();
$printer->output();
$content = ob_get_contents();
ob_clean();
like('#removed#',$content);
like('#added#',$content);
*/
return $diffs;
}
public function upgrade()
{
$parser = TableParser::create($this->driver, $this->connection);
$tableSchemas = $parser->getDeclareSchemaMap();
$comparator = new Comparator();
$existingTables = $parser->getTables();
// schema from runtime
foreach ($tableSchemas as $table => $schema) {
$this->logger->debug("Checking table {$table} for schema " . get_class($schema));
if (!in_array($table, $existingTables)) {
$this->logger->debug("Table {$table} does not exist, try importing...");
// generate create table statement.
// use sqlbuilder to build schema sql
$this->importSchema($schema);
continue;
}
$this->logger->debug("Found existing table {$table}");
$before = $parser->reverseTableSchema($table);
$this->logger->info("Comparing table `{$table}` with schema");
$diffs = $comparator->compare($before, $schema);
if (count($diffs) == 0) {
$this->logger->info("Nothing changed.");
continue;
}
$this->logger->debug("Found " . count($diffs) . ' differences');
$alterTable = $this->alterTable($table);
foreach ($diffs as $diff) {
if ($this->options->{'separate-alter'}) {
$alterTable = $this->alterTable($table);
}
$column = $diff->getAfterColumn();
switch ($diff->flag) {
case 'A':
$alterTable->addColumn($column);
break;
case 'D':
if ($this->options->{'no-drop-column'}) {
continue;
}
$alterTable->dropColumnByName($diff->name);
break;
case 'M':
$afterColumn = $diff->getAfterColumn();
$beforeColumn = $diff->getBeforeColumn();
if (!$afterColumn || !$beforeColumn) {
throw new LogicException("afterColumn or beforeColumn is undefined.");
}
// check foreign key change
if ($beforeColumn->primary != $afterColumn->primary) {
$alterTable->add()->primaryKey(['id']);
}
$alterTable->modifyColumn($afterColumn);
break;
default:
throw new LogicException("Unsupported flat: " . $diff->flag);
break;
}
}
$this->executeQuery($alterTable);
}
$this->logger->info('Done!');
}
public function generateWithDiff($taskName, $dataSourceId, array $schemas, $time = null)
{
$connectionManager = \LazyRecord\ConnectionManager::getInstance();
$connection = $connectionManager->getConnection($dataSourceId);
$driver = $connectionManager->getQueryDriver($dataSourceId);
$parser = TableParser::create($connection, $driver);
$tableSchemas = $schemas;
$existingTables = $parser->getTables();
$this->logger->info('Found ' . count($schemas) . ' schemas to compare.');
$template = $this->createClassTemplate($taskName, $time);
$upgradeMethod = $template->addMethod('public', 'upgrade', array(), '');
$downgradeMethod = $template->addMethod('public', 'downgrade', array(), '');
$comparator = new Comparator($driver);
// schema from runtime
foreach ($tableSchemas as $key => $a) {
$table = is_numeric($key) ? $a->getTable() : $key;
if (!in_array($table, $existingTables)) {
$this->logger->info(sprintf("Found schema '%s' to be imported to '%s'", $a, $table), 1);
// generate create table statement.
// use sqlbuilder to build schema sql
$upcall = new MethodCallExpr('$this', 'importSchema', [new Raw('new ' . get_class($a))]);
$upgradeMethod->getBlock()->appendLine(new Statement($upcall));
$downcall = new MethodCallExpr('$this', 'dropTable', [$table]);
$downgradeMethod->getBlock()->appendLine(new Statement($downcall));
continue;
}
// revsersed schema
$b = $parser->reverseTableSchema($table, $a);
$diffs = $comparator->compare($b, $a);
if (empty($diffs)) {
continue;
}
// generate alter table statement.
foreach ($diffs as $diff) {
switch ($diff->flag) {
case 'A':
$alterTable = new AlterTableQuery($table);
$alterTable->addColumn($diff->getAfterColumn());
$this->appendQueryStatement($upgradeMethod, $driver, $alterTable, new ArgumentArray());
$alterTable = new AlterTableQuery($table);
$alterTable->dropColumn($diff->getAfterColumn());
$this->appendQueryStatement($downgradeMethod, $driver, $alterTable, new ArgumentArray());
break;
case 'M':
$alterTable = new AlterTableQuery($table);
$after = $diff->getAfterColumn();
$before = $diff->getBeforeColumn();
if (!$after || !$before) {
throw new LogicException('afterColumn or beforeColumn is undefined.');
}
// Check primary key
if ($before->primary != $after->primary) {
// primary key requires another sub-statement "ADD PRIMARY KEY .."
$alterTable->add()->primaryKey([$after->name]);
}
$alterTable->modifyColumn($after);
$this->appendQueryStatement($upgradeMethod, $driver, $alterTable, new ArgumentArray());
$alterTable = new AlterTableQuery($table);
$alterTable->modifyColumn($before);
$this->appendQueryStatement($downgradeMethod, $driver, $alterTable, new ArgumentArray());
break;
case 'D':
$alterTable = new AlterTableQuery($table);
$alterTable->dropColumnByName($diff->name);
$this->appendQueryStatement($upgradeMethod, $driver, $alterTable, new ArgumentArray());
$alterTable = new AlterTableQuery($table);
$alterTable->addColumn($diff->getBeforeColumn());
$this->appendQueryStatement($downgradeMethod, $driver, $alterTable, new ArgumentArray());
break;
default:
$this->logger->warn('** unsupported flag.');
continue;
}
}
}
$filename = $this->generateFilename($taskName, $time);
$path = $this->migrationDir . DIRECTORY_SEPARATOR . $filename;
if (false === file_put_contents($path, $template->render())) {
throw new RuntimeException("Can't write migration script to {$path}.");
}
return array($template->class->name, $path);
}
/**
* @param DeclareSchema[string tableName]
*/
public function upgrade(array $tableSchemas)
{
$parser = TableParser::create($this->connection, $this->driver);
$existingTables = $parser->getTables();
$comparator = new Comparator($this->driver);
// Schema from runtime
foreach ($tableSchemas as $key => $a) {
$table = is_numeric($key) ? $a->getTable() : $key;
$this->logger->debug("Checking table {$table} for schema " . get_class($a));
if (!in_array($table, $existingTables)) {
$this->logger->debug("Table {$table} does not exist, try importing...");
// generate create table statement.
// use sqlbuilder to build schema sql
$this->importSchema($a);
continue;
}
$this->logger->debug("Found existing table {$table}");
$b = $parser->reverseTableSchema($table, $a);
$this->logger->debug("Comparing table `{$table}` with schema");
$diffs = $comparator->compare($b, $a);
do {
if (count($diffs) == 0) {
$this->logger->debug("Nothing changed in `{$table}`.");
break;
}
$this->logger->debug('Found ' . count($diffs) . ' differences');
$alterTable = $this->alterTable($table);
foreach ($diffs as $diff) {
if ($this->options->{'separate-alter'}) {
$alterTable = $this->alterTable($table);
}
$column = $diff->getAfterColumn();
switch ($diff->flag) {
case 'A':
$alterTable->addColumn($column);
break;
case 'D':
if ($this->options->{'no-drop-column'}) {
continue;
}
$alterTable->dropColumnByName($diff->name);
break;
case 'M':
$afterColumn = $diff->getAfterColumn();
$beforeColumn = $diff->getBeforeColumn();
if (!$afterColumn || !$beforeColumn) {
throw new LogicException('afterColumn or beforeColumn is undefined.');
}
// Check primary key
if ($beforeColumn->primary != $afterColumn->primary) {
$alterTable->add()->primaryKey(['id']);
}
$alterTable->modifyColumn($afterColumn);
break;
default:
throw new LogicException('Unsupported flat: ' . $diff->flag);
break;
}
}
$this->executeQuery($alterTable);
} while (0);
// Compare references with relationships
if ($parser instanceof ReferenceParser) {
$references = $parser->queryReferences($table);
$relationships = $a->getRelations();
$relationshipColumns = [];
foreach ($relationships as $accessor => $rel) {
if ($rel['type'] !== Relationship::BELONGS_TO) {
continue;
}
if ($rel['foreign_schema'] == $rel['self_schema']) {
continue;
}
if (isset($rel['self_column']) && $rel['self_column'] == 'id') {
continue;
}
if (!$rel->usingIndex) {
continue;
}
$class = $rel['foreign_schema'];
$foreignSchema = new $class();
$foreignColumn = $foreignSchema->getColumn($rel['foreign_column']);
$col = $rel['self_column'];
$relationshipColumns[$col] = $rel;
if (isset($references[$col]) && preg_match('/_ibfk_/i', $references[$col]->name)) {
$this->logger->debug("Column {$col} foreign key {$references[$col]->name} exists");
continue;
}
if ($constraint = $this->builder->buildForeignKeyConstraint($rel)) {
$alterTable = $this->alterTable($table);
$add = $alterTable->add();
$add->foreignKey($rel['self_column']);
$fSchema = new $rel['foreign_schema']();
$alterReferences = $add->references($fSchema->getTable(), (array) $rel['foreign_column']);
if ($this->driver instanceof MySQLDriver) {
if ($act = $rel->onUpdate) {
$alterReferences->onUpdate($act);
}
if ($act = $rel->onDelete) {
$alterReferences->onDelete($act);
}
}
$this->executeQuery($alterTable);
}
}
// Find foreign keys that are dropped (doesn't exist in relationship)
foreach ($references as $col => $ref) {
if (!preg_match('/_ibfk_/i', $ref->name)) {
continue;
}
if (!isset($relationshipColumns[$col])) {
// echo "drop {$ref->name} for ({$ref->table}, {$ref->column}) from table $table\n";
$alterTable = $this->alterTable($table);
$alterTable->dropForeignKey($ref->name);
$this->executeQuery($alterTable);
}
}
}
}
}
public function generateWithDiff($taskName, $dataSourceId, $schemas, $time = null)
{
$connectionManager = \LazyRecord\ConnectionManager::getInstance();
$connection = $connectionManager->getConnection($dataSourceId);
$driver = $connectionManager->getQueryDriver($dataSourceId);
$parser = TableParser::create($driver, $connection);
$tableSchemas = $parser->getTableSchemaMap();
$this->logger->info('Found ' . count($schemas) . ' schemas to compare.');
$template = $this->createClassTemplate($taskName, $time);
$template->useClass('SQLBuilder\\Universal\\Syntax\\Column');
$upgradeMethod = $template->addMethod('public', 'upgrade', array(), '');
$downgradeMethod = $template->addMethod('public', 'downgrade', array(), '');
$comparator = new Comparator();
// schema from runtime
foreach ($schemas as $b) {
$tableName = $b->getTable();
$foundTable = isset($tableSchemas[$tableName]);
if ($foundTable) {
$a = $tableSchemas[$tableName];
// schema object, extracted from database.
$diffs = $comparator->compare($a, $b);
// generate alter table statement.
foreach ($diffs as $diff) {
if ($diff->flag == 'A') {
$this->logger->info(sprintf("'%s': add column %s", $tableName, $diff->name), 1);
$column = $diff->getAfterColumn();
$upcall = new MethodCallExpr('$this', 'addColumn', [$tableName, $column]);
$upgradeMethod[] = new Statement($upcall);
$downcall = new MethodCallExpr('$this', 'dropColumnByName', [$tableName, $diff->name]);
$downgradeMethod[] = new Statement($downcall);
} else {
if ($diff->flag == 'D') {
$upcall = new MethodCallExpr('$this', 'dropColumnByName', [$tableName, $diff->name]);
$upgradeMethod->getBlock()->appendLine(new Statement($upcall));
} else {
if ($diff->flag == 'M') {
if ($afterColumn = $diff->getAfterColumn()) {
$upcall = new MethodCallExpr('$this', 'modifyColumn', [$tableName, $afterColumn]);
$upgradeMethod[] = new Statement($upcall);
} else {
throw new \Exception("afterColumn is undefined.");
}
continue;
} else {
$this->logger->warn("** unsupported flag.");
continue;
}
}
}
}
} else {
$this->logger->info(sprintf("Found schema '%s' to be imported to '%s'", $b, $tableName), 1);
// generate create table statement.
// use sqlbuilder to build schema sql
$upcall = new MethodCallExpr('$this', 'importSchema', [new Raw('new ' . get_class($b))]);
$upgradeMethod->getBlock()->appendLine(new Statement($upcall));
$downcall = new MethodCallExpr('$this', 'dropTable', [$tableName]);
$downgradeMethod->getBlock()->appendLine(new Statement($downcall));
}
}
$filename = $this->generateFilename($taskName, $time);
$path = $this->migrationDir . DIRECTORY_SEPARATOR . $filename;
if (false === file_put_contents($path, $template->render())) {
throw new RuntimeException("Can't write migration script to {$path}.");
}
return array($template->class->name, $path);
}