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