diff options
| -rw-r--r-- | .gitignore | 1 | ||||
| -rw-r--r-- | classes/Managed_DataObject.php | 155 | ||||
| -rw-r--r-- | classes/Schema_version.php | 22 | ||||
| -rw-r--r-- | classes/statusnet.ini | 8 | ||||
| -rw-r--r-- | db/core.php | 1024 | ||||
| -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 | 220 | ||||
| -rw-r--r-- | lib/mysqlschema.php | 693 | ||||
| -rw-r--r-- | lib/pgsqlschema.php | 635 | ||||
| -rw-r--r-- | lib/schema.php | 684 | ||||
| -rw-r--r-- | lib/schemaupdater.php | 117 | ||||
| -rw-r--r-- | lib/statusnet.php | 2 | ||||
| -rw-r--r-- | plugins/CacheLog/locale/nb/LC_MESSAGES/CacheLog.po | 26 | ||||
| -rw-r--r-- | plugins/OStatus/classes/Ostatus_profile.php | 88 | ||||
| -rw-r--r-- | plugins/Sample/SamplePlugin.php | 17 | ||||
| -rw-r--r-- | scripts/dumpschema.php | 249 |
18 files changed, 2934 insertions, 1281 deletions
diff --git a/.gitignore b/.gitignore index d84e5e742..bf491ec3c 100644 --- a/.gitignore +++ b/.gitignore @@ -26,3 +26,4 @@ lac08.log php.log .DS_Store nbproject +*.mo diff --git a/classes/Managed_DataObject.php b/classes/Managed_DataObject.php new file mode 100644 index 000000000..35d52c512 --- /dev/null +++ b/classes/Managed_DataObject.php @@ -0,0 +1,155 @@ +<?php +/* + * StatusNet - the distributed open-source microblogging tool + * Copyright (C) 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/>. + */ + +/** + * Wrapper for Memcached_DataObject which knows its own schema definition. + * Builds its own damn settings from a schema definition. + * + * @author Brion Vibber <brion@status.net> + */ +abstract class Managed_DataObject extends Memcached_DataObject +{ + /** + * The One True Thingy that must be defined and declared. + */ + public static abstract function schemaDef(); + + /** + * get/set an associative array of table columns + * + * @access public + * @return array (associative) + */ + function table() + { + // Hack for PHP 5.2 not supporting late static binding + //$table = static::schemaDef(); + $table = call_user_func(array(get_class($this), 'schemaDef')); + return array_map(array($this, 'columnBitmap'), $table['fields']); + } + + /** + * get/set an array of table primary keys + * + * Key info is pulled from the table definition array. + * + * @access private + * @return array + */ + function keys() + { + return array_keys($this->keyTypes()); + } + + /** + * Get a sequence key + * + * Returns the first serial column defined in the table, if any. + * + * @access private + * @return array (column,use_native,sequence_name) + */ + + function sequenceKey() + { + $table = self::schemaDef(); + foreach ($table['fields'] as $name => $column) { + if ($column['type'] == 'serial') { + // We have a serial/autoincrement column. + // Declare it to be a native sequence! + return array($name, true, false); + } + } + + // No sequence key on this table. + return array(false, false, false); + } + + /** + * Return key definitions for DB_DataObject and Memcache_DataObject. + * + * DB_DataObject needs to know about keys that the table has; this function + * defines them. + * + * @return array key definitions + */ + + function keyTypes() + { + $keys = array(); + $table = self::schemaDef(); + + if (!empty($table['unique keys'])) { + foreach ($table['unique keys'] as $idx => $fields) { + foreach ($fields as $name) { + $keys[$name] = 'U'; + } + } + } + + if (!empty($table['primary key'])) { + foreach ($table['primary key'] as $name) { + $keys[$name] = 'K'; + } + } + return $keys; + } + + /** + * Build the appropriate DB_DataObject bitfield map for this field. + * + * @param array $column + * @return int + */ + function columnBitmap($column) + { + $type = $column['type']; + + // For quoting style... + $intTypes = array('int', + 'integer', + 'float', + 'serial', + 'numeric'); + if (in_array($type, $intTypes)) { + $style = DB_DATAOBJECT_INT; + } else { + $style = DB_DATAOBJECT_STR; + } + + // Data type formatting style... + $formatStyles = array('blob' => DB_DATAOBJECT_BLOB, + 'text' => DB_DATAOBJECT_TXT, + 'date' => DB_DATAOBJECT_DATE, + 'time' => DB_DATAOBJECT_TIME, + 'datetime' => DB_DATAOBJECT_DATE | DB_DATAOBJECT_TIME, + 'timestamp' => DB_DATAOBJECT_MYSQLTIMESTAMP); + + if (isset($formatStyles[$type])) { + $style |= $formatStyles[$type]; + } + + // Nullable? + if (!empty($column['not null'])) { + $style |= DB_DATAOBJECT_NOTNULL; + } + + return $style; + } +}
\ No newline at end of file diff --git a/classes/Schema_version.php b/classes/Schema_version.php new file mode 100644 index 000000000..6b464c6d1 --- /dev/null +++ b/classes/Schema_version.php @@ -0,0 +1,22 @@ +<?php +/** + * Table Definition for schema_version + */ + +class Schema_version extends Memcached_DataObject +{ + ###START_AUTOCODE + /* the code below is auto generated do not remove the above tag */ + + public $__table = 'schema_version'; // table name + public $table_name; // varchar(64) primary_key not_null + public $checksum; // varchar(64) not_null + public $modified; // datetime() not_null + + /* Static get */ + function staticGet($k,$v=null) + { return Memcached_DataObject::staticGet('Schema_version',$k,$v); } + + /* the code above is auto generated do not remove the tag below */ + ###END_AUTOCODE +} diff --git a/classes/statusnet.ini b/classes/statusnet.ini index 2888bab56..d1d2980fd 100644 --- a/classes/statusnet.ini +++ b/classes/statusnet.ini @@ -509,6 +509,14 @@ replied_id = 1 notice_id = K profile_id = K +[schema_version] +table_name = 130 +checksum = 130 +modified = 384 + +[schema_version__keys] +table_name = K + [session] id = 130 session_data = 34 diff --git a/db/core.php b/db/core.php new file mode 100644 index 000000000..eb90ca85f --- /dev/null +++ b/db/core.php @@ -0,0 +1,1024 @@ +<?php + +/** + * + * Some notes... + * + * Drupal docs don't list a bool type, but it might be nice to use rather than 'tinyint' + * Note however that we use bitfields and things as well in tinyints, and PG's + * "bool" type isn't 100% compatible with 0/1 checks. Just keeping tinyints. :) + * + * decimal <-> numeric + * + * MySQL 'timestamp' columns were formerly used for 'modified' files for their + * auto-updating properties. This didn't play well with changes to cache usage + * in 0.9.x, as we don't know the timestamp value at INSERT time and never + * have a chance to load it up again before caching. + * + * Current code should be setting 'created' and 'modified' fields explicitly; + * this also avoids mismatches between server and client timezone settings. + * + * + * fulltext indexes? + * got one or two things wanting a custom charset setting on a field? + * + * foreign keys are kinda funky... + * those specified in inline syntax (as all in the original .sql) are NEVER ENFORCED on mysql + * those made with an explicit 'foreign key' WITHIN INNODB and IF there's a proper index, do get enforced + * double-check what we've been doing on postgres? + */ + +$schema['profile'] = array( + 'description' => 'local and remote users have profiles', + 'fields' => array( + 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'), + 'nickname' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'nickname or username'), + 'fullname' => array('type' => 'varchar', 'length' => 255, 'description' => 'display name'), + 'profileurl' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL, cached so we dont regenerate'), + 'homepage' => array('type' => 'varchar', 'length' => 255, 'description' => 'identifying URL'), + 'bio' => array('type' => 'text', 'description' => 'descriptive biography'), + 'location' => array('type' => 'varchar', 'length' => 255, 'description' => 'physical location'), + 'lat' => array('type' => 'numeric', 'precision' => 10, 'scale' => 7, 'description' => 'latitude'), + 'lon' => array('type' => 'numeric', 'precision' => 10, 'scale' => 7, 'description' => 'longitude'), + 'location_id' => array('type' => 'int', 'description' => 'location id if possible'), + 'location_ns' => array('type' => 'int', 'description' => 'namespace for location'), + + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('id'), + 'indexes' => array( + 'profile_nickname_idx' => array('nickname'), + ), + 'fulltext indexes' => array( + 'nickname' => array('nickname', 'fullname', 'location', 'bio', 'homepage') + ), +); + +$schema['avatar'] = array( + 'fields' => array( + 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table'), + 'original' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'uploaded by user or generated?'), + 'width' => array('type' => 'int', 'not null' => true, 'description' => 'image width'), + 'height' => array('type' => 'int', 'not null' => true, 'description' => 'image height'), + 'mediatype' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'file type'), + 'filename' => array('type' => 'varchar', 'length' => 255, 'description' => 'local filename, if local'), + 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'avatar location'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('profile_id', 'width', 'height'), + 'unique keys' => array( + 'avatar_url_key' => array('url'), + ), + 'foreign keys' => array( + 'avatar_profile_id_fkey' => array('profile', array('profile_id' => 'id')), + ), + 'indexes' => array( + 'avatar_profile_id_idx' => array('profile_id'), + ), +); + +$schema['sms_carrier'] = array( + 'fields' => array( + 'id' => array('type' => 'int', 'not null' => true, 'description' => 'primary key for SMS carrier'), + 'name' => array('type' => 'varchar', 'length' => 64, 'description' => 'name of the carrier'), + 'email_pattern' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'sprintf pattern for making an email address from a phone number'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('id'), + 'unique keys' => array( + 'sms_carrier_name_key' => array('name'), + ), +); + +$schema['user'] = array( + 'description' => 'local users', + 'fields' => array( + 'id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table'), + 'nickname' => array('type' => 'varchar', 'length' => 64, 'description' => 'nickname or username, duped in profile'), + 'password' => array('type' => 'varchar', 'length' => 255, 'description' => 'salted password, can be null for OpenID users'), + 'email' => array('type' => 'varchar', 'length' => 255, 'description' => 'email address for password recovery etc.'), + 'incomingemail' => array('type' => 'varchar', 'length' => 255, 'description' => 'email address for post-by-email'), + 'emailnotifysub' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Notify by email of subscriptions'), + 'emailnotifyfav' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Notify by email of favorites'), + 'emailnotifynudge' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Notify by email of nudges'), + 'emailnotifymsg' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Notify by email of direct messages'), + 'emailnotifyattn' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Notify by email of @-replies'), + 'emailmicroid' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'whether to publish email microid'), + 'language' => array('type' => 'varchar', 'length' => 50, 'description' => 'preferred language'), + 'timezone' => array('type' => 'varchar', 'length' => 50, 'description' => 'timezone'), + 'emailpost' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Post by email'), + 'sms' => array('type' => 'varchar', 'length' => 64, 'description' => 'sms phone number'), + 'carrier' => array('type' => 'int', 'description' => 'foreign key to sms_carrier'), + 'smsnotify' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'whether to send notices to SMS'), + 'smsreplies' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'whether to send notices to SMS on replies'), + 'smsemail' => array('type' => 'varchar', 'length' => 255, 'description' => 'built from sms and carrier'), + 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier, usually a tag URI'), + 'autosubscribe' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'automatically subscribe to users who subscribe to us'), + 'urlshorteningservice' => array('type' => 'varchar', 'length' => 50, 'default' => 'ur1.ca', 'description' => 'service to use for auto-shortening URLs'), + 'inboxed' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'has an inbox been created for this user?'), + 'design_id' => array('type' => 'int', 'description' => 'id of a design'), + 'viewdesigns' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'whether to view user-provided designs'), + + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('id'), + 'unique keys' => array( + 'user_nickname_key' => array('nickname'), + 'user_email_key' => array('email'), + 'user_incomingemail_key' => array('incomingemail'), + 'user_sms_key' => array('sms'), + 'user_uri_key' => array('uri'), + ), + 'foreign keys' => array( + 'user_id_fkey' => array('profile', array('id' => 'id')), + 'user_carrier_fkey' => array('sms_carrier', array('carrier' => 'id')), + 'user_design_id_fkey' => array('design', array('design_id' => 'id')), + ), + 'indexes' => array( + 'user_smsemail_idx' => array('smsemail'), + ), +); + +$schema['remote_profile'] = array( + 'description' => 'remote people (OMB)', + 'fields' => array( + 'id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table'), + 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier, usually a tag URI'), + 'postnoticeurl' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL we use for posting notices'), + 'updateprofileurl' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL we use for updates to this profile'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('id'), + 'unique keys' => array( + 'remote_profile_uri_key' => array('uri'), + ), + 'foreign keys' => array( + 'remote_profile_id_fkey' => array('profile', array('id' => 'id')), + ), +); + +$schema['subscription'] = array( + 'fields' => array( + 'subscriber' => array('type' => 'int', 'not null' => true, 'description' => 'profile listening'), + 'subscribed' => array('type' => 'int', 'not null' => true, 'description' => 'profile being listened to'), + 'jabber' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'deliver jabber messages'), + 'sms' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'deliver sms messages'), + 'token' => array('type' => 'varchar', 'length' => 255, 'description' => 'authorization token'), + 'secret' => array('type' => 'varchar', 'length' => 255, 'description' => 'token secret'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('subscriber', 'subscribed'), + 'indexes' => array( + 'subscription_subscriber_idx' => array('subscriber', 'created'), + 'subscription_subscribed_idx' => array('subscribed', 'created'), + 'subscription_token_idx' => array('token'), + ), +); + +$schema['notice'] = array( + 'fields' => array( + 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'), + 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'who made the update'), + 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier, usually a tag URI'), + 'content' => array('type' => 'text', 'description' => 'update content'), + 'rendered' => array('type' => 'text', 'description' => 'HTML version of the content'), + 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL of any attachment (image, video, bookmark, whatever)'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + 'reply_to' => array('type' => 'int', 'description' => 'notice replied to (usually a guess)'), + 'is_local' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'notice was generated by a user'), + 'source' => array('type' => 'varchar', 'length' => 32, 'description' => 'source of comment, like "web", "im", or "clientname"'), + 'conversation' => array('type' => 'int', 'description' => 'id of root notice in this conversation'), + 'lat' => array('type' => 'numeric', 'precision' => 10, 'scale' => 7, 'description' => 'latitude'), + 'lon' => array('type' => 'numeric', 'precision' => 10, 'scale' => 7, 'description' => 'longitude'), + 'location_id' => array('type' => 'int', 'description' => 'location id if possible'), + 'location_ns' => array('type' => 'int', 'description' => 'namespace for location'), + 'repeat_of' => array('type' => 'int', 'description' => 'notice this is a repeat of'), + ), + 'primary key' => array('id'), + 'unique keys' => array( + 'notice_uri_key' => array('uri'), + ), + 'foreign keys' => array( + 'notice_profile_id_fkey' => array('profile', array('profile_id' => 'id')), + 'notice_reply_to_fkey' => array('notice', array('reply_to' => 'id')), + 'notice_conversation_fkey' => array('conversation', array('conversation' => 'id')), # note... used to refer to notice.id + 'notice_repeat_of_fkey' => array('notice', array('repeat_of' => 'id')), # @fixme: what about repeats of deleted notices? + ), + 'indexes' => array( + 'notice_profile_id_idx' => array('profile_id', 'created', 'id'), + 'notice_conversation_idx' => array('conversation'), + 'notice_created_idx' => array('created'), + 'notice_replyto_idx' => array('reply_to'), + 'notice_repeatof_idx' => array('repeat_of'), + ), + 'fulltext indexes' => array( + 'content' => array('content'), + ) +); + +$schema['notice_source'] = array( + 'fields' => array( + 'code' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'source code'), + 'name' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'name of the source'), + 'url' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'url to link to'), + 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('code'), +); + +$schema['reply'] = array( + 'fields' => array( + 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice that is the reply'), + 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'profile replied to'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + 'replied_id' => array('type' => 'int', 'description' => 'notice replied to (not used, see notice.reply_to)'), + ), + 'primary key' => array('notice_id', 'profile_id'), + 'foreign keys' => array( + 'reply_notice_id_fkey' => array('notice', array('notice_id' => 'id')), + 'reply_profile_id_fkey' => array('profile', array('profile_id' => 'id')), + ), + 'indexes' => array( + 'reply_notice_id_idx' => array('notice_id'), + 'reply_profile_id_idx' => array('profile_id'), + 'reply_replied_id_idx' => array('replied_id'), + ), +); + +$schema['fave'] = array( + 'fields' => array( + 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice that is the favorite'), + 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who likes this notice'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('notice_id', 'user_id'), + 'foreign keys' => array( + 'fave_notice_id_fkey' => array('notice', array('notice_id' => 'id')), + 'fave_user_id_fkey' => array('profile', array('user_id' => 'id')), // note: formerly referenced notice.id, but we can now record remote users' favorites + ), + 'indexes' => array( + 'fave_notice_id_idx' => array('notice_id'), + 'fave_user_id_idx' => array('user_id', 'modified'), + 'fave_modified_idx' => array('modified'), + ), +); + +/* tables for OAuth */ + +$schema['consumer'] = array( + 'description' => 'OAuth consumer record', + 'fields' => array( + 'consumer_key' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'unique identifier, root URL'), + 'consumer_secret' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'secret value'), + 'seed' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'seed for new tokens by this consumer'), + + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('consumer_key'), +); + +$schema['token'] = array( + 'description' => 'OAuth token record', + 'fields' => array( + 'consumer_key' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'unique identifier, root URL'), + 'tok' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'identifying value'), + 'secret' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'secret value'), + 'type' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 0, 'description' => 'request or access'), + 'state' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'for requests, 0 = initial, 1 = authorized, 2 = used'), + 'verifier' => array('type' => 'varchar', 'length' => 255, 'description' => 'verifier string for OAuth 1.0a'), + 'verified_callback' => array('type' => 'varchar', 'length' => 255, 'description' => 'verified callback URL for OAuth 1.0a'), + + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('consumer_key', 'tok'), + 'foreign keys' => array( + 'token_consumer_key_fkey' => array('consumer', array('consumer_key'=> 'consumer_key')), + ), +); + +$schema['nonce'] = array( + 'description' => 'OAuth nonce record', + 'fields' => array( + 'consumer_key' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'unique identifier, root URL'), + 'tok' => array('type' => 'char', 'length' => 32, 'description' => 'buggy old value, ignored'), + 'nonce' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'nonce'), + 'ts' => array('type' => 'datetime', 'not null' => true, 'description' => 'timestamp sent'), + + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('consumer_key', 'ts', 'nonce'), +); + +$schema['oauth_application'] = array( + 'description' => 'OAuth application registration record', + 'fields' => array( + 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'), + 'owner' => array('type' => 'int', 'not null' => true, 'description' => 'owner of the application'), + 'consumer_key' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'application consumer key'), + 'name' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'name of the application'), + 'description' => array('type' => 'varchar', 'length' => 255, 'description' => 'description of the application'), + 'icon' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'application icon'), + 'source_url' => array('type' => 'varchar', 'length' => 255, 'description' => 'application homepage - used for source link'), + 'organization' => array('type' => 'varchar', 'length' => 255, 'description' => 'name of the organization running the application'), + 'homepage' => array('type' => 'varchar', 'length' => 255, 'description' => 'homepage for the organization'), + 'callback_url' => array('type' => 'varchar', 'length' => 255, 'description' => 'url to redirect to after authentication'), + 'type' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'type of app, 1 = browser, 2 = desktop'), + 'access_type' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'default access type, bit 1 = read, bit 2 = write'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('id'), + 'unique keys' => array( + 'oauth_application_name_key' => array('name'), // in the long run, we should perhaps not force these unique, and use another source id + ), + 'foreign keys' => array( + 'oauth_application_owner_fkey' => array('profile', array('owner' => 'id')), // Are remote users allowed to create oauth application records? + 'oauth_application_consumer_key_fkey' => array('consumer', array('consumer_key' => 'consumer_key')), + ), +); + +$schema['oauth_application_user'] = array( + 'fields' => array( + 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'user of the application'), + 'application_id' => array('type' => 'int', 'not null' => true, 'description' => 'id of the application'), + 'access_type' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'access type, bit 1 = read, bit 2 = write'), + 'token' => array('type' => 'varchar', 'length' => 255, 'description' => 'request or access token'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('profile_id', 'application_id'), + 'foreign keys' => array( + 'oauth_application_user_profile_id_fkey' => array('profile', array('profile_id' => 'id')), + 'oauth_application_user_application_id_fkey' => array('oauth_application', array('application_id' => 'id')), + ), +); + +/* These are used by JanRain OpenID library */ + +$schema['oid_associations'] = array( + 'fields' => array( + 'server_url' => array('type' => 'blob', 'not null' => true), + 'handle' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'default' => ''), // character set latin1, + 'secret' => array('type' => 'blob'), + 'issued' => array('type' => 'int'), + 'lifetime' => array('type' => 'int'), + 'assoc_type' => array('type' => 'varchar', 'length' => 64), + ), + 'primary key' => array(array('server_url', 255), 'handle'), +); + +$schema['oid_nonces'] = array( + 'fields' => array( + 'server_url' => array('type' => 'varchar', 'length' => 2047), + 'timestamp' => array('type' => 'int'), + 'salt' => array('type' => 'char', 'length' => 40), + ), + 'unique keys' => array( + 'oid_nonces_server_url_timestamp_salt_key' => array(array('server_url', 255), 'timestamp', 'salt'), + ), +); + +$schema['confirm_address'] = array( + 'fields' => array( + 'code' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'good random code'), + 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who requested confirmation'), + 'address' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'address (email, xmpp, SMS, etc.)'), + 'address_extra' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'carrier ID, for SMS'), + 'address_type' => array('type' => 'varchar', 'length' => 8, 'not null' => true, 'description' => 'address type ("email", "xmpp", "sms")'), + 'claimed' => array('type' => 'datetime', 'description' => 'date this was claimed for queueing'), + 'sent' => array('type' => 'datetime', 'description' => 'date this was sent for queueing'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('code'), + 'foreign keys' => array( + 'confirm_address_user_id_fkey' => array('user', array('user_id' => 'id')), + ), +); + +$schema['remember_me'] = array( + 'fields' => array( + 'code' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'good random code'), + 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who is logged in'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('code'), + 'foreign keys' => array( + 'remember_me_user_id_fkey' => array('user', array('user_id' => 'id')), + ), +); + +$schema['queue_item'] = array( + 'fields' => array( + 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'), + 'frame' => array('type' => 'blob', 'not null' => true, 'description' => 'data: object reference or opaque string'), + 'transport' => array('type' => 'varchar', 'length' => 8, 'not null' => true, 'description' => 'queue for what? "email", "xmpp", "sms", "irc", ...'), // @fixme 8 chars is too short; bump up. + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'claimed' => array('type' => 'datetime', 'description' => 'date this item was claimed'), + ), + 'primary key' => array('id'), + 'indexes' => array( + 'queue_item_created_idx' => array('created'), + ), +); + +$schema['notice_tag'] = array( + 'description' => 'Hash tags', + 'fields' => array( + 'tag' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'hash tag associated with this notice'), + 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice tagged'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + ), + 'primary key' => array('tag', 'notice_id'), + 'foreign keys' => array( + 'notice_tag_notice_id_fkey' => array('notice', array('notice_id' => 'id')), + ), + 'indexes' => array( + 'notice_tag_created_idx' => array('created'), + 'notice_tag_notice_id_idx' => array('notice_id'), + ), +); + +/* Synching with foreign services */ + +$schema['foreign_service'] = array( + 'fields' => array( + 'id' => array('type' => 'int', 'not null' => true, 'description' => 'numeric key for service'), + 'name' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'name of the service'), + 'description' => array('type' => 'varchar', 'length' => 255, 'description' => 'description'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('id'), + 'unique keys' => array( + 'foreign_service_name_key' => array('name'), + ), +); + +$schema['foreign_user'] = array( + 'fields' => array( + 'id' => array('type' => 'int', 'size' => 'big', 'not null' => true, 'description' => 'unique numeric key on foreign service'), + 'service' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to service'), + 'uri' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'identifying URI'), + 'nickname' => array('type' => 'varchar', 'length' => 255, 'description' => 'nickname on foreign service'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('id', 'service'), + 'foreign keys' => array( + 'foreign_user_service_fkey' => array('foreign_service', array('service' => 'id')), + ), + 'unique keys' => array( + 'foreign_user_uri_key' => array('uri'), + ), +); + +$schema['foreign_link'] = array( + 'fields' => array( + 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'link to user on this system, if exists'), + 'foreign_id' => array('type' => 'int', 'size' => 'big', 'unsigned' => true, 'not null' => true, 'description' => 'link to user on foreign service, if exists'), + 'service' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to service'), + 'credentials' => array('type' => 'varchar', 'length' => 255, 'description' => 'authc credentials, typically a password'), + 'noticesync' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 1, 'description' => 'notice synchronization, bit 1 = sync outgoing, bit 2 = sync incoming, bit 3 = filter local replies'), + 'friendsync' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 2, 'description' => 'friend synchronization, bit 1 = sync outgoing, bit 2 = sync incoming'), + 'profilesync' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 1, 'description' => 'profile synchronization, bit 1 = sync outgoing, bit 2 = sync incoming'), + 'last_noticesync' => array('type' => 'datetime', 'description' => 'last time notices were imported'), + 'last_friendsync' => array('type' => 'datetime', 'description' => 'last time friends were imported'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('user_id', 'foreign_id', 'service'), + 'foreign keys' => array( + 'foreign_link_user_id_fkey' => array('user', array('user_id' => 'id')), + 'foreign_link_foreign_id_fkey' => array('foreign_user', array('foreign_id' => 'id', 'service' => 'service')), + 'foreign_link_service_fkey' => array('foreign_service', array('service' => 'id')), + ), + 'indexes' => array( + 'foreign_user_user_id_idx' => array('user_id'), + ), +); + +$schema['foreign_subscription'] = array( + 'fields' => array( + 'service' => array('type' => 'int', 'not null' => true, 'description' => 'service where relationship happens'), + 'subscriber' => array('type' => 'int', 'size' => 'big', 'not null' => true, 'description' => 'subscriber on foreign service'), + 'subscribed' => array('type' => 'int', 'size' => 'big', 'not null' => true, 'description' => 'subscribed user'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + ), + 'primary key' => array('service', 'subscriber', 'subscribed'), + 'foreign keys' => array( + 'foreign_subscription_service_fkey' => array('foreign_service', array('service' => 'id')), + 'foreign_subscription_subscriber_fkey' => array('foreign_user', array('subscriber' => 'id', 'service' => 'service')), + 'foreign_subscription_subscribed_fkey' => array('foreign_user', array('subscribed' => 'id', 'service' => 'service')), + ), + 'indexes' => array( + 'foreign_subscription_subscriber_idx' => array('service', 'subscriber'), + 'foreign_subscription_subscribed_idx' => array('service', 'subscribed'), + ), +); + +$schema['invitation'] = array( + 'fields' => array( + 'code' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'random code for an invitation'), + 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'who sent the invitation'), + 'address' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'invitation sent to'), + 'address_type' => array('type' => 'varchar', 'length' => 8, 'not null' => true, 'description' => 'address type ("email", "xmpp", "sms")'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + ), + 'primary key' => array('code'), + 'foreign keys' => array( + 'invitation_user_id_fkey' => array('user', array('user_id' => 'id')), + ), + 'indexes' => array( + 'invitation_address_idx' => array('address', 'address_type'), + 'invitation_user_id_idx' => array('user_id'), + ), +); + +$schema['message'] = array( + 'fields' => array( + 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'), + 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier'), + 'from_profile' => array('type' => 'int', 'not null' => true, 'description' => 'who the message is from'), + 'to_profile' => array('type' => 'int', 'not null' => true, 'description' => 'who the message is to'), + 'content' => array('type' => 'text', 'description' => 'message content'), + 'rendered' => array('type' => 'text', 'description' => 'HTML version of the content'), + 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL of any attachment (image, video, bookmark, whatever)'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + 'source' => array('type' => 'varchar', 'length' => 32, 'description' => 'source of comment, like "web", "im", or "clientname"'), + ), + 'primary key' => array('id'), + 'unique keys' => array( + 'message_uri_key' => array('uri'), + ), + 'foreign keys' => array( + 'message_from_profile_fkey' => array('profile', array('from_profile' => 'id')), + 'message_to_profile_fkey' => array('profile', array('to_profile' => 'id')), + ), + 'indexes' => array( + // @fixme these are really terrible indexes, since you can only sort on one of them at a time. + // looks like we really need a (to_profile, created) for inbox and a (from_profile, created) for outbox + 'message_from_idx' => array('from_profile'), + 'message_to_idx' => array('to_profile'), + 'message_created_idx' => array('created'), + ), +); + +$schema['notice_inbox'] = array( + 'description' => 'Obsolete; old entries here are converted to packed entries in the inbox table since 0.9', + 'fields' => array( + 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user receiving the message'), + 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice received'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice was created'), + 'source' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'reason it is in the inbox, 1=subscription'), + ), + 'primary key' => array('user_id', 'notice_id'), + 'foreign keys' => array( + 'notice_inbox_user_id_fkey' => array('user', array('user_id' => 'id')), + 'notice_inbox_notice_id_fkey' => array('notice', array('notice_id' => 'id')), + ), + 'indexes' => array( + 'notice_inbox_notice_id_idx' => array('notice_id'), + ), +); + +$schema['profile_tag'] = array( + 'fields' => array( + 'tagger' => array('type' => 'int', 'not null' => true, 'description' => 'user making the tag'), + 'tagged' => array('type' => 'int', 'not null' => true, 'description' => 'profile tagged'), + 'tag' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'hash tag associated with this notice'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the tag was added'), + ), + 'primary key' => array('tagger', 'tagged', 'tag'), + 'foreign keys' => array( + 'profile_tag_tagger_fkey' => array('user', array('tagger' => 'id')), + 'profile_tag_tagged_fkey' => array('profile', array('tagged' => 'id')), + ), + 'indexes' => array( + 'profile_tag_modified_idx' => array('modified'), + 'profile_tag_tagger_tag_idx' => array('tagger', 'tag'), + 'profile_tag_tagged_idx' => array('tagged'), + ), +); + +$schema['profile_block'] = array( + 'fields' => array( + 'blocker' => array('type' => 'int', 'not null' => true, 'description' => 'user making the block'), + 'blocked' => array('type' => 'int', 'not null' => true, 'description' => 'profile that is blocked'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date of blocking'), + ), + 'foreign keys' => array( + 'profile_block_blocker_fkey' => array('user', array('blocker' => 'id')), + 'profile_block_blocked_fkey' => array('profile', array('blocked' => 'id')), + ), + 'primary key' => array('blocker', 'blocked'), +); + +$schema['user_group'] = array( + 'fields' => array( + 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'), + + 'nickname' => array('type' => 'varchar', 'length' => 64, 'description' => 'nickname for addressing'), + 'fullname' => array('type' => 'varchar', 'length' => 255, 'description' => 'display name'), + 'homepage' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL, cached so we dont regenerate'), + 'description' => array('type' => 'text', 'description' => 'group description'), + 'location' => array('type' => 'varchar', 'length' => 255, 'description' => 'related physical location, if any'), + + 'original_logo' => array('type' => 'varchar', 'length' => 255, 'description' => 'original size logo'), + 'homepage_logo' => array('type' => 'varchar', 'length' => 255, 'description' => 'homepage (profile) size logo'), + 'stream_logo' => array('type' => 'varchar', 'length' => 255, 'description' => 'stream-sized logo'), + 'mini_logo' => array('type' => 'varchar', 'length' => 255, 'description' => 'mini logo'), + 'design_id' => array('type' => 'int', 'description' => 'id of a design'), + + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + + 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universal identifier'), + 'mainpage' => array('type' => 'varchar', 'length' => 255, 'description' => 'page for group info to link to'), + ), + 'primary key' => array('id'), + 'unique keys' => array( + 'user_group_uri_key' => array('uri'), + ), + 'foreign keys' => array( + 'user_group_design_id_fkey' => array('design', array('design_id' => 'id')), + ), + 'indexes' => array( + 'user_group_nickname_idx' => array('nickname'), + ), +); + +$schema['group_member'] = array( + 'fields' => array( + 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to user_group'), + 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table'), + 'is_admin' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'is this user an admin?'), + + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('group_id', 'profile_id'), + 'foreign keys' => array( + 'group_member_group_id_fkey' => array('user_group', array('group_id' => 'id')), + 'group_member_profile_id_fkey' => array('profile', array('profile_id' => 'id')), + ), + 'indexes' => array( + // @fixme probably we want a (profile_id, created) index here? + 'group_member_profile_id_idx' => array('profile_id'), + 'group_member_created_idx' => array('created'), + ), +); + +$schema['related_group'] = array( + // @fixme description for related_group? + 'fields' => array( + 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to user_group'), + 'related_group_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to user_group'), + + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + ), + 'primary key' => array('group_id', 'related_group_id'), + 'foreign keys' => array( + 'related_group_group_id_fkey' => array('user_group', array('group_id' => 'id')), + 'related_group_related_group_id_fkey' => array('user_group', array('related_group_id' => 'id')), + ), +); + +$schema['group_inbox'] = array( + 'description' => 'Many-many table listing notices posted to a given group, or which groups a given notice was posted to.', + 'fields' => array( + 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'group receiving the message'), + 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice received'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice was created'), + ), + 'primary key' => array('group_id', 'notice_id'), + 'foreign keys' => array( + 'group_inbox_group_id_fkey' => array('user_group', array('group_id' => 'id')), + 'group_inbox_notice_id_fkey' => array('notice', array('notice_id' => 'id')), + ), + 'indexes' => array( + 'group_inbox_created_idx' => array('created'), + 'group_inbox_notice_id_idx' => array('notice_id'), + ), +); + +$schema['file'] = array( + 'fields' => array( + 'id' => array('type' => 'serial', 'not null' => true), + 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'destination URL after following redirections'), + 'mimetype' => array('type' => 'varchar', 'length' => 50, 'description' => 'mime type of resource'), + 'size' => array('type' => 'int', 'description' => 'size of resource when available'), + 'title' => array('type' => 'varchar', 'length' => 255, 'description' => 'title of resource when available'), + 'date' => array('type' => 'int', 'description' => 'date of resource according to http query'), + 'protected' => array('type' => 'int', 'description' => 'true when URL is private (needs login)'), + 'filename' => array('type' => 'varchar', 'length' => 255, 'description' => 'if a local file, name of the file'), + + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('id'), + 'unique keys' => array( + 'file_url_key' => array('url'), + ), +); + +$schema['file_oembed'] = array( + 'fields' => array( + 'file_id' => array('type' => 'int', 'not null' => true, 'description' => 'oEmbed for that URL/file'), + 'version' => array('type' => 'varchar', 'length' => 20, 'description' => 'oEmbed spec. version'), + 'type' => array('type' => 'varchar', 'length' => 20, 'description' => 'oEmbed type: photo, video, link, rich'), + 'mimetype' => array('type' => 'varchar', 'length' => 50, 'description' => 'mime type of resource'), + 'provider' => array('type' => 'varchar', 'length' => 50, 'description' => 'name of this oEmbed provider'), + 'provider_url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL of this oEmbed provider'), + 'width' => array('type' => 'int', 'description' => 'width of oEmbed resource when available'), + 'height' => array('type' => 'int', 'description' => 'height of oEmbed resource when available'), + 'html' => array('type' => 'text', 'description' => 'html representation of this oEmbed resource when applicable'), + 'title' => array('type' => 'varchar', 'length' => 255, 'description' => 'title of oEmbed resource when available'), + 'author_name' => array('type' => 'varchar', 'length' => 50, 'description' => 'author name for this oEmbed resource'), + 'author_url' => array('type' => 'varchar', 'length' => 255, 'description' => 'author URL for this oEmbed resource'), + 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL for this oEmbed resource when applicable (photo, link)'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('file_id'), + 'foreign keys' => array( + 'file_oembed_file_id_fkey' => array('file', array('file_id' => 'id')), + ), +); + +$schema['file_redirection'] = array( + 'fields' => array( + 'url' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'short URL (or any other kind of redirect) for file (id)'), + 'file_id' => array('type' => 'int', 'description' => 'short URL for what URL/file'), + 'redirections' => array('type' => 'int', 'description' => 'redirect count'), + 'httpcode' => array('type' => 'int', 'description' => 'HTTP status code (20x, 30x, etc.)'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('url'), + 'foreign keys' => array( + 'file_redirection_file_id_fkey' => array('file' => array('file_id' => 'id')), + ), +); + +$schema['file_thumbnail'] = array( + 'fields' => array( + 'file_id' => array('type' => 'int', 'not null' => true, 'description' => 'thumbnail for what URL/file'), + 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL of thumbnail'), + 'width' => array('type' => 'int', 'description' => 'width of thumbnail'), + 'height' => array('type' => 'int', 'description' => 'height of thumbnail'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('file_id'), + 'foreign keys' => array( + 'file_thumbnail_file_id_fkey' => array('file', array('file_id' => 'id')), + ), + 'unique keys' => array( + 'file_thumbnail_url_key' => array('url'), + ), +); + +$schema['file_to_post'] = array( + 'fields' => array( + 'file_id' => array('type' => 'int', 'not null' => true, 'description' => 'id of URL/file'), + 'post_id' => array('type' => 'int', 'not null' => true, 'description' => 'id of the notice it belongs to'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('file_id', 'post_id'), + 'foreign keys' => array( + 'file_to_post_file_id_fkey' => array('file', array('file_id' => 'id')), + 'file_to_post_post_id_fkey' => array('notice', array('post_id' => 'id')), + ), + 'indexes' => array( + 'post_id_idx' => array('post_id'), + ), +); + +$schema['design'] = array( + 'fields' => array( + 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'design ID'), + 'backgroundcolor' => array('type' => 'int', 'description' => 'main background color'), + 'contentcolor' => array('type' => 'int', 'description' => 'content area background color'), + 'sidebarcolor' => array('type' => 'int', 'description' => 'sidebar background color'), + 'textcolor' => array('type' => 'int', 'description' => 'text color'), + 'linkcolor' => array('type' => 'int', 'description' => 'link color'), + 'backgroundimage' => array('type' => 'varchar', 'length' => 255, 'description' => 'background image, if any'), + 'disposition' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'bit 1 = hide background image, bit 2 = display background image, bit 4 = tile background image'), + ), + 'primary key' => array('id'), +); + +$schema['group_block'] = array( + 'fields' => array( + 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'group profile is blocked from'), + 'blocked' => array('type' => 'int', 'not null' => true, 'description' => 'profile that is blocked'), + 'blocker' => array('type' => 'int', 'not null' => true, 'description' => 'user making the block'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date of blocking'), + ), + 'primary key' => array('group_id', 'blocked'), + 'foreign keys' => array( + 'group_block_group_id_fkey' => array('user_group', array('group_id' => 'id')), + 'group_block_blocked_fkey' => array('profile', array('blocked' => 'id')), + 'group_block_blocker_fkey' => array('user', array('blocker' => 'id')), + ), +); + +$schema['group_alias'] = array( + 'fields' => array( + 'alias' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'additional nickname for the group'), + 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'group profile is blocked from'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date alias was created'), + ), + 'primary key' => array('alias'), + 'foreign keys' => array( + 'group_alias_group_id_fkey' => array('user_group', array('group_id' => 'id')), + ), + 'indexes' => array( + 'group_alias_group_id_idx' => array('group_id'), + ), +); + +$schema['session'] = array( + 'fields' => array( + 'id' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'session ID'), + 'session_data' => array('type' => 'text', 'description' => 'session data'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('id'), + 'indexes' => array( + 'session_modified_idx' => array('modified'), + ), +); + +$schema['deleted_notice'] = array( + 'fields' => array( + 'id' => array('type' => 'int', 'not null' => true, 'description' => 'identity of notice'), + 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'author of the notice'), + 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier, usually a tag URI'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice record was created'), + 'deleted' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice record was created'), + ), + 'primary key' => array('id'), + 'unique keys' => array( + 'deleted_notice_uri_key' => array('uri'), + ), + 'indexes' => array( + 'deleted_notice_profile_id_idx' => array('profile_id'), + ), +); + +$schema['config'] = array( + 'fields' => array( + 'section' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'default' => '', 'description' => 'configuration section'), + 'setting' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'default' => '', 'description' => 'configuration setting'), + 'value' => array('type' => 'varchar', 'length' => 255, 'description' => 'configuration value'), + ), + 'primary key' => array('section', 'setting'), +); + +$schema['profile_role'] = array( + 'fields' => array( + 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'account having the role'), + 'role' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'string representing the role'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the role was granted'), + ), + 'primary key' => array('profile_id', 'role'), + 'foreign keys' => array( + 'profile_role_profile_id_fkey' => array('profile', array('profile_id' => 'id')), + ), +); + +$schema['location_namespace'] = array( + 'fields' => array( + 'id' => array('type' => 'int', 'not null' => true, 'description' => 'identity for this namespace'), + 'description' => array('type' => 'varchar', 'length' => 255, 'description' => 'description of the namespace'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('id'), +); + +$schema['login_token'] = array( + 'fields' => array( + 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user owning this token'), + 'token' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'token useable for logging in'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('user_id'), + 'foreign keys' => array( + 'login_token_user_id_fkey' => array('user', array('user_id' => 'id')), + ), +); + +$schema['user_location_prefs'] = array( + 'fields' => array( + 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who has the preference'), + 'share_location' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Whether to share location data'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('user_id'), + 'foreign keys' => array( + 'user_location_prefs_user_id_fkey' => array('user', array('user_id' => 'id')), + ), +); + +$schema['inbox'] = array( + 'fields' => array( + 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user receiving the notice'), + 'notice_ids' => array('type' => 'blob', 'description' => 'packed list of notice ids'), + ), + 'primary key' => array('user_id'), + 'foreign keys' => array( + 'inbox_user_id_fkey' => array('user', array('user_id' => 'id')), + ), +); + +// @fixme possibly swap this for a more general prefs table? +$schema['user_im_prefs'] = array( + 'fields' => array( + 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user'), + 'screenname' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'screenname on this service'), + 'transport' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'transport (ex xmpp, aim)'), + 'notify' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 0, 'description' => 'Notify when a new notice is sent'), + 'replies' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 0, 'description' => 'Send replies from people not subscribed to'), + 'microid' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 1, 'description' => 'Publish a MicroID'), + 'updatefrompresence' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 0, 'description' => 'Send replies from people not subscribed to.'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('user_id', 'transport'), + 'unique keys' => array( + 'transport_screenname_key' => array('transport', 'screenname'), + ), + 'foreign keys' => array( + 'user_im_prefs_user_id_fkey' => array('user', array('user_id' => 'id')), + ), +); + +$schema['conversation'] = array( + 'fields' => array( + 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'), + 'uri' => array('type' => 'varchar', 'length' => 225, 'description' => 'URI of the conversation'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('id'), + 'unique keys' => array( + 'conversation_uri_key' => array('uri'), + ), +); + +$schema['local_group'] = array( + 'description' => 'Record for a user group on the local site, with some additional info not in user_group', + 'fields' => array( + 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'group represented'), + 'nickname' => array('type' => 'varchar', 'length' => 64, 'description' => 'group represented'), + + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('group_id'), + 'foreign keys' => array( + 'local_group_group_id_fkey' => array('user_group', array('group_id' => 'id')), + ), + 'unique keys' => array( + 'local_group_nickname_key' => array('nickname'), + ), +); + +$schema['user_urlshortener_prefs'] = array( + 'fields' => array( + 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user'), + 'urlshorteningservice' => array('type' => 'varchar', 'length' => 50, 'default' => 'ur1.ca', 'description' => 'service to use for auto-shortening URLs'), + 'maxurllength' => array('type' => 'int', 'not null' => true, 'description' => 'urls greater than this length will be shortened, 0 = always, null = never'), + 'maxnoticelength' => array('type' => 'int', 'not null' => true, 'description' => 'notices with content greater than this value will have all urls shortened, 0 = always, null = never'), + + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('user_id'), + 'foreign keys' => array( + 'user_urlshortener_prefs_user_id_fkey' => array('user', array('user_id' => 'id')), + ), +); + +$schema['schema_version'] = array( + 'description' => 'To avoid checking database structure all the time, we store a checksum of the expected schema info for each table here. If it has not changed since the last time we checked the table, we can leave it as is.', + 'fields' => array( + 'table_name' => array('type' => 'varchar', 'length' => '64', 'not null' => true, 'description' => 'Table name'), + 'checksum' => array('type' => 'varchar', 'length' => '64', 'not null' => true, 'description' => 'Checksum of schema array; a mismatch indicates we should check the table more thoroughly.'), + 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('table_name'), +); 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 a1dac7b5e..26b872da4 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..441f72660 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,96 @@ 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); } + } - $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 +427,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,24 +441,9 @@ 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) { + $res = $conn->execute($stmt); + if (DB::isError($res)) { + $error = $result->getMessage(); $this->updateStatus("ERROR ($error) for SQL '$stmt'"); return $res; } @@ -559,10 +507,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..937c20ea8 100644 --- a/lib/mysqlschema.php +++ b/lib/mysqlschema.php @@ -72,72 +72,122 @@ 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']; + + 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) { + $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']; + } - while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { + $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 = new ColumnDef(); + if ($row['CHARACTER_SET_NAME'] !== null) { + // @fixme check against defaults? + //$def['charset'] = $row['CHARACTER_SET_NAME']; + //$def['collate'] = $row['COLLATION_NAME']; + } - $cd->name = $row['COLUMN_NAME']; + $def['fields'][$name] = $field; + } - $packed = $row['COLUMN_TYPE']; + 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']; - if (preg_match('/^(\w+)\((\d+)\)$/', $packed, $match)) { - $cd->type = $match[1]; - $cd->size = $match[2]; - } else { - $cd->type = $packed; + 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; } - $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; + foreach ($keyTypes as $name => $type) { + if ($type == 'primary key') { + // there can be only one + $def[$type] = $keys[$name]; + } else { + $def[$type][$name] = $keys[$name]; + } } - - // mysql extensions -- not (yet) used by base class - $cd->charset = $row['CHARACTER_SET_NAME']; - $cd->collate = $row['COLLATION_NAME']; - - $td->columns[] = $cd; } - $res->free(); - - return $td; + return $def; } /** @@ -150,127 +200,58 @@ 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. - * - * Throws an exception if the table is not found. - * - * @param string $table name of the table - * @param string $column name of the column + * Pull some INFORMATION.SCHEMA data for the given table. * - * @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. - * - * @param string $name Name of the table - * @param array $columns Array of ColumnDef objects - * for new table. + * Pull 'SHOW INDEX' data for the given table. * - * @return boolean success flag + * @param string $table + * @return array of arrays */ - - public function createTable($name, $columns) + function fetchIndexInfo($table) { - $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; + $query = "SHOW INDEX FROM `%s`"; + $sql = sprintf($query, $table); + return $this->fetchQueryData($sql); } /** - * Look over a list of column definitions and list up which - * indices will be present + * Close out a 'create table' SQL statement. + * + * @param string $name + * @param array $def + * @return string; + * + * @fixme ENGINE may need to be set differently in some cases, + * such as to support fulltext index. */ - private function _indexList(array $columns) + function endCreateTable($name, array $def) { - $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; + return ") ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin"; } /** @@ -289,344 +270,45 @@ class MysqlSchema extends Schema return "{$tableName}_{$columnName}_idx"; } - /** - * 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 + * MySQL doesn't take 'DROP CONSTRAINT', need to treat unique keys as + * if they were indexes here. * - * @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) { - // 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'; + $phrase[] = 'ENGINE=InnoDB'; } 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; - } - - // For efficiency, we want this all in one - // query, instead of using our methods. - - $phrase = array(); - - foreach ($dropIndex as $indexSql) { - $phrase[] = $indexSql; + $phrase[] = 'DEFAULT CHARSET=utf8'; + $phrase[] = 'COLLATE=utf8_bin'; } - - 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 +323,86 @@ 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)) { + return parent::typeAndSize($column) . ' CHARSET utf8'; + } 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..e4b7f416c 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,195 @@ 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) . ")"; + // Multi-value indexes are advisory and for best portability + // should be created as separate statements. + $statements = array(); + $statements[] = $this->startCreateTable($name, $def) . "\n" . + implode($sql, ",\n") . "\n" . + $this->endCreateTable($name, $def); + 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)"; - } + return $statements; + } - foreach ($indices as $i) { - $sql .= ",\nindex {$name}_{$i}_idx ($i)"; - } + /** + * 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) . ' ('; + } - $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 ')'; + } - $res = $this->conn->query($sql); + /** + * 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); + } - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + /** + * 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 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 +354,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 +469,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 +550,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); + } - $phrase[] = 'ADD COLUMN ' . $this->_columnSql($cd); + foreach ($uniques['del'] + $uniques['mod'] as $keyName) { + $this->appendAlterDropUnique($phrase, $keyName); } - foreach ($todrop as $columnName) { - $phrase[] = 'DROP COLUMN ' . $columnName; + foreach ($fields['add'] as $columnName) { + $this->appendAlterAddColumn($phrase, $columnName, + $def['fields'][$columnName]); } - foreach ($tomod as $columnName) { - $cd = $this->_byName($columns, $columnName); + foreach ($fields['mod'] as $columnName) { + $this->appendAlterModifyColumn($phrase, $columnName, + $old['fields'][$columnName], + $def['fields'][$columnName]); + } - $phrase[] = 'MODIFY COLUMN ' . $this->_columnSql($cd); + foreach ($fields['del'] as $columnName) { + $this->appendAlterDropColumn($phrase, $columnName); } - $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(', ', $phrase); + foreach ($uniques['mod'] + $uniques['add'] as $keyName) { + $this->appendAlterAddUnique($phrase, $keyName, $def['unique keys'][$keyName]); + } - $res = $this->conn->query($sql); + foreach ($foreign['mod'] + $foreign['add'] as $keyName) { + $this->appendAlterAddForeign($phrase, $keyName, $def['foreign keys'][$keyName]); + } - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + $this->appendAlterExtras($phrase, $tableName); + + 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) + { + // 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 +755,7 @@ class Schema * @return array strings for name values */ - private function _names($cds) + protected function _names($cds) { $names = array(); @@ -442,7 +776,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 +799,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; } + } - if (!empty($cd->auto_increment)) { - $sql .= " auto_increment "; + /** + * 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 ($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 (count($def) && $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 $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()); } - return $sql; + $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..b872b0d57 --- /dev/null +++ b/lib/schemaupdater.php @@ -0,0 +1,117 @@ +<?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->conn = $conn; + $this->checksums = $this->getChecksums(); + } + + /** + * @param array $tableDefs + * @fixme handle tables that belong on different database servers...? + */ + public function checkTables(array $tableDefs) + { + $checksums = $this->checksums; + foreach ($tableDefs as $table => $def) { + $checksum = $this->tableChecksum($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; diff --git a/plugins/CacheLog/locale/nb/LC_MESSAGES/CacheLog.po b/plugins/CacheLog/locale/nb/LC_MESSAGES/CacheLog.po deleted file mode 100644 index 93a0de6fa..000000000 --- a/plugins/CacheLog/locale/nb/LC_MESSAGES/CacheLog.po +++ /dev/null @@ -1,26 +0,0 @@ -# Translation of StatusNet - CacheLog to Norwegian (bokmål) (Norsk (bokmål)) -# Expored from translatewiki.net -# -# Author: Nghtwlkr -# -- -# This file is distributed under the same license as the StatusNet package. -# -msgid "" -msgstr "" -"Project-Id-Version: StatusNet - CacheLog\n" -"Report-Msgid-Bugs-To: \n" -"POT-Creation-Date: 2010-10-23 18:02+0000\n" -"PO-Revision-Date: 2010-10-23 18:04:45+0000\n" -"Language-Team: Norwegian (bokmål) <http://translatewiki.net/wiki/Portal:no>\n" -"Content-Type: text/plain; charset=UTF-8\n" -"Content-Transfer-Encoding: 8bit\n" -"X-POT-Import-Date: 2010-10-09 14:33:29+0000\n" -"X-Generator: MediaWiki 1.17alpha (r75280); Translate extension (2010-09-17)\n" -"X-Translation-Project: translatewiki.net at http://translatewiki.net\n" -"X-Language-Code: no\n" -"X-Message-Group: #out-statusnet-plugin-cachelog\n" -"Plural-Forms: nplurals=2; plural=(n != 1);\n" - -#: CacheLogPlugin.php:106 -msgid "Log reads and writes to the cache." -msgstr "Logg leser og skriver til hurtiglageret." diff --git a/plugins/OStatus/classes/Ostatus_profile.php b/plugins/OStatus/classes/Ostatus_profile.php index 03fcb71df..4ba0a6c85 100644 --- a/plugins/OStatus/classes/Ostatus_profile.php +++ b/plugins/OStatus/classes/Ostatus_profile.php @@ -25,7 +25,8 @@ if (!defined('STATUSNET')) { * @package OStatusPlugin * @maintainer Brion Vibber <brion@status.net> */ -class Ostatus_profile extends Memcached_DataObject + +class Ostatus_profile extends Managed_DataObject { public $__table = 'ostatus_profile'; @@ -47,74 +48,35 @@ class Ostatus_profile extends Memcached_DataObject } /** - * return table definition for DB_DataObject - * - * DB_DataObject needs to know something about the table to manipulate - * instances. This method provides all the DB_DataObject needs to know. + * Return table definition for Schema setup and DB_DataObject usage. * * @return array array of column definitions */ - function table() - { - return array('uri' => DB_DATAOBJECT_STR + DB_DATAOBJECT_NOTNULL, - 'profile_id' => DB_DATAOBJECT_INT, - 'group_id' => DB_DATAOBJECT_INT, - 'feeduri' => DB_DATAOBJECT_STR, - 'salmonuri' => DB_DATAOBJECT_STR, - 'avatar' => DB_DATAOBJECT_STR, - 'created' => DB_DATAOBJECT_STR + DB_DATAOBJECT_DATE + DB_DATAOBJECT_TIME + DB_DATAOBJECT_NOTNULL, - 'modified' => DB_DATAOBJECT_STR + DB_DATAOBJECT_DATE + DB_DATAOBJECT_TIME + DB_DATAOBJECT_NOTNULL); - } static function schemaDef() { - return array(new ColumnDef('uri', 'varchar', - 255, false, 'PRI'), - new ColumnDef('profile_id', 'integer', - null, true, 'UNI'), - new ColumnDef('group_id', 'integer', - null, true, 'UNI'), - new ColumnDef('feeduri', 'varchar', - 255, true, 'UNI'), - new ColumnDef('salmonuri', 'text', - null, true), - new ColumnDef('avatar', 'text', - null, true), - new ColumnDef('created', 'datetime', - null, false), - new ColumnDef('modified', 'datetime', - null, false)); - } - - /** - * return key definitions for DB_DataObject - * - * DB_DataObject needs to know about keys that the table has; this function - * defines them. - * - * @return array key definitions - */ - function keys() - { - return array_keys($this->keyTypes()); - } - - /** - * return key definitions for Memcached_DataObject - * - * Our caching system uses the same key definitions, but uses a different - * method to get them. - * - * @return array key definitions - */ - function keyTypes() - { - return array('uri' => 'K', 'profile_id' => 'U', 'group_id' => 'U', 'feeduri' => 'U'); - } - - function sequenceKey() - { - return array(false, false, false); + return array( + 'fields' => array( + 'uri' => array('type' => 'varchar', 'length' => 255, 'not null' => true), + 'profile_id' => array('type' => 'integer'), + 'group_id' => array('type' => 'integer'), + 'feeduri' => array('type' => 'varchar', 'length' => 255), + 'salmonuri' => array('type' => 'varchar', 'length' => 255), + 'avatar' => array('type' => 'text'), + 'created' => array('type' => 'datetime', 'not null' => true), + 'modified' => array('type' => 'datetime', 'not null' => true), + ), + 'primary key' => array('uri'), + 'unique keys' => array( + 'ostatus_profile_profile_id_idx' => array('profile_id'), + 'ostatus_profile_group_id_idx' => array('group_id'), + 'ostatus_profile_feeduri_idx' => array('feeduri'), + ), + 'foreign keys' => array( + 'ostatus_profile_profile_id_fkey' => array('profile', array('profile_id' => 'id')), + 'ostatus_profile_group_id_fkey' => array('user_group', array('group_id' => 'id')), + ), + ); } /** diff --git a/plugins/Sample/SamplePlugin.php b/plugins/Sample/SamplePlugin.php index ef69121a9..a0d1140f3 100644 --- a/plugins/Sample/SamplePlugin.php +++ b/plugins/Sample/SamplePlugin.php @@ -172,9 +172,20 @@ class SamplePlugin extends Plugin // For storing user-submitted flags on profiles $schema->ensureTable('user_greeting_count', - array(new ColumnDef('user_id', 'integer', null, - true, 'PRI'), - new ColumnDef('greeting_count', 'integer'))); + array( + 'fields' => array( + 'user_id' => array('type' => 'int', 'not null' => true), + 'greeting_count' => array('type' => 'int'), + ), + 'primary key' => array('user_id'), + 'foreign keys' => array( + // Not all databases will support foreign keys, but even + // when not enforced it's helpful to include these definitions + // as documentation. + 'user_greeting_count_user_id_fkey' => array('user', array('user_id' => 'id')), + ), + ) + ); return true; } diff --git a/scripts/dumpschema.php b/scripts/dumpschema.php new file mode 100644 index 000000000..2b238f006 --- /dev/null +++ b/scripts/dumpschema.php @@ -0,0 +1,249 @@ +#!/usr/bin/env php +<?php +/* + * StatusNet - a distributed open-source microblogging tool + * Copyright (C) 2008, 2009, 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/>. + */ + +define('INSTALLDIR', realpath(dirname(__FILE__) . '/..')); + +$helptext = <<<END_OF_CHECKSCHEMA_HELP +Attempt to pull a schema definition for a given table. + + --all run over all defined core tables + --diff show differences between the expected and live table defs + --raw skip compatibility filtering for diffs + --create dump SQL that would be run to update or create this table + --build dump SQL that would be run to create this table fresh + --checksum just output checksums from the source schema defs + + +END_OF_CHECKSCHEMA_HELP; + +$longoptions = array('diff', 'all', 'create', 'update', 'raw', 'checksum'); +require_once INSTALLDIR.'/scripts/commandline.inc'; + +function indentOptions($indent) +{ + $cutoff = 3; + if ($indent < $cutoff) { + $space = $indent ? str_repeat(' ', $indent * 4) : ''; + $sep = ","; + $lf = "\n"; + $endspace = "$lf" . ($indent ? str_repeat(' ', ($indent - 1) * 4) : ''); + } else { + $space = ''; + $sep = ", "; + $lf = ''; + $endspace = ''; + } + if ($indent - 1 < $cutoff) { + } + return array($space, $sep, $lf, $endspace); +} + +function prettyDumpArray($arr, $key=null, $indent=0) +{ + // hack + if ($key == 'primary key') { + $subIndent = $indent + 2; + } else { + $subIndent = $indent + 1; + } + + list($space, $sep, $lf, $endspace) = indentOptions($indent); + list($inspace, $insep, $inlf, $inendspace) = indentOptions($subIndent); + + print "{$space}"; + if (!is_numeric($key)) { + print "'$key' => "; + } + if (is_array($arr)) { + print "array({$inlf}"; + $n = 0; + foreach ($arr as $key => $row) { + $n++; + prettyDumpArray($row, $key, $subIndent); + if ($n < count($arr)) { + print "$insep$inlf"; + } + } + // hack! + print "{$inendspace})"; + } else { + print var_export($arr, true); + } +} + +function getCoreSchema($tableName) +{ + $schema = array(); + include INSTALLDIR . '/db/core.php'; + return $schema[$tableName]; +} + +function getCoreTables() +{ + $schema = array(); + include INSTALLDIR . '/db/core.php'; + return array_keys($schema); +} + +function dumpTable($tableName, $live) +{ + if ($live) { + $schema = Schema::get(); + $def = $schema->getTableDef($tableName); + } else { + // hack + $def = getCoreSchema($tableName); + } + prettyDumpArray($def, $tableName); + print "\n"; +} + +function dumpBuildTable($tableName) +{ + echo "-- \n"; + echo "-- $tableName\n"; + echo "-- \n"; + + $schema = Schema::get(); + $def = getCoreSchema($tableName); + $sql = $schema->buildCreateTable($tableName, $def); + $sql[] = ''; + + echo implode(";\n", $sql); + echo "\n"; +} + +function dumpEnsureTable($tableName) +{ + $schema = Schema::get(); + $def = getCoreSchema($tableName); + $sql = $schema->buildEnsureTable($tableName, $def); + + if ($sql) { + echo "-- \n"; + echo "-- $tableName\n"; + echo "-- \n"; + + $sql[] = ''; + echo implode(";\n", $sql); + echo "\n"; + } +} + +function dumpDiff($tableName, $filter) +{ + $schema = Schema::get(); + $def = getCoreSchema($tableName); + try { + $old = $schema->getTableDef($tableName); + } catch (Exception $e) { + // @fixme this is a terrible check :D + if (preg_match('/no such table/i', $e->getMessage())) { + return dumpTable($tableName, false); + } else { + throw $e; + } + } + + if ($filter) { + //$old = $schema->filterDef($old); + $def = $schema->filterDef($def); + } + + // @hack + $old = tweakPrimaryKey($old); + $def = tweakPrimaryKey($def); + + $sections = array_unique(array_merge(array_keys($old), array_keys($def))); + $final = array(); + foreach ($sections as $section) { + if ($section == 'fields') { + // this shouldn't be needed maybe... wait what? + } + $diff = $schema->diffArrays($old, $def, $section, $compare); + $chunks = array('del', 'mod', 'add'); + foreach ($chunks as $chunk) { + if ($diff[$chunk]) { + foreach ($diff[$chunk] as $key) { + if ($chunk == 'del') { + $final[$section]["DEL $key"] = $old[$section][$key]; + } else if ($chunk == 'add') { + $final[$section]["ADD $key"] = $def[$section][$key]; + } else if ($chunk == 'mod') { + $final[$section]["OLD $key"] = $old[$section][$key]; + $final[$section]["NEW $key"] = $def[$section][$key]; + } + } + } + } + } + + prettyDumpArray($final, $tableName); + print "\n"; +} + +function tweakPrimaryKey($def) +{ + if (isset($def['primary key'])) { + $def['primary keys'] = array('primary key' => $def['primary key']); + unset($def['primary key']); + } + return $def; +} + +function dumpChecksum($tableName) +{ + $schema = Schema::get(); + $def = getCoreSchema($tableName); + + $updater = new SchemaUpdater($schema); + $checksum = $updater->checksum($def); + $old = @$updater->checksums[$tableName]; + + if ($old == $checksum) { + echo "OK $checksum $tableName\n"; + } else if (!$old) { + echo "NEW $checksum $tableName\n"; + } else { + echo "MOD $checksum $tableName (was $old)\n"; + } +} + +if (have_option('all')) { + $args = getCoreTables(); +} + +if (count($args)) { + foreach ($args as $tableName) { + if (have_option('diff')) { + dumpDiff($tableName, !have_option('raw')); + } else if (have_option('create')) { + dumpBuildTable($tableName); + } else if (have_option('update')) { + dumpEnsureTable($tableName); + } else if (have_option('checksum')) { + dumpChecksum($tableName); + } else { + dumpTable($tableName, true); + } + } +} else { + show_help($helptext); +}
\ No newline at end of file |