/** * 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); }