edgardmessias / yii2-firebird

src/QueryBuilder.php

<?php

/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace edgardmessias\db\firebird;

use yii\base\InvalidParamException;
use yii\base\NotSupportedException;
use yii\db\Expression;
use yii\db\Query;

/**
 *
 * @author Edgard Lorraine Messias <edgardmessias@gmail.com>
 * @since 2.0
 */
class QueryBuilder extends \yii\db\QueryBuilder
{

    /**
     * @var array mapping from abstract column types (keys) to physical column types (values).
     */
    public $typeMap = [
        Schema::TYPE_PK        => 'integer NOT NULL PRIMARY KEY',
        Schema::TYPE_UPK       => 'integer NOT NULL PRIMARY KEY',
        Schema::TYPE_BIGPK     => 'bigint NOT NULL PRIMARY KEY',
        Schema::TYPE_UBIGPK    => 'bigint NOT NULL PRIMARY KEY',
        Schema::TYPE_CHAR      => 'char(1)',
        Schema::TYPE_STRING    => 'varchar(255)',
        Schema::TYPE_TEXT      => 'blob sub_type text',
        Schema::TYPE_SMALLINT  => 'smallint',
        Schema::TYPE_INTEGER   => 'integer',
        Schema::TYPE_BIGINT    => 'bigint',
        Schema::TYPE_FLOAT     => 'float',
        Schema::TYPE_DOUBLE    => 'double precision',
        Schema::TYPE_DECIMAL   => 'numeric(10,0)',
        Schema::TYPE_DATETIME  => 'timestamp',
        Schema::TYPE_TIMESTAMP => 'timestamp',
        Schema::TYPE_TIME      => 'time',
        Schema::TYPE_DATE      => 'date',
        Schema::TYPE_BINARY    => 'blob',
        Schema::TYPE_BOOLEAN   => 'smallint',
        Schema::TYPE_MONEY     => 'numeric(18,4)',
    ];
    
    public function init()
    {
        if ($this->db->supportColumnIdentity) {
            $this->typeMap[Schema::TYPE_PK] = 'integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY';
            $this->typeMap[Schema::TYPE_UPK] = 'integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY';
            $this->typeMap[Schema::TYPE_BIGPK] = 'bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY';
            $this->typeMap[Schema::TYPE_UBIGPK] = 'bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY';
            $this->typeMap[Schema::TYPE_BOOLEAN] = 'boolean';
        }
        
        parent::init();
    }

    protected function defaultExpressionBuilders()
    {
        return array_merge(parent::defaultExpressionBuilders(), [
            'yii\db\Expression' => 'edgardmessias\db\firebird\ExpressionBuilder',
            'yii\db\conditions\InCondition' => 'edgardmessias\db\firebird\conditions\InConditionBuilder',
            'yii\db\conditions\LikeCondition' => 'edgardmessias\db\firebird\conditions\LikeConditionBuilder',
        ]);
    }

    /**
     * Generates a SELECT SQL statement from a [[Query]] object.
     * @param Query $query the [[Query]] object from which the SQL statement will be generated.
     * @param array $params the parameters to be bound to the generated SQL statement. These parameters will
     * be included in the result with the additional parameters generated during the query building process.
     * @return array the generated SQL statement (the first array element) and the corresponding
     * parameters to be bound to the SQL statement (the second array element). The parameters returned
     * include those provided in `$params`.
     */
    public function build($query, $params = [])
    {
        $query = $query->prepare($this);

        $params = empty($params) ? $query->params : array_merge($params, $query->params);

        $clauses = [
            $this->buildSelect($query->select, $params, $query->distinct, $query->selectOption),
            $this->buildFrom($query->from, $params),
            $this->buildJoin($query->join, $params),
            $this->buildWhere($query->where, $params),
            $this->buildGroupBy($query->groupBy),
            $this->buildHaving($query->having, $params),
        ];

        $sql = implode($this->separator, array_filter($clauses));
        $sql = $this->buildOrderByAndLimit($sql, $query->orderBy, $query->limit, $query->offset);

        if (!empty($query->orderBy)) {
            foreach ($query->orderBy as $expression) {
                if ($expression instanceof Expression) {
                    $params = array_merge($params, $expression->params);
                }
            }
        }
        if (!empty($query->groupBy)) {
            foreach ($query->groupBy as $expression) {
                if ($expression instanceof Expression) {
                    $params = array_merge($params, $expression->params);
                }
            }
        }

        $union = $this->buildUnion($query->union, $params);
        if ($union !== '') {
            $sql = "$sql{$this->separator}$union";
        }

        return [$sql, $params];
    }

    /**
     * @inheritdoc
     */
    public function buildSelect($columns, &$params, $distinct = false, $selectOption = null)
    {
        if (is_array($columns)) {
            foreach ($columns as $i => $column) {
                if (!is_string($column)) {
                    continue;
                }
                $matches = [];
                if (preg_match('/^(COUNT|SUM|AVG|MIN|MAX)\([\{\[]{0,2}(\w+|\*)[\}\]]{0,2}\)$/i', $column, $matches)) {
                    $function = $matches[1];
                    $alias = $matches[2] != '*' ? $matches[2] : 'ALL';

                    $columns[$i] = "{$column} AS {$function}_{$alias}";
                }
            }
        }

        return parent::buildSelect($columns, $params, $distinct, $selectOption);
    }
    
    public function buildLimit($limit, $offset)
    {
        $sql = '';
        if ($this->hasLimit($limit)) {
            if ($limit instanceof \yii\db\ExpressionInterface) {
                $limit = "($limit)";
            }
            $sql = 'FIRST ' . $limit;
        }
        if ($this->hasOffset($offset)) {
            if ($offset instanceof \yii\db\ExpressionInterface) {
                $offset = "($offset)";
            }
            $sql .= ' SKIP ' . $offset;
        }

        return ltrim($sql);
    }

    /**
     * @inheritdoc
     */
    public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
    {

        $orderBy = $this->buildOrderBy($orderBy);
        if ($orderBy !== '') {
            $sql .= $this->separator . $orderBy;
        }
        
        $limit = $this->buildLimit($limit, $offset);
        if ($limit !== '') {
            $sql = preg_replace('/^SELECT /i', 'SELECT ' . $limit . ' ', $sql, 1);
        }
        
        return $sql;
    }

    /**
     * @param array $unions
     * @param array $params the binding parameters to be populated
     * @return string the UNION clause built from [[Query::$union]].
     */
    public function buildUnion($unions, &$params)
    {
        if (empty($unions)) {
            return '';
        }

        $result = '';

        foreach ($unions as $i => $union) {
            $query = $union['query'];
            if ($query instanceof Query) {
                list($unions[$i]['query'], $params) = $this->build($query, $params);
            }

            $result .= 'UNION ' . ($union['all'] ? 'ALL ' : '') . $unions[$i]['query'] . ' ';
        }

        return trim($result);
    }

    /**
     * @inheritdoc
     */
    public function prepareInsertValues($table, $columns, $params = [])
    {
        $schema = $this->db->getSchema();
        $tableSchema = $schema->getTableSchema($table);
        $columnSchemas = $tableSchema !== null ? $tableSchema->columns : [];
        
        //Empty insert
        if (empty($columns) && !empty($columnSchemas)) {
            $columns = [];
            foreach ($columnSchemas as $columnSchema) {
                if (!$columnSchema->autoIncrement) {
                    $columns[$columnSchema->name] = $columnSchema->defaultValue;
                }
            }
        }

        if (is_array($columns)) {
            foreach ($columns as $name => $value) {
                if ($value instanceof \yii\db\ExpressionInterface) {
                    continue;
                }
                if ($value instanceof \yii\db\PdoValue) {
                    continue;
                }
                if (isset($columnSchemas[$name]) && in_array($columnSchemas[$name]->type, [Schema::TYPE_TEXT, Schema::TYPE_BINARY])) {
                    $columns[$name] = [$value, \PDO::PARAM_LOB];
                }
            }
        }

        return parent::prepareInsertValues($table, $columns, $params);
    }
    
    /**
     * @inheritdoc
     */
    protected function prepareInsertSelectSubQuery($columns, $schema, $params = [])
    {
        /**
         * @see https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-insert.html#fblangref25-dml-insert-select-unstable
         */
        if (!$this->db->supportStableCursor) {
            throw new NotSupportedException('Firebird < 3.0.0 has the "Unstable Cursor" problem');
        }

        return parent::prepareInsertSelectSubQuery($columns, $schema, $params);
    }

    /**
     * @inheritdoc
     */
    public function prepareUpdateSets($table, $columns, $params = [])
    {
        $schema = $this->db->getSchema();
        $tableSchema = $schema->getTableSchema($table);
        $columnSchemas = $tableSchema !== null ? $tableSchema->columns : [];

        foreach ($columns as $name => $value) {
            if ($value instanceof \yii\db\ExpressionInterface) {
                continue;
            }
            if ($value instanceof \yii\db\PdoValue) {
                continue;
            }
            if (isset($columnSchemas[$name]) && in_array($columnSchemas[$name]->type, [Schema::TYPE_TEXT, Schema::TYPE_BINARY])) {
                $columns[$name] = [$value, \PDO::PARAM_LOB];
            }
        }
        return parent::prepareUpdateSets($table, $columns, $params);
    }
    
