1: <?php
  2: 
  3: namespace Yajra\Oci8\Schema\Grammars;
  4: 
  5: use Illuminate\Database\Connection;
  6: use Illuminate\Database\Schema\Blueprint;
  7: use Illuminate\Database\Schema\Grammars\Grammar;
  8: use Illuminate\Support\Fluent;
  9: use Yajra\Oci8\OracleReservedWords;
 10: 
 11: class OracleGrammar extends Grammar
 12: {
 13:     use OracleReservedWords;
 14: 
 15:     /**
 16:      * The keyword identifier wrapper format.
 17:      *
 18:      * @var string
 19:      */
 20:     protected $wrapper = '%s';
 21: 
 22:     /**
 23:      * The possible column modifiers.
 24:      *
 25:      * @var array
 26:      */
 27:     protected $modifiers = ['Increment', 'Nullable', 'Default'];
 28: 
 29:     /**
 30:      * The possible column serials
 31:      *
 32:      * @var array
 33:      */
 34:     protected $serials = ['bigInteger', 'integer', 'mediumInteger', 'smallInteger', 'tinyInteger'];
 35: 
 36:     /**
 37:      * Compile a create table command.
 38:      *
 39:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
 40:      * @param  \Illuminate\Support\Fluent $command
 41:      * @return string
 42:      */
 43:     public function compileCreate(Blueprint $blueprint, Fluent $command)
 44:     {
 45:         $columns = implode(', ', $this->getColumns($blueprint));
 46: 
 47:         $sql = 'create table ' . $this->wrapTable($blueprint) . " ( $columns";
 48: 
 49:         /**
 50:          * To be able to name the primary/foreign keys when the table is
 51:          * initially created we will need to check for a primary/foreign
 52:          * key commands and add the columns to the table's declaration
 53:          * here so they can be created on the tables.
 54:          */
 55:         $sql .= (string) $this->addForeignKeys($blueprint);
 56: 
 57:         $sql .= (string) $this->addPrimaryKeys($blueprint);
 58: 
 59:         $sql .= ' )';
 60: 
 61:         return $sql;
 62:     }
 63: 
 64:     /**
 65:      * Get the foreign key syntax for a table creation statement.
 66:      *
 67:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
 68:      * @return string
 69:      */
 70:     protected function addForeignKeys(Blueprint $blueprint)
 71:     {
 72:         $sql = '';
 73: 
 74:         $foreigns = $this->getCommandsByName($blueprint, 'foreign');
 75: 
 76:         // Once we have all the foreign key commands for the table creation statement
 77:         // we'll loop through each of them and add them to the create table SQL we
 78:         // are building
 79:         foreach ($foreigns as $foreign) {
 80:             $on = $this->wrapTable($foreign->on);
 81: 
 82:             $columns = $this->columnize($foreign->columns);
 83: 
 84:             $onColumns = $this->columnize((array) $foreign->references);
 85: 
 86:             $sql .= ", constraint {$foreign->index} foreign key ( {$columns} ) references {$on} ( {$onColumns} )";
 87: 
 88:             // Once we have the basic foreign key creation statement constructed we can
 89:             // build out the syntax for what should happen on an update or delete of
 90:             // the affected columns, which will get something like "cascade", etc.
 91:             if (! is_null($foreign->onDelete)) {
 92:                 $sql .= " on delete {$foreign->onDelete}";
 93:             }
 94:         }
 95: 
 96:         return $sql;
 97:     }
 98: 
 99:     /**
100:      * Get the primary key syntax for a table creation statement.
101:      *
102:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
103:      * @return string|null
104:      */
105:     protected function addPrimaryKeys(Blueprint $blueprint)
106:     {
107:         $primary = $this->getCommandByName($blueprint, 'primary');
108: 
109:         if (! is_null($primary)) {
110:             $columns = $this->columnize($primary->columns);
111: 
112:             return ", constraint {$primary->index} primary key ( {$columns} )";
113:         }
114: 
115:         return "";
116:     }
117: 
118:     /**
119:      * Compile the query to determine if a table exists.
120:      *
121:      * @return string
122:      */
123:     public function compileTableExists()
124:     {
125:         return "select * from user_tables where upper(table_name) = upper(?)";
126:     }
127: 
128: 
129:     /**
130:      * Compile the query to determine the list of columns.
131:      *
132:      * @return string
133:      */
134:     public function compileColumnExists($table)
135:     {
136:         return "select column_name from user_tab_columns where table_name = upper('".$table."')";
137:     }
138: 
139:     /**
140:      * Compile an add column command.
141:      *
142:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
143:      * @param  \Illuminate\Support\Fluent $command
144:      * @return string
145:      */
146:     public function compileAdd(Blueprint $blueprint, Fluent $command)
147:     {
148:         $columns = implode(', ', $this->getColumns($blueprint));
149: 
150:         $sql = 'alter table ' . $this->wrapTable($blueprint) . " add ( $columns";
151: 
152:         $sql .= (string) $this->addPrimaryKeys($blueprint);
153: 
154:         return $sql .= ' )';
155:     }
156: 
157:     /**
158:      * Compile a primary key command.
159:      *
160:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
161:      * @param  \Illuminate\Support\Fluent $command
162:      * @return string
163:      */
164:     public function compilePrimary(Blueprint $blueprint, Fluent $command)
165:     {
166:         $create = $this->getCommandByName($blueprint, 'create');
167: 
168:         if (is_null($create)) {
169:             $columns = $this->columnize($command->columns);
170: 
171:             $table = $this->wrapTable($blueprint);
172: 
173:             return "alter table {$table} add constraint {$command->index} primary key ({$columns})";
174:         }
175:     }
176: 
177:     /**
178:      * Compile a foreign key command.
179:      *
180:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
181:      * @param  \Illuminate\Support\Fluent $command
182:      * @return string|void
183:      */
184:     public function compileForeign(Blueprint $blueprint, Fluent $command)
185:     {
186:         $create = $this->getCommandByName($blueprint, 'create');
187: 
188:         if (is_null($create)) {
189:             $table = $this->wrapTable($blueprint);
190: 
191:             $on = $this->wrapTable($command->on);
192: 
193:             // We need to prepare several of the elements of the foreign key definition
194:             // before we can create the SQL, such as wrapping the tables and convert
195:             // an array of columns to comma-delimited strings for the SQL queries.
196:             $columns = $this->columnize($command->columns);
197: 
198:             $onColumns = $this->columnize((array) $command->references);
199: 
200:             $sql = "alter table {$table} add constraint {$command->index} ";
201: 
202:             $sql .= "foreign key ( {$columns} ) references {$on} ( {$onColumns} )";
203: 
204:             // Once we have the basic foreign key creation statement constructed we can
205:             // build out the syntax for what should happen on an update or delete of
206:             // the affected columns, which will get something like "cascade", etc.
207:             if (! is_null($command->onDelete)) {
208:                 $sql .= " on delete {$command->onDelete}";
209:             }
210: 
211:             return $sql;
212:         }
213:     }
214: 
215:     /**
216:      * Compile a unique key command.
217:      *
218:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
219:      * @param  \Illuminate\Support\Fluent $command
220:      * @return string
221:      */
222:     public function compileUnique(Blueprint $blueprint, Fluent $command)
223:     {
224:         return "alter table " . $this->wrapTable($blueprint) . " add constraint {$command->index} unique ( " . $this->columnize($command->columns) . " )";
225:     }
226: 
227:     /**
228:      * Compile a plain index key command.
229:      *
230:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
231:      * @param  \Illuminate\Support\Fluent $command
232:      * @return string
233:      */
234:     public function compileIndex(Blueprint $blueprint, Fluent $command)
235:     {
236:         return "create index {$command->index} on " . $this->wrapTable($blueprint) . " ( " . $this->columnize($command->columns) . " )";
237:     }
238: 
239:     /**
240:      * Compile a drop table command.
241:      *
242:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
243:      * @param  \Illuminate\Support\Fluent $command
244:      * @return string
245:      */
246:     public function compileDrop(Blueprint $blueprint, Fluent $command)
247:     {
248:         return 'drop table ' . $this->wrapTable($blueprint);
249:     }
250: 
251:     /**
252:      * Compile a drop table (if exists) command.
253:      *
254:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
255:      * @param  \Illuminate\Support\Fluent $command
256:      * @return string
257:      */
258:     public function compileDropIfExists(Blueprint $blueprint, Fluent $command)
259:     {
260:         $table = $this->wrapTable($blueprint);
261: 
262:         return "declare c int;
263:             begin
264:                select count(*) into c from user_tables where table_name = upper('$table');
265:                if c = 1 then
266:                   execute immediate 'drop table $table';
267:                end if;
268:             end;";
269:     }
270: 
271:     /**
272:      * Compile a drop column command.
273:      *
274:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
275:      * @param  \Illuminate\Support\Fluent $command
276:      * @return string
277:      */
278:     public function compileDropColumn(Blueprint $blueprint, Fluent $command)
279:     {
280:         $columns = $this->wrapArray($command->columns);
281: 
282:         $table = $this->wrapTable($blueprint);
283: 
284:         return 'alter table ' . $table . ' drop ( ' . implode(', ', $columns) . ' )';
285:     }
286: 
287:     /**
288:      * Compile a drop primary key command.
289:      *
290:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
291:      * @param  \Illuminate\Support\Fluent $command
292:      * @return string
293:      */
294:     public function compileDropPrimary(Blueprint $blueprint, Fluent $command)
295:     {
296:         return $this->dropConstraint($blueprint, $command, 'primary');
297:     }
298: 
299:     /**
300:      * @param Blueprint $blueprint
301:      * @param Fluent $command
302:      * @param string $type
303:      * @return string
304:      */
305:     private function dropConstraint(Blueprint $blueprint, Fluent $command, $type)
306:     {
307:         $table = $this->wrapTable($blueprint);
308:         $index = substr($command->index, 0, 30);
309: 
310:         if ($type === 'index') {
311:             return "drop index {$index}";
312:         }
313: 
314:         return "alter table {$table} drop constraint {$index}";
315:     }
316: 
317:     /**
318:      * Compile a drop unique key command.
319:      *
320:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
321:      * @param  \Illuminate\Support\Fluent $command
322:      * @return string
323:      */
324:     public function compileDropUnique(Blueprint $blueprint, Fluent $command)
325:     {
326:         return $this->dropConstraint($blueprint, $command, 'unique');
327:     }
328: 
329:     /**
330:      * Compile a drop index command.
331:      *
332:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
333:      * @param  \Illuminate\Support\Fluent $command
334:      * @return string
335:      */
336:     public function compileDropIndex(Blueprint $blueprint, Fluent $command)
337:     {
338:         return $this->dropConstraint($blueprint, $command, 'index');
339:     }
340: 
341:     /**
342:      * Compile a drop foreign key command.
343:      *
344:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
345:      * @param  \Illuminate\Support\Fluent $command
346:      * @return string
347:      */
348:     public function compileDropForeign(Blueprint $blueprint, Fluent $command)
349:     {
350:         return $this->dropConstraint($blueprint, $command, 'foreign');
351:     }
352: 
353:     /**
354:      * Compile a rename table command.
355:      *
356:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
357:      * @param  \Illuminate\Support\Fluent $command
358:      * @return string
359:      */
360:     public function compileRename(Blueprint $blueprint, Fluent $command)
361:     {
362:         $from = $this->wrapTable($blueprint);
363: 
364:         return "alter table {$from} rename to " . $this->wrapTable($command->to);
365:     }
366: 
367:     /**
368:      * Compile a rename column command.
369:      *
370:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
371:      * @param  \Illuminate\Support\Fluent $command
372:      * @param  \Illuminate\Database\Connection $connection
373:      * @return array
374:      */
375:     public function compileRenameColumn(Blueprint $blueprint, Fluent $command, Connection $connection)
376:     {
377:         $table = $this->wrapTable($blueprint);
378: 
379:         $rs    = [];
380:         $rs[0] = 'alter table ' . $table . ' rename column ' . $command->from . ' to ' . $command->to;
381: 
382:         return (array) $rs;
383:     }
384: 
385:     /**
386:      * Create the column definition for a char type.
387:      *
388:      * @param  \Illuminate\Support\Fluent $column
389:      * @return string
390:      */
391:     protected function typeChar(Fluent $column)
392:     {
393:         return "char({$column->length})";
394:     }
395: 
396:     /**
397:      * Create the column definition for a string type.
398:      *
399:      * @param  \Illuminate\Support\Fluent $column
400:      * @return string
401:      */
402:     protected function typeString(Fluent $column)
403:     {
404:         return "varchar2({$column->length})";
405:     }
406: 
407:     /**
408:      * Create column definition for a nvarchar type.
409:      *
410:      * @param \Illuminate\Support\Fluent $column
411:      * @return string
412:      */
413:     protected function typeNvarchar2(Fluent $column)
414:     {
415:         return "nvarchar2({$column->length})";
416:     }
417: 
418:     /**
419:      * Create the column definition for a text type.
420:      *
421:      * @param  \Illuminate\Support\Fluent $column
422:      * @return string
423:      */
424:     protected function typeText(Fluent $column)
425:     {
426:         return "clob";
427:     }
428: 
429:     /**
430:      * Create the column definition for a medium text type.
431:      *
432:      * @param  \Illuminate\Support\Fluent $column
433:      * @return string
434:      */
435:     protected function typeMediumText(Fluent $column)
436:     {
437:         return 'clob';
438:     }
439: 
440:     /**
441:      * Create the column definition for a long text type.
442:      *
443:      * @param  \Illuminate\Support\Fluent $column
444:      * @return string
445:      */
446:     protected function typeLongText(Fluent $column)
447:     {
448:         return 'clob';
449:     }
450: 
451:     /**
452:      * Create the column definition for a integer type.
453:      *
454:      * @param  \Illuminate\Support\Fluent $column
455:      * @return string
456:      */
457:     protected function typeInteger(Fluent $column)
458:     {
459:         $length = ($column->length) ? $column->length : 10;
460: 
461:         return "number({$length},0)";
462:     }
463: 
464:     /**
465:      * Create the column definition for a integer type.
466:      *
467:      * @param  \Illuminate\Support\Fluent $column
468:      * @return string
469:      */
470:     protected function typeBigInteger(Fluent $column)
471:     {
472:         $length = ($column->length) ? $column->length : 19;
473: 
474:         return "number({$length},0)";
475:     }
476: 
477:     /**
478:      * Create the column definition for a medium integer type.
479:      *
480:      * @param  \Illuminate\Support\Fluent $column
481:      * @return string
482:      */
483:     protected function typeMediumInteger(Fluent $column)
484:     {
485:         $length = ($column->length) ? $column->length : 7;
486: 
487:         return "number({$length},0)";
488:     }
489: 
490:     /**
491:      * Create the column definition for a small integer type.
492:      *
493:      * @param  \Illuminate\Support\Fluent $column
494:      * @return string
495:      */
496:     protected function typeSmallInteger(Fluent $column)
497:     {
498:         $length = ($column->length) ? $column->length : 5;
499: 
500:         return "number({$length},0)";
501:     }
502: 
503:     /**
504:      * Create the column definition for a tiny integer type.
505:      *
506:      * @param  \Illuminate\Support\Fluent $column
507:      * @return string
508:      */
509:     protected function typeTinyInteger(Fluent $column)
510:     {
511:         $length = ($column->length) ? $column->length : 3;
512: 
513:         return "number({$length},0)";
514:     }
515: 
516:     /**
517:      * Create the column definition for a float type.
518:      *
519:      * @param  \Illuminate\Support\Fluent $column
520:      * @return string
521:      */
522:     protected function typeFloat(Fluent $column)
523:     {
524:         return "number({$column->total}, {$column->places})";
525:     }
526: 
527:     /**
528:      * Create the column definition for a double type.
529:      *
530:      * @param  \Illuminate\Support\Fluent $column
531:      * @return string
532:      */
533:     protected function typeDouble(Fluent $column)
534:     {
535:         return "number({$column->total}, {$column->places})";
536:     }
537: 
538:     /**
539:      * Create the column definition for a decimal type.
540:      *
541:      * @param  \Illuminate\Support\Fluent $column
542:      * @return string
543:      */
544:     protected function typeDecimal(Fluent $column)
545:     {
546:         return "number({$column->total}, {$column->places})";
547:     }
548: 
549:     /**
550:      * Create the column definition for a boolean type.
551:      *
552:      * @param  \Illuminate\Support\Fluent $column
553:      * @return string
554:      */
555:     protected function typeBoolean(Fluent $column)
556:     {
557:         return "char(1)";
558:     }
559: 
560:     /**
561:      * Create the column definition for a enum type.
562:      *
563:      * @param  \Illuminate\Support\Fluent $column
564:      * @return string
565:      */
566:     protected function typeEnum(Fluent $column)
567:     {
568:         $length = ($column->length) ? $column->length : 255;
569: 
570:         return "varchar2({$length})";
571:     }
572: 
573:     /**
574:      * Create the column definition for a date type.
575:      *
576:      * @param  \Illuminate\Support\Fluent $column
577:      * @return string
578:      */
579:     protected function typeDate(Fluent $column)
580:     {
581:         return 'date';
582:     }
583: 
584:     /**
585:      * Create the column definition for a date-time type.
586:      *
587:      * @param  \Illuminate\Support\Fluent $column
588:      * @return string
589:      */
590:     protected function typeDateTime(Fluent $column)
591:     {
592:         return 'date';
593:     }
594: 
595:     /**
596:      * Create the column definition for a time type.
597:      *
598:      * @param  \Illuminate\Support\Fluent $column
599:      * @return string
600:      */
601:     protected function typeTime(Fluent $column)
602:     {
603:         return 'date';
604:     }
605: 
606:     /**
607:      * Create the column definition for a timestamp type.
608:      *
609:      * @param  \Illuminate\Support\Fluent $column
610:      * @return string
611:      */
612:     protected function typeTimestamp(Fluent $column)
613:     {
614:         return 'timestamp';
615:     }
616: 
617:     /**
618:      * Create the column definition for a timestamp type with timezone.
619:      *
620:      * @param Fluent $column
621:      * @return string
622:      */
623:     protected function typeTimestampTz(Fluent $column)
624:     {
625:         return 'timestamp with time zone';
626:     }
627: 
628:     /**
629:      * Create the column definition for a binary type.
630:      *
631:      * @param  \Illuminate\Support\Fluent $column
632:      * @return string
633:      */
634:     protected function typeBinary(Fluent $column)
635:     {
636:         return 'blob';
637:     }
638: 
639:     /**
640:      * Get the SQL for a nullable column modifier.
641:      *
642:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
643:      * @param  \Illuminate\Support\Fluent $column
644:      * @return string
645:      */
646:     protected function modifyNullable(Blueprint $blueprint, Fluent $column)
647:     {
648:         // check if field is declared as enum
649:         $enum = "";
650:         if (count((array) $column->allowed)) {
651:             $enum = " check ({$column->name} in ('" . implode("', '", $column->allowed) . "'))";
652:         }
653: 
654:         $null = $column->nullable ? ' null' : ' not null';
655:         $null .= $enum;
656: 
657:         if (! is_null($column->default)) {
658:             return " default " . $this->getDefaultValue($column->default) . $null;
659:         }
660: 
661:         return $null;
662:     }
663: 
664:     /**
665:      * Get the SQL for a default column modifier.
666:      *
667:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
668:      * @param  \Illuminate\Support\Fluent $column
669:      * @return string
670:      */
671:     protected function modifyDefault(Blueprint $blueprint, Fluent $column)
672:     {
673:         // implemented @modifyNullable
674:         return "";
675:     }
676: 
677:     /**
678:      * Get the SQL for an auto-increment column modifier.
679:      *
680:      * @param  \Illuminate\Database\Schema\Blueprint $blueprint
681:      * @param  \Illuminate\Support\Fluent $column
682:      * @return string|null
683:      */
684:     protected function modifyIncrement(Blueprint $blueprint, Fluent $column)
685:     {
686:         if (in_array($column->type, $this->serials) && $column->autoIncrement) {
687:             $blueprint->primary($column->name);
688:         }
689:     }
690: 
691:     /**
692:      * Wrap a single string in keyword identifiers.
693:      *
694:      * @param  string $value
695:      * @return string
696:      */
697:     protected function wrapValue($value)
698:     {
699:         if ($this->isReserved($value)) {
700:             return parent::wrapValue($value);
701:         }
702: 
703:         return $value !== '*' ? sprintf($this->wrapper, $value) : $value;
704:     }
705: }
706: