More schema work in progress... removing duped code from schema child classes, rebuilding things a bit more (incomplete; non-working state)

3 files changed, 135 insertions, 471 deletions

diff --git a/lib/mysqlschema.php b/lib/mysqlschema.php
index f9552c1dc..b84883c03 100644
--- a/lib/mysqlschema.php
+++ b/lib/mysqlschema.php
@@ -168,31 +168,6 @@ class MysqlSchema extends Schema
     }
 
     /**
-     * Gets a ColumnDef object for a single column.
-     *
-     * Throws an exception if the table is not found.
-     *
-     * @param string $table  name of the table
-     * @param string $column name of the column
-     *
-     * @return ColumnDef definition of the column or null
-     *                   if not found.
-     */
-
-    public function getColumnDef($table, $column)
-    {
-        $td = $this->getTableDef($table);
-
-        foreach ($td->columns as $cd) {
-            if ($cd->name == $column) {
-                return $cd;
-            }
-        }
-
-        return null;
-    }
-
-    /**
      * Creates a table with the given names and columns.
      *
      * @param string $name    Name of the table
@@ -290,156 +265,6 @@ class MysqlSchema extends Schema
     }
 
     /**
-     * Drops a table from the schema
-     *
-     * Throws an exception if the table is not found.
-     *
-     * @param string $name Name of the table to drop
-     *
-     * @return boolean success flag
-     */
-
-    public function dropTable($name)
-    {
-        $res = $this->conn->query("DROP TABLE $name");
-
-        if (PEAR::isError($res)) {
-            throw new Exception($res->getMessage());
-        }
-
-        return true;
-    }
-
-    /**
-     * Adds an index to a table.
-     *
-     * If no name is provided, a name will be made up based
-     * on the table name and column names.
-     *
-     * Throws an exception on database error, esp. if the table
-     * does not exist.
-     *
-     * @param string $table       Name of the table
-     * @param array  $columnNames Name of columns to index
-     * @param string $name        (Optional) name of the index
-     *
-     * @return boolean success flag
-     */
-
-    public function createIndex($table, $columnNames, $name=null)
-    {
-        if (!is_array($columnNames)) {
-            $columnNames = array($columnNames);
-        }
-
-        if (empty($name)) {
-            $name = "{$table}_".implode("_", $columnNames)."_idx";
-        }
-
-        $res = $this->conn->query("ALTER TABLE $table ".
-                                   "ADD INDEX $name (".
-                                   implode(",", $columnNames).")");
-
-        if (PEAR::isError($res)) {
-            throw new Exception($res->getMessage());
-        }
-
-        return true;
-    }
-
-    /**
-     * Drops a named index from a table.
-     *
-     * @param string $table name of the table the index is on.
-     * @param string $name  name of the index
-     *
-     * @return boolean success flag
-     */
-
-    public function dropIndex($table, $name)
-    {
-        $res = $this->conn->query("ALTER TABLE $table DROP INDEX $name");
-
-        if (PEAR::isError($res)) {
-            throw new Exception($res->getMessage());
-        }
-
-        return true;
-    }
-
-    /**
-     * Adds a column to a table
-     *
-     * @param string    $table     name of the table
-     * @param ColumnDef $columndef Definition of the new
-     *                             column.
-     *
-     * @return boolean success flag
-     */
-
-    public function addColumn($table, $columndef)
-    {
-        $sql = "ALTER TABLE $table ADD COLUMN " . $this->_columnSql($columndef);
-
-        $res = $this->conn->query($sql);
-
-        if (PEAR::isError($res)) {
-            throw new Exception($res->getMessage());
-        }
-
-        return true;
-    }
-
-    /**
-     * Modifies a column in the schema.
-     *
-     * The name must match an existing column and table.
-     *
-     * @param string    $table     name of the table
-     * @param ColumnDef $columndef new definition of the column.
-     *
-     * @return boolean success flag
-     */
-
-    public function modifyColumn($table, $columndef)
-    {
-        $sql = "ALTER TABLE $table MODIFY COLUMN " .
-          $this->_columnSql($columndef);
-
-        $res = $this->conn->query($sql);
-
-        if (PEAR::isError($res)) {
-            throw new Exception($res->getMessage());
-        }
-
-        return true;
-    }
-
-    /**
-     * Drops a column from a table
-     *
-     * The name must match an existing column.
-     *
-     * @param string $table      name of the table
-     * @param string $columnName name of the column to drop
-     *
-     * @return boolean success flag
-     */
-
-    public function dropColumn($table, $columnName)
-    {
-        $sql = "ALTER TABLE $table DROP COLUMN $columnName";
-
-        $res = $this->conn->query($sql);
-
-        if (PEAR::isError($res)) {
-            throw new Exception($res->getMessage());
-        }
-
-        return true;
-    }
-
-    /**
      * Ensures that a table exists with the given
      * name and the given column definitions.
      *
@@ -589,44 +414,12 @@ class MysqlSchema extends Schema
     }
 
     /**
-     * Returns the array of names from an array of
-     * ColumnDef objects.
-     *
-     * @param array $cds array of ColumnDef objects
-     *
-     * @return array strings for name values
-     */
-
-    private function _names($cds)
-    {
-        $names = array();
-
-        foreach ($cds as $cd) {
-            $names[] = $cd->name;
-        }
-
-        return $names;
-    }
-
-    /**
-     * Get a ColumnDef from an array matching
-     * name.
-     *
-     * @param array  $cds  Array of ColumnDef objects
-     * @param string $name Name of the column
-     *
-     * @return ColumnDef matching item or null if no match.
+     * Is this column a string type?
      */
-
-    private function _byName($cds, $name)
+    private function _isString(array $cd)
     {
-        foreach ($cds as $cd) {
-            if ($cd->name == $name) {
-                return $cd;
-            }
-        }
-
-        return null;
+        $strings = array('char', 'varchar', 'text');
+        return in_array(strtolower($cd['type']), $strings);
     }
 
     /**
@@ -641,43 +434,61 @@ class MysqlSchema extends Schema
      * @return string correct SQL for that column
      */
 
-    private function _columnSql($cd)
+    function columnSql(array $cd)
     {
-        $sql = "{$cd->name} ";
+        $line = array();
+        $line[] = parent::_columnSql($cd);
 
-        if (!empty($cd->size)) {
-            $sql .= "{$cd->type}({$cd->size}) ";
-        } else {
-            $sql .= "{$cd->type} ";
+        if ($cd['type'] == 'serial') {
+            $line[] = 'auto_increment';
         }
 
-        if ($this->_isString($cd)) {
-            $sql .= " CHARACTER SET utf8 ";
+        if (!empty($cd['extra'])) {
+            $line[] = $cd['extra']; // hisss boooo
         }
 
-        if (!empty($cd->default)) {
-            $sql .= "default {$cd->default} ";
-        } else {
-            $sql .= ($cd->nullable) ? "null " : "not null ";
-        }
-        
-        if (!empty($cd->auto_increment)) {
-            $sql .= " auto_increment ";
+        if (!empty($cd['description'])) {
+            $line[] = 'comment';
+            $line[] = $this->quote($cd['description']);
         }
 
-        if (!empty($cd->extra)) {
-            $sql .= "{$cd->extra} ";
+        return implode(' ', $line);
+    }
+
+    function mapType($column)
+    {
+        $map = array('serial' => 'int',
+                     'integer' => 'int',
+                     'numeric' => 'decimal');
+        
+        $type = $column['type'];
+        if (isset($map[$type])) {
+            $type = $map[$type];
+        }
+
+        if (!empty($column['size'])) {
+            $size = $column['size'];
+            if ($type == 'int' &&
+                       in_array($size, array('tiny', 'small', 'medium', 'big'))) {
+                $type = $size . $type;
+            } else if (in_array($type, array('blob', 'text')) &&
+                       in_array($size, array('tiny', 'medium', 'long'))) {
+                $type = $size . $type;
+            }
         }
 
-        return $sql;
+        return $type;
     }
 
-    /**
-     * Is this column a string type?
-     */
-    private function _isString(ColumnDef $cd)
+    function typeAndSize($column)
     {
-        $strings = array('char', 'varchar', 'text');
-        return in_array(strtolower($cd->type), $strings);
+        if ($column['type'] == 'enum') {
+            $vals = array_map(array($this, 'quote'), $column['enum']);
+            return 'enum(' . implode(',', $vals) . ')';
+        } else if ($this->_isString($column)) {
+            return parent::typeAndSize($column) . ' CHARSET utf8';
+        } else {
+            return parent::typeAndSize($column);
+        }
     }
 }
diff --git a/lib/pgsqlschema.php b/lib/pgsqlschema.php
index 272f7eff6..2d0f60983 100644
--- a/lib/pgsqlschema.php
+++ b/lib/pgsqlschema.php
@@ -104,31 +104,6 @@ class PgsqlSchema extends Schema
     }
 
     /**
-     * Gets a ColumnDef object for a single column.
-     *
-     * Throws an exception if the table is not found.
-     *
-     * @param string $table  name of the table
-     * @param string $column name of the column
-     *
-     * @return ColumnDef definition of the column or null
-     *                   if not found.
-     */
-
-    public function getColumnDef($table, $column)
-    {
-        $td = $this->getTableDef($table);
-
-        foreach ($td->columns as $cd) {
-            if ($cd->name == $column) {
-                return $cd;
-            }
-        }
-
-        return null;
-    }
-
-    /**
      * Creates a table with the given names and columns.
      *
      * @param string $name    Name of the table
@@ -193,27 +168,6 @@ class PgsqlSchema extends Schema
     }
 
     /**
-     * Drops a table from the schema
-     *
-     * Throws an exception if the table is not found.
-     *
-     * @param string $name Name of the table to drop
-     *
-     * @return boolean success flag
-     */
-
-    public function dropTable($name)
-    {
-        $res = $this->conn->query("DROP TABLE $name");
-
-        if (PEAR::isError($res)) {
-            throw new Exception($res->getMessage());
-        }
-
-        return true;
-    }
-
-    /**
      * Translate the (mostly) mysql-ish column types into somethings more standard
      * @param string column type
      *
@@ -230,86 +184,6 @@ class PgsqlSchema extends Schema
     }
 
     /**
-     * Adds an index to a table.
-     *
-     * If no name is provided, a name will be made up based
-     * on the table name and column names.
-     *
-     * Throws an exception on database error, esp. if the table
-     * does not exist.
-     *
-     * @param string $table       Name of the table
-     * @param array  $columnNames Name of columns to index
-     * @param string $name        (Optional) name of the index
-     *
-     * @return boolean success flag
-     */
-
-    public function createIndex($table, $columnNames, $name=null)
-    {
-        if (!is_array($columnNames)) {
-            $columnNames = array($columnNames);
-        }
-
-        if (empty($name)) {
-            $name = "$table_".implode("_", $columnNames)."_idx";
-        }
-
-        $res = $this->conn->query("ALTER TABLE $table ".
-                                   "ADD INDEX $name (".
-                                   implode(",", $columnNames).")");
-
-        if (PEAR::isError($res)) {
-            throw new Exception($res->getMessage());
-        }
-
-        return true;
-    }
-
-    /**
-     * Drops a named index from a table.
-     *
-     * @param string $table name of the table the index is on.
-     * @param string $name  name of the index
-     *
-     * @return boolean success flag
-     */
-
-    public function dropIndex($table, $name)
-    {
-        $res = $this->conn->query("ALTER TABLE $table DROP INDEX $name");
-
-        if (PEAR::isError($res)) {
-            throw new Exception($res->getMessage());
-        }
-
-        return true;
-    }
-
-    /**
-     * Adds a column to a table
-     *
-     * @param string    $table     name of the table
-     * @param ColumnDef $columndef Definition of the new
-     *                             column.
-     *
-     * @return boolean success flag
-     */
-
-    public function addColumn($table, $columndef)
-    {
-        $sql = "ALTER TABLE $table ADD COLUMN " . $this->_columnSql($columndef);
-
-        $res = $this->conn->query($sql);
-
-        if (PEAR::isError($res)) {
-            throw new Exception($res->getMessage());
-        }
-
-        return true;
-    }
-
-    /**
      * Modifies a column in the schema.
      *
      * The name must match an existing column and table.
@@ -334,29 +208,6 @@ class PgsqlSchema extends Schema
         return true;
     }
 
-    /**
-     * Drops a column from a table
-     *
-     * The name must match an existing column.
-     *
-     * @param string $table      name of the table
-     * @param string $columnName name of the column to drop
-     *
-     * @return boolean success flag
-     */
-
-    public function dropColumn($table, $columnName)
-    {
-        $sql = "ALTER TABLE $table DROP COLUMN $columnName";
-
-        $res = $this->conn->query($sql);
-
-        if (PEAR::isError($res)) {
-            throw new Exception($res->getMessage());
-        }
-
-        return true;
-    }
 
     /**
      * Ensures that a table exists with the given
@@ -445,88 +296,68 @@ class PgsqlSchema extends Schema
     }
 
     /**
-     * Returns the array of names from an array of
-     * ColumnDef objects.
-     *
-     * @param array $cds array of ColumnDef objects
+     * Return the proper SQL for creating or
+     * altering a column.
      *
-     * @return array strings for name values
-     */
-
-    private function _names($cds)
-    {
-        $names = array();
-
-        foreach ($cds as $cd) {
-            $names[] = $cd->name;
-        }
-
-        return $names;
-    }
-
-    /**
-     * Get a ColumnDef from an array matching
-     * name.
+     * Appropriate for use in CREATE TABLE or
+     * ALTER TABLE statements.
      *
-     * @param array  $cds  Array of ColumnDef objects
-     * @param string $name Name of the column
+     * @param string $tableName
+     * @param array $tableDef
+     * @param string $columnName
+     * @param array $cd column to create
      *
-     * @return ColumnDef matching item or null if no match.
+     * @return string correct SQL for that column
      */
 
-    private function _byName($cds, $name)
+    function columnSql($name, array $cd)
     {
-        foreach ($cds as $cd) {
-            if ($cd->name == $name) {
-                return $cd;
+        $line = array();
+        $line[] = parent::_columnSql($cd);
+
+        if ($table['foreign keys'][$name]) {
+            foreach ($table['foreign keys'][$name] as $foreignTable => $foreignColumn) {
+                $line[] = 'references';
+                $line[] = $this->quoteId($foreignTable);
+                $line[] = '(' . $this->quoteId($foreignColumn) . ')';
             }
         }
 
-        return null;
+        return implode(' ', $line);
     }
 
-    /**
-     * Return the proper SQL for creating or
-     * altering a column.
-     *
-     * Appropriate for use in CREATE TABLE or
-     * ALTER TABLE statements.
-     *
-     * @param ColumnDef $cd column to create
-     *
-     * @return string correct SQL for that column
-     */
-    private function _columnSql($cd)
+    function mapType($column)
     {
-        $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.
+        $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.
+                     'numeric' => 'decimal',
+                     'datetime' => 'timestamp',
+                     'blob' => 'bytea');
+
+        $type = $column['type'];
+        if (isset($map[$type])) {
+            $type = $map[$type];
         }
 
-        if (!empty($cd->size)) {
-            $sql .= "{$type}({$cd->size}) ";
-        } else {
-            $sql .= "{$type} ";
+        if (!empty($column['size'])) {
+            $size = $column['size'];
+            if ($type == 'integer' &&
+                       in_array($size, array('small', 'big'))) {
+                $type = $size . 'int';
+            }
         }
 
-        if (!empty($cd->default)) {
-            $sql .= "default {$cd->default} ";
+        return $type;
+    }
+
+    // @fixme need name... :P
+    function typeAndSize($column)
+    {
+        if ($column['type'] == 'enum') {
+            $vals = array_map(array($this, 'quote'), $column['enum']);
+            return "text check ($name in " . implode(',', $vals) . ')';
         } else {
-            $sql .= ($cd->nullable) ? "null " : "not null ";
+            return parent::typeAndSize($column);
         }
-
-//         if (!empty($cd->extra)) {
-//             $sql .= "{$cd->extra} ";
-//         }
-
-        return $sql;
     }
+
 }
diff --git a/lib/schema.php b/lib/schema.php
index 9ebef8aa2..d20bced4a 100644
--- a/lib/schema.php
+++ b/lib/schema.php
@@ -224,7 +224,7 @@ class Schema
         }
 
         if (empty($name)) {
-            $name = "$table_".implode("_", $columnNames)."_idx";
+            $name = "{$table}_".implode("_", $columnNames)."_idx";
         }
 
         $res = $this->conn->query("ALTER TABLE $table ".
@@ -422,7 +422,7 @@ class Schema
      * @return array strings for name values
      */
 
-    private function _names($cds)
+    protected function _names($cds)
     {
         $names = array();
 
@@ -443,7 +443,7 @@ class Schema
      * @return ColumnDef matching item or null if no match.
      */
 
-    private function _byName($cds, $name)
+    protected function _byName($cds, $name)
     {
         foreach ($cds as $cd) {
             if ($cd->name == $name) {
@@ -466,31 +466,53 @@ class Schema
      * @return string correct SQL for that column
      */
 
-    private function _columnSql($cd)
+    function columnSql(array $cd)
     {
-        $sql = "{$cd->name} ";
-
-        if (!empty($cd->size)) {
-            $sql .= "{$cd->type}({$cd->size}) ";
-        } else {
-            $sql .= "{$cd->type} ";
+        $line = array();
+        $line[] = $this->typeAndSize();
+
+        if (isset($cd['default'])) {
+            $line[] = 'default';
+            $line[] = $this->quoted($cd['default']);
+        } else if (!empty($cd['not null'])) {
+            // Can't have both not null AND default!
+            $line[] = 'not null';
         }
 
-        if (!empty($cd->default)) {
-            $sql .= "default {$cd->default} ";
-        } else {
-            $sql .= ($cd->nullable) ? "null " : "not null ";
-        }
+        return implode(' ', $line);
+    }
 
-        if (!empty($cd->auto_increment)) {
-            $sql .= " auto_increment ";
-        }
+    /**
+     *
+     * @param string $column canonical type name in defs
+     * @return string native DB type name
+     */
+    function mapType($column)
+    {
+        return $column;
+    }
 
-        if (!empty($cd->extra)) {
-            $sql .= "{$cd->extra} ";
+    function typeAndSize($column)
+    {
+        $type = $this->mapType($column);
+        $lengths = array();
+
+        if ($column['type'] == 'numeric') {
+            if (isset($column['precision'])) {
+                $lengths[] = $column['precision'];
+                if (isset($column['scale'])) {
+                    $lengths[] = $column['scale'];
+                }
+            }
+        } else if (isset($column['length'])) {
+            $lengths[] = $column['length'];
         }
 
-        return $sql;
+        if ($lengths) {
+            return $type . '(' . implode(',', $lengths) . ')';
+        } else {
+            return $type;
+        }
     }
 
     /**