    public function rawInsert($table, $columns, $values, &$params)
    {
        $schema = $this->db->getSchema();
        if (($tableSchema = $schema->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->columns;
        } else {
            $columnSchemas = [];
        }

        $cs = [];
        $vs = [];
        foreach ($values as $i => $value) {
            if (isset($columns[$i], $columnSchemas[$columns[$i]]) && !is_array($value)) {
                $value = $columnSchemas[$columns[$i]]->dbTypecast($value);
            }
            if (is_string($value)) {
                // Quote the {{table}} to {{@table@}} for insert
                $value = preg_replace('/(\{\{)(%?[\w\-\. ]+%?)(\}\})/', '\1@\2@\3', $value);
                $value = $schema->quoteValue($value);
            } elseif (is_float($value)) {
                // ensure type cast always has . as decimal separator in all locales
                $value = \yii\helpers\StringHelper::floatToString($value);
            } elseif ($value === false) {
                $value = 0;
            } elseif ($value === null) {
                $value = 'NULL';
            } elseif ($value instanceof ExpressionInterface) {
                $value = $this->buildExpression($value, $params);
            }

            if (isset($columns[$i])) {
                $cs[] = $schema->quoteColumnName($columns[$i]);
            }
            $vs[] = $value;
        }

        if (empty($vs)) {
            return 'INSERT INTO ' . $schema->quoteTableName($table)
                    . ' DEFAULT VALUES';
        }

        return 'INSERT INTO ' . $schema->quoteTableName($table)
                . ' (' . implode(', ', $cs) . ') VALUES (' . implode(', ', $vs) . ')';
    }

    /**
     * @inheritdoc
     */
    public function batchInsert($table, $columns, $rows, &$params = [])
    {
        if (empty($rows)) {
            return '';
        }

        $schema = $this->db->getSchema();
        if (($tableSchema = $schema->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->columns;

$columnSchemas is not used, you could remove the assignment.

        } else {
            $columnSchemas = [];

$columnSchemas is not used, you could remove the assignment.

        }

        $values = [];
        foreach ($rows as $row) {
            $values[] = $this->rawInsert($table, $columns, $row, $params) . ';';
        }

        return 'EXECUTE block AS BEGIN ' . implode(' ', $values) . ' END;';
    }
    
    /**
     * {@inheritdoc}
     * @see https://www.firebirdsql.org/refdocs/langrefupd21-update-or-insert.html
     * @see https://www.firebirdsql.org/refdocs/langrefupd21-merge.html
     */
    public function upsert($table, $insertColumns, $updateColumns, &$params)
    {
        /** @var Constraint[] $constraints */
        list($uniqueNames, $insertNames, $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns, $constraints);
        if (empty($uniqueNames)) {
            return 'UPDATE OR ' . $this->insert($table, $insertColumns, $params);
        }

        $onCondition = ['or'];
        $quotedTableName = $this->db->quoteTableName($table);
        foreach ($constraints as $constraint) {
            $constraintCondition = ['and'];
            foreach ($constraint->columnNames as $name) {
                $quotedName = $this->db->quoteColumnName($name);
                $constraintCondition[] = "$quotedTableName.$quotedName=\"EXCLUDED\".$quotedName";
            }
            $onCondition[] = $constraintCondition;
        }
        $on = $this->buildCondition($onCondition, $params);
        list(, $placeholders, $values, $params) = $this->prepareInsertValues($table, $insertColumns, $params);
        if (!empty($placeholders)) {
            /** @var Schema $schema */
            $schema = $this->db->getSchema();
            $tableSchema = $schema->getTableSchema($table);
            $columnSchemas = $tableSchema !== null ? $tableSchema->columns : [];
            
            $usingSelectValues = [];
            foreach ($insertNames as $index => $name) {
                if (isset($columnSchemas[$name])) {
                    $usingSelectValues[$name] = new Expression("CAST({$placeholders[$index]} AS {$columnSchemas[$name]->dbType})");
                }
            }
            $usingSubQuery = (new Query())
                ->select($usingSelectValues)
                ->from('RDB$DATABASE');
            list($usingValues, $params) = $this->build($usingSubQuery, $params);
        }
        $mergeSql = 'MERGE INTO ' . $this->db->quoteTableName($table) . ' '
            . 'USING (' . (isset($usingValues) ? $usingValues : ltrim($values, ' ')) . ') "EXCLUDED" '
            . "ON ($on)";
        $insertValues = [];
        foreach ($insertNames as $name) {
            $quotedName = $this->db->quoteColumnName($name);
            if (strrpos($quotedName, '.') === false) {
                $quotedName = '"EXCLUDED".' . $quotedName;
            }
            $insertValues[] = $quotedName;
        }
        $insertSql = 'INSERT (' . implode(', ', $insertNames) . ')'
            . ' VALUES (' . implode(', ', $insertValues) . ')';
        if ($updateColumns === false) {
            return "$mergeSql WHEN NOT MATCHED THEN $insertSql";
        }

        if ($updateColumns === true) {
            $updateColumns = [];
            foreach ($updateNames as $name) {
                $quotedName = $this->db->quoteColumnName($name);
                if (strrpos($quotedName, '.') === false) {
                    $quotedName = '"EXCLUDED".' . $quotedName;
                }
                $updateColumns[$name] = new Expression($quotedName);
            }
        }
        list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params);
        $updateSql = 'UPDATE SET ' . implode(', ', $updates);
        return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql";
    }

    /**
     * @inheritdoc
     */
    public function renameTable($oldName, $newName)
    {
        throw new \yii\base\NotSupportedException($this->db->getDriverName() . ' does not support rename table.');
    }
    
    /**
     * @inheritdoc
     */
    public function truncateTable($table)
    {
        return 'DELETE FROM ' . $this->db->quoteTableName($table);
    }
    
    /**
     * @inheritdoc
     */
    public function dropColumn($table, $column)
    {
        return 'ALTER TABLE ' . $this->db->quoteTableName($table)
            . ' DROP ' . $this->db->quoteColumnName($column);
    }
    
    /**
     * @inheritdoc
     */
    public function renameColumn($table, $oldName, $newName)
    {
        return 'ALTER TABLE ' . $this->db->quoteTableName($table)
            . ' ALTER ' . $this->db->quoteColumnName($oldName)
            . ' TO ' . $this->db->quoteColumnName($newName);
    }
    
    /**
     * @inheritdoc
     */
    public function alterColumn($table, $column, $type)
    {
        $schema = $this->db->getSchema();
        $tableSchema = $schema->getTableSchema($table);
        $columnSchema = $tableSchema->getColumn($column);
        
        $allowNullNewType = !preg_match('/not +null/i', $type);
        
        $type = preg_replace('/ +(not)? *null/i', '', $type);
        
        $hasType = false;
        
        $matches = [];
        if (isset($this->typeMap[$type])) {
            $hasType = true;
        } elseif (preg_match('/^(\w+)[\( ]/', $type, $matches)) {
            if (isset($this->typeMap[$matches[1]])) {
                $hasType = true;
            }
        }
        
        $baseSql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
        . ' ALTER ' . $this->db->quoteColumnName($column)
        . (($hasType) ? ' TYPE ' : ' ') .  $this->getColumnType($type);
        
        if (version_compare($this->db->firebird_version, '3.0.0', '>=')) {
            $nullSql = false;
            
            if ($columnSchema->allowNull != $allowNullNewType) {
                $nullSql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
                . ' ALTER ' . $this->db->quoteColumnName($column)
                . ($allowNullNewType ? ' DROP' : ' SET')
                . ' NOT NULL';
            }

            $sql = 'EXECUTE BLOCK AS BEGIN'
                . ' EXECUTE STATEMENT ' . $this->db->quoteValue($baseSql) . ';';
            
            /**
             * In any case (whichever option you choose), make sure that the column doesn't have any NULLs.
             * Firebird will not check it for you. Later when you backup the database, everything is fine,
             * but restore will fail as the NOT NULL column has NULLs in it. To be safe, each time you change from NULL to NOT NULL.
             */
            if (!$allowNullNewType) {
                $sql .= ' UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . $this->db->quoteColumnName($column) . ' = 0'
                    . ' WHERE ' . $this->db->quoteColumnName($column) . ' IS NULL;';
            }

            if ($nullSql) {
                $sql .= ' EXECUTE STATEMENT ' . $this->db->quoteValue($nullSql) . ';';
            }

            $sql .= ' END';
            return $sql;
        }

        if ($columnSchema->allowNull == $allowNullNewType) {
            return $baseSql;
        } else {
            $sql = 'EXECUTE BLOCK AS BEGIN'
                . ' EXECUTE STATEMENT ' . $this->db->quoteValue($baseSql) . ';'
                . ' UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = ' . ($allowNullNewType ? 'NULL' : '1')
                . ' WHERE UPPER(RDB$FIELD_NAME) = UPPER(\'' . $column . '\') AND UPPER(RDB$RELATION_NAME) = UPPER(\'' . $table . '\');';
            /**
             * In any case (whichever option you choose), make sure that the column doesn't have any NULLs.
             * Firebird will not check it for you. Later when you backup the database, everything is fine,
             * but restore will fail as the NOT NULL column has NULLs in it. To be safe, each time you change from NULL to NOT NULL.
             */
            if (!$allowNullNewType) {
                $sql .= ' UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . $this->db->quoteColumnName($column) . ' = 0'
                    . ' WHERE ' . $this->db->quoteColumnName($column) . ' IS NULL;';
            }
            $sql .= ' END';
            return $sql;
        }
    }
    
    /**
     * @inheritdoc
     */
    public function dropIndex($name, $table)
    {
        return 'DROP INDEX ' . $this->db->quoteTableName($name);
    }
    
    /**
     * {@inheritdoc}
     */
    public function addDefaultValue($name, $table, $column, $value)
    {
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ALTER COLUMN '
            . $this->db->quoteColumnName($column) . ' SET DEFAULT ' . $this->db->quoteValue($value);
    }

    /**
     * {@inheritdoc}
     */
    public function dropDefaultValue($name, $table)
    {
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ALTER COLUMN '
            . $this->db->quoteColumnName($name) . ' DROP DEFAULT';
    }

    /**
     * @inheritdoc
     */
    public function resetSequence($table, $value = null)
    {
        $tableSchema = $this->db->getTableSchema($table);
        if ($tableSchema === null) {
            throw new InvalidParamException("Table not found: $table");
        }
        if ($tableSchema->sequenceName === null) {
            throw new InvalidParamException("There is not sequence associated with table '$table'.");
        }

        if ($value !== null) {
            $value = (int) $value;
        } else {
            // use master connection to get the biggest PK value
            $value = $this->db->useMaster(function (Connection $db) use ($tableSchema) {
                $key = false;
                foreach ($tableSchema->primaryKey as $name) {
                    if ($tableSchema->columns[$name]->autoIncrement) {
                        $key = $name;
                        break;
                    }
                }
                if ($key === false) {
                    return 0;
                }
                return $db->createCommand("SELECT MAX({$this->db->quoteColumnName($key)}) FROM {$this->db->quoteTableName($tableSchema->name)}")->queryScalar();
            }) + 1;
        }

        return "ALTER SEQUENCE {$this->db->quoteColumnName($tableSchema->sequenceName)} RESTART WITH $value";
    }
    
    /**
     * @inheritdoc
     */
    public function createTable($table, $columns, $options = null)
    {
        $sql = parent::createTable($table, $columns, $options);
        
        if ($this->db->supportColumnIdentity) {
            return $sql;
        }

        foreach ($columns as $name => $type) {
            if (!is_string($name)) {
                continue;
            }
            
            if (strpos($type, Schema::TYPE_PK) === 0 || strpos($type, Schema::TYPE_BIGPK) === 0) {
                $sqlTrigger = <<<SQLTRIGGER
CREATE TRIGGER tr_{$table}_{$name} FOR {$this->db->quoteTableName($table)}
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    if (NEW.{$this->db->quoteColumnName($name)} is NULL) then NEW.{$this->db->quoteColumnName($name)} = NEXT VALUE FOR seq_{$table}_{$name};
END
SQLTRIGGER;
                
                $sqlBlock = <<<SQL
EXECUTE block AS
BEGIN
    EXECUTE STATEMENT {$this->db->quoteValue($sql)};
    EXECUTE STATEMENT {$this->db->quoteValue("CREATE SEQUENCE seq_{$table}_{$name}")};
    EXECUTE STATEMENT {$this->db->quoteValue($sqlTrigger)};
END;
SQL;

                return $sqlBlock;
            }
        }
        
        return $sql;
    }
    
    /**
     * @inheritdoc
     */
    public function dropTable($table)
    {
        $sql = parent::dropTable($table);
        
        $tableSchema = $this->db->getTableSchema($table);
        if ($tableSchema === null || $tableSchema->sequenceName === null) {
            return $sql;
        }
        
        /**
         * Not drop sequence for sequence "GENERATED BY DEFAULT AS IDENTITY"
         */
        if ($this->db->supportColumnIdentity) {
            $sqlUserSquence = 'SELECT 1 FROM RDB$GENERATORS
                WHERE RDB$SYSTEM_FLAG = 0 AND RDB$GENERATOR_NAME = :name';
            
            $is_user_sequence = $this->db->createCommand($sqlUserSquence, [':name' => $tableSchema->sequenceName])->queryScalar();
            
            if (!$is_user_sequence) {
                return $sql;
            }
        }
        
        $sqlBlock = <<<SQL
EXECUTE block AS
BEGIN
    EXECUTE STATEMENT {$this->db->quoteValue($sql)};
    EXECUTE STATEMENT {$this->db->quoteValue("DROP SEQUENCE {$tableSchema->sequenceName}")};
END;
SQL;
                return $sqlBlock;
    }

    /**
     * Creates a SELECT EXISTS() SQL statement.
     * @param string $rawSql the subquery in a raw form to select from.
     * @return string the SELECT EXISTS() SQL statement.
     *
     * @since 2.0.8
     */
    public function selectExists($rawSql)
    {
        return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END FROM RDB$DATABASE';
    }
}