public function addIndex($tableName, $columnName, array $options = [])
{
if (!isset($options['name'])) {
$options['name'] = '';
}
if (!empty($options['unique'])) {
$index = new Ddl\Constraint\UniqueKey($columnName, $options['name']);
} elseif (!empty($options['primary_key'])) {
$index = new Constraint\PrimaryKey($columnName);
} else {
$index = new Constraint\IndexKey($columnName);
}
$ddl = new Ddl\AlterTable($tableName);
$ddl->addConstraint($index);
$this->queryAdapter($ddl);
}
/**
* @covers Zend\Db\Sql\Ddl\AlterTable::getSqlString
* @todo Implement testGetSqlString().
*/
public function testGetSqlString()
{
$at = new AlterTable('foo');
$at->addColumn(new Column\Varchar('another', 255));
$at->changeColumn('name', new Column\Varchar('new_name', 50));
$at->dropColumn('foo');
$at->addConstraint(new Constraint\ForeignKey('my_fk', 'other_id', 'other_table', 'id', 'CASCADE', 'CASCADE'));
$at->dropConstraint('my_index');
$expected = <<<EOS
ALTER TABLE "foo"
ADD COLUMN "another" VARCHAR(255) NOT NULL ,
CHANGE COLUMN "name" "new_name" VARCHAR(50) NOT NULL ,
DROP COLUMN "foo",
ADD CONSTRAINT "my_fk" FOREIGN KEY ("other_id") REFERENCES "other_table" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
DROP CONSTRAINT "my_index"
EOS;
$this->assertEquals($expected, $at->getSqlString());
}
/**
* Create one table. if it is exsist, check the columns. if the column is exsist, change it's type, else create the column.
* @param unknown $tableName
* @param array $table
*/
private function create_table($tableName, $tableStructureData)
{
$adapter = $this->adapter;
$sql = new Sql($adapter);
if (!in_array($tableName, $this->tables)) {
// Create the $table
$CreateTable = new Ddl\CreateTable($tableName);
foreach ($tableStructureData['column'] as $column) {
$CreateTable->addColumn($column);
}
foreach ($tableStructureData['constraint'] as $constraint) {
$CreateTable->addConstraint($constraint);
}
$adapter->query($sql->getSqlStringForSqlObject($CreateTable), $adapter::QUERY_MODE_EXECUTE);
} else {
// Check the columns
$columns = $this->getColumns($tableName);
$constraints = $this->getConstraints($tableName);
$AlterTable = new Ddl\AlterTable($tableName);
foreach ($tableStructureData['column'] as $createColumn) {
$column_exsist = false;
foreach ($columns as $column) {
if ($createColumn->getName() == $column->getName()) {
$column_exsist = true;
}
}
if ($column_exsist) {
// Alter the table, change the column.
$AlterTable->changeColumn($createColumn->getName(), $createColumn);
} else {
// Alter the table, add the column.
$AlterTable->addColumn($createColumn);
}
}
// Delete exsisted constraints(mysql index) but PRIMARY KEY
$exsisted_constraints = $this->getConstraints($tableName);
foreach ($exsisted_constraints as $exsisted_constraint) {
if ($exsisted_constraint->getType() != 'PRIMARY KEY') {
$adapter->query('ALTER TABLE `' . $tableName . '`
DROP index `' . str_replace('_zf_' . $tableName . '_', '', $exsisted_constraint->getName()) . '`', $adapter::QUERY_MODE_EXECUTE);
}
}
// Add all constraints but PRIMARY KEY
foreach ($tableStructureData['constraint'] as $constraint) {
if ($constraint instanceof Constraint\PrimaryKey) {
// Do nothing
} else {
// Add to DB
$AlterTable->addConstraint($constraint);
}
}
$adapter->query($sql->getSqlStringForSqlObject($AlterTable), $adapter::QUERY_MODE_EXECUTE);
}
}
/**
* Creates table by its name and config
*
* @param $tableName
* @param $tableConfig
* @return Adapter\Driver\StatementInterface|\Zend\Db\ResultSet\ResultSet
* @throws RestException
*/
protected function create($tableName, $tableConfig = null)
{
$tableConfig = is_null($tableConfig) ? $tableConfig = $tableName : $tableConfig;
$tableConfigArray = $this->getTableConfig($tableConfig);
$table = new CreateTable($tableName);
$alterTable = new AlterTable($tableName);
$table->addConstraint(new Constraint\PrimaryKey('id'));
foreach ($tableConfigArray as $fieldName => $fieldData) {
$fieldType = $fieldData[self::FIELD_TYPE];
$fieldParams = $this->getFieldParams($fieldData, $fieldType);
array_unshift($fieldParams, $fieldName);
$fieldClass = '\\Zend\\Db\\Sql\\Ddl\\Column\\' . $fieldType;
$reflectionObject = new \ReflectionClass($fieldClass);
$fieldInstance = $reflectionObject->newInstanceArgs($fieldParams);
// it' like new class($callParamsArray[1], $callParamsArray[2]...)
$table->addColumn($fieldInstance);
if (isset($fieldData[self::UNIQUE_KEY])) {
$uniqueKeyConstraintName = $fieldData[self::UNIQUE_KEY] === true ? 'UniqueKey_' . $tableName . '_' . $fieldName : $fieldData[self::UNIQUE_KEY];
$uniqueKeyInstance = new UniqueKey([$fieldName], $uniqueKeyConstraintName);
$alterTable->addConstraint($uniqueKeyInstance);
}
if (isset($fieldData[self::FOREIGN_KEY])) {
$foreignKeyConstraintName = !isset($fieldData[self::FOREIGN_KEY]['name']) ? 'ForeignKey_' . $tableName . '_' . $fieldName : $fieldData[self::FOREIGN_KEY]['name'];
$onDeleteRule = isset($fieldData[self::FOREIGN_KEY]['onDeleteRule']) ? $fieldData[self::FOREIGN_KEY]['onDeleteRule'] : null;
$onUpdateRule = isset($fieldData[self::FOREIGN_KEY]['onUpdateRule']) ? $fieldData[self::FOREIGN_KEY]['onUpdateRule'] : null;
$foreignKeyInstance = new Constraint\ForeignKey($foreignKeyConstraintName, [$fieldName], $fieldData[self::FOREIGN_KEY]['referenceTable'], $fieldData[self::FOREIGN_KEY]['referenceColumn'], $onDeleteRule, $onUpdateRule, $foreignKeyConstraintName);
$alterTable->addConstraint($foreignKeyInstance);
}
}
// this is simpler version, not MySQL only, but without options[] support
//$mySqlPlatformSql = new Sql\Platform\Mysql\Mysql();
//$sql = new Sql\Sql($this->db, null, $mySqlPlatformSql);
//$sqlString = $sql->buildSqlString($table);
$ctdMysql = new Sql\Platform\Mysql\Ddl\CreateTableDecorator();
$mySqlPlatformDbAdapter = new Adapter\Platform\Mysql();
$mySqlPlatformDbAdapter->setDriver($this->db->getDriver());
$sqlStringCreate = $ctdMysql->setSubject($table)->getSqlString($mySqlPlatformDbAdapter);
$mySqlPlatformSql = new Sql\Platform\Mysql\Mysql();
$sql = new Sql\Sql($this->db, null, $mySqlPlatformSql);
$sqlStringAlter = $sql->buildSqlString($alterTable);
$sqlString = $sqlStringCreate . ';' . PHP_EOL . $sqlStringAlter . ';';
return $this->db->query($sqlString, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
}
/**
* @param string $name
* @return static
*/
public function dropConstraint($name)
{
if (!empty($this->renameColumns)) {
trigger_error('One statement must rename a column, other separate statements must change table definition.', E_USER_DEPRECATED);
return $this;
}
return parent::dropConstraint($name);
}
public function dropColumn($columnName, $tableName = null)
{
if ($tableName == null) {
$tableName = $this->table;
}
if (!TableSchema::hasTableColumn($tableName, $columnName, true)) {
return false;
}
// Drop table column if is a non-alias column
if (!array_key_exists($columnName, array_flip(TableSchema::getAllAliasTableColumns($tableName, true)))) {
$sql = new Sql($this->adapter);
$alterTable = new Ddl\AlterTable($tableName);
$dropColumn = $alterTable->dropColumn($columnName);
$query = $sql->getSqlStringForSqlObject($dropColumn);
$this->adapter->query($query)->execute();
}
// Remove column from directus_columns
$columnsTableGateway = new TableGateway('directus_columns', $this->adapter);
$columnsTableGateway->delete(['table_name' => $tableName, 'column_name' => $columnName]);
// Remove column from directus_ui
$uisTableGateway = new TableGateway('directus_ui', $this->adapter);
$uisTableGateway->delete(['table_name' => $tableName, 'column_name' => $columnName]);
return true;
}
public function dropColumn($columnName, $tableName = null)
{
if ($tableName == null) {
$tableName = $this->table;
}
if (!TableSchema::hasTableColumn($tableName, $columnName, true)) {
return false;
}
if (!$this->acl->hasTablePrivilege($tableName, 'alter')) {
$aclErrorPrefix = $this->acl->getErrorMessagePrefix();
throw new UnauthorizedTableAddException($aclErrorPrefix . 'Table alter access forbidden on table ' . $tableName);
}
// Drop table column if is a non-alias column
if (!array_key_exists($columnName, array_flip(TableSchema::getAllAliasTableColumns($tableName, true)))) {
$sql = new Sql($this->adapter);
$alterTable = new Ddl\AlterTable($tableName);
$dropColumn = $alterTable->dropColumn($columnName);
$query = $sql->getSqlStringForSqlObject($dropColumn);
$this->adapter->query($query)->execute();
}
// Remove column from directus_columns
$columnsTableGateway = new TableGateway('directus_columns', $this->adapter);
$columnsTableGateway->delete(['table_name' => $tableName, 'column_name' => $columnName]);
// Remove column from directus_ui
$uisTableGateway = new TableGateway('directus_ui', $this->adapter);
$uisTableGateway->delete(['table_name' => $tableName, 'column_name' => $columnName]);
return true;
}
