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: 17: 18: 19:
20: protected $wrapper = '%s';
21:
22: 23: 24: 25: 26:
27: protected $modifiers = ['Increment', 'Nullable', 'Default'];
28:
29: 30: 31: 32: 33:
34: protected $serials = ['bigInteger', 'integer', 'mediumInteger', 'smallInteger', 'tinyInteger'];
35:
36: 37: 38:
39: protected $schema_prefix = '';
40:
41: 42: 43: 44: 45:
46: protected $transactions = true;
47:
48: 49: 50: 51: 52: 53: 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: 63: 64: 65: 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: 78: 79: 80: 81:
82: public function wrapTable($table)
83: {
84: return $this->getSchemaPrefix() . parent::wrapTable($table);
85: }
86:
87: 88: 89: 90: 91:
92: public function getSchemaPrefix()
93: {
94: return ! empty($this->schema_prefix) ? $this->schema_prefix . '.' : '';
95: }
96:
97: 98: 99: 100: 101:
102: public function setSchemaPrefix($prefix)
103: {
104: $this->schema_prefix = $prefix;
105: }
106:
107: 108: 109: 110: 111: 112:
113: protected function addForeignKeys(Blueprint $blueprint)
114: {
115: $sql = '';
116:
117: $foreigns = $this->getCommandsByName($blueprint, 'foreign');
118:
119:
120:
121:
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:
132:
133:
134: if (! is_null($foreign->onDelete)) {
135: $sql .= " on delete {$foreign->onDelete}";
136: }
137: }
138:
139: return $sql;
140: }
141:
142: 143: 144: 145: 146: 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: 163: 164: 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: 173: 174: 175: 176: 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: 185: 186: 187: 188: 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: 203: 204: 205: 206: 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: 223: 224: 225: 226: 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:
238:
239:
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:
249:
250:
251: if (! is_null($command->onDelete)) {
252: $sql .= " on delete {$command->onDelete}";
253: }
254:
255: return $sql;
256: }
257: }
258:
259: 260: 261: 262: 263: 264: 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: 273: 274: 275: 276: 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: 285: 286: 287: 288: 289:
290: public function compileDrop(Blueprint $blueprint, Fluent $command)
291: {
292: return 'drop table ' . $this->wrapTable($blueprint);
293: }
294:
295: 296: 297: 298: 299: 300: 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: 317: 318: 319: 320: 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: 333: 334: 335: 336: 337:
338: public function compileDropPrimary(Blueprint $blueprint, Fluent $command)
339: {
340: return $this->dropConstraint($blueprint, $command, 'primary');
341: }
342:
343: 344: 345: 346: 347: 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: 363: 364: 365: 366: 367:
368: public function compileDropUnique(Blueprint $blueprint, Fluent $command)
369: {
370: return $this->dropConstraint($blueprint, $command, 'unique');
371: }
372:
373: 374: 375: 376: 377: 378: 379:
380: public function compileDropIndex(Blueprint $blueprint, Fluent $command)
381: {
382: return $this->dropConstraint($blueprint, $command, 'index');
383: }
384:
385: 386: 387: 388: 389: 390: 391:
392: public function compileDropForeign(Blueprint $blueprint, Fluent $command)
393: {
394: return $this->dropConstraint($blueprint, $command, 'foreign');
395: }
396:
397: 398: 399: 400: 401: 402: 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: 413: 414: 415: 416: 417: 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: 431: 432: 433: 434:
435: protected function typeChar(Fluent $column)
436: {
437: return "char({$column->length})";
438: }
439:
440: 441: 442: 443: 444: 445:
446: protected function typeString(Fluent $column)
447: {
448: return "varchar2({$column->length})";
449: }
450:
451: 452: 453: 454: 455: 456:
457: protected function typeNvarchar2(Fluent $column)
458: {
459: return "nvarchar2({$column->length})";
460: }
461:
462: 463: 464: 465: 466: 467:
468: protected function typeText(Fluent $column)
469: {
470: return "clob";
471: }
472:
473: 474: 475: 476: 477: 478:
479: protected function typeMediumText(Fluent $column)
480: {
481: return 'clob';
482: }
483:
484: 485: 486: 487: 488: 489:
490: protected function typeLongText(Fluent $column)
491: {
492: return 'clob';
493: }
494:
495: 496: 497: 498: 499: 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: 510: 511: 512: 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: 523: 524: 525: 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: 536: 537: 538: 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: 549: 550: 551: 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: 562: 563: 564: 565:
566: protected function typeFloat(Fluent $column)
567: {
568: return "number({$column->total}, {$column->places})";
569: }
570:
571: 572: 573: 574: 575: 576:
577: protected function typeDouble(Fluent $column)
578: {
579: return "number({$column->total}, {$column->places})";
580: }
581:
582: 583: 584: 585: 586: 587:
588: protected function typeDecimal(Fluent $column)
589: {
590: return "number({$column->total}, {$column->places})";
591: }
592:
593: 594: 595: 596: 597: 598:
599: protected function typeBoolean(Fluent $column)
600: {
601: return "char(1)";
602: }
603:
604: 605: 606: 607: 608: 609:
610: protected function typeEnum(Fluent $column)
611: {
612: $length = ($column->length) ? $column->length : 255;
613:
614: return "varchar2({$length})";
615: }
616:
617: 618: 619: 620: 621: 622:
623: protected function typeDate(Fluent $column)
624: {
625: return 'date';
626: }
627:
628: 629: 630: 631: 632: 633:
634: protected function typeDateTime(Fluent $column)
635: {
636: return 'date';
637: }
638:
639: 640: 641: 642: 643: 644:
645: protected function typeTime(Fluent $column)
646: {
647: return 'date';
648: }
649:
650: 651: 652: 653: 654: 655:
656: protected function typeTimestamp(Fluent $column)
657: {
658: return 'timestamp';
659: }
660:
661: 662: 663: 664: 665: 666:
667: protected function typeTimestampTz(Fluent $column)
668: {
669: return 'timestamp with time zone';
670: }
671:
672: 673: 674: 675: 676: 677:
678: protected function typeBinary(Fluent $column)
679: {
680: return 'blob';
681: }
682:
683: 684: 685: 686: 687: 688: 689:
690: protected function modifyNullable(Blueprint $blueprint, Fluent $column)
691: {
692:
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: 710: 711: 712: 713: 714:
715: protected function modifyDefault(Blueprint $blueprint, Fluent $column)
716: {
717:
718: return "";
719: }
720:
721: 722: 723: 724: 725: 726: 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: 737: 738: 739: 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: