diff options
Diffstat (limited to 'lib')
| -rw-r--r-- | lib/common.php | 128 | ||||
| -rw-r--r-- | lib/default.php | 3 | ||||
| -rw-r--r-- | lib/framework.php | 143 | ||||
| -rw-r--r-- | lib/installer.php | 227 | ||||
| -rw-r--r-- | lib/mysqlschema.php | 723 | ||||
| -rw-r--r-- | lib/pgsqlschema.php | 635 | ||||
| -rw-r--r-- | lib/schema.php | 705 | ||||
| -rw-r--r-- | lib/schemaupdater.php | 126 | ||||
| -rw-r--r-- | lib/statusnet.php | 2 |
9 files changed, 1502 insertions, 1190 deletions
diff --git a/lib/common.php b/lib/common.php index 34d77c3f6..ca02a3e7f 100644 --- a/lib/common.php +++ b/lib/common.php @@ -1,7 +1,7 @@ <?php /* * StatusNet - the distributed open-source microblogging tool - * Copyright (C) 2008, 2009, StatusNet, Inc. + * Copyright (C) 2008-2010, StatusNet, Inc. * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License as published by @@ -19,131 +19,13 @@ if (!defined('STATUSNET') && !defined('LACONICA')) { exit(1); } +// @fixme shouldn't this be in index.php instead? //exit with 200 response, if this is checking fancy from the installer if (isset($_REQUEST['p']) && $_REQUEST['p'] == 'check-fancy') { exit; } -define('STATUSNET_VERSION', '0.9.6'); -define('LACONICA_VERSION', STATUSNET_VERSION); // compatibility - -define('STATUSNET_CODENAME', 'Man on the Moon'); - -define('AVATAR_PROFILE_SIZE', 96); -define('AVATAR_STREAM_SIZE', 48); -define('AVATAR_MINI_SIZE', 24); - -define('NOTICES_PER_PAGE', 20); -define('PROFILES_PER_PAGE', 20); - -define('FOREIGN_NOTICE_SEND', 1); -define('FOREIGN_NOTICE_RECV', 2); -define('FOREIGN_NOTICE_SEND_REPLY', 4); - -define('FOREIGN_FRIEND_SEND', 1); -define('FOREIGN_FRIEND_RECV', 2); - -define('NOTICE_INBOX_SOURCE_SUB', 1); -define('NOTICE_INBOX_SOURCE_GROUP', 2); -define('NOTICE_INBOX_SOURCE_REPLY', 3); -define('NOTICE_INBOX_SOURCE_FORWARD', 4); -define('NOTICE_INBOX_SOURCE_GATEWAY', -1); - -# append our extlib dir as the last-resort place to find libs - -set_include_path(get_include_path() . PATH_SEPARATOR . INSTALLDIR . '/extlib/'); - -// To protect against upstream libraries which haven't updated -// for PHP 5.3 where dl() function may not be present... -if (!function_exists('dl')) { - // function_exists() returns false for things in disable_functions, - // but they still exist and we'll die if we try to redefine them. - // - // Fortunately trying to call the disabled one will only trigger - // a warning, not a fatal, so it's safe to leave it for our case. - // Callers will be suppressing warnings anyway. - $disabled = array_filter(array_map('trim', explode(',', ini_get('disable_functions')))); - if (!in_array('dl', $disabled)) { - function dl($library) { - return false; - } - } -} - -# global configuration object - -require_once('PEAR.php'); -require_once('PEAR/Exception.php'); -require_once('DB/DataObject.php'); -require_once('DB/DataObject/Cast.php'); # for dates - -require_once(INSTALLDIR.'/lib/language.php'); - -// This gets included before the config file, so that admin code and plugins -// can use it - -require_once(INSTALLDIR.'/lib/event.php'); -require_once(INSTALLDIR.'/lib/plugin.php'); - -function addPlugin($name, $attrs = null) -{ - return StatusNet::addPlugin($name, $attrs); -} - -function _have_config() -{ - return StatusNet::haveConfig(); -} - -function __autoload($cls) -{ - if (file_exists(INSTALLDIR.'/classes/' . $cls . '.php')) { - require_once(INSTALLDIR.'/classes/' . $cls . '.php'); - } else if (file_exists(INSTALLDIR.'/lib/' . strtolower($cls) . '.php')) { - require_once(INSTALLDIR.'/lib/' . strtolower($cls) . '.php'); - } else if (mb_substr($cls, -6) == 'Action' && - file_exists(INSTALLDIR.'/actions/' . strtolower(mb_substr($cls, 0, -6)) . '.php')) { - require_once(INSTALLDIR.'/actions/' . strtolower(mb_substr($cls, 0, -6)) . '.php'); - } else if ($cls == 'OAuthRequest') { - require_once('OAuth.php'); - } else { - Event::handle('Autoload', array(&$cls)); - } -} - -// XXX: how many of these could be auto-loaded on use? -// XXX: note that these files should not use config options -// at compile time since DB config options are not yet loaded. - -require_once 'Validate.php'; -require_once 'markdown.php'; - -// XXX: other formats here - -define('NICKNAME_FMT', VALIDATE_NUM.VALIDATE_ALPHA_LOWER); - -require_once INSTALLDIR.'/lib/util.php'; -require_once INSTALLDIR.'/lib/action.php'; -require_once INSTALLDIR.'/lib/mail.php'; -require_once INSTALLDIR.'/lib/subs.php'; - -require_once INSTALLDIR.'/lib/clientexception.php'; -require_once INSTALLDIR.'/lib/serverexception.php'; - - -//set PEAR error handling to use regular PHP exceptions -function PEAR_ErrorToPEAR_Exception($err) -{ - //DB_DataObject throws error when an empty set would be returned - //That behavior is weird, and not how the rest of StatusNet works. - //So just ignore those errors. - if ($err->getCode() == DB_DATAOBJECT_ERROR_NODATA) { - return; - } - if ($err->getCode()) { - throw new PEAR_Exception($err->getMessage(), $err->getCode()); - } - throw new PEAR_Exception($err->getMessage()); -} -PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, 'PEAR_ErrorToPEAR_Exception'); +// All the fun stuff to actually initialize StatusNet's framework code, +// without loading up a site configuration. +require_once INSTALLDIR . '/lib/framework.php'; try { StatusNet::init(@$server, @$path, @$conffile); diff --git a/lib/default.php b/lib/default.php index e41d5c686..f524e194c 100644 --- a/lib/default.php +++ b/lib/default.php @@ -75,7 +75,8 @@ $default = 'schemacheck' => 'runtime', // 'runtime' or 'script' 'annotate_queries' => false, // true to add caller comments to queries, eg /* POST Notice::saveNew */ 'log_queries' => false, // true to log all DB queries - 'log_slow_queries' => 0), // if set, log queries taking over N seconds + 'log_slow_queries' => 0, // if set, log queries taking over N seconds + 'mysql_foreign_keys' => false), // if set, enables experimental foreign key support on MySQL 'syslog' => array('appname' => 'statusnet', # for syslog 'priority' => 'debug', # XXX: currently ignored diff --git a/lib/framework.php b/lib/framework.php new file mode 100644 index 000000000..acfca9f0e --- /dev/null +++ b/lib/framework.php @@ -0,0 +1,143 @@ +<?php +/* + * StatusNet - the distributed open-source microblogging tool + * Copyright (C) 2008-2010, StatusNet, Inc. + * + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + */ + +if (!defined('STATUSNET') && !defined('LACONICA')) { exit(1); } + +define('STATUSNET_VERSION', '0.9.6'); +define('LACONICA_VERSION', STATUSNET_VERSION); // compatibility + +define('STATUSNET_CODENAME', 'Man on the Moon'); + +define('AVATAR_PROFILE_SIZE', 96); +define('AVATAR_STREAM_SIZE', 48); +define('AVATAR_MINI_SIZE', 24); + +define('NOTICES_PER_PAGE', 20); +define('PROFILES_PER_PAGE', 20); + +define('FOREIGN_NOTICE_SEND', 1); +define('FOREIGN_NOTICE_RECV', 2); +define('FOREIGN_NOTICE_SEND_REPLY', 4); + +define('FOREIGN_FRIEND_SEND', 1); +define('FOREIGN_FRIEND_RECV', 2); + +define('NOTICE_INBOX_SOURCE_SUB', 1); +define('NOTICE_INBOX_SOURCE_GROUP', 2); +define('NOTICE_INBOX_SOURCE_REPLY', 3); +define('NOTICE_INBOX_SOURCE_FORWARD', 4); +define('NOTICE_INBOX_SOURCE_GATEWAY', -1); + +# append our extlib dir as the last-resort place to find libs + +set_include_path(get_include_path() . PATH_SEPARATOR . INSTALLDIR . '/extlib/'); + +// To protect against upstream libraries which haven't updated +// for PHP 5.3 where dl() function may not be present... +if (!function_exists('dl')) { + // function_exists() returns false for things in disable_functions, + // but they still exist and we'll die if we try to redefine them. + // + // Fortunately trying to call the disabled one will only trigger + // a warning, not a fatal, so it's safe to leave it for our case. + // Callers will be suppressing warnings anyway. + $disabled = array_filter(array_map('trim', explode(',', ini_get('disable_functions')))); + if (!in_array('dl', $disabled)) { + function dl($library) { + return false; + } + } +} + +# global configuration object + +require_once('PEAR.php'); +require_once('PEAR/Exception.php'); +require_once('DB/DataObject.php'); +require_once('DB/DataObject/Cast.php'); # for dates + +require_once(INSTALLDIR.'/lib/language.php'); + +// This gets included before the config file, so that admin code and plugins +// can use it + +require_once(INSTALLDIR.'/lib/event.php'); +require_once(INSTALLDIR.'/lib/plugin.php'); + +function addPlugin($name, $attrs = null) +{ + return StatusNet::addPlugin($name, $attrs); +} + +function _have_config() +{ + return StatusNet::haveConfig(); +} + +function __autoload($cls) +{ + if (file_exists(INSTALLDIR.'/classes/' . $cls . '.php')) { + require_once(INSTALLDIR.'/classes/' . $cls . '.php'); + } else if (file_exists(INSTALLDIR.'/lib/' . strtolower($cls) . '.php')) { + require_once(INSTALLDIR.'/lib/' . strtolower($cls) . '.php'); + } else if (mb_substr($cls, -6) == 'Action' && + file_exists(INSTALLDIR.'/actions/' . strtolower(mb_substr($cls, 0, -6)) . '.php')) { + require_once(INSTALLDIR.'/actions/' . strtolower(mb_substr($cls, 0, -6)) . '.php'); + } else if ($cls == 'OAuthRequest') { + require_once('OAuth.php'); + } else { + Event::handle('Autoload', array(&$cls)); + } +} + +// XXX: how many of these could be auto-loaded on use? +// XXX: note that these files should not use config options +// at compile time since DB config options are not yet loaded. + +require_once 'Validate.php'; +require_once 'markdown.php'; + +// XXX: other formats here + +define('NICKNAME_FMT', VALIDATE_NUM.VALIDATE_ALPHA_LOWER); + +require_once INSTALLDIR.'/lib/util.php'; +require_once INSTALLDIR.'/lib/action.php'; +require_once INSTALLDIR.'/lib/mail.php'; +require_once INSTALLDIR.'/lib/subs.php'; + +require_once INSTALLDIR.'/lib/clientexception.php'; +require_once INSTALLDIR.'/lib/serverexception.php'; + + +//set PEAR error handling to use regular PHP exceptions +function PEAR_ErrorToPEAR_Exception($err) +{ + //DB_DataObject throws error when an empty set would be returned + //That behavior is weird, and not how the rest of StatusNet works. + //So just ignore those errors. + if ($err->getCode() == DB_DATAOBJECT_ERROR_NODATA) { + return; + } + if ($err->getCode()) { + throw new PEAR_Exception($err->getMessage(), $err->getCode()); + } + throw new PEAR_Exception($err->getMessage()); +} +PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, 'PEAR_ErrorToPEAR_Exception'); diff --git a/lib/installer.php b/lib/installer.php index a9d809011..ad1989f4e 100644 --- a/lib/installer.php +++ b/lib/installer.php @@ -2,7 +2,7 @@ /** * StatusNet - the distributed open-source microblogging tool - * Copyright (C) 2009, StatusNet, Inc. + * Copyright (C) 2009-2010, StatusNet, Inc. * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License as published by @@ -32,9 +32,10 @@ * @author Sarven Capadisli <csarven@status.net> * @author Tom Adams <tom@holizz.com> * @author Zach Copley <zach@status.net> + * @copyright 2009-2010 StatusNet, Inc http://status.net * @copyright 2009 Free Software Foundation, Inc http://www.fsf.org * @license GNU Affero General Public License http://www.gnu.org/licenses/ - * @version 0.9.x + * @version 1.0.x * @link http://status.net */ @@ -53,12 +54,12 @@ abstract class Installer 'mysql' => array( 'name' => 'MySQL', 'check_module' => 'mysqli', - 'installer' => 'mysql_db_installer', + 'scheme' => 'mysqli', // DSN prefix for PEAR::DB ), 'pgsql' => array( 'name' => 'PostgreSQL', 'check_module' => 'pgsql', - 'installer' => 'pgsql_db_installer', + 'scheme' => 'pgsql', // DSN prefix for PEAR::DB ), ); @@ -254,6 +255,7 @@ abstract class Installer * Set up the database with the appropriate function for the selected type... * Saves database info into $this->db. * + * @fixme escape things in the connection string in case we have a funny pass etc * @return mixed array of database connection params on success, false on failure */ function setupDatabase() @@ -261,134 +263,97 @@ abstract class Installer if ($this->db) { throw new Exception("Bad order of operations: DB already set up."); } - $method = self::$dbModules[$this->dbtype]['installer']; - $db = call_user_func(array($this, $method), - $this->host, - $this->database, - $this->username, - $this->password); - $this->db = $db; - return $this->db; - } - - /** - * Set up a database on PostgreSQL. - * Will output status updates during the operation. - * - * @param string $host - * @param string $database - * @param string $username - * @param string $password - * @return mixed array of database connection params on success, false on failure - * - * @fixme escape things in the connection string in case we have a funny pass etc - */ - function Pgsql_Db_installer($host, $database, $username, $password) - { - $connstring = "dbname=$database host=$host user=$username"; - - //No password would mean trust authentication used. - if (!empty($password)) { - $connstring .= " password=$password"; - } $this->updateStatus("Starting installation..."); - $this->updateStatus("Checking database..."); - $conn = pg_connect($connstring); - if ($conn ===false) { - $this->updateStatus("Failed to connect to database: $connstring"); - return false; + if (empty($this->password)) { + $auth = ''; + } else { + $auth = ":$this->password"; } + $scheme = self::$dbModules[$this->dbtype]['scheme']; + $dsn = "{$scheme}://{$this->username}{$auth}@{$this->host}/{$this->database}"; - //ensure database encoding is UTF8 - $record = pg_fetch_object(pg_query($conn, 'SHOW server_encoding')); - if ($record->server_encoding != 'UTF8') { - $this->updateStatus("StatusNet requires UTF8 character encoding. Your database is ". htmlentities($record->server_encoding)); - return false; + $this->updateStatus("Checking database..."); + $conn = $this->connectDatabase($dsn); + + // ensure database encoding is UTF8 + if ($this->dbtype == 'mysql') { + // @fixme utf8m4 support for mysql 5.5? + // Force the comms charset to utf8 for sanity + // This doesn't currently work. :P + //$conn->executes('set names utf8'); + } else if ($this->dbtype == 'pgsql') { + $record = $conn->getRow('SHOW server_encoding'); + if ($record->server_encoding != 'UTF8') { + $this->updateStatus("StatusNet requires UTF8 character encoding. Your database is ". htmlentities($record->server_encoding)); + return false; + } } - $this->updateStatus("Running database script..."); - //wrap in transaction; - pg_query($conn, 'BEGIN'); - $res = $this->runDbScript('statusnet_pg.sql', $conn, 'pgsql'); - - if ($res === false) { - $this->updateStatus("Can't run database script.", true); + $res = $this->updateStatus("Creating database tables..."); + if (!$this->createCoreTables($conn)) { + $this->updateStatus("Error creating tables.", true); return false; } + foreach (array('sms_carrier' => 'SMS carrier', 'notice_source' => 'notice source', 'foreign_services' => 'foreign service') as $scr => $name) { $this->updateStatus(sprintf("Adding %s data to database...", $name)); - $res = $this->runDbScript($scr.'.sql', $conn, 'pgsql'); + $res = $this->runDbScript($scr.'.sql', $conn); if ($res === false) { - $this->updateStatus(sprintf("Can't run %s script.", $name), true); + $this->updateStatus(sprintf("Can't run %d script.", $name), true); return false; } } - pg_query($conn, 'COMMIT'); - - if (empty($password)) { - $sqlUrl = "pgsql://$username@$host/$database"; - } else { - $sqlUrl = "pgsql://$username:$password@$host/$database"; - } - - $db = array('type' => 'pgsql', 'database' => $sqlUrl); + $db = array('type' => $this->dbtype, 'database' => $dsn); return $db; } /** - * Set up a database on MySQL. - * Will output status updates during the operation. - * - * @param string $host - * @param string $database - * @param string $username - * @param string $password - * @return mixed array of database connection params on success, false on failure - * - * @fixme escape things in the connection string in case we have a funny pass etc + * Open a connection to the database. + * + * @param <type> $dsn + * @return <type> */ - function Mysql_Db_installer($host, $database, $username, $password) + function connectDatabase($dsn) { - $this->updateStatus("Starting installation..."); - $this->updateStatus("Checking database..."); - - $conn = mysqli_init(); - if (!$conn->real_connect($host, $username, $password)) { - $this->updateStatus("Can't connect to server '$host' as '$username'.", true); - return false; - } - $this->updateStatus("Changing to database..."); - if (!$conn->select_db($database)) { - $this->updateStatus("Can't change to database.", true); - return false; - } + // @fixme move this someplace more sensible + //set_include_path(INSTALLDIR . '/extlib' . PATH_SEPARATOR . get_include_path()); + require_once 'DB.php'; + return DB::connect($dsn); + } - $this->updateStatus("Running database script..."); - $res = $this->runDbScript('statusnet.sql', $conn); - if ($res === false) { - $this->updateStatus("Can't run database script.", true); - return false; - } - foreach (array('sms_carrier' => 'SMS carrier', - 'notice_source' => 'notice source', - 'foreign_services' => 'foreign service') - as $scr => $name) { - $this->updateStatus(sprintf("Adding %s data to database...", $name)); - $res = $this->runDbScript($scr.'.sql', $conn); - if ($res === false) { - $this->updateStatus(sprintf("Can't run %d script.", $name), true); - return false; + /** + * Create core tables on the given database connection. + * + * @param DB_common $conn + */ + function createCoreTables(DB_common $conn) + { + $schema = Schema::get($conn); + $tableDefs = $this->getCoreSchema(); + foreach ($tableDefs as $name => $def) { + if (defined('DEBUG_INSTALLER')) { + echo " $name "; } + $schema->ensureTable($name, $def); } + return true; + } - $sqlUrl = "mysqli://$username:$password@$host/$database"; - $db = array('type' => 'mysql', 'database' => $sqlUrl); - return $db; + /** + * Fetch the core table schema definitions. + * + * @return array of table names => table def arrays + */ + function getCoreSchema() + { + $schema = array(); + include INSTALLDIR . '/db/core.php'; + return $schema; } /** @@ -463,13 +428,12 @@ abstract class Installer /** * Install schema into the database * - * @param string $filename location of database schema file - * @param dbconn $conn connection to database - * @param string $type type of database, currently mysql or pgsql + * @param string $filename location of database schema file + * @param DB_common $conn connection to database * * @return boolean - indicating success or failure */ - function runDbScript($filename, $conn, $type = 'mysqli') + function runDbScript($filename, DB_common $conn) { $sql = trim(file_get_contents(INSTALLDIR . '/db/' . $filename)); $stmts = explode(';', $sql); @@ -478,26 +442,12 @@ abstract class Installer if (!mb_strlen($stmt)) { continue; } - // FIXME: use PEAR::DB or PDO instead of our own switch - switch ($type) { - case 'mysqli': - $res = $conn->query($stmt); - if ($res === false) { - $error = $conn->error; - } - break; - case 'pgsql': - $res = pg_query($conn, $stmt); - if ($res === false) { - $error = pg_last_error(); - } - break; - default: - $this->updateStatus("runDbScript() error: unknown database type ". $type ." provided."); - } - if ($res === false) { + try { + $res = $conn->simpleQuery($stmt); + } catch (Exception $e) { + $error = $e->getMessage(); $this->updateStatus("ERROR ($error) for SQL '$stmt'"); - return $res; + return false; } } return true; @@ -510,9 +460,6 @@ abstract class Installer */ function registerInitialUser() { - define('STATUSNET', true); - define('LACONICA', true); // compatibility - require_once INSTALLDIR . '/lib/common.php'; $data = array('nickname' => $this->adminNick, @@ -559,10 +506,22 @@ abstract class Installer */ function doInstall() { - $this->db = $this->setupDatabase(); - - if (!$this->db) { - // database connection failed, do not move on to create config file. + $this->updateStatus("Initializing..."); + ini_set('display_errors', 1); + error_reporting(E_ALL); + define('STATUSNET', 1); + require_once INSTALLDIR . '/lib/framework.php'; + StatusNet::initDefaults($this->server, $this->path); + + try { + $this->db = $this->setupDatabase(); + if (!$this->db) { + // database connection failed, do not move on to create config file. + return false; + } + } catch (Exception $e) { + // Lower-level DB error! + $this->updateStatus("Database error: " . $e->getMessage(), true); return false; } diff --git a/lib/mysqlschema.php b/lib/mysqlschema.php index f9552c1dc..c3d3501c7 100644 --- a/lib/mysqlschema.php +++ b/lib/mysqlschema.php @@ -72,72 +72,127 @@ class MysqlSchema extends Schema * * Throws an exception if the table is not found. * - * @param string $name Name of the table to get + * @param string $table Name of the table to get * * @return TableDef tabledef for that table. * @throws SchemaTableMissingException */ - public function getTableDef($name) + public function getTableDef($table) { - $query = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS " . - "WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'"; - $schema = $this->conn->dsn['database']; - $sql = sprintf($query, $schema, $name); - $res = $this->conn->query($sql); + $def = array(); + $hasKeys = false; - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - if ($res->numRows() == 0) { - $res->free(); - throw new SchemaTableMissingException("No such table: $name"); + // Pull column data from INFORMATION_SCHEMA + $columns = $this->fetchMetaInfo($table, 'COLUMNS', 'ORDINAL_POSITION'); + if (count($columns) == 0) { + throw new SchemaTableMissingException("No such table: $table"); } - $td = new TableDef(); + foreach ($columns as $row) { - $td->name = $name; - $td->columns = array(); + $name = $row['COLUMN_NAME']; + $field = array(); - $row = array(); + // warning -- 'unsigned' attr on numbers isn't given in DATA_TYPE and friends. + // It is stuck in on COLUMN_TYPE though (eg 'bigint(20) unsigned') + $field['type'] = $type = $row['DATA_TYPE']; - while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { + if ($type == 'char' || $type == 'varchar') { + if ($row['CHARACTER_MAXIMUM_LENGTH'] !== null) { + $field['length'] = intval($row['CHARACTER_MAXIMUM_LENGTH']); + } + } + if ($type == 'decimal') { + // Other int types may report these values, but they're irrelevant. + // Just ignore them! + if ($row['NUMERIC_PRECISION'] !== null) { + $field['precision'] = intval($row['NUMERIC_PRECISION']); + } + if ($row['NUMERIC_SCALE'] !== null) { + $field['scale'] = intval($row['NUMERIC_SCALE']); + } + } + if ($row['IS_NULLABLE'] == 'NO') { + $field['not null'] = true; + } + if ($row['COLUMN_DEFAULT'] !== null) { + // Hack for timestamp cols + if ($type == 'timestamp' && $row['COLUMN_DEFAULT'] == 'CURRENT_TIMESTAMP') { + // skip + } else { + $field['default'] = $row['COLUMN_DEFAULT']; + if ($this->isNumericType($type)) { + $field['default'] = intval($field['default']); + } + } + } + if ($row['COLUMN_KEY'] !== null) { + // We'll need to look up key info... + $hasKeys = true; + } + if ($row['COLUMN_COMMENT'] !== null && $row['COLUMN_COMMENT'] != '') { + $field['description'] = $row['COLUMN_COMMENT']; + } - $cd = new ColumnDef(); + $extra = $row['EXTRA']; + if ($extra) { + if (preg_match('/(^|\s)auto_increment(\s|$)/i', $extra)) { + $field['auto_increment'] = true; + } + // $row['EXTRA'] may contain 'on update CURRENT_TIMESTAMP' + // ^ ...... how to specify? + } - $cd->name = $row['COLUMN_NAME']; + if ($row['CHARACTER_SET_NAME'] !== null) { + // @fixme check against defaults? + //$def['charset'] = $row['CHARACTER_SET_NAME']; + //$def['collate'] = $row['COLLATION_NAME']; + } - $packed = $row['COLUMN_TYPE']; + $def['fields'][$name] = $field; + } - if (preg_match('/^(\w+)\((\d+)\)$/', $packed, $match)) { - $cd->type = $match[1]; - $cd->size = $match[2]; - } else { - $cd->type = $packed; - } + if ($hasKeys) { + // INFORMATION_SCHEMA's CONSTRAINTS and KEY_COLUMN_USAGE tables give + // good info on primary and unique keys but don't list ANY info on + // multi-value keys, which is lame-o. Sigh. + // + // Let's go old school and use SHOW INDEX :D + // + $keyInfo = $this->fetchIndexInfo($table); + $keys = array(); + foreach ($keyInfo as $row) { + $name = $row['Key_name']; + $column = $row['Column_name']; - $cd->nullable = ($row['IS_NULLABLE'] == 'YES') ? true : false; - $cd->key = $row['COLUMN_KEY']; - $cd->default = $row['COLUMN_DEFAULT']; - $cd->extra = $row['EXTRA']; - - // Autoincrement is stuck into the extra column. - // Pull it out so we don't accidentally mod it every time... - $extra = preg_replace('/(^|\s)auto_increment(\s|$)/i', '$1$2', $cd->extra); - if ($extra != $cd->extra) { - $cd->extra = trim($extra); - $cd->auto_increment = true; + if (!isset($keys[$name])) { + $keys[$name] = array(); + } + $keys[$name][] = $column; + + if ($name == 'PRIMARY') { + $type = 'primary key'; + } else if ($row['Non_unique'] == 0) { + $type = 'unique keys'; + } else if ($row['Index_type'] == 'FULLTEXT') { + $type = 'fulltext indexes'; + } else { + $type = 'indexes'; + } + $keyTypes[$name] = $type; } - // mysql extensions -- not (yet) used by base class - $cd->charset = $row['CHARACTER_SET_NAME']; - $cd->collate = $row['COLLATION_NAME']; - - $td->columns[] = $cd; + foreach ($keyTypes as $name => $type) { + if ($type == 'primary key') { + // there can be only one + $def[$type] = $keys[$name]; + } else { + $def[$type][$name] = $keys[$name]; + } + } } - $res->free(); - - return $td; + return $def; } /** @@ -150,483 +205,139 @@ class MysqlSchema extends Schema function getTableProperties($table, $props) { - $query = "SELECT %s FROM INFORMATION_SCHEMA.TABLES " . - "WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'"; - $schema = $this->conn->dsn['database']; - $sql = sprintf($query, implode(',', $props), $schema, $table); - $res = $this->conn->query($sql); - - $row = array(); - $ok = $res->fetchInto($row, DB_FETCHMODE_ASSOC); - $res->free(); - - if ($ok) { - return $row; + $data = $this->fetchMetaInfo($table, 'TABLES'); + if ($data) { + return $data[0]; } else { throw new SchemaTableMissingException("No such table: $table"); } } /** - * Gets a ColumnDef object for a single column. + * Pull some INFORMATION.SCHEMA data for the given table. * - * 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. + * @param string $table + * @return array of arrays */ - - public function getColumnDef($table, $column) + function fetchMetaInfo($table, $infoTable, $orderBy=null) { - $td = $this->getTableDef($table); - - foreach ($td->columns as $cd) { - if ($cd->name == $column) { - return $cd; - } + $query = "SELECT * FROM INFORMATION_SCHEMA.%s " . + "WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'"; + $schema = $this->conn->dsn['database']; + $sql = sprintf($query, $infoTable, $schema, $table); + if ($orderBy) { + $sql .= ' ORDER BY ' . $orderBy; } - - return null; + return $this->fetchQueryData($sql); } /** - * Creates a table with the given names and columns. + * Pull 'SHOW INDEX' data for the given table. * - * @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); - } - - $idx = $this->_indexList($columns); - - if ($idx['primary']) { - $sql .= ",\nconstraint primary key (" . implode(',', $idx['primary']) . ")"; - } - - foreach ($idx['uniques'] as $u) { - $key = $this->_uniqueKey($name, $u); - $sql .= ",\nunique index $key ($u)"; - } - - foreach ($idx['indices'] as $i) { - $key = $this->_key($name, $i); - $sql .= ",\nindex $key ($i)"; - } - - $sql .= ") ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; "; - - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; - } - - /** - * Look over a list of column definitions and list up which - * indices will be present + * @param string $table + * @return array of arrays */ - private function _indexList(array $columns) + function fetchIndexInfo($table) { - $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; + $query = "SHOW INDEX FROM `%s`"; + $sql = sprintf($query, $table); + return $this->fetchQueryData($sql); } /** - * Get the unique index key name for a given column on this table - */ - function _uniqueKey($tableName, $columnName) - { - return $this->_key($tableName, $columnName); - } - - /** - * Get the index key name for a given column on this table - */ - function _key($tableName, $columnName) - { - return "{$tableName}_{$columnName}_idx"; - } - - /** - * Drops a table from the schema + * Append an SQL statement with an index definition for a full-text search + * index over one or more columns on a table. * - * Throws an exception if the table is not found. - * - * @param string $name Name of the table to drop - * - * @return boolean success flag + * @param array $statements + * @param string $table + * @param string $name + * @param array $def */ - - public function dropTable($name) + function appendCreateFulltextIndex(array &$statements, $table, $name, array $def) { - $res = $this->conn->query("DROP TABLE $name"); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; + $statements[] = "CREATE FULLTEXT INDEX $name ON $table " . $this->buildIndexList($def); } /** - * 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. + * Close out a 'create table' SQL statement. * - * Throws an exception on database error, esp. if the table - * does not exist. + * @param string $name + * @param array $def + * @return string; * - * @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 + * @fixme ENGINE may need to be set differently in some cases, + * such as to support fulltext index. */ - - public function createIndex($table, $columnNames, $name=null) + function endCreateTable($name, array $def) { - 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; + $engine = $this->preferredEngine($def); + return ") ENGINE=$engine CHARACTER SET utf8 COLLATE utf8_bin"; } - - /** - * 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) + + function preferredEngine($def) { - $res = $this->conn->query("ALTER TABLE $table DROP INDEX $name"); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + if (!empty($def['fulltext indexes'])) { + return 'MyISAM'; } - - return true; + return 'InnoDB'; } /** - * 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 + * Get the unique index key name for a given column on this table */ - - public function addColumn($table, $columndef) + function _uniqueKey($tableName, $columnName) { - $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; + return $this->_key($tableName, $columnName); } /** - * 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 + * Get the index key name for a given column on this table */ - - public function modifyColumn($table, $columndef) + function _key($tableName, $columnName) { - $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; + return "{$tableName}_{$columnName}_idx"; } + /** - * Drops a column from a table + * MySQL doesn't take 'DROP CONSTRAINT', need to treat unique keys as + * if they were indexes here. * - * 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 + * @param array $phrase + * @param <type> $keyName MySQL */ - - public function dropColumn($table, $columnName) + function appendAlterDropUnique(array &$phrase, $keyName) { - $sql = "ALTER TABLE $table DROP COLUMN $columnName"; - - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; + $phrase[] = 'DROP INDEX ' . $keyName; } /** - * 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 + * Throw some table metadata onto the ALTER TABLE if we have a mismatch + * in expected type, collation. */ - - public function ensureTable($tableName, $columns) + function appendAlterExtras(array &$phrase, $tableName, array $def) { - // XXX: DB engine portability -> toilet - - try { - $td = $this->getTableDef($tableName); - } catch (SchemaTableMissingException $e) { - return $this->createTable($tableName, $columns); - } - - $cur = $this->_names($td->columns); - $new = $this->_names($columns); - - $dropIndex = array(); - $toadd = array_diff($new, $cur); - $todrop = array_diff($cur, $new); - $same = array_intersect($new, $cur); - $tomod = array(); - $addIndex = array(); - $tableProps = array(); - - foreach ($same as $m) { - $curCol = $this->_byName($td->columns, $m); - $newCol = $this->_byName($columns, $m); - - if (!$newCol->equals($curCol)) { - $tomod[] = $newCol->name; - continue; - } - - // Earlier versions may have accidentally left tables at default - // charsets which might be latin1 or other freakish things. - if ($this->_isString($curCol)) { - if ($curCol->charset != 'utf8') { - $tomod[] = $newCol->name; - continue; - } - } - } - - // Find any indices we have to change... - $curIdx = $this->_indexList($td->columns); - $newIdx = $this->_indexList($columns); - - if ($curIdx['primary'] != $newIdx['primary']) { - if ($curIdx['primary']) { - $dropIndex[] = 'drop primary key'; - } - if ($newIdx['primary']) { - $keys = implode(',', $newIdx['primary']); - $addIndex[] = "add constraint primary key ($keys)"; - } - } - - $dropUnique = array_diff($curIdx['uniques'], $newIdx['uniques']); - $addUnique = array_diff($newIdx['uniques'], $curIdx['uniques']); - foreach ($dropUnique as $columnName) { - $dropIndex[] = 'drop key ' . $this->_uniqueKey($tableName, $columnName); - } - foreach ($addUnique as $columnName) { - $addIndex[] = 'add constraint unique key ' . $this->_uniqueKey($tableName, $columnName) . " ($columnName)";; - } - - $dropMultiple = array_diff($curIdx['indices'], $newIdx['indices']); - $addMultiple = array_diff($newIdx['indices'], $curIdx['indices']); - foreach ($dropMultiple as $columnName) { - $dropIndex[] = 'drop key ' . $this->_key($tableName, $columnName); - } - foreach ($addMultiple as $columnName) { - $addIndex[] = 'add key ' . $this->_key($tableName, $columnName) . " ($columnName)"; - } - // Check for table properties: make sure we're using a sane // engine type and charset/collation. // @fixme make the default engine configurable? $oldProps = $this->getTableProperties($tableName, array('ENGINE', 'TABLE_COLLATION')); - if (strtolower($oldProps['ENGINE']) != 'innodb') { - $tableProps['ENGINE'] = 'InnoDB'; + $engine = $this->preferredEngine($def); + if (strtolower($oldProps['ENGINE']) != strtolower($engine)) { + $phrase[] = "ENGINE=$engine"; } if (strtolower($oldProps['TABLE_COLLATION']) != 'utf8_bin') { - $tableProps['DEFAULT CHARSET'] = 'utf8'; - $tableProps['COLLATE'] = 'utf8_bin'; - } - - if (count($dropIndex) + count($toadd) + count($todrop) + count($tomod) + count($addIndex) + count($tableProps) == 0) { - // nothing to do - return true; + $phrase[] = 'DEFAULT CHARSET=utf8'; + $phrase[] = 'COLLATE=utf8_bin'; } - - // For efficiency, we want this all in one - // query, instead of using our methods. - - $phrase = array(); - - foreach ($dropIndex as $indexSql) { - $phrase[] = $indexSql; - } - - 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); - } - - foreach ($addIndex as $indexSql) { - $phrase[] = $indexSql; - } - - foreach ($tableProps as $key => $val) { - $phrase[] = "$key=$val"; - } - - $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(', ', $phrase); - - common_log(LOG_DEBUG, __METHOD__ . ': ' . $sql); - $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. + * 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 +352,93 @@ 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} "; + // This'll have been added from our transform of 'serial' type + if (!empty($cd['auto_increment'])) { + $line[] = 'auto_increment'; } - if ($this->_isString($cd)) { - $sql .= " CHARACTER SET utf8 "; + if (!empty($cd['description'])) { + $line[] = 'comment'; + $line[] = $this->quoteValue($cd['description']); } - if (!empty($cd->default)) { - $sql .= "default {$cd->default} "; - } else { - $sql .= ($cd->nullable) ? "null " : "not null "; - } + return implode(' ', $line); + } + + function mapType($column) + { + $map = array('serial' => 'int', + 'integer' => 'int', + 'numeric' => 'decimal'); - if (!empty($cd->auto_increment)) { - $sql .= " auto_increment "; + $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; + } } - if (!empty($cd->extra)) { - $sql .= "{$cd->extra} "; - } + return $type; + } - return $sql; + function typeAndSize($column) + { + if ($column['type'] == 'enum') { + $vals = array_map(array($this, 'quote'), $column['enum']); + return 'enum(' . implode(',', $vals) . ')'; + } else if ($this->_isString($column)) { + $col = parent::typeAndSize($column); + if (!empty($column['charset'])) { + $col .= ' CHARSET ' . $column['charset']; + } + if (!empty($column['collate'])) { + $col .= ' COLLATE ' . $column['collate']; + } + return $col; + } else { + return parent::typeAndSize($column); + } } /** - * Is this column a string type? + * Filter the given table definition array to match features available + * in this database. + * + * This lets us strip out unsupported things like comments, foreign keys, + * or type variants that we wouldn't get back from getTableDef(). + * + * @param array $tableDef */ - private function _isString(ColumnDef $cd) + function filterDef(array $tableDef) { - $strings = array('char', 'varchar', 'text'); - return in_array(strtolower($cd->type), $strings); + foreach ($tableDef['fields'] as $name => &$col) { + if ($col['type'] == 'serial') { + $col['type'] = 'int'; + $col['auto_increment'] = true; + } + if ($col['type'] == 'datetime' && isset($col['default']) && $col['default'] == 'CURRENT_TIMESTAMP') { + $col['type'] = 'timestamp'; + } + $col['type'] = $this->mapType($col); + unset($col['size']); + } + if (!common_config('db', 'mysql_foreign_keys')) { + unset($tableDef['foreign keys']); + } + return $tableDef; } } diff --git a/lib/pgsqlschema.php b/lib/pgsqlschema.php index 272f7eff6..d50e35f66 100644 --- a/lib/pgsqlschema.php +++ b/lib/pgsqlschema.php @@ -42,6 +42,7 @@ if (!defined('STATUSNET')) { * @package StatusNet * @author Evan Prodromou <evan@status.net> * @author Brenda Wallace <shiny@cpan.org> + * @author Brion Vibber <brion@status.net> * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 * @link http://status.net/ */ @@ -50,167 +51,209 @@ class PgsqlSchema extends Schema { /** - * Returns a TableDef object for the table + * Returns a table definition array 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 + * @param string $table Name of the table to get * - * @return TableDef tabledef for that table. + * @return array tabledef for that table. */ - public function getTableDef($name) + public function getTableDef($table) { - $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'"); + $def = array(); + $hasKeys = false; - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + // Pull column data from INFORMATION_SCHEMA + $columns = $this->fetchMetaInfo($table, 'columns', 'ordinal_position'); + if (count($columns) == 0) { + throw new SchemaTableMissingException("No such table: $table"); } - $td = new TableDef(); + // We'll need to match up fields by ordinal reference + $orderedFields = array(); - $td->name = $name; - $td->columns = array(); + foreach ($columns as $row) { - if ($res->numRows() == 0 ) { - throw new Exception('no such table'); //pretend to be the msyql error. yeah, this sucks. - } - $row = array(); + $name = $row['column_name']; + $orderedFields[$row['ordinal_position']] = $name; - while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { - $cd = new ColumnDef(); + $field = array(); + $field['type'] = $row['udt_name']; - $cd->name = $row['field']; + if ($type == 'char' || $type == 'varchar') { + if ($row['character_maximum_length'] !== null) { + $field['length'] = intval($row['character_maximum_length']); + } + } + if ($type == 'numeric') { + // Other int types may report these values, but they're irrelevant. + // Just ignore them! + if ($row['numeric_precision'] !== null) { + $field['precision'] = intval($row['numeric_precision']); + } + if ($row['numeric_scale'] !== null) { + $field['scale'] = intval($row['numeric_scale']); + } + } + if ($row['is_nullable'] == 'NO') { + $field['not null'] = true; + } + if ($row['column_default'] !== null) { + $field['default'] = $row['column_default']; + if ($this->isNumericType($type)) { + $field['default'] = intval($field['default']); + } + } - $packed = $row['type']; + $def['fields'][$name] = $field; + } - if (preg_match('/^(\w+)\((\d+)\)$/', $packed, $match)) { - $cd->type = $match[1]; - $cd->size = $match[2]; - } else { - $cd->type = $packed; + // Pulling index info from pg_class & pg_index + // This can give us primary & unique key info, but not foreign key constraints + // so we exclude them and pick them up later. + $indexInfo = $this->getIndexInfo($table); + foreach ($indexInfo as $row) { + $keyName = $row['key_name']; + + // Dig the column references out! + // + // These are inconvenient arrays with partial references to the + // pg_att table, but since we've already fetched up the column + // info on the current table, we can look those up locally. + $cols = array(); + $colPositions = explode(' ', $row['indkey']); + foreach ($colPositions as $ord) { + if ($ord == 0) { + $cols[] = 'FUNCTION'; // @fixme + } else { + $cols[] = $orderedFields[$ord]; + } } - $cd->nullable = ($row['null'] == 'YES') ? true : false; - $cd->key = $row['Key']; - $cd->default = $row['default']; - $cd->extra = $row['Extra']; - - $td->columns[] = $cd; + $def['indexes'][$keyName] = $cols; } - 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); + // Pull constraint data from INFORMATION_SCHEMA: + // Primary key, unique keys, foreign keys + $keyColumns = $this->fetchMetaInfo($table, 'key_column_usage', 'constraint_name,ordinal_position'); + $keys = array(); - foreach ($td->columns as $cd) { - if ($cd->name == $column) { - return $cd; + foreach ($keyColumns as $row) { + $keyName = $row['constraint_name']; + $keyCol = $row['column_name']; + if (!isset($keys[$keyName])) { + $keys[$keyName] = array(); } + $keys[$keyName][] = $keyCol; } - return null; + foreach ($keys as $keyName => $cols) { + // name hack -- is this reliable? + if ($keyName == "{$table}_pkey") { + $def['primary key'] = $cols; + } else if (preg_match("/^{$table}_(.*)_fkey$/", $keyName, $matches)) { + $fkey = $this->getForeignKeyInfo($table, $keyName); + $colMap = array_combine($cols, $fkey['col_names']); + $def['foreign keys'][$keyName] = array($fkey['table_name'], $colMap); + } else { + $def['unique keys'][$keyName] = $cols; + } + } + return $def; } /** - * 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. + * Pull some INFORMATION.SCHEMA data for the given table. * - * @return boolean success flag + * @param string $table + * @return array of arrays */ - - public function createTable($name, $columns) + function fetchMetaInfo($table, $infoTable, $orderBy=null) { - $uniques = array(); - $primary = array(); - $indices = array(); - $onupdate = 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 .= ",\n PRIMARY KEY (" . implode(',', $primary) . ")"; - } - - $sql .= "); "; - - - foreach ($uniques as $u) { - $sql .= "\n CREATE index {$name}_{$u}_idx ON {$name} ($u); "; + $query = "SELECT * FROM information_schema.%s " . + "WHERE table_name='%s'"; + $sql = sprintf($query, $infoTable, $table); + if ($orderBy) { + $sql .= ' ORDER BY ' . $orderBy; } + return $this->fetchQueryData($sql); + } - foreach ($indices as $i) { - $sql .= "CREATE index {$name}_{$i}_idx ON {$name} ($i)"; - } - $res = $this->conn->query($sql); + /** + * Pull some PG-specific index info + * @param string $table + * @return array of arrays + */ + function getIndexInfo($table) + { + $query = 'SELECT ' . + '(SELECT relname FROM pg_class WHERE oid=indexrelid) AS key_name, ' . + '* FROM pg_index ' . + 'WHERE indrelid=(SELECT oid FROM pg_class WHERE relname=\'%s\') ' . + 'AND indisprimary=\'f\' AND indisunique=\'f\' ' . + 'ORDER BY indrelid, indexrelid'; + $sql = sprintf($query, $table); + return $this->fetchQueryData($sql); + } - if (PEAR::isError($res)) { - throw new Exception($res->getMessage(). ' SQL was '. $sql); + /** + * Column names from the foreign table can be resolved with a call to getTableColumnNames() + * @param <type> $table + * @return array array of rows with keys: fkey_name, table_name, table_id, col_names (array of strings) + */ + function getForeignKeyInfo($table, $constraint_name) + { + // In a sane world, it'd be easier to query the column names directly. + // But it's pretty hard to work with arrays such as col_indexes in direct SQL here. + $query = 'SELECT ' . + '(SELECT relname FROM pg_class WHERE oid=confrelid) AS table_name, ' . + 'confrelid AS table_id, ' . + '(SELECT indkey FROM pg_index WHERE indexrelid=conindid) AS col_indexes ' . + 'FROM pg_constraint ' . + 'WHERE conrelid=(SELECT oid FROM pg_class WHERE relname=\'%s\') ' . + 'AND conname=\'%s\' ' . + 'AND contype=\'f\''; + $sql = sprintf($query, $table, $constraint_name); + $data = $this->fetchQueryData($sql); + if (count($data) < 1) { + throw new Exception("Could not find foreign key " . $constraint_name . " on table " . $table); } - return true; + $row = $data[0]; + return array( + 'table_name' => $row['table_name'], + 'col_names' => $this->getTableColumnNames($row['table_id'], $row['col_indexes']) + ); } /** - * 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 + * @param int $table_id + * @param array $col_indexes + * @return array of strings */ - - public function dropTable($name) + function getTableColumnNames($table_id, $col_indexes) { - $res = $this->conn->query("DROP TABLE $name"); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + $indexes = array_map('intval', explode(' ', $col_indexes)); + $query = 'SELECT attnum AS col_index, attname AS col_name ' . + 'FROM pg_attribute where attrelid=%d ' . + 'AND attnum IN (%s)'; + $sql = sprintf($query, $table_id, implode(',', $indexes)); + $data = $this->fetchQueryData($sql); + + $byId = array(); + foreach ($data as $row) { + $byId[$row['col_index']] = $row['col_name']; } - return true; + $out = array(); + foreach ($indexes as $id) { + $out[] = $byId[$id]; + } + return $out; } /** @@ -230,303 +273,183 @@ 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. + * Return the proper SQL for creating or + * altering a column. * - * Throws an exception on database error, esp. if the table - * does not exist. + * Appropriate for use in CREATE TABLE or + * ALTER TABLE statements. * - * @param string $table Name of the table - * @param array $columnNames Name of columns to index - * @param string $name (Optional) name of the index + * @param array $cd column to create * - * @return boolean success flag + * @return string correct SQL for that column */ - public function createIndex($table, $columnNames, $name=null) + function columnSql(array $cd) { - 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()); + $line = array(); + $line[] = parent::columnSql($cd); + + /* + if ($table['foreign keys'][$name]) { + foreach ($table['foreign keys'][$name] as $foreignTable => $foreignColumn) { + $line[] = 'references'; + $line[] = $this->quoteIdentifier($foreignTable); + $line[] = '(' . $this->quoteIdentifier($foreignColumn) . ')'; + } } + */ - return true; + return implode(' ', $line); } /** - * 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 + * 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. * - * @return boolean success flag + * @param array $phrase + * @param string $columnName + * @param array $old previous column definition as found in DB + * @param array $cd current column definition */ - - public function dropIndex($table, $name) + function appendAlterModifyColumn(array &$phrase, $columnName, array $old, array $cd) { - $res = $this->conn->query("ALTER TABLE $table DROP INDEX $name"); + $prefix = 'ALTER COLUMN ' . $this->quoteIdentifier($columnName) . ' '; - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + $oldType = $this->mapType($old); + $newType = $this->mapType($cd); + if ($oldType != $newType) { + $phrase[] = $prefix . 'TYPE ' . $newType; } - 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()); + 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'; } - return true; + 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); + } } /** - * Modifies a column in the schema. - * - * The name must match an existing column and table. + * Append an SQL statement to drop an index from a table. + * Note that in PostgreSQL, index names are DB-unique. * - * @param string $table name of the table - * @param ColumnDef $columndef new definition of the column. - * - * @return boolean success flag + * @param array $statements + * @param string $table + * @param string $name + * @param array $def */ - - public function modifyColumn($table, $columndef) + function appendDropIndex(array &$statements, $table, $name) { - $sql = "ALTER TABLE $table ALTER COLUMN TYPE " . - $this->_columnSql($columndef); - - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; + $statements[] = "DROP INDEX $name"; } /** - * Drops a column from a table - * - * The name must match an existing column. + * Quote a db/table/column identifier if necessary. * - * @param string $table name of the table - * @param string $columnName name of the column to drop - * - * @return boolean success flag + * @param string $name + * @return string */ - - public function dropColumn($table, $columnName) + function quoteIdentifier($name) { - $sql = "ALTER TABLE $table DROP COLUMN $columnName"; - - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; + return $this->conn->quoteIdentifier($name); } - /** - * 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) + function mapType($column) { - // 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; - } + $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]; } - $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 ($type == 'int') { + if (!empty($column['size'])) { + $size = $column['size']; + if ($size == 'small') { + return 'int2'; + } else if ($size == 'big') { + return 'int8'; + } } - } - 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 'int4'; } - return true; + return $type; } - /** - * 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) + // @fixme need name... :P + function typeAndSize($column) { - $names = array(); - - foreach ($cds as $cd) { - $names[] = $cd->name; + if ($column['type'] == 'enum') { + $vals = array_map(array($this, 'quote'), $column['enum']); + return "text check ($name in " . implode(',', $vals) . ')'; + } else { + return parent::typeAndSize($column); } - - return $names; } /** - * Get a ColumnDef from an array matching - * name. + * Filter the given table definition array to match features available + * in this database. * - * @param array $cds Array of ColumnDef objects - * @param string $name Name of the column + * This lets us strip out unsupported things like comments, foreign keys, + * or type variants that we wouldn't get back from getTableDef(). * - * @return ColumnDef matching item or null if no match. + * @param array $tableDef */ - - private function _byName($cds, $name) + function filterDef(array $tableDef) { - foreach ($cds as $cd) { - if ($cd->name == $name) { - return $cd; + foreach ($tableDef['fields'] as $name => &$col) { + // No convenient support for field descriptions + unset($col['description']); + + /* + if (isset($col['size'])) { + // Don't distinguish between tinyint and int. + if ($col['size'] == 'tiny' && $col['type'] == 'int') { + unset($col['size']); + } } + */ + $col['type'] = $this->mapType($col); + unset($col['size']); } - - return null; + if (!empty($tableDef['primary key'])) { + $tableDef['primary key'] = $this->filterKeyDef($tableDef['primary key']); + } + if (!empty($tableDef['unique keys'])) { + foreach ($tableDef['unique keys'] as $i => $def) { + $tableDef['unique keys'][$i] = $this->filterKeyDef($def); + } + } + return $tableDef; } /** - * Return the proper SQL for creating or - * altering a column. - * - * Appropriate for use in CREATE TABLE or - * ALTER TABLE statements. + * Filter the given key/index definition to match features available + * in this database. * - * @param ColumnDef $cd column to create - * - * @return string correct SQL for that column + * @param array $def + * @return array */ - private function _columnSql($cd) + function filterKeyDef(array $def) { - $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 .= "{$type}({$cd->size}) "; - } else { - $sql .= "{$type} "; - } - - if (!empty($cd->default)) { - $sql .= "default {$cd->default} "; - } else { - $sql .= ($cd->nullable) ? "null " : "not null "; + // PostgreSQL doesn't like prefix lengths specified on keys...? + foreach ($def as $i => $item) + { + if (is_array($item)) { + $def[$i] = $item[0]; + } } - -// if (!empty($cd->extra)) { -// $sql .= "{$cd->extra} "; -// } - - return $sql; + return $def; } } diff --git a/lib/schema.php b/lib/schema.php index e5def514e..2e2795588 100644 --- a/lib/schema.php +++ b/lib/schema.php @@ -41,6 +41,7 @@ if (!defined('STATUSNET')) { * @category Database * @package StatusNet * @author Evan Prodromou <evan@status.net> + * @author Brion Vibber <brion@status.net> * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 * @link http://status.net/ */ @@ -118,65 +119,216 @@ class Schema /** * 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. + * @param string $tableName Name of the table + * @param array $def Table definition array listing fields and indexes. * * @return boolean success flag */ - public function createTable($name, $columns) + public function createTable($tableName, $def) { - $uniques = array(); - $primary = array(); - $indices = array(); + $statements = $this->buildCreateTable($tableName, $def); + return $this->runSqlSet($statements); + } - $sql = "CREATE TABLE $name (\n"; + /** + * Build a set of SQL statements to create a table with the given + * name and columns. + * + * @param string $name Name of the table + * @param array $def Table definition array + * + * @return boolean success flag + */ + public function buildCreateTable($name, $def) + { + $def = $this->validateDef($name, $def); + $def = $this->filterDef($def); + $sql = array(); - for ($i = 0; $i < count($columns); $i++) { + foreach ($def['fields'] as $col => $colDef) { + $this->appendColumnDef($sql, $col, $colDef); + } - $cd =& $columns[$i]; + // Primary, unique, and foreign keys are constraints, so go within + // the CREATE TABLE statement normally. + if (!empty($def['primary key'])) { + $this->appendPrimaryKeyDef($sql, $def['primary key']); + } - if ($i > 0) { - $sql .= ",\n"; + if (!empty($def['unique keys'])) { + foreach ($def['unique keys'] as $col => $colDef) { + $this->appendUniqueKeyDef($sql, $col, $colDef); } + } - $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 (!empty($def['foreign keys'])) { + foreach ($def['foreign keys'] as $keyName => $keyDef) { + $this->appendForeignKeyDef($sql, $keyName, $keyDef); } } - if (count($primary) > 0) { // it really should be... - $sql .= ",\nconstraint primary key (" . implode(',', $primary) . ")"; + // Wrap the CREATE TABLE around the main body chunks... + $statements = array(); + $statements[] = $this->startCreateTable($name, $def) . "\n" . + implode($sql, ",\n") . "\n" . + $this->endCreateTable($name, $def); + + // Multi-value indexes are advisory and for best portability + // should be created as separate statements. + if (!empty($def['indexes'])) { + foreach ($def['indexes'] as $col => $colDef) { + $this->appendCreateIndex($statements, $name, $col, $colDef); + } } - - foreach ($uniques as $u) { - $sql .= ",\nunique index {$name}_{$u}_idx ($u)"; + if (!empty($def['fulltext indexes'])) { + foreach ($def['fulltext indexes'] as $col => $colDef) { + $this->appendCreateFulltextIndex($statements, $name, $col, $colDef); + } } - foreach ($indices as $i) { - $sql .= ",\nindex {$name}_{$i}_idx ($i)"; - } + return $statements; + } - $sql .= "); "; + /** + * Set up a 'create table' SQL statement. + * + * @param string $name table name + * @param array $def table definition + * @param $string + */ + function startCreateTable($name, array $def) + { + return 'CREATE TABLE ' . $this->quoteIdentifier($name) . ' ('; + } - $res = $this->conn->query($sql); + /** + * Close out a 'create table' SQL statement. + * + * @param string $name table name + * @param array $def table definition + * @return string + */ + function endCreateTable($name, array $def) + { + return ')'; + } - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + /** + * Append an SQL fragment with a column definition in a CREATE TABLE statement. + * + * @param array $sql + * @param string $name + * @param array $def + */ + function appendColumnDef(array &$sql, $name, array $def) + { + $sql[] = "$name " . $this->columnSql($def); + } + + /** + * Append an SQL fragment with a constraint definition for a primary + * key in a CREATE TABLE statement. + * + * @param array $sql + * @param array $def + */ + function appendPrimaryKeyDef(array &$sql, array $def) + { + $sql[] = "PRIMARY KEY " . $this->buildIndexList($def); + } + + /** + * Append an SQL fragment with a constraint definition for a unique + * key in a CREATE TABLE statement. + * + * @param array $sql + * @param string $name + * @param array $def + */ + function appendUniqueKeyDef(array &$sql, $name, array $def) + { + $sql[] = "CONSTRAINT $name UNIQUE " . $this->buildIndexList($def); + } + + /** + * Append an SQL fragment with a constraint definition for a foreign + * key in a CREATE TABLE statement. + * + * @param array $sql + * @param string $name + * @param array $def + */ + function appendForeignKeyDef(array &$sql, $name, array $def) + { + if (count($def) != 2) { + throw new Exception("Invalid foreign key def for $name: " . var_export($def, true)); } + list($refTable, $map) = $def; + $srcCols = array_keys($map); + $refCols = array_values($map); + $sql[] = "CONSTRAINT $name FOREIGN KEY " . + $this->buildIndexList($srcCols) . + " REFERENCES " . + $this->quoteIdentifier($refTable) . + " " . + $this->buildIndexList($refCols); + } - return true; + /** + * Append an SQL statement with an index definition for an advisory + * index over one or more columns on a table. + * + * @param array $statements + * @param string $table + * @param string $name + * @param array $def + */ + function appendCreateIndex(array &$statements, $table, $name, array $def) + { + $statements[] = "CREATE INDEX $name ON $table " . $this->buildIndexList($def); + } + + /** + * Append an SQL statement with an index definition for a full-text search + * index over one or more columns on a table. + * + * @param array $statements + * @param string $table + * @param string $name + * @param array $def + */ + function appendCreateFulltextIndex(array &$statements, $table, $name, array $def) + { + throw new Exception("Fulltext index not supported in this database"); + } + + /** + * Append an SQL statement to drop an index from a table. + * + * @param array $statements + * @param string $table + * @param string $name + * @param array $def + */ + function appendDropIndex(array &$statements, $table, $name) + { + $statements[] = "DROP INDEX $name ON " . $this->quoteIdentifier($table); + } + + function buildIndexList(array $def) + { + // @fixme + return '(' . implode(',', array_map(array($this, 'buildIndexItem'), $def)) . ')'; + } + + function buildIndexItem($def) + { + if (is_array($def)) { + list($name, $size) = $def; + return $this->quoteIdentifier($name) . '(' . intval($size) . ')'; + } + return $this->quoteIdentifier($def); } /** @@ -223,7 +375,7 @@ class Schema } if (empty($name)) { - $name = "$table_".implode("_", $columnNames)."_idx"; + $name = "{$table}_".implode("_", $columnNames)."_idx"; } $res = $this->conn->query("ALTER TABLE $table ". @@ -338,46 +490,80 @@ class Schema * 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 + * @param array $def Table definition array * * @return boolean success flag */ - public function ensureTable($tableName, $columns) + public function ensureTable($tableName, $def) { - // XXX: DB engine portability -> toilet + $statements = $this->buildEnsureTable($tableName, $def); + return $this->runSqlSet($statements); + } - try { - $td = $this->getTableDef($tableName); - } catch (Exception $e) { - if (preg_match('/no such table/', $e->getMessage())) { - return $this->createTable($tableName, $columns); - } else { - throw $e; + /** + * Run a given set of SQL commands on the connection in sequence. + * Empty input is ok. + * + * @fixme if multiple statements, wrap in a transaction? + * @param array $statements + * @return boolean success flag + */ + function runSqlSet(array $statements) + { + $ok = true; + foreach ($statements as $sql) { + if (defined('DEBUG_INSTALLER')) { + echo "<tt>" . htmlspecialchars($sql) . "</tt><br/>\n"; + } + $res = $this->conn->query($sql); + + if (PEAR::isError($res)) { + throw new Exception($res->getMessage()); } } + return $ok; + } - $cur = $this->_names($td->columns); - $new = $this->_names($columns); + /** + * Check a table's status, and if needed build a set + * of SQL statements which change it to be consistent + * with the given table definition. + * + * If the table does not yet exist, statements will + * be returned to create the table. If it does exist, + * statements will be returned to 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 array of SQL statements + */ - $toadd = array_diff($new, $cur); - $todrop = array_diff($cur, $new); - $same = array_intersect($new, $cur); - $tomod = array(); + function buildEnsureTable($tableName, array $def) + { + try { + $old = $this->getTableDef($tableName); + } catch (SchemaTableMissingException $e) { + return $this->buildCreateTable($tableName, $def); + } - foreach ($same as $m) { - $curCol = $this->_byName($td->columns, $m); - $newCol = $this->_byName($columns, $m); + // Filter the DB-independent table definition to match the current + // database engine's features and limitations. + $def = $this->validateDef($tableName, $def); + $def = $this->filterDef($def); - if (!$newCol->equals($curCol)) { - $tomod[] = $newCol->name; - } - } + $statements = array(); + $fields = $this->diffArrays($old, $def, 'fields', array($this, 'columnsEqual')); + $uniques = $this->diffArrays($old, $def, 'unique keys'); + $indexes = $this->diffArrays($old, $def, 'indexes'); + $foreign = $this->diffArrays($old, $def, 'foreign keys'); - if (count($toadd) + count($todrop) + count($tomod) == 0) { - // nothing to do - return true; + // Drop any obsolete or modified indexes ahead... + foreach ($indexes['del'] + $indexes['mod'] as $indexName) { + $this->appendDropIndex($statements, $tableName, $indexName); } // For efficiency, we want this all in one @@ -385,31 +571,200 @@ class Schema $phrase = array(); - foreach ($toadd as $columnName) { - $cd = $this->_byName($columns, $columnName); + foreach ($foreign['del'] + $foreign['mod'] as $keyName) { + $this->appendAlterDropForeign($phrase, $keyName); + } + + foreach ($uniques['del'] + $uniques['mod'] as $keyName) { + $this->appendAlterDropUnique($phrase, $keyName); + } - $phrase[] = 'ADD COLUMN ' . $this->_columnSql($cd); + foreach ($fields['add'] as $columnName) { + $this->appendAlterAddColumn($phrase, $columnName, + $def['fields'][$columnName]); } - foreach ($todrop as $columnName) { - $phrase[] = 'DROP COLUMN ' . $columnName; + foreach ($fields['mod'] as $columnName) { + $this->appendAlterModifyColumn($phrase, $columnName, + $old['fields'][$columnName], + $def['fields'][$columnName]); } - foreach ($tomod as $columnName) { - $cd = $this->_byName($columns, $columnName); + foreach ($fields['del'] as $columnName) { + $this->appendAlterDropColumn($phrase, $columnName); + } - $phrase[] = 'MODIFY COLUMN ' . $this->_columnSql($cd); + foreach ($uniques['mod'] + $uniques['add'] as $keyName) { + $this->appendAlterAddUnique($phrase, $keyName, $def['unique keys'][$keyName]); } - $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(', ', $phrase); + foreach ($foreign['mod'] + $foreign['add'] as $keyName) { + $this->appendAlterAddForeign($phrase, $keyName, $def['foreign keys'][$keyName]); + } - $res = $this->conn->query($sql); + $this->appendAlterExtras($phrase, $tableName, $def); - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + if (count($phrase) > 0) { + $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(",\n", $phrase); + $statements[] = $sql; } - return true; + // Now create any indexes... + foreach ($indexes['mod'] + $indexes['add'] as $indexName) { + $this->appendCreateIndex($statements, $tableName, $indexName, $def['indexes'][$indexName]); + } + + return $statements; + } + + function diffArrays($oldDef, $newDef, $section, $compareCallback=null) + { + $old = isset($oldDef[$section]) ? $oldDef[$section] : array(); + $new = isset($newDef[$section]) ? $newDef[$section] : array(); + + $oldKeys = array_keys($old); + $newKeys = array_keys($new); + + $toadd = array_diff($newKeys, $oldKeys); + $todrop = array_diff($oldKeys, $newKeys); + $same = array_intersect($newKeys, $oldKeys); + $tomod = array(); + $tokeep = array(); + + // Find which fields have actually changed definition + // in a way that we need to tweak them for this DB type. + foreach ($same as $name) { + if ($compareCallback) { + $same = call_user_func($compareCallback, $old[$name], $new[$name]); + } else { + $same = ($old[$name] == $new[$name]); + } + if ($same) { + $tokeep[] = $name; + continue; + } + $tomod[] = $name; + } + return array('add' => $toadd, + 'del' => $todrop, + 'mod' => $tomod, + 'keep' => $tokeep, + 'count' => count($toadd) + count($todrop) + count($tomod)); + } + + /** + * 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); + } + + function appendAlterAddUnique(array &$phrase, $keyName, array $def) + { + $sql = array(); + $sql[] = 'ADD'; + $this->appendUniqueKeyDef($sql, $keyName, $def); + $phrase[] = implode(' ', $sql); + } + + function appendAlterAddForeign(array &$phrase, $keyName, array $def) + { + $sql = array(); + $sql[] = 'ADD'; + $this->appendForeignKeyDef($sql, $keyName, $def); + $phrase[] = implode(' ', $sql); + } + + function appendAlterDropUnique(array &$phrase, $keyName) + { + $phrase[] = 'DROP CONSTRAINT ' . $keyName; + } + + function appendAlterDropForeign(array &$phrase, $keyName) + { + $phrase[] = 'DROP FOREIGN KEY ' . $keyName; + } + + function appendAlterExtras(array &$phrase, $tableName, array $def) + { + // no-op + } + + /** + * 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->quoteSmart($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); } /** @@ -421,7 +776,7 @@ class Schema * @return array strings for name values */ - private function _names($cds) + protected function _names($cds) { $names = array(); @@ -442,7 +797,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) { @@ -465,32 +820,194 @@ class Schema * @return string correct SQL for that column */ - private function _columnSql($cd) + function columnSql(array $cd) { - $sql = "{$cd->name} "; + $line = array(); + $line[] = $this->typeAndSize($cd); + + if (isset($cd['default'])) { + $line[] = 'default'; + $line[] = $this->quoteDefaultValue($cd); + } else if (!empty($cd['not null'])) { + // Can't have both not null AND default! + $line[] = 'not null'; + } - if (!empty($cd->size)) { - $sql .= "{$cd->type}({$cd->size}) "; - } else { - $sql .= "{$cd->type} "; + return implode(' ', $line); + } + + /** + * + * @param string $column canonical type name in defs + * @return string native DB type name + */ + function mapType($column) + { + return $column; + } + + function typeAndSize($column) + { + //$type = $this->mapType($column); + $type = $column['type']; + if (isset($column['size'])) { + $type = $column['size'] . $type; } + $lengths = array(); - if (!empty($cd->default)) { - $sql .= "default {$cd->default} "; + if (isset($column['precision'])) { + $lengths[] = $column['precision']; + if (isset($column['scale'])) { + $lengths[] = $column['scale']; + } + } else if (isset($column['length'])) { + $lengths[] = $column['length']; + } + + if ($lengths) { + return $type . '(' . implode(',', $lengths) . ')'; } else { - $sql .= ($cd->nullable) ? "null " : "not null "; + return $type; } + } + + /** + * Convert an old-style set of ColumnDef objects into the current + * Drupal-style schema definition array, for backwards compatibility + * with plugins written for 0.9.x. + * + * @param string $tableName + * @param array $defs: array of ColumnDef objects + * @return array + */ + protected function oldToNew($tableName, array $defs) + { + $table = array(); + $prefixes = array( + 'tiny', + 'small', + 'medium', + 'big', + ); + foreach ($defs as $cd) { + $column = array(); + $column['type'] = $cd->type; + foreach ($prefixes as $prefix) { + if (substr($cd->type, 0, strlen($prefix)) == $prefix) { + $column['type'] = substr($cd->type, strlen($prefix)); + $column['size'] = $prefix; + break; + } + } - if (!empty($cd->auto_increment)) { - $sql .= " auto_increment "; + if ($cd->size) { + if ($cd->type == 'varchar' || $cd->type == 'char') { + $column['length'] = $cd->size; + } + } + if (!$cd->nullable) { + $column['not null'] = true; + } + if ($cd->auto_increment) { + $column['type'] = 'serial'; + } + if ($cd->default) { + $column['default'] = $cd->default; + } + $table['fields'][$cd->name] = $column; + + if ($cd->key == 'PRI') { + // If multiple columns are defined as primary key, + // we'll pile them on in sequence. + if (!isset($table['primary key'])) { + $table['primary key'] = array(); + } + $table['primary key'][] = $cd->name; + } else if ($cd->key == 'MUL') { + // Individual multiple-value indexes are only per-column + // using the old ColumnDef syntax. + $idx = "{$tableName}_{$cd->name}_idx"; + $table['indexes'][$idx] = array($cd->name); + } else if ($cd->key == 'UNI') { + // Individual unique-value indexes are only per-column + // using the old ColumnDef syntax. + $idx = "{$tableName}_{$cd->name}_idx"; + $table['unique keys'][$idx] = array($cd->name); + } } - if (!empty($cd->extra)) { - $sql .= "{$cd->extra} "; + return $table; + } + + /** + * Filter the given table definition array to match features available + * in this database. + * + * This lets us strip out unsupported things like comments, foreign keys, + * or type variants that we wouldn't get back from getTableDef(). + * + * @param array $tableDef + */ + function filterDef(array $tableDef) + { + return $tableDef; + } + + /** + * Validate a table definition array, checking for basic structure. + * + * If necessary, converts from an old-style array of ColumnDef objects. + * + * @param string $tableName + * @param array $def: table definition array + * @return array validated table definition array + * + * @throws Exception on wildly invalid input + */ + function validateDef($tableName, array $def) + { + if (isset($def[0]) && $def[0] instanceof ColumnDef) { + $def = $this->oldToNew($tableName, $def); + } + + // A few quick checks :D + if (!isset($def['fields'])) { + throw new Exception("Invalid table definition for $tableName: no fields."); } - return $sql; + return $def; + } + + function isNumericType($type) + { + $type = strtolower($type); + $known = array('int', 'serial', 'numeric'); + return in_array($type, $known); } + + /** + * Pull info from the query into a fun-fun array of dooooom + * + * @param string $sql + * @return array of arrays + */ + protected function fetchQueryData($sql) + { + $res = $this->conn->query($sql); + if (PEAR::isError($res)) { + throw new Exception($res->getMessage()); + } + + $out = array(); + $row = array(); + while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { + $out[] = $row; + } + $res->free(); + + return $out; + } + } class SchemaTableMissingException extends Exception diff --git a/lib/schemaupdater.php b/lib/schemaupdater.php new file mode 100644 index 000000000..64f7c596d --- /dev/null +++ b/lib/schemaupdater.php @@ -0,0 +1,126 @@ +<?php + +/** + * StatusNet, the distributed open-source microblogging tool + * + * Database schema utilities + * + * PHP version 5 + * + * LICENCE: This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + * + * @category Database + * @package StatusNet + * @author Evan Prodromou <evan@status.net> + * @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 SchemaUpdater +{ + public function __construct($schema) + { + $this->schema = $schema; + $this->checksums = $this->getChecksums(); + } + + /** + * @param string $tableName + * @param array $tableDef + */ + public function register($tableName, array $tableDef) + { + $this->tables[$tableName] = $tableDef; + } + + /** + * Go ping em! + * + * @fixme handle tables that belong on different database servers...? + */ + public function checkSchema() + { + $checksums = $this->checksums; + foreach ($this->tables as $table => $def) { + $checksum = $this->checksum($def); + if (empty($checksums[$table])) { + common_log(LOG_DEBUG, "No previous schema_version for $table: updating to $checksum"); + } else if ($checksums[$table] == $checksum) { + common_log(LOG_DEBUG, "Last schema_version for $table up to date: $checksum"); + continue; + } else { + common_log(LOG_DEBUG, "Last schema_version for $table is {$checksums[$table]}: updating to $checksum"); + } + //$this->conn->query('BEGIN'); + $this->schema->ensureTable($table, $def); + $this->saveChecksum($table, $checksum); + //$this->conn->commit(); + } + } + + /** + * Calculate a checksum for this table definition array. + * + * @param array $def + * @return string + */ + public function checksum(array $def) + { + $flat = serialize($def); + return sha1($flat); + } + + /** + * Pull all known table checksums into an array for easy lookup. + * + * @return array: associative array of table names to checksum strings + */ + protected function getChecksums() + { + $checksums = array(); + + $sv = new Schema_version(); + $sv->find(); + while ($sv->fetch()) { + $checksums[$sv->table_name] = $sv->checksum; + } + + return $checksums; + } + + /** + * Save or update current available checksums. + * + * @param string $table + * @param string $checksum + */ + protected function saveChecksum($table, $checksum) + { + $sv = new Schema_version(); + $sv->table_name = $table; + $sv->checksum = $checksum; + $sv->modified = common_sql_now(); + if (isset($this->checksums[$table])) { + $sv->update(); + } else { + $sv->insert(); + } + $this->checksums[$table] = $checksum; + } +} diff --git a/lib/statusnet.php b/lib/statusnet.php index 2e2359c28..d94d856c9 100644 --- a/lib/statusnet.php +++ b/lib/statusnet.php @@ -245,7 +245,7 @@ class StatusNet * Establish default configuration based on given or default server and path * Sets global $_server, $_path, and $config */ - protected static function initDefaults($server, $path) + public static function initDefaults($server, $path) { global $_server, $_path, $config; |