Completed
Push — master ( 2c9278...a9fb01 )
by Alexander
26:38 queued 23:33
created

QueryBuilder::buildLikeCondition()   A

Complexity

Conditions 2
Paths 2

Size

Total Lines 11
Code Lines 4

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 0
CRAP Score 6

Importance

Changes 0
Metric Value
dl 0
loc 11
ccs 0
cts 7
cp 0
rs 9.4285
c 0
b 0
f 0
cc 2
eloc 4
nc 2
nop 3
crap 6
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
     * @inheritdoc
51
     */
52
    protected $likeEscapeCharacter = '!';
53
    /**
54
     * `\` is initialized in [[buildLikeCondition()]] method since
55
     * we need to choose replacement value based on [[\yii\db\Schema::quoteValue()]].
56
     * @inheritdoc
57
     */
58
    protected $likeEscapingReplacements = [
59
        '%' => '!%',
60
        '_' => '!_',
61
        '!' => '!!',
62
    ];
63
64
    /**
65
     * @inheritdoc
66
     */
67
    public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
68
    {
69
        $orderBy = $this->buildOrderBy($orderBy);
70
        if ($orderBy !== '') {
71
            $sql .= $this->separator . $orderBy;
72
        }
73
74
        $filters = [];
75
        if ($this->hasOffset($offset)) {
76
            $filters[] = 'rowNumId > ' . $offset;
77
        }
78
        if ($this->hasLimit($limit)) {
79
            $filters[] = 'rownum <= ' . $limit;
80
        }
81
        if (empty($filters)) {
82
            return $sql;
83
        }
84
85
        $filter = implode(' AND ', $filters);
86
        return <<<EOD
87
WITH USER_SQL AS ($sql),
88
    PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL)
89
SELECT *
90
FROM PAGINATION
91
WHERE $filter
92
EOD;
93
    }
94
95
    /**
96
     * Builds a SQL statement for renaming a DB table.
97
     *
98
     * @param string $table the table to be renamed. The name will be properly quoted by the method.
99
     * @param string $newName the new table name. The name will be properly quoted by the method.
100
     * @return string the SQL statement for renaming a DB table.
101
     */
102
    public function renameTable($table, $newName)
103
    {
104
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' RENAME TO ' . $this->db->quoteTableName($newName);
105
    }
106
107
    /**
108
     * Builds a SQL statement for changing the definition of a column.
109
     *
110
     * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method.
111
     * @param string $column the name of the column to be changed. The name will be properly quoted by the method.
112
     * @param string $type the new column type. The [[getColumnType]] method will be invoked to convert abstract column type (if any)
113
     * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL.
114
     * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
115
     * @return string the SQL statement for changing the definition of a column.
116
     */
117
    public function alterColumn($table, $column, $type)
118
    {
119
        $type = $this->getColumnType($type);
120
121
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' MODIFY ' . $this->db->quoteColumnName($column) . ' ' . $this->getColumnType($type);
122
    }
123
124
    /**
125
     * Builds a SQL statement for dropping an index.
126
     *
127
     * @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
128
     * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
129
     * @return string the SQL statement for dropping an index.
130
     */
131
    public function dropIndex($name, $table)
132
    {
133
        return 'DROP INDEX ' . $this->db->quoteTableName($name);
134
    }
135
136
    /**
137
     * @inheritdoc
138
     */
139
    public function resetSequence($table, $value = null)
140
    {
141
        $tableSchema = $this->db->getTableSchema($table);
142
        if ($tableSchema === null) {
143
            throw new InvalidParamException("Unknown table: $table");
144
        }
145
        if ($tableSchema->sequenceName === null) {
146
            return '';
147
        }
148
149
        if ($value !== null) {
150
            $value = (int) $value;
151
        } else {
152
            // use master connection to get the biggest PK value
153
            $value = $this->db->useMaster(function (Connection $db) use ($tableSchema) {
154
                return $db->createCommand("SELECT MAX(\"{$tableSchema->primaryKey}\") FROM \"{$tableSchema->name}\"")->queryScalar();
155
            }) + 1;
156
        }
157
158
        return "DROP SEQUENCE \"{$tableSchema->name}_SEQ\";"
159
            . "CREATE SEQUENCE \"{$tableSchema->name}_SEQ\" START WITH {$value} INCREMENT BY 1 NOMAXVALUE NOCACHE";
160
    }
161
162
    /**
163
     * @inheritdoc
164
     */
165
    public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null)
166
    {
167
        $sql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
168
            . ' ADD CONSTRAINT ' . $this->db->quoteColumnName($name)
169
            . ' FOREIGN KEY (' . $this->buildColumns($columns) . ')'
170
            . ' REFERENCES ' . $this->db->quoteTableName($refTable)
171
            . ' (' . $this->buildColumns($refColumns) . ')';
172
        if ($delete !== null) {
173
            $sql .= ' ON DELETE ' . $delete;
174
        }
175
        if ($update !== null) {
176
            throw new Exception('Oracle does not support ON UPDATE clause.');
177
        }
178
179
        return $sql;
180
    }
181
182
    /**
183
     * @inheritdoc
184
     */
185
    public function insert($table, $columns, &$params)
186
    {
187
        $schema = $this->db->getSchema();
188
        if (($tableSchema = $schema->getTableSchema($table)) !== null) {
189
            $columnSchemas = $tableSchema->columns;
190
        } else {
191
            $columnSchemas = [];
192
        }
193
        $names = [];
194
        $placeholders = [];
195
        $values = ' DEFAULT VALUES';
196
        if ($columns instanceof \yii\db\Query) {
197
            list($names, $values) = $this->prepareInsertSelectSubQuery($columns, $schema);
198
        } else {
199
            foreach ($columns as $name => $value) {
200
                $names[] = $schema->quoteColumnName($name);
201
                if ($value instanceof Expression) {
202
                    $placeholders[] = $value->expression;
203
                    foreach ($value->params as $n => $v) {
204
                        $params[$n] = $v;
205
                    }
206
                } elseif ($value instanceof \yii\db\Query) {
207
                    list($sql, $params) = $this->build($value, $params);
208
                    $placeholders[] = "($sql)";
209
                } else {
210
                    $phName = self::PARAM_PREFIX . count($params);
211
                    $placeholders[] = $phName;
212
                    $params[$phName] = !is_array($value) && isset($columnSchemas[$name]) ? $columnSchemas[$name]->dbTypecast($value) : $value;
213
                }
214
            }
215
            if (empty($names) && $tableSchema !== null) {
216
                $columns = !empty($tableSchema->primaryKey) ? $tableSchema->primaryKey : [reset($tableSchema->columns)->name];
217
                foreach ($columns as $name) {
218
                    $names[] = $schema->quoteColumnName($name);
219
                    $placeholders[] = 'DEFAULT';
220
                }
221
            }
222
        }
223
224
        return 'INSERT INTO ' . $schema->quoteTableName($table)
225
            . (!empty($names) ? ' (' . implode(', ', $names) . ')' : '')
226
            . (!empty($placeholders) ? ' VALUES (' . implode(', ', $placeholders) . ')' : $values);
227
    }
228
229
    /**
230
     * Generates a batch INSERT SQL statement.
231
     * For example,
232
     *
233
     * ```php
234
     * $sql = $queryBuilder->batchInsert('user', ['name', 'age'], [
235
     *     ['Tom', 30],
236
     *     ['Jane', 20],
237
     *     ['Linda', 25],
238
     * ]);
239
     * ```
240
     *
241
     * Note that the values in each row must match the corresponding column names.
242
     *
243
     * @param string $table the table that new rows will be inserted into.
244
     * @param array $columns the column names
245
     * @param array $rows the rows to be batch inserted into the table
246
     * @return string the batch INSERT SQL statement
247
     */
248
    public function batchInsert($table, $columns, $rows)
249
    {
250
        if (empty($rows)) {
251
            return '';
252
        }
253
254
        $schema = $this->db->getSchema();
255
        if (($tableSchema = $schema->getTableSchema($table)) !== null) {
256
            $columnSchemas = $tableSchema->columns;
257
        } else {
258
            $columnSchemas = [];
259
        }
260
261
        $values = [];
262
        foreach ($rows as $row) {
263
            $vs = [];
264
            foreach ($row as $i => $value) {
265
                if (isset($columns[$i], $columnSchemas[$columns[$i]]) && !is_array($value)) {
266
                    $value = $columnSchemas[$columns[$i]]->dbTypecast($value);
267
                }
268
                if (is_string($value)) {
269
                    $value = $schema->quoteValue($value);
270
                } elseif ($value === false) {
271
                    $value = 0;
272
                } elseif ($value === null) {
273
                    $value = 'NULL';
274
                }
275
                $vs[] = $value;
276
            }
277
            $values[] = '(' . implode(', ', $vs) . ')';
278
        }
279
        if (empty($values)) {
280
            return '';
281
        }
282
283
        foreach ($columns as $i => $name) {
284
            $columns[$i] = $schema->quoteColumnName($name);
285
        }
286
287
        $tableAndColumns = ' INTO ' . $schema->quoteTableName($table)
288
        . ' (' . implode(', ', $columns) . ') VALUES ';
289
290
        return 'INSERT ALL ' . $tableAndColumns . implode($tableAndColumns, $values) . ' SELECT 1 FROM SYS.DUAL';
291
    }
292
293
    /**
294
     * @inheritdoc
295
     * @since 2.0.8
296
     */
297
    public function selectExists($rawSql)
298
    {
299
        return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END FROM DUAL';
300
    }
301
302
    /**
303
     * @inheritdoc
304
     * @since 2.0.8
305
     */
306
    public function dropCommentFromColumn($table, $column)
307
    {
308
        return 'COMMENT ON COLUMN ' . $this->db->quoteTableName($table) . '.' . $this->db->quoteColumnName($column) . " IS ''";
309
    }
310
311
    /**
312
     * @inheritdoc
313
     * @since 2.0.8
314
     */
315
    public function dropCommentFromTable($table)
316
    {
317
        return 'COMMENT ON TABLE ' . $this->db->quoteTableName($table) . " IS ''";
318
    }
319
320
    /**
321
     * @inheritDoc
322
     */
323
    public function buildLikeCondition($operator, $operands, &$params)
324
    {
325
        if (!isset($this->likeEscapingReplacements['\\'])) {
326
            /*
327
             * Different pdo_oci8 versions may or may not implement PDO::quote(), so
328
             * yii\db\Schema::quoteValue() may or may not quote \.
329
             */
330
            $this->likeEscapingReplacements['\\'] = substr($this->db->quoteValue('\\'), 1, -1);
331
        }
332
        return parent::buildLikeCondition($operator, $operands, $params);
333
    }
334
}
335