X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=db%2Fcore.php;h=16a59462d491bd2f884f3c454858486185e86331;hb=874804a0c4c7deb89ca9292a12f684ed9cda388a;hp=c0def7ce5adee4365e802c40431c98ac71c4b356;hpb=4c3aebd39644d913df4abdb69b848413ca65c298;p=quix0rs-gnu-social.git diff --git a/db/core.php b/db/core.php index c0def7ce5a..16a59462d4 100644 --- a/db/core.php +++ b/db/core.php @@ -1,24 +1,33 @@ numeric - -timestamps... how to specify? -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? - -*/ + * + * 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. For now I'm leaving them + * in, but we may want to clean them up later. + * + * 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', @@ -36,7 +45,7 @@ $schema['profile'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('id'), 'indexes' => array( @@ -57,7 +66,7 @@ $schema['avatar'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('profile_id', 'width', 'height'), 'unique keys' => array( @@ -77,7 +86,7 @@ $schema['sms_carrier'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('id'), 'unique keys' => array( @@ -109,13 +118,13 @@ $schema['user'] = array( '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'), + 'urlshorteningservice' => array('type' => 'varchar', 'length' => 50, 'default' => 'internal', '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('id'), 'unique keys' => array( @@ -143,7 +152,7 @@ $schema['remote_profile'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('id'), 'unique keys' => array( @@ -163,7 +172,7 @@ $schema['subscription'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('subscriber', 'subscribed'), 'indexes' => array( @@ -182,7 +191,7 @@ $schema['notice'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', '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"'), @@ -192,6 +201,7 @@ $schema['notice'] = array( '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'), + 'object_type' => array('type' => 'varchar', 'length' => 255, 'description' => 'URI representing activity streams object type', 'default' => 'http://activitystrea.ms/schema/1.0/note'), ), 'primary key' => array('id'), 'unique keys' => array( @@ -221,7 +231,8 @@ $schema['notice_source'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('code'), ); @@ -273,7 +284,7 @@ $schema['consumer'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('consumer_key'), ); @@ -290,7 +301,7 @@ $schema['token'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('consumer_key', 'tok'), 'foreign keys' => array( @@ -307,7 +318,7 @@ $schema['nonce'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('consumer_key', 'ts', 'nonce'), ); @@ -328,7 +339,7 @@ $schema['oauth_application'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('id'), 'unique keys' => array( @@ -347,7 +358,7 @@ $schema['oauth_application_user'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('profile_id', 'application_id'), 'foreign keys' => array( @@ -390,7 +401,7 @@ $schema['confirm_address'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('code'), 'foreign keys' => array( @@ -402,7 +413,7 @@ $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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('code'), 'foreign keys' => array( @@ -449,7 +460,7 @@ $schema['foreign_service'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('id'), 'unique keys' => array( @@ -464,14 +475,13 @@ $schema['foreign_user'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', '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_id_key' => array('id'), 'foreign_user_uri_key' => array('uri'), ), ); @@ -488,12 +498,12 @@ $schema['foreign_link'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', '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')), + '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( @@ -504,19 +514,19 @@ $schema['foreign_link'] = array( $schema['foreign_subscription'] = array( 'fields' => array( 'service' => array('type' => 'int', 'not null' => true, 'description' => 'service where relationship happens'), - 'subscriber' => array('type' => 'int', 'not null' => true, 'description' => 'subscriber on foreign service'), - 'subscribed' => array('type' => 'int', 'not null' => true, 'description' => 'subscribed user'), + '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')), - 'foreign_subscription_subscribed_fkey' => array('foreign_user', array('subscribed' => '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('subscriber'), - 'foreign_subscription_subscribed_idx' => array('subscribed'), + 'foreign_subscription_subscriber_idx' => array('service', 'subscriber'), + 'foreign_subscription_subscribed_idx' => array('service', 'subscribed'), ), ); @@ -548,7 +558,7 @@ $schema['message'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', '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'), @@ -591,7 +601,7 @@ $schema['profile_tag'] = 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' => 'timestamp', 'description' => 'date the tag was added'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date the tag was added'), ), 'primary key' => array('tagger', 'tagged', 'tag'), 'foreign keys' => array( @@ -609,7 +619,7 @@ $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' => 'timestamp', 'description' => 'date of blocking'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date of blocking'), ), 'foreign keys' => array( 'profile_block_blocker_fkey' => array('user', array('blocker' => 'id')), @@ -635,14 +645,14 @@ $schema['user_group'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', '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_uri_key' => array('uri'), + 'user_group_uri_key' => array('uri'), ), 'foreign keys' => array( 'user_group_design_id_fkey' => array('design', array('design_id' => 'id')), @@ -659,7 +669,7 @@ $schema['group_member'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('group_id', 'profile_id'), 'foreign keys' => array( @@ -717,7 +727,7 @@ $schema['file'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('id'), 'unique keys' => array( @@ -740,7 +750,7 @@ $schema['file_oembed'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('file_id'), 'foreign keys' => array( @@ -754,7 +764,7 @@ $schema['file_redirection'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('url'), 'foreign keys' => array( @@ -768,7 +778,7 @@ $schema['file_thumbnail'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('file_id'), 'foreign keys' => array( @@ -783,7 +793,7 @@ $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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('file_id', 'post_id'), 'foreign keys' => array( @@ -814,7 +824,7 @@ $schema['group_block'] = 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' => 'timestamp', 'description' => 'date of blocking'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date of blocking'), ), 'primary key' => array('group_id', 'blocked'), 'foreign keys' => array( @@ -828,7 +838,7 @@ $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' => 'timestamp', 'description' => 'date alias was created'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date alias was created'), ), 'primary key' => array('alias'), 'foreign keys' => array( @@ -844,7 +854,7 @@ $schema['session'] = 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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('id'), 'indexes' => array( @@ -871,8 +881,8 @@ $schema['deleted_notice'] = array( $schema['config'] = array( 'fields' => array( - 'section' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'configuration section'), - 'setting' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'configuration setting'), + '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'), @@ -895,7 +905,7 @@ $schema['location_namespace'] = 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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('id'), ); @@ -905,7 +915,7 @@ $schema['login_token'] = 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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('user_id'), 'foreign keys' => array( @@ -918,7 +928,7 @@ $schema['user_location_prefs'] = 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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('user_id'), 'foreign keys' => array( @@ -947,8 +957,8 @@ $schema['user_im_prefs'] = array( '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' => 'timestamp', 'not null' => true, 'default' => 'CURRENT_TIMESTAMP', 'description' => 'date this record was created'), // @fixme will that default work? - 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'), + 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('user_id', 'transport'), 'unique keys' => array( @@ -964,7 +974,7 @@ $schema['conversation'] = 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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('id'), 'unique keys' => array( @@ -979,7 +989,7 @@ $schema['local_group'] = array( '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), ), 'primary key' => array('group_id'), 'foreign keys' => array( @@ -993,15 +1003,25 @@ $schema['local_group'] = array( $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'), + 'urlshorteningservice' => array('type' => 'varchar', 'length' => 50, 'default' => 'internal', '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' => 'timestamp', 'description' => 'date this record was modified'), + 'modified' => array('type' => 'timestamp', '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' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'), + ), + 'primary key' => array('table_name'), +);