/lib/Doctrine/DBAL/Platforms/SQLAnywherePlatform.php
PHP | 1519 lines | 952 code | 211 blank | 356 comment | 82 complexity | b6fed5f3ce413316cf375b2a8022afe1 MD5 | raw file
Possible License(s): Unlicense
- <?php
- namespace Doctrine\DBAL\Platforms;
- use Doctrine\DBAL\DBALException;
- use Doctrine\DBAL\LockMode;
- use Doctrine\DBAL\Schema\Column;
- use Doctrine\DBAL\Schema\ColumnDiff;
- use Doctrine\DBAL\Schema\Constraint;
- use Doctrine\DBAL\Schema\ForeignKeyConstraint;
- use Doctrine\DBAL\Schema\Identifier;
- use Doctrine\DBAL\Schema\Index;
- use Doctrine\DBAL\Schema\Table;
- use Doctrine\DBAL\Schema\TableDiff;
- use Doctrine\DBAL\TransactionIsolationLevel;
- use InvalidArgumentException;
- use function array_merge;
- use function array_unique;
- use function array_values;
- use function assert;
- use function count;
- use function explode;
- use function func_get_args;
- use function get_class;
- use function implode;
- use function is_string;
- use function preg_match;
- use function sprintf;
- use function strlen;
- use function strpos;
- use function strtoupper;
- use function substr;
- /**
- * The SQLAnywherePlatform provides the behavior, features and SQL dialect of the
- * SAP Sybase SQL Anywhere 10 database platform.
- */
- class SQLAnywherePlatform extends AbstractPlatform
- {
- public const FOREIGN_KEY_MATCH_SIMPLE = 1;
- public const FOREIGN_KEY_MATCH_FULL = 2;
- public const FOREIGN_KEY_MATCH_SIMPLE_UNIQUE = 129;
- public const FOREIGN_KEY_MATCH_FULL_UNIQUE = 130;
- /**
- * {@inheritdoc}
- */
- public function appendLockHint($fromClause, $lockMode)
- {
- switch (true) {
- case $lockMode === LockMode::NONE:
- return $fromClause . ' WITH (NOLOCK)';
- case $lockMode === LockMode::PESSIMISTIC_READ:
- return $fromClause . ' WITH (UPDLOCK)';
- case $lockMode === LockMode::PESSIMISTIC_WRITE:
- return $fromClause . ' WITH (XLOCK)';
- default:
- return $fromClause;
- }
- }
- /**
- * {@inheritdoc}
- *
- * SQL Anywhere supports a maximum length of 128 bytes for identifiers.
- */
- public function fixSchemaElementName($schemaElementName)
- {
- $maxIdentifierLength = $this->getMaxIdentifierLength();
- if (strlen($schemaElementName) > $maxIdentifierLength) {
- return substr($schemaElementName, 0, $maxIdentifierLength);
- }
- return $schemaElementName;
- }
- /**
- * {@inheritdoc}
- */
- public function getAdvancedForeignKeyOptionsSQL(ForeignKeyConstraint $foreignKey)
- {
- $query = '';
- if ($foreignKey->hasOption('match')) {
- $query = ' MATCH ' . $this->getForeignKeyMatchClauseSQL($foreignKey->getOption('match'));
- }
- $query .= parent::getAdvancedForeignKeyOptionsSQL($foreignKey);
- if ($foreignKey->hasOption('check_on_commit') && (bool) $foreignKey->getOption('check_on_commit')) {
- $query .= ' CHECK ON COMMIT';
- }
- if ($foreignKey->hasOption('clustered') && (bool) $foreignKey->getOption('clustered')) {
- $query .= ' CLUSTERED';
- }
- if ($foreignKey->hasOption('for_olap_workload') && (bool) $foreignKey->getOption('for_olap_workload')) {
- $query .= ' FOR OLAP WORKLOAD';
- }
- return $query;
- }
- /**
- * {@inheritdoc}
- */
- public function getAlterTableSQL(TableDiff $diff)
- {
- $sql = [];
- $columnSql = [];
- $commentsSQL = [];
- $tableSql = [];
- $alterClauses = [];
- foreach ($diff->addedColumns as $column) {
- if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) {
- continue;
- }
- $alterClauses[] = $this->getAlterTableAddColumnClause($column);
- $comment = $this->getColumnComment($column);
- if ($comment === null || $comment === '') {
- continue;
- }
- $commentsSQL[] = $this->getCommentOnColumnSQL(
- $diff->getName($this)->getQuotedName($this),
- $column->getQuotedName($this),
- $comment
- );
- }
- foreach ($diff->removedColumns as $column) {
- if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) {
- continue;
- }
- $alterClauses[] = $this->getAlterTableRemoveColumnClause($column);
- }
- foreach ($diff->changedColumns as $columnDiff) {
- if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) {
- continue;
- }
- $alterClause = $this->getAlterTableChangeColumnClause($columnDiff);
- if ($alterClause !== null) {
- $alterClauses[] = $alterClause;
- }
- if (! $columnDiff->hasChanged('comment')) {
- continue;
- }
- $column = $columnDiff->column;
- $commentsSQL[] = $this->getCommentOnColumnSQL(
- $diff->getName($this)->getQuotedName($this),
- $column->getQuotedName($this),
- $this->getColumnComment($column)
- );
- }
- foreach ($diff->renamedColumns as $oldColumnName => $column) {
- if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) {
- continue;
- }
- $sql[] = $this->getAlterTableClause($diff->getName($this)) . ' ' .
- $this->getAlterTableRenameColumnClause($oldColumnName, $column);
- }
- if (! $this->onSchemaAlterTable($diff, $tableSql)) {
- if (! empty($alterClauses)) {
- $sql[] = $this->getAlterTableClause($diff->getName($this)) . ' ' . implode(', ', $alterClauses);
- }
- $sql = array_merge($sql, $commentsSQL);
- $newName = $diff->getNewName();
- if ($newName !== false) {
- $sql[] = $this->getAlterTableClause($diff->getName($this)) . ' ' .
- $this->getAlterTableRenameTableClause($newName);
- }
- $sql = array_merge(
- $this->getPreAlterTableIndexForeignKeySQL($diff),
- $sql,
- $this->getPostAlterTableIndexForeignKeySQL($diff)
- );
- }
- return array_merge($sql, $tableSql, $columnSql);
- }
- /**
- * Returns the SQL clause for creating a column in a table alteration.
- *
- * @param Column $column The column to add.
- *
- * @return string
- */
- protected function getAlterTableAddColumnClause(Column $column)
- {
- return 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray());
- }
- /**
- * Returns the SQL clause for altering a table.
- *
- * @param Identifier $tableName The quoted name of the table to alter.
- *
- * @return string
- */
- protected function getAlterTableClause(Identifier $tableName)
- {
- return 'ALTER TABLE ' . $tableName->getQuotedName($this);
- }
- /**
- * Returns the SQL clause for dropping a column in a table alteration.
- *
- * @param Column $column The column to drop.
- *
- * @return string
- */
- protected function getAlterTableRemoveColumnClause(Column $column)
- {
- return 'DROP ' . $column->getQuotedName($this);
- }
- /**
- * Returns the SQL clause for renaming a column in a table alteration.
- *
- * @param string $oldColumnName The quoted name of the column to rename.
- * @param Column $column The column to rename to.
- *
- * @return string
- */
- protected function getAlterTableRenameColumnClause($oldColumnName, Column $column)
- {
- $oldColumnName = new Identifier($oldColumnName);
- return 'RENAME ' . $oldColumnName->getQuotedName($this) . ' TO ' . $column->getQuotedName($this);
- }
- /**
- * Returns the SQL clause for renaming a table in a table alteration.
- *
- * @param Identifier $newTableName The quoted name of the table to rename to.
- *
- * @return string
- */
- protected function getAlterTableRenameTableClause(Identifier $newTableName)
- {
- return 'RENAME ' . $newTableName->getQuotedName($this);
- }
- /**
- * Returns the SQL clause for altering a column in a table alteration.
- *
- * This method returns null in case that only the column comment has changed.
- * Changes in column comments have to be handled differently.
- *
- * @param ColumnDiff $columnDiff The diff of the column to alter.
- *
- * @return string|null
- */
- protected function getAlterTableChangeColumnClause(ColumnDiff $columnDiff)
- {
- $column = $columnDiff->column;
- // Do not return alter clause if only comment has changed.
- if (! ($columnDiff->hasChanged('comment') && count($columnDiff->changedProperties) === 1)) {
- $columnAlterationClause = 'ALTER ' .
- $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray());
- if ($columnDiff->hasChanged('default') && $column->getDefault() === null) {
- $columnAlterationClause .= ', ALTER ' . $column->getQuotedName($this) . ' DROP DEFAULT';
- }
- return $columnAlterationClause;
- }
- return null;
- }
- /**
- * {@inheritdoc}
- */
- public function getBigIntTypeDeclarationSQL(array $columnDef)
- {
- $columnDef['integer_type'] = 'BIGINT';
- return $this->_getCommonIntegerTypeDeclarationSQL($columnDef);
- }
- /**
- * {@inheritdoc}
- */
- public function getBinaryDefaultLength()
- {
- return 1;
- }
- /**
- * {@inheritdoc}
- */
- public function getBinaryMaxLength()
- {
- return 32767;
- }
- /**
- * {@inheritdoc}
- */
- public function getBlobTypeDeclarationSQL(array $field)
- {
- return 'LONG BINARY';
- }
- /**
- * {@inheritdoc}
- *
- * BIT type columns require an explicit NULL declaration
- * in SQL Anywhere if they shall be nullable.
- * Otherwise by just omitting the NOT NULL clause,
- * SQL Anywhere will declare them NOT NULL nonetheless.
- */
- public function getBooleanTypeDeclarationSQL(array $columnDef)
- {
- $nullClause = isset($columnDef['notnull']) && (bool) $columnDef['notnull'] === false ? ' NULL' : '';
- return 'BIT' . $nullClause;
- }
- /**
- * {@inheritdoc}
- */
- public function getClobTypeDeclarationSQL(array $field)
- {
- return 'TEXT';
- }
- /**
- * {@inheritdoc}
- */
- public function getCommentOnColumnSQL($tableName, $columnName, $comment)
- {
- $tableName = new Identifier($tableName);
- $columnName = new Identifier($columnName);
- $comment = $comment === null ? 'NULL' : $this->quoteStringLiteral($comment);
- return sprintf(
- 'COMMENT ON COLUMN %s.%s IS %s',
- $tableName->getQuotedName($this),
- $columnName->getQuotedName($this),
- $comment
- );
- }
- /**
- * {@inheritdoc}
- */
- public function getConcatExpression()
- {
- return 'STRING(' . implode(', ', (array) func_get_args()) . ')';
- }
- /**
- * {@inheritdoc}
- */
- public function getCreateConstraintSQL(Constraint $constraint, $table)
- {
- if ($constraint instanceof ForeignKeyConstraint) {
- return $this->getCreateForeignKeySQL($constraint, $table);
- }
- if ($table instanceof Table) {
- $table = $table->getQuotedName($this);
- }
- return 'ALTER TABLE ' . $table .
- ' ADD ' . $this->getTableConstraintDeclarationSQL($constraint, $constraint->getQuotedName($this));
- }
- /**
- * {@inheritdoc}
- */
- public function getCreateDatabaseSQL($database)
- {
- $database = new Identifier($database);
- return "CREATE DATABASE '" . $database->getName() . "'";
- }
- /**
- * {@inheritdoc}
- *
- * Appends SQL Anywhere specific flags if given.
- */
- public function getCreateIndexSQL(Index $index, $table)
- {
- return parent::getCreateIndexSQL($index, $table) . $this->getAdvancedIndexOptionsSQL($index);
- }
- /**
- * {@inheritdoc}
- */
- public function getCreatePrimaryKeySQL(Index $index, $table)
- {
- if ($table instanceof Table) {
- $table = $table->getQuotedName($this);
- }
- return 'ALTER TABLE ' . $table . ' ADD ' . $this->getPrimaryKeyDeclarationSQL($index);
- }
- /**
- * {@inheritdoc}
- */
- public function getCreateTemporaryTableSnippetSQL()
- {
- return 'CREATE ' . $this->getTemporaryTableSQL() . ' TABLE';
- }
- /**
- * {@inheritdoc}
- */
- public function getCreateViewSQL($name, $sql)
- {
- return 'CREATE VIEW ' . $name . ' AS ' . $sql;
- }
- /**
- * {@inheritdoc}
- */
- public function getCurrentDateSQL()
- {
- return 'CURRENT DATE';
- }
- /**
- * {@inheritdoc}
- */
- public function getCurrentTimeSQL()
- {
- return 'CURRENT TIME';
- }
- /**
- * {@inheritdoc}
- */
- public function getCurrentTimestampSQL()
- {
- return 'CURRENT TIMESTAMP';
- }
- /**
- * {@inheritdoc}
- */
- protected function getDateArithmeticIntervalExpression($date, $operator, $interval, $unit)
- {
- $factorClause = '';
- if ($operator === '-') {
- $factorClause = '-1 * ';
- }
- return 'DATEADD(' . $unit . ', ' . $factorClause . $interval . ', ' . $date . ')';
- }
- /**
- * {@inheritdoc}
- */
- public function getDateDiffExpression($date1, $date2)
- {
- return 'DATEDIFF(day, ' . $date2 . ', ' . $date1 . ')';
- }
- /**
- * {@inheritdoc}
- */
- public function getDateTimeFormatString()
- {
- return 'Y-m-d H:i:s.u';
- }
- /**
- * {@inheritdoc}
- */
- public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration)
- {
- return 'DATETIME';
- }
- /**
- * {@inheritdoc}
- */
- public function getDateTimeTzFormatString()
- {
- return $this->getDateTimeFormatString();
- }
- /**
- * {@inheritdoc}
- */
- public function getDateTypeDeclarationSQL(array $fieldDeclaration)
- {
- return 'DATE';
- }
- /**
- * {@inheritdoc}
- */
- public function getDefaultTransactionIsolationLevel()
- {
- return TransactionIsolationLevel::READ_UNCOMMITTED;
- }
- /**
- * {@inheritdoc}
- */
- public function getDropDatabaseSQL($database)
- {
- $database = new Identifier($database);
- return "DROP DATABASE '" . $database->getName() . "'";
- }
- /**
- * {@inheritdoc}
- */
- public function getDropIndexSQL($index, $table = null)
- {
- if ($index instanceof Index) {
- $index = $index->getQuotedName($this);
- }
- if (! is_string($index)) {
- throw new InvalidArgumentException(
- 'SQLAnywherePlatform::getDropIndexSQL() expects $index parameter to be string or ' . Index::class . '.'
- );
- }
- if (! isset($table)) {
- return 'DROP INDEX ' . $index;
- }
- if ($table instanceof Table) {
- $table = $table->getQuotedName($this);
- }
- if (! is_string($table)) {
- throw new InvalidArgumentException(
- 'SQLAnywherePlatform::getDropIndexSQL() expects $table parameter to be string or ' . Index::class . '.'
- );
- }
- return 'DROP INDEX ' . $table . '.' . $index;
- }
- /**
- * {@inheritdoc}
- */
- public function getDropViewSQL($name)
- {
- return 'DROP VIEW ' . $name;
- }
- /**
- * {@inheritdoc}
- */
- public function getForeignKeyBaseDeclarationSQL(ForeignKeyConstraint $foreignKey)
- {
- $sql = '';
- $foreignKeyName = $foreignKey->getName();
- $localColumns = $foreignKey->getQuotedLocalColumns($this);
- $foreignColumns = $foreignKey->getQuotedForeignColumns($this);
- $foreignTableName = $foreignKey->getQuotedForeignTableName($this);
- if (! empty($foreignKeyName)) {
- $sql .= 'CONSTRAINT ' . $foreignKey->getQuotedName($this) . ' ';
- }
- if (empty($localColumns)) {
- throw new InvalidArgumentException("Incomplete definition. 'local' required.");
- }
- if (empty($foreignColumns)) {
- throw new InvalidArgumentException("Incomplete definition. 'foreign' required.");
- }
- if (empty($foreignTableName)) {
- throw new InvalidArgumentException("Incomplete definition. 'foreignTable' required.");
- }
- if ($foreignKey->hasOption('notnull') && (bool) $foreignKey->getOption('notnull')) {
- $sql .= 'NOT NULL ';
- }
- return $sql .
- 'FOREIGN KEY (' . $this->getIndexFieldDeclarationListSQL($localColumns) . ') ' .
- 'REFERENCES ' . $foreignKey->getQuotedForeignTableName($this) .
- ' (' . $this->getIndexFieldDeclarationListSQL($foreignColumns) . ')';
- }
- /**
- * Returns foreign key MATCH clause for given type.
- *
- * @param int $type The foreign key match type
- *
- * @return string
- *
- * @throws InvalidArgumentException If unknown match type given.
- */
- public function getForeignKeyMatchClauseSQL($type)
- {
- switch ((int) $type) {
- case self::FOREIGN_KEY_MATCH_SIMPLE:
- return 'SIMPLE';
- case self::FOREIGN_KEY_MATCH_FULL:
- return 'FULL';
- case self::FOREIGN_KEY_MATCH_SIMPLE_UNIQUE:
- return 'UNIQUE SIMPLE';
- case self::FOREIGN_KEY_MATCH_FULL_UNIQUE:
- return 'UNIQUE FULL';
- default:
- throw new InvalidArgumentException('Invalid foreign key match type: ' . $type);
- }
- }
- /**
- * {@inheritdoc}
- */
- public function getForeignKeyReferentialActionSQL($action)
- {
- // NO ACTION is not supported, therefore falling back to RESTRICT.
- if (strtoupper($action) === 'NO ACTION') {
- return 'RESTRICT';
- }
- return parent::getForeignKeyReferentialActionSQL($action);
- }
- /**
- * {@inheritdoc}
- */
- public function getForUpdateSQL()
- {
- return '';
- }
- /**
- * {@inheritdoc}
- *
- * @deprecated Use application-generated UUIDs instead
- */
- public function getGuidExpression()
- {
- return 'NEWID()';
- }
- /**
- * {@inheritdoc}
- */
- public function getGuidTypeDeclarationSQL(array $field)
- {
- return 'UNIQUEIDENTIFIER';
- }
- /**
- * {@inheritdoc}
- */
- public function getIndexDeclarationSQL($name, Index $index)
- {
- // Index declaration in statements like CREATE TABLE is not supported.
- throw DBALException::notSupported(__METHOD__);
- }
- /**
- * {@inheritdoc}
- */
- public function getIntegerTypeDeclarationSQL(array $columnDef)
- {
- $columnDef['integer_type'] = 'INT';
- return $this->_getCommonIntegerTypeDeclarationSQL($columnDef);
- }
- /**
- * {@inheritdoc}
- */
- public function getListDatabasesSQL()
- {
- return 'SELECT db_name(number) AS name FROM sa_db_list()';
- }
- /**
- * {@inheritdoc}
- */
- public function getListTableColumnsSQL($table, $database = null)
- {
- $user = 'USER_NAME()';
- if (strpos($table, '.') !== false) {
- [$user, $table] = explode('.', $table);
- $user = $this->quoteStringLiteral($user);
- }
- return sprintf(
- <<<'SQL'
- SELECT col.column_name,
- COALESCE(def.user_type_name, def.domain_name) AS 'type',
- def.declared_width AS 'length',
- def.scale,
- CHARINDEX('unsigned', def.domain_name) AS 'unsigned',
- IF col.nulls = 'Y' THEN 0 ELSE 1 ENDIF AS 'notnull',
- col."default",
- def.is_autoincrement AS 'autoincrement',
- rem.remarks AS 'comment'
- FROM sa_describe_query('SELECT * FROM "%s"') AS def
- JOIN SYS.SYSTABCOL AS col
- ON col.table_id = def.base_table_id AND col.column_id = def.base_column_id
- LEFT JOIN SYS.SYSREMARK AS rem
- ON col.object_id = rem.object_id
- WHERE def.base_owner_name = %s
- ORDER BY def.base_column_id ASC
- SQL
- ,
- $table,
- $user
- );
- }
- /**
- * {@inheritdoc}
- *
- * @todo Where is this used? Which information should be retrieved?
- */
- public function getListTableConstraintsSQL($table)
- {
- $user = '';
- if (strpos($table, '.') !== false) {
- [$user, $table] = explode('.', $table);
- $user = $this->quoteStringLiteral($user);
- $table = $this->quoteStringLiteral($table);
- } else {
- $table = $this->quoteStringLiteral($table);
- }
- return sprintf(
- <<<'SQL'
- SELECT con.*
- FROM SYS.SYSCONSTRAINT AS con
- JOIN SYS.SYSTAB AS tab ON con.table_object_id = tab.object_id
- WHERE tab.table_name = %s
- AND tab.creator = USER_ID(%s)
- SQL
- ,
- $table,
- $user
- );
- }
- /**
- * {@inheritdoc}
- */
- public function getListTableForeignKeysSQL($table)
- {
- $user = '';
- if (strpos($table, '.') !== false) {
- [$user, $table] = explode('.', $table);
- $user = $this->quoteStringLiteral($user);
- $table = $this->quoteStringLiteral($table);
- } else {
- $table = $this->quoteStringLiteral($table);
- }
- return sprintf(
- <<<'SQL'
- SELECT fcol.column_name AS local_column,
- ptbl.table_name AS foreign_table,
- pcol.column_name AS foreign_column,
- idx.index_name,
- IF fk.nulls = 'N'
- THEN 1
- ELSE NULL
- ENDIF AS notnull,
- CASE ut.referential_action
- WHEN 'C' THEN 'CASCADE'
- WHEN 'D' THEN 'SET DEFAULT'
- WHEN 'N' THEN 'SET NULL'
- WHEN 'R' THEN 'RESTRICT'
- ELSE NULL
- END AS on_update,
- CASE dt.referential_action
- WHEN 'C' THEN 'CASCADE'
- WHEN 'D' THEN 'SET DEFAULT'
- WHEN 'N' THEN 'SET NULL'
- WHEN 'R' THEN 'RESTRICT'
- ELSE NULL
- END AS on_delete,
- IF fk.check_on_commit = 'Y'
- THEN 1
- ELSE NULL
- ENDIF AS check_on_commit, -- check_on_commit flag
- IF ftbl.clustered_index_id = idx.index_id
- THEN 1
- ELSE NULL
- ENDIF AS 'clustered', -- clustered flag
- IF fk.match_type = 0
- THEN NULL
- ELSE fk.match_type
- ENDIF AS 'match', -- match option
- IF pidx.max_key_distance = 1
- THEN 1
- ELSE NULL
- ENDIF AS for_olap_workload -- for_olap_workload flag
- FROM SYS.SYSFKEY AS fk
- JOIN SYS.SYSIDX AS idx
- ON fk.foreign_table_id = idx.table_id
- AND fk.foreign_index_id = idx.index_id
- JOIN SYS.SYSPHYSIDX pidx
- ON idx.table_id = pidx.table_id
- AND idx.phys_index_id = pidx.phys_index_id
- JOIN SYS.SYSTAB AS ptbl
- ON fk.primary_table_id = ptbl.table_id
- JOIN SYS.SYSTAB AS ftbl
- ON fk.foreign_table_id = ftbl.table_id
- JOIN SYS.SYSIDXCOL AS idxcol
- ON idx.table_id = idxcol.table_id
- AND idx.index_id = idxcol.index_id
- JOIN SYS.SYSTABCOL AS pcol
- ON ptbl.table_id = pcol.table_id
- AND idxcol.primary_column_id = pcol.column_id
- JOIN SYS.SYSTABCOL AS fcol
- ON ftbl.table_id = fcol.table_id
- AND idxcol.column_id = fcol.column_id
- LEFT JOIN SYS.SYSTRIGGER ut
- ON fk.foreign_table_id = ut.foreign_table_id
- AND fk.foreign_index_id = ut.foreign_key_id
- AND ut.event = 'C'
- LEFT JOIN SYS.SYSTRIGGER dt
- ON fk.foreign_table_id = dt.foreign_table_id
- AND fk.foreign_index_id = dt.foreign_key_id
- AND dt.event = 'D'
- WHERE ftbl.table_name = %s
- AND ftbl.creator = USER_ID(%s)
- ORDER BY fk.foreign_index_id ASC, idxcol.sequence ASC
- SQL
- ,
- $table,
- $user
- );
- }
- /**
- * {@inheritdoc}
- */
- public function getListTableIndexesSQL($table, $currentDatabase = null)
- {
- $user = '';
- if (strpos($table, '.') !== false) {
- [$user, $table] = explode('.', $table);
- $user = $this->quoteStringLiteral($user);
- $table = $this->quoteStringLiteral($table);
- } else {
- $table = $this->quoteStringLiteral($table);
- }
- return sprintf(
- <<<'SQL'
- SELECT idx.index_name AS key_name,
- IF idx.index_category = 1
- THEN 1
- ELSE 0
- ENDIF AS 'primary',
- col.column_name,
- IF idx."unique" IN(1, 2, 5)
- THEN 0
- ELSE 1
- ENDIF AS non_unique,
- IF tbl.clustered_index_id = idx.index_id
- THEN 1
- ELSE NULL
- ENDIF AS 'clustered', -- clustered flag
- IF idx."unique" = 5
- THEN 1
- ELSE NULL
- ENDIF AS with_nulls_not_distinct, -- with_nulls_not_distinct flag
- IF pidx.max_key_distance = 1
- THEN 1
- ELSE NULL
- ENDIF AS for_olap_workload -- for_olap_workload flag
- FROM SYS.SYSIDX AS idx
- JOIN SYS.SYSPHYSIDX pidx
- ON idx.table_id = pidx.table_id
- AND idx.phys_index_id = pidx.phys_index_id
- JOIN SYS.SYSIDXCOL AS idxcol
- ON idx.table_id = idxcol.table_id AND idx.index_id = idxcol.index_id
- JOIN SYS.SYSTABCOL AS col
- ON idxcol.table_id = col.table_id AND idxcol.column_id = col.column_id
- JOIN SYS.SYSTAB AS tbl
- ON idx.table_id = tbl.table_id
- WHERE tbl.table_name = %s
- AND tbl.creator = USER_ID(%s)
- AND idx.index_category != 2 -- exclude indexes implicitly created by foreign key constraints
- ORDER BY idx.index_id ASC, idxcol.sequence ASC
- SQL
- ,
- $table,
- $user
- );
- }
- /**
- * {@inheritdoc}
- */
- public function getListTablesSQL()
- {
- return "SELECT tbl.table_name
- FROM SYS.SYSTAB AS tbl
- JOIN SYS.SYSUSER AS usr ON tbl.creator = usr.user_id
- JOIN dbo.SYSOBJECTS AS obj ON tbl.object_id = obj.id
- WHERE tbl.table_type IN(1, 3) -- 'BASE', 'GBL TEMP'
- AND usr.user_name NOT IN('SYS', 'dbo', 'rs_systabgroup') -- exclude system users
- AND obj.type = 'U' -- user created tables only
- ORDER BY tbl.table_name ASC";
- }
- /**
- * {@inheritdoc}
- *
- * @todo Where is this used? Which information should be retrieved?
- */
- public function getListUsersSQL()
- {
- return 'SELECT * FROM SYS.SYSUSER ORDER BY user_name ASC';
- }
- /**
- * {@inheritdoc}
- */
- public function getListViewsSQL($database)
- {
- return "SELECT tbl.table_name, v.view_def
- FROM SYS.SYSVIEW v
- JOIN SYS.SYSTAB tbl ON v.view_object_id = tbl.object_id
- JOIN SYS.SYSUSER usr ON tbl.creator = usr.user_id
- JOIN dbo.SYSOBJECTS obj ON tbl.object_id = obj.id
- WHERE usr.user_name NOT IN('SYS', 'dbo', 'rs_systabgroup') -- exclude system users
- ORDER BY tbl.table_name ASC";
- }
- /**
- * {@inheritdoc}
- */
- public function getLocateExpression($str, $substr, $startPos = false)
- {
- if ($startPos === false) {
- return 'LOCATE(' . $str . ', ' . $substr . ')';
- }
- return 'LOCATE(' . $str . ', ' . $substr . ', ' . $startPos . ')';
- }
- /**
- * {@inheritdoc}
- */
- public function getMaxIdentifierLength()
- {
- return 128;
- }
- /**
- * {@inheritdoc}
- */
- public function getMd5Expression($column)
- {
- return 'HASH(' . $column . ", 'MD5')";
- }
- /**
- * {@inheritdoc}
- */
- public function getName()
- {
- return 'sqlanywhere';
- }
- /**
- * Obtain DBMS specific SQL code portion needed to set a primary key
- * declaration to be used in statements like ALTER TABLE.
- *
- * @param Index $index Index definition
- * @param string $name Name of the primary key
- *
- * @return string DBMS specific SQL code portion needed to set a primary key
- *
- * @throws InvalidArgumentException If the given index is not a primary key.
- */
- public function getPrimaryKeyDeclarationSQL(Index $index, $name = null)
- {
- if (! $index->isPrimary()) {
- throw new InvalidArgumentException(
- 'Can only create primary key declarations with getPrimaryKeyDeclarationSQL()'
- );
- }
- return $this->getTableConstraintDeclarationSQL($index, $name);
- }
- /**
- * {@inheritdoc}
- */
- public function getSetTransactionIsolationSQL($level)
- {
- return 'SET TEMPORARY OPTION isolation_level = ' . $this->_getTransactionIsolationLevelSQL($level);
- }
- /**
- * {@inheritdoc}
- */
- public function getSmallIntTypeDeclarationSQL(array $columnDef)
- {
- $columnDef['integer_type'] = 'SMALLINT';
- return $this->_getCommonIntegerTypeDeclarationSQL($columnDef);
- }
- /**
- * Returns the SQL statement for starting an existing database.
- *
- * In SQL Anywhere you can start and stop databases on a
- * database server instance.
- * This is a required statement after having created a new database
- * as it has to be explicitly started to be usable.
- * SQL Anywhere does not automatically start a database after creation!
- *
- * @param string $database Name of the database to start.
- *
- * @return string
- */
- public function getStartDatabaseSQL($database)
- {
- $database = new Identifier($database);
- return "START DATABASE '" . $database->getName() . "' AUTOSTOP OFF";
- }
- /**
- * Returns the SQL statement for stopping a running database.
- *
- * In SQL Anywhere you can start and stop databases on a
- * database server instance.
- * This is a required statement before dropping an existing database
- * as it has to be explicitly stopped before it can be dropped.
- *
- * @param string $database Name of the database to stop.
- *
- * @return string
- */
- public function getStopDatabaseSQL($database)
- {
- $database = new Identifier($database);
- return 'STOP DATABASE "' . $database->getName() . '" UNCONDITIONALLY';
- }
- /**
- * {@inheritdoc}
- */
- public function getSubstringExpression($value, $from, $length = null)
- {
- if ($length === null) {
- return 'SUBSTRING(' . $value . ', ' . $from . ')';
- }
- return 'SUBSTRING(' . $value . ', ' . $from . ', ' . $length . ')';
- }
- /**
- * {@inheritdoc}
- */
- public function getTemporaryTableSQL()
- {
- return 'GLOBAL TEMPORARY';
- }
- /**
- * {@inheritdoc}
- */
- public function getTimeFormatString()
- {
- return 'H:i:s.u';
- }
- /**
- * {@inheritdoc}
- */
- public function getTimeTypeDeclarationSQL(array $fieldDeclaration)
- {
- return 'TIME';
- }
- /**
- * {@inheritdoc}
- */
- public function getTrimExpression($str, $pos = TrimMode::UNSPECIFIED, $char = false)
- {
- if (! $char) {
- switch ($pos) {
- case TrimMode::LEADING:
- return $this->getLtrimExpression($str);
- case TrimMode::TRAILING:
- return $this->getRtrimExpression($str);
- default:
- return 'TRIM(' . $str . ')';
- }
- }
- $pattern = "'%[^' + " . $char . " + ']%'";
- switch ($pos) {
- case TrimMode::LEADING:
- return 'SUBSTR(' . $str . ', PATINDEX(' . $pattern . ', ' . $str . '))';
- case TrimMode::TRAILING:
- return 'REVERSE(SUBSTR(REVERSE(' . $str . '), PATINDEX(' . $pattern . ', REVERSE(' . $str . '))))';
- default:
- return 'REVERSE(SUBSTR(REVERSE(SUBSTR(' . $str . ', PATINDEX(' . $pattern . ', ' . $str . '))), ' .
- 'PATINDEX(' . $pattern . ', REVERSE(SUBSTR(' . $str . ', PATINDEX(' . $pattern . ', ' . $str . '))))))';
- }
- }
- /**
- * {@inheritdoc}
- */
- public function getTruncateTableSQL($tableName, $cascade = false)
- {
- $tableIdentifier = new Identifier($tableName);
- return 'TRUNCATE TABLE ' . $tableIdentifier->getQuotedName($this);
- }
- /**
- * {@inheritdoc}
- */
- public function getUniqueConstraintDeclarationSQL($name, Index $index)
- {
- if ($index->isPrimary()) {
- throw new InvalidArgumentException(
- 'Cannot create primary key constraint declarations with getUniqueConstraintDeclarationSQL().'
- );
- }
- if (! $index->isUnique()) {
- throw new InvalidArgumentException(
- 'Can only create unique constraint declarations, no common index declarations with ' .
- 'getUniqueConstraintDeclarationSQL().'
- );
- }
- return $this->getTableConstraintDeclarationSQL($index, $name);
- }
- /**
- * {@inheritdoc}
- */
- public function getVarcharDefaultLength()
- {
- return 1;
- }
- /**
- * {@inheritdoc}
- */
- public function getVarcharMaxLength()
- {
- return 32767;
- }
- /**
- * {@inheritdoc}
- */
- public function hasNativeGuidType()
- {
- return true;
- }
- /**
- * {@inheritdoc}
- */
- public function prefersIdentityColumns()
- {
- return true;
- }
- /**
- * {@inheritdoc}
- */
- public function supportsCommentOnStatement()
- {
- return true;
- }
- /**
- * {@inheritdoc}
- */
- public function supportsIdentityColumns()
- {
- return true;
- }
- /**
- * {@inheritdoc}
- */
- protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef)
- {
- $unsigned = ! empty($columnDef['unsigned']) ? 'UNSIGNED ' : '';
- $autoincrement = ! empty($columnDef['autoincrement']) ? ' IDENTITY' : '';
- return $unsigned . $columnDef['integer_type'] . $autoincrement;
- }
- /**
- * {@inheritdoc}
- */
- protected function _getCreateTableSQL($tableName, array $columns, array $options = [])
- {
- $columnListSql = $this->getColumnDeclarationListSQL($columns);
- $indexSql = [];
- if (! empty($options['uniqueConstraints'])) {
- foreach ((array) $options['uniqueConstraints'] as $name => $definition) {
- $columnListSql .= ', ' . $this->getUniqueConstraintDeclarationSQL($name, $definition);
- }
- }
- if (! empty($options['indexes'])) {
- foreach ((array) $options['indexes'] as $index) {
- assert($index instanceof Index);
- $indexSql[] = $this->getCreateIndexSQL($index, $tableName);
- }
- }
- if (! empty($options['primary'])) {
- $flags = '';
- if (isset($options['primary_index']) && $options['primary_index']->hasFlag('clustered')) {
- $flags = ' CLUSTERED ';
- }
- $columnListSql .= ', PRIMARY KEY' . $flags . ' (' . implode(', ', array_unique(array_values((array) $options['primary']))) . ')';
- }
- if (! empty($options['foreignKeys'])) {
- foreach ((array) $options['foreignKeys'] as $definition) {
- $columnListSql .= ', ' . $this->getForeignKeyDeclarationSQL($definition);
- }
- }
- $query = 'CREATE TABLE ' . $tableName . ' (' . $columnListSql;
- $check = $this->getCheckDeclarationSQL($columns);
- if (! empty($check)) {
- $query .= ', ' . $check;
- }
- $query .= ')';
- return array_merge([$query], $indexSql);
- }
- /**
- * {@inheritdoc}
- */
- protected function _getTransactionIsolationLevelSQL($level)
- {
- switch ($level) {
- case TransactionIsolationLevel::READ_UNCOMMITTED:
- return '0';
- case TransactionIsolationLevel::READ_COMMITTED:
- return '1';
- case TransactionIsolationLevel::REPEATABLE_READ:
- return '2';
- case TransactionIsolationLevel::SERIALIZABLE:
- return '3';
- default:
- throw new InvalidArgumentException('Invalid isolation level:' . $level);
- }
- }
- /**
- * {@inheritdoc}
- */
- protected function doModifyLimitQuery($query, $limit, $offset)
- {
- $limitOffsetClause = $this->getTopClauseSQL($limit, $offset);
- if ($limitOffsetClause === '') {
- return $query;
- }
- if (! preg_match('/^\s*(SELECT\s+(DISTINCT\s+)?)(.*)/i', $query, $matches)) {
- return $query;
- }
- return $matches[1] . $limitOffsetClause . ' ' . $matches[3];
- }
- private function getTopClauseSQL(?int $limit, ?int $offset) : string
- {
- if ($offset > 0) {
- return sprintf('TOP %s START AT %d', $limit ?? 'ALL', $offset + 1);
- }
- return $limit === null ? '' : 'TOP ' . $limit;
- }
- /**
- * Return the INDEX query section dealing with non-standard
- * SQL Anywhere options.
- *
- * @param Index $index Index definition
- *
- * @return string
- */
- protected function getAdvancedIndexOptionsSQL(Index $index)
- {
- $sql = '';
- if (! $index->isPrimary() && $index->hasFlag('for_olap_workload')) {
- $sql .= ' FOR OLAP WORKLOAD';
- }
- return $sql;
- }
- /**
- * {@inheritdoc}
- */
- protected function getBinaryTypeDeclarationSQLSnippet($length, $fixed)
- {
- return $fixed
- ? 'BINARY(' . ($length ?: $this->getBinaryDefaultLength()) . ')'
- : 'VARBINARY(' . ($length ?: $this->getBinaryDefaultLength()) . ')';
- }
- /**
- * Returns the SQL snippet for creating a table constraint.
- *
- * @param Constraint $constraint The table constraint to create the SQL snippet for.
- * @param string|null $name The table constraint name to use if any.
- *
- * @return string
- *
- * @throws InvalidArgumentException If the given table constraint type is not supported by this method.
- */
- protected function getTableConstraintDeclarationSQL(Constraint $constraint, $name = null)
- {
- if ($constraint instanceof ForeignKeyConstraint) {
- return $this->getForeignKeyDeclarationSQL($constraint);
- }
- if (! $constraint instanceof Index) {
- throw new InvalidArgumentException('Unsupported constraint type: ' . get_class($constraint));
- }
- if (! $constraint->isPrimary() && ! $constraint->isUnique()) {
- throw new InvalidArgumentException(
- 'Can only create primary, unique or foreign key constraint declarations, no common index declarations ' .
- 'with getTableConstraintDeclarationSQL().'
- );
- }
- $constraintColumns = $constraint->getQuotedColumns($this);
- if (empty($constraintColumns)) {
- throw new InvalidArgumentException("Incomplete definition. 'columns' required.");
- }
- $sql = '';
- $flags = '';
- if (! empty($name)) {
- $name = new Identifier($name);
- $sql .= 'CONSTRAINT ' . $name->getQuotedName($this) . ' ';
- }
- if ($constraint->hasFlag('clustered')) {
- $flags = 'CLUSTERED ';
- }
- if ($constraint->isPrimary()) {
- return $sql . 'PRIMARY KEY ' . $flags . '(' . $this->getIndexFieldDeclarationListSQL($constraintColumns) . ')';
- }
- return $sql . 'UNIQUE ' . $flags . '(' . $this->getIndexFieldDeclarationListSQL($constraintColumns) . ')';
- }
- /**
- * {@inheritdoc}
- */
- protected function getCreateIndexSQLFlags(Index $index)
- {
- $type = '';
- if ($index->hasFlag('virtual')) {
- $type .= 'VIRTUAL ';
- }
- if ($index->isUnique()) {
- $type .= 'UNIQUE ';
- }
- if ($index->hasFlag('clustered')) {
- $type .= 'CLUSTERED ';
- }
- return $type;
- }
- /**
- * {@inheritdoc}
- */
- protected function getRenameIndexSQL($oldIndexName, Index $index, $tableName)
- {
- return ['ALTER INDEX ' . $oldIndexName . ' ON ' . $tableName . ' RENAME TO ' . $index->getQuotedName($this)];
- }
- /**
- * {@inheritdoc}
- */
- protected function getReservedKeywordsClass()
- {
- return Keywords\SQLAnywhereKeywords::class;
- }
- /**
- * {@inheritdoc}
- */
- protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)
- {
- return $fixed
- ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(' . $this->getVarcharDefaultLength() . ')')
- : ($length ? 'VARCHAR(' . $length . ')' : 'VARCHAR(' . $this->getVarcharDefaultLength() . ')');
- }
- /**
- * {@inheritdoc}
- */
- protected function initializeDoctrineTypeMappings()
- {
- $this->doctrineTypeMapping = [
- 'char' => 'string',
- 'long nvarchar' => 'text',
- 'long varchar' => 'text',
- 'nchar' => 'string',
- 'ntext' => 'text',
- 'nvarchar' => 'string',
- 'text' => 'text',
- 'uniqueidentifierstr' => 'guid',
- 'varchar' => 'string',
- 'xml' => 'text',
- 'bigint' => 'bigint',
- 'unsigned bigint' => 'bigint',
- 'bit' => 'boolean',
- 'decimal' => 'decimal',
- 'double' => 'float',
- 'float' => 'float',
- 'int' => 'integer',
- 'integer' => 'integer',
- 'unsigned int' => 'integer',
- 'numeric' => 'decimal',
- 'smallint' => 'smallint',
- 'unsigned smallint' => 'smallint',
- 'tinyint' => 'smallint',
- 'unsigned tinyint' => 'smallint',
- 'money' => 'decimal',
- 'smallmoney' => 'decimal',
- 'long varbit' => 'text',
- 'varbit' => 'string',
- 'date' => 'date',
- 'datetime' => 'datetime',
- 'smalldatetime' => 'datetime',
- 'time' => 'time',
- 'timestamp' => 'datetime',
- 'binary' => 'binary',
- 'image' => 'blob',
- 'long binary' => 'blob',
- 'uniqueidentifier' => 'guid',
- 'varbinary' => 'binary',
- ];
- }
- }