diff options
author | Brion Vibber <brion@pobox.com> | 2010-08-16 16:31:18 -0700 |
---|---|---|
committer | Brion Vibber <brion@pobox.com> | 2010-08-16 16:31:18 -0700 |
commit | 033a7570133d4183c2e162859e9b85fe7df3e40b (patch) | |
tree | 5b073d537b75a35f11dbb1bf554b805f8d683878 | |
parent | 1a7d830fff04a9c16b259caf0633cb18fb74fe01 (diff) |
More schema work in progress... removing duped code from schema child classes, rebuilding things a bit more (incomplete; non-working state)
-rw-r--r-- | lib/mysqlschema.php | 283 | ||||
-rw-r--r-- | lib/pgsqlschema.php | 259 | ||||
-rw-r--r-- | lib/schema.php | 64 |
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; + } } /** |