QueryBuilder::insert() - Code Metrics - Inspection of "Consistent behavior of `run()` method in widgets" - yiisoft/yii2 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — 2.1 ( c952e8...98ed49 )

by Carsten

created 2016-06-20 11:40 UTC

QueryBuilder::insert() D

↳ Parent: QueryBuilder

Complexity

Conditions	13
Paths	144

Size

Total Lines	35
Code Lines	26

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	182

Importance

Changes

Metric	Value
dl	0
loc	35
rs	4.8178
c	0
b	0
f	0
ccs	0
cts	34
cp	0
cc	13
eloc	26
nc	144
nop	3
crap	182

How to fix Complexity

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

namespace yii\db\oci;

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

/**
 * QueryBuilder is the query builder for Oracle databases.
 *
 * @author Qiang Xue <[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 => 'NUMBER(10) NOT NULL PRIMARY KEY',
        Schema::TYPE_UPK => 'NUMBER(10) UNSIGNED NOT NULL PRIMARY KEY',
        Schema::TYPE_BIGPK => 'NUMBER(20) NOT NULL PRIMARY KEY',
        Schema::TYPE_UBIGPK => 'NUMBER(20) UNSIGNED NOT NULL PRIMARY KEY',
        Schema::TYPE_CHAR => 'CHAR(1)',
        Schema::TYPE_STRING => 'VARCHAR2(255)',
        Schema::TYPE_TEXT => 'CLOB',
        Schema::TYPE_SMALLINT => 'NUMBER(5)',
        Schema::TYPE_INTEGER => 'NUMBER(10)',
        Schema::TYPE_BIGINT => 'NUMBER(20)',
        Schema::TYPE_FLOAT => 'NUMBER',
        Schema::TYPE_DOUBLE => 'NUMBER',
        Schema::TYPE_DECIMAL => 'NUMBER',
        Schema::TYPE_DATETIME => 'TIMESTAMP',
        Schema::TYPE_TIMESTAMP => 'TIMESTAMP',
        Schema::TYPE_TIME => 'TIMESTAMP',
        Schema::TYPE_DATE => 'DATE',
        Schema::TYPE_BINARY => 'BLOB',
        Schema::TYPE_BOOLEAN => 'NUMBER(1)',
        Schema::TYPE_MONEY => 'NUMBER(19,4)',
    ];


    /**
     * @inheritdoc
     */
    public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
    {
        $orderBy = $this->buildOrderBy($orderBy);

        if ($orderBy !== '') {
            $sql .= $this->separator . $orderBy;
        }

        $filters = [];
        if ($this->hasOffset($offset)) {
            $filters[] = 'rowNumId > ' . $offset;
        }
        if ($this->hasLimit($limit)) {
            $filters[] = 'rownum <= ' . $limit;
        }
        if (empty($filters)) {
            return $sql;
        }

        $filter = implode(' AND ', $filters);
        return <<<EOD
WITH USER_SQL AS ($sql),
    PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL)
