QueryBuilder::createTable() - Code Metrics - Inspection of "Updated to Yii 2.0.9;" - edgardmessias/yii2-firebird - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — yii2-stable ( 01db8a...27a08d )

by Edgard

created 2016-08-01 11:47 UTC

QueryBuilder::createTable() B

↳ Parent: QueryBuilder

Complexity

Conditions	5
Paths	4

Size

Total Lines	34
Code Lines	18

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	17
CRAP Score	5.0042

Importance

Changes	2
Bugs	0	Features	1

Metric	Value
c	2
b	0
f	1
dl	0
loc	34
ccs	17
cts	18
cp	0.9444
rs	8.439
cc	5
eloc	18
nc	4
nop	3
crap	5.0042

<?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 = [];
        }

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

edgardmessias / yii2-firebird

Push — yii2-stable ( 01db8a...27a08d )

QueryBuilder::createTable() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like