QueryBuilder::buildOrderByAndLimit() - Code Metrics - Inspection of "Fixed Batch Insert test;" - edgardmessias/yii2-firebird - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — yii2-stable ( 235e5e...df1f2b )

by Edgard

created 2016-08-22 12:04 UTC

QueryBuilder::buildOrderByAndLimit() C

↳ Parent: QueryBuilder

Complexity

Conditions	12
Paths	40

Size

Total Lines	38
Code Lines	21

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	21
CRAP Score	12.0135

Importance

Changes	4
Bugs	1	Features	1

Metric	Value
c	4
b	1
f	1
dl	0
loc	38
ccs	21
cts	22
cp	0.9545
rs	5.1612
cc	12
eloc	21
nc	40
nop	4
crap	12.0135

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\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)){
            $columns = [];
            foreach ($columnSchemas as $columnSchema) {
                $columns[$columnSchema->name] = $columnSchema->defaultValue;
            }
        }

        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, 'blob'];
            }
        }

        return parent::insert($table, $columns, $params);
    }

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

edgardmessias / yii2-firebird

Push — yii2-stable ( 235e5e...df1f2b )

QueryBuilder::buildOrderByAndLimit() C

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Complexity

Long Method

Duplication Side-by-Side

Filter issues like