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: 15: 16: 17:
18: protected $wrapper = '%s';
19:
20: 21: 22:
23: protected $schema_prefix = '';
24:
25: 26: 27: 28: 29: 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: 43: 44: 45: 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:
56:
57:
58: if ($this->isPaginationable($query, $components)) {
59: return $this->compileAnsiOffset($query, $components);
60: }
61:
62: return trim($this->concatenate($components));
63: }
64:
65: 66: 67: 68: 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: 77: 78: 79: 80: 81:
82: protected function compileAnsiOffset(Builder $query, $components)
83: {
84: $constraint = $this->compileRowConstraint($query);
85:
86: $sql = $this->concatenate($components);
87:
88:
89:
90:
91: $temp = $this->compileTableExpression($sql, $constraint, $query);
92:
93: return $temp;
94: }
95:
96: 97: 98: 99: 100: 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: 121: 122: 123: 124: 125: 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: 138: 139: 140: 141:
142: public function compileTruncate(Builder $query)
143: {
144: return ['truncate table ' . $this->wrapTable($query->from) => []];
145: }
146:
147: 148: 149: 150: 151: 152:
153: public function wrapTable($table)
154: {
155: if ($this->isExpression($table)) {
156: return $this->getValue($table);
157: }
158:
159: if (strpos(strtolower($table), ' as ') !== false) {
160: $table = str_replace(' as ', ' ', $table);
161: }
162:
163: return $this->getSchemaPrefix() . $this->wrap($this->tablePrefix . $table, true);
164: }
165:
166: 167: 168: 169: 170:
171: public function getSchemaPrefix()
172: {
173: return ! empty($this->schema_prefix) ? $this->schema_prefix . '.' : '';
174: }
175:
176: 177: 178: 179: 180:
181: public function setSchemaPrefix($prefix)
182: {
183: $this->schema_prefix = $prefix;
184: }
185:
186: 187: 188: 189: 190: 191: 192: 193:
194: public function compileInsertGetId(Builder $query, $values, $sequence = 'id')
195: {
196: if (empty($sequence)) {
197: $sequence = 'id';
198: }
199:
200: $backtrace = debug_backtrace(DEBUG_BACKTRACE_PROVIDE_OBJECT, 4)[3]['object'];
201:
202: if (method_exists($backtrace, 'getModel')) {
203: $model = $backtrace->getModel();
204: if ($model->sequence && ! isset($values[$model->getKeyName()]) && $model->incrementing) {
205: $values[$sequence] = null;
206: }
207: }
208:
209: return $this->compileInsert($query, $values) . ' returning ' . $this->wrap($sequence) . ' into ?';
210: }
211:
212: 213: 214: 215: 216: 217: 218:
219: public function compileInsert(Builder $query, array $values)
220: {
221:
222:
223:
224: $table = $this->wrapTable($query->from);
225:
226: if (! is_array(reset($values))) {
227: $values = [$values];
228: }
229:
230: $columns = $this->columnize(array_keys(reset($values)));
231:
232:
233:
234:
235: $parameters = $this->parameterize(reset($values));
236:
237: $value = array_fill(0, count($values), "($parameters)");
238:
239: if (count($value) > 1) {
240: $insertQueries = [];
241: foreach ($value as $parameter) {
242: $parameter = (str_replace(['(', ')'], '', $parameter));
243: $insertQueries[] = "select " . $parameter . " from dual ";
244: }
245: $parameters = implode('union all ', $insertQueries);
246:
247: return "insert into $table ($columns) $parameters";
248: }
249: $parameters = implode(', ', $value);
250:
251: return "insert into $table ($columns) values $parameters";
252: }
253:
254: 255: 256: 257: 258: 259: 260: 261: 262:
263: public function compileInsertLob(Builder $query, $values, $binaries, $sequence = 'id')
264: {
265: if (empty($sequence)) {
266: $sequence = 'id';
267: }
268:
269: $table = $this->wrapTable($query->from);
270:
271: if (! is_array(reset($values))) {
272: $values = [$values];
273: }
274:
275: if (! is_array(reset($binaries))) {
276: $binaries = [$binaries];
277: }
278:
279: $columns = $this->columnize(array_keys(reset($values)));
280: $binaryColumns = $this->columnize(array_keys(reset($binaries)));
281: $columns .= (empty($columns) ? '' : ', ') . $binaryColumns;
282:
283: $parameters = $this->parameterize(reset($values));
284: $binaryParameters = $this->parameterize(reset($binaries));
285:
286: $value = array_fill(0, count($values), "$parameters");
287: $binaryValue = array_fill(0, count($binaries), str_replace('?', 'EMPTY_BLOB()', $binaryParameters));
288:
289: $value = array_merge($value, $binaryValue);
290: $parameters = implode(', ', array_filter($value));
291:
292: return "insert into $table ($columns) values ($parameters) returning " . $binaryColumns . ', ' . $this->wrap($sequence) . ' into ' . $binaryParameters . ', ?';
293: }
294:
295: 296: 297: 298: 299: 300: 301: 302: 303:
304: public function compileUpdateLob(Builder $query, array $values, array $binaries, $sequence = 'id')
305: {
306: $table = $this->wrapTable($query->from);
307:
308:
309:
310:
311: $columns = [];
312:
313: foreach ($values as $key => $value) {
314: $columns[] = $this->wrap($key) . ' = ' . $this->parameter($value);
315: }
316:
317: $columns = implode(', ', $columns);
318:
319:
320: if (! is_array(reset($binaries))) {
321: $binaries = [$binaries];
322: }
323: $binaryColumns = $this->columnize(array_keys(reset($binaries)));
324: $binaryParameters = $this->parameterize(reset($binaries));
325:
326:
327: $binarySql = [];
328: foreach ((array) $binaryColumns as $binary) {
329: $binarySql[] = "$binary = EMPTY_BLOB()";
330: }
331:
332:
333: if (count($binarySql)) {
334: $binarySql = (empty($columns) ? '' : ', ') . implode(',', $binarySql);
335: }
336:
337:
338:
339:
340: if (isset($query->joins)) {
341: $joins = ' ' . $this->compileJoins($query, $query->joins);
342: } else {
343: $joins = '';
344: }
345:
346:
347:
348:
349: $where = $this->compileWheres($query);
350:
351: return "update {$table}{$joins} set $columns$binarySql $where returning " . $binaryColumns . ', ' . $this->wrap($sequence) . ' into ' . $binaryParameters . ', ?';
352: }
353:
354: 355: 356: 357: 358: 359: 360:
361: protected function compileLock(Builder $query, $value)
362: {
363: if (is_string($value)) {
364: return $value;
365: }
366:
367: if ($value) {
368: return 'for update';
369: }
370:
371: return '';
372: }
373:
374: 375: 376: 377: 378: 379: 380:
381: protected function compileLimit(Builder $query, $limit)
382: {
383: return '';
384: }
385:
386: 387: 388: 389: 390: 391: 392:
393: protected function compileOffset(Builder $query, $offset)
394: {
395: return '';
396: }
397:
398: 399: 400: 401: 402: 403: 404:
405: protected function whereDate(Builder $query, $where)
406: {
407: $value = $this->parameter($where['value']);
408:
409: return "trunc({$this->wrap($where['column'])}) {$where['operator']} $value";
410: }
411:
412: 413: 414: 415: 416: 417: 418: 419:
420: protected function dateBasedWhere($type, Builder $query, $where)
421: {
422: $value = $this->parameter($where['value']);
423:
424: return "extract ($type from {$this->wrap($where['column'])}) {$where['operator']} $value";
425: }
426:
427: 428: 429: 430: 431: 432:
433: protected function wrapValue($value)
434: {
435: if ($this->isReserved($value)) {
436: return parent::wrapValue($value);
437: }
438:
439: return $value !== '*' ? sprintf($this->wrapper, $value) : $value;
440: }
441: }
442: