QueryBuilder::insert() - Code Metrics - Inspection of "Added timeout for tests;" - edgardmessias/yii2-firebird - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( d61641...0ffc57 )

by Edgard

created 2017-02-07 17:38 UTC

QueryBuilder::insert() C

↳ Parent: QueryBuilder

Complexity

Conditions	11
Paths	8

Size

Total Lines	31
Code Lines	18

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	16
CRAP Score	11.0245

Importance

Changes

Metric	Value
dl	0
loc	31
ccs	16
cts	17
cp	0.9412
rs	5.2653
c	0
b	0
f	0
cc	11
eloc	18
nc	8
nop	3
crap	11.0245

How to fix Complexity

<?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)',
    ];

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

edgardmessias / yii2-firebird

Push — master ( d61641...0ffc57 )

QueryBuilder::insert() C

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Complexity

Long Method

Duplication Side-by-Side

Filter issues like