1: | <?php
|
2: | |
3: | |
4: | |
5: | |
6: | |
7: | |
8: | |
9: | |
10: | |
11: | |
12: | |
13: | |
14: |
|
15: |
|
16: | namespace LucidFrame\Core;
|
17: |
|
18: | use LucidFrame\Console\Command;
|
19: |
|
20: | |
21: | |
22: |
|
23: | class SchemaManager
|
24: | {
|
25: |
|
26: | protected $schema = array();
|
27: |
|
28: | private $driver = 'mysql';
|
29: |
|
30: | private $defaultOptions;
|
31: |
|
32: | private static $dataTypes = array(
|
33: | 'mysql' => array(
|
34: | 'tinyint' => 'TINYINT',
|
35: | 'smallint' => 'SMALLINT',
|
36: | 'mediumint' => 'MEDIUMINT',
|
37: | 'int' => 'INT',
|
38: | 'integer' => 'INT',
|
39: | 'bigint' => 'BIGINT',
|
40: | 'decimal' => 'NUMERIC',
|
41: | 'float' => 'DOUBLE',
|
42: |
|
43: |
|
44: |
|
45: |
|
46: | 'string' => 'VARCHAR',
|
47: | 'char' => 'CHAR',
|
48: | 'binary' => 'VARBINARY',
|
49: | 'tinytext' => 'TINYTEXT',
|
50: | 'text' => 'TEXT',
|
51: | 'mediumtext'=> 'MEDIUMTEXT',
|
52: | 'longtext' => 'LONGTEXT',
|
53: | 'tinyblob' => 'TINYBLOB',
|
54: | 'blob' => 'BLOB',
|
55: | 'mediumblob'=> 'MEDIUMBLOB',
|
56: | 'longblob' => 'LONGBLOB',
|
57: | 'array' => 'TEXT',
|
58: | 'json' => 'TEXT',
|
59: |
|
60: |
|
61: |
|
62: |
|
63: | 'boolean' => 'TINYINT',
|
64: | 'date' => 'DATE',
|
65: | 'datetime' => 'DATETIME',
|
66: | 'time' => 'TIME',
|
67: | ),
|
68: | );
|
69: |
|
70: | public static $relationships = array('1:m', 'm:1', 'm:m', '1:1');
|
71: |
|
72: | private $dbNamespace = 'default';
|
73: |
|
74: | private $sqlStatements = array();
|
75: |
|
76: | private $sqlExtension = '.sqlc';
|
77: |
|
78: | private $droppedTables = array();
|
79: |
|
80: | private $addedTables = array();
|
81: |
|
82: | private $droppedColumns = array();
|
83: |
|
84: | private $addedColumns = array();
|
85: |
|
86: | private $tablesRenamed = array();
|
87: |
|
88: | private $columnsRenamed = array();
|
89: |
|
90: | |
91: | |
92: | |
93: | |
94: |
|
95: | public function __construct($schema = array(), $dbNamespace = null)
|
96: | {
|
97: | $this->defaultOptions = array(
|
98: | 'timestamps' => true,
|
99: | 'constraints' => true,
|
100: | 'charset' => 'utf8mb4',
|
101: | 'collate' => 'utf8mb4_general_ci',
|
102: | 'engine' => 'InnoDB',
|
103: | );
|
104: |
|
105: | $this->setSchema($schema);
|
106: |
|
107: | if ($dbNamespace) {
|
108: | $this->dbNamespace = $dbNamespace;
|
109: | }
|
110: | }
|
111: |
|
112: | |
113: | |
114: | |
115: | |
116: |
|
117: | public function setSchema($schema)
|
118: | {
|
119: | if (!is_array($schema)) {
|
120: | $schema = array(
|
121: | '_options' => $this->defaultOptions
|
122: | );
|
123: | }
|
124: |
|
125: | $this->schema = $schema;
|
126: |
|
127: | return $this;
|
128: | }
|
129: |
|
130: | |
131: | |
132: | |
133: |
|
134: | public function getSchema()
|
135: | {
|
136: | return $this->schema;
|
137: | }
|
138: |
|
139: | |
140: | |
141: | |
142: | |
143: | |
144: |
|
145: | private function setDriver($driver)
|
146: | {
|
147: | $this->driver = $driver;
|
148: |
|
149: | return $this;
|
150: | }
|
151: |
|
152: | |
153: | |
154: | |
155: |
|
156: | public function getDriver()
|
157: | {
|
158: | return $this->driver;
|
159: | }
|
160: |
|
161: | |
162: | |
163: | |
164: | |
165: |
|
166: | public function setDbNamespace($namespace)
|
167: | {
|
168: | $this->dbNamespace = $namespace;
|
169: |
|
170: | return $this;
|
171: | }
|
172: |
|
173: | |
174: | |
175: | |
176: |
|
177: | public function getDbNamespace()
|
178: | {
|
179: | return $this->dbNamespace;
|
180: | }
|
181: |
|
182: | |
183: | |
184: | |
185: |
|
186: | private function getPKDefaultType()
|
187: | {
|
188: | return array(
|
189: | 'type' => 'int',
|
190: | 'autoinc' => true,
|
191: | 'null' => false,
|
192: | 'unsigned' => true
|
193: | );
|
194: | }
|
195: |
|
196: | |
197: | |
198: | |
199: | |
200: | |
201: |
|
202: | private function getRelationOptions($relation, $fkTable = '')
|
203: | {
|
204: | if (empty($relation)) {
|
205: | $relation = array('name' => $fkTable . '_id');
|
206: | }
|
207: |
|
208: | if (_arrayAssoc($relation)) {
|
209: | $relations[] = $relation;
|
210: | } else {
|
211: | $relations = $relation;
|
212: | }
|
213: |
|
214: | foreach ($relations as $i => $rel) {
|
215: | if (!isset($rel['name'])) {
|
216: | $rel['name'] = $fkTable . '_id';
|
217: | }
|
218: |
|
219: | $relations[$i] = $rel + array(
|
220: | 'unique' => false,
|
221: | 'default' => null,
|
222: | 'cascade' => false
|
223: | );
|
224: | }
|
225: |
|
226: | return $relations;
|
227: | }
|
228: |
|
229: | |
230: | |
231: | |
232: | |
233: | |
234: | |
235: |
|
236: | public function getFieldStatement($field, $definition, $collate = null)
|
237: | {
|
238: | $type = $this->getVendorFieldType($definition);
|
239: | if ($type === null) {
|
240: | return '';
|
241: | }
|
242: |
|
243: | $statement = "`{$field}` {$type}";
|
244: |
|
245: | $length = $this->getFieldLength($definition);
|
246: | if ($length) {
|
247: | $statement .= "($length)";
|
248: | }
|
249: |
|
250: | if (in_array($definition['type'], array('string', 'char', 'text', 'array', 'json'))) {
|
251: |
|
252: | $statement .= ' COLLATE ';
|
253: | $statement .= $collate ? $collate : $this->schema['_options']['collate'];
|
254: | }
|
255: |
|
256: | if (isset($definition['unsigned'])) {
|
257: |
|
258: | $statement .= ' unsigned';
|
259: | }
|
260: |
|
261: | if (isset($definition['null'])) {
|
262: |
|
263: |
|
264: | $statement .= $definition['null'] ? ' DEFAULT NULL' : ' NOT NULL';
|
265: | }
|
266: |
|
267: | if (isset($definition['default'])) {
|
268: | $statement .= sprintf(" DEFAULT '%s'", $definition['default']);
|
269: | }
|
270: |
|
271: | if (isset($definition['autoinc']) && $definition['autoinc']) {
|
272: |
|
273: | $statement .= ' AUTO_INCREMENT';
|
274: | }
|
275: |
|
276: | return $statement;
|
277: | }
|
278: |
|
279: | |
280: | |
281: | |
282: | |
283: |
|
284: | public function getVendorFieldType(&$definition)
|
285: | {
|
286: | if (!isset(self::$dataTypes[$this->driver][$definition['type']])) {
|
287: |
|
288: | return null;
|
289: | }
|
290: |
|
291: | $type = self::$dataTypes[$this->driver][$definition['type']];
|
292: |
|
293: | if (in_array($definition['type'], array('text', 'blob', 'array', 'json'))) {
|
294: | if (isset($definition['length']) && in_array($definition['length'], array('tiny', 'medium', 'long'))) {
|
295: | return strtoupper($definition['length']) . $type;
|
296: | } else {
|
297: | return $definition['type'] == 'blob' ? self::$dataTypes[$this->driver]['blob'] : self::$dataTypes[$this->driver]['text'];
|
298: | }
|
299: | }
|
300: |
|
301: | if ($definition['type'] == 'boolean') {
|
302: |
|
303: | $definition['unsigned'] = true;
|
304: |
|
305: | if (!isset($definition['default'])) {
|
306: | $definition['default'] = false;
|
307: | }
|
308: |
|
309: | if (!isset($definition['null'])) {
|
310: | $definition['null'] = false;
|
311: | } else {
|
312: | if ($definition['null'] === true) {
|
313: | $definition['default'] = null;
|
314: | }
|
315: | }
|
316: | }
|
317: |
|
318: | return $type;
|
319: | }
|
320: |
|
321: | |
322: | |
323: | |
324: | |
325: |
|
326: | public function getFieldLength(&$definition)
|
327: | {
|
328: | $type = $definition['type'];
|
329: |
|
330: | if ($type == 'string' || $type == 'char') {
|
331: | $length = 255;
|
332: | } elseif ($type == 'int' || $type == 'integer') {
|
333: | $length = 11;
|
334: | } elseif ($type === 'boolean') {
|
335: | $length = 1;
|
336: | } elseif (in_array($type, array('text', 'blob', 'array', 'json'))) {
|
337: | $length = 0;
|
338: | } elseif ($type == 'decimal' || $type == 'float') {
|
339: | $length = isset($definition['length']) ? $definition['length'] : 0;
|
340: | if (is_array($length) && count($length) == 2) {
|
341: | $length = implode(', ', $length);
|
342: | }
|
343: | } else {
|
344: | $length = 0;
|
345: | }
|
346: |
|
347: | if (isset($definition['length']) && is_numeric($definition['length'])) {
|
348: | $length = $definition['length'];
|
349: | }
|
350: |
|
351: | return $length;
|
352: | }
|
353: |
|
354: | |
355: | |
356: | |
357: | |
358: | |
359: |
|
360: | protected function getFKField($fkTable, $relation)
|
361: | {
|
362: | $field = $relation['name'];
|
363: | $pkFields = $this->schema['_options']['pk'];
|
364: |
|
365: | if (isset($pkFields[$fkTable][$field])) {
|
366: | $fkField = $pkFields[$fkTable][$field];
|
367: | } else {
|
368: | $keys = array_keys($pkFields[$fkTable]);
|
369: | $firstPKField = array_shift($keys);
|
370: | $fkField = $pkFields[$fkTable][$firstPKField];
|
371: | }
|
372: |
|
373: | if (isset($fkField['autoinc'])) {
|
374: | unset($fkField['autoinc']);
|
375: | }
|
376: |
|
377: | if ($relation['unique']) {
|
378: | $fkField['unique'] = true;
|
379: | }
|
380: |
|
381: | if ($relation['default'] === null) {
|
382: | $fkField['null'] = true;
|
383: | } else {
|
384: | $fkField['default'] = $relation['default'];
|
385: | $fkField['null'] = false;
|
386: | }
|
387: |
|
388: | return $fkField;
|
389: | }
|
390: |
|
391: | |
392: | |
393: | |
394: | |
395: | |
396: | |
397: |
|
398: | protected function getFKConstraint($fkTable, $relation, $schema = array())
|
399: | {
|
400: | if ($this->schema['_options']['constraints']) {
|
401: | $pkFields = $this->schema['_options']['pk'];
|
402: | $field = $relation['name'];
|
403: | $refField = $field;
|
404: |
|
405: | if (!isset($pkFields[$fkTable][$refField])) {
|
406: | $refField = 'id';
|
407: | }
|
408: |
|
409: | if ($relation['cascade'] === true) {
|
410: | $cascade = 'CASCADE';
|
411: | } elseif ($relation['cascade'] === null) {
|
412: | $cascade = 'SET NULL';
|
413: | } else {
|
414: | $cascade = 'RESTRICT';
|
415: | }
|
416: |
|
417: | return array(
|
418: | 'name' => 'FK_' . strtoupper(_randomCode(15)),
|
419: | 'fields' => $field,
|
420: | 'reference_table' => $fkTable,
|
421: | 'reference_fields' => $refField,
|
422: | 'on_delete' => $cascade,
|
423: | 'on_update' => 'NO ACTION'
|
424: | );
|
425: | }
|
426: |
|
427: | return null;
|
428: | }
|
429: |
|
430: | |
431: | |
432: | |
433: |
|
434: | private function load()
|
435: | {
|
436: | $schema = $this->schema;
|
437: | unset($schema['_options']);
|
438: |
|
439: | if (count($schema) == 0) {
|
440: | return false;
|
441: | }
|
442: |
|
443: |
|
444: | $this->populatePrimaryKeys($schema);
|
445: |
|
446: | $constraints = $this->populatePivots($schema);
|
447: |
|
448: | $pkFields = $this->getPrimaryKeys();
|
449: |
|
450: | $sql = array();
|
451: | $sql[] = 'SET FOREIGN_KEY_CHECKS=0;';
|
452: |
|
453: |
|
454: | foreach ($schema as $table => $def) {
|
455: | $fullTableName = db_table($table);
|
456: | $createSql = $this->createTableStatement($table, $schema, $pkFields, $constraints);
|
457: | if ($createSql) {
|
458: | $sql[] = '--';
|
459: | $sql[] = '-- Table structure for table `' . $fullTableName . '`';
|
460: | $sql[] = '--';
|
461: | $sql[] = "DROP TABLE IF EXISTS `{$fullTableName}`;";
|
462: | $sql[] = $createSql;
|
463: | }
|
464: | }
|
465: |
|
466: |
|
467: | $constraintSql = $this->createConstraintStatements($constraints);
|
468: | if ($constraintSql) {
|
469: | $sql = array_merge($sql, $constraintSql);
|
470: | }
|
471: |
|
472: | $sql[] = 'SET FOREIGN_KEY_CHECKS=1;';
|
473: |
|
474: | $this->sqlStatements = $sql;
|
475: |
|
476: |
|
477: | $versions = $this->checkVersions($schema);
|
478: | if (is_array($versions) && count($versions)) {
|
479: | $currentVersion = str_replace($this->sqlExtension, '', array_pop($versions));
|
480: | } else {
|
481: | $currentVersion = 0;
|
482: | }
|
483: |
|
484: | $this->schema['_options']['version'] = $currentVersion;
|
485: | $schema['_options'] = $this->schema['_options'];
|
486: | $this->schema = $schema;
|
487: |
|
488: | return true;
|
489: | }
|
490: |
|
491: | |
492: | |
493: | |
494: | |
495: | |
496: |
|
497: | public function build($dbNamespace = null, $backup = false)
|
498: | {
|
499: | if (!$this->isLoaded()) {
|
500: | $this->load();
|
501: | }
|
502: |
|
503: | if ($dbNamespace === null) {
|
504: | $dbNamespace = $this->dbNamespace;
|
505: | }
|
506: |
|
507: | $fileName = self::getSchemaLockFileName($dbNamespace);
|
508: | $result = file_put_contents($fileName, serialize($this->schema));
|
509: | if ($result) {
|
510: | if ($backup) {
|
511: | copy($fileName, self::getSchemaLockFileName($dbNamespace, true));
|
512: | }
|
513: |
|
514: | return true;
|
515: | }
|
516: |
|
517: | return false;
|
518: | }
|
519: |
|
520: | |
521: | |
522: | |
523: | |
524: |
|
525: | public function import($dbNamespace = null)
|
526: | {
|
527: | if ($dbNamespace === null) {
|
528: | $dbNamespace = $this->dbNamespace;
|
529: | }
|
530: |
|
531: | if (!$this->isLoaded()) {
|
532: | $this->load();
|
533: | }
|
534: |
|
535: | if ($this->executeQueries($dbNamespace, $this->sqlStatements)) {
|
536: | $this->build($dbNamespace);
|
537: | return true;
|
538: | }
|
539: |
|
540: | return false;
|
541: | }
|
542: |
|
543: | |
544: | |
545: | |
546: | |
547: |
|
548: | public function export($dbNamespace = null)
|
549: | {
|
550: | if ($dbNamespace === null) {
|
551: | $dbNamespace = $this->dbNamespace;
|
552: | }
|
553: |
|
554: | $this->build($dbNamespace);
|
555: |
|
556: | if (!count($this->sqlStatements)) {
|
557: | return false;
|
558: | }
|
559: |
|
560: | $dump = '--' . PHP_EOL
|
561: | . '-- Generated by PHPLucidFrame ' . _version() . PHP_EOL
|
562: | . '-- ' . date('r') . PHP_EOL
|
563: | . '-- ;' . PHP_EOL . PHP_EOL
|
564: | . implode(PHP_EOL, $this->sqlStatements);
|
565: |
|
566: | return file_put_contents(DB . 'generated' . _DS_ . 'schema.' . $dbNamespace . '.sql', $dump) ? true : false;
|
567: | }
|
568: |
|
569: | |
570: | |
571: | |
572: | |
573: | |
574: |
|
575: | public function update(Command $cmd, $dbNamespace = null)
|
576: | {
|
577: | if ($dbNamespace === null) {
|
578: | $dbNamespace = $this->dbNamespace;
|
579: | }
|
580: |
|
581: | $schemaFrom = self::getSchemaLockDefinition($dbNamespace);
|
582: |
|
583: | if (!$this->isLoaded()) {
|
584: | $this->load();
|
585: | }
|
586: |
|
587: | $schemaTo = $this->schema;
|
588: | $isSchemaChanged = $this->isSchemaChanged($schemaFrom, $schemaTo);
|
589: | $versions = $this->checkVersions($schemaFrom);
|
590: |
|
591: | if (is_array($versions) && count($versions)) {
|
592: |
|
593: | $version = $this->migrate($versions, $schemaFrom, $schemaTo);
|
594: |
|
595: | if ($version) {
|
596: |
|
597: | $this->schema['_options']['version'] = $version;
|
598: | $this->build($dbNamespace);
|
599: |
|
600: | _writeln();
|
601: | _writeln('Your schema has been updated.');
|
602: | }
|
603: |
|
604: | return true;
|
605: | }
|
606: |
|
607: | if ($versions === 0 || $versions === 1) {
|
608: |
|
609: | if ($isSchemaChanged) {
|
610: |
|
611: | $sql = $this->generateSqlFromDiff($schemaFrom, $schemaTo, $cmd);
|
612: | } else {
|
613: | _writeln();
|
614: | _writeln('Your schema is up-to-date.');
|
615: |
|
616: | return true;
|
617: | }
|
618: | }
|
619: |
|
620: | if (!empty($sql['up'])) {
|
621: | _writeln();
|
622: | _writeln('##########');
|
623: | foreach ($sql['up'] as $query) {
|
624: | _writeln($query);
|
625: | }
|
626: | _writeln('##########');
|
627: | _writeln();
|
628: | }
|
629: |
|
630: | $dropConstraintSql = $this->dropConstraintStatements($this->getConstraints($schemaFrom));
|
631: | $createConstraintSql = $this->createConstraintStatements();
|
632: |
|
633: | if (empty($sql['up']) && count($dropConstraintSql) == 0 && count($createConstraintSql) == 0) {
|
634: | return false;
|
635: | }
|
636: |
|
637: |
|
638: | $statements = array();
|
639: | if ($cmd->confirm('Type "y" to execute or type "n" to abort:')) {
|
640: | $statements = array_merge($statements, $dropConstraintSql, $sql['up'], $createConstraintSql);
|
641: |
|
642: | $noOfQueries = $this->executeQueries($dbNamespace, $statements);
|
643: | if (!$noOfQueries) {
|
644: | return false;
|
645: | }
|
646: | } else {
|
647: | _writeln('Aborted.');
|
648: | return false;
|
649: | }
|
650: |
|
651: |
|
652: | if ($dbVersion = $this->exportVersionFile($sql['up'], $dbNamespace)) {
|
653: |
|
654: | $this->schema['_options']['version'] = $dbVersion;
|
655: | $this->build($dbNamespace);
|
656: | } else {
|
657: | return false;
|
658: | }
|
659: |
|
660: | _writeln('--------------------');
|
661: | _writeln('%d queries executed.', $noOfQueries);
|
662: |
|
663: | return true;
|
664: | }
|
665: |
|
666: | |
667: | |
668: | |
669: | |
670: | |
671: |
|
672: | public function diff(Command $cmd, $dbNamespace = null)
|
673: | {
|
674: | if ($dbNamespace === null) {
|
675: | $dbNamespace = $this->dbNamespace;
|
676: | }
|
677: |
|
678: | $schemaFrom = self::getSchemaLockDefinition($dbNamespace);
|
679: |
|
680: | if (!$this->isLoaded()) {
|
681: | $this->load();
|
682: | }
|
683: |
|
684: | $schemaTo = $this->schema;
|
685: | $isSchemaChanged = $this->isSchemaChanged($schemaFrom, $schemaTo);
|
686: | $versions = $this->checkVersions($schemaFrom);
|
687: |
|
688: | if (is_array($versions) && count($versions)) {
|
689: | return false;
|
690: | }
|
691: |
|
692: | if ($versions === 0 || $versions === 1) {
|
693: |
|
694: | if ($isSchemaChanged) {
|
695: |
|
696: | $sql = $this->generateSqlFromDiff($schemaFrom, $schemaTo, $cmd);
|
697: | if ($dbVersion = $this->exportVersionFile($sql['up'], $dbNamespace)) {
|
698: | $versionDir = $this->getVersionDir($dbNamespace);
|
699: |
|
700: | _writeln();
|
701: | _writeln($versionDir . _DS_ . $dbVersion . $this->sqlExtension . ' is exported.');
|
702: | _writeln('Check the file and run `php lucidframe schema:update ' . $dbNamespace . '`');
|
703: |
|
704: | return true;
|
705: | }
|
706: | }
|
707: | }
|
708: |
|
709: | return false;
|
710: | }
|
711: |
|
712: | |
713: | |
714: | |
715: | |
716: | |
717: |
|
718: | private function exportVersionFile(array $sql, $dbNamespace = null)
|
719: | {
|
720: | if (!count($sql)) {
|
721: | return false;
|
722: | }
|
723: |
|
724: | if ($dbNamespace === null) {
|
725: | $dbNamespace = $this->dbNamespace;
|
726: | }
|
727: |
|
728: |
|
729: | $dbVersion = date('YmdHis');
|
730: |
|
731: | $dump = '--' . PHP_EOL
|
732: | . '-- Version ' . $dbVersion . PHP_EOL
|
733: | . '-- Generated by PHPLucidFrame ' . _version() . PHP_EOL
|
734: | . '-- ' . date('r') . PHP_EOL
|
735: | . '-- ;' . PHP_EOL . PHP_EOL
|
736: | . implode(PHP_EOL . PHP_EOL, $sql);
|
737: |
|
738: | $versionDir = $this->getVersionDir($dbNamespace);
|
739: | if (file_put_contents($versionDir . _DS_ . $dbVersion . $this->sqlExtension, $dump)) {
|
740: | return $dbVersion;
|
741: | }
|
742: |
|
743: | return false;
|
744: | }
|
745: |
|
746: | |
747: | |
748: | |
749: | |
750: | |
751: | |
752: |
|
753: | public function generateSqlFromDiff($schemaFrom, $schemaTo, Command $cmd)
|
754: | {
|
755: | $fieldNamesChanged = array();
|
756: | $this->columnsRenamed = array();
|
757: |
|
758: | $sql = array(
|
759: | 'up' => array(),
|
760: | 'down' => array(),
|
761: | );
|
762: |
|
763: |
|
764: | $this->detectTableRenamings($schemaFrom, $schemaTo);
|
765: | if (count($this->tablesRenamed)) {
|
766: | _writeln();
|
767: | _writeln('Type "y" to rename or type "n" to drop/create for the following tables:');
|
768: | _writeln();
|
769: | }
|
770: |
|
771: |
|
772: | foreach ($this->tablesRenamed as $from => $to) {
|
773: | if (!$cmd->confirm('Table renaming from `' . $from . '` to `' . $to . '`:')) {
|
774: | unset($this->tablesRenamed[$from]);
|
775: | }
|
776: | }
|
777: |
|
778: |
|
779: | $this->detectColumnRenamings($schemaFrom, $schemaTo);
|
780: | if (count($this->columnsRenamed)) {
|
781: | _writeln();
|
782: | _writeln('Type "y" to rename or type "n" to drop/create for the following fields:');
|
783: | _writeln();
|
784: | }
|
785: |
|
786: |
|
787: | foreach ($this->columnsRenamed as $from => $to) {
|
788: | $fieldFrom = explode('.', $from);
|
789: | if (!$cmd->confirm('Field renaming from `' . $fieldFrom[1] . '` to `' . $fieldFrom[0] . '.' . $to . '`:')) {
|
790: | unset($this->columnsRenamed[$from]);
|
791: | }
|
792: | }
|
793: |
|
794: |
|
795: | foreach ($schemaFrom as $table => $tableDef) {
|
796: | if ($table == '_options') {
|
797: | continue;
|
798: | }
|
799: |
|
800: | $fullTableName = db_table($table);
|
801: | $renamedTable = $this->isRenamed($table, $this->tablesRenamed);
|
802: |
|
803: | if (isset($schemaTo[$table]) || ($renamedTable && isset($schemaTo[$renamedTable]))) {
|
804: |
|
805: | if ($renamedTable) {
|
806: |
|
807: | $table = $renamedTable;
|
808: | }
|
809: |
|
810: | foreach ($tableDef as $field => $fieldDef) {
|
811: | $collate = $this->getTableCollation($table, $schemaTo);
|
812: | $oldField = $field;
|
813: | $renamedField = $this->isRenamed($table . '.' . $field, $this->columnsRenamed);
|
814: |
|
815: | if (isset($schemaTo[$table][$field]) || ($renamedField && isset($schemaTo[$table][$renamedField]))) {
|
816: |
|
817: | if ($renamedField) {
|
818: | $field = $renamedField;
|
819: | }
|
820: |
|
821: | $diff = $fieldDef !== $schemaTo[$table][$field];
|
822: | if ($diff) {
|
823: |
|
824: | if (in_array($field, self::$relationships)) {
|
825: | continue;
|
826: | }
|
827: |
|
828: | if ($field == 'options') {
|
829: | if (!empty($fieldDef['m:m'])) {
|
830: |
|
831: | continue;
|
832: | }
|
833: |
|
834: | $fromFieldOptions = $fieldDef;
|
835: | $toFieldOptions = $schemaTo[$table][$field];
|
836: | $diffOptions = $this->diffColumns($fromFieldOptions, $toFieldOptions);
|
837: |
|
838: | foreach ($diffOptions['diff'] as $optName => $optValue) {
|
839: | switch ($optName) {
|
840: | case 'unique':
|
841: |
|
842: | if (isset($fromFieldOptions['unique'])) {
|
843: | foreach ($fromFieldOptions['unique'] as $keyName => $uniqueFields) {
|
844: | $sql['up'][] = "ALTER TABLE `{$fullTableName}` DROP INDEX `IDX_$keyName`;";
|
845: | }
|
846: | }
|
847: |
|
848: | if (isset($toFieldOptions['unique'])) {
|
849: |
|
850: | foreach ($toFieldOptions['unique'] as $keyName => $uniqueFields) {
|
851: | $sql['up'][] = "ALTER TABLE `{$fullTableName}` ADD UNIQUE `IDX_$keyName` (`" . implode('`,`', $uniqueFields) . "`);";
|
852: | }
|
853: | }
|
854: | break;
|
855: |
|
856: | case 'engine':
|
857: | $sql['up'][] = "ALTER TABLE `{$fullTableName}` ENGINE={$toFieldOptions['engine']};";
|
858: | break;
|
859: |
|
860: | case 'charset':
|
861: | case 'collate':
|
862: | $sql['up'][] = "ALTER TABLE `{$fullTableName}` CONVERT TO CHARACTER SET {$toFieldOptions['charset']} COLLATE {$toFieldOptions['collate']};";
|
863: | break;
|
864: | }
|
865: | }
|
866: |
|
867: | continue;
|
868: | }
|
869: |
|
870: | $newField = $field;
|
871: |
|
872: | $sql['up'][] = "ALTER TABLE `{$fullTableName}` CHANGE COLUMN `{$oldField}` " .
|
873: | $this->getFieldStatement($newField, $schemaTo[$table][$newField], $collate) . ';';
|
874: |
|
875: | if (isset($schemaFrom[$table][$oldField]['unique']) && !isset($schemaTo[$table][$newField]['unique'])) {
|
876: | $sql['up'][] = "ALTER TABLE `{$fullTableName}` DROP INDEX `IDX_$oldField`;";
|
877: | } elseif (!isset($schemaFrom[$table][$oldField]['unique']) && isset($schemaTo[$table][$newField]['unique'])) {
|
878: | $sql['up'][] = "ALTER TABLE `{$fullTableName}` ADD UNIQUE `IDX_$newField` (`$newField`);";
|
879: | }
|
880: |
|
881: | $fieldNamesChanged[] = $table . '.' . $oldField;
|
882: | $fieldNamesChanged = array_unique($fieldNamesChanged);
|
883: | } else {
|
884: | if ($renamedField) {
|
885: | $fieldNamesChanged[] = $table . '.' . $renamedField;
|
886: | $sql['up'][] = "ALTER TABLE `{$fullTableName}` CHANGE COLUMN `{$oldField}` " .
|
887: | $this->getFieldStatement($renamedField, $schemaTo[$table][$renamedField], $collate) . ';';
|
888: | }
|
889: | }
|
890: | } else {
|
891: |
|
892: | if (in_array($field, array('m:m', '1:m', 'm:1'))) {
|
893: | continue;
|
894: | }
|
895: |
|
896: | if (in_array($table . '.' . $field, $fieldNamesChanged)) {
|
897: |
|
898: | continue;
|
899: | }
|
900: |
|
901: | if ($field == '1:1') {
|
902: | foreach ($fieldDef as $fkFieldInTable) {
|
903: | $sql['up'][] = "ALTER TABLE `{$fullTableName}` DROP COLUMN `{$fkFieldInTable['name']}`;";
|
904: | }
|
905: |
|
906: | continue;
|
907: | }
|
908: |
|
909: | $sql['up'][] = "ALTER TABLE `{$fullTableName}` DROP COLUMN `{$field}`;";
|
910: | }
|
911: | }
|
912: |
|
913: |
|
914: | if ($renamedTable) {
|
915: | $sql['up'][] = 'RENAME TABLE `' . $fullTableName . '` TO `' . db_table($renamedTable) . '`;';
|
916: | }
|
917: | } else {
|
918: |
|
919: | $sql['up'][] = "DROP TABLE IF EXISTS `{$fullTableName}`;";
|
920: | }
|
921: | }
|
922: |
|
923: | $pkFields = $this->getPrimaryKeys();
|
924: | $constraints = $this->getConstraints();
|
925: | foreach ($schemaTo as $table => $tableDef) {
|
926: | if ($table == '_options') {
|
927: | $dbOptions = $table;
|
928: | continue;
|
929: | }
|
930: |
|
931: | $collate = $this->getTableCollation($table, $schemaTo);
|
932: | $fullTableName = db_table($table);
|
933: | $tableFrom = $table;
|
934: | $fieldBefore = '';
|
935: |
|
936: | if (!isset($schemaFrom[$table])) {
|
937: | $oldTable = array_search($table, $this->tablesRenamed);
|
938: | if ($oldTable === false) {
|
939: |
|
940: | $createSql = trim($this->createTableStatement($table, $schemaTo, $pkFields, $constraints));
|
941: | if ($createSql) {
|
942: | $sql['up'][] = $createSql;
|
943: | }
|
944: |
|
945: | continue;
|
946: | } else {
|
947: | $tableFrom = $oldTable;
|
948: | }
|
949: | }
|
950: |
|
951: |
|
952: | foreach ($tableDef as $field => $fieldDef) {
|
953: | if (in_array($field, array_merge(SchemaManager::$relationships, array('options')))) {
|
954: | continue;
|
955: | }
|
956: |
|
957: | if (!isset($schemaFrom[$tableFrom][$field]) && array_search($table . '.' . $field, $fieldNamesChanged) === false) {
|
958: |
|
959: | $alterSql = "ALTER TABLE `{$fullTableName}` ADD COLUMN ";
|
960: | $alterSql .= $this->getFieldStatement($field, $fieldDef, $collate);
|
961: | if ($fieldBefore && $field != 'created') {
|
962: | $alterSql .= " AFTER `{$fieldBefore}`";
|
963: | }
|
964: | $alterSql .= ';';
|
965: | $sql['up'][] = $alterSql;
|
966: | }
|
967: |
|
968: | $fieldBefore = $field;
|
969: | }
|
970: | }
|
971: |
|
972: | return $sql;
|
973: | }
|
974: |
|
975: | |
976: | |
977: | |
978: | |
979: | |
980: | |
981: | |
982: |
|
983: | public function migrate(array $versions, array $schemaFrom, array $schemaTo, $verbose = true)
|
984: | {
|
985: |
|
986: | if ($dropConstraintSql = $this->dropConstraintStatements($this->getConstraints($schemaFrom))) {
|
987: | $this->executeQueries($this->dbNamespace, $dropConstraintSql);
|
988: | }
|
989: |
|
990: | if ($verbose) {
|
991: | _writeln();
|
992: | }
|
993: |
|
994: | $version = false;
|
995: | $noOfQueries = 0;
|
996: | foreach ($versions as $verFile) {
|
997: | $version = str_replace($this->sqlExtension, '', $verFile);
|
998: |
|
999: | if ($verbose) {
|
1000: | _writeln('Executing ' . $version);
|
1001: | }
|
1002: |
|
1003: | $sql = file_get_contents(DB . 'version' . _DS_ . $this->dbNamespace . _DS_ . $verFile);
|
1004: | if (empty($sql)) {
|
1005: | if ($verbose) {
|
1006: | _writeln('No sql statements executed.');
|
1007: | }
|
1008: |
|
1009: | return false;
|
1010: | }
|
1011: |
|
1012: | $sqls = explode(';', $sql);
|
1013: | $sql = array_filter($sqls, function($line) {
|
1014: | $line = trim($line);
|
1015: | return !empty($line) && strpos($line, '--') === false;
|
1016: | });
|
1017: |
|
1018: | if (empty($sql)) {
|
1019: | if ($verbose) {
|
1020: | _writeln('No sql statements executed.');
|
1021: | }
|
1022: |
|
1023: | return false;
|
1024: | }
|
1025: |
|
1026: | $executed = $this->executeQueries($this->dbNamespace, $sql);
|
1027: | if (!$executed) {
|
1028: | return false;
|
1029: | }
|
1030: |
|
1031: | $noOfQueries += $executed;
|
1032: | if ($verbose) {
|
1033: | _writeln();
|
1034: | }
|
1035: | }
|
1036: |
|
1037: |
|
1038: | if ($createConstraintSql = $this->createConstraintStatements($this->getConstraints($schemaTo))) {
|
1039: | $this->executeQueries($this->dbNamespace, $createConstraintSql);
|
1040: | }
|
1041: |
|
1042: | return $version;
|
1043: | }
|
1044: |
|
1045: | |
1046: | |
1047: | |
1048: | |
1049: | |
1050: | |
1051: |
|
1052: | private function executeQueries($dbNamespace, $queries)
|
1053: | {
|
1054: | if (!count($queries)) {
|
1055: | return false;
|
1056: | }
|
1057: |
|
1058: | if ($this->dbNamespace !== $dbNamespace) {
|
1059: | db_switch($dbNamespace);
|
1060: | }
|
1061: |
|
1062: | db_transaction();
|
1063: |
|
1064: | array_unshift($queries, 'SET FOREIGN_KEY_CHECKS = 0;');
|
1065: | array_push($queries, 'SET FOREIGN_KEY_CHECKS = 1;');
|
1066: |
|
1067: | $count = 0;
|
1068: | $error = false;
|
1069: | foreach ($queries as $sql) {
|
1070: | $sql = trim($sql);
|
1071: |
|
1072: | if (empty($sql)) {
|
1073: | continue;
|
1074: | }
|
1075: |
|
1076: | if (substr($sql, 0, 2) == '--') {
|
1077: | continue;
|
1078: | }
|
1079: |
|
1080: | if (!db_query($sql)) {
|
1081: | $error = true;
|
1082: | break;
|
1083: | }
|
1084: |
|
1085: | $count++;
|
1086: | }
|
1087: |
|
1088: | if ($error) {
|
1089: | db_rollback();
|
1090: | } else {
|
1091: | db_commit();
|
1092: | }
|
1093: |
|
1094: | if ($this->dbNamespace !== $dbNamespace) {
|
1095: |
|
1096: | db_switch($this->dbNamespace);
|
1097: | }
|
1098: |
|
1099: | if ($error == true) {
|
1100: | return false;
|
1101: | } else {
|
1102: | return $count;
|
1103: | }
|
1104: | }
|
1105: |
|
1106: | |
1107: | |
1108: | |
1109: | |
1110: | |
1111: |
|
1112: | public function isSchemaChanged(array $from, array $to)
|
1113: | {
|
1114: | if (isset($from['_options']['version'])) {
|
1115: | unset($from['_options']['version']);
|
1116: | }
|
1117: |
|
1118: | if (isset($from['_options']['fkConstraints'])) {
|
1119: | unset($from['_options']['fkConstraints']);
|
1120: | }
|
1121: |
|
1122: | if (isset($to['_options']['version'])) {
|
1123: | unset($to['_options']['version']);
|
1124: | }
|
1125: |
|
1126: | if (isset($to['_options']['fkConstraints'])) {
|
1127: | unset($to['_options']['fkConstraints']);
|
1128: | }
|
1129: |
|
1130: | return $from != $to;
|
1131: | }
|
1132: |
|
1133: | |
1134: | |
1135: | |
1136: |
|
1137: | public function getCurrentVersion()
|
1138: | {
|
1139: | $version = 0;
|
1140: | if ($schema = self::getSchemaLockDefinition($this->dbNamespace)) {
|
1141: | $version = isset($schema['_options']['version']) ? $schema['_options']['version'] : 0;
|
1142: | }
|
1143: |
|
1144: | return $version;
|
1145: | }
|
1146: |
|
1147: | |
1148: | |
1149: | |
1150: | |
1151: | |
1152: | |
1153: | |
1154: |
|
1155: | public function checkVersions(array $schema)
|
1156: | {
|
1157: |
|
1158: | $versionDir = DB . 'version' . _DS_ . $this->dbNamespace;
|
1159: | if (!is_dir($versionDir)) {
|
1160: | return 0;
|
1161: | }
|
1162: |
|
1163: | $files = scandir($versionDir);
|
1164: | rsort($files);
|
1165: |
|
1166: |
|
1167: | $lastVersion = 0;
|
1168: | if (isset($schema['_options']['version'])) {
|
1169: | $lastVersion = $schema['_options']['version'];
|
1170: | if ($lastVersion . $this->sqlExtension == $files[0]) {
|
1171: | return 1;
|
1172: | }
|
1173: | }
|
1174: |
|
1175: |
|
1176: | $manager = $this;
|
1177: | $files = array_filter($files, function ($fileName) use ($lastVersion, $manager) {
|
1178: | if (preg_match('/\d{14}\\' . $manager->sqlExtension . '/', $fileName)) {
|
1179: | if ($lastVersion == 0) {
|
1180: | return true;
|
1181: | }
|
1182: |
|
1183: | $version = str_replace($manager->sqlExtension, '', $fileName);
|
1184: | if ($version > $lastVersion) {
|
1185: | return true;
|
1186: | }
|
1187: | }
|
1188: |
|
1189: | return false;
|
1190: | });
|
1191: |
|
1192: | if (count($files)) {
|
1193: | sort($files);
|
1194: | return $files;
|
1195: | }
|
1196: |
|
1197: | return 0;
|
1198: | }
|
1199: |
|
1200: | |
1201: | |
1202: | |
1203: |
|
1204: | public function isLoaded()
|
1205: | {
|
1206: | return isset($this->schema['_options']['pk']);
|
1207: | }
|
1208: |
|
1209: | |
1210: | |
1211: | |
1212: | |
1213: | |
1214: | |
1215: |
|
1216: | protected function isRenamed($needle, $haystack)
|
1217: | {
|
1218: | if (isset($haystack[$needle])) {
|
1219: | return $haystack[$needle];
|
1220: | } else {
|
1221: | return false;
|
1222: | }
|
1223: | }
|
1224: |
|
1225: | |
1226: | |
1227: | |
1228: | |
1229: |
|
1230: | public function hasTable($table)
|
1231: | {
|
1232: | if (!$this->isLoaded()) {
|
1233: | return false;
|
1234: | }
|
1235: |
|
1236: | $table = db_table($table);
|
1237: |
|
1238: | return isset($this->schema[$table]);
|
1239: | }
|
1240: |
|
1241: | |
1242: | |
1243: | |
1244: | |
1245: | |
1246: |
|
1247: | public function hasField($table, $field)
|
1248: | {
|
1249: | if (!$this->isLoaded()) {
|
1250: | return false;
|
1251: | }
|
1252: |
|
1253: | $table = db_table($table);
|
1254: |
|
1255: | return isset($this->schema[$table][$field]);
|
1256: | }
|
1257: |
|
1258: | |
1259: | |
1260: | |
1261: | |
1262: |
|
1263: | public function hasTimestamps($table)
|
1264: | {
|
1265: | if (!$this->isLoaded()) {
|
1266: | return false;
|
1267: | }
|
1268: |
|
1269: | $table = db_table($table);
|
1270: |
|
1271: | return (isset($this->schema[$table]['options']['timestamps']) && $this->schema[$table]['options']['timestamps']) ? true : false;
|
1272: | }
|
1273: |
|
1274: | |
1275: | |
1276: | |
1277: | |
1278: |
|
1279: | public function hasSlug($table)
|
1280: | {
|
1281: | if (!$this->isLoaded()) {
|
1282: | return false;
|
1283: | }
|
1284: |
|
1285: | $table = db_table($table);
|
1286: |
|
1287: | return isset($this->schema[$table]['slug']) ? true : false;
|
1288: | }
|
1289: |
|
1290: | |
1291: | |
1292: | |
1293: | |
1294: | |
1295: |
|
1296: | public function getFieldType($table, $field)
|
1297: | {
|
1298: | $table = db_table($table);
|
1299: |
|
1300: | if ($this->hasField($table, $field)) {
|
1301: | return $this->schema[$table][$field]['type'];
|
1302: | }
|
1303: |
|
1304: | return null;
|
1305: | }
|
1306: |
|
1307: | |
1308: | |
1309: | |
1310: | |
1311: | |
1312: |
|
1313: | protected function getOptions()
|
1314: | {
|
1315: | if (isset($this->schema['_options'])) {
|
1316: | $options = $this->schema['_options'] + $this->defaultOptions;
|
1317: | } else {
|
1318: | $options = $this->defaultOptions;
|
1319: | }
|
1320: |
|
1321: | return $options;
|
1322: | }
|
1323: |
|
1324: | |
1325: | |
1326: | |
1327: | |
1328: | |
1329: | |
1330: |
|
1331: | protected function getTableOptions($tableDef)
|
1332: | {
|
1333: | $options = $this->getOptions();
|
1334: |
|
1335: | if (isset($options['pk'])) {
|
1336: | unset($options['pk']);
|
1337: | }
|
1338: |
|
1339: | if (isset($options['fkConstraints'])) {
|
1340: | unset($options['fkConstraints']);
|
1341: | }
|
1342: |
|
1343: | if (isset($tableDef['options'])) {
|
1344: | $tableDef['options'] += $options;
|
1345: | } else {
|
1346: | $tableDef['options'] = $options;
|
1347: | }
|
1348: |
|
1349: | return $tableDef['options'];
|
1350: | }
|
1351: |
|
1352: | |
1353: | |
1354: | |
1355: | |
1356: |
|
1357: | public function populatePrimaryKeys(&$schema)
|
1358: | {
|
1359: |
|
1360: | $pkFields = array();
|
1361: | foreach ($schema as $table => $def) {
|
1362: | $def['options'] = $this->getTableOptions($def);
|
1363: |
|
1364: | if ($def['options']['timestamps']) {
|
1365: | $def['created'] = array('type' => 'datetime', 'null' => true);
|
1366: | $def['updated'] = array('type' => 'datetime', 'null' => true);
|
1367: | $def['deleted'] = array('type' => 'datetime', 'null' => true);
|
1368: | }
|
1369: |
|
1370: | $schema[$table] = $def;
|
1371: |
|
1372: |
|
1373: | $pkFields[$table] = array();
|
1374: | if (isset($def['options']['pk'])) {
|
1375: | foreach ($def['options']['pk'] as $pk) {
|
1376: | if (isset($def[$pk])) {
|
1377: |
|
1378: | $pkFields[$table][$pk] = $def[$pk];
|
1379: | } else {
|
1380: |
|
1381: | $pkFields[$table][$pk] = $this->getPKDefaultType();
|
1382: | }
|
1383: | }
|
1384: | } else {
|
1385: | $pkFields[$table]['id'] = $this->getPKDefaultType();
|
1386: | }
|
1387: | }
|
1388: |
|
1389: | $this->setPrimaryKeys($pkFields);
|
1390: |
|
1391: | return $pkFields;
|
1392: | }
|
1393: |
|
1394: | |
1395: | |
1396: | |
1397: | |
1398: |
|
1399: | public function populatePivots(&$schema)
|
1400: | {
|
1401: | $constraints = array();
|
1402: | $pkFields = $this->getPrimaryKeys();
|
1403: |
|
1404: | $manyToMany = array_filter($schema, function ($def) {
|
1405: | return isset($def['m:m']);
|
1406: | });
|
1407: |
|
1408: | foreach ($manyToMany as $table => $def) {
|
1409: | foreach ($def['m:m'] as $fkTable => $joint) {
|
1410: | if (!empty($joint['table']) && isset($schema[$joint['table']])) {
|
1411: |
|
1412: | continue;
|
1413: | }
|
1414: |
|
1415: | if (isset($schema[$table . '_to_' . $fkTable]) || isset($schema[$fkTable . '_to_' . $table])) {
|
1416: |
|
1417: | continue;
|
1418: | }
|
1419: |
|
1420: | if (isset($schema[$fkTable]['m:m'][$table])) {
|
1421: | if (empty($joint['table']) && !empty($schema[$fkTable]['m:m'][$table]['table'])) {
|
1422: | $joint['table'] = $schema[$fkTable]['m:m'][$table]['table'];
|
1423: | }
|
1424: |
|
1425: |
|
1426: | $jointTable = !empty($joint['table']) ? $joint['table'] : $table . '_to_' . $fkTable;
|
1427: | $schema[$jointTable]['options'] = array(
|
1428: | 'pk' => array(),
|
1429: | 'timestamps' => false,
|
1430: | 'm:m' => true
|
1431: | ) + $this->defaultOptions;
|
1432: |
|
1433: |
|
1434: | $relation = $this->getRelationOptions($joint, $table);
|
1435: | foreach ($relation as $rel) {
|
1436: | $field = $rel['name'];
|
1437: | $schema[$jointTable][$field] = $this->getFKField($table, $rel);
|
1438: | $schema[$jointTable][$field]['null'] = false;
|
1439: | $schema[$jointTable]['options']['pk'][] = $field;
|
1440: | $pkFields[$jointTable][$field] = $schema[$jointTable][$field];
|
1441: |
|
1442: | $constraint = $this->getFKConstraint($table, $rel, $schema);
|
1443: | if ($constraint) {
|
1444: | $constraints[$jointTable][$field] = $constraint;
|
1445: | }
|
1446: | }
|
1447: |
|
1448: |
|
1449: | $relation = $this->getRelationOptions($schema[$fkTable]['m:m'][$table], $fkTable);
|
1450: | foreach ($relation as $rel) {
|
1451: | $field = $rel['name'];
|
1452: | $schema[$jointTable][$field] = $this->getFKField($fkTable, $rel);
|
1453: | $schema[$jointTable][$field]['null'] = false;
|
1454: | $schema[$jointTable]['options']['pk'][] = $field;
|
1455: | $pkFields[$jointTable][$field] = $schema[$jointTable][$field];
|
1456: |
|
1457: | $constraint = $this->getFKConstraint($fkTable, $rel, $schema);
|
1458: | if ($constraint) {
|
1459: | $constraints[$jointTable][$field] = $constraint;
|
1460: | }
|
1461: | }
|
1462: | }
|
1463: | }
|
1464: | }
|
1465: |
|
1466: | $this->setPrimaryKeys($pkFields);
|
1467: | $this->setConstraints($constraints);
|
1468: |
|
1469: | return $constraints;
|
1470: | }
|
1471: |
|
1472: | |
1473: | |
1474: | |
1475: | |
1476: | |
1477: | |
1478: | |
1479: |
|
1480: | public function createTableStatement($table, &$schema, &$pkFields, &$constraints)
|
1481: | {
|
1482: | if (!isset($schema[$table])) {
|
1483: | return null;
|
1484: | }
|
1485: |
|
1486: | $def = $schema[$table];
|
1487: | $fullTableName = db_table($table);
|
1488: | $fkFields = array();
|
1489: |
|
1490: |
|
1491: | if (isset($def['m:1']) && is_array($def['m:1'])) {
|
1492: | foreach ($def['m:1'] as $fkTable) {
|
1493: | if (isset($schema[$fkTable]['1:m'][$table]) || array_search($table, $schema[$fkTable]['1:m']) !== false) {
|
1494: | $relationOptions = array();
|
1495: | if (isset($schema[$fkTable]['1:m'][$table])) {
|
1496: | $relationOptions = $schema[$fkTable]['1:m'][$table];
|
1497: | }
|
1498: |
|
1499: | $relation = $this->getRelationOptions($relationOptions, $fkTable);
|
1500: | foreach ($relation as $rel) {
|
1501: | $field = $rel['name'];
|
1502: |
|
1503: | $fkFields[$field] = $this->getFKField($fkTable, $rel);
|
1504: |
|
1505: | $constraint = $this->getFKConstraint($fkTable, $rel, $schema);
|
1506: | if ($constraint) {
|
1507: | $constraints[$table][$field] = $constraint;
|
1508: | }
|
1509: | }
|
1510: | }
|
1511: | }
|
1512: | }
|
1513: |
|
1514: |
|
1515: | if (isset($def['1:1']) && is_array($def['1:1'])) {
|
1516: | foreach ($def['1:1'] as $fkTable => $fk) {
|
1517: | $relationOptions = array();
|
1518: | if (is_numeric($fkTable)) {
|
1519: | $fkTable = $fk;
|
1520: | } else {
|
1521: | $relationOptions = $fk;
|
1522: | }
|
1523: |
|
1524: | $relation = $this->getRelationOptions($relationOptions, $fkTable);
|
1525: | foreach ($relation as $rel) {
|
1526: | $field = $rel['name'];
|
1527: |
|
1528: | $fkFields[$field] = $this->getFKField($fkTable, $rel);
|
1529: |
|
1530: | $constraint = $this->getFKConstraint($fkTable, $rel, $schema);
|
1531: | if ($constraint) {
|
1532: | $constraints[$table][$field] = $constraint;
|
1533: | }
|
1534: | }
|
1535: | }
|
1536: | }
|
1537: |
|
1538: | $this->setConstraints($constraints);
|
1539: |
|
1540: | $def = array_merge($pkFields[$table], $fkFields, $def);
|
1541: | $schema[$table] = $def;
|
1542: |
|
1543: |
|
1544: | if (isset($def['options']['m:m']) && $def['options']['m:m']) {
|
1545: | $jointTable = $table;
|
1546: | foreach ($schema[$jointTable] as $field => $rule) {
|
1547: | if ($field == 'options') {
|
1548: | continue;
|
1549: | }
|
1550: | $fkFields[$field] = $rule;
|
1551: | }
|
1552: | }
|
1553: |
|
1554: | $options = $this->getTableOptions($def);
|
1555: | $def['options'] = $options;
|
1556: |
|
1557: |
|
1558: | $sql = "CREATE TABLE IF NOT EXISTS `{$fullTableName}` (" . PHP_EOL;
|
1559: |
|
1560: |
|
1561: | $autoinc = false;
|
1562: | foreach ($def as $name => $rule) {
|
1563: |
|
1564: | if (in_array($name, self::$relationships) || $name == 'options') {
|
1565: | continue;
|
1566: | }
|
1567: |
|
1568: | $sql .= ' ' . $this->getFieldStatement($name, $rule, $this->getTableCollation($name, $schema)) . ',' . PHP_EOL;
|
1569: |
|
1570: |
|
1571: | if (isset($rule['unique']) && $rule['unique']) {
|
1572: | $fkFields[$name] = $rule;
|
1573: | }
|
1574: |
|
1575: | if (isset($rule['autoinc']) && $rule['autoinc']) {
|
1576: | $autoinc = true;
|
1577: | }
|
1578: | }
|
1579: |
|
1580: |
|
1581: | if (count($fkFields)) {
|
1582: | foreach (array_keys($fkFields) as $name) {
|
1583: | if (isset($fkFields[$name]['unique']) && $fkFields[$name]['unique']) {
|
1584: | $sql .= ' UNIQUE KEY';
|
1585: | } else {
|
1586: | $sql .= ' KEY';
|
1587: | }
|
1588: | $sql .= " `IDX_$name` (`$name`)," . PHP_EOL;
|
1589: | }
|
1590: | }
|
1591: |
|
1592: |
|
1593: | if (isset($options['unique']) && is_array($options['unique'])) {
|
1594: | foreach ($options['unique'] as $keyName => $uniqueFields) {
|
1595: | $sql .= ' UNIQUE KEY';
|
1596: | $sql .= " `IDX_$keyName` (`" . implode('`,`', $uniqueFields) . "`)," . PHP_EOL;
|
1597: | }
|
1598: | }
|
1599: |
|
1600: |
|
1601: | if (isset($pkFields[$table])) {
|
1602: | $sql .= ' PRIMARY KEY (`' . implode('`,`', array_keys($pkFields[$table])) . '`)' . PHP_EOL;
|
1603: | }
|
1604: |
|
1605: | $sql .= ')';
|
1606: | $sql .= ' ENGINE=' . $options['engine'];
|
1607: | $sql .= ' DEFAULT CHARSET=' . $options['charset'];
|
1608: | $sql .= ' COLLATE=' . $options['collate'];
|
1609: |
|
1610: | if ($autoinc) {
|
1611: | $sql .= ' AUTO_INCREMENT=1';
|
1612: | }
|
1613: |
|
1614: | $sql .= ';' . PHP_EOL;
|
1615: |
|
1616: | return $sql;
|
1617: | }
|
1618: |
|
1619: | |
1620: | |
1621: | |
1622: | |
1623: |
|
1624: | public function createConstraintStatements($constraints = null)
|
1625: | {
|
1626: | if ($constraints === null) {
|
1627: | $constraints = $this->getConstraints();
|
1628: | }
|
1629: |
|
1630: | $options = $this->getOptions();
|
1631: | $sql = array();
|
1632: |
|
1633: | if ($options['constraints']) {
|
1634: | foreach ($constraints as $table => $constraint) {
|
1635: | $fullTableName = db_table($table);
|
1636: | $constraintSql = "ALTER TABLE `{$fullTableName}`" . PHP_EOL;
|
1637: | $statement = array();
|
1638: | foreach ($constraint as $field => $rule) {
|
1639: | $statement[] = " ADD CONSTRAINT `{$rule['name']}` FOREIGN KEY (`{$rule['fields']}`)"
|
1640: | . " REFERENCES `{$rule['reference_table']}` (`{$rule['reference_fields']}`)"
|
1641: | . " ON DELETE {$rule['on_delete']}"
|
1642: | . " ON UPDATE {$rule['on_update']}";
|
1643: | }
|
1644: | $constraintSql .= implode(',' . PHP_EOL, $statement) . ';' . PHP_EOL;
|
1645: | $sql[] = $constraintSql;
|
1646: | }
|
1647: | }
|
1648: |
|
1649: | return count($sql) ? $sql : null;
|
1650: | }
|
1651: |
|
1652: | |
1653: | |
1654: | |
1655: | |
1656: |
|
1657: | public function dropConstraintStatements($constraints = null)
|
1658: | {
|
1659: | if ($constraints === null) {
|
1660: | $constraints = $this->getConstraints();
|
1661: | }
|
1662: |
|
1663: | $options = $this->getOptions();
|
1664: | $sql = array();
|
1665: |
|
1666: | if ($options['constraints']) {
|
1667: | $tables = array_keys($constraints);
|
1668: | foreach ($tables as $table) {
|
1669: | $fullTableName = db_table($table);
|
1670: | $result = db_query("SHOW CREATE TABLE `{$fullTableName}`");
|
1671: | if ($result && $row = db_fetchArray($result)) {
|
1672: | $fKeys = array();
|
1673: | if (preg_match_all('/CONSTRAINT `(FK_[A-Z0-9]+)` FOREIGN KEY/', $row[1], $matches)) {
|
1674: | foreach ($matches[1] as $constraintName) {
|
1675: | $fKeys[] = " DROP FOREIGN KEY `{$constraintName}`";
|
1676: | }
|
1677: | }
|
1678: |
|
1679: | if (count($fKeys)) {
|
1680: | $sql[] = "ALTER TABLE `{$fullTableName}`" . PHP_EOL . implode(',' . PHP_EOL, $fKeys) . ';';
|
1681: | }
|
1682: | }
|
1683: | }
|
1684: | }
|
1685: |
|
1686: | return count($sql) ? $sql : null;
|
1687: | }
|
1688: |
|
1689: | |
1690: | |
1691: | |
1692: | |
1693: |
|
1694: | public function setPrimaryKeys($pkFields)
|
1695: | {
|
1696: | $this->schema['_options']['pk'] = $pkFields;
|
1697: | }
|
1698: |
|
1699: | |
1700: | |
1701: | |
1702: | |
1703: |
|
1704: | public function getPrimaryKeys($schema = null)
|
1705: | {
|
1706: | if ($schema === null) {
|
1707: | $schema = $this->schema;
|
1708: | }
|
1709: |
|
1710: | return !empty($schema['_options']['pk']) ? $schema['_options']['pk'] : array();
|
1711: | }
|
1712: |
|
1713: | |
1714: | |
1715: | |
1716: | |
1717: |
|
1718: | public function setConstraints($constraints)
|
1719: | {
|
1720: | $this->schema['_options']['fkConstraints'] = $constraints;
|
1721: | }
|
1722: |
|
1723: | |
1724: | |
1725: | |
1726: | |
1727: |
|
1728: | public function getConstraints($schema = null)
|
1729: | {
|
1730: | if ($schema === null) {
|
1731: | $schema = $this->schema;
|
1732: | }
|
1733: |
|
1734: | return !empty($schema['_options']['fkConstraints']) ? $schema['_options']['fkConstraints'] : array();
|
1735: | }
|
1736: |
|
1737: | |
1738: | |
1739: | |
1740: | |
1741: | |
1742: |
|
1743: | public function getTableCollation($table, $schema = null)
|
1744: | {
|
1745: | if ($schema === null) {
|
1746: | $schema = $this->schema;
|
1747: | }
|
1748: |
|
1749: | return isset($schema[$table]['options']['collate']) ? $schema[$table]['options']['collate'] : null;
|
1750: | }
|
1751: |
|
1752: | |
1753: | |
1754: | |
1755: | |
1756: | |
1757: | |
1758: | |
1759: | |
1760: | |
1761: | |
1762: |
|
1763: | private function getSimilarity($needle, array $from, array $to, $table = null)
|
1764: | {
|
1765: | if (in_array($needle, array_merge(SchemaManager::$relationships, array('options')))) {
|
1766: | return false;
|
1767: | }
|
1768: |
|
1769: | if ($table) {
|
1770: | $compared = 'field';
|
1771: | if (isset($this->droppedColumns[$table]) && !in_array($needle, $this->droppedColumns[$table])) {
|
1772: | return false;
|
1773: | }
|
1774: |
|
1775: | $haystack = &$this->addedColumns[$table];
|
1776: | } else {
|
1777: | $compared = 'table';
|
1778: | if (!in_array($needle, $this->droppedTables)) {
|
1779: | return false;
|
1780: | }
|
1781: |
|
1782: | $haystack = &$this->addedTables;
|
1783: | }
|
1784: |
|
1785: | if (!is_array($haystack) || in_array($needle, $haystack)) {
|
1786: | return false;
|
1787: | }
|
1788: |
|
1789: | $similarity = array();
|
1790: | $matchingText = array();
|
1791: | $matchingMetaphone = array();
|
1792: |
|
1793: | foreach ($haystack as $i => $name) {
|
1794: | if ($needle === $name) {
|
1795: | return false;
|
1796: | }
|
1797: |
|
1798: | $scores = array();
|
1799: | $matching[$name] = array();
|
1800: |
|
1801: | $changes = 100;
|
1802: | if ($compared == 'table') {
|
1803: |
|
1804: | $diff = $this->diffTables($from[$needle], $to[$name]);
|
1805: | if ($diff['changes'] == 0) {
|
1806: | unset($haystack[$i]);
|
1807: | return $name;
|
1808: | }
|
1809: | $changes = $diff['changes'];
|
1810: | } else {
|
1811: |
|
1812: | $diff = $this->diffColumns($from[$needle], $to[$name]);
|
1813: | $changes = $diff['changes'];
|
1814: | }
|
1815: | $percentChanges = 100 - $changes;
|
1816: |
|
1817: |
|
1818: | similar_text(strtolower($needle), strtolower($name), $percent1);
|
1819: | $matchingText[$name] = (int)round($percent1);
|
1820: |
|
1821: |
|
1822: | $metaphone1 = metaphone(strtolower($needle));
|
1823: | $metaphone2 = metaphone(strtolower($name));
|
1824: | similar_text($metaphone1, $metaphone2, $percent2);
|
1825: | $matchingMetaphone[$name] = (int)round($percent2);
|
1826: |
|
1827: | $percentByTwo = round(($percent1 + $percent2) / 2);
|
1828: | $percent1 = round($percent1);
|
1829: |
|
1830: | if ($percent1 < 100 && $percent2 == 100) {
|
1831: |
|
1832: | $scores[] = $percent1 + $percentChanges;
|
1833: | }
|
1834: |
|
1835: | if ($percentByTwo >= 95 && $percentByTwo <= 100) {
|
1836: |
|
1837: | $scores[] = $percentByTwo + $percentChanges;
|
1838: | }
|
1839: |
|
1840: | if ($percent1 > 50 && $percent1 < 100) {
|
1841: |
|
1842: | $scores[] = $percent1 + $percentChanges;
|
1843: | }
|
1844: |
|
1845: | if ($compared == 'field' && strpos(strtolower($needle), 'id') !== false && strpos(strtolower($name), 'id') !== false) {
|
1846: |
|
1847: | $scores[] = 75 + $percentChanges;
|
1848: | }
|
1849: |
|
1850: | if (count($scores)) {
|
1851: | arsort($scores);
|
1852: | $similarity[$name] = (int)round(array_shift($scores));
|
1853: | }
|
1854: | }
|
1855: |
|
1856: | if (count($similarity) == 0) {
|
1857: | return false;
|
1858: | }
|
1859: |
|
1860: | arsort($similarity);
|
1861: | arsort($matchingText);
|
1862: | arsort($matchingMetaphone);
|
1863: |
|
1864: | foreach (array($similarity, $matchingText, $matchingMetaphone) as $i => $matchings) {
|
1865: | $dups = array_count_values($matchings);
|
1866: | if (array_pop($dups) == 1 || $i == 2) {
|
1867: | $candidate = array_keys($matchings);
|
1868: | $topSimilarity = array_shift($candidate);
|
1869: | break;
|
1870: | }
|
1871: | }
|
1872: |
|
1873: | unset($haystack[array_search($topSimilarity, $haystack)]);
|
1874: |
|
1875: | return $topSimilarity;
|
1876: | }
|
1877: |
|
1878: | |
1879: | |
1880: | |
1881: | |
1882: | |
1883: |
|
1884: | private function detectDroppedTables(array $schemaFrom, array $schemaTo)
|
1885: | {
|
1886: |
|
1887: | foreach ($schemaFrom as $table => $tableDef) {
|
1888: | if ($table == '_options') {
|
1889: | continue;
|
1890: | }
|
1891: |
|
1892: | if (!isset($schemaTo[$table])) {
|
1893: | $this->droppedTables[] = $table;
|
1894: | continue;
|
1895: | }
|
1896: | }
|
1897: | }
|
1898: |
|
1899: | |
1900: | |
1901: | |
1902: | |
1903: | |
1904: |
|
1905: | private function detectAddedTables(array $schemaFrom, array $schemaTo)
|
1906: | {
|
1907: |
|
1908: | foreach ($schemaTo as $table => $tableDef) {
|
1909: | if ($table == '_options') {
|
1910: | continue;
|
1911: | }
|
1912: |
|
1913: | if (!isset($schemaFrom[$table])) {
|
1914: | $this->addedTables[] = $table;
|
1915: | continue;
|
1916: | }
|
1917: | }
|
1918: | }
|
1919: |
|
1920: | |
1921: | |
1922: | |
1923: | |
1924: | |
1925: |
|
1926: | private function detectDroppedColumns(array $schemaFrom, array $schemaTo)
|
1927: | {
|
1928: |
|
1929: | foreach ($schemaFrom as $table => $tableDef) {
|
1930: | if ($table == '_options') {
|
1931: | continue;
|
1932: | }
|
1933: |
|
1934: |
|
1935: | foreach ($tableDef as $field => $fieldDef) {
|
1936: | if (in_array($field, array_merge(SchemaManager::$relationships, array('options')))) {
|
1937: | continue;
|
1938: | }
|
1939: |
|
1940: | if (!isset($schemaTo[$table][$field])) {
|
1941: |
|
1942: | $this->droppedColumns[$table][] = $field;
|
1943: | }
|
1944: | }
|
1945: | }
|
1946: | }
|
1947: |
|
1948: | |
1949: | |
1950: | |
1951: | |
1952: | |
1953: |
|
1954: | private function detectAddedColumns(array $schemaFrom, array $schemaTo)
|
1955: | {
|
1956: |
|
1957: | foreach ($schemaTo as $table => $tableDef) {
|
1958: | if ($table == '_options') {
|
1959: | continue;
|
1960: | }
|
1961: |
|
1962: |
|
1963: | foreach ($tableDef as $field => $fieldDef) {
|
1964: | if (in_array($field, array_merge(SchemaManager::$relationships, array('options')))) {
|
1965: | continue;
|
1966: | }
|
1967: |
|
1968: | if (!isset($schemaFrom[$table][$field])) {
|
1969: |
|
1970: | $this->addedColumns[$table][] = $field;
|
1971: | }
|
1972: | }
|
1973: | }
|
1974: | }
|
1975: |
|
1976: |
|
1977: | |
1978: | |
1979: | |
1980: | |
1981: | |
1982: | |
1983: | |
1984: |
|
1985: | private function detectTableRenamings(array $schemaFrom, array $schemaTo)
|
1986: | {
|
1987: | $this->detectDroppedTables($schemaFrom, $schemaTo);
|
1988: | $this->detectAddedTables($schemaFrom, $schemaTo);
|
1989: |
|
1990: |
|
1991: | foreach ($schemaFrom as $table => $tableDef) {
|
1992: | if ($table == '_options') {
|
1993: | continue;
|
1994: | }
|
1995: |
|
1996: | $renamedTable = $this->getSimilarity($table, $schemaFrom, $schemaTo);
|
1997: | if ($renamedTable) {
|
1998: | $this->tablesRenamed[$table] = $renamedTable;
|
1999: | }
|
2000: | }
|
2001: | }
|
2002: |
|
2003: | |
2004: | |
2005: | |
2006: | |
2007: | |
2008: | |
2009: | |
2010: |
|
2011: | private function detectColumnRenamings(array $schemaFrom, array $schemaTo)
|
2012: | {
|
2013: | $this->detectDroppedColumns($schemaFrom, $schemaTo);
|
2014: | $this->detectAddedColumns($schemaFrom, $schemaTo);
|
2015: |
|
2016: |
|
2017: | foreach ($schemaFrom as $table => $tableDef) {
|
2018: | if ($table == '_options') {
|
2019: | continue;
|
2020: | }
|
2021: |
|
2022: | $originalTable = $table;
|
2023: | $renamedTable = null;
|
2024: | if (isset($this->tablesRenamed[$table])) {
|
2025: | $renamedTable = $this->tablesRenamed[$table];
|
2026: | }
|
2027: |
|
2028: | if (isset($schemaTo[$table]) || ($renamedTable && isset($schemaTo[$renamedTable]))) {
|
2029: | if ($renamedTable) {
|
2030: | $table = $renamedTable;
|
2031: | }
|
2032: |
|
2033: | foreach ($tableDef as $field => $fieldDef) {
|
2034: | if (in_array($field, array_merge(SchemaManager::$relationships, array('options')))) {
|
2035: | continue;
|
2036: | }
|
2037: |
|
2038: | if (!isset($schemaTo[$table][$field])) {
|
2039: |
|
2040: | $renamedCol = $this->getSimilarity($field, $tableDef, $schemaTo[$table], $table);
|
2041: | if ($renamedCol) {
|
2042: | $this->columnsRenamed[$table . '.' . $field] = $renamedCol;
|
2043: | }
|
2044: | }
|
2045: | }
|
2046: | }
|
2047: | }
|
2048: | }
|
2049: |
|
2050: | |
2051: | |
2052: | |
2053: | |
2054: | |
2055: | |
2056: | |
2057: | |
2058: | |
2059: | |
2060: |
|
2061: | private function diffColumns(array $from, array $to)
|
2062: | {
|
2063: | $changes = 0;
|
2064: | $diff = array();
|
2065: | foreach ($from as $key => $value) {
|
2066: | if (!isset($to[$key])) {
|
2067: | $diff[$key] = $value;
|
2068: | $changes++;
|
2069: | continue;
|
2070: | }
|
2071: |
|
2072: | if (isset($to[$key]) && $from[$key] != $to[$key]) {
|
2073: | $diff[$key] = $to[$key];
|
2074: | $changes++;
|
2075: | continue;
|
2076: | }
|
2077: | }
|
2078: |
|
2079: | $fromKeys = array_keys($from);
|
2080: | $toKeys = array_keys($to);
|
2081: | $diffKeys = array_diff($toKeys, $fromKeys);
|
2082: | foreach ($diffKeys as $key) {
|
2083: | $diff[$key] = $to[$key];
|
2084: | $changes++;
|
2085: | }
|
2086: |
|
2087: | return array(
|
2088: | 'diff' => $diff,
|
2089: | 'changes' => $changes,
|
2090: | );
|
2091: | }
|
2092: |
|
2093: | |
2094: | |
2095: | |
2096: | |
2097: | |
2098: | |
2099: | |
2100: | |
2101: | |
2102: | |
2103: |
|
2104: | private function diffTables(array $from, array $to)
|
2105: | {
|
2106: | $changes = 0;
|
2107: | $diff = array();
|
2108: | foreach ($from as $key => $value) {
|
2109: | if (!isset($to[$key]) || (isset($to[$key]) && $from[$key] != $to[$key])) {
|
2110: | $diff[$key] = $value;
|
2111: | $changes++;
|
2112: | }
|
2113: | }
|
2114: |
|
2115: | return array(
|
2116: | 'diff' => $diff,
|
2117: | 'changes' => $changes,
|
2118: | );
|
2119: | }
|
2120: |
|
2121: | |
2122: | |
2123: | |
2124: | |
2125: |
|
2126: | private function getVersionDir($dbNamespace = null)
|
2127: | {
|
2128: | if ($dbNamespace === null) {
|
2129: | $dbNamespace = $this->dbNamespace;
|
2130: | }
|
2131: |
|
2132: | $versionDir = DB . 'version' . _DS_ . $dbNamespace;
|
2133: | if (!is_dir($versionDir)) {
|
2134: | mkdir($versionDir, 777, true);
|
2135: | }
|
2136: |
|
2137: | return $versionDir;
|
2138: | }
|
2139: |
|
2140: | |
2141: | |
2142: | |
2143: | |
2144: |
|
2145: | public static function getSchemaLockDefinition($dbNamespace = null)
|
2146: | {
|
2147: | $file = DB . _DS_ . 'build' . _DS_ . 'schema';
|
2148: | if ($dbNamespace) {
|
2149: | $file .= '.' . $dbNamespace;
|
2150: | }
|
2151: | $file .= '.lock';
|
2152: |
|
2153: | if (!(is_file($file) && file_exists($file))) {
|
2154: | return null;
|
2155: | }
|
2156: |
|
2157: | return unserialize(file_get_contents($file));
|
2158: | }
|
2159: |
|
2160: | |
2161: | |
2162: | |
2163: | |
2164: | |
2165: |
|
2166: | public static function getSchemaLockFileName($dbNamespace = null, $backupFileName = false)
|
2167: | {
|
2168: | $file = DB . _DS_ . 'build' . _DS_;
|
2169: |
|
2170: | if ($backupFileName) {
|
2171: | $file .= '~';
|
2172: | }
|
2173: |
|
2174: | $file .= 'schema';
|
2175: |
|
2176: | if ($dbNamespace) {
|
2177: | $file .= '.' . $dbNamespace;
|
2178: | }
|
2179: |
|
2180: | $file .= '.lock';
|
2181: |
|
2182: | return $file;
|
2183: | }
|
2184: | }
|
2185: | |