QueryBuilder::resetSequence() - Code Metrics - Inspection of "Merge branch 'yii2-stable'" - edgardmessias/yii2-firebird - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 96636c...782a93 )

by Edgard

created 2016-10-27 15:47 UTC

QueryBuilder::resetSequence() C

↳ Parent: QueryBuilder

Complexity

Conditions	7
Paths	4

Size

Total Lines	31
Code Lines	20

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	56

Importance

Changes

Metric	Value
dl	0
loc	31
ccs	0
cts	21
cp	0
rs	6.7272
c	0
b	0
f	0
cc	7
eloc	20
nc	4
nop	2
crap	56

<?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	99	public function build($query, $params = [])
59		{
60	99	$query = $query->prepare($this);
61
62	99	$params = empty($params) ? $query->params : array_merge($params, $query->params);
63
64		$clauses = [
65	99	$this->buildSelect($query->select, $params, $query->distinct, $query->selectOption),
66	99	$this->buildFrom($query->from, $params),
67	99	$this->buildJoin($query->join, $params),
68	99	$this->buildWhere($query->where, $params),
69	99	$this->buildGroupBy($query->groupBy),
70	99	$this->buildHaving($query->having, $params),
71	99	];
72
73	99	$sql = implode($this->separator, array_filter($clauses));
74	99	$sql = $this->buildOrderByAndLimit($sql, $query->orderBy, $query->limit, $query->offset);
75
76	99	if (!empty($query->orderBy)) {
77	1	foreach ($query->orderBy as $expression) {
78	1	if ($expression instanceof Expression) {
79	1	$params = array_merge($params, $expression->params);
80	1	}
81	1	}
82	1	}
83	99	if (!empty($query->groupBy)) {
84	1	foreach ($query->groupBy as $expression) {
85	1	if ($expression instanceof Expression) {
86	1	$params = array_merge($params, $expression->params);
87	1	}
88	1	}
89	1	}
90
91	99	$union = $this->buildUnion($query->union, $params);
92	99	if ($union !== '') {
93	1	$sql = "$sql{$this->separator}$union";
94	1	}
95
96	99	return [$sql, $params];
97		}
98
99		/**
100		* @inheritdoc
101		*/
102	99	public function buildSelect($columns, &$params, $distinct = false, $selectOption = null)
103		{
104	99	if (is_array($columns)) {
105	18	foreach ($columns as $i => $column) {
106	18	if (!is_string($column)) {
107	3	continue;
108		}
109	17	$matches = [];
110	17	if (preg_match("/^(COUNT\|SUM\|AVG\|MIN\|MAX)\([\{\[]{0,2}(\w+\|\*)[\}\]]{0,2}\)$/i", $column, $matches)) {
111	3	$function = $matches[1];
112	3	$alias = $matches[2] != '*' ? $matches[2] : 'ALL';
113
114	3	$columns[$i] = "{$column} AS {$function}_{$alias}";
115	3	}
116	18	}
117	18	}
118
119	99	return parent::buildSelect($columns, $params, $distinct, $selectOption);
120		}
121
122		/**
123		* @inheritdoc
124		*/
125	4	protected function buildCompositeInCondition($operator, $columns, $values, &$params)
126		{
127	4	$quotedColumns = [];
128	4	foreach ($columns as $i => $column) {
129	4	$quotedColumns[$i] = strpos($column, '(') === false ? $this->db->quoteColumnName($column) : $column;
130	4	}
131	4	$vss = [];
132	4	foreach ($values as $value) {
133	4	$vs = [];
134	4	foreach ($columns as $i => $column) {
135	4	if (isset($value[$column])) {
136	4	$phName = self::PARAM_PREFIX . count($params);
137	4	$params[$phName] = $value[$column];
138	4	$vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' = ' : ' != ') . $phName;
139	4	} else {
140		$vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' IS' : ' IS NOT') . ' NULL';
141		}
142	4	}
143	4	$vss[] = '(' . implode($operator === 'IN' ? ' AND ' : ' OR ', $vs) . ')';
144	4	}
145	4	return '(' . implode($operator === 'IN' ? ' OR ' : ' AND ', $vss) . ')';
146		}
147
148		/**
149		* @inheritdoc
150		*/
151	99	public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
152		{
153
154	99	$orderBy = $this->buildOrderBy($orderBy);
155	99	if ($orderBy !== '') {
156	1	$sql .= $this->separator . $orderBy;
157	1	}
158
159	99	$limit = $limit !== null ? intval($limit) : -1;
160	99	$offset = $offset !== null ? intval($offset) : -1;
161		// If ignoring both params then do nothing
162	99	if ($offset < 0 && $limit < 0) {
163	99	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		if ($offset >= 0 && $limit < 0) {
168		$count = 1; //Only do it once
169		$sql = preg_replace('/^SELECT /i', 'SELECT SKIP ' . (int) $offset . ' ', $sql, $count);
170		return $sql;
171		}
172		// If we are ignoring $offset then return $limit rows.
173		// ie, return the first $limit rows in the set.
174		if ($offset < 0 && $limit >= 0) {
175		$count = 1; //Only do it once
176		$sql = preg_replace('/^SELECT /i', 'SELECT FIRST ' . (int) $limit . ' ', $sql, $count);
177		return $sql;
178		}
179		// Otherwise apply the params and return the amended sql.
180		if ($offset >= 0 && $limit >= 0) {
181		$count = 1; //Only do it once
182		$sql = preg_replace('/^SELECT /i', 'SELECT FIRST ' . (int) $limit . ' SKIP ' . (int) $offset, $sql, $count);
183		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	99	public function buildUnion($unions, &$params)
196		{
197	99	if (empty($unions)) {
198	99	return '';
199		}
200
201	1	$result = '';
202
203	1	foreach ($unions as $i => $union) {
204	1	$query = $union['query'];
205	1	if ($query instanceof Query) {
206	1	list($unions[$i]['query'], $params) = $this->build($query, $params);
207	1	}
208
209	1	$result .= 'UNION ' . ($union['all'] ? 'ALL ' : '') . $unions[$i]['query'] . ' ';
210	1	}
211
212	1	return trim($result);
213		}
214
215		/**
216		*
217		* @param Expression $value
218		* @return Expression
219		*/
220		protected function convertExpression($value)
221		{
222		if (!($value instanceof Expression)) {
223		return $value;
224		}
225
226		$expressionMap = [
227		"strftime('%Y')" => "EXTRACT(YEAR FROM TIMESTAMP 'now')"
228		];
229
230		if (isset($expressionMap[$value->expression])) {
231		return new Expression($expressionMap[$value->expression]);
232		}
233		return $value;
234		}
235
236		/**
237		* @inheritdoc
238		*/
239	1	public function insert($table, $columns, &$params)
240		{
241	1	$schema = $this->db->getSchema();
242	1	if (($tableSchema = $schema->getTableSchema($table)) !== null) {
243	1	$columnSchemas = $tableSchema->columns;
244	1	} else {
245		$columnSchemas = [];
246		}
247
248		//Empty insert
249	1	if(empty($columns) && !empty($columnSchemas)){
250		$columns = [];
251		foreach ($columnSchemas as $columnSchema) {
252		if(!$columnSchema->autoIncrement){
253		$columns[$columnSchema->name] = $columnSchema->defaultValue;
254		}
255		}
256		}
257
258	1	foreach ($columns as $name => $value) {
259	1	if ($value instanceof Expression) {
260		$columns[$name] = $this->convertExpression($value);
261	1	} elseif (isset($columnSchemas[$name]) && in_array($columnSchemas[$name]->type, [Schema::TYPE_TEXT, Schema::TYPE_BINARY])) {
262		$columns[$name] = [$value, 'blob'];
263		}
264	1	}
265
266	1	return parent::insert($table, $columns, $params);
267		}
268
269		/**
270		* @inheritdoc
271		*/
272		public function update($table, $columns, $condition, &$params)
273		{
274		$schema = $this->db->getSchema();
275		if (($tableSchema = $schema->getTableSchema($table)) !== null) {
276		$columnSchemas = $tableSchema->columns;
277		} else {
278		$columnSchemas = [];
279		}
280		foreach ($columns as $name => $value) {
281		if ($value instanceof Expression) {
282		$columns[$name] = $this->convertExpression($value);
283		} elseif (isset($columnSchemas[$name]) && in_array($columnSchemas[$name]->type, [Schema::TYPE_TEXT, Schema::TYPE_BINARY])) {
284		$columns[$name] = [$value, 'blob'];
285		}
286		}
287		return parent::update($table, $columns, $condition, $params);
288		}
289
290		/**
291		* @inheritdoc
292		*/
293		public function batchInsert($table, $columns, $rows)
294		{
295		if (empty($rows)) {
296		return '';
297		}
298
299		$schema = $this->db->getSchema();
300		if (($tableSchema = $schema->getTableSchema($table)) !== null) {
301		$columnSchemas = $tableSchema->columns;
302		} else {
303		$columnSchemas = [];
304		}
305
306		$values = [];
307		foreach ($rows as $row) {
308		$vs = [];
309		foreach ($row as $i => $value) {
310		if (isset($columns[$i], $columnSchemas[$columns[$i]]) && !is_array($value)) {
311		$value = $columnSchemas[$columns[$i]]->dbTypecast($value);
312		}
313		if (is_string($value)) {
314		$value = $schema->quoteValue($value);
315		} elseif ($value === false) {
316		$value = 0;
317		} elseif ($value === null) {
318		$value = 'NULL';
319		}
320		$vs[] = $value;
321		}
322		$values[] = 'INSERT INTO ' . $schema->quoteTableName($table)
323		. ' (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $vs) . ');';
324		}
325
326		foreach ($columns as $i => $name) {
327		$columns[$i] = $schema->quoteColumnName($name);
328		}
329
330		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		public function truncateTable($table)
345		{
346		return "DELETE FROM " . $this->db->quoteTableName($table);
347		}
348
349		/**
350		* @inheritdoc
351		*/
352		public function dropColumn($table, $column)
353		{
354		return "ALTER TABLE " . $this->db->quoteTableName($table)
355		. " DROP " . $this->db->quoteColumnName($column);
356		}
357
358		/**
359		* @inheritdoc
360		*/
361		public function renameColumn($table, $oldName, $newName)
362		{
363		return "ALTER TABLE " . $this->db->quoteTableName($table)
364		. " ALTER " . $this->db->quoteColumnName($oldName)
365		. " TO " . $this->db->quoteColumnName($newName);
366		}
367
368		/**
369		* @inheritdoc
370		*/
371	1	public function alterColumn($table, $column, $type)
372		{
373	1	$schema = $this->db->getSchema();
374	1	$tableSchema = $schema->getTableSchema($table);
375	1	$columnSchema = $tableSchema->getColumn($column);
376
377	1	$allowNullNewType = !preg_match("/not +null/i", $type);
378
379	1	$type = preg_replace("/ +(not)? *null/i", "", $type);
380
381	1	$hasType = false;
382
383	1	$matches = [];
384	1	if (isset($this->typeMap[$type])) {
385		$hasType = true;
386	1	} elseif (preg_match('/^(\w+)[\( ]/', $type, $matches)) {
387	1	if (isset($this->typeMap[$matches[1]])) {
388	1	$hasType = true;
389	1	}
390	1	}
391
392	1	$baseSql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
393	1	. ' ALTER ' . $this->db->quoteColumnName($column)
394	1	. (($hasType) ? ' TYPE ' : ' ') . $this->getColumnType($type);
395
396	1	if ($columnSchema->allowNull == $allowNullNewType) {
397		return $baseSql;
398		} else {
399		$sql = 'EXECUTE BLOCK AS BEGIN'
400	1	. ' EXECUTE STATEMENT ' . $this->db->quoteValue($baseSql) . ';'
401	1	. ' UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = ' . ($allowNullNewType ? 'NULL' : '1')
402	1	. ' 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	1	if (!$allowNullNewType) {
409	1	$sql .= ' UPDATE ' . $this->db->quoteTableName($table) . ' SET ' . $this->db->quoteColumnName($column) . ' = 0'
410	1	. ' WHERE ' . $this->db->quoteColumnName($column) . ' IS NULL;';
411	1	}
412	1	$sql .= ' END';
413	1	return $sql;
414		}
415		}
416
417		/**
418		* @inheritdoc
419		*/
420		public function dropIndex($name, $table)
421		{
422		return 'DROP INDEX ' . $this->db->quoteTableName($name);
423		}
424
425		/**
426		* @inheritdoc
427		*/
428		public function resetSequence($table, $value = null)
429		{
430		$tableSchema = $this->db->getTableSchema($table);
431		if ($tableSchema === null) {
432		throw new InvalidParamException("Table not found: $table");
433		}
434		if ($tableSchema->sequenceName === null) {
435		throw new InvalidParamException("There is not sequence associated with table '$table'.");
436		}
437
438		if ($value !== null) {
439		$value = (int) $value;
440		} else {
441		// use master connection to get the biggest PK value
442		$value = $this->db->useMaster(function(Connection $db) use ($tableSchema) {
443		$key = false;
444		foreach ($tableSchema->primaryKey as $name) {
445		if ($tableSchema->columns[$name]->autoIncrement) {
446		$key = $name;
447		break;
448		}
449		}
450		if ($key === false) {
451		return 0;
452		}
453		return $db->createCommand("SELECT MAX({$this->db->quoteColumnName($key)}) FROM {$this->db->quoteTableName($tableSchema->name)}")->queryScalar();
454		}) + 1;
455		}
456
457		return "ALTER SEQUENCE {$this->db->quoteColumnName($tableSchema->sequenceName)} RESTART WITH $value";
458		}
459
460		/**
461		* @inheritdoc
462		*/
463	2	public function createTable($table, $columns, $options = null)
464		{
465	2	$sql = parent::createTable($table, $columns, $options);
466
467	2	foreach ($columns as $name => $type) {
468	2	if (!is_string($name)) {
469		continue;
470		}
471
472	2	if (strpos($type, Schema::TYPE_PK) === 0 \|\| strpos($type, Schema::TYPE_BIGPK) === 0) {
473		$sqlTrigger = <<<SQLTRIGGER
474	1	CREATE TRIGGER tr_{$table}_{$name} FOR {$this->db->quoteTableName($table)}
475		ACTIVE BEFORE INSERT POSITION 0
476		AS
477		BEGIN
478	1	if (NEW.{$this->db->quoteColumnName($name)} is NULL) then NEW.{$this->db->quoteColumnName($name)} = NEXT VALUE FOR seq_{$table}_{$name};
479	1	END
480	1	SQLTRIGGER;
481
482		$sqlBlock = <<<SQL
483		EXECUTE block AS
484		BEGIN
485	1	EXECUTE STATEMENT {$this->db->quoteValue($sql)};
486	1	EXECUTE STATEMENT {$this->db->quoteValue("CREATE SEQUENCE seq_{$table}_{$name}")};
487	1	EXECUTE STATEMENT {$this->db->quoteValue($sqlTrigger)};
488	1	END;
489	1	SQL;
490
491	1	return $sqlBlock;
492		}
493	1	}
494
495	1	return $sql;
496		}
497
498		/**
499		* @inheritdoc
500		*/
501	1	public function dropTable($table)
502		{
503	1	$sql = parent::dropTable($table);
504
505	1	$tableSchema = $this->db->getTableSchema($table);
506	1	if ($tableSchema === null \|\| $tableSchema->sequenceName === null) {
507		return $sql;
508		}
509
510		$sqlBlock = <<<SQL
511		EXECUTE block AS
512		BEGIN
513	1	EXECUTE STATEMENT {$this->db->quoteValue($sql)};
514	1	EXECUTE STATEMENT {$this->db->quoteValue("DROP SEQUENCE {$tableSchema->sequenceName}")};
515	1	END;
516	1	SQL;
517	1	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		public function selectExists($rawSql)
529		{
530		return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END FROM RDB$DATABASE';
531		}
532		}
533

edgardmessias / yii2-firebird

Push — master ( 96636c...782a93 )

QueryBuilder::resetSequence() C

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like