QueryBuilder::insert() - Code Metrics - Inspection of "Updated .travis.yml" - edgardmessias/yii2-ibm-db2 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( c81dc7...eea340 )

by Edgard

created 2018-04-26 11:46 UTC

QueryBuilder::insert() D

↳ Parent: QueryBuilder

Complexity

Conditions	9
Paths	48

Size

Total Lines	32
Code Lines	23

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	24
CRAP Score	9.0368

Importance

Changes

Metric	Value
dl	0
loc	32
ccs	24
cts	26
cp	0.9231
rs	4.909
c	0
b	0
f	0
cc	9
eloc	23
nc	48
nop	3
crap	9.0368

<?php

/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace edgardmessias\db\ibm\db2;

use yii\base\InvalidParamException;
use yii\db\Expression;

/**
 * QueryBuilder is the query builder for DB2 databases.
 * 
 * @property Connection $db Connetion
 *
 * @author Edgard Lorraine Messias <[email protected]>
 * @author Nikita Verkhovin <[email protected]>
 */
class QueryBuilder extends \yii\db\QueryBuilder
{
    public $typeMap = [
        Schema::TYPE_PK => 'integer NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY',
        Schema::TYPE_BIGPK => 'bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY',
        Schema::TYPE_STRING => 'varchar(255)',
        Schema::TYPE_TEXT => 'clob',
        Schema::TYPE_SMALLINT => 'smallint',
        Schema::TYPE_INTEGER => 'integer',
        Schema::TYPE_BIGINT => 'bigint',
        Schema::TYPE_FLOAT => 'float',
        Schema::TYPE_DOUBLE => 'double',
        Schema::TYPE_DECIMAL => 'decimal(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 => 'decimal(19,4)',
    ];

    /**
     * Builds a SQL statement for truncating a DB table.
     * @param string $table the table to be truncated. The name will be properly quoted by the method.
     * @return string the SQL statement for truncating a DB table.
     */
    public function truncateTable($table)
    {
        return 'TRUNCATE TABLE ' . $this->db->quoteTableName($table) . ' IMMEDIATE';
    }

    /**
     * @inheritdoc
     */
    public function resetSequence($tableName, $value = null)
    {
        $table = $this->db->getTableSchema($tableName);

        if ($table !== null && isset($table->columns[$table->sequenceName])) {
            if ($value === null) {
                $sql = 'SELECT MAX("'. $table->sequenceName .'") FROM "'. $tableName . '"';
                $value = $this->db->createCommand($sql)->queryScalar() + 1;
            } else {
                $value = (int) $value;
            }
            return 'ALTER TABLE "' . $tableName . '" ALTER COLUMN "'.$table->sequenceName.'" RESTART WITH ' . $value;
        } elseif ($table === null) {
            throw new InvalidParamException("Table not found: $tableName");
        } else {
            throw new InvalidParamException("There is no sequence associated with table '$tableName'.");
        }
    }

    /**
     * Builds a SQL statement for enabling or disabling integrity check.
     * @param boolean $check whether to turn on or off the integrity check.
     * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
     * @param string $table the table name. Defaults to empty string, meaning that no table will be changed.
     * @return string the SQL statement for checking integrity
     * @throws \yii\base\NotSupportedException if this is not supported by the underlying DBMS
     * @see http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000998.html?cp=SSEPGG_10.5.0%2F2-12-7-227
     */
    public function checkIntegrity($check = true, $schema = '', $table = '')
    {
        if ($table) {
            $tableNames = [$table];
        } else {
            if (!$schema) {
                $schema = $this->db->defaultSchema;
            }

            //Return only tables
            $sql = "SELECT t.tabname FROM syscat.tables AS t"
                    . " WHERE t.type in ('T') AND t.ownertype != 'S'";

            /**
             * Filter by integrity pending
             * @see http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001063.html
             */
            if ($check) {
                $sql .= " AND t.status = 'C'";
            }
            if ($schema) {
                $sql .= ' AND t.tabschema = :schema';
            }
            
            $command = $this->db->createCommand($sql);
            if ($schema) {
                $command->bindValue(':schema', $schema);
            }

            $tableNames = $command->queryColumn();
        }

        if (empty($tableNames)) {
            return '';
        }

        $quotedTableNames = [];
        foreach ($tableNames as $tableName) {
            $quotedTableNames[] = $this->db->quoteTableName($tableName) . ($check? '' : ' ALL');
        }

        $enable = $check ? 'CHECKED' : 'UNCHECKED';
        return 'SET INTEGRITY FOR ' . implode(', ', $quotedTableNames) . ' IMMEDIATE ' . $enable. ';';
    }

    /**
     * @inheritdoc
     */
    public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
    {
        $limitOffsetStatment = $this->buildLimit($limit, $offset);
        if ($limitOffsetStatment != '') {
            $sql = str_replace(':query', $sql, $limitOffsetStatment);

            //convert "item"."id" to "id" to use in OVER()
            $newOrderBy = [];

            if(!empty($orderBy)){
                foreach ($orderBy as $name => $direction) {
                    if(is_string($name)){
                        $e = explode('.', $name);
                        $name = array_pop($e);
                    }
                    $newOrderBy[$name] = $direction;
                }
            }

            $orderByStatment = $this->buildOrderBy($newOrderBy);

            $sql = str_replace(':order', $orderByStatment,$sql);
        }else{
            $orderByStatment = $this->buildOrderBy($orderBy);
            if ($orderByStatment !== '') {
                $sql .= $this->separator . $orderByStatment;
            }
        }
        return $sql;
    }

    /**
     * @inheritdoc
     */
    public function buildLimit($limit, $offset)
    {
        if (!$this->hasLimit($limit) && !$this->hasOffset($offset)) {
            return '';
        }

        $limitOffsetStatment = 'SELECT * FROM (SELECT SUBQUERY_.*, ROW_NUMBER() OVER(:order) AS RN_ FROM ( :query ) AS SUBQUERY_) as t WHERE :offset :limit';

        $replacement = $this->hasOffset($offset) ? 't.RN_ > ' . $offset : 't.RN_ > 0';
        $limitOffsetStatment = str_replace(':offset', $replacement, $limitOffsetStatment);

        $replacement = $this->hasLimit($limit) ? 'AND t.RN_ <= ' . ($limit + $offset) : '';
        $limitOffsetStatment = str_replace(':limit', $replacement, $limitOffsetStatment);

        return $limitOffsetStatment;
    }

    /**
     * @inheritdoc
     */
    public function alterColumn($table, $column, $type)
    {
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ALTER COLUMN '
        . $this->db->quoteColumnName($column) . ' SET DATA TYPE '
        . $this->getColumnType($type);
    }

    /**
     * @inheritdoc
     */
    protected function buildCompositeInCondition($operator, $columns, $values, &$params)
    {
        $vss = [];
        foreach ($values as $value) {
            $vs = [];
            foreach ($columns as $column) {
                if (isset($value[$column])) {
                    $phName = self::PARAM_PREFIX . count($params);
                    $params[$phName] = $value[$column];
                    $vs[] = $phName;
                } else {
                    $vs[] = 'NULL';
                }
            }
            $vss[] = 'select ' . implode(', ', $vs) . ' from SYSIBM.SYSDUMMY1';
        }

        $sqlColumns = [];
        foreach ($columns as $i => $column) {
            $sqlColumns[] = strpos($column, '(') === false ? $this->db->quoteColumnName($column) : $column;
        }

        return '(' . implode(', ', $sqlColumns) . ") $operator (" . implode(' UNION ', $vss) . ')';
    }

