/**
* @param Table $localTable
* @param ForeignKeyConstraint $fkConstraint
*/
public function acceptForeignKey(Table $localTable, ForeignKeyConstraint $fkConstraint)
{
// Append the foreign key constraints SQL
if ($this->_platform->supportsForeignKeyConstraints()) {
$this->_createFkConstraintQueries = array_merge($this->_createFkConstraintQueries, (array) $this->_platform->getCreateForeignKeySQL($fkConstraint, $localTable->getQuotedName($this->_platform)));
}
}
public function getCreateTableSQL(Table $table, array $columns, array $options = array())
{
$tableName = $table->getQuotedName($this->platform);
$sql = $this->_getCreateTableSQL($tableName, $columns, $options);
if ($this->platform->supportsCommentOnStatement()) {
foreach ($table->getColumns() as $column) {
$comment = $this->getColumnComment($column);
if (null !== $comment && '' !== $comment) {
$sql[] = $this->platform->getCommentOnColumnSQL($tableName, $column->getQuotedName($this->platform), $comment);
}
}
}
return $sql;
}
/**
* @param \TYPO3\CMS\Core\Database\Schema\Parser\AST\CreateIndexDefinitionItem $item
* @return \Doctrine\DBAL\Schema\Index
* @throws \Doctrine\DBAL\Schema\SchemaException
* @throws \InvalidArgumentException
*/
protected function addIndex(CreateIndexDefinitionItem $item) : Index
{
$indexName = $item->indexName->getQuotedName();
$columnNames = array_map(function (IndexColumnName $columnName) {
if ($columnName->length) {
return $columnName->columnName->getQuotedName() . '(' . $columnName->length . ')';
}
return $columnName->columnName->getQuotedName();
}, $item->columnNames);
if ($item->isPrimary) {
$this->table->setPrimaryKey($columnNames);
$index = $this->table->getPrimaryKey();
} else {
$index = GeneralUtility::makeInstance(Index::class, $indexName, $columnNames, $item->isUnique, $item->isPrimary);
if ($item->isFulltext) {
$index->addFlag('fulltext');
} elseif ($item->isSpatial) {
$index->addFlag('spatial');
}
$this->table = GeneralUtility::makeInstance(Table::class, $this->table->getQuotedName($this->platform), $this->table->getColumns(), array_merge($this->table->getIndexes(), [strtolower($indexName) => $index]), $this->table->getForeignKeys(), 0, $this->table->getOptions());
}
return $index;
}
/**
* @param \Doctrine\DBAL\Schema\TableDiff $diff
*
* @return array|bool
*/
private function getSimpleAlterTableSQL(TableDiff $diff)
{
// Suppress changes on integer type autoincrement columns.
foreach ($diff->changedColumns as $oldColumnName => $columnDiff) {
if (!$columnDiff->fromColumn instanceof Column || !$columnDiff->column instanceof Column || !$columnDiff->column->getAutoincrement() || !(string) $columnDiff->column->getType() === 'Integer') {
continue;
}
if (!$columnDiff->hasChanged('type') && $columnDiff->hasChanged('unsigned')) {
unset($diff->changedColumns[$oldColumnName]);
continue;
}
$fromColumnType = (string) $columnDiff->fromColumn->getType();
if ($fromColumnType === 'SmallInt' || $fromColumnType === 'BigInt') {
unset($diff->changedColumns[$oldColumnName]);
}
}
if (!empty($diff->renamedColumns) || !empty($diff->addedForeignKeys) || !empty($diff->addedIndexes) || !empty($diff->changedColumns) || !empty($diff->changedForeignKeys) || !empty($diff->changedIndexes) || !empty($diff->removedColumns) || !empty($diff->removedForeignKeys) || !empty($diff->removedIndexes) || !empty($diff->renamedIndexes)) {
return false;
}
$table = new Table($diff->name);
$sql = array();
$tableSql = array();
$columnSql = array();
foreach ($diff->addedColumns as $column) {
if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) {
continue;
}
$field = array_merge(array('unique' => null, 'autoincrement' => null, 'default' => null), $column->toArray());
$type = (string) $field['type'];
switch (true) {
case isset($field['columnDefinition']) || $field['autoincrement'] || $field['unique']:
case $type == 'DateTime' && $field['default'] == $this->getCurrentTimestampSQL():
case $type == 'Date' && $field['default'] == $this->getCurrentDateSQL():
case $type == 'Time' && $field['default'] == $this->getCurrentTimeSQL():
return false;
}
$field['name'] = $column->getQuotedName($this);
if (strtolower($field['type']) == 'string' && $field['length'] === null) {
$field['length'] = 255;
}
$sql[] = 'ALTER TABLE ' . $table->getQuotedName($this) . ' ADD COLUMN ' . $this->getColumnDeclarationSQL($field['name'], $field);
}
if (!$this->onSchemaAlterTable($diff, $tableSql)) {
if ($diff->newName !== false) {
$newTable = new Identifier($diff->newName);
$sql[] = 'ALTER TABLE ' . $table->getQuotedName($this) . ' RENAME TO ' . $newTable->getQuotedName($this);
}
}
return array_merge($sql, $tableSql, $columnSql);
}
/**
* @group DBAL-177
*/
public function testQuoteSchemaPrefixed()
{
$table = new Table("`test`.`test`");
$this->assertEquals("test.test", $table->getName());
$this->assertEquals("`test`.`test`", $table->getQuotedName(new \Doctrine\DBAL\Platforms\MySqlPlatform()));
}
/**
* Drops and creates a new table.
*
* @param \Doctrine\DBAL\Schema\Table $table
*
* @return void
*/
public function dropAndCreateTable(Table $table)
{
$this->tryMethod('dropTable', $table->getQuotedName($this->_platform));
$this->createTable($table);
}
/**
* @param \Doctrine\DBAL\Schema\TableDiff $diff
*
* @return array|bool
*/
private function getSimpleAlterTableSQL(TableDiff $diff)
{
if (!empty($diff->renamedColumns) || !empty($diff->addedForeignKeys) || !empty($diff->addedIndexes) || !empty($diff->changedColumns) || !empty($diff->changedForeignKeys) || !empty($diff->changedIndexes) || !empty($diff->removedColumns) || !empty($diff->removedForeignKeys) || !empty($diff->removedIndexes)) {
return false;
}
$table = new Table($diff->name);
$sql = array();
$tableSql = array();
$columnSql = array();
foreach ($diff->addedColumns as $column) {
if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) {
continue;
}
$field = array_merge(array('unique' => null, 'autoincrement' => null, 'default' => null), $column->toArray());
$type = (string) $field['type'];
switch (true) {
case isset($field['columnDefinition']) || $field['autoincrement'] || $field['unique']:
case $type == 'DateTime' && $field['default'] == $this->getCurrentTimestampSQL():
case $type == 'Date' && $field['default'] == $this->getCurrentDateSQL():
case $type == 'Time' && $field['default'] == $this->getCurrentTimeSQL():
return false;
}
$field['name'] = $column->getQuotedName($this);
if (strtolower($field['type']) == 'string' && $field['length'] === null) {
$field['length'] = 255;
}
$sql[] = 'ALTER TABLE ' . $table->getQuotedName($this) . ' ADD COLUMN ' . $this->getColumnDeclarationSQL($field['name'], $field);
}
if (!$this->onSchemaAlterTable($diff, $tableSql)) {
if ($diff->newName !== false) {
$newTable = new Table($diff->newName);
$sql[] = 'ALTER TABLE ' . $table->getQuotedName($this) . ' RENAME TO ' . $newTable->getQuotedName($this);
}
}
return array_merge($sql, $tableSql, $columnSql);
}
/**
* @param AbstractPlatform $platform The platform to use for retrieving this table diff's name.
*
* @return \Doctrine\DBAL\Schema\Identifier
*/
public function getName(AbstractPlatform $platform)
{
return new Identifier($this->fromTable instanceof Table ? $this->fromTable->getQuotedName($platform) : $this->name);
}
/**
* @param TableConfiguration $tableConfig
* @param Table $table
* @param array $harvestedValues
*
* @return QueryBuilder
*/
private function createSelectQueryBuilder(TableConfiguration $tableConfig, Table $table, $harvestedValues = array())
{
$qb = $this->connectionHandler->getConnection()->createQueryBuilder()->select('*')->from($table->getQuotedName($this->connectionHandler->getPlatform()), 't');
$this->addFiltersToSelectQuery($qb, $tableConfig, $harvestedValues);
if ($tableConfig->getLimit() != null) {
$qb->setMaxResults($tableConfig->getLimit());
}
if ($tableConfig->getOrderBy() != null) {
$qb->add('orderBy', $tableConfig->getOrderBy());
}
return $qb;
}
/**
* @param Table $table
*/
public function acceptTable(Table $table)
{
$this->_tables[] = $this->_platform->getDropTableSQL($table->getQuotedName($this->_platform));
}
/**
* Dumps the contents of a single table.
*
* @param Table $table
* @param TableConfiguration $tableConfig
*/
private function dumpTableContent(Table $table, TableConfiguration $tableConfig)
{
// Ensure connection is still open (to prevent for example "MySQL server has gone" errors)
$this->connectionHandler->reconnectIfNecessary();
// The PDO instance is used to quote the dumped values.
$pdo = $this->connectionHandler->getPdo();
$tableName = $table->getName();
$quotedTableName = $table->getQuotedName($this->connectionHandler->getPlatform());
$insertColumns = null;
$harvestColumns = $tableConfig->getColumnsToHarvest();
// The buffer is used to create combined SQL statements.
$maxBufferSize = $this->context->getConfig()->getOutputConfig()->getRowsPerStatement();
$bufferCount = 0;
// number of rows in buffer
$buffer = array();
// array to buffer rows
$rowCount = $this->dataLoader->countRows($tableConfig, $table, $this->harvestedValues);
$result = $this->dataLoader->executeSelectQuery($tableConfig, $table, $this->harvestedValues);
$this->logger->info(sprintf('Dumping table %s (%d rows)', $table->getName(), $rowCount));
$progress = $this->context->getProgressBarHelper()->createProgressBar($rowCount, OutputInterface::VERBOSITY_VERY_VERBOSE);
foreach ($result as $row) {
/*
* The following code (in this loop) will be executed for each dumped row!
* Performance in this place is essential!
*/
foreach ($harvestColumns as $harvestColumn) {
if (!isset($row[$harvestColumn])) {
throw new InvalidArgumentException(sprintf('Trying to collect value of column %s in table %s which does not exist.', $harvestColumn, $tableName));
}
$this->harvestedValues[$tableName][$harvestColumn][] = $row[$harvestColumn];
}
// Quote all values in the row.
$context = $row;
foreach ($row as $key => $value) {
$value = $this->dataConverter->convert($tableName . '.' . $key, $value, $context);
if (is_null($value)) {
$value = 'NULL';
} else {
$value = $pdo->quote($value);
}
$row[$key] = $value;
}
if ($insertColumns === null) {
// As custom queries can be defined to select the data, the column order in the insert statement
// depends on the actual result set.
$insertColumns = $this->extractInsertColumns(array_keys($row));
}
$buffer[] = '(' . implode(', ', $row) . ')';
$bufferCount++;
if ($bufferCount >= $maxBufferSize) {
$query = 'INSERT INTO ' . $quotedTableName . ' (' . $insertColumns . ')' . ' VALUES ' . implode(', ', $buffer) . ';';
$this->dumpOutput->writeln($query);
$progress->advance($bufferCount);
$buffer = array();
$bufferCount = 0;
}
}
if ($bufferCount > 0) {
// Dump the final buffer.
$query = 'INSERT INTO ' . $quotedTableName . ' (' . $insertColumns . ')' . ' VALUES ' . implode(', ', $buffer) . ';';
$this->dumpOutput->writeln($query);
}
$progress->finish();
}
/**
* {@inheritDoc}
* Gets the SQL statement(s) to create a table with the specified name, columns and constraints
* on this platform.
*
* @param Table $table The name of the table.
* @param integer $createFlags
*
* @return array The sequence of SQL statements.
*/
public function getCreateTableSQL(Table $table, $createFlags = self::CREATE_INDEXES)
{
if (!is_int($createFlags)) {
$msg = "Second argument of CratePlatform::getCreateTableSQL() has to be integer.";
throw new \InvalidArgumentException($msg);
}
if (count($table->getColumns()) === 0) {
throw DBALException::noColumnsSpecifiedForTable($table->getName());
}
$tableName = $table->getQuotedName($this);
$options = $table->getOptions();
$options['uniqueConstraints'] = array();
$options['indexes'] = array();
$options['primary'] = array();
if (($createFlags & self::CREATE_INDEXES) > 0) {
foreach ($table->getIndexes() as $index) {
/* @var $index Index */
if ($index->isPrimary()) {
$platform = $this;
$options['primary'] = array_map(function ($columnName) use($table, $platform) {
return $table->getColumn($columnName)->getQuotedName($platform);
}, $index->getColumns());
$options['primary_index'] = $index;
} else {
$options['indexes'][$index->getName()] = $index;
}
}
}
$columnSql = array();
$columns = array();
foreach ($table->getColumns() as $column) {
if (null !== $this->_eventManager && $this->_eventManager->hasListeners(Events::onSchemaCreateTableColumn)) {
$eventArgs = new SchemaCreateTableColumnEventArgs($column, $table, $this);
$this->_eventManager->dispatchEvent(Events::onSchemaCreateTableColumn, $eventArgs);
$columnSql = array_merge($columnSql, $eventArgs->getSql());
if ($eventArgs->isDefaultPrevented()) {
continue;
}
}
$columns[$column->getQuotedName($this)] = $this->prepareColumnData($column, $options['primary']);
}
if (null !== $this->_eventManager && $this->_eventManager->hasListeners(Events::onSchemaCreateTable)) {
$eventArgs = new SchemaCreateTableEventArgs($table, $columns, $options, $this);
$this->_eventManager->dispatchEvent(Events::onSchemaCreateTable, $eventArgs);
if ($eventArgs->isDefaultPrevented()) {
return array_merge($eventArgs->getSql(), $columnSql);
}
}
$sql = $this->_getCreateTableSQL($tableName, $columns, $options);
if ($this->supportsCommentOnStatement()) {
foreach ($table->getColumns() as $column) {
if ($this->getColumnComment($column)) {
$sql[] = $this->getCommentOnColumnSQL($tableName, $column->getName(), $this->getColumnComment($column));
}
}
}
return array_merge($sql, $columnSql);
}
/**
* @group DBAL-64
*/
public function testQuotedTableName()
{
$table = new Table("`bar`");
$mysqlPlatform = new \Doctrine\DBAL\Platforms\MySqlPlatform();
$sqlitePlatform = new \Doctrine\DBAL\Platforms\SqlitePlatform();
$this->assertEquals('bar', $table->getName());
$this->assertEquals('`bar`', $table->getQuotedName($mysqlPlatform));
$this->assertEquals('"bar"', $table->getQuotedName($sqlitePlatform));
}
/**
* Gathers the table alteration SQL for a given column diff.
*
* @param Table $table The table to gather the SQL for.
* @param ColumnDiff $columnDiff The column diff to evaluate.
* @param array $sql The sequence of table alteration statements to fill.
* @param array $queryParts The sequence of column alteration clauses to fill.
*/
private function gatherAlterColumnSQL(Table $table, ColumnDiff $columnDiff, array &$sql, array &$queryParts)
{
$alterColumnClauses = $this->getAlterColumnClausesSQL($columnDiff);
if (empty($alterColumnClauses)) {
return;
}
// If we have a single column alteration, we can append the clause to the main query.
if (count($alterColumnClauses) === 1) {
$queryParts[] = current($alterColumnClauses);
return;
}
// We have multiple alterations for the same column,
// so we need to trigger a complete ALTER TABLE statement
// for each ALTER COLUMN clause.
foreach ($alterColumnClauses as $alterColumnClause) {
$sql[] = 'ALTER TABLE ' . $table->getQuotedName($this) . ' ' . $alterColumnClause;
}
}
/**
* Returns the quoted representation of the referenced table name
* the foreign key constraint is associated with.
*
* But only if it was defined with one or the referenced table name
* is a keyword reserved by the platform.
* Otherwise the plain unquoted value as inserted is returned.
*
* @param \Doctrine\DBAL\Platforms\AbstractPlatform $platform The platform to use for quotation.
*
* @return string
*/
public function getQuotedForeignTableName(AbstractPlatform $platform)
{
return $this->_foreignTableName->getQuotedName($platform);
}
/**
* Gets the SQL statement(s) to create a table with the specified name, columns and constraints
* on this platform.
*
* @param string $table The name of the table.
* @param int $createFlags
* @return array The sequence of SQL statements.
*/
public function getCreateTableSQL(Table $table, $createFlags = self::CREATE_INDEXES)
{
if (!is_int($createFlags)) {
throw new \InvalidArgumentException("Second argument of AbstractPlatform::getCreateTableSQL() has to be integer.");
}
if (count($table->getColumns()) == 0) {
throw DBALException::noColumnsSpecifiedForTable($table->getName());
}
$tableName = $table->getQuotedName($this);
$options = $table->getOptions();
$options['uniqueConstraints'] = array();
$options['indexes'] = array();
$options['primary'] = array();
if (($createFlags & self::CREATE_INDEXES) > 0) {
foreach ($table->getIndexes() as $index) {
/* @var $index Index */
if ($index->isPrimary()) {
$options['primary'] = $index->getColumns();
} else {
$options['indexes'][$index->getName()] = $index;
}
}
}
$columnSql = array();
$columns = array();
foreach ($table->getColumns() as $column) {
/* @var \Doctrine\DBAL\Schema\Column $column */
if (null !== $this->_eventManager && $this->_eventManager->hasListeners(Events::onSchemaCreateTableColumn)) {
$eventArgs = new SchemaCreateTableColumnEventArgs($column, $table, $this);
$this->_eventManager->dispatchEvent(Events::onSchemaCreateTableColumn, $eventArgs);
$columnSql = array_merge($columnSql, $eventArgs->getSql());
if ($eventArgs->isDefaultPrevented()) {
continue;
}
}
$columnData = array();
$columnData['name'] = $column->getQuotedName($this);
$columnData['type'] = $column->getType();
$columnData['length'] = $column->getLength();
$columnData['notnull'] = $column->getNotNull();
$columnData['fixed'] = $column->getFixed();
$columnData['unique'] = false;
// TODO: what do we do about this?
$columnData['version'] = $column->hasPlatformOption("version") ? $column->getPlatformOption('version') : false;
if (strtolower($columnData['type']) == "string" && $columnData['length'] === null) {
$columnData['length'] = 255;
}
$columnData['unsigned'] = $column->getUnsigned();
$columnData['precision'] = $column->getPrecision();
$columnData['scale'] = $column->getScale();
$columnData['default'] = $column->getDefault();
$columnData['columnDefinition'] = $column->getColumnDefinition();
$columnData['autoincrement'] = $column->getAutoincrement();
$columnData['comment'] = $this->getColumnComment($column);
if (in_array($column->getName(), $options['primary'])) {
$columnData['primary'] = true;
}
$columns[$columnData['name']] = $columnData;
}
if (($createFlags & self::CREATE_FOREIGNKEYS) > 0) {
$options['foreignKeys'] = array();
foreach ($table->getForeignKeys() as $fkConstraint) {
$options['foreignKeys'][] = $fkConstraint;
}
}
if (null !== $this->_eventManager && $this->_eventManager->hasListeners(Events::onSchemaCreateTable)) {
$eventArgs = new SchemaCreateTableEventArgs($table, $columns, $options, $this);
$this->_eventManager->dispatchEvent(Events::onSchemaCreateTable, $eventArgs);
if ($eventArgs->isDefaultPrevented()) {
return array_merge($eventArgs->getSql(), $columnSql);
}
}
$sql = $this->_getCreateTableSQL($tableName, $columns, $options);
if ($this->supportsCommentOnStatement()) {
foreach ($table->getColumns() as $column) {
if ($this->getColumnComment($column)) {
$sql[] = $this->getCommentOnColumnSQL($tableName, $column->getName(), $this->getColumnComment($column));
}
}
}
return array_merge($sql, $columnSql);
}
public function testQuotedTableNames()
{
$table = new Table('"test"');
$table->addColumn('"id"', 'integer', array('autoincrement' => true));
// assert tabel
$this->assertTrue($table->isQuoted());
$this->assertEquals('test', $table->getName());
$this->assertEquals('"test"', $table->getQuotedName($this->_platform));
$sql = $this->_platform->getCreateTableSQL($table);
$this->assertEquals('CREATE TABLE "test" ("id" NUMBER(10) NOT NULL)', $sql[0]);
$this->assertEquals('CREATE SEQUENCE "test_SEQ" START WITH 1 MINVALUE 1 INCREMENT BY 1', $sql[2]);
$createTriggerStatement = <<<EOD
CREATE TRIGGER "test_AI_PK"
BEFORE INSERT
ON "test"
FOR EACH ROW
DECLARE
last_Sequence NUMBER;
last_InsertID NUMBER;
BEGIN
SELECT "test_SEQ".NEXTVAL INTO :NEW."id" FROM DUAL;
IF (:NEW."id" IS NULL OR :NEW."id" = 0) THEN
SELECT "test_SEQ".NEXTVAL INTO :NEW."id" FROM DUAL;
ELSE
SELECT NVL(Last_Number, 0) INTO last_Sequence
FROM User_Sequences
WHERE Sequence_Name = 'test_SEQ';
SELECT :NEW."id" INTO last_InsertID FROM DUAL;
WHILE (last_InsertID > last_Sequence) LOOP
SELECT "test_SEQ".NEXTVAL INTO last_Sequence FROM DUAL;
END LOOP;
END IF;
END;
EOD;
$this->assertEquals($createTriggerStatement, $sql[3]);
}