SELECT *
FROM PAGINATION
WHERE $filter
EOD;
    }

    /**
     * Builds a SQL statement for renaming a DB table.
     *
     * @param string $table the table to be renamed. The name will be properly quoted by the method.
     * @param string $newName the new table name. The name will be properly quoted by the method.
     * @return string the SQL statement for renaming a DB table.
     */
    public function renameTable($table, $newName)
    {
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' RENAME TO ' . $this->db->quoteTableName($newName);
    }

    /**
     * Builds a SQL statement for changing the definition of a column.
     *
     * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method.
     * @param string $column the name of the column to be changed. The name will be properly quoted by the method.
     * @param string $type the new column type. The [[getColumnType]] method will be invoked to convert abstract column type (if any)
     * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL.
     * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
     * @return string the SQL statement for changing the definition of a column.
     */
    public function alterColumn($table, $column, $type)
    {
        $type = $this->getColumnType($type);

        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' MODIFY ' . $this->db->quoteColumnName($column) . ' ' . $this->getColumnType($type);
    }

    /**
     * Builds a SQL statement for dropping an index.
     *
     * @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
     * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
     * @return string the SQL statement for dropping an index.
     */
    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("Unknown table: $table");
        }
        if ($tableSchema->sequenceName === null) {
            return '';
        }

        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) {
                return $db->createCommand("SELECT MAX(\"{$tableSchema->primaryKey}\") FROM \"{$tableSchema->name}\"")->queryScalar();
            }) + 1;
        }

        return "DROP SEQUENCE \"{$tableSchema->name}_SEQ\";"
            . "CREATE SEQUENCE \"{$tableSchema->name}_SEQ\" START WITH {$value} INCREMENT BY 1 NOMAXVALUE NOCACHE";
    }

    /**
     * @inheritdoc
     */
    public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null)
    {
        $sql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
            . ' ADD CONSTRAINT ' . $this->db->quoteColumnName($name)
            . ' FOREIGN KEY (' . $this->buildColumns($columns) . ')'
            . ' REFERENCES ' . $this->db->quoteTableName($refTable)
            . ' (' . $this->buildColumns($refColumns) . ')';
        if ($delete !== null) {
            $sql .= ' ON DELETE ' . $delete;
        }
        if ($update !== null) {
            throw new Exception('Oracle does not support ON UPDATE clause.');
        }

        return $sql;
    }

    /**
     * @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($names) && $tableSchema !== null) {
            $columns = !empty($tableSchema->primaryKey) ? $tableSchema->primaryKey : reset($tableSchema->columns)->name;
            foreach ($columns as $name) {
                $names[] = $schema->quoteColumnName($name);
                $placeholders[] = 'DEFAULT';
            }
        }

        return 'INSERT INTO ' . $schema->quoteTableName($table)
            . (!empty($names) ? ' (' . implode(', ', $names) . ')' : '')
            . (!empty($placeholders) ? ' VALUES (' . implode(', ', $placeholders) . ')' : ' DEFAULT VALUES');
    }

    /**
     * Generates a batch INSERT SQL statement.
     * For example,
     *
     * ```php
     * $sql = $queryBuilder->batchInsert('user', ['name', 'age'], [
     *     ['Tom', 30],
     *     ['Jane', 20],
     *     ['Linda', 25],
     * ]);
     * ```
     *
     * Note that the values in each row must match the corresponding column names.
     *
     * @param string $table the table that new rows will be inserted into.
     * @param array $columns the column names
     * @param array $rows the rows to be batch inserted into the table
     * @return string the batch INSERT SQL statement
     */
    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[] = '(' . implode(', ', $vs) . ')';
        }

        foreach ($columns as $i => $name) {
            $columns[$i] = $schema->quoteColumnName($name);
        }

        $tableAndColumns = ' INTO ' . $schema->quoteTableName($table)
        . ' (' . implode(', ', $columns) . ') VALUES ';

        return 'INSERT ALL ' . $tableAndColumns . implode($tableAndColumns, $values) . ' SELECT 1 FROM SYS.DUAL';
    }

    /**
     * @inheritdoc
     * @since 2.0.8
     */
    public function selectExists($rawSql)
    {
        return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END FROM DUAL';
    }

    /**
     * @inheritdoc
     * @since 2.0.8
     */
    public function dropCommentFromColumn($table, $column)
    {
        return 'COMMENT ON COLUMN ' . $this->db->quoteTableName($table) . '.' . $this->db->quoteColumnName($column) . " IS ''";
    }

    /**
     * @inheritdoc
     * @since 2.0.8
     */
    public function dropCommentFromTable($table)
    {
        return 'COMMENT ON TABLE ' . $this->db->quoteTableName($table) . " IS ''";
    }
}


1			<?php
2			/**
3			* @link http://www.yiiframework.com/
4			* @copyright Copyright (c) 2008 Yii Software LLC
5			* @license http://www.yiiframework.com/license/
6			*/
7
8			namespace yii\db\oci;
9
10			use yii\base\InvalidParamException;
11			use yii\db\Connection;
12			use yii\db\Exception;
13			use yii\db\Expression;
14
15			/**
16			* QueryBuilder is the query builder for Oracle databases.
17			*
18			* @author Qiang Xue <[email protected]>
19			* @since 2.0
20			*/
21			class QueryBuilder extends \yii\db\QueryBuilder
22			{
23			/**
24			* @var array mapping from abstract column types (keys) to physical column types (values).
25			*/
26			public $typeMap = [
27			Schema::TYPE_PK => 'NUMBER(10) NOT NULL PRIMARY KEY',
28			Schema::TYPE_UPK => 'NUMBER(10) UNSIGNED NOT NULL PRIMARY KEY',
29			Schema::TYPE_BIGPK => 'NUMBER(20) NOT NULL PRIMARY KEY',
30			Schema::TYPE_UBIGPK => 'NUMBER(20) UNSIGNED NOT NULL PRIMARY KEY',
31			Schema::TYPE_CHAR => 'CHAR(1)',
32			Schema::TYPE_STRING => 'VARCHAR2(255)',
33			Schema::TYPE_TEXT => 'CLOB',
34			Schema::TYPE_SMALLINT => 'NUMBER(5)',
35			Schema::TYPE_INTEGER => 'NUMBER(10)',
36			Schema::TYPE_BIGINT => 'NUMBER(20)',
37			Schema::TYPE_FLOAT => 'NUMBER',
38			Schema::TYPE_DOUBLE => 'NUMBER',
39			Schema::TYPE_DECIMAL => 'NUMBER',
40			Schema::TYPE_DATETIME => 'TIMESTAMP',
41			Schema::TYPE_TIMESTAMP => 'TIMESTAMP',
42			Schema::TYPE_TIME => 'TIMESTAMP',
43			Schema::TYPE_DATE => 'DATE',
44			Schema::TYPE_BINARY => 'BLOB',
45			Schema::TYPE_BOOLEAN => 'NUMBER(1)',
46			Schema::TYPE_MONEY => 'NUMBER(19,4)',
47			];
48
49
50			/**
51			* @inheritdoc
52			*/
53			public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
54			{
55			$orderBy = $this->buildOrderBy($orderBy);
			0 ignored issues – show Bug introduced 2016-01-28 16:56 UTC by Report Bug Copy Issue Report The call to `buildOrderBy()` misses a required argument `$params`. This check looks for function calls that miss required arguments. Loading history...
56			if ($orderBy !== '') {
57			$sql .= $this->separator . $orderBy;
58			}
59
60			$filters = [];
61			if ($this->hasOffset($offset)) {
62			$filters[] = 'rowNumId > ' . $offset;
63			}
64			if ($this->hasLimit($limit)) {
65			$filters[] = 'rownum <= ' . $limit;
66			}
67			if (empty($filters)) {
68			return $sql;
69			}
70
71			$filter = implode(' AND ', $filters);
72			return <<<EOD
73			WITH USER_SQL AS ($sql),
74			PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL)
75			SELECT *
76			FROM PAGINATION
77			WHERE $filter
78			EOD;
79			}
80
81			/**
82			* Builds a SQL statement for renaming a DB table.
83			*
84			* @param string $table the table to be renamed. The name will be properly quoted by the method.
85			* @param string $newName the new table name. The name will be properly quoted by the method.
86			* @return string the SQL statement for renaming a DB table.
87			*/
88			public function renameTable($table, $newName)
89			{
90			return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' RENAME TO ' . $this->db->quoteTableName($newName);
91			}
92
93			/**
94			* Builds a SQL statement for changing the definition of a column.
95			*
96			* @param string $table the table whose column is to be changed. The table name will be properly quoted by the method.
97			* @param string $column the name of the column to be changed. The name will be properly quoted by the method.
98			* @param string $type the new column type. The [[getColumnType]] method will be invoked to convert abstract column type (if any)
99			* into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL.
100			* For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
101			* @return string the SQL statement for changing the definition of a column.
102			*/
103			public function alterColumn($table, $column, $type)
104			{
105			$type = $this->getColumnType($type);
106
107			return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' MODIFY ' . $this->db->quoteColumnName($column) . ' ' . $this->getColumnType($type);
108			}
109
110			/**
111			* Builds a SQL statement for dropping an index.
112			*
113			* @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
114			* @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
115			* @return string the SQL statement for dropping an index.
116			*/
117			public function dropIndex($name, $table)
118			{
119			return 'DROP INDEX ' . $this->db->quoteTableName($name);
120			}
121
122			/**
123			* @inheritdoc
124			*/
125			public function resetSequence($table, $value = null)
126			{
127			$tableSchema = $this->db->getTableSchema($table);
128			if ($tableSchema === null) {
129			throw new InvalidParamException("Unknown table: $table");
130			}
131			if ($tableSchema->sequenceName === null) {
132			return '';
133			}
134
135			if ($value !== null) {
136			$value = (int) $value;
137			} else {
138			// use master connection to get the biggest PK value
139			$value = $this->db->useMaster(function (Connection $db) use ($tableSchema) {
140			return $db->createCommand("SELECT MAX(\"{$tableSchema->primaryKey}\") FROM \"{$tableSchema->name}\"")->queryScalar();
141			}) + 1;
142			}
143
144			return "DROP SEQUENCE \"{$tableSchema->name}_SEQ\";"
145			. "CREATE SEQUENCE \"{$tableSchema->name}_SEQ\" START WITH {$value} INCREMENT BY 1 NOMAXVALUE NOCACHE";
146			}
147
148			/**
149			* @inheritdoc
150			*/
151			public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null)
152			{
153			$sql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
154			. ' ADD CONSTRAINT ' . $this->db->quoteColumnName($name)
155			. ' FOREIGN KEY (' . $this->buildColumns($columns) . ')'
156			. ' REFERENCES ' . $this->db->quoteTableName($refTable)
157			. ' (' . $this->buildColumns($refColumns) . ')';
158			if ($delete !== null) {
159			$sql .= ' ON DELETE ' . $delete;
160			}
161			if ($update !== null) {
162			throw new Exception('Oracle does not support ON UPDATE clause.');
163			}
164
165			return $sql;
166			}
167
168			/**
169			* @inheritdoc
170			*/
171			public function insert($table, $columns, &$params)
172			{
173			$schema = $this->db->getSchema();
174			if (($tableSchema = $schema->getTableSchema($table)) !== null) {
175			$columnSchemas = $tableSchema->columns;
176			} else {
177			$columnSchemas = [];
178			}
179			$names = [];
180			$placeholders = [];
181			foreach ($columns as $name => $value) {
182			$names[] = $schema->quoteColumnName($name);
183			if ($value instanceof Expression) {
184			$placeholders[] = $value->expression;
185			foreach ($value->params as $n => $v) {
186			$params[$n] = $v;
187			}
188			} else {
189			$phName = self::PARAM_PREFIX . count($params);
190			$placeholders[] = $phName;
191			$params[$phName] = !is_array($value) && isset($columnSchemas[$name]) ? $columnSchemas[$name]->dbTypecast($value) : $value;
192			}
193			}
194			if (empty($names) && $tableSchema !== null) {
195			$columns = !empty($tableSchema->primaryKey) ? $tableSchema->primaryKey : reset($tableSchema->columns)->name;
196			foreach ($columns as $name) {
197			$names[] = $schema->quoteColumnName($name);
198			$placeholders[] = 'DEFAULT';
199			}
200			}
201
202			return 'INSERT INTO ' . $schema->quoteTableName($table)
203			. (!empty($names) ? ' (' . implode(', ', $names) . ')' : '')
204			. (!empty($placeholders) ? ' VALUES (' . implode(', ', $placeholders) . ')' : ' DEFAULT VALUES');
205			}
206
207			/**
208			* Generates a batch INSERT SQL statement.
209			* For example,
210			*
211			* ```php
212			* $sql = $queryBuilder->batchInsert('user', ['name', 'age'], [
213			* ['Tom', 30],
214			* ['Jane', 20],
215			* ['Linda', 25],
216			* ]);
217			* ```
218			*
219			* Note that the values in each row must match the corresponding column names.
220			*
221			* @param string $table the table that new rows will be inserted into.
222			* @param array $columns the column names
223			* @param array $rows the rows to be batch inserted into the table
224			* @return string the batch INSERT SQL statement
225			*/
226			public function batchInsert($table, $columns, $rows)
227			{
228			$schema = $this->db->getSchema();
229			if (($tableSchema = $schema->getTableSchema($table)) !== null) {
230			$columnSchemas = $tableSchema->columns;
231			} else {
232			$columnSchemas = [];
233			}
234
235			$values = [];
236			foreach ($rows as $row) {
237			$vs = [];
238			foreach ($row as $i => $value) {
239			if (isset($columns[$i], $columnSchemas[$columns[$i]]) && !is_array($value)) {
240			$value = $columnSchemas[$columns[$i]]->dbTypecast($value);
241			}
242			if (is_string($value)) {
243			$value = $schema->quoteValue($value);
244			} elseif ($value === false) {
245			$value = 0;
246			} elseif ($value === null) {
247			$value = 'NULL';
248			}
249			$vs[] = $value;
250			}
251			$values[] = '(' . implode(', ', $vs) . ')';
252			}
253
254			foreach ($columns as $i => $name) {
255			$columns[$i] = $schema->quoteColumnName($name);
256			}
257
258			$tableAndColumns = ' INTO ' . $schema->quoteTableName($table)
259			. ' (' . implode(', ', $columns) . ') VALUES ';
260
261			return 'INSERT ALL ' . $tableAndColumns . implode($tableAndColumns, $values) . ' SELECT 1 FROM SYS.DUAL';
262			}
263
264			/**
265			* @inheritdoc
266			* @since 2.0.8
267			*/
268			public function selectExists($rawSql)
269			{
270			return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END FROM DUAL';
271			}
272
273			/**
274			* @inheritdoc
275			* @since 2.0.8
276			*/
277			public function dropCommentFromColumn($table, $column)
278			{
279			return 'COMMENT ON COLUMN ' . $this->db->quoteTableName($table) . '.' . $this->db->quoteColumnName($column) . " IS ''";
280			}
281
282			/**
283			* @inheritdoc
284			* @since 2.0.8
285			*/
286			public function dropCommentFromTable($table)
287			{
288			return 'COMMENT ON TABLE ' . $this->db->quoteTableName($table) . " IS ''";
289			}
290			}
291

yiisoft / yii2

Push — 2.1 ( c952e8...98ed49 )

QueryBuilder::insert() D

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Complexity

Long Method

Duplication Side-by-Side

Filter issues like