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:      * Compile an exists statement into SQL.
 22:      *
 23:      * @param \Illuminate\Database\Query\Builder $query
 24:      * @return string
 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:      * Compile a select query into SQL.
 38:      *
 39:      * @param  \Illuminate\Database\Query\Builder
 40:      * @return string
 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:         // If an offset is present on the query, we will need to wrap the query in
 51:         // a big "ANSI" offset syntax block. This is very nasty compared to the
 52:         // other database systems but is necessary for implementing features.
 53:         if ($this->isPaginationable($query, $components)) {
 54:             return $this->compileAnsiOffset($query, $components);
 55:         }
 56: 
 57:         return trim($this->concatenate($components));
 58:     }
 59: 
 60:     /**
 61:      * @param Builder $query
 62:      * @param array $components
 63:      * @return bool
 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:      * Create a full ANSI offset clause for the query.
 72:      *
 73:      * @param  \Illuminate\Database\Query\Builder $query
 74:      * @param  array $components
 75:      * @return string
 76:      */
 77:     protected function compileAnsiOffset(Builder $query, $components)
 78:     {
 79:         $constraint = $this->compileRowConstraint($query);
 80: 
 81:         $sql = $this->concatenate($components);
 82: 
 83:         // We are now ready to build the final SQL query so we'll create a common table
 84:         // expression from the query and get the records with row numbers within our
 85:         // given limit and offset value that we just put on as a query constraint.
 86:         $temp = $this->compileTableExpression($sql, $constraint, $query);
 87: 
 88:         return $temp;
 89:     }
 90: 
 91:     /**
 92:      * Compile the limit / offset row constraint for a query.
 93:      *
 94:      * @param  \Illuminate\Database\Query\Builder $query
 95:      * @return string
 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:      * Compile a common table expression for a query.
116:      *
117:      * @param  string $sql
118:      * @param  string $constraint
119:      * @param Builder $query
120:      * @return string
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:      * Compile a truncate table statement into SQL.
133:      *
134:      * @param  \Illuminate\Database\Query\Builder $query
135:      * @return array
136:      */
137:     public function compileTruncate(Builder $query)
138:     {
139:         return ['truncate table ' . $this->wrapTable($query->from) => []];
140:     }
141: 
142:     /**
143:      * Wrap a table in keyword identifiers.
144:      *
145:      * @param  \Illuminate\Database\Query\Expression|string $table
146:      * @return string
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:      * Compile an insert and get ID statement into SQL.
163:      *
164:      * @param  \Illuminate\Database\Query\Builder $query
165:      * @param  array $values
166:      * @param  string $sequence
167:      * @return string
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:      * Compile an insert statement into SQL.
180:      *
181:      * @param  \Illuminate\Database\Query\Builder $query
182:      * @param  array $values
183:      * @return string
184:      */
185:     public function compileInsert(Builder $query, array $values)
186:     {
187:         // Essentially we will force every insert to be treated as a batch insert which
188:         // simply makes creating the SQL easier for us since we can utilize the same
189:         // basic routine regardless of an amount of records given to us to insert.
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:         // We need to build a list of parameter place-holders of values that are bound
199:         // to the query. Each insert should have the exact same amount of parameter
200:         // bindings so we can just go off the first list of values in this array.
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:      * Compile an insert with blob field statement into SQL.
222:      *
223:      * @param  \Illuminate\Database\Query\Builder $query
224:      * @param  array $values
225:      * @param  array $binaries
226:      * @param  string $sequence
227:      * @return string
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:      * Compile an update statement into SQL.
263:      *
264:      * @param  \Illuminate\Database\Query\Builder $query
265:      * @param  array $values
266:      * @param  array $binaries
267:      * @param  string $sequence
268:      * @return string
269:      */
270:     public function compileUpdateLob(Builder $query, array $values, array $binaries, $sequence = 'id')
271:     {
272:         $table = $this->wrapTable($query->from);
273: 
274:         // Each one of the columns in the update statements needs to be wrapped in the
275:         // keyword identifiers, also a place-holder needs to be created for each of
276:         // the values in the list of bindings so we can make the sets statements.
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:         // set blob variables
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:         // create EMPTY_BLOB sql for each binary
293:         $binarySql = [];
294:         foreach ((array) $binaryColumns as $binary) {
295:             $binarySql[] = "$binary = EMPTY_BLOB()";
296:         }
297: 
298:         // prepare binary SQLs
299:         if (count($binarySql)) {
300:             $binarySql = (empty($columns) ? '' : ', ') . implode(',', $binarySql);
301:         }
302: 
303:         // If the query has any "join" clauses, we will setup the joins on the builder
304:         // and compile them so we can attach them to this update, as update queries
305:         // can get join statements to attach to other tables when they're needed.
306:         if (isset($query->joins)) {
307:             $joins = ' ' . $this->compileJoins($query, $query->joins);
308:         } else {
309:             $joins = '';
310:         }
311: 
312:         // Of course, update queries may also be constrained by where clauses so we'll
313:         // need to compile the where clauses and attach it to the query so only the
314:         // intended records are updated by the SQL statements we generate to run.
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:      * Compile the lock into SQL.
322:      *
323:      * @param  \Illuminate\Database\Query\Builder $query
324:      * @param  bool|string $value
325:      * @return string
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:      * Compile the "limit" portions of the query.
342:      *
343:      * @param  \Illuminate\Database\Query\Builder $query
344:      * @param  int $limit
345:      * @return string
346:      */
347:     protected function compileLimit(Builder $query, $limit)
348:     {
349:         return '';
350:     }
351: 
352:     /**
353:      * Compile the "offset" portions of the query.
354:      *
355:      * @param  \Illuminate\Database\Query\Builder $query
356:      * @param  int $offset
357:      * @return string
358:      */
359:     protected function compileOffset(Builder $query, $offset)
360:     {
361:         return '';
362:     }
363: 
364:     /**
365:      * Compile a "where date" clause.
366:      *
367:      * @param  \Illuminate\Database\Query\Builder $query
368:      * @param  array $where
369:      * @return string
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:      * Compile a date based where clause.
380:      *
381:      * @param  string $type
382:      * @param  \Illuminate\Database\Query\Builder $query
383:      * @param  array $where
384:      * @return string
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:      * Wrap a single string in keyword identifiers.
395:      *
396:      * @param  string $value
397:      * @return string
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: