From 81087e45c5b797028e90181459e4c673cd7be278 Mon Sep 17 00:00:00 2001 From: Evan Prodromou Date: Sun, 31 Jan 2010 15:25:59 -0500 Subject: move schema.type.php to typeschema.php like other files --- lib/mysqlschema.php | 537 +++++++++++++++++++++++++++++++++++++++++++++++++++ lib/pgsqlschema.php | 503 +++++++++++++++++++++++++++++++++++++++++++++++ lib/schema.mysql.php | 537 --------------------------------------------------- lib/schema.pgsql.php | 503 ----------------------------------------------- lib/schema.php | 8 +- 5 files changed, 1043 insertions(+), 1045 deletions(-) create mode 100644 lib/mysqlschema.php create mode 100644 lib/pgsqlschema.php delete mode 100644 lib/schema.mysql.php delete mode 100644 lib/schema.pgsql.php diff --git a/lib/mysqlschema.php b/lib/mysqlschema.php new file mode 100644 index 000000000..1f7c3d092 --- /dev/null +++ b/lib/mysqlschema.php @@ -0,0 +1,537 @@ +. + * + * @category Database + * @package StatusNet + * @author Evan Prodromou + * @copyright 2009 StatusNet, Inc. + * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 + * @link http://status.net/ + */ + +if (!defined('STATUSNET')) { + exit(1); +} + +/** + * Class representing the database schema + * + * A class representing the database schema. Can be used to + * manipulate the schema -- especially for plugins and upgrade + * utilities. + * + * @category Database + * @package StatusNet + * @author Evan Prodromou + * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 + * @link http://status.net/ + */ + +class MysqlSchema extends Schema +{ + static $_single = null; + protected $conn = null; + + /** + * Constructor. Only run once for singleton object. + */ + + protected function __construct() + { + // XXX: there should be an easier way to do this. + $user = new User(); + + $this->conn = $user->getDatabaseConnection(); + + $user->free(); + + unset($user); + } + + /** + * Main public entry point. Use this to get + * the singleton object. + * + * @return Schema the (single) Schema object + */ + + static function get() + { + if (empty(self::$_single)) { + self::$_single = new Schema(); + } + return self::$_single; + } + + /** + * Returns a TableDef object for the table + * in the schema with the given name. + * + * Throws an exception if the table is not found. + * + * @param string $name Name of the table to get + * + * @return TableDef tabledef for that table. + */ + + public function getTableDef($name) + { + $res = $this->conn->query('DESCRIBE ' . $name); + + if (PEAR::isError($res)) { + throw new Exception($res->getMessage()); + } + + $td = new TableDef(); + + $td->name = $name; + $td->columns = array(); + + $row = array(); + + while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { + + $cd = new ColumnDef(); + + $cd->name = $row['Field']; + + $packed = $row['Type']; + + if (preg_match('/^(\w+)\((\d+)\)$/', $packed, $match)) { + $cd->type = $match[1]; + $cd->size = $match[2]; + } else { + $cd->type = $packed; + } + + $cd->nullable = ($row['Null'] == 'YES') ? true : false; + $cd->key = $row['Key']; + $cd->default = $row['Default']; + $cd->extra = $row['Extra']; + + $td->columns[] = $cd; + } + + return $td; + } + + /** + * 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 + * @param array $columns Array of ColumnDef objects + * for new table. + * + * @return boolean success flag + */ + + public function createTable($name, $columns) + { + $uniques = array(); + $primary = array(); + $indices = array(); + + $sql = "CREATE TABLE $name (\n"; + + for ($i = 0; $i < count($columns); $i++) { + + $cd =& $columns[$i]; + + if ($i > 0) { + $sql .= ",\n"; + } + + $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; + } + } + + if (count($primary) > 0) { // it really should be... + $sql .= ",\nconstraint primary key (" . implode(',', $primary) . ")"; + } + + foreach ($uniques as $u) { + $sql .= ",\nunique index {$name}_{$u}_idx ($u)"; + } + + foreach ($indices as $i) { + $sql .= ",\nindex {$name}_{$i}_idx ($i)"; + } + + $sql .= "); "; + + $res = $this->conn->query($sql); + + if (PEAR::isError($res)) { + throw new Exception($res->getMessage()); + } + + return true; + } + + /** + * 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. + * + * 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)) { + $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); + + $phrase[] = 'MODIFY 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; + } + + /** + * 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. + */ + + private function _byName($cds, $name) + { + foreach ($cds as $cd) { + if ($cd->name == $name) { + return $cd; + } + } + + return null; + } + + /** + * 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) + { + $sql = "{$cd->name} "; + + if (!empty($cd->size)) { + $sql .= "{$cd->type}({$cd->size}) "; + } else { + $sql .= "{$cd->type} "; + } + + 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->extra)) { + $sql .= "{$cd->extra} "; + } + + return $sql; + } +} diff --git a/lib/pgsqlschema.php b/lib/pgsqlschema.php new file mode 100644 index 000000000..91bc09667 --- /dev/null +++ b/lib/pgsqlschema.php @@ -0,0 +1,503 @@ +. + * + * @category Database + * @package StatusNet + * @author Evan Prodromou + * @copyright 2009 StatusNet, Inc. + * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 + * @link http://status.net/ + */ + +if (!defined('STATUSNET')) { + exit(1); +} + +/** + * Class representing the database schema + * + * A class representing the database schema. Can be used to + * manipulate the schema -- especially for plugins and upgrade + * utilities. + * + * @category Database + * @package StatusNet + * @author Evan Prodromou + * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 + * @link http://status.net/ + */ + +class PgsqlSchema extends Schema +{ + + /** + * Returns a TableDef object for the table + * in the schema with the given name. + * + * Throws an exception if the table is not found. + * + * @param string $name Name of the table to get + * + * @return TableDef tabledef for that table. + */ + + 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'"); + + if (PEAR::isError($res)) { + throw new Exception($res->getMessage()); + } + + $td = new TableDef(); + + $td->name = $name; + $td->columns = array(); + + $row = array(); + + while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { +// var_dump($row); + $cd = new ColumnDef(); + + $cd->name = $row['field']; + + $packed = $row['type']; + + if (preg_match('/^(\w+)\((\d+)\)$/', $packed, $match)) { + $cd->type = $match[1]; + $cd->size = $match[2]; + } else { + $cd->type = $packed; + } + + $cd->nullable = ($row['null'] == 'YES') ? true : false; + $cd->key = $row['Key']; + $cd->default = $row['default']; + $cd->extra = $row['Extra']; + + $td->columns[] = $cd; + } + return $td; + } + + /** + * 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 + * @param array $columns Array of ColumnDef objects + * for new table. + * + * @return boolean success flag + */ + + public function createTable($name, $columns) + { + $uniques = array(); + $primary = array(); + $indices = array(); + + $sql = "CREATE TABLE $name (\n"; + + for ($i = 0; $i < count($columns); $i++) { + + $cd =& $columns[$i]; + + if ($i > 0) { + $sql .= ",\n"; + } + + $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; + } + } + + if (count($primary) > 0) { // it really should be... + $sql .= ",\nconstraint primary key (" . implode(',', $primary) . ")"; + } + + foreach ($uniques as $u) { + $sql .= ",\nunique index {$name}_{$u}_idx ($u)"; + } + + foreach ($indices as $i) { + $sql .= ",\nindex {$name}_{$i}_idx ($i)"; + } + + $sql .= "); "; + + $res = $this->conn->query($sql); + + if (PEAR::isError($res)) { + throw new Exception($res->getMessage()); + } + + return true; + } + + /** + * 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. + * + * 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)) { + $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); + + $phrase[] = 'MODIFY 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; + } + + /** + * 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. + */ + + private function _byName($cds, $name) + { + foreach ($cds as $cd) { + if ($cd->name == $name) { + return $cd; + } + } + + return null; + } + + /** + * 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) + { + $sql = "{$cd->name} "; + + if (!empty($cd->size)) { + $sql .= "{$cd->type}({$cd->size}) "; + } else { + $sql .= "{$cd->type} "; + } + + 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->extra)) { + $sql .= "{$cd->extra} "; + } + + return $sql; + } +} diff --git a/lib/schema.mysql.php b/lib/schema.mysql.php deleted file mode 100644 index 1f7c3d092..000000000 --- a/lib/schema.mysql.php +++ /dev/null @@ -1,537 +0,0 @@ -. - * - * @category Database - * @package StatusNet - * @author Evan Prodromou - * @copyright 2009 StatusNet, Inc. - * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 - * @link http://status.net/ - */ - -if (!defined('STATUSNET')) { - exit(1); -} - -/** - * Class representing the database schema - * - * A class representing the database schema. Can be used to - * manipulate the schema -- especially for plugins and upgrade - * utilities. - * - * @category Database - * @package StatusNet - * @author Evan Prodromou - * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 - * @link http://status.net/ - */ - -class MysqlSchema extends Schema -{ - static $_single = null; - protected $conn = null; - - /** - * Constructor. Only run once for singleton object. - */ - - protected function __construct() - { - // XXX: there should be an easier way to do this. - $user = new User(); - - $this->conn = $user->getDatabaseConnection(); - - $user->free(); - - unset($user); - } - - /** - * Main public entry point. Use this to get - * the singleton object. - * - * @return Schema the (single) Schema object - */ - - static function get() - { - if (empty(self::$_single)) { - self::$_single = new Schema(); - } - return self::$_single; - } - - /** - * Returns a TableDef object for the table - * in the schema with the given name. - * - * Throws an exception if the table is not found. - * - * @param string $name Name of the table to get - * - * @return TableDef tabledef for that table. - */ - - public function getTableDef($name) - { - $res = $this->conn->query('DESCRIBE ' . $name); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - $td = new TableDef(); - - $td->name = $name; - $td->columns = array(); - - $row = array(); - - while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { - - $cd = new ColumnDef(); - - $cd->name = $row['Field']; - - $packed = $row['Type']; - - if (preg_match('/^(\w+)\((\d+)\)$/', $packed, $match)) { - $cd->type = $match[1]; - $cd->size = $match[2]; - } else { - $cd->type = $packed; - } - - $cd->nullable = ($row['Null'] == 'YES') ? true : false; - $cd->key = $row['Key']; - $cd->default = $row['Default']; - $cd->extra = $row['Extra']; - - $td->columns[] = $cd; - } - - return $td; - } - - /** - * 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 - * @param array $columns Array of ColumnDef objects - * for new table. - * - * @return boolean success flag - */ - - public function createTable($name, $columns) - { - $uniques = array(); - $primary = array(); - $indices = array(); - - $sql = "CREATE TABLE $name (\n"; - - for ($i = 0; $i < count($columns); $i++) { - - $cd =& $columns[$i]; - - if ($i > 0) { - $sql .= ",\n"; - } - - $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; - } - } - - if (count($primary) > 0) { // it really should be... - $sql .= ",\nconstraint primary key (" . implode(',', $primary) . ")"; - } - - foreach ($uniques as $u) { - $sql .= ",\nunique index {$name}_{$u}_idx ($u)"; - } - - foreach ($indices as $i) { - $sql .= ",\nindex {$name}_{$i}_idx ($i)"; - } - - $sql .= "); "; - - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; - } - - /** - * 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. - * - * 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)) { - $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); - - $phrase[] = 'MODIFY 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; - } - - /** - * 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. - */ - - private function _byName($cds, $name) - { - foreach ($cds as $cd) { - if ($cd->name == $name) { - return $cd; - } - } - - return null; - } - - /** - * 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) - { - $sql = "{$cd->name} "; - - if (!empty($cd->size)) { - $sql .= "{$cd->type}({$cd->size}) "; - } else { - $sql .= "{$cd->type} "; - } - - 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->extra)) { - $sql .= "{$cd->extra} "; - } - - return $sql; - } -} diff --git a/lib/schema.pgsql.php b/lib/schema.pgsql.php deleted file mode 100644 index 91bc09667..000000000 --- a/lib/schema.pgsql.php +++ /dev/null @@ -1,503 +0,0 @@ -. - * - * @category Database - * @package StatusNet - * @author Evan Prodromou - * @copyright 2009 StatusNet, Inc. - * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 - * @link http://status.net/ - */ - -if (!defined('STATUSNET')) { - exit(1); -} - -/** - * Class representing the database schema - * - * A class representing the database schema. Can be used to - * manipulate the schema -- especially for plugins and upgrade - * utilities. - * - * @category Database - * @package StatusNet - * @author Evan Prodromou - * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 - * @link http://status.net/ - */ - -class PgsqlSchema extends Schema -{ - - /** - * Returns a TableDef object for the table - * in the schema with the given name. - * - * Throws an exception if the table is not found. - * - * @param string $name Name of the table to get - * - * @return TableDef tabledef for that table. - */ - - 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'"); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - $td = new TableDef(); - - $td->name = $name; - $td->columns = array(); - - $row = array(); - - while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { -// var_dump($row); - $cd = new ColumnDef(); - - $cd->name = $row['field']; - - $packed = $row['type']; - - if (preg_match('/^(\w+)\((\d+)\)$/', $packed, $match)) { - $cd->type = $match[1]; - $cd->size = $match[2]; - } else { - $cd->type = $packed; - } - - $cd->nullable = ($row['null'] == 'YES') ? true : false; - $cd->key = $row['Key']; - $cd->default = $row['default']; - $cd->extra = $row['Extra']; - - $td->columns[] = $cd; - } - return $td; - } - - /** - * 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 - * @param array $columns Array of ColumnDef objects - * for new table. - * - * @return boolean success flag - */ - - public function createTable($name, $columns) - { - $uniques = array(); - $primary = array(); - $indices = array(); - - $sql = "CREATE TABLE $name (\n"; - - for ($i = 0; $i < count($columns); $i++) { - - $cd =& $columns[$i]; - - if ($i > 0) { - $sql .= ",\n"; - } - - $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; - } - } - - if (count($primary) > 0) { // it really should be... - $sql .= ",\nconstraint primary key (" . implode(',', $primary) . ")"; - } - - foreach ($uniques as $u) { - $sql .= ",\nunique index {$name}_{$u}_idx ($u)"; - } - - foreach ($indices as $i) { - $sql .= ",\nindex {$name}_{$i}_idx ($i)"; - } - - $sql .= "); "; - - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; - } - - /** - * 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. - * - * 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)) { - $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); - - $phrase[] = 'MODIFY 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; - } - - /** - * 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. - */ - - private function _byName($cds, $name) - { - foreach ($cds as $cd) { - if ($cd->name == $name) { - return $cd; - } - } - - return null; - } - - /** - * 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) - { - $sql = "{$cd->name} "; - - if (!empty($cd->size)) { - $sql .= "{$cd->type}({$cd->size}) "; - } else { - $sql .= "{$cd->type} "; - } - - 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->extra)) { - $sql .= "{$cd->extra} "; - } - - return $sql; - } -} diff --git a/lib/schema.php b/lib/schema.php index 27a4deda1..137b814e0 100644 --- a/lib/schema.php +++ b/lib/schema.php @@ -77,14 +77,12 @@ class Schema { $type = common_config('db', 'type'); if (empty(self::$_single)) { - include "lib/schema.{$type}.php"; - $class = $type.='Schema'; - self::$_single = new $class(); + $schemaClass = ucfirst($type).'Schema'; + self::$_single = new $schemaClass(); } return self::$_single; } - /** * Gets a ColumnDef object for a single column. * @@ -475,7 +473,7 @@ class Schema } else { $sql .= ($cd->nullable) ? "null " : "not null "; } - + if (!empty($cd->auto_increment)) { $sql .= " auto_increment "; } -- cgit v1.2.3-54-g00ecf