QueryBuilder::dropTable() - Code Metrics - Inspection of "Added support to column identity in Firebird 3;" - edgardmessias/yii2-firebird - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 4a233e...d91d64 )

by Edgard

created 2017-02-09 13:55 UTC

QueryBuilder::dropTable() B

↳ Parent: QueryBuilder

Complexity

Conditions	5
Paths	4

Size

Total Lines	32
Code Lines	16

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	2
CRAP Score	5

Importance

Changes

Metric	Value
c	0
b	0
f	0
dl	0
loc	32
ccs	2
cts	2
cp	1
rs	8.439
cc	5
eloc	16
nc	4
nop	1
crap	5

<?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 <[email protected]>
 * @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 (version_compare($this->db->firebird_version, '3.0.0', '>=')) {
            $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();
    }

    /**
     * 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);
    }

    /**
     * @inheritdoc
     */
    protected function buildCompositeInCondition($operator, $columns, $values, &$params)
    {
        $quotedColumns = [];
        foreach ($columns as $i => $column) {
            $quotedColumns[$i] = strpos($column, '(') === false ? $this->db->quoteColumnName($column) : $column;
        }
        $vss = [];
        foreach ($values as $value) {
            $vs = [];
            foreach ($columns as $i => $column) {
                if (isset($value[$column])) {
                    $phName = self::PARAM_PREFIX . count($params);
                    $params[$phName] = $value[$column];
                    $vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' = ' : ' != ') . $phName;
                } else {
                    $vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' IS' : ' IS NOT') . ' NULL';
                }
            }
            $vss[] = '(' . implode($operator === 'IN' ? ' AND ' : ' OR ', $vs) . ')';
        }
        return '(' . implode($operator === 'IN' ? ' OR ' : ' AND ', $vss) . ')';
    }

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

        $orderBy = $this->buildOrderBy($orderBy);
        if ($orderBy !== '') {
            $sql .= $this->separator . $orderBy;
        }

        $limit = $limit !== null ? intval($limit) : -1;
        $offset = $offset !== null ? intval($offset) : -1;
        // If ignoring both params then do nothing
        if ($offset < 0 && $limit < 0) {
            return $sql;
        }
        // If we are ignoring limit then return full result set starting
        // from $offset. In Firebird this can only be done with SKIP
        if ($offset >= 0 && $limit < 0) {
            $count = 1; //Only do it once
            $sql = preg_replace('/^SELECT /i', 'SELECT SKIP ' . (int) $offset . ' ', $sql, $count);
            return $sql;
        }
        // If we are ignoring $offset then return $limit rows.
        // ie, return the first $limit rows in the set.
        if ($offset < 0 && $limit >= 0) {
            $count = 1; //Only do it once
            $sql = preg_replace('/^SELECT /i', 'SELECT FIRST ' . (int) $limit . ' ', $sql, $count);
            return $sql;
        }
        // Otherwise apply the params and return the amended sql.
        if ($offset >= 0 && $limit >= 0) {
            $count = 1; //Only do it once
            $sql = preg_replace('/^SELECT /i', 'SELECT FIRST ' . (int) $limit . ' SKIP ' . (int) $offset . ' ', $sql, $count);
            return $sql;
        }
        // If we have fallen through the cracks then just pass
        // the sql back.
        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);
    }

    /**
     *
     * @param Expression $value
     * @return Expression
     */
    protected function convertExpression($value)
    {
        if (!($value instanceof Expression)) {
            return $value;
        }
        
        $expressionMap = [
            "strftime('%Y')" => "EXTRACT(YEAR FROM TIMESTAMP 'now')"
        ];
        
        if (isset($expressionMap[$value->expression])) {
            return new Expression($expressionMap[$value->expression]);
        }
        return $value;
    }

    /**
     * @inheritdoc
     */
    public function insert($table, $columns, &$params)
    {
        $schema = $this->db->getSchema();
        if (($tableSchema = $schema->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->columns;
        } else {
            $columnSchemas = [];
        }
        
        //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 Expression) {
                    $columns[$name] = $this->convertExpression($value);
                } elseif (isset($columnSchemas[$name]) && in_array($columnSchemas[$name]->type, [Schema::TYPE_TEXT, Schema::TYPE_BINARY])) {
                    $columns[$name] = [$value, \PDO::PARAM_LOB];
                }
            }
        }

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

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

    /**
     * @inheritdoc
     */
    public function update($table, $columns, $condition, &$params)
    {
        $schema = $this->db->getSchema();
        if (($tableSchema = $schema->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->columns;
        } else {
            $columnSchemas = [];
        }
        foreach ($columns as $name => $value) {
            if ($value instanceof Expression) {
                $columns[$name] = $this->convertExpression($value);
            } elseif (isset($columnSchemas[$name]) && in_array($columnSchemas[$name]->type, [Schema::TYPE_TEXT, Schema::TYPE_BINARY])) {
                $columns[$name] = [$value, \PDO::PARAM_LOB];
            }
        }
        return parent::update($table, $columns, $condition, $params);
    }

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

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

        $values = [];
        foreach ($rows as $row) {
            $vs = [];
            foreach ($row as $i => $value) {
                if (isset($columns[$i], $columnSchemas[$columns[$i]]) && !is_array($value)) {
                    $value = $columnSchemas[$columns[$i]]->dbTypecast($value);
                }
                if (is_string($value)) {
                    $value = $schema->quoteValue($value);
                } elseif ($value === false) {
                    $value = 0;
                } elseif ($value === null) {
                    $value = 'NULL';
                }
                $vs[] = $value;
            }
            $values[] = 'INSERT INTO ' . $schema->quoteTableName($table)
                    . ' (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $vs) . ');';
        }

        foreach ($columns as $i => $name) {
            $columns[$i] = $schema->quoteColumnName($name);
        }

        return 'EXECUTE block AS BEGIN ' . implode(' ', $values) . ' END;';
    }
    
    /**
     * @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) {
''   == false // true
''   == null  // true
'ab' == false // false
'ab' == null  // false

// It is often better to use strict comparison
'' === false // false
'' === null  // false
                $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 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 (version_compare($this->db->firebird_version, '3.0.0', '>=')) {
            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 (version_compare($this->db->firebird_version, '3.0.0', '>=')) {
            $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';
    }
}


1		<?php
2
3		/**
4		* @link http://www.yiiframework.com/
5		* @copyright Copyright (c) 2008 Yii Software LLC
6		* @license http://www.yiiframework.com/license/
7		*/
8
9		namespace edgardmessias\db\firebird;
10
11		use yii\base\InvalidParamException;
12		use yii\base\NotSupportedException;
13		use yii\db\Expression;
14		use yii\db\Query;
15
16		/**
17		*
18		* @author Edgard Lorraine Messias <[email protected]>
19		* @since 2.0
20		*/
21		class QueryBuilder extends \yii\db\QueryBuilder
22		{
23
24		/**
25		* @var array mapping from abstract column types (keys) to physical column types (values).
26		*/
27		public $typeMap = [
28		Schema::TYPE_PK => 'integer NOT NULL PRIMARY KEY',
29		Schema::TYPE_UPK => 'integer NOT NULL PRIMARY KEY',
30		Schema::TYPE_BIGPK => 'bigint NOT NULL PRIMARY KEY',
31		Schema::TYPE_UBIGPK => 'bigint NOT NULL PRIMARY KEY',
32		Schema::TYPE_CHAR => 'char(1)',
33		Schema::TYPE_STRING => 'varchar(255)',
34		Schema::TYPE_TEXT => 'blob sub_type text',
35		Schema::TYPE_SMALLINT => 'smallint',
36		Schema::TYPE_INTEGER => 'integer',
37		Schema::TYPE_BIGINT => 'bigint',
38		Schema::TYPE_FLOAT => 'float',
39		Schema::TYPE_DOUBLE => 'double precision',
40		Schema::TYPE_DECIMAL => 'numeric(10,0)',
41		Schema::TYPE_DATETIME => 'timestamp',
42		Schema::TYPE_TIMESTAMP => 'timestamp',
43		Schema::TYPE_TIME => 'time',
44		Schema::TYPE_DATE => 'date',
45		Schema::TYPE_BINARY => 'blob',
46		Schema::TYPE_BOOLEAN => 'smallint',
47		Schema::TYPE_MONEY => 'numeric(18,4)',
48		];
49
50		public function init()
51		{
52		if (version_compare($this->db->firebird_version, '3.0.0', '>=')) {
53		$this->typeMap[Schema::TYPE_PK] = 'integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY';
54		$this->typeMap[Schema::TYPE_UPK] = 'integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY';
55		$this->typeMap[Schema::TYPE_BIGPK] = 'bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY';
56		$this->typeMap[Schema::TYPE_UBIGPK] = 'bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY';
57		$this->typeMap[Schema::TYPE_BOOLEAN] = 'boolean';
58		}
59	181
60		parent::init();
61	181	}
62
63	181	/**
64		* Generates a SELECT SQL statement from a [[Query]] object.
65		* @param Query $query the [[Query]] object from which the SQL statement will be generated.
66	181	* @param array $params the parameters to be bound to the generated SQL statement. These parameters will
67	181	* be included in the result with the additional parameters generated during the query building process.
68	181	* @return array the generated SQL statement (the first array element) and the corresponding
69	181	* parameters to be bound to the SQL statement (the second array element). The parameters returned
70	181	* include those provided in `$params`.
71	181	*/
72		public function build($query, $params = [])
73		{
74	181	$query = $query->prepare($this);
75	181
76		$params = empty($params) ? $query->params : array_merge($params, $query->params);
77	181
78	34	$clauses = [
79	34	$this->buildSelect($query->select, $params, $query->distinct, $query->selectOption),
80	34	$this->buildFrom($query->from, $params),
81		$this->buildJoin($query->join, $params),
82		$this->buildWhere($query->where, $params),
83		$this->buildGroupBy($query->groupBy),
84	181	$this->buildHaving($query->having, $params),
85	2	];
86	2
87	2	$sql = implode($this->separator, array_filter($clauses));
88		$sql = $this->buildOrderByAndLimit($sql, $query->orderBy, $query->limit, $query->offset);
89
90		if (!empty($query->orderBy)) {
91		foreach ($query->orderBy as $expression) {
92	181	if ($expression instanceof Expression) {
93	181	$params = array_merge($params, $expression->params);
94	2	}
95		}
96		}
97	181	if (!empty($query->groupBy)) {
98		foreach ($query->groupBy as $expression) {
99		if ($expression instanceof Expression) {
100		$params = array_merge($params, $expression->params);
101		}
102		}
103	181	}
104
105	181	$union = $this->buildUnion($query->union, $params);
106	53	if ($union !== '') {
107	53	$sql = "$sql{$this->separator}$union";
108	3	}
109
110	52	return [$sql, $params];
111	52	}
112	21
113	21	/**
114		* @inheritdoc
115	52	*/
116		public function buildSelect($columns, &$params, $distinct = false, $selectOption = null)
117		{
118		if (is_array($columns)) {
119		foreach ($columns as $i => $column) {
120	181	if (!is_string($column)) {
121		continue;
122		}
123		$matches = [];
124		if (preg_match('/^(COUNT\|SUM\|AVG\|MIN\|MAX)\([\{\[]{0,2}(\w+\|\*)[\}\]]{0,2}\)$/i', $column, $matches)) {
125		$function = $matches[1];
126	5	$alias = $matches[2] != '*' ? $matches[2] : 'ALL';
127
128	5	$columns[$i] = "{$column} AS {$function}_{$alias}";
129	5	}
130	5	}
131		}
132	5
133	5	return parent::buildSelect($columns, $params, $distinct, $selectOption);
134	5	}
135	5
136	5	/**
137	5	* @inheritdoc
138	5	*/
139	5	protected function buildCompositeInCondition($operator, $columns, $values, &$params)
140		{
141	5	$quotedColumns = [];
142		foreach ($columns as $i => $column) {
143		$quotedColumns[$i] = strpos($column, '(') === false ? $this->db->quoteColumnName($column) : $column;
144	5	}
145		$vss = [];
146	5	foreach ($values as $value) {
147		$vs = [];
148		foreach ($columns as $i => $column) {
149		if (isset($value[$column])) {
150		$phName = self::PARAM_PREFIX . count($params);
151		$params[$phName] = $value[$column];
152	181	$vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' = ' : ' != ') . $phName;
153		} else {
154		$vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' IS' : ' IS NOT') . ' NULL';
155	181	}
156	181	}
157	34	$vss[] = '(' . implode($operator === 'IN' ? ' AND ' : ' OR ', $vs) . ')';
158		}
159		return '(' . implode($operator === 'IN' ? ' OR ' : ' AND ', $vss) . ')';
160	181	}
161	181
162		/**
163	181	* @inheritdoc
164	180	*/
165		public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
166		{
167
168	3	$orderBy = $this->buildOrderBy($orderBy);
169	2	if ($orderBy !== '') {
170	2	$sql .= $this->separator . $orderBy;
171	2	}
172
173		$limit = $limit !== null ? intval($limit) : -1;
174		$offset = $offset !== null ? intval($offset) : -1;
175	2	// If ignoring both params then do nothing
176	2	if ($offset < 0 && $limit < 0) {
177	2	return $sql;
178	2	}
179		// If we are ignoring limit then return full result set starting
180		// from $offset. In Firebird this can only be done with SKIP
181	1	if ($offset >= 0 && $limit < 0) {
182	1	$count = 1; //Only do it once
183	1	$sql = preg_replace('/^SELECT /i', 'SELECT SKIP ' . (int) $offset . ' ', $sql, $count);
184	1	return $sql;
185		}
186		// If we are ignoring $offset then return $limit rows.
187		// ie, return the first $limit rows in the set.
188		if ($offset < 0 && $limit >= 0) {
189		$count = 1; //Only do it once
190		$sql = preg_replace('/^SELECT /i', 'SELECT FIRST ' . (int) $limit . ' ', $sql, $count);
191		return $sql;
192		}
193		// Otherwise apply the params and return the amended sql.
194		if ($offset >= 0 && $limit >= 0) {
195		$count = 1; //Only do it once
196	181	$sql = preg_replace('/^SELECT /i', 'SELECT FIRST ' . (int) $limit . ' SKIP ' . (int) $offset . ' ', $sql, $count);
197		return $sql;
198	181	}
199	181	// If we have fallen through the cracks then just pass
200		// the sql back.
201		return $sql;
202	2	}
203
204	2	/**
205	2	* @param array $unions
206	2	* @param array $params the binding parameters to be populated
207	2	* @return string the UNION clause built from [[Query::$union]].
208		*/
209		public function buildUnion($unions, &$params)
210	2	{
211		if (empty($unions)) {
212		return '';
213	2	}
214
215		$result = '';
216
217		foreach ($unions as $i => $union) {
218		$query = $union['query'];
219		if ($query instanceof Query) {
220		list($unions[$i]['query'], $params) = $this->build($query, $params);
221	3	}
222
223	3	$result .= 'UNION ' . ($union['all'] ? 'ALL ' : '') . $unions[$i]['query'] . ' ';
224		}
225
226		return trim($result);
227		}
228
229	3	/**
230		*
231	3	* @param Expression $value
232		* @return Expression
233		*/
234	3	protected function convertExpression($value)
235		{
236		if (!($value instanceof Expression)) {
237		return $value;
238		}
239
240	23	$expressionMap = [
241		"strftime('%Y')" => "EXTRACT(YEAR FROM TIMESTAMP 'now')"
242	23	];
243	23
244	23	if (isset($expressionMap[$value->expression])) {
245		return new Expression($expressionMap[$value->expression]);
246		}
247		return $value;
248		}
249
250	23	/**
251	1	* @inheritdoc
252	1	*/
253	1	public function insert($table, $columns, &$params)
254	1	{
255		$schema = $this->db->getSchema();
256		if (($tableSchema = $schema->getTableSchema($table)) !== null) {
257		$columnSchemas = $tableSchema->columns;
258		} else {
259	23	$columnSchemas = [];
260	23	}
261	23
262	1	//Empty insert
263	23	if (empty($columns) && !empty($columnSchemas)) {
264	23	$columns = [];
265		foreach ($columnSchemas as $columnSchema) {
266		if (!$columnSchema->autoIncrement) {
267		$columns[$columnSchema->name] = $columnSchema->defaultValue;
268		}
269	23	}
270		}
271
272		if (is_array($columns)) {
273		foreach ($columns as $name => $value) {
274		if ($value instanceof Expression) {
275		$columns[$name] = $this->convertExpression($value);
276		} elseif (isset($columnSchemas[$name]) && in_array($columnSchemas[$name]->type, [Schema::TYPE_TEXT, Schema::TYPE_BINARY])) {
277		$columns[$name] = [$value, \PDO::PARAM_LOB];
278		}
279		}
280		}
281
282		return parent::insert($table, $columns, $params);
283		}
284
285		/**
286		* @inheritdoc
287		*/
288		protected function prepareInsertSelectSubQuery($columns, $schema)
289		{
290	13	/**
291		* @see https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-insert.html#fblangref25-dml-insert-select-unstable
292	13	*/
293	13	if (version_compare($this->db->firebird_version, '3.0.0', '<')) {
294	13	throw new NotSupportedException('Firebird < 3.0.0 has the "Unstable Cursor" problem');
295		}
296
297		return parent::prepareInsertSelectSubQuery($columns, $schema);
298	13	}
299	13
300	2	/**
301	11	* @inheritdoc
302	13	*/
303		public function update($table, $columns, $condition, &$params)
304		{
305	13	$schema = $this->db->getSchema();
306		if (($tableSchema = $schema->getTableSchema($table)) !== null) {
307		$columnSchemas = $tableSchema->columns;
308		} else {
309		$columnSchemas = [];
310		}
311	8	foreach ($columns as $name => $value) {
312		if ($value instanceof Expression) {
313	8	$columns[$name] = $this->convertExpression($value);
314	2	} elseif (isset($columnSchemas[$name]) && in_array($columnSchemas[$name]->type, [Schema::TYPE_TEXT, Schema::TYPE_BINARY])) {
315		$columns[$name] = [$value, \PDO::PARAM_LOB];
316		}
317	7	}
318	7	return parent::update($table, $columns, $condition, $params);
319	7	}
320
321		/**
322		* @inheritdoc
323		*/
324	7	public function batchInsert($table, $columns, $rows)
325	7	{
326	7	if (empty($rows)) {
327	7	return '';
328	7	}
329	4
330		$schema = $this->db->getSchema();
331	7	if (($tableSchema = $schema->getTableSchema($table)) !== null) {
332	4	$columnSchemas = $tableSchema->columns;
333	4	} else {
334	1	$columnSchemas = [];
335	4	}
336	3
337		$values = [];
338	7	foreach ($rows as $row) {
339		$vs = [];
340	7	foreach ($row as $i => $value) {
341	7	if (isset($columns[$i], $columnSchemas[$columns[$i]]) && !is_array($value)) {
342		$value = $columnSchemas[$columns[$i]]->dbTypecast($value);
343		}
344	7	if (is_string($value)) {
345	6	$value = $schema->quoteValue($value);
346		} elseif ($value === false) {
347		$value = 0;
348	7	} elseif ($value === null) {
349		$value = 'NULL';
350		}
351		$vs[] = $value;
352		}
353		$values[] = 'INSERT INTO ' . $schema->quoteTableName($table)
354	1	. ' (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $vs) . ');';
355		}
356	1
357		foreach ($columns as $i => $name) {
358		$columns[$i] = $schema->quoteColumnName($name);
359		}
360
361		return 'EXECUTE block AS BEGIN ' . implode(' ', $values) . ' END;';
362	2	}
363
364	2	/**
365		* @inheritdoc
366		*/
367		public function renameTable($oldName, $newName)
368		{
369		throw new \yii\base\NotSupportedException($this->db->getDriverName() . ' does not support rename table.');
370	1	}
371
372	1	/**
373	1	* @inheritdoc
374		*/
375		public function truncateTable($table)
376		{
377		return 'DELETE FROM ' . $this->db->quoteTableName($table);
378		}
379	1
380		/**
381	1	* @inheritdoc
382	1	*/
383	1	public function dropColumn($table, $column)
384		{
385		return 'ALTER TABLE ' . $this->db->quoteTableName($table)
386		. ' DROP ' . $this->db->quoteColumnName($column);
387		}
388
389	3	/**
390		* @inheritdoc
391	3	*/
392	3	public function renameColumn($table, $oldName, $newName)
393	3	{
394		return 'ALTER TABLE ' . $this->db->quoteTableName($table)
395	3	. ' ALTER ' . $this->db->quoteColumnName($oldName)
396		. ' TO ' . $this->db->quoteColumnName($newName);
397	3	}
398
399	3	/**
400		* @inheritdoc
401	3	*/
402	3	public function alterColumn($table, $column, $type)
403	2	{
404	2	$schema = $this->db->getSchema();
405	2	$tableSchema = $schema->getTableSchema($table);
406	2	$columnSchema = $tableSchema->getColumn($column);
407
408		$allowNullNewType = !preg_match('/not +null/i', $type);
409
410	3	$type = preg_replace('/ +(not)? *null/i', '', $type);
411	3
412	3	$hasType = false;
413
414	3	$matches = [];
415		if (isset($this->typeMap[$type])) {
416		$hasType = true;
417		} elseif (preg_match('/^(\w+)[\( ]/', $type, $matches)) {
418		if (isset($this->typeMap[$matches[1]])) {
419		$hasType = true;
420		}
421		}
422
423		$baseSql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
424		. ' ALTER ' . $this->db->quoteColumnName($column)
425		. (($hasType) ? ' TYPE ' : ' ') . $this->getColumnType($type);
426
427		if (version_compare($this->db->firebird_version, '3.0.0', '>=')) {
428		$nullSql = false;
429
430		if ($columnSchema->allowNull != $allowNullNewType) {
431		$nullSql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
432		. ' ALTER ' . $this->db->quoteColumnName($column)
433		. ($allowNullNewType ? ' DROP' : ' SET')
434		. ' NOT NULL';
435		}
436
437		$sql = 'EXECUTE BLOCK AS BEGIN'
438		. ' EXECUTE STATEMENT ' . $this->db->quoteValue($baseSql) . ';';
439
440		/**
441		* In any case (whichever option you choose), make sure that the column doesn't have any NULLs.
442		* Firebird will not check it for you. Later when you backup the database, everything is fine,
443		* 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.
444		*/
445	3	if (!$allowNullNewType) {
446	2	$sql .= ' UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . $this->db->quoteColumnName($column) . ' = 0'
447		. ' WHERE ' . $this->db->quoteColumnName($column) . ' IS NULL;';
448		}
449	2
450	2	if ($nullSql) {
		0 ignored issues – show Bug Best Practice introduced 2017-02-06 17:46 UTC by Report Bug Copy Issue Report The expression `$nullSql` of type `string\|false` is loosely compared to `true`; this is ambiguous if the string can be empty. You might want to explicitly use `!== false` instead. In PHP, under loose comparison (like `==`, or `!=`, or `switch` conditions), values of different types might be equal. For `string` values, the empty string `''` is a special case, in particular the following results might be unexpected: '' == false // true '' == null // true 'ab' == false // false 'ab' == null // false // It is often better to use strict comparison '' === false // false '' === null // false Loading history...
451	2	$sql .= ' EXECUTE STATEMENT ' . $this->db->quoteValue($nullSql) . ';';
452		}
453
454		$sql .= ' END';
455		return $sql;
456		}
457	2
458	2	if ($columnSchema->allowNull == $allowNullNewType) {
459	2	return $baseSql;
460		} else {
461	2	$sql = 'EXECUTE BLOCK AS BEGIN'
462	2	. ' EXECUTE STATEMENT ' . $this->db->quoteValue($baseSql) . ';'
463		. ' UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = ' . ($allowNullNewType ? 'NULL' : '1')
464		. ' WHERE UPPER(RDB$FIELD_NAME) = UPPER(\'' . $column . '\') AND UPPER(RDB$RELATION_NAME) = UPPER(\'' . $table . '\');';
465		/**
466		* In any case (whichever option you choose), make sure that the column doesn't have any NULLs.
467		* Firebird will not check it for you. Later when you backup the database, everything is fine,
468		* 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.
469	1	*/
470		if (!$allowNullNewType) {
471	1	$sql .= ' UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . $this->db->quoteColumnName($column) . ' = 0'
472		. ' WHERE ' . $this->db->quoteColumnName($column) . ' IS NULL;';
473		}
474		$sql .= ' END';
475		return $sql;
476		}
477	1	}
478
479	1	/**
480	1	* @inheritdoc
481		*/
482		public function dropIndex($name, $table)
483	1	{
484		return 'DROP INDEX ' . $this->db->quoteTableName($name);
485		}
486
487	1	/**
488	1	* @inheritdoc
489		*/
490		public function resetSequence($table, $value = null)
491	1	{
492	1	$tableSchema = $this->db->getTableSchema($table);
493	1	if ($tableSchema === null) {
494	1	throw new InvalidParamException("Table not found: $table");
495	1	}
496	1	if ($tableSchema->sequenceName === null) {
497		throw new InvalidParamException("There is not sequence associated with table '$table'.");
498		}
499	1
500		if ($value !== null) {
501		$value = (int) $value;
502	1	} else {
503	1	// use master connection to get the biggest PK value
504		$value = $this->db->useMaster(function (Connection $db) use ($tableSchema) {
505		$key = false;
506	1	foreach ($tableSchema->primaryKey as $name) {
507		if ($tableSchema->columns[$name]->autoIncrement) {
508		$key = $name;
509		break;
510		}
511		}
512	5	if ($key === false) {
513		return 0;
514	5	}
515		return $db->createCommand("SELECT MAX({$this->db->quoteColumnName($key)}) FROM {$this->db->quoteTableName($tableSchema->name)}")->queryScalar();
516	5	}) + 1;
517	5	}
518
519		return "ALTER SEQUENCE {$this->db->quoteColumnName($tableSchema->sequenceName)} RESTART WITH $value";
520		}
521	5
522		/**
523	4	* @inheritdoc
524		*/
525		public function createTable($table, $columns, $options = null)
526		{
527	4	$sql = parent::createTable($table, $columns, $options);
528	4
529		if (version_compare($this->db->firebird_version, '3.0.0', '>=')) {
530		return $sql;
531		}
532
533		foreach ($columns as $name => $type) {
534	4	if (!is_string($name)) {
535	4	continue;
536	4	}
537	4
538		if (strpos($type, Schema::TYPE_PK) === 0 \|\| strpos($type, Schema::TYPE_BIGPK) === 0) {
539		$sqlTrigger = <<<SQLTRIGGER
540	5	CREATE TRIGGER tr_{$table}_{$name} FOR {$this->db->quoteTableName($table)}
541		ACTIVE BEFORE INSERT POSITION 0
542		AS
543		BEGIN
544	1	if (NEW.{$this->db->quoteColumnName($name)} is NULL) then NEW.{$this->db->quoteColumnName($name)} = NEXT VALUE FOR seq_{$table}_{$name};
545		END
546		SQLTRIGGER;
547
548		$sqlBlock = <<<SQL
549		EXECUTE block AS
550	3	BEGIN
551		EXECUTE STATEMENT {$this->db->quoteValue($sql)};
552	3	EXECUTE STATEMENT {$this->db->quoteValue("CREATE SEQUENCE seq_{$table}_{$name}")};
553		EXECUTE STATEMENT {$this->db->quoteValue($sqlTrigger)};
554	3	END;
555	3	SQL;
556	1
557		return $sqlBlock;
558		}
559		}
560
561		return $sql;
562	2	}
563	2
564	2	/**
565		* @inheritdoc
566	2	*/
567		public function dropTable($table)
568		{
569		$sql = parent::dropTable($table);
570
571		$tableSchema = $this->db->getTableSchema($table);
572		if ($tableSchema === null \|\| $tableSchema->sequenceName === null) {
573		return $sql;
574		}
575
576	3	/**
577		* Not drop sequence for sequence "GENERATED BY DEFAULT AS IDENTITY"
578	3	*/
579		if (version_compare($this->db->firebird_version, '3.0.0', '>=')) {
580		$sqlUserSquence = 'SELECT 1 FROM RDB$GENERATORS
581		WHERE RDB$SYSTEM_FLAG = 0 AND RDB$GENERATOR_NAME = :name';
582
583		$is_user_sequence = $this->db->createCommand($sqlUserSquence, [':name' => $tableSchema->sequenceName])->queryScalar();
584
585		if (!$is_user_sequence) {
586		return $sql;
587		}
588		}
589
590		$sqlBlock = <<<SQL
591		EXECUTE block AS
592		BEGIN
593		EXECUTE STATEMENT {$this->db->quoteValue($sql)};
594		EXECUTE STATEMENT {$this->db->quoteValue("DROP SEQUENCE {$tableSchema->sequenceName}")};
595		END;
596		SQL;
597		return $sqlBlock;
598		}
599
600		/**
601		* Creates a SELECT EXISTS() SQL statement.
602		* @param string $rawSql the subquery in a raw form to select from.
603		* @return string the SELECT EXISTS() SQL statement.
604		*
605		* @since 2.0.8
606		*/
607		public function selectExists($rawSql)
608		{
609		return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END FROM RDB$DATABASE';
610		}
611		}
612

edgardmessias / yii2-firebird

Push — master ( 4a233e...d91d64 )

QueryBuilder::dropTable() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like