Completed
Push — master ( e7b533...82e63d )
by Arjay
01:59
created

OracleGrammar::wrapTable()   A

Complexity

Conditions 2
Paths 2

Size

Total Lines 8
Code Lines 4

Duplication

Lines 0
Ratio 0 %

Importance

Changes 1
Bugs 1 Features 1
Metric Value
c 1
b 1
f 1
dl 0
loc 8
rs 9.4285
cc 2
eloc 4
nc 2
nop 1
1
<?php
2
3
namespace Yajra\Oci8\Query\Grammars;
4
5
use Illuminate\Database\Query\Builder;
6
use Illuminate\Database\Query\Grammars\Grammar;
7
use Yajra\Oci8\OracleReservedWords;
8
9
class OracleGrammar extends Grammar
10
{
11
    use OracleReservedWords;
12
13
    /**
14
     * The keyword identifier wrapper format.
15
     *
16
     * @var string
17
     */
18
    protected $wrapper = '%s';
19
20
    /**
21
     * @var string
22
     */
23
    protected $schema_prefix = '';
24
25
    /**
26
     * Compile an exists statement into SQL.
27
     *
28
     * @param \Illuminate\Database\Query\Builder $query
29
     * @return string
30
     */
31
    public function compileExists(Builder $query)
32
    {
33
        $q          = clone $query;
34
        $q->columns = [];
35
        $q->selectRaw('1 as "exists"')
36
          ->whereRaw("rownum = 1");
37
38
        return $this->compileSelect($q);
39
    }
40
41
    /**
42
     * Compile a select query into SQL.
43
     *
44
     * @param  \Illuminate\Database\Query\Builder
45
     * @return string
46
     */
47
    public function compileSelect(Builder $query)
48
    {
49
        if (is_null($query->columns)) {
50
            $query->columns = ['*'];
51
        }
52
53
        $components = $this->compileComponents($query);
54
55
        // If an offset is present on the query, we will need to wrap the query in
56
        // a big "ANSI" offset syntax block. This is very nasty compared to the
57
        // other database systems but is necessary for implementing features.
58
        if ($this->isPaginationable($query, $components)) {
59
            return $this->compileAnsiOffset($query, $components);
60
        }
61
62
        return trim($this->concatenate($components));
63
    }
64
65
    /**
66
     * @param Builder $query
67
     * @param array $components
68
     * @return bool
69
     */
70
    protected function isPaginationable(Builder $query, array $components)
71
    {
72
        return ($query->limit > 0 || $query->offset > 0) && ! array_key_exists('lock', $components);
73
    }
74
75
    /**
76
     * Create a full ANSI offset clause for the query.
77
     *
78
     * @param  \Illuminate\Database\Query\Builder $query
79
     * @param  array $components
80
     * @return string
81
     */
82
    protected function compileAnsiOffset(Builder $query, $components)
83
    {
84
        $constraint = $this->compileRowConstraint($query);
85
86
        $sql = $this->concatenate($components);
87
88
        // We are now ready to build the final SQL query so we'll create a common table
89
        // expression from the query and get the records with row numbers within our
90
        // given limit and offset value that we just put on as a query constraint.
91
        $temp = $this->compileTableExpression($sql, $constraint, $query);
92
93
        return $temp;
94
    }
95
96
    /**
97
     * Compile the limit / offset row constraint for a query.
98
     *
99
     * @param  \Illuminate\Database\Query\Builder $query
100
     * @return string
101
     */
102
    protected function compileRowConstraint($query)
103
    {
104
        $start = $query->offset + 1;
105
106
        if ($query->limit == 1) {
107
            return "= 1";
108
        }
109
110
        if ($query->limit > 1) {
111
            $finish = $query->offset + $query->limit;
112
113
            return "between {$start} and {$finish}";
114
        }
115
116
        return ">= {$start}";
117
    }
118
119
    /**
120
     * Compile a common table expression for a query.
121
     *
122
     * @param  string $sql
123
     * @param  string $constraint
124
     * @param Builder $query
125
     * @return string
126
     */
127
    protected function compileTableExpression($sql, $constraint, $query)
128
    {
129
        if ($query->limit > 1) {
130
            return "select t2.* from ( select rownum AS \"rn\", t1.* from ({$sql}) t1 ) t2 where t2.\"rn\" {$constraint}";
131
        } else {
132
            return "select * from ({$sql}) where rownum {$constraint}";
133
        }
134
    }
135
136
    /**
137
     * Compile a truncate table statement into SQL.
138
     *
139
     * @param  \Illuminate\Database\Query\Builder $query
140
     * @return array
141
     */
142
    public function compileTruncate(Builder $query)
143
    {
144
        return ['truncate table ' . $this->wrapTable($query->from) => []];
145
    }
146
147
    /**
148
     * Compile an insert and get ID statement into SQL.
149
     *
150
     * @param  \Illuminate\Database\Query\Builder $query
151
     * @param  array $values
152
     * @param  string $sequence
153
     * @return string
154
     */
155
    public function compileInsertGetId(Builder $query, $values, $sequence = 'id')
156
    {
157
        if (empty($sequence)) {
158
            $sequence = 'id';
159
        }
160
161
        $backtrace = debug_backtrace(DEBUG_BACKTRACE_PROVIDE_OBJECT, 4)[3]['object'];
162
163
        if (method_exists($backtrace, 'getModel')) {
164
            $model = $backtrace->getModel();
165
            if ($model->sequence && ! isset($values[$model->getKeyName()]) && $model->incrementing) {
166
                $values[$sequence] = null;
167
            }
168
        }
169
170
        return $this->compileInsert($query, $values) . ' returning ' . $this->wrap($sequence) . ' into ?';
171
    }
172
173
    /**
174
     * Compile an insert statement into SQL.
175
     *
176
     * @param  \Illuminate\Database\Query\Builder $query
177
     * @param  array $values
178
     * @return string
179
     */
180
    public function compileInsert(Builder $query, array $values)
181
    {
182
        // Essentially we will force every insert to be treated as a batch insert which
183
        // simply makes creating the SQL easier for us since we can utilize the same
184
        // basic routine regardless of an amount of records given to us to insert.
185
        $table = $this->wrapTable($query->from);
186
187
        if (! is_array(reset($values))) {
188
            $values = [$values];
189
        }
190
191
        $columns = $this->columnize(array_keys(reset($values)));
192
193
        // We need to build a list of parameter place-holders of values that are bound
194
        // to the query. Each insert should have the exact same amount of parameter
195
        // bindings so we can just go off the first list of values in this array.
196
        $parameters = $this->parameterize(reset($values));
197
198
        $value = array_fill(0, count($values), "($parameters)");
199
200
        if (count($value) > 1) {
201
            $insertQueries = [];
202
            foreach ($value as $parameter) {
203
                $parameter       = (str_replace(['(', ')'], '', $parameter));
204
                $insertQueries[] = "select " . $parameter . " from dual ";
205
            }
206
            $parameters = implode('union all ', $insertQueries);
207
208
            return "insert into $table ($columns) $parameters";
209
        }
210
        $parameters = implode(', ', $value);
211
212
        return "insert into $table ($columns) values $parameters";
213
    }
214
215
    /**
216
     * Compile an insert with blob field statement into SQL.
217
     *
218
     * @param  \Illuminate\Database\Query\Builder $query
219
     * @param  array $values
220
     * @param  array $binaries
221
     * @param  string $sequence
222
     * @return string
223
     */
224
    public function compileInsertLob(Builder $query, $values, $binaries, $sequence = 'id')
225
    {
226
        if (empty($sequence)) {
227
            $sequence = 'id';
228
        }
229
230
        $table = $this->wrapTable($query->from);
231
232
        if (! is_array(reset($values))) {
233
            $values = [$values];
234
        }
235
236
        if (! is_array(reset($binaries))) {
237
            $binaries = [$binaries];
238
        }
239
240
        $columns       = $this->columnize(array_keys(reset($values)));
241
        $binaryColumns = $this->columnize(array_keys(reset($binaries)));
242
        $columns .= (empty($columns) ? '' : ', ') . $binaryColumns;
243
244
        $parameters       = $this->parameterize(reset($values));
245
        $binaryParameters = $this->parameterize(reset($binaries));
246
247
        $value       = array_fill(0, count($values), "$parameters");
248
        $binaryValue = array_fill(0, count($binaries), str_replace('?', 'EMPTY_BLOB()', $binaryParameters));
249
250
        $value      = array_merge($value, $binaryValue);
251
        $parameters = implode(', ', array_filter($value));
252
253
        return "insert into $table ($columns) values ($parameters) returning " . $binaryColumns . ', ' . $this->wrap($sequence) . ' into ' . $binaryParameters . ', ?';
254
    }
255
256
    /**
257
     * Compile an update statement into SQL.
258
     *
259
     * @param  \Illuminate\Database\Query\Builder $query
260
     * @param  array $values
261
     * @param  array $binaries
262
     * @param  string $sequence
263
     * @return string
264
     */
265
    public function compileUpdateLob(Builder $query, array $values, array $binaries, $sequence = 'id')
266
    {
267
        $table = $this->wrapTable($query->from);
268
269
        // Each one of the columns in the update statements needs to be wrapped in the
270
        // keyword identifiers, also a place-holder needs to be created for each of
271
        // the values in the list of bindings so we can make the sets statements.
272
        $columns = [];
273
274
        foreach ($values as $key => $value) {
275
            $columns[] = $this->wrap($key) . ' = ' . $this->parameter($value);
276
        }
277
278
        $columns = implode(', ', $columns);
279
280
        // set blob variables
281
        if (! is_array(reset($binaries))) {
282
            $binaries = [$binaries];
283
        }
284
        $binaryColumns    = $this->columnize(array_keys(reset($binaries)));
285
        $binaryParameters = $this->parameterize(reset($binaries));
286
287
        // create EMPTY_BLOB sql for each binary
288
        $binarySql = [];
289
        foreach ((array) $binaryColumns as $binary) {
290
            $binarySql[] = "$binary = EMPTY_BLOB()";
291
        }
292
293
        // prepare binary SQLs
294
        if (count($binarySql)) {
295
            $binarySql = (empty($columns) ? '' : ', ') . implode(',', $binarySql);
296
        }
297
298
        // If the query has any "join" clauses, we will setup the joins on the builder
299
        // and compile them so we can attach them to this update, as update queries
300
        // can get join statements to attach to other tables when they're needed.
301
        if (isset($query->joins)) {
302
            $joins = ' ' . $this->compileJoins($query, $query->joins);
303
        } else {
304
            $joins = '';
305
        }
306
307
        // Of course, update queries may also be constrained by where clauses so we'll
308
        // need to compile the where clauses and attach it to the query so only the
309
        // intended records are updated by the SQL statements we generate to run.
310
        $where = $this->compileWheres($query);
311
312
        return "update {$table}{$joins} set $columns$binarySql $where returning " . $binaryColumns . ', ' . $this->wrap($sequence) . ' into ' . $binaryParameters . ', ?';
313
    }
314
315
    /**
316
     * Compile the lock into SQL.
317
     *
318
     * @param  \Illuminate\Database\Query\Builder $query
319
     * @param  bool|string $value
320
     * @return string
321
     */
322
    protected function compileLock(Builder $query, $value)
323
    {
324
        if (is_string($value)) {
325
            return $value;
326
        }
327
328
        if ($value) {
329
            return 'for update';
330
        }
331
332
        return '';
333
    }
334
335
    /**
336
     * Compile the "limit" portions of the query.
337
     *
338
     * @param  \Illuminate\Database\Query\Builder $query
339
     * @param  int $limit
340
     * @return string
341
     */
342
    protected function compileLimit(Builder $query, $limit)
343
    {
344
        return '';
345
    }
346
347
    /**
348
     * Compile the "offset" portions of the query.
349
     *
350
     * @param  \Illuminate\Database\Query\Builder $query
351
     * @param  int $offset
352
     * @return string
353
     */
354
    protected function compileOffset(Builder $query, $offset)
355
    {
356
        return '';
357
    }
358
359
    /**
360
     * Compile a "where date" clause.
361
     *
362
     * @param  \Illuminate\Database\Query\Builder $query
363
     * @param  array $where
364
     * @return string
365
     */
366
    protected function whereDate(Builder $query, $where)
367
    {
368
        $value = $this->parameter($where['value']);
369
370
        return "trunc({$this->wrap($where['column'])}) {$where['operator']} $value";
371
    }
372
373
    /**
374
     * Compile a date based where clause.
375
     *
376
     * @param  string $type
377
     * @param  \Illuminate\Database\Query\Builder $query
378
     * @param  array $where
379
     * @return string
380
     */
381
    protected function dateBasedWhere($type, Builder $query, $where)
382
    {
383
        $value = $this->parameter($where['value']);
384
385
        return "extract ($type from {$this->wrap($where['column'])}) {$where['operator']} $value";
386
    }
387
388
    /**
389
     * Wrap a single string in keyword identifiers.
390
     *
391
     * @param  string $value
392
     * @return string
393
     */
394 View Code Duplication
    protected function wrapValue($value)
0 ignored issues
show
Duplication introduced by
This method seems to be duplicated in your project.

Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.

You can also find more detailed suggestions in the “Code” section of your repository.

Loading history...
395
    {
396
        if ($this->isReserved($value)) {
397
            return parent::wrapValue($value);
398
        }
399
400
        return $value !== '*' ? sprintf($this->wrapper, $value) : $value;
401
    }
402
403
    /**
404
     * Return the schema prefix
405
     *
406
     * @return string
407
     */
408
    public function getSchemaPrefix()
409
    {
410
        return ! empty($this->schema_prefix) ? $this->schema_prefix . '.' : '';
411
    }
412
413
    /**
414
     * Set the shema prefix
415
     *
416
     * @param string $prefix
417
     */
418
    public function setSchemaPrefix($prefix)
419
    {
420
        $this->schema_prefix = $prefix;
421
    }
422
423
    /**
424
     * Wrap a table in keyword identifiers.
425
     *
426
     * @param  \Illuminate\Database\Query\Expression|string $table
427
     * @return string
428
     */
429
    public function wrapTable($table)
430
    {
431
        if ($this->isExpression($table)) {
432
            return $this->getValue($table);
0 ignored issues
show
Bug introduced by
It seems like $table defined by parameter $table on line 429 can also be of type string; however, Illuminate\Database\Grammar::getValue() does only seem to accept object<Illuminate\Database\Query\Expression>, maybe add an additional type check?

This check looks at variables that have been passed in as parameters and are passed out again to other methods.

If the outgoing method call has stricter type requirements than the method itself, an issue is raised.

An additional type check may prevent trouble.

Loading history...
433
        }
434
435
        return $this->getSchemaPrefix() . $this->wrap($this->tablePrefix . $table, true);
436
    }
437
}