    /**
     * @inheritdoc
     */
    public function insert($table, $columns, &$params)
    {
        $schema = $this->db->getSchema();
        if (($tableSchema = $schema->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->columns;
        } else {
            $columnSchemas = [];
        }
        $names = [];
        $placeholders = [];
        foreach ($columns as $name => $value) {
            $names[] = $schema->quoteColumnName($name);
            if ($value instanceof Expression) {
                $placeholders[] = $value->expression;
                foreach ($value->params as $n => $v) {
                    $params[$n] = $v;
                }
            } else {
                $phName = self::PARAM_PREFIX . count($params);
                $placeholders[] = $phName;
                $params[$phName] = !is_array($value) && isset($columnSchemas[$name]) ? $columnSchemas[$name]->dbTypecast($value) : $value;
            }
        }

        if (empty($placeholders)) {
            $placeholders = array_fill(0, count($columnSchemas), 'DEFAULT');
        }

        return 'INSERT INTO ' . $schema->quoteTableName($table)
        . (!empty($names) ? ' (' . implode(', ', $names) . ')' : '')
        . ' VALUES (' . implode(', ', $placeholders) . ')';
    }
    
    /**
     * 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 SYSIBM.SYSDUMMY1';
    }

    /**
     * Builds a SQL command for adding comment to column
     *
     * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method.
     * @param string $column the name of the column to be commented. The column name will be properly quoted by the method.
     * @return string the SQL statement for adding comment on column
     * @since 2.0.8
     */
    public function dropCommentFromColumn($table, $column)
    {
        return 'COMMENT ON COLUMN ' . $this->db->quoteTableName($table) . '.' . $this->db->quoteColumnName($column) . " IS ''";
    }

    /**
     * Builds a SQL command for adding comment to table
     *
     * @param string $table the table whose column is to be commented. The table name will be properly quoted by the method.
     * @return string the SQL statement for adding comment on column
     * @since 2.0.8
     */
    public function dropCommentFromTable($table)
    {
        return 'COMMENT ON TABLE ' . $this->db->quoteTableName($table) . " IS ''";
    }
}


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\ibm\db2;
10
11		use yii\base\InvalidParamException;
12		use yii\db\Expression;
13
14		/**
15		* QueryBuilder is the query builder for DB2 databases.
16		*
17		* @property Connection $db Connetion
18		*
19		* @author Edgard Lorraine Messias <[email protected]>
20		* @author Nikita Verkhovin <[email protected]>
21		*/
22		class QueryBuilder extends \yii\db\QueryBuilder
23		{
24		public $typeMap = [
25		Schema::TYPE_PK => 'integer NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY',
26		Schema::TYPE_BIGPK => 'bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY',
27		Schema::TYPE_STRING => 'varchar(255)',
28		Schema::TYPE_TEXT => 'clob',
29		Schema::TYPE_SMALLINT => 'smallint',
30		Schema::TYPE_INTEGER => 'integer',
31		Schema::TYPE_BIGINT => 'bigint',
32		Schema::TYPE_FLOAT => 'float',
33		Schema::TYPE_DOUBLE => 'double',
34		Schema::TYPE_DECIMAL => 'decimal(10,0)',
35		Schema::TYPE_DATETIME => 'timestamp',
36		Schema::TYPE_TIMESTAMP => 'timestamp',
37		Schema::TYPE_TIME => 'time',
38		Schema::TYPE_DATE => 'date',
39		Schema::TYPE_BINARY => 'blob',
40		Schema::TYPE_BOOLEAN => 'smallint',
41		Schema::TYPE_MONEY => 'decimal(19,4)',
42		];
43
44		/**
45		* Builds a SQL statement for truncating a DB table.
46		* @param string $table the table to be truncated. The name will be properly quoted by the method.
47		* @return string the SQL statement for truncating a DB table.
48		*/
49	1	public function truncateTable($table)
50		{
51	1	return 'TRUNCATE TABLE ' . $this->db->quoteTableName($table) . ' IMMEDIATE';
52		}
53
54		/**
55		* @inheritdoc
56		*/
57	2	public function resetSequence($tableName, $value = null)
58		{
59	2	$table = $this->db->getTableSchema($tableName);
60
61	2	if ($table !== null && isset($table->columns[$table->sequenceName])) {
62	2	if ($value === null) {
63		$sql = 'SELECT MAX("'. $table->sequenceName .'") FROM "'. $tableName . '"';
64		$value = $this->db->createCommand($sql)->queryScalar() + 1;
65		} else {
66	2	$value = (int) $value;
67		}
68	2	return 'ALTER TABLE "' . $tableName . '" ALTER COLUMN "'.$table->sequenceName.'" RESTART WITH ' . $value;
69		} elseif ($table === null) {
70		throw new InvalidParamException("Table not found: $tableName");
71		} else {
72		throw new InvalidParamException("There is no sequence associated with table '$tableName'.");
73		}
74		}
75
76		/**
77		* Builds a SQL statement for enabling or disabling integrity check.
78		* @param boolean $check whether to turn on or off the integrity check.
79		* @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
80		* @param string $table the table name. Defaults to empty string, meaning that no table will be changed.
81		* @return string the SQL statement for checking integrity
82		* @throws \yii\base\NotSupportedException if this is not supported by the underlying DBMS
83		* @see http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000998.html?cp=SSEPGG_10.5.0%2F2-12-7-227
84		*/
85	2	public function checkIntegrity($check = true, $schema = '', $table = '')
86		{
87	2	if ($table) {
88		$tableNames = [$table];
89		} else {
90	2	if (!$schema) {
91	2	$schema = $this->db->defaultSchema;
92	2	}
93
94		//Return only tables
95		$sql = "SELECT t.tabname FROM syscat.tables AS t"
96	2	. " WHERE t.type in ('T') AND t.ownertype != 'S'";
97
98		/**
99		* Filter by integrity pending
100		* @see http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001063.html
101		*/
102	2	if ($check) {
103	2	$sql .= " AND t.status = 'C'";
104	2	}
105	2	if ($schema) {
106	2	$sql .= ' AND t.tabschema = :schema';
107	2	}
108
109	2	$command = $this->db->createCommand($sql);
110	2	if ($schema) {
111	2	$command->bindValue(':schema', $schema);
112	2	}
113
114	2	$tableNames = $command->queryColumn();
115		}
116
117	2	if (empty($tableNames)) {
118	2	return '';
119		}
120
121	2	$quotedTableNames = [];
122	2	foreach ($tableNames as $tableName) {
123	2	$quotedTableNames[] = $this->db->quoteTableName($tableName) . ($check? '' : ' ALL');
124	2	}
125
126	2	$enable = $check ? 'CHECKED' : 'UNCHECKED';
127	2	return 'SET INTEGRITY FOR ' . implode(', ', $quotedTableNames) . ' IMMEDIATE ' . $enable. ';';
128		}
129
130		/**
131		* @inheritdoc
132		*/
133	191	public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
134		{
135	191	$limitOffsetStatment = $this->buildLimit($limit, $offset);
136	191	if ($limitOffsetStatment != '') {
137	13	$sql = str_replace(':query', $sql, $limitOffsetStatment);
138
139		//convert "item"."id" to "id" to use in OVER()
140	13	$newOrderBy = [];
141
142	13	if(!empty($orderBy)){
143	7	foreach ($orderBy as $name => $direction) {
144	7	if(is_string($name)){
145	7	$e = explode('.', $name);
146	7	$name = array_pop($e);
147	7	}
148	7	$newOrderBy[$name] = $direction;
149	7	}
150	7	}
151
152	13	$orderByStatment = $this->buildOrderBy($newOrderBy);
153
154	13	$sql = str_replace(':order', $orderByStatment,$sql);
155	13	}else{
156	190	$orderByStatment = $this->buildOrderBy($orderBy);
157	190	if ($orderByStatment !== '') {
158	32	$sql .= $this->separator . $orderByStatment;
159	32	}
160		}
161	191	return $sql;
162		}
163
164		/**
165		* @inheritdoc
166		*/
167	191	public function buildLimit($limit, $offset)
168		{
169	191	if (!$this->hasLimit($limit) && !$this->hasOffset($offset)) {
170	190	return '';
171		}
172
173	13	$limitOffsetStatment = 'SELECT * FROM (SELECT SUBQUERY_.*, ROW_NUMBER() OVER(:order) AS RN_ FROM ( :query ) AS SUBQUERY_) as t WHERE :offset :limit';
174
175	13	$replacement = $this->hasOffset($offset) ? 't.RN_ > ' . $offset : 't.RN_ > 0';
176	13	$limitOffsetStatment = str_replace(':offset', $replacement, $limitOffsetStatment);
177
178	13	$replacement = $this->hasLimit($limit) ? 'AND t.RN_ <= ' . ($limit + $offset) : '';
179	13	$limitOffsetStatment = str_replace(':limit', $replacement, $limitOffsetStatment);
180
181	13	return $limitOffsetStatment;
182		}
183
184		/**
185		* @inheritdoc
186		*/
187	1	public function alterColumn($table, $column, $type)
188		{
189	1	return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ALTER COLUMN '
190	1	. $this->db->quoteColumnName($column) . ' SET DATA TYPE '
191	1	. $this->getColumnType($type);
192		}
193
194		/**
195		* @inheritdoc
196		*/
197	5	protected function buildCompositeInCondition($operator, $columns, $values, &$params)
198		{
199	5	$vss = [];
200	5	foreach ($values as $value) {
201	5	$vs = [];
202	5	foreach ($columns as $column) {
203	5	if (isset($value[$column])) {
204	5	$phName = self::PARAM_PREFIX . count($params);
205	5	$params[$phName] = $value[$column];
206	5	$vs[] = $phName;
207	5	} else {
208		$vs[] = 'NULL';
209		}
210	5	}
211	5	$vss[] = 'select ' . implode(', ', $vs) . ' from SYSIBM.SYSDUMMY1';
212	5	}
213
214	5	$sqlColumns = [];
215	5	foreach ($columns as $i => $column) {
216	5	$sqlColumns[] = strpos($column, '(') === false ? $this->db->quoteColumnName($column) : $column;
217	5	}
218
219	5	return '(' . implode(', ', $sqlColumns) . ") $operator (" . implode(' UNION ', $vss) . ')';
220		}
221
222		/**
223		* @inheritdoc
224		*/
225	26	public function insert($table, $columns, &$params)
226		{
227	26	$schema = $this->db->getSchema();
228	26	if (($tableSchema = $schema->getTableSchema($table)) !== null) {
229	26	$columnSchemas = $tableSchema->columns;
230	26	} else {
231		$columnSchemas = [];
232		}
233	26	$names = [];
234	26	$placeholders = [];
235	26	foreach ($columns as $name => $value) {
236	25	$names[] = $schema->quoteColumnName($name);
237	25	if ($value instanceof Expression) {
238	1	$placeholders[] = $value->expression;
239	1	foreach ($value->params as $n => $v) {
240		$params[$n] = $v;
241	1	}
242	1	} else {
243	25	$phName = self::PARAM_PREFIX . count($params);
244	25	$placeholders[] = $phName;
245	25	$params[$phName] = !is_array($value) && isset($columnSchemas[$name]) ? $columnSchemas[$name]->dbTypecast($value) : $value;
246		}
247	26	}
248
249	26	if (empty($placeholders)) {
250	1	$placeholders = array_fill(0, count($columnSchemas), 'DEFAULT');
251	1	}
252
253	26	return 'INSERT INTO ' . $schema->quoteTableName($table)
254	26	. (!empty($names) ? ' (' . implode(', ', $names) . ')' : '')
255	26	. ' VALUES (' . implode(', ', $placeholders) . ')';
256		}
257
258		/**
259		* Creates a SELECT EXISTS() SQL statement.
260		* @param string $rawSql the subquery in a raw form to select from.
261		* @return string the SELECT EXISTS() SQL statement.
262		*
263		* @since 2.0.8
264		*/
265	13	public function selectExists($rawSql)
266		{
267	13	return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END FROM SYSIBM.SYSDUMMY1';
268		}
269
270		/**
271		* Builds a SQL command for adding comment to column
272		*
273		* @param string $table the table whose column is to be commented. The table name will be properly quoted by the method.
274		* @param string $column the name of the column to be commented. The column name will be properly quoted by the method.
275		* @return string the SQL statement for adding comment on column
276		* @since 2.0.8
277		*/
278	1	public function dropCommentFromColumn($table, $column)
279		{
280	1	return 'COMMENT ON COLUMN ' . $this->db->quoteTableName($table) . '.' . $this->db->quoteColumnName($column) . " IS ''";
281		}
282
283		/**
284		* Builds a SQL command for adding comment to table
285		*
286		* @param string $table the table whose column is to be commented. The table name will be properly quoted by the method.
287		* @return string the SQL statement for adding comment on column
288		* @since 2.0.8
289		*/
290	1	public function dropCommentFromTable($table)
291		{
292	1	return 'COMMENT ON TABLE ' . $this->db->quoteTableName($table) . " IS ''";
293		}
294		}
295

edgardmessias / yii2-ibm-db2

Push — master ( c81dc7...eea340 )

QueryBuilder::insert() D

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like