SQLAnywherePlatform.php

<?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.
 *
 * @deprecated Use SQLAnywhere 16 or newer
 */
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 $column)
    {
        $column['integer_type'] = 'BIGINT';

        return $this->_getCommonIntegerTypeDeclarationSQL($column);
    }

    /**
     * {@inheritdoc}
     */
    public function getBinaryDefaultLength()
    {
        return 1;
    }

    /**
     * {@inheritdoc}
     */
    public function getBinaryMaxLength()
    {
        return 32767;
    }

    /**
     * {@inheritdoc}
     */
    public function getBlobTypeDeclarationSQL(array $column)
    {
        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 $column)
    {
        $nullClause = isset($column['notnull']) && (bool) $column['notnull'] === false ? ' NULL' : '';

        return 'BIT' . $nullClause;
    }

    /**
     * {@inheritdoc}
     */
    public function getClobTypeDeclarationSQL(array $column)
    {
        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 $column)
    {
        return 'DATETIME';
    }

    /**
     * {@inheritdoc}
     */
    public function getDateTimeTzFormatString()
    {
        return $this->getDateTimeFormatString();
    }

    /**
     * {@inheritdoc}
     */
    public function getDateTypeDeclarationSQL(array $column)
    {
        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(
                __METHOD__ . '() 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(
                __METHOD__ . '() 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 $column)
    {
        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 $column)
    {
        $column['integer_type'] = 'INT';

        return $this->_getCommonIntegerTypeDeclarationSQL($column);
    }

    /**
     * {@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, $database = 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 $column)
    {
        $column['integer_type'] = 'SMALLINT';

        return $this->_getCommonIntegerTypeDeclarationSQL($column);
    }

    /**
     * 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 $column)
    {
        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 $column)
    {
        $unsigned      = ! empty($column['unsigned']) ? 'UNSIGNED ' : '';
        $autoincrement = ! empty($column['autoincrement']) ? ' IDENTITY' : '';

        return $unsigned . $column['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',
        ];
    }
}