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: 24: 25:
26: public function compileExists(Builder $query)
27: {
28: $q = clone $query;
29: $q->columns = [];
30: $q->selectRaw('1 as "exists"')
31: ->whereRaw("rownum = 1");
32:
33: return $this->compileSelect($q);
34: }
35:
36: 37: 38: 39: 40: 41:
42: public function compileSelect(Builder $query)
43: {
44: if (is_null($query->columns)) {
45: $query->columns = ['*'];
46: }
47:
48: $components = $this->compileComponents($query);
49:
50:
51:
52:
53: if ($this->isPaginationable($query, $components)) {
54: return $this->compileAnsiOffset($query, $components);
55: }
56:
57: return trim($this->concatenate($components));
58: }
59:
60: 61: 62: 63: 64:
65: protected function isPaginationable(Builder $query, array $components)
66: {
67: return ($query->limit > 0 || $query->offset > 0) && ! array_key_exists('lock', $components);
68: }
69:
70: 71: 72: 73: 74: 75: 76:
77: protected function compileAnsiOffset(Builder $query, $components)
78: {
79: $constraint = $this->compileRowConstraint($query);
80:
81: $sql = $this->concatenate($components);
82:
83:
84:
85:
86: $temp = $this->compileTableExpression($sql, $constraint, $query);
87:
88: return $temp;
89: }
90:
91: 92: 93: 94: 95: 96:
97: protected function compileRowConstraint($query)
98: {
99: $start = $query->offset + 1;
100:
101: if ($query->limit == 1) {
102: return "= 1";
103: }
104:
105: if ($query->limit > 1) {
106: $finish = $query->offset + $query->limit;
107:
108: return "between {$start} and {$finish}";
109: }
110:
111: return ">= {$start}";
112: }
113:
114: 115: 116: 117: 118: 119: 120: 121:
122: protected function compileTableExpression($sql, $constraint, $query)
123: {
124: if ($query->limit > 1) {
125: return "select t2.* from ( select rownum AS \"rn\", t1.* from ({$sql}) t1 ) t2 where t2.\"rn\" {$constraint}";
126: } else {
127: return "select * from ({$sql}) where rownum {$constraint}";
128: }
129: }
130:
131: 132: 133: 134: 135: 136:
137: public function compileTruncate(Builder $query)
138: {
139: return ['truncate table ' . $this->wrapTable($query->from) => []];
140: }
141:
142: 143: 144: 145: 146: 147:
148: public function wrapTable($table)
149: {
150: if ($this->isExpression($table)) {
151: return $this->getValue($table);
152: }
153:
154: if (strpos(strtolower($table), ' as ') !== false) {
155: $table = str_replace(' as ', ' ', $table);
156: }
157:
158: return $this->wrap($this->tablePrefix . $table, true);
159: }
160:
161: 162: 163: 164: 165: 166: 167: 168:
169: public function compileInsertGetId(Builder $query, $values, $sequence = 'id')
170: {
171: if (empty($sequence)) {
172: $sequence = 'id';
173: }
174:
175: return $this->compileInsert($query, $values) . ' returning ' . $this->wrap($sequence) . ' into ?';
176: }
177:
178: 179: 180: 181: 182: 183: 184:
185: public function compileInsert(Builder $query, array $values)
186: {
187:
188:
189:
190: $table = $this->wrapTable($query->from);
191:
192: if (! is_array(reset($values))) {
193: $values = [$values];
194: }
195:
196: $columns = $this->columnize(array_keys(reset($values)));
197:
198:
199:
200:
201: $parameters = $this->parameterize(reset($values));
202:
203: $value = array_fill(0, count($values), "($parameters)");
204:
205: if (count($value) > 1) {
206: $insertQueries = [];
207: foreach ($value as $parameter) {
208: $parameter = (str_replace(['(', ')'], '', $parameter));
209: $insertQueries[] = "select " . $parameter . " from dual ";
210: }
211: $parameters = implode('union all ', $insertQueries);
212:
213: return "insert into $table ($columns) $parameters";
214: }
215: $parameters = implode(', ', $value);
216:
217: return "insert into $table ($columns) values $parameters";
218: }
219:
220: 221: 222: 223: 224: 225: 226: 227: 228:
229: public function compileInsertLob(Builder $query, $values, $binaries, $sequence = 'id')
230: {
231: if (empty($sequence)) {
232: $sequence = 'id';
233: }
234:
235: $table = $this->wrapTable($query->from);
236:
237: if (! is_array(reset($values))) {
238: $values = [$values];
239: }
240:
241: if (! is_array(reset($binaries))) {
242: $binaries = [$binaries];
243: }
244:
245: $columns = $this->columnize(array_keys(reset($values)));
246: $binaryColumns = $this->columnize(array_keys(reset($binaries)));
247: $columns .= (empty($columns) ? '' : ', ') . $binaryColumns;
248:
249: $parameters = $this->parameterize(reset($values));
250: $binaryParameters = $this->parameterize(reset($binaries));
251:
252: $value = array_fill(0, count($values), "$parameters");
253: $binaryValue = array_fill(0, count($binaries), str_replace('?', 'EMPTY_BLOB()', $binaryParameters));
254:
255: $value = array_merge($value, $binaryValue);
256: $parameters = implode(', ', array_filter($value));
257:
258: return "insert into $table ($columns) values ($parameters) returning " . $binaryColumns . ', ' . $this->wrap($sequence) . ' into ' . $binaryParameters . ', ?';
259: }
260:
261: 262: 263: 264: 265: 266: 267: 268: 269:
270: public function compileUpdateLob(Builder $query, array $values, array $binaries, $sequence = 'id')
271: {
272: $table = $this->wrapTable($query->from);
273:
274:
275:
276:
277: $columns = [];
278:
279: foreach ($values as $key => $value) {
280: $columns[] = $this->wrap($key) . ' = ' . $this->parameter($value);
281: }
282:
283: $columns = implode(', ', $columns);
284:
285:
286: if (! is_array(reset($binaries))) {
287: $binaries = [$binaries];
288: }
289: $binaryColumns = $this->columnize(array_keys(reset($binaries)));
290: $binaryParameters = $this->parameterize(reset($binaries));
291:
292:
293: $binarySql = [];
294: foreach ((array) $binaryColumns as $binary) {
295: $binarySql[] = "$binary = EMPTY_BLOB()";
296: }
297:
298:
299: if (count($binarySql)) {
300: $binarySql = (empty($columns) ? '' : ', ') . implode(',', $binarySql);
301: }
302:
303:
304:
305:
306: if (isset($query->joins)) {
307: $joins = ' ' . $this->compileJoins($query, $query->joins);
308: } else {
309: $joins = '';
310: }
311:
312:
313:
314:
315: $where = $this->compileWheres($query);
316:
317: return "update {$table}{$joins} set $columns$binarySql $where returning " . $binaryColumns . ', ' . $this->wrap($sequence) . ' into ' . $binaryParameters . ', ?';
318: }
319:
320: 321: 322: 323: 324: 325: 326:
327: protected function compileLock(Builder $query, $value)
328: {
329: if (is_string($value)) {
330: return $value;
331: }
332:
333: if ($value) {
334: return 'for update';
335: }
336:
337: return '';
338: }
339:
340: 341: 342: 343: 344: 345: 346:
347: protected function compileLimit(Builder $query, $limit)
348: {
349: return '';
350: }
351:
352: 353: 354: 355: 356: 357: 358:
359: protected function compileOffset(Builder $query, $offset)
360: {
361: return '';
362: }
363:
364: 365: 366: 367: 368: 369: 370:
371: protected function whereDate(Builder $query, $where)
372: {
373: $value = $this->parameter($where['value']);
374:
375: return "trunc({$this->wrap($where['column'])}) {$where['operator']} $value";
376: }
377:
378: 379: 380: 381: 382: 383: 384: 385:
386: protected function dateBasedWhere($type, Builder $query, $where)
387: {
388: $value = $this->parameter($where['value']);
389:
390: return "extract ($type from {$this->wrap($where['column'])}) {$where['operator']} $value";
391: }
392:
393: 394: 395: 396: 397: 398:
399: protected function wrapValue($value)
400: {
401: if ($this->isReserved($value)) {
402: return parent::wrapValue($value);
403: }
404:
405: return $value !== '*' ? sprintf($this->wrapper, $value) : $value;
406: }
407: }
408: