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: 40: 41: 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: 51: 52: 53: 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: 66: 67: 68: 69:
70: protected function addForeignKeys(Blueprint $blueprint)
71: {
72: $sql = '';
73:
74: $foreigns = $this->getCommandsByName($blueprint, 'foreign');
75:
76:
77:
78:
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:
89:
90:
91: if (! is_null($foreign->onDelete)) {
92: $sql .= " on delete {$foreign->onDelete}";
93: }
94: }
95:
96: return $sql;
97: }
98:
99: 100: 101: 102: 103: 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: 120: 121: 122:
123: public function compileTableExists()
124: {
125: return "select * from user_tables where upper(table_name) = upper(?)";
126: }
127:
128:
129: 130: 131: 132: 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: 141: 142: 143: 144: 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: 159: 160: 161: 162: 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: 179: 180: 181: 182: 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:
194:
195:
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:
205:
206:
207: if (! is_null($command->onDelete)) {
208: $sql .= " on delete {$command->onDelete}";
209: }
210:
211: return $sql;
212: }
213: }
214:
215: 216: 217: 218: 219: 220: 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: 229: 230: 231: 232: 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: 241: 242: 243: 244: 245:
246: public function compileDrop(Blueprint $blueprint, Fluent $command)
247: {
248: return 'drop table ' . $this->wrapTable($blueprint);
249: }
250:
251: 252: 253: 254: 255: 256: 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: 273: 274: 275: 276: 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: 289: 290: 291: 292: 293:
294: public function compileDropPrimary(Blueprint $blueprint, Fluent $command)
295: {
296: return $this->dropConstraint($blueprint, $command, 'primary');
297: }
298:
299: 300: 301: 302: 303: 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: 319: 320: 321: 322: 323:
324: public function compileDropUnique(Blueprint $blueprint, Fluent $command)
325: {
326: return $this->dropConstraint($blueprint, $command, 'unique');
327: }
328:
329: 330: 331: 332: 333: 334: 335:
336: public function compileDropIndex(Blueprint $blueprint, Fluent $command)
337: {
338: return $this->dropConstraint($blueprint, $command, 'index');
339: }
340:
341: 342: 343: 344: 345: 346: 347:
348: public function compileDropForeign(Blueprint $blueprint, Fluent $command)
349: {
350: return $this->dropConstraint($blueprint, $command, 'foreign');
351: }
352:
353: 354: 355: 356: 357: 358: 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: 369: 370: 371: 372: 373: 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: 387: 388: 389: 390:
391: protected function typeChar(Fluent $column)
392: {
393: return "char({$column->length})";
394: }
395:
396: 397: 398: 399: 400: 401:
402: protected function typeString(Fluent $column)
403: {
404: return "varchar2({$column->length})";
405: }
406:
407: 408: 409: 410: 411: 412:
413: protected function typeNvarchar2(Fluent $column)
414: {
415: return "nvarchar2({$column->length})";
416: }
417:
418: 419: 420: 421: 422: 423:
424: protected function typeText(Fluent $column)
425: {
426: return "clob";
427: }
428:
429: 430: 431: 432: 433: 434:
435: protected function typeMediumText(Fluent $column)
436: {
437: return 'clob';
438: }
439:
440: 441: 442: 443: 444: 445:
446: protected function typeLongText(Fluent $column)
447: {
448: return 'clob';
449: }
450:
451: 452: 453: 454: 455: 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: 466: 467: 468: 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: 479: 480: 481: 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: 492: 493: 494: 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: 505: 506: 507: 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: 518: 519: 520: 521:
522: protected function typeFloat(Fluent $column)
523: {
524: return "number({$column->total}, {$column->places})";
525: }
526:
527: 528: 529: 530: 531: 532:
533: protected function typeDouble(Fluent $column)
534: {
535: return "number({$column->total}, {$column->places})";
536: }
537:
538: 539: 540: 541: 542: 543:
544: protected function typeDecimal(Fluent $column)
545: {
546: return "number({$column->total}, {$column->places})";
547: }
548:
549: 550: 551: 552: 553: 554:
555: protected function typeBoolean(Fluent $column)
556: {
557: return "char(1)";
558: }
559:
560: 561: 562: 563: 564: 565:
566: protected function typeEnum(Fluent $column)
567: {
568: $length = ($column->length) ? $column->length : 255;
569:
570: return "varchar2({$length})";
571: }
572:
573: 574: 575: 576: 577: 578:
579: protected function typeDate(Fluent $column)
580: {
581: return 'date';
582: }
583:
584: 585: 586: 587: 588: 589:
590: protected function typeDateTime(Fluent $column)
591: {
592: return 'date';
593: }
594:
595: 596: 597: 598: 599: 600:
601: protected function typeTime(Fluent $column)
602: {
603: return 'date';
604: }
605:
606: 607: 608: 609: 610: 611:
612: protected function typeTimestamp(Fluent $column)
613: {
614: return 'timestamp';
615: }
616:
617: 618: 619: 620: 621: 622:
623: protected function typeTimestampTz(Fluent $column)
624: {
625: return 'timestamp with time zone';
626: }
627:
628: 629: 630: 631: 632: 633:
634: protected function typeBinary(Fluent $column)
635: {
636: return 'blob';
637: }
638:
639: 640: 641: 642: 643: 644: 645:
646: protected function modifyNullable(Blueprint $blueprint, Fluent $column)
647: {
648:
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: 666: 667: 668: 669: 670:
671: protected function modifyDefault(Blueprint $blueprint, Fluent $column)
672: {
673:
674: return "";
675: }
676:
677: 678: 679: 680: 681: 682: 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: 693: 694: 695: 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: