public function testAddForeignKey() { $q = new AlterTableQuery('products'); $q->add()->foreignKey('created_by')->references('users', array('id')); $q->add()->constraint('fk_updated_by')->foreignKey('updated_by')->references('users', array('id')); $this->assertDriverQuery(new MySQLDriver(), $q); $this->assertDriverQuery(new PgSQLDriver(), $q); $this->assertSqlStrings($q, [[new MySQLDriver(), 'ALTER TABLE `products` ADD FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),' . "\n" . ' ADD CONSTRAINT `fk_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)']]); }
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); }