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: 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: 56: 57: 58: 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: 71: 72: 73: 74:
75: public function wrapTable($table)
76: {
77: return $this->getSchemaPrefix() . parent::wrapTable($table);
78: }
79:
80: 81: 82: 83: 84:
85: public function getSchemaPrefix()
86: {
87: return ! empty($this->schema_prefix) ? $this->schema_prefix . '.' : '';
88: }
89:
90: 91: 92: 93: 94:
95: public function setSchemaPrefix($prefix)
96: {
97: $this->schema_prefix = $prefix;
98: }
99:
100: 101: 102: 103: 104: 105:
106: protected function addForeignKeys(Blueprint $blueprint)
107: {
108: $sql = '';
109:
110: $foreigns = $this->getCommandsByName($blueprint, 'foreign');
111:
112:
113:
114:
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:
125:
126:
127: if (! is_null($foreign->onDelete)) {
128: $sql .= " on delete {$foreign->onDelete}";
129: }
130: }
131:
132: return $sql;
133: }
134:
135: 136: 137: 138: 139: 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: 156: 157: 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: 166: 167: 168: 169: 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: 178: 179: 180: 181: 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: 196: 197: 198: 199: 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: 216: 217: 218: 219: 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:
231:
232:
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:
242:
243:
244: if (! is_null($command->onDelete)) {
245: $sql .= " on delete {$command->onDelete}";
246: }
247:
248: return $sql;
249: }
250: }
251:
252: 253: 254: 255: 256: 257: 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: 266: 267: 268: 269: 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: 278: 279: 280: 281: 282:
283: public function compileDrop(Blueprint $blueprint, Fluent $command)
284: {
285: return 'drop table ' . $this->wrapTable($blueprint);
286: }
287:
288: 289: 290: 291: 292: 293: 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: 310: 311: 312: 313: 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: 326: 327: 328: 329: 330:
331: public function compileDropPrimary(Blueprint $blueprint, Fluent $command)
332: {
333: return $this->dropConstraint($blueprint, $command, 'primary');
334: }
335:
336: 337: 338: 339: 340: 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: 356: 357: 358: 359: 360:
361: public function compileDropUnique(Blueprint $blueprint, Fluent $command)
362: {
363: return $this->dropConstraint($blueprint, $command, 'unique');
364: }
365:
366: 367: 368: 369: 370: 371: 372:
373: public function compileDropIndex(Blueprint $blueprint, Fluent $command)
374: {
375: return $this->dropConstraint($blueprint, $command, 'index');
376: }
377:
378: 379: 380: 381: 382: 383: 384:
385: public function compileDropForeign(Blueprint $blueprint, Fluent $command)
386: {
387: return $this->dropConstraint($blueprint, $command, 'foreign');
388: }
389:
390: 391: 392: 393: 394: 395: 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: 406: 407: 408: 409: 410: 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: 424: 425: 426: 427:
428: protected function typeChar(Fluent $column)
429: {
430: return "char({$column->length})";
431: }
432:
433: 434: 435: 436: 437: 438:
439: protected function typeString(Fluent $column)
440: {
441: return "varchar2({$column->length})";
442: }
443:
444: 445: 446: 447: 448: 449:
450: protected function typeNvarchar2(Fluent $column)
451: {
452: return "nvarchar2({$column->length})";
453: }
454:
455: 456: 457: 458: 459: 460:
461: protected function typeText(Fluent $column)
462: {
463: return "clob";
464: }
465:
466: 467: 468: 469: 470: 471:
472: protected function typeMediumText(Fluent $column)
473: {
474: return 'clob';
475: }
476:
477: 478: 479: 480: 481: 482:
483: protected function typeLongText(Fluent $column)
484: {
485: return 'clob';
486: }
487:
488: 489: 490: 491: 492: 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: 503: 504: 505: 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: 516: 517: 518: 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: 529: 530: 531: 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: 542: 543: 544: 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: 555: 556: 557: 558:
559: protected function typeFloat(Fluent $column)
560: {
561: return "number({$column->total}, {$column->places})";
562: }
563:
564: 565: 566: 567: 568: 569:
570: protected function typeDouble(Fluent $column)
571: {
572: return "number({$column->total}, {$column->places})";
573: }
574:
575: 576: 577: 578: 579: 580:
581: protected function typeDecimal(Fluent $column)
582: {
583: return "number({$column->total}, {$column->places})";
584: }
585:
586: 587: 588: 589: 590: 591:
592: protected function typeBoolean(Fluent $column)
593: {
594: return "char(1)";
595: }
596:
597: 598: 599: 600: 601: 602:
603: protected function typeEnum(Fluent $column)
604: {
605: $length = ($column->length) ? $column->length : 255;
606:
607: return "varchar2({$length})";
608: }
609:
610: 611: 612: 613: 614: 615:
616: protected function typeDate(Fluent $column)
617: {
618: return 'date';
619: }
620:
621: 622: 623: 624: 625: 626:
627: protected function typeDateTime(Fluent $column)
628: {
629: return 'date';
630: }
631:
632: 633: 634: 635: 636: 637:
638: protected function typeTime(Fluent $column)
639: {
640: return 'date';
641: }
642:
643: 644: 645: 646: 647: 648:
649: protected function typeTimestamp(Fluent $column)
650: {
651: return 'timestamp';
652: }
653:
654: 655: 656: 657: 658: 659:
660: protected function typeTimestampTz(Fluent $column)
661: {
662: return 'timestamp with time zone';
663: }
664:
665: 666: 667: 668: 669: 670:
671: protected function typeBinary(Fluent $column)
672: {
673: return 'blob';
674: }
675:
676: 677: 678: 679: 680: 681: 682:
683: protected function modifyNullable(Blueprint $blueprint, Fluent $column)
684: {
685:
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: 703: 704: 705: 706: 707:
708: protected function modifyDefault(Blueprint $blueprint, Fluent $column)
709: {
710:
711: return "";
712: }
713:
714: 715: 716: 717: 718: 719: 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: 730: 731: 732: 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: