/**
* Create or update table according to schema
*
* @param \Zend\Log\Logger $logger Logger instance
* @param array $schema Parsed table schema
* @param \Nada\Database\AbstractDatabase $database Database object
* @param string[] $obsoleteColumns List of obsolete columns to prune or warn about
* @param bool $prune Drop obsolete tables/columns
*/
public static function setSchema($logger, $schema, $database, array $obsoleteColumns = array(), $prune = false)
{
$tableName = $schema['name'];
if (in_array($tableName, $database->getTableNames())) {
// Table exists
// Update table and column comments
$table = $database->getTable($tableName);
if ($schema['comment'] != $table->getComment()) {
$table->setComment($schema['comment']);
}
$columns = $table->getColumns();
foreach ($schema['columns'] as $column) {
if (isset($columns[$column['name']])) {
// Column exists. Set comment.
$columnObj = $table->getColumn($column['name']);
$columnObj->setComment($column['comment']);
// Change datatype if different.
if ($columnObj->getDatatype() != $column['type'] or $columnObj->getLength() != $column['length']) {
$logger->info("Setting column {$tableName}.{$column['name']} type to {$column['type']}({$column['length']})...");
$columnObj->setDatatype($column['type'], $column['length']);
$logger->info('done.');
}
// Change constraints if different.
if ($columnObj->getNotNull() != $column['notnull']) {
$logger->info(($column['notnull'] ? 'Setting' : 'Removing') . " NOT NULL constraint on column {$tableName}.{$column['name']}...");
$columnObj->setNotNull($column['notnull']);
$logger->info('done.');
}
// Change default if different.
// Since SQL types cannot be completely mapped to PHP
// types, a loose comparision is required, but changes
// to/from NULL must be taken into account.
if ($columnObj->getDefault() === null and $column['default'] !== null or $columnObj->getDefault() !== null and $column['default'] === null or $columnObj->getDefault() != $column['default']) {
$logger->info(sprintf("Setting default value of column {$tableName}.{$column['name']} to %s...", $column['default'] === null ? 'NULL' : "'{$column['default']}'"));
$columnObj->setDefault($column['default']);
$logger->info('done.');
}
} else {
$logger->info("Creating column {$tableName}.{$column['name']}...");
$table->addColumnObject($database->createColumnFromArray($column));
$logger->info('done.');
}
}
// Check for altered PK definition
$primaryKey = $table->getPrimaryKey();
if ($primaryKey) {
foreach ($primaryKey as &$column) {
$column = $column->getName();
}
unset($column);
} else {
$primaryKey = array();
}
if ($schema['primary_key'] != $primaryKey) {
$logger->info(sprintf('Changing PK of %s from (%s) to (%s)...', $tableName, implode(', ', $primaryKey), implode(', ', $schema['primary_key'])));
$table->setPrimaryKey($schema['primary_key']);
$logger->info('done.');
}
} else {
// Table does not exist, create it
$logger->info("Creating table '{$tableName}'...");
$table = $database->createTable($tableName, $schema['columns'], $schema['primary_key']);
$table->setComment($schema['comment']);
if ($database->isMySql()) {
$table->setEngine($schema['mysql']['engine']);
$table->setCharset('utf8');
}
$logger->info('done.');
}
// Create missing indexes. Ignore name for comparision with existing indexes.
if (isset($schema['indexes'])) {
foreach ($schema['indexes'] as $index) {
if (!$table->hasIndex($index['columns'], $index['unique'])) {
$logger->info("Creating index '{$index['name']}'...");
$table->createIndex($index['name'], $index['columns'], $index['unique']);
$logger->info('done.');
}
}
}
// Detect obsolete columns that are present in the database but not in
// the current schema.
foreach ($obsoleteColumns as $column) {
if ($prune) {
$logger->notice("Dropping column {$tableName}.{$column}...");
$table->dropColumn($column);
$logger->notice('done.');
} else {
$logger->warn("Obsolete column {$tableName}.{$column} detected.");
}
}
}
