<?php
/*
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * and is licensed under the MIT license. For more information, see
 * <http://www.doctrine-project.org>.
 */

namespace Doctrine\DBAL\Platforms;

use Doctrine\DBAL\Connection;
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\Index;
use Doctrine\DBAL\Schema\Identifier;
use Doctrine\DBAL\Schema\Table;
use Doctrine\DBAL\Schema\TableDiff;

/**
 * The SQLAnywherePlatform provides the behavior, features and SQL dialect of the
 * SAP Sybase SQL Anywhere 10 database platform.
 *
 * @author Steve Müller <st.mueller@dzh-online.de>
 * @link   www.doctrine-project.org
 * @since  2.5
 */
class SQLAnywherePlatform extends AbstractPlatform
{
    /**
     * @var integer
     */
    const FOREIGN_KEY_MATCH_SIMPLE = 1;
    /**
     * @var integer
     */
    const FOREIGN_KEY_MATCH_FULL = 2;
    /**
     * @var integer
     */
    const FOREIGN_KEY_MATCH_SIMPLE_UNIQUE = 129;
    /**
     * @var integer
     */
    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') && (boolean) $foreignKey->getOption('check_on_commit')) {
            $query .= ' CHECK ON COMMIT';
        }

        if ($foreignKey->hasOption('clustered') && (boolean) $foreignKey->getOption('clustered')) {
            $query .= ' CLUSTERED';
        }

        if ($foreignKey->hasOption('for_olap_workload') && (boolean) $foreignKey->getOption('for_olap_workload')) {
            $query .= ' FOR OLAP WORKLOAD';
        }

        return $query;
    }

    /**
     * {@inheritdoc}
     */
    public function getAlterTableSQL(TableDiff $diff)
    {
        $sql          = array();
        $columnSql    = array();
        $commentsSQL  = array();
        $tableSql     = array();
        $alterClauses = array();

        /** @var \Doctrine\DBAL\Schema\Column $column */
        foreach ($diff->addedColumns as $column) {
            if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) {
                continue;
            }

            $alterClauses[] = $this->getAlterTableAddColumnClause($column);

            $comment = $this->getColumnComment($column);

            if (null !== $comment && '' !== $comment) {
                $commentsSQL[] = $this->getCommentOnColumnSQL(
                    $diff->getName($this)->getQuotedName($this),
                    $column->getQuotedName($this),
                    $comment
                );
            }
        }

        /** @var \Doctrine\DBAL\Schema\Column $column */
        foreach ($diff->removedColumns as $column) {
            if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) {
                continue;
            }

            $alterClauses[] = $this->getAlterTableRemoveColumnClause($column);
        }

        /** @var \Doctrine\DBAL\Schema\ColumnDiff $columnDiff */
        foreach ($diff->changedColumns as $columnDiff) {
            if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) {
                continue;
            }

            $alterClause = $this->getAlterTableChangeColumnClause($columnDiff);

            if (null !== $alterClause) {
                $alterClauses[] = $alterClause;
            }

            if ($columnDiff->hasChanged('comment')) {
                $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);

            if ($diff->newName !== false) {
                $sql[] = $this->getAlterTableClause($diff->getName($this)) . ' ' .
                    $this->getAlterTableRenameTableClause($diff->getNewName());
            }

            $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') && null === $column->getDefault()) {
                $columnAlterationClause .= ', ALTER ' . $column->getQuotedName($this) . ' DROP DEFAULT';
            }

            return $columnAlterationClause;
        }
    }

    /**
     * {@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']) && (boolean) $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 "COMMENT ON COLUMN " . $tableName->getQuotedName($this) . '.' . $columnName->getQuotedName($this) .
            " IS $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 Connection::TRANSACTION_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 ' .
                '\Doctrine\DBAL\Schema\Index.'
            );
        }

        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 ' .
                '\Doctrine\DBAL\Schema\Table.'
            );
        }

        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') && (boolean) $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 integer $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';
                break;
            case self::FOREIGN_KEY_MATCH_FULL:
                return 'FULL';
                break;
            case self::FOREIGN_KEY_MATCH_SIMPLE_UNIQUE:
                return 'UNIQUE SIMPLE';
                break;
            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}
     */
    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) {
            list($user, $table) = explode('.', $table);
            $user = "'" . $user . "'";
        }

        return "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 \"$table\"') 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 = $user
                ORDER BY  def.base_column_id ASC";
    }

    /**
     * {@inheritdoc}
     *
     * @todo Where is this used? Which information should be retrieved?
     */
    public function getListTableConstraintsSQL($table)
    {
        $user = '';

        if (strpos($table, '.') !== false) {
            list($user, $table) = explode('.', $table);
            $user = "'" . $user . "'";
        }

        return "SELECT con.*
                FROM   SYS.SYSCONSTRAINT AS con
                JOIN   SYS.SYSTAB AS tab ON con.table_object_id = tab.object_id
                WHERE  tab.table_name = '$table'
                AND    tab.creator = USER_ID($user)";
    }

    /**
     * {@inheritdoc}
     */
    public function getListTableForeignKeysSQL($table)
    {
        $user = '';

        if (strpos($table, '.') !== false) {
            list($user, $table) = explode('.', $table);
            $user = "'" . $user . "'";
        }

        return "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 = '$table'
                AND       ftbl.creator = USER_ID($user)
                ORDER BY  fk.foreign_index_id ASC, idxcol.sequence ASC";
    }

    /**
     * {@inheritdoc}
     */
    public function getListTableIndexesSQL($table, $currentDatabase = null)
    {
        $user = '';

        if (strpos($table, '.') !== false) {
            list($user, $table) = explode('.', $table);
            $user = "'" . $user . "'";
        }

        return "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 = '$table'
                AND      tbl.creator = USER_ID($user)
                AND      idx.index_category != 2 -- exclude indexes implicitly created by foreign key constraints
                ORDER BY idx.index_id ASC, idxcol.sequence ASC";
    }

    /**
     * {@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 (null === $length) {
            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 = self::TRIM_UNSPECIFIED, $char = false)
    {
        if ( ! $char) {
            switch ($pos) {
                case self::TRIM_LEADING:
                    return $this->getLtrimExpression($str);
                case self::TRIM_TRAILING:
                    return $this->getRtrimExpression($str);
                default:
                    return 'TRIM(' . $str . ')';
            }
        }

        $pattern = "'%[^' + $char + ']%'";

        switch ($pos) {
            case self::TRIM_LEADING:
                return 'SUBSTR(' . $str . ', PATINDEX(' . $pattern . ', ' . $str . '))';
            case self::TRIM_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)
    {
        return 'TRUNCATE TABLE ' . $tableName;
    }

    /**
     * {@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 = array())
    {
        $columnListSql = $this->getColumnDeclarationListSQL($columns);
        $indexSql = array();

        if ( ! empty($options['uniqueConstraints'])) {
            foreach ((array) $options['uniqueConstraints'] as $name => $definition) {
                $columnListSql .= ', ' . $this->getUniqueConstraintDeclarationSQL($name, $definition);
            }
        }

        if ( ! empty($options['indexes'])) {
            /** @var \Doctrine\DBAL\Schema\Index $index */
            foreach ((array) $options['indexes'] as $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(array($query), $indexSql);
    }

    /**
     * {@inheritdoc}
     */
    protected function _getTransactionIsolationLevelSQL($level)
    {
        switch ($level) {
            case Connection::TRANSACTION_READ_UNCOMMITTED:
                return 0;
            case Connection::TRANSACTION_READ_COMMITTED:
                return 1;
            case Connection::TRANSACTION_REPEATABLE_READ:
                return 2;
            case Connection::TRANSACTION_SERIALIZABLE:
                return 3;
            default:
                throw new \InvalidArgumentException('Invalid isolation level:' . $level);
        }
    }

    /**
     * {@inheritdoc}
     */
    protected function doModifyLimitQuery($query, $limit, $offset)
    {
        $limitOffsetClause = '';

        if ($limit > 0) {
            $limitOffsetClause = 'TOP ' . $limit . ' ';
        }

        if ($offset > 0) {
            if ($limit == 0) {
                $limitOffsetClause = 'TOP ALL ';
            }

            $limitOffsetClause .= 'START AT ' . ($offset + 1) . ' ';
        }

        if ($limitOffsetClause) {
            return preg_replace('/^\s*(SELECT\s+(DISTINCT\s+)?)/i', '\1' . $limitOffsetClause, $query);
        }

        return $query;
    }

    /**
     * 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 array(
            'ALTER INDEX ' . $oldIndexName . ' ON ' . $tableName . ' RENAME TO ' . $index->getQuotedName($this)
        );
    }

    /**
     * {@inheritdoc}
     */
    protected function getReservedKeywordsClass()
    {
        return 'Doctrine\DBAL\Platforms\Keywords\SQLAnywhereKeywords';
    }

    /**
     * {@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 = array(
            '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',
        );
    }
}