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:     protected function wrapValue($value)
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);
433:         }
434: 
435:         if (strpos(strtolower($table), ' as ') !== false) {
436:             $table = str_replace(' as ', ' ', $table);
437:         }
438: 
439:         return $this->getSchemaPrefix() . $this->wrap($this->tablePrefix . $table, true);
440:     }
441: }
442: