Completed
Push — master ( 8512ea...2b9498 )
by Arjay
02:00
created

OracleGrammar::compileExists()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 9
Code Lines 6

Duplication

Lines 0
Ratio 0 %

Importance

Changes 2
Bugs 0 Features 0
Metric Value
c 2
b 0
f 0
dl 0
loc 9
rs 9.6666
cc 1
eloc 6
nc 1
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
        return $this->compileInsert($query, $values) . ' returning ' . $this->wrap($sequence) . ' into ?';
162
    }
163
164
    /**
165
     * Compile an insert statement into SQL.
166
     *
167
     * @param  \Illuminate\Database\Query\Builder $query
168
     * @param  array $values
169
     * @return string
170
     */
171
    public function compileInsert(Builder $query, array $values)
172
    {
173
        // Essentially we will force every insert to be treated as a batch insert which
174
        // simply makes creating the SQL easier for us since we can utilize the same
175
        // basic routine regardless of an amount of records given to us to insert.
176
        $table = $this->wrapTable($query->from);
177
178
        if (! is_array(reset($values))) {
179
            $values = [$values];
180
        }
181
182
        $columns = $this->columnize(array_keys(reset($values)));
183
184
        // We need to build a list of parameter place-holders of values that are bound
185
        // to the query. Each insert should have the exact same amount of parameter
186
        // bindings so we can just go off the first list of values in this array.
187
        $parameters = $this->parameterize(reset($values));
188
189
        $value = array_fill(0, count($values), "($parameters)");
190
191
        if (count($value) > 1) {
192
            $insertQueries = [];
193
            foreach ($value as $parameter) {
194
                $parameter       = (str_replace(['(', ')'], '', $parameter));
195
                $insertQueries[] = "select " . $parameter . " from dual ";
196
            }
197
            $parameters = implode('union all ', $insertQueries);
198
199
            return "insert into $table ($columns) $parameters";
200
        }
201
        $parameters = implode(', ', $value);
202
203
        return "insert into $table ($columns) values $parameters";
204
    }
205
206
    /**
207
     * Compile an insert with blob field statement into SQL.
208
     *
209
     * @param  \Illuminate\Database\Query\Builder $query
210
     * @param  array $values
211
     * @param  array $binaries
212
     * @param  string $sequence
213
     * @return string
214
     */
215
    public function compileInsertLob(Builder $query, $values, $binaries, $sequence = 'id')
216
    {
217
        if (empty($sequence)) {
218
            $sequence = 'id';
219
        }
220
221
        $table = $this->wrapTable($query->from);
222
223
        if (! is_array(reset($values))) {
224
            $values = [$values];
225
        }
226
227
        if (! is_array(reset($binaries))) {
228
            $binaries = [$binaries];
229
        }
230
231
        $columns       = $this->columnize(array_keys(reset($values)));
232
        $binaryColumns = $this->columnize(array_keys(reset($binaries)));
233
        $columns .= (empty($columns) ? '' : ', ') . $binaryColumns;
234
235
        $parameters       = $this->parameterize(reset($values));
236
        $binaryParameters = $this->parameterize(reset($binaries));
237
238
        $value       = array_fill(0, count($values), "$parameters");
239
        $binaryValue = array_fill(0, count($binaries), str_replace('?', 'EMPTY_BLOB()', $binaryParameters));
240
241
        $value      = array_merge($value, $binaryValue);
242
        $parameters = implode(', ', array_filter($value));
243
244
        return "insert into $table ($columns) values ($parameters) returning " . $binaryColumns . ', ' . $this->wrap($sequence) . ' into ' . $binaryParameters . ', ?';
245
    }
246
247
    /**
248
     * Compile an update statement into SQL.
249
     *
250
     * @param  \Illuminate\Database\Query\Builder $query
251
     * @param  array $values
252
     * @param  array $binaries
253
     * @param  string $sequence
254
     * @return string
255
     */
256
    public function compileUpdateLob(Builder $query, array $values, array $binaries, $sequence = 'id')
257
    {
258
        $table = $this->wrapTable($query->from);
259
260
        // Each one of the columns in the update statements needs to be wrapped in the
261
        // keyword identifiers, also a place-holder needs to be created for each of
262
        // the values in the list of bindings so we can make the sets statements.
263
        $columns = [];
264
265
        foreach ($values as $key => $value) {
266
            $columns[] = $this->wrap($key) . ' = ' . $this->parameter($value);
267
        }
268
269
        $columns = implode(', ', $columns);
270
271
        // set blob variables
272
        if (! is_array(reset($binaries))) {
273
            $binaries = [$binaries];
274
        }
275
        $binaryColumns    = $this->columnize(array_keys(reset($binaries)));
276
        $binaryParameters = $this->parameterize(reset($binaries));
277
278
        // create EMPTY_BLOB sql for each binary
279
        $binarySql = [];
280
        foreach ((array) $binaryColumns as $binary) {
281
            $binarySql[] = "$binary = EMPTY_BLOB()";
282
        }
283
284
        // prepare binary SQLs
285
        if (count($binarySql)) {
286
            $binarySql = (empty($columns) ? '' : ', ') . implode(',', $binarySql);
287
        }
288
289
        // If the query has any "join" clauses, we will setup the joins on the builder
290
        // and compile them so we can attach them to this update, as update queries
291
        // can get join statements to attach to other tables when they're needed.
292
        if (isset($query->joins)) {
293
            $joins = ' ' . $this->compileJoins($query, $query->joins);
294
        } else {
295
            $joins = '';
296
        }
297
298
        // Of course, update queries may also be constrained by where clauses so we'll
299
        // need to compile the where clauses and attach it to the query so only the
300
        // intended records are updated by the SQL statements we generate to run.
301
        $where = $this->compileWheres($query);
302
303
        return "update {$table}{$joins} set $columns$binarySql $where returning " . $binaryColumns . ', ' . $this->wrap($sequence) . ' into ' . $binaryParameters . ', ?';
304
    }
305
306
    /**
307
     * Compile the lock into SQL.
308
     *
309
     * @param  \Illuminate\Database\Query\Builder $query
310
     * @param  bool|string $value
311
     * @return string
312
     */
313
    protected function compileLock(Builder $query, $value)
314
    {
315
        if (is_string($value)) {
316
            return $value;
317
        }
318
319
        if ($value) {
320
            return 'for update';
321
        }
322
323
        return '';
324
    }
325
326
    /**
327
     * Compile the "limit" portions of the query.
328
     *
329
     * @param  \Illuminate\Database\Query\Builder $query
330
     * @param  int $limit
331
     * @return string
332
     */
333
    protected function compileLimit(Builder $query, $limit)
334
    {
335
        return '';
336
    }
337
338
    /**
339
     * Compile the "offset" portions of the query.
340
     *
341
     * @param  \Illuminate\Database\Query\Builder $query
342
     * @param  int $offset
343
     * @return string
344
     */
345
    protected function compileOffset(Builder $query, $offset)
346
    {
347
        return '';
348
    }
349
350
    /**
351
     * Compile a "where date" clause.
352
     *
353
     * @param  \Illuminate\Database\Query\Builder $query
354
     * @param  array $where
355
     * @return string
356
     */
357
    protected function whereDate(Builder $query, $where)
358
    {
359
        $value = $this->parameter($where['value']);
360
361
        return "trunc({$this->wrap($where['column'])}) {$where['operator']} $value";
362
    }
363
364
    /**
365
     * Compile a date based where clause.
366
     *
367
     * @param  string $type
368
     * @param  \Illuminate\Database\Query\Builder $query
369
     * @param  array $where
370
     * @return string
371
     */
372
    protected function dateBasedWhere($type, Builder $query, $where)
373
    {
374
        $value = $this->parameter($where['value']);
375
376
        return "extract ($type from {$this->wrap($where['column'])}) {$where['operator']} $value";
377
    }
378
379
    /**
380
     * Wrap a single string in keyword identifiers.
381
     *
382
     * @param  string $value
383
     * @return string
384
     */
385 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...
386
    {
387
        if ($this->isReserved($value)) {
388
            return parent::wrapValue($value);
389
        }
390
391
        return $value !== '*' ? sprintf($this->wrapper, $value) : $value;
392
    }
393
394
    /**
395
     * Compile the "from" portion of the query.
396
     *
397
     * @param  \Illuminate\Database\Query\Builder $query
398
     * @param  string $table
399
     * @return string
400
     */
401
    protected function compileFrom(Builder $query, $table)
402
    {
403
404
        $query = $this->wrapTable($table);
405
406
        if ($this->isSubQuery($query)) {
407
            return 'from ' . $this->wrapTable($table);
408
        }
409
410
        return 'from ' . $this->getSchemaPrefix() . $this->wrapTable($table);
411
    }
412
413
    /**
414
     * Check if given query is a sub-query.
415
     *
416
     * @param string $query
417
     * @return bool
418
     */
419
    protected function isSubQuery($query)
420
    {
421
        return substr($query, 0, 1) == '(';
422
    }
423
424
    /**
425
     * Return the schema prefix
426
     *
427
     * @return string
428
     */
429
    public function getSchemaPrefix()
430
    {
431
        return ! empty($this->schema_prefix) ? $this->schema_prefix . '.' : '';
432
    }
433
434
    /**
435
     * Set the shema prefix
436
     *
437
     * @param string $prefix
438
     */
439
    public function setSchemaPrefix($prefix)
440
    {
441
        $this->schema_prefix = $prefix;
442
    }
443
}
444