X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=lib%2Fschema.php;h=2e27955881c235450bbc383ad3e75791cfe11031;hb=b37c33dea2d151dcabf84357b2ea2a0b4f36be3c;hp=9f2899ba8c62fa179bf978a1fddd7c8629838d76;hpb=a923ef971912e7d095cd4cae335abb9095930ecb;p=quix0rs-gnu-social.git diff --git a/lib/schema.php b/lib/schema.php index 9f2899ba8c..2e27955881 100644 --- a/lib/schema.php +++ b/lib/schema.php @@ -142,6 +142,7 @@ class Schema */ public function buildCreateTable($name, $def) { + $def = $this->validateDef($name, $def); $def = $this->filterDef($def); $sql = array(); @@ -149,7 +150,7 @@ class Schema $this->appendColumnDef($sql, $col, $colDef); } - // Primary and unique keys are constraints, so go within + // Primary, unique, and foreign keys are constraints, so go within // the CREATE TABLE statement normally. if (!empty($def['primary key'])) { $this->appendPrimaryKeyDef($sql, $def['primary key']); @@ -161,17 +162,30 @@ class Schema } } - // Multi-value indexes are advisory and for best portability - // should be created as separate statements. + if (!empty($def['foreign keys'])) { + foreach ($def['foreign keys'] as $keyName => $keyDef) { + $this->appendForeignKeyDef($sql, $keyName, $keyDef); + } + } + + // Wrap the CREATE TABLE around the main body chunks... $statements = array(); $statements[] = $this->startCreateTable($name, $def) . "\n" . implode($sql, ",\n") . "\n" . $this->endCreateTable($name, $def); + + // Multi-value indexes are advisory and for best portability + // should be created as separate statements. if (!empty($def['indexes'])) { foreach ($def['indexes'] as $col => $colDef) { $this->appendCreateIndex($statements, $name, $col, $colDef); } } + if (!empty($def['fulltext indexes'])) { + foreach ($def['fulltext indexes'] as $col => $colDef) { + $this->appendCreateFulltextIndex($statements, $name, $col, $colDef); + } + } return $statements; } @@ -225,7 +239,7 @@ class Schema } /** - * Append an SQL fragment with a constraint definition for a primary + * Append an SQL fragment with a constraint definition for a unique * key in a CREATE TABLE statement. * * @param array $sql @@ -234,7 +248,31 @@ class Schema */ function appendUniqueKeyDef(array &$sql, $name, array $def) { - $sql[] = "UNIQUE $name " . $this->buildIndexList($def); + $sql[] = "CONSTRAINT $name UNIQUE " . $this->buildIndexList($def); + } + + /** + * Append an SQL fragment with a constraint definition for a foreign + * key in a CREATE TABLE statement. + * + * @param array $sql + * @param string $name + * @param array $def + */ + function appendForeignKeyDef(array &$sql, $name, array $def) + { + if (count($def) != 2) { + throw new Exception("Invalid foreign key def for $name: " . var_export($def, true)); + } + list($refTable, $map) = $def; + $srcCols = array_keys($map); + $refCols = array_values($map); + $sql[] = "CONSTRAINT $name FOREIGN KEY " . + $this->buildIndexList($srcCols) . + " REFERENCES " . + $this->quoteIdentifier($refTable) . + " " . + $this->buildIndexList($refCols); } /** @@ -251,6 +289,33 @@ class Schema $statements[] = "CREATE INDEX $name ON $table " . $this->buildIndexList($def); } + /** + * Append an SQL statement with an index definition for a full-text search + * index over one or more columns on a table. + * + * @param array $statements + * @param string $table + * @param string $name + * @param array $def + */ + function appendCreateFulltextIndex(array &$statements, $table, $name, array $def) + { + throw new Exception("Fulltext index not supported in this database"); + } + + /** + * Append an SQL statement to drop an index from a table. + * + * @param array $statements + * @param string $table + * @param string $name + * @param array $def + */ + function appendDropIndex(array &$statements, $table, $name) + { + $statements[] = "DROP INDEX $name ON " . $this->quoteIdentifier($table); + } + function buildIndexList(array $def) { // @fixme @@ -477,7 +542,7 @@ class Schema * @return array of SQL statements */ - function buildEnsureTable($tableName, $def) + function buildEnsureTable($tableName, array $def) { try { $old = $this->getTableDef($tableName); @@ -485,18 +550,20 @@ class Schema return $this->buildCreateTable($tableName, $def); } - //$old = $this->filterDef($old); + // Filter the DB-independent table definition to match the current + // database engine's features and limitations. + $def = $this->validateDef($tableName, $def); $def = $this->filterDef($def); - // @fixme check if not present + $statements = array(); $fields = $this->diffArrays($old, $def, 'fields', array($this, 'columnsEqual')); $uniques = $this->diffArrays($old, $def, 'unique keys'); $indexes = $this->diffArrays($old, $def, 'indexes'); + $foreign = $this->diffArrays($old, $def, 'foreign keys'); - $total = $fields['count'] + $uniques['count'] + $indexes['count']; - if ($total == 0) { - // nothing to do - return array(); + // Drop any obsolete or modified indexes ahead... + foreach ($indexes['del'] + $indexes['mod'] as $indexName) { + $this->appendDropIndex($statements, $tableName, $indexName); } // For efficiency, we want this all in one @@ -504,6 +571,10 @@ class Schema $phrase = array(); + foreach ($foreign['del'] + $foreign['mod'] as $keyName) { + $this->appendAlterDropForeign($phrase, $keyName); + } + foreach ($uniques['del'] + $uniques['mod'] as $keyName) { $this->appendAlterDropUnique($phrase, $keyName); } @@ -527,9 +598,23 @@ class Schema $this->appendAlterAddUnique($phrase, $keyName, $def['unique keys'][$keyName]); } - $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(",\n", $phrase); + foreach ($foreign['mod'] + $foreign['add'] as $keyName) { + $this->appendAlterAddForeign($phrase, $keyName, $def['foreign keys'][$keyName]); + } + + $this->appendAlterExtras($phrase, $tableName, $def); + + if (count($phrase) > 0) { + $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(",\n", $phrase); + $statements[] = $sql; + } + + // Now create any indexes... + foreach ($indexes['mod'] + $indexes['add'] as $indexName) { + $this->appendCreateIndex($statements, $tableName, $indexName, $def['indexes'][$indexName]); + } - return array($sql); + return $statements; } function diffArrays($oldDef, $newDef, $section, $compareCallback=null) @@ -617,7 +702,15 @@ class Schema $sql = array(); $sql[] = 'ADD'; $this->appendUniqueKeyDef($sql, $keyName, $def); - $phrase[] = implode(' ', $sql);'ADD CONSTRAINT ' . $keyName; + $phrase[] = implode(' ', $sql); + } + + function appendAlterAddForeign(array &$phrase, $keyName, array $def) + { + $sql = array(); + $sql[] = 'ADD'; + $this->appendForeignKeyDef($sql, $keyName, $def); + $phrase[] = implode(' ', $sql); } function appendAlterDropUnique(array &$phrase, $keyName) @@ -625,6 +718,16 @@ class Schema $phrase[] = 'DROP CONSTRAINT ' . $keyName; } + function appendAlterDropForeign(array &$phrase, $keyName) + { + $phrase[] = 'DROP FOREIGN KEY ' . $keyName; + } + + function appendAlterExtras(array &$phrase, $tableName, array $def) + { + // no-op + } + /** * Quote a db/table/column identifier if necessary. * @@ -647,11 +750,7 @@ class Schema function quoteValue($val) { - if (is_int($val) || is_float($val) || is_double($val)) { - return strval($val); - } else { - return '"' . $this->conn->escapeSimple($val) . '"'; - } + return $this->conn->quoteSmart($val); } /** @@ -778,10 +877,10 @@ class Schema * with plugins written for 0.9.x. * * @param string $tableName - * @param array $defs + * @param array $defs: array of ColumnDef objects * @return array */ - function oldToNew($tableName, $defs) + protected function oldToNew($tableName, array $defs) { $table = array(); $prefixes = array( @@ -791,7 +890,6 @@ class Schema 'big', ); foreach ($defs as $cd) { - $cd->addToTableDef($table); $column = array(); $column['type'] = $cd->type; foreach ($prefixes as $prefix) { @@ -810,7 +908,7 @@ class Schema if (!$cd->nullable) { $column['not null'] = true; } - if ($cd->autoincrement) { + if ($cd->auto_increment) { $column['type'] = 'serial'; } if ($cd->default) { @@ -855,6 +953,31 @@ class Schema return $tableDef; } + /** + * Validate a table definition array, checking for basic structure. + * + * If necessary, converts from an old-style array of ColumnDef objects. + * + * @param string $tableName + * @param array $def: table definition array + * @return array validated table definition array + * + * @throws Exception on wildly invalid input + */ + function validateDef($tableName, array $def) + { + if (isset($def[0]) && $def[0] instanceof ColumnDef) { + $def = $this->oldToNew($tableName, $def); + } + + // A few quick checks :D + if (!isset($def['fields'])) { + throw new Exception("Invalid table definition for $tableName: no fields."); + } + + return $def; + } + function isNumericType($type) { $type = strtolower($type);