/**
* Create an index
*
* The presence of an existing index with the same name or column set is not checked.
*
* @param string $name Index name
* @param mixed Column name or array of column names
* @param bool $unique Create unique index, default: false
*/
public function createIndex($name, $columns, $unique = false)
{
if (!is_array($columns)) {
$columns = array($columns);
}
foreach ($columns as &$column) {
$column = $this->_database->prepareIdentifier($column);
}
$this->_database->exec(sprintf('CREATE %s INDEX %s ON %s (%s)', $unique ? 'UNIQUE' : '', $this->_database->prepareIdentifier($name), $this->_database->prepareIdentifier($this->_name), implode(', ', $columns)));
// Reset index cache to force re-read by next getIndexes() invokation
$this->_indexes = array();
}
/**
* 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.");
}
}
}
/**
* DBMS-specific implementation for setting a column's default value
**/
protected function _setDefault()
{
if ($this->_default === null) {
$this->_table->alter(sprintf('ALTER COLUMN %s DROP DEFAULT', $this->_database->prepareIdentifier($this->_name)));
} else {
$this->_table->alter(sprintf('ALTER COLUMN %s SET DEFAULT %s', $this->_database->prepareIdentifier($this->_name), $this->_database->prepareValue($this->_default, $this->_datatype)));
}
}
/** {@inheritdoc} */
protected function _getTablePkDeclaration(array $primaryKey, $autoIncrement)
{
// For autoincrement columns, the PK is already specified with the
// column and must not be set again for the table.
if ($autoIncrement) {
return '';
} else {
return parent::_getTablePkDeclaration($primaryKey, $autoIncrement);
}
}
/** {@inheritdoc} */
public function getNativeDatatype($type, $length = null, $cast = false)
{
if ($cast) {
switch ($type) {
case Column::TYPE_INTEGER:
return 'SIGNED';
case Column::TYPE_VARCHAR:
if ($length === null) {
return 'CHAR';
} elseif (ctype_digit((string) $length)) {
return "CHAR({$length})";
} else {
throw new \InvalidArgumentException('Invalid length: ' . $length);
}
case Column::TYPE_TIMESTAMP:
return 'DATETIME';
case Column::TYPE_BOOL:
throw new \DomainException('Values cannot be cast to BOOL');
case Column::TYPE_CLOB:
return 'CHAR';
case Column::TYPE_BLOB:
return 'BINARY';
case Column::TYPE_DECIMAL:
return str_replace('NUMERIC', 'DECIMAL', parent::getNativeDatatype($type, $length, $cast));
case Column::TYPE_FLOAT:
return 'DECIMAL';
default:
return parent::getNativeDatatype($type, $length, $cast);
}
} else {
switch ($type) {
case Column::TYPE_INTEGER:
if ($length == 8) {
return 'TINYINT';
}
return parent::getNativeDatatype($type, $length, $cast);
case Column::TYPE_TIMESTAMP:
return 'DATETIME';
case Column::TYPE_BOOL:
if (in_array(Column::TYPE_BOOL, $this->emulatedDatatypes)) {
return 'TINYINT';
} else {
throw new \DomainException('BOOL not supported by MySQL and not emulated');
}
case Column::TYPE_CLOB:
return 'LONGTEXT';
case Column::TYPE_BLOB:
return 'LONGBLOB';
default:
return parent::getNativeDatatype($type, $length, $cast);
}
}
}
/**
* Drop a column if it exists
*
* @param \Zend\Log\Logger $logger Logger instance
* @param \Nada\Database\AbstractDatabase $database Database object
* @param string $column column name
* @codeCoverageIgnore
*/
protected function _dropColumnIfExists($logger, $database, $column)
{
$tables = $database->getTables();
if (isset($tables[$this->table])) {
$table = $tables[$this->table];
$columns = $table->getColumns();
if (isset($columns[$column])) {
$logger->notice("Dropping column {$this->table}.{$column}...");
$table->dropColumn($column);
$logger->notice('done.');
}
}
}
/** {@inheritdoc} */
public function createTable($name, array $columns, $primaryKey = null)
{
$table = parent::createTable($name, $columns, $primaryKey);
// CREATE TABLE does not set comments. Add them manually.
foreach ($columns as $column) {
if (is_array($column)) {
$column = $this->createColumnFromArray($column);
}
$comment = $column->getComment();
if ($comment) {
$column->setTable($table);
$column->setComment(null);
// Cached value is invalid at this stage, reset it
$column->setComment($comment);
}
}
// Refresh cached table object
$this->clearCache($name);
return $this->getTable($name);
}
