X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=lib%2Fschema.php;h=acc9412841935aa8d4cb5ffc7c37de705ff68dc8;hb=2c9f877ab5b79f4a0734149f3769bfa0ddf4dd4d;hp=d20bced4a740e056deab2b183c921adf05c643c9;hpb=033a7570133d4183c2e162859e9b85fe7df3e40b;p=quix0rs-gnu-social.git diff --git a/lib/schema.php b/lib/schema.php index d20bced4a7..acc9412841 100644 --- a/lib/schema.php +++ b/lib/schema.php @@ -128,56 +128,133 @@ class Schema public function createTable($name, $columns) { - $uniques = array(); - $primary = array(); - $indices = array(); + $statements = $this->buildCreateTable($tableName, $def); + return $this->runSqlSet($statements); + } - $sql = "CREATE TABLE $name (\n"; + /** + * Build a set of SQL statements to create a table with the given + * name and columns. + * + * @param string $name Name of the table + * @param array $def Table definition array + * + * @return boolean success flag + */ + public function buildCreateTable($name, $def) + { + $sql = array(); - for ($i = 0; $i < count($columns); $i++) { + foreach ($def['fields'] as $col => $colDef) { + $this->appendColumnDef($sql, $col, $colDef); + } - $cd =& $columns[$i]; + // Primary and unique keys are constraints, so go within + // the CREATE TABLE statement normally. + if (!empty($def['primary key'])) { + $this->appendPrimaryKeyDef($sql, $def['primary key']); + } - if ($i > 0) { - $sql .= ",\n"; + if (!empty($def['unique keys'])) { + foreach ($def['unique keys'] as $col => $colDef) { + $this->appendUniqueKeyDef($sql, $col, $colDef); } + } - $sql .= $this->_columnSql($cd); - - switch ($cd->key) { - case 'UNI': - $uniques[] = $cd->name; - break; - case 'PRI': - $primary[] = $cd->name; - break; - case 'MUL': - $indices[] = $cd->name; - break; + // Multi-value indexes are advisory and for best portability + // should be created as separate statements. + $statements = array(); + $statements[] = $this->startCreateTable($name, $def) . "\n" . + implode($sql, ",\n") . "\n" . + $this->endCreateTable($name, $def); + if (!empty($def['indexes'])) { + foreach ($def['indexes'] as $col => $colDef) { + $this->appendCreateIndex($statements, $name, $col, $colDef); } } - if (count($primary) > 0) { // it really should be... - $sql .= ",\nconstraint primary key (" . implode(',', $primary) . ")"; - } + return $statements; + } - foreach ($uniques as $u) { - $sql .= ",\nunique index {$name}_{$u}_idx ($u)"; - } + /** + * Set up a 'create table' SQL statement. + * + * @param string $name table name + * @param array $def table definition + * @param $string + */ + function startCreateTable($name, array $def) + { + return 'CREATE TABLE ' . $this->quoteIdentifier($name) . ' ('; + } - foreach ($indices as $i) { - $sql .= ",\nindex {$name}_{$i}_idx ($i)"; - } + /** + * Close out a 'create table' SQL statement. + * + * @param string $name table name + * @param array $def table definition + * @return string + */ + function endCreateTable($name, array $def) + { + return ')'; + } - $sql .= "); "; + /** + * Append an SQL fragment with a column definition in a CREATE TABLE statement. + * + * @param array $sql + * @param string $name + * @param array $def + */ + function appendColumnDef(array &$sql, $name, array $def) + { + $sql[] = "$name " . $this->columnSql($def); + } - $res = $this->conn->query($sql); + /** + * Append an SQL fragment with a constraint definition for a primary + * key in a CREATE TABLE statement. + * + * @param array $sql + * @param array $def + */ + function appendPrimaryKeyDef(array &$sql, array $def) + { + $sql[] = "PRIMARY KEY " . $this->buildIndexList($def); + } - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } + /** + * Append an SQL fragment with a constraint definition for a primary + * key in a CREATE TABLE statement. + * + * @param array $sql + * @param string $name + * @param array $def + */ + function appendUniqueKeyDef(array &$sql, $name, array $def) + { + $sql[] = "UNIQUE $key " . $this->buildIndexList($def); + } - return true; + /** + * Append an SQL statement with an index definition for an advisory + * index over one or more columns on a table. + * + * @param array $statements + * @param string $table + * @param string $name + * @param array $def + */ + function appendCreateIndex(array &$statements, $table, $name, array $def) + { + $statements[] = "CREATE INDEX $name ON $table " . $this->buildIndexList($def); + } + + function buildIndexList(array $def) + { + // @fixme + return '(' . implode(',', array_map(array($this, 'quoteIdentifier'), $def)) . ')'; } /** @@ -339,40 +416,84 @@ class Schema * alter the table to match the column definitions. * * @param string $tableName name of the table - * @param array $columns array of ColumnDef - * objects for the table + * @param array $def Table definition array * * @return boolean success flag */ - public function ensureTable($tableName, $columns) + public function ensureTable($tableName, $def) { - // XXX: DB engine portability -> toilet + $statements = $this->buildEnsureTable($tableName, $def); + return $this->runSqlSet($statements); + } + + /** + * Run a given set of SQL commands on the connection in sequence. + * Empty input is ok. + * + * @fixme if multiple statements, wrap in a transaction? + * @param array $statements + * @return boolean success flag + */ + function runSqlSet(array $statements) + { + $ok = true; + foreach ($statements as $sql) { + $res = $this->conn->query($sql); + + if (PEAR::isError($res)) { + throw new Exception($res->getMessage()); + } + } + return $ok; + } + + /** + * Check a table's status, and if needed build a set + * of SQL statements which change it to be consistent + * with the given table definition. + * + * If the table does not yet exist, statements will + * be returned to create the table. If it does exist, + * statements will be returned to alter the table to + * match the column definitions. + * + * @param string $tableName name of the table + * @param array $columns array of ColumnDef + * objects for the table + * + * @return array of SQL statements + */ + function buildEnsureTable($tableName, $def) + { try { - $td = $this->getTableDef($tableName); + $old = $this->getTableDef($tableName); } catch (Exception $e) { + // @fixme this is a terrible check :D if (preg_match('/no such table/', $e->getMessage())) { - return $this->createTable($tableName, $columns); + return $this->buildCreateTable($tableName, $def); } else { throw $e; } } - $cur = $this->_names($td->columns); - $new = $this->_names($columns); + $cur = array_keys($old['fields']); + $new = array_keys($def['fields']); $toadd = array_diff($new, $cur); $todrop = array_diff($cur, $new); $same = array_intersect($new, $cur); $tomod = array(); - foreach ($same as $m) { - $curCol = $this->_byName($td->columns, $m); - $newCol = $this->_byName($columns, $m); + // Find which fields have actually changed definition + // in a way that we need to tweak them for this DB type. + foreach ($same as $name) { + $curCol = $old['fields'][$name]; + $newCol = $cur['fields'][$name]; - if (!$newCol->equals($curCol)) { - $tomod[] = $newCol->name; + if (!$this->columnsEqual($curCol, $newCol)) { + $tomod[] = $name; } } @@ -387,19 +508,18 @@ class Schema $phrase = array(); foreach ($toadd as $columnName) { - $cd = $this->_byName($columns, $columnName); - - $phrase[] = 'ADD COLUMN ' . $this->_columnSql($cd); + $this->appendAlterAddColumn($phrase, $columnName, + $def['fields'][$columnName]); } foreach ($todrop as $columnName) { - $phrase[] = 'DROP COLUMN ' . $columnName; + $this->appendAlterModifyColumn($phrase, $columnName, + $old['fields'][$columnName], + $def['fields'][$columnName]); } foreach ($tomod as $columnName) { - $cd = $this->_byName($columns, $columnName); - - $phrase[] = 'MODIFY COLUMN ' . $this->_columnSql($cd); + $this->appendAlterDropColumn($phrase, $columnName); } $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(', ', $phrase); @@ -413,6 +533,94 @@ class Schema return true; } + /** + * Append phrase(s) to an array of partial ALTER TABLE chunks in order + * to add the given column definition to the table. + * + * @param array $phrase + * @param string $columnName + * @param array $cd + */ + function appendAlterAddColumn(array &$phrase, $columnName, array $cd) + { + $phrase[] = 'ADD COLUMN ' . + $this->quoteIdentifier($columnName) . + ' ' . + $this->columnSql($cd); + } + + /** + * Append phrase(s) to an array of partial ALTER TABLE chunks in order + * to alter the given column from its old state to a new one. + * + * @param array $phrase + * @param string $columnName + * @param array $old previous column definition as found in DB + * @param array $cd current column definition + */ + function appendAlterModifyColumn(array &$phrase, $columnName, array $old, array $cd) + { + $phrase[] = 'MODIFY COLUMN ' . + $this->quoteIdentifier($columnName) . + ' ' . + $this->columnSql($cd); + } + + /** + * Append phrase(s) to an array of partial ALTER TABLE chunks in order + * to drop the given column definition from the table. + * + * @param array $phrase + * @param string $columnName + */ + function appendAlterDropColumn(array &$phrase, $columnName) + { + $phrase[] = 'DROP COLUMN ' . $this->quoteIdentifier($columnName); + } + + /** + * Quote a db/table/column identifier if necessary. + * + * @param string $name + * @return string + */ + function quoteIdentifier($name) + { + return $name; + } + + function quoteDefaultValue($cd) + { + if ($cd['type'] == 'datetime' && $cd['default'] == 'CURRENT_TIMESTAMP') { + return $cd['default']; + } else { + return $this->quoteValue($cd['default']); + } + } + + function quoteValue($val) + { + if (is_int($val) || is_float($val) || is_double($val)) { + return strval($val); + } else { + return '"' . $this->conn->escapeSimple($val) . '"'; + } + } + + /** + * Check if two column definitions are equivalent. + * The default implementation checks _everything_ but in many cases + * you may be able to discard a bunch of equivalencies. + * + * @param array $a + * @param array $b + * @return boolean + */ + function columnsEqual(array $a, array $b) + { + return !array_diff_assoc($a, $b) && !array_diff_assoc($b, $a); + } + /** * Returns the array of names from an array of * ColumnDef objects. @@ -469,11 +677,11 @@ class Schema function columnSql(array $cd) { $line = array(); - $line[] = $this->typeAndSize(); + $line[] = $this->typeAndSize($cd); if (isset($cd['default'])) { $line[] = 'default'; - $line[] = $this->quoted($cd['default']); + $line[] = $this->quoteDefaultValue($cd); } else if (!empty($cd['not null'])) { // Can't have both not null AND default! $line[] = 'not null'; @@ -515,6 +723,17 @@ class Schema } } + /** + * Map a native type back to an independent type + size + * + * @param string $type + * @return array ($type, $size) -- $size may be null + */ + protected function reverseMapType($type) + { + return array($type, null); + } + /** * Convert an old-style set of ColumnDef objects into the current * Drupal-style schema definition array, for backwards compatibility @@ -583,6 +802,37 @@ class Schema return $table; } + + function isNumericType($type) + { + $type = strtolower($type); + $known = array('int', 'serial', 'numeric'); + return in_array($type, $known); + } + + /** + * Pull info from the query into a fun-fun array of dooooom + * + * @param string $sql + * @return array of arrays + */ + protected function fetchQueryData($sql) + { + $res = $this->conn->query($sql); + if (PEAR::isError($res)) { + throw new Exception($res->getMessage()); + } + + $out = array(); + $row = array(); + while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { + $out[] = $row; + } + $res->free(); + + return $out; + } + } class SchemaTableMissingException extends Exception