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:      * Wrap a table in keyword identifiers.
149:      *
150:      * @param  \Illuminate\Database\Query\Expression|string $table
151:      * @return string
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:      * Return the schema prefix
168:      *
169:      * @return string
170:      */
171:     public function getSchemaPrefix()
172:     {
173:         return ! empty($this->schema_prefix) ? $this->schema_prefix . '.' : '';
174:     }
175: 
176:     /**
177:      * Set the shema prefix
178:      *
179:      * @param string $prefix
180:      */
181:     public function setSchemaPrefix($prefix)
182:     {
183:         $this->schema_prefix = $prefix;
184:     }
185: 
186:     /**
187:      * Compile an insert and get ID statement into SQL.
188:      *
189:      * @param  \Illuminate\Database\Query\Builder $query
190:      * @param  array $values
191:      * @param  string $sequence
192:      * @return string
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:      * Compile an insert statement into SQL.
214:      *
215:      * @param  \Illuminate\Database\Query\Builder $query
216:      * @param  array $values
217:      * @return string
218:      */
219:     public function compileInsert(Builder $query, array $values)
220:     {
221:         // Essentially we will force every insert to be treated as a batch insert which
222:         // simply makes creating the SQL easier for us since we can utilize the same
223:         // basic routine regardless of an amount of records given to us to insert.
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:         // We need to build a list of parameter place-holders of values that are bound
233:         // to the query. Each insert should have the exact same amount of parameter
234:         // bindings so we can just go off the first list of values in this array.
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:      * Compile an insert with blob field statement into SQL.
256:      *
257:      * @param  \Illuminate\Database\Query\Builder $query
258:      * @param  array $values
259:      * @param  array $binaries
260:      * @param  string $sequence
261:      * @return string
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:      * Compile an update statement into SQL.
297:      *
298:      * @param  \Illuminate\Database\Query\Builder $query
299:      * @param  array $values
300:      * @param  array $binaries
301:      * @param  string $sequence
302:      * @return string
303:      */
304:     public function compileUpdateLob(Builder $query, array $values, array $binaries, $sequence = 'id')
305:     {
306:         $table = $this->wrapTable($query->from);
307: 
308:         // Each one of the columns in the update statements needs to be wrapped in the
309:         // keyword identifiers, also a place-holder needs to be created for each of
310:         // the values in the list of bindings so we can make the sets statements.
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:         // set blob variables
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:         // create EMPTY_BLOB sql for each binary
327:         $binarySql = [];
328:         foreach ((array) $binaryColumns as $binary) {
329:             $binarySql[] = "$binary = EMPTY_BLOB()";
330:         }
331: 
332:         // prepare binary SQLs
333:         if (count($binarySql)) {
334:             $binarySql = (empty($columns) ? '' : ', ') . implode(',', $binarySql);
335:         }
336: 
337:         // If the query has any "join" clauses, we will setup the joins on the builder
338:         // and compile them so we can attach them to this update, as update queries
339:         // can get join statements to attach to other tables when they're needed.
340:         if (isset($query->joins)) {
341:             $joins = ' ' . $this->compileJoins($query, $query->joins);
342:         } else {
343:             $joins = '';
344:         }
345: 
346:         // Of course, update queries may also be constrained by where clauses so we'll
347:         // need to compile the where clauses and attach it to the query so only the
348:         // intended records are updated by the SQL statements we generate to run.
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:      * Compile the lock into SQL.
356:      *
357:      * @param  \Illuminate\Database\Query\Builder $query
358:      * @param  bool|string $value
359:      * @return string
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:      * Compile the "limit" portions of the query.
376:      *
377:      * @param  \Illuminate\Database\Query\Builder $query
378:      * @param  int $limit
379:      * @return string
380:      */
381:     protected function compileLimit(Builder $query, $limit)
382:     {
383:         return '';
384:     }
385: 
386:     /**
387:      * Compile the "offset" portions of the query.
388:      *
389:      * @param  \Illuminate\Database\Query\Builder $query
390:      * @param  int $offset
391:      * @return string
392:      */
393:     protected function compileOffset(Builder $query, $offset)
394:     {
395:         return '';
396:     }
397: 
398:     /**
399:      * Compile a "where date" clause.
400:      *
401:      * @param  \Illuminate\Database\Query\Builder $query
402:      * @param  array $where
403:      * @return string
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:      * Compile a date based where clause.
414:      *
415:      * @param  string $type
416:      * @param  \Illuminate\Database\Query\Builder $query
417:      * @param  array $where
418:      * @return string
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:      * Wrap a single string in keyword identifiers.
429:      *
430:      * @param  string $value
431:      * @return string
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: