From 2e475ceab05c92014e428fe6db6bc79badaad9ca Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Thu, 7 Oct 2010 18:33:02 -0700 Subject: [PATCH] Some more poking at schema stuff, on the road towards a more portable table-modification with the new schema bits --- lib/mysqlschema.php | 25 --------- lib/pgsqlschema.php | 130 +++++++++++++++----------------------------- lib/schema.php | 117 +++++++++++++++++++++++++++++++++------ 3 files changed, 144 insertions(+), 128 deletions(-) diff --git a/lib/mysqlschema.php b/lib/mysqlschema.php index 696b2b8d2a..0a5545286b 100644 --- a/lib/mysqlschema.php +++ b/lib/mysqlschema.php @@ -322,31 +322,6 @@ class MysqlSchema extends Schema return true; } - /** - * Look over a list of column definitions and list up which - * indices will be present - */ - private function _indexList(array $columns) - { - $list = array('uniques' => array(), - 'primary' => array(), - 'indices' => array()); - foreach ($columns as $cd) { - switch ($cd->key) { - case 'UNI': - $list['uniques'][] = $cd->name; - break; - case 'PRI': - $list['primary'][] = $cd->name; - break; - case 'MUL': - $list['indices'][] = $cd->name; - break; - } - } - return $list; - } - /** * Get the unique index key name for a given column on this table */ diff --git a/lib/pgsqlschema.php b/lib/pgsqlschema.php index 2d0f609836..585d05d615 100644 --- a/lib/pgsqlschema.php +++ b/lib/pgsqlschema.php @@ -208,93 +208,6 @@ class PgsqlSchema extends Schema return true; } - - /** - * Ensures that a table exists with the given - * name and the given column definitions. - * - * If the table does not yet exist, it will - * create the table. If it does exist, it will - * 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 boolean success flag - */ - - public function ensureTable($tableName, $columns) - { - // XXX: DB engine portability -> toilet - - try { - $td = $this->getTableDef($tableName); - - } catch (Exception $e) { - if (preg_match('/no such table/', $e->getMessage())) { - return $this->createTable($tableName, $columns); - } else { - throw $e; - } - } - - $cur = $this->_names($td->columns); - $new = $this->_names($columns); - - $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); - - - if (!$newCol->equals($curCol)) { - // BIG GIANT TODO! - // stop it detecting different types and trying to modify on every page request -// $tomod[] = $newCol->name; - } - } - if (count($toadd) + count($todrop) + count($tomod) == 0) { - // nothing to do - return true; - } - - // For efficiency, we want this all in one - // query, instead of using our methods. - - $phrase = array(); - - foreach ($toadd as $columnName) { - $cd = $this->_byName($columns, $columnName); - - $phrase[] = 'ADD COLUMN ' . $this->_columnSql($cd); - } - - foreach ($todrop as $columnName) { - $phrase[] = 'DROP COLUMN ' . $columnName; - } - - foreach ($tomod as $columnName) { - $cd = $this->_byName($columns, $columnName); - - /* brute force */ - $phrase[] = 'DROP COLUMN ' . $columnName; - $phrase[] = 'ADD COLUMN ' . $this->_columnSql($cd); - } - - $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(', ', $phrase); - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; - } - /** * Return the proper SQL for creating or * altering a column. @@ -326,6 +239,49 @@ class PgsqlSchema extends Schema return implode(' ', $line); } + /** + * 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) + { + $prefix = 'ALTER COLUMN ' . $this->quoteIdentifier($columnName) . ' '; + + $oldType = $this->mapType($old); + $newType = $this->mapType($cd); + if ($oldType != $newType) { + $phrase[] .= $prefix . 'TYPE ' . $newType; + } + + if (!empty($old['not null']) && empty($cd['not null'])) { + $phrase[] .= $prefix . 'DROP NOT NULL'; + } else if (empty($old['not null']) && !empty($cd['not null'])) { + $phrase[] .= $prefix . 'SET NOT NULL'; + } + + if (isset($old['default']) && !isset($cd['default'])) { + $phrase[] . $prefix . 'DROP DEFAULT'; + } else if (!isset($old['default']) && isset($cd['default'])) { + $phrase[] . $prefix . 'SET DEFAULT ' . $this->quoteDefaultValue($cd); + } + } + + /** + * Quote a db/table/column identifier if necessary. + * + * @param string $name + * @return string + */ + function quoteIdentifier($name) + { + return '"' . $name . '"'; + } + function mapType($column) { $map = array('serial' => 'bigserial', // FIXME: creates the wrong name for the sequence for some internal sequence-lookup function, so better fix this to do the real 'create sequence' dance. diff --git a/lib/schema.php b/lib/schema.php index 5868627ed7..60d3324ad6 100644 --- a/lib/schema.php +++ b/lib/schema.php @@ -345,12 +345,12 @@ class Schema * @return boolean success flag */ - public function ensureTable($tableName, $columns) + public function ensureTable($tableName, $def) { // XXX: DB engine portability -> toilet try { - $td = $this->getTableDef($tableName); + $old = $this->getTableDef($tableName); } catch (Exception $e) { if (preg_match('/no such table/', $e->getMessage())) { return $this->createTable($tableName, $columns); @@ -359,20 +359,22 @@ class Schema } } - $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 +389,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 +414,90 @@ 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) + { + return $this->conn->escape($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. -- 2.39.5