QueryBuilder::build() - Code Metrics - Inspection of "Fixed empty insert;" - edgardmessias/yii2-firebird - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — yii2-stable ( df1f2b...8b4e44 )

by Edgard

created 2016-08-22 12:17 UTC

QueryBuilder::build() D

↳ Parent: QueryBuilder

Complexity

Conditions	9
Paths	16

Size

Total Lines	40
Code Lines	24

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	31
CRAP Score	9

Importance

Changes	1
Bugs	1	Features	0

Metric	Value
c	1
b	1
f	0
dl	0
loc	40
ccs	31
cts	31
cp	1
rs	4.909
cc	9
eloc	24
nc	16
nop	2
crap	9

<?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) && !empty($columnSchemas)){
            $columns = [];
            foreach ($columnSchemas as $columnSchema) {
                if(!$columnSchema->autoIncrement){
                    $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) && !empty($columnSchemas)){
250	1	$columns = [];
251	1	foreach ($columnSchemas as $columnSchema) {
252	1	if(!$columnSchema->autoIncrement){
253	1	$columns[$columnSchema->name] = $columnSchema->defaultValue;
254	1	}
255	1	}
256	1	}
257
258	22	foreach ($columns as $name => $value) {
259	22	if ($value instanceof Expression) {
260	1	$columns[$name] = $this->convertExpression($value);
261	22	} elseif (isset($columnSchemas[$name]) && in_array($columnSchemas[$name]->type, [Schema::TYPE_TEXT, Schema::TYPE_BINARY])) {
262		$columns[$name] = [$value, 'blob'];
263		}
264	22	}
265
266	22	return parent::insert($table, $columns, $params);
267		}
268
269		/**
270		* @inheritdoc
271		*/
272	13	public function update($table, $columns, $condition, &$params)
273		{
274	13	$schema = $this->db->getSchema();
275	13	if (($tableSchema = $schema->getTableSchema($table)) !== null) {
276	13	$columnSchemas = $tableSchema->columns;
277	13	} else {
278		$columnSchemas = [];
279		}
280	13	foreach ($columns as $name => $value) {
281	13	if ($value instanceof Expression) {
282	2	$columns[$name] = $this->convertExpression($value);
283	13	} elseif (isset($columnSchemas[$name]) && in_array($columnSchemas[$name]->type, [Schema::TYPE_TEXT, Schema::TYPE_BINARY])) {
284		$columns[$name] = [$value, 'blob'];
285		}
286	13	}
287	13	return parent::update($table, $columns, $condition, $params);
288		}
289
290		/**
291		* @inheritdoc
292		*/
293	1	public function batchInsert($table, $columns, $rows)
294		{
295	1	if (empty($rows)) {
296	1	return '';
297		}
298
299	1	$schema = $this->db->getSchema();
300	1	if (($tableSchema = $schema->getTableSchema($table)) !== null) {
301	1	$columnSchemas = $tableSchema->columns;
302	1	} else {
303		$columnSchemas = [];
304		}
305
306	1	$values = [];
307	1	foreach ($rows as $row) {
308	1	$vs = [];
309	1	foreach ($row as $i => $value) {
310	1	if (isset($columns[$i], $columnSchemas[$columns[$i]]) && !is_array($value)) {
311	1	$value = $columnSchemas[$columns[$i]]->dbTypecast($value);
312	1	}
313	1	if (is_string($value)) {
314	1	$value = $schema->quoteValue($value);
315	1	} elseif ($value === false) {
316		$value = 0;
317	1	} elseif ($value === null) {
318	1	$value = 'NULL';
319	1	}
320	1	$vs[] = $value;
321	1	}
322	1	$values[] = 'INSERT INTO ' . $schema->quoteTableName($table)
323	1	. ' (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $vs) . ');';
324	1	}
325
326	1	foreach ($columns as $i => $name) {
327	1	$columns[$i] = $schema->quoteColumnName($name);
328	1	}
329
330	1	return 'EXECUTE block AS BEGIN ' . implode(' ', $values) . ' END;';
331		}
332
333		/**
334		* @inheritdoc
335		*/
336	1	public function renameTable($oldName, $newName)
337		{
338	1	throw new \yii\base\NotSupportedException($this->db->getDriverName() . ' does not support rename table.');
339		}
340
341		/**
342		* @inheritdoc
343		*/
344	2	public function truncateTable($table)
345		{
346	2	return "DELETE FROM " . $this->db->quoteTableName($table);
347		}
348
349		/**
350		* @inheritdoc
351		*/
352	1	public function dropColumn($table, $column)
353		{
354	1	return "ALTER TABLE " . $this->db->quoteTableName($table)
355	1	. " DROP " . $this->db->quoteColumnName($column);
356		}
357
358		/**
359		* @inheritdoc
360		*/
361	1	public function renameColumn($table, $oldName, $newName)
362		{
363	1	return "ALTER TABLE " . $this->db->quoteTableName($table)
364	1	. " ALTER " . $this->db->quoteColumnName($oldName)
365	1	. " TO " . $this->db->quoteColumnName($newName);
366		}
367
368		/**
369		* @inheritdoc
370		*/
371	3	public function alterColumn($table, $column, $type)
372		{
373	3	$schema = $this->db->getSchema();
374	3	$tableSchema = $schema->getTableSchema($table);
375	3	$columnSchema = $tableSchema->getColumn($column);
376
377	3	$allowNullNewType = !preg_match("/not +null/i", $type);
378
379	3	$type = preg_replace("/ +(not)? *null/i", "", $type);
380
381	3	$hasType = false;
382
383	3	$matches = [];
384	3	if (isset($this->typeMap[$type])) {
385	2	$hasType = true;
386	3	} elseif (preg_match('/^(\w+)[\( ]/', $type, $matches)) {
387	2	if (isset($this->typeMap[$matches[1]])) {
388	2	$hasType = true;
389	2	}
390	2	}
391
392	3	$baseSql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
393	3	. ' ALTER '. $this->db->quoteColumnName($column)
394	3	. (($hasType) ? ' TYPE ': ' ') . $this->getColumnType($type);
395
396	3	if ($columnSchema->allowNull == $allowNullNewType) {
397	2	return $baseSql;
398		} else {
399		$sql = 'EXECUTE BLOCK AS BEGIN'
400	2	. ' EXECUTE STATEMENT ' . $this->db->quoteValue($baseSql) . ';'
401	2	. ' UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = ' . ($allowNullNewType ? 'NULL' : '1')
402	2	. ' WHERE UPPER(RDB$FIELD_NAME) = UPPER(\'' . $column . '\') AND UPPER(RDB$RELATION_NAME) = UPPER(\'' . $table . '\');';
403		/**
404		* In any case (whichever option you choose), make sure that the column doesn't have any NULLs.
405		* Firebird will not check it for you. Later when you backup the database, everything is fine,
406		* 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.
407		*/
408	2	if (!$allowNullNewType) {
409	2	$sql .= ' UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . $this->db->quoteColumnName($column) . ' = 0'
410	2	. ' WHERE ' . $this->db->quoteColumnName($column) . ' IS NULL;';
411	2	}
412	2	$sql .= ' END';
413	2	return $sql;
414		}
415		}
416
417		/**
418		* @inheritdoc
419		*/
420	1	public function dropIndex($name, $table)
421		{
422	1	return 'DROP INDEX ' . $this->db->quoteTableName($name);
423		}
424
425		/**
426		* @inheritdoc
427		*/
428	1	public function resetSequence($table, $value = null)
429		{
430	1	$tableSchema = $this->db->getTableSchema($table);
431	1	if ($tableSchema === null) {
432		throw new InvalidParamException("Table not found: $table");
433		}
434	1	if ($tableSchema->sequenceName === null) {
435		throw new InvalidParamException("There is not sequence associated with table '$table'.");
436		}
437
438	1	if ($value !== null) {
439	1	$value = (int) $value;
440	1	} else {
441		// use master connection to get the biggest PK value
442	1	$value = $this->db->useMaster(function(Connection $db) use ($tableSchema) {
443	1	$key = false;
444	1	foreach ($tableSchema->primaryKey as $name) {
445	1	if ($tableSchema->columns[$name]->autoIncrement) {
446	1	$key = $name;
447	1	break;
448		}
449	1	}
450	1	if ($key === false){
451		return 0;
452		}
453	1	return $db->createCommand("SELECT MAX({$this->db->quoteColumnName($key)}) FROM {$this->db->quoteTableName($tableSchema->name)}")->queryScalar();
454	1	}) + 1;
455		}
456
457	1	return "ALTER SEQUENCE {$this->db->quoteColumnName($tableSchema->sequenceName)} RESTART WITH $value";
458		}
459
460		/**
461		* @inheritdoc
462		*/
463	5	public function createTable($table, $columns, $options = null)
464		{
465	5	$sql = parent::createTable($table, $columns, $options);
466
467	5	foreach ($columns as $name => $type) {
468	5	if (!is_string($name)) {
469		continue;
470		}
471
472	5	if (strpos($type, Schema::TYPE_PK) === 0 \|\| strpos($type, Schema::TYPE_BIGPK) === 0) {
473		$sqlTrigger = <<<SQLTRIGGER
474	4	CREATE TRIGGER tr_{$table}_{$name} FOR {$this->db->quoteTableName($table)}
475		ACTIVE BEFORE INSERT POSITION 0
476		AS
477		BEGIN
478	4	if (NEW.{$this->db->quoteColumnName($name)} is NULL) then NEW.{$this->db->quoteColumnName($name)} = NEXT VALUE FOR seq_{$table}_{$name};
479	4	END
480	4	SQLTRIGGER;
481
482		$sqlBlock = <<<SQL
483		EXECUTE block AS
484		BEGIN
485	4	EXECUTE STATEMENT {$this->db->quoteValue($sql)};
486	4	EXECUTE STATEMENT {$this->db->quoteValue("CREATE SEQUENCE seq_{$table}_{$name}")};
487	4	EXECUTE STATEMENT {$this->db->quoteValue($sqlTrigger)};
488	4	END;
489	4	SQL;
490
491	4	return $sqlBlock;
492		}
493	1	}
494
495	1	return $sql;
496		}
497
498		/**
499		* @inheritdoc
500		*/
501	3	public function dropTable($table)
502		{
503	3	$sql = parent::dropTable($table);
504
505	3	$tableSchema = $this->db->getTableSchema($table);
506	3	if ($tableSchema === null \|\| $tableSchema->sequenceName === null) {
507	1	return $sql;
508		}
509
510		$sqlBlock = <<<SQL
511		EXECUTE block AS
512		BEGIN
513	2	EXECUTE STATEMENT {$this->db->quoteValue($sql)};
514	2	EXECUTE STATEMENT {$this->db->quoteValue("DROP SEQUENCE {$tableSchema->sequenceName}")};
515	2	END;
516	2	SQL;
517	2	return $sqlBlock;
518
519		}
520
521		/**
522		* Creates a SELECT EXISTS() SQL statement.
523		* @param string $rawSql the subquery in a raw form to select from.
524		* @return string the SELECT EXISTS() SQL statement.
525		*
526		* @since 2.0.8
527		*/
528	3	public function selectExists($rawSql)
529		{
530	3	return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END FROM RDB$DATABASE';
531		}
532		}
533

edgardmessias / yii2-firebird

Push — yii2-stable ( df1f2b...8b4e44 )

QueryBuilder::build() D

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like