diff options
Diffstat (limited to 'lib/pgsqlschema.php')
| -rw-r--r-- | lib/pgsqlschema.php | 81 |
1 files changed, 55 insertions, 26 deletions
diff --git a/lib/pgsqlschema.php b/lib/pgsqlschema.php index 91bc09667..272f7eff6 100644 --- a/lib/pgsqlschema.php +++ b/lib/pgsqlschema.php @@ -41,6 +41,7 @@ if (!defined('STATUSNET')) { * @category Database * @package StatusNet * @author Evan Prodromou <evan@status.net> + * @author Brenda Wallace <shiny@cpan.org> * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 * @link http://status.net/ */ @@ -61,7 +62,8 @@ class PgsqlSchema extends Schema public function getTableDef($name) { - $res = $this->conn->query("select *, column_default as default, is_nullable as Null, udt_name as Type, column_name AS Field from INFORMATION_SCHEMA.COLUMNS where table_name = '$name'"); + $res = $this->conn->query("SELECT *, column_default as default, is_nullable as Null, + udt_name as Type, column_name AS Field from INFORMATION_SCHEMA.COLUMNS where table_name = '$name'"); if (PEAR::isError($res)) { throw new Exception($res->getMessage()); @@ -72,10 +74,12 @@ class PgsqlSchema extends Schema $td->name = $name; $td->columns = array(); + if ($res->numRows() == 0 ) { + throw new Exception('no such table'); //pretend to be the msyql error. yeah, this sucks. + } $row = array(); while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { -// var_dump($row); $cd = new ColumnDef(); $cd->name = $row['field']; @@ -139,6 +143,7 @@ class PgsqlSchema extends Schema $uniques = array(); $primary = array(); $indices = array(); + $onupdate = array(); $sql = "CREATE TABLE $name (\n"; @@ -151,7 +156,6 @@ class PgsqlSchema extends Schema } $sql .= $this->_columnSql($cd); - switch ($cd->key) { case 'UNI': $uniques[] = $cd->name; @@ -166,23 +170,23 @@ class PgsqlSchema extends Schema } if (count($primary) > 0) { // it really should be... - $sql .= ",\nconstraint primary key (" . implode(',', $primary) . ")"; + $sql .= ",\n PRIMARY KEY (" . implode(',', $primary) . ")"; } + $sql .= "); "; + + foreach ($uniques as $u) { - $sql .= ",\nunique index {$name}_{$u}_idx ($u)"; + $sql .= "\n CREATE index {$name}_{$u}_idx ON {$name} ($u); "; } foreach ($indices as $i) { - $sql .= ",\nindex {$name}_{$i}_idx ($i)"; + $sql .= "CREATE index {$name}_{$i}_idx ON {$name} ($i)"; } - - $sql .= "); "; - $res = $this->conn->query($sql); if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + throw new Exception($res->getMessage(). ' SQL was '. $sql); } return true; @@ -210,6 +214,22 @@ class PgsqlSchema extends Schema } /** + * Translate the (mostly) mysql-ish column types into somethings more standard + * @param string column type + * + * @return string postgres happy column type + */ + private function _columnTypeTranslation($type) { + $map = array( + 'datetime' => 'timestamp', + ); + if(!empty($map[$type])) { + return $map[$type]; + } + return $type; + } + + /** * Adds an index to a table. * * If no name is provided, a name will be made up based @@ -302,7 +322,7 @@ class PgsqlSchema extends Schema public function modifyColumn($table, $columndef) { - $sql = "ALTER TABLE $table MODIFY COLUMN " . + $sql = "ALTER TABLE $table ALTER COLUMN TYPE " . $this->_columnSql($columndef); $res = $this->conn->query($sql); @@ -359,6 +379,7 @@ class PgsqlSchema extends Schema try { $td = $this->getTableDef($tableName); + } catch (Exception $e) { if (preg_match('/no such table/', $e->getMessage())) { return $this->createTable($tableName, $columns); @@ -374,16 +395,17 @@ class PgsqlSchema extends Schema $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)) { - $tomod[] = $newCol->name; + // 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; @@ -407,11 +429,12 @@ class PgsqlSchema extends Schema foreach ($tomod as $columnName) { $cd = $this->_byName($columns, $columnName); - $phrase[] = 'MODIFY COLUMN ' . $this->_columnSql($cd); + /* 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)) { @@ -473,15 +496,25 @@ class PgsqlSchema extends Schema * * @return string correct SQL for that column */ - private function _columnSql($cd) { $sql = "{$cd->name} "; + $type = $this->_columnTypeTranslation($cd->type); + + //handle those mysql enum fields that postgres doesn't support + if (preg_match('!^enum!', $type)) { + $allowed_values = preg_replace('!^enum!', '', $type); + $sql .= " text check ({$cd->name} in $allowed_values)"; + return $sql; + } + if (!empty($cd->auto_increment)) { + $type = "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. + } if (!empty($cd->size)) { - $sql .= "{$cd->type}({$cd->size}) "; + $sql .= "{$type}({$cd->size}) "; } else { - $sql .= "{$cd->type} "; + $sql .= "{$type} "; } if (!empty($cd->default)) { @@ -489,14 +522,10 @@ class PgsqlSchema extends Schema } else { $sql .= ($cd->nullable) ? "null " : "not null "; } - - if (!empty($cd->auto_increment)) { - $sql .= " auto_increment "; - } - if (!empty($cd->extra)) { - $sql .= "{$cd->extra} "; - } +// if (!empty($cd->extra)) { +// $sql .= "{$cd->extra} "; +// } return $sql; } |