Namespaces

Classes

1: <?php
2: /**
3: * This file is part of the PHPLucidFrame library.
4: * SchemaManager manages your database schema.
5: *
6: * @package PHPLucidFrame\Core
7: * @since PHPLucidFrame v 1.14.0
8: * @copyright Copyright (c), PHPLucidFrame.
9: * @link http://phplucidframe.com
10: * @license http://www.opensource.org/licenses/mit-license.php MIT License
11: *
12: * This source file is subject to the MIT license that is bundled
13: * with this source code in the file LICENSE
14: */
15:
16: namespace LucidFrame\Core;
17:
18: use LucidFrame\Console\Command;
19:
20: /**
21: * Schema Manager
22: */
23: class SchemaManager
24: {
25: /** @var array The schema definition */
26: protected $schema = array();
27: /** @var string The database driver; currently it allows "mysql" only */
28: private $driver = 'mysql';
29: /** @var array The global schema options */
30: private $defaultOptions;
31: /** @var array The data types for each db driver */
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: # For decimal and float
43: # length => array(p, s) where p is the precision and s is the scale
44: # The precision represents the number of significant digits that are stored for values, and
45: # the scale represents the number of digits that can be stored following the decimal point.
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: # For text, blob, array and json
60: # length => tiny, medium or long
61: # tiny for TINYTEXT, medium for MEDIUMTEXT, long for LONGTEXT
62: # if no length is specified, default to TEXT
63: 'boolean' => 'TINYINT', # TINYINT(1)
64: 'date' => 'DATE',
65: 'datetime' => 'DATETIME',
66: 'time' => 'TIME',
67: ),
68: );
69: /** @var array The relational database relationships */
70: public static $relationships = array('1:m', 'm:1', 'm:m', '1:1');
71: /** @var string The namespace for the database */
72: private $dbNamespace = 'default';
73: /** @var array The array of generated SQL statements */
74: private $sqlStatements = array();
75: /** @var string Version file name extension */
76: private $sqlExtension = '.sqlc';
77: /** @var array Dropped table names */
78: private $droppedTables = array();
79: /** @var array Added table names */
80: private $addedTables = array();
81: /** @var array Dropped field names */
82: private $droppedColumns = array();
83: /** @var array Added column names */
84: private $addedColumns = array();
85: /** @var array Renamed table names */
86: private $tablesRenamed = array();
87: /** @var array Renamed field names */
88: private $columnsRenamed = array();
89:
90: /**
91: * Constructor
92: * @param array $schema The array of schema definition
93: * @param string $dbNamespace The namespace for the database schema
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: * Setter for the property `schema`
114: * @param array $schema The array of schema definition
115: * @return object SchemaManager
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: * Getter for the property `schema`
132: * @return array The array of schema definition
133: */
134: public function getSchema()
135: {
136: return $this->schema;
137: }
138:
139: /**
140: * Setter for the property `driver`
141: * Currently driver allows mysql only, that's why this method is private
142: * @param string $driver Database driver
143: * @return object SchemaManager
144: */
145: private function setDriver($driver)
146: {
147: $this->driver = $driver;
148:
149: return $this;
150: }
151:
152: /**
153: * Getter for the property `driver`
154: * @return string
155: */
156: public function getDriver()
157: {
158: return $this->driver;
159: }
160:
161: /**
162: * Setter for the property `dbNamespace`
163: * @param string $namespace The namespace
164: * @return object SchemaManager
165: */
166: public function setDbNamespace($namespace)
167: {
168: $this->dbNamespace = $namespace;
169:
170: return $this;
171: }
172:
173: /**
174: * Getter for the property `dbNamespace`
175: * @return string
176: */
177: public function getDbNamespace()
178: {
179: return $this->dbNamespace;
180: }
181:
182: /**
183: * Get default field type for primary key
184: * @return array Array of field type options
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: * Get relationship options with defaults
198: * @param array $relation The relationship options
199: * @param string $fkTable The FK table
200: * @return array The relationship options with defaults
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 { // when more than one relationship for the same table
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: * Get field statement for CREATE TABLE
231: * @param string $field The field name
232: * @param array $definition SchemaManager field definition
233: * @param string $collate The collation for the field; if it is null, db collation is used
234: * @return string The field statement
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: # COLLATE for text fields
252: $statement .= ' COLLATE ';
253: $statement .= $collate ? $collate : $this->schema['_options']['collate'];
254: }
255:
256: if (isset($definition['unsigned'])) {
257: # unsigned
258: $statement .= ' unsigned';
259: }
260:
261: if (isset($definition['null'])) {
262: # true: DEFAULT NULL
263: # false: NOT NULL
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: # AUTO_INCREMENT
273: $statement .= ' AUTO_INCREMENT';
274: }
275:
276: return $statement;
277: }
278:
279: /**
280: * Get field type
281: * @param array $definition SchemaManager field definition
282: * @return string The underlying db field type
283: */
284: public function getVendorFieldType(&$definition)
285: {
286: if (!isset(self::$dataTypes[$this->driver][$definition['type']])) {
287: # if no data type is defined
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: # if type is boolean, force unsigned
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: * Get field length
323: * @param array $definition SchemaManager field definition
324: * @return integer The field length
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: * Get foreign key schema definition
356: * @param string $fkTable The reference table name
357: * @param array $relation The relationship definition
358: * @return array Foreign key schema definition
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: * Get foreign key constraint definition
393: * @param string $fkTable The reference table name
394: * @param array $relation The relationship definition
395: * @param array $schema The whole schema definition
396: * @return array|null Foreign key constraint definition
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: * Process schema
432: * @return boolean TRUE for success; FALSE for failure
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: # Populate primary key fields
444: $this->populatePrimaryKeys($schema);
445: # Add ManyToMany tables to the schema
446: $constraints = $this->populatePivots($schema);
447:
448: $pkFields = $this->getPrimaryKeys();
449:
450: $sql = array();
451: $sql[] = 'SET FOREIGN_KEY_CHECKS=0;';
452:
453: # Create each table
454: foreach ($schema as $table => $def) {
455: $fullTableName = db_table($table); # The full table name with prefix
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: # Generate FK constraints
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: # Get the current version
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: * Export the built schema definition into a file
493: * @param string $dbNamespace The namespace for the database
494: * @param boolean $backup Create a backup file or not
495: * @return boolean TRUE for success; FALSE for failure
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: * Import schema to the database
522: * @param string $dbNamespace The namespace for the database
523: * @return boolean TRUE for success; FALSE for failure
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: * Export sql dump file
545: * @param string $dbNamespace The namespace for the database
546: * @return boolean TRUE for success; FALSE for failure
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: * Update schema to the latest version
571: * @param Command $cmd LucidFrame\Console\Command
572: * @param string $dbNamespace The namespace for the database
573: * @return boolean TRUE for success; FALSE for failure
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: # Migrate to the latest version
593: $version = $this->migrate($versions, $schemaFrom, $schemaTo);
594:
595: if ($version) {
596: # Update build version
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: # if there is no version file or if the schema is up-to-date;
609: if ($isSchemaChanged) {
610: # but if the schema is changed, get the difference
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: # Confirm before executing the queries
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: # Export version sql file
652: if ($dbVersion = $this->exportVersionFile($sql['up'], $dbNamespace)) {
653: # Build schema
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: * Find the schema difference and generate SQL file
668: * @param Command $cmd LucidFrame\Console\Command
669: * @param string $dbNamespace The namespace for the database
670: * @return boolean TRUE for SQL file exported; FALSE for no updates
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: # if there is no version file or if the schema is up-to-date;
694: if ($isSchemaChanged) {
695: # but if the schema is changed, get the difference
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: * Export the SQL file with .sqlc extension in the directory /db/version/{namespace}/
714: * @param array $sql Array of SQL statements
715: * @param string $dbNamespace The namespace for the database
716: * @return mixed The version number on success or FALSE on failure
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: # Export version sql file
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: * Get schema difference and generate SQL statements
748: * @param array $schemaFrom Array of the current schema data
749: * @param array $schemaTo Array of the updated schema data
750: * @param Command $cmd LucidFrame\Console\Command
751: * @return array
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: # Detect table renaming
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: # Get user confirmation for table renaming
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: # Detect field renaming
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: # Get user confirmation for column renaming
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: # Detect schema differences and generate SQL statements
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: # Existing table
805: if ($renamedTable) {
806: # if the table is renamed
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: # Existing field
817: if ($renamedField) {
818: $field = $renamedField;
819: }
820:
821: $diff = $fieldDef !== $schemaTo[$table][$field];
822: if ($diff) {
823: # Change field
824: if (in_array($field, self::$relationships)) {
825: continue;
826: }
827:
828: if ($field == 'options') {
829: if (!empty($fieldDef['m:m'])) {
830: # if it is many-to-many table, skip
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: // Drop old composite unique indices
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: // Add new composite unique indices
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: # Drop or change field
892: if (in_array($field, array('m:m', '1:m', 'm:1'))) {
893: continue;
894: }
895:
896: if (in_array($table . '.' . $field, $fieldNamesChanged)) {
897: # The field name is already changed, no need to drop it
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: # Rename table
914: if ($renamedTable) {
915: $sql['up'][] = 'RENAME TABLE `' . $fullTableName . '` TO `' . db_table($renamedTable) . '`;';
916: }
917: } else {
918: # Drop table
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: # Create a new table
940: $createSql = trim($this->createTableStatement($table, $schemaTo, $pkFields, $constraints));
941: if ($createSql) {
942: $sql['up'][] = $createSql;
943: }
944: # if new table, no need to lookup field changes and then continue the next table
945: continue;
946: } else {
947: $tableFrom = $oldTable;
948: }
949: }
950:
951: # Add new fields for existing table
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: # Add a new field
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: * Migrate db to the latest version
977: * @param array $versions Array of versions (older to newer)
978: * @param array $schemaFrom Array of the current schema data
979: * @param array $schemaTo Array of the updated schema data
980: * @param bool $verbose Output in console or not
981: * @return string|bool
982: */
983: public function migrate(array $versions, array $schemaFrom, array $schemaTo, $verbose = true)
984: {
985: # Drop all foreign key constraints from the old schema
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: # Re-create all foreign key constraints from the new schema
1038: if ($createConstraintSql = $this->createConstraintStatements($this->getConstraints($schemaTo))) {
1039: $this->executeQueries($this->dbNamespace, $createConstraintSql);
1040: }
1041:
1042: return $version;
1043: }
1044:
1045: /**
1046: * Execute batch queries
1047: *
1048: * @param string $dbNamespace The namespace for the database
1049: * @param array $queries Array of SQL statements
1050: * @return boolean TRUE for success; FALSE for failure
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: # back to default db
1096: db_switch($this->dbNamespace);
1097: }
1098:
1099: if ($error == true) {
1100: return false;
1101: } else {
1102: return $count;
1103: }
1104: }
1105:
1106: /**
1107: * Check if schema changed
1108: * @param array $from The last schema
1109: * @param array $to The changed schema
1110: * @return bool TRUE if the schema is changed, otherwise FALSE
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: * Get the current db version
1135: * @return integer The version number
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: * Check db version files in the version directory against the current version in $schema[_options][version]
1149: * @param array $schema The schema to check in
1150: * @return mixed
1151: * 0 if there is no version file;
1152: * 1 if the schema is up-to-date;
1153: * ARRAY if there is version file to migrate
1154: */
1155: public function checkVersions(array $schema)
1156: {
1157: # Check if there is version files in the version directory
1158: $versionDir = DB . 'version' . _DS_ . $this->dbNamespace;
1159: if (!is_dir($versionDir)) {
1160: return 0;
1161: }
1162:
1163: $files = scandir($versionDir);
1164: rsort($files); # sort file name by descending
1165:
1166: # Check if the current schema version is up-to-date
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: # Filter all version greater than the last version
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: * Check if the schema is parsed and fully loaded
1202: * @return boolean TRUE/FALSE
1203: */
1204: public function isLoaded()
1205: {
1206: return isset($this->schema['_options']['pk']);
1207: }
1208:
1209: /**
1210: * Check if a table or field is renamed
1211: *
1212: * @param string $needle The table or field name
1213: * @param array $haystack Array of renamed fields or tables
1214: * @return mixed The renamed table name or field name or false
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: * Check if the table exists
1227: * @param string $table The table name
1228: * @return boolean TRUE if the table exists, otherwise FALSE
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: * Check if a field exists
1243: * @param string $table The table name
1244: * @param string $field The field name
1245: * @return boolean TRUE if the table exists, otherwise FALSE
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: * Check if the table has the timestamp fields or not
1260: * @param string $table The table name without prefix
1261: * @return boolean TRUE if the table has the timestamp fields, otherwise FALSE
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: * Check if the table has the slug field or not
1276: * @param string $table The table name without prefix
1277: * @return boolean TRUE if the table has the slug field, otherwise FALSE
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: * Get data type of the field
1292: * @param string $table The table name
1293: * @param string $field The field name in the table
1294: * @return string The data type or null if there is no field
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: * Get schema options if it is defined
1309: * otherwise return the default options
1310: *
1311: * @return array
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: * Get table options if it is defined
1326: * otherwise return the default options
1327: *
1328: * @param array $tableDef The table definition
1329: * @return array
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: * Populate primary keys acccording to the schema defined
1354: * @param array $schema The database schema
1355: * @return array
1356: */
1357: public function populatePrimaryKeys(&$schema)
1358: {
1359: # Populate primary key fields
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: # PK Field(s)
1373: $pkFields[$table] = array();
1374: if (isset($def['options']['pk'])) {
1375: foreach ($def['options']['pk'] as $pk) {
1376: if (isset($def[$pk])) {
1377: # user-defined PK field type
1378: $pkFields[$table][$pk] = $def[$pk];
1379: } else {
1380: # default PK field type
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: * Populate pivot tables (joint tables fo many-to-many relationship) into the schema
1396: * @param array $schema The database schema
1397: * @return array Array of constraints
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: # if the joint table has already been defined
1412: continue;
1413: }
1414:
1415: if (isset($schema[$table . '_to_' . $fkTable]) || isset($schema[$fkTable . '_to_' . $table])) {
1416: # if the joint table has already been defined
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: # table1_to_table2
1426: $jointTable = !empty($joint['table']) ? $joint['table'] : $table . '_to_' . $fkTable;
1427: $schema[$jointTable]['options'] = array(
1428: 'pk' => array(),
1429: 'timestamps' => false, # no need timestamp fields for many-to-many table
1430: 'm:m' => true
1431: ) + $this->defaultOptions;
1432:
1433: # table1.field
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: # Get FK constraints
1442: $constraint = $this->getFKConstraint($table, $rel, $schema);
1443: if ($constraint) {
1444: $constraints[$jointTable][$field] = $constraint;
1445: }
1446: }
1447:
1448: # table2.field
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: # Get FK constraints
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: * Generate CREATE TABLE SQL
1474: * @param string $table The new table name
1475: * @param array $schema The database schema
1476: * @param array $pkFields Array of PK fields
1477: * @param array $constraints Array of FK constraints
1478: * @return string
1479: */
1480: public function createTableStatement($table, &$schema, &$pkFields, &$constraints)
1481: {
1482: if (!isset($schema[$table])) {
1483: return null;
1484: }
1485:
1486: $def = $schema[$table]; # The table definition
1487: $fullTableName = db_table($table); # The full table name with prefix
1488: $fkFields = array(); # Populate foreign key fields
1489:
1490: # OneToMany
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: # Get FK field definition
1503: $fkFields[$field] = $this->getFKField($fkTable, $rel);
1504: # Get FK constraints
1505: $constraint = $this->getFKConstraint($fkTable, $rel, $schema);
1506: if ($constraint) {
1507: $constraints[$table][$field] = $constraint;
1508: }
1509: }
1510: }
1511: }
1512: }
1513:
1514: # OneToOne
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: # Get FK field definition
1528: $fkFields[$field] = $this->getFKField($fkTable, $rel);
1529: # Get FK constraints
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: # ManyToMany table FK indexes
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: # CREATE TABLE Statement
1558: $sql = "CREATE TABLE IF NOT EXISTS `{$fullTableName}` (" . PHP_EOL;
1559:
1560: # loop the fields
1561: $autoinc = false;
1562: foreach ($def as $name => $rule) {
1563: # Skip for relationship and option definitions
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: # if there is any unique index
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: # Indexes
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: // Unique indexes for composite unique fields
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: # Primary key indexes
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: * Generate foreign key constraints SQL statements
1621: * @param array $constraints Array of populated constraints
1622: * @return array Array of SQL statements
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: # FK constraints
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: * Generate DROP foreign key constraints SQL statements
1654: * @param array $constraints Array of populated constraints
1655: * @return array Array of SQL statements
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: # FK constraints
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: * Set the populated primary keys into the schema database options
1691: * @param array $pkFields Array of primary keys
1692: * @return void
1693: */
1694: public function setPrimaryKeys($pkFields)
1695: {
1696: $this->schema['_options']['pk'] = $pkFields;
1697: }
1698:
1699: /**
1700: * Get the populated primary keys from the schema database options
1701: * @param array $schema The schema definition
1702: * @return array Array of primary keys
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: * Set the populated foreign key constraints into the schema database options
1715: * @param array $constraints Array of FK constraints
1716: * @return void
1717: */
1718: public function setConstraints($constraints)
1719: {
1720: $this->schema['_options']['fkConstraints'] = $constraints;
1721: }
1722:
1723: /**
1724: * Get the populated foreign key constraints from the schema database options
1725: * @param array $schema The schema definition
1726: * @return array Array of FK constraints
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: * Return table collation from the schema definition
1739: * @param string $table The table name
1740: * @param array $schema The schema definition (optional)
1741: * @return string
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: * Try to find columns that only changed their name, rename operations maybe cheaper than add/drop
1754: * however ambiguities between different possibilities should not lead to renaming at all.
1755: *
1756: * @param string $needle The table or field name
1757: * @param array $from The table or field definition to check difference against $to
1758: * @param array $to The table or field definition to check difference against $from
1759: * @param string $table The table name or null
1760: *
1761: * @return mixed The similar name or false
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: # Table definition comparison
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: # Field definition comparison
1812: $diff = $this->diffColumns($from[$needle], $to[$name]);
1813: $changes = $diff['changes'];
1814: }
1815: $percentChanges = 100 - $changes;
1816:
1817: # Check similar chars
1818: similar_text(strtolower($needle), strtolower($name), $percent1);
1819: $matchingText[$name] = (int)round($percent1);
1820:
1821: # Check sound
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: # not similar_text, but same sound
1832: $scores[] = $percent1 + $percentChanges;
1833: }
1834:
1835: if ($percentByTwo >= 95 && $percentByTwo <= 100) {
1836: # similar_text + metaphone
1837: $scores[] = $percentByTwo + $percentChanges;
1838: }
1839:
1840: if ($percent1 > 50 && $percent1 < 100) {
1841: # similar_text only
1842: $scores[] = $percent1 + $percentChanges;
1843: }
1844:
1845: if ($compared == 'field' && strpos(strtolower($needle), 'id') !== false && strpos(strtolower($name), 'id') !== false) {
1846: # id field
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: * Try to find out dropped tables
1880: * @param array $schemaFrom The schema definion from
1881: * @param array $schemaTo The schema definion to
1882: * @return void
1883: */
1884: private function detectDroppedTables(array $schemaFrom, array $schemaTo)
1885: {
1886: # Find out dropped tables and columns
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: * Try to find out possible new tables
1901: * @param array $schemaFrom The schema definion from
1902: * @param array $schemaTo The schema definion to
1903: * @return void
1904: */
1905: private function detectAddedTables(array $schemaFrom, array $schemaTo)
1906: {
1907: # Find out possible new tables and columns
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: * Try to find out dropped tables
1922: * @param array $schemaFrom The schema definion from
1923: * @param array $schemaTo The schema definion to
1924: * @return void
1925: */
1926: private function detectDroppedColumns(array $schemaFrom, array $schemaTo)
1927: {
1928: # Find out dropped tables and columns
1929: foreach ($schemaFrom as $table => $tableDef) {
1930: if ($table == '_options') {
1931: continue;
1932: }
1933:
1934: # Add new fields for existing table
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: # Add a new field
1942: $this->droppedColumns[$table][] = $field;
1943: }
1944: }
1945: }
1946: }
1947:
1948: /**
1949: * Try to find out possible new columns
1950: * @param array $schemaFrom The schema definition from
1951: * @param array $schemaTo The schema definition to
1952: * @return void
1953: */
1954: private function detectAddedColumns(array $schemaFrom, array $schemaTo)
1955: {
1956: # Find out possible new tables and columns
1957: foreach ($schemaTo as $table => $tableDef) {
1958: if ($table == '_options') {
1959: continue;
1960: }
1961:
1962: # Add new fields for existing table
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: # Add a new field
1970: $this->addedColumns[$table][] = $field;
1971: }
1972: }
1973: }
1974: }
1975:
1976:
1977: /**
1978: * Try to find tables and columns that only changed their name, rename operations maybe cheaper than add/drop
1979: * however ambiguities between different possibilities should not lead to renaming at all.
1980: *
1981: * @param array $schemaFrom The schema definition from
1982: * @param array $schemaTo The schema definition to
1983: * @return void
1984: */
1985: private function detectTableRenamings(array $schemaFrom, array $schemaTo)
1986: {
1987: $this->detectDroppedTables($schemaFrom, $schemaTo);
1988: $this->detectAddedTables($schemaFrom, $schemaTo);
1989:
1990: # Detect table and column renaming
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: * Try to find tables and columns that only changed their name, rename operations maybe cheaper than add/drop
2005: * however ambiguities between different possibilities should not lead to renaming at all.
2006: *
2007: * @param array $schemaFrom The schema definion from
2008: * @param array $schemaTo The schema definion to
2009: * @return void
2010: */
2011: private function detectColumnRenamings(array $schemaFrom, array $schemaTo)
2012: {
2013: $this->detectDroppedColumns($schemaFrom, $schemaTo);
2014: $this->detectAddedColumns($schemaFrom, $schemaTo);
2015:
2016: # Detect table and column renaming
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: # Check if there is similar field name
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: * Computes the difference of two arrays similar to the native function `array_diff`
2052: * which can't be used for multi-dimensional arrays
2053: *
2054: * @param array $from The array to compare from
2055: * @param array $to An array to compare against
2056: *
2057: * @return array The array with two keys:
2058: * `diff` - an array containing all the entries from $from that are not present in the other array $to.
2059: * `changes` - number of changes; the more differences, the higher numbers; 0 means the two arrays are identical
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: * Computes the difference of two arrays similar to the native function `array_diff`
2095: * which can't be used for multi-dimensional arrays
2096: *
2097: * @param array $from The array to compare from
2098: * @param array $to An array to compare against
2099: *
2100: * @return array The array with two keys:
2101: * `diff` - an array containing all the entries from $from that are not present in the other array $to.
2102: * `changes` - number of changes; the more differences, the higher numbers; 0 means the two arrays are identical
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: * Get the version directory path
2123: * @param string $dbNamespace The namespace for the database
2124: * @return string The full directory path
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: * Get schema definition from the built schema file
2142: * @param string $dbNamespace The namespace for the database
2143: * @return array The schema definition; NULL when there is no file
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: * Get schema lock file name
2162: * @param string $dbNamespace The namespace for the database
2163: * @param boolean $backupFileName If true, ~ will be prefixed in the file name
2164: * @return string The file name with full path
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:
PHPLucidFrame 3.5 API documentation generated by ApiGen v7.0.0-alpha.6