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