X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=db%2Fcore.php;h=16a59462d491bd2f884f3c454858486185e86331;hb=874804a0c4c7deb89ca9292a12f684ed9cda388a;hp=f3461243fa8ac4b5382dcd53746df316127e3be6;hpb=4101de7dd7cf059816c29c666c816f260a84c252;p=quix0rs-gnu-social.git

diff --git a/db/core.php b/db/core.php
index f3461243fa..16a59462d4 100644
--- a/db/core.php
+++ b/db/core.php
@@ -1,24 +1,33 @@
 <?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
-
-decimal <-> 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,14 +66,14 @@ $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(
-        'avatar_url_idx' => array('url'),
+        'avatar_url_key' => array('url'),
     ),
     'foreign keys' => array(
-        'profile_id' => array('profile' => 'id'),
+        'avatar_profile_id_fkey' => array('profile', array('profile_id' => 'id')),
     ),
     'indexes' => array(
         'avatar_profile_id_idx' => array('profile_id'),
@@ -77,11 +86,11 @@ $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(
-        'sms_carrier_name_idx' => array('name'),
+        'sms_carrier_name_key' => array('name'),
     ),
 );
 
@@ -109,26 +118,26 @@ $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(
-        'user_nickname_idx' => array('nickname'),
-        'user_email_idx' => array('email'),
-        'user_incomingemail_idx' => array('incomingemail'),
-        'user_sms_idx' => array('sms'),
-        'user_uri_idx' => array('uri'),
+        '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(
-        'id' => array('profile' => 'id'),
-        'carrier' => array('sms_carrier' => 'id'),
-        'design_id' => array('desgin' => 'id'),
+        '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'),
@@ -143,14 +152,14 @@ $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(
-        'remote_profile_uri_idx' => array('uri'),
+        'remote_profile_uri_key' => array('uri'),
     ),
     'foreign keys' => array(
-        'id' => array('profile' => 'id'),
+        'remote_profile_id_fkey' => array('profile', array('id' => 'id')),
     ),
 );
 
@@ -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,16 +201,17 @@ $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(
-        'notice_uri_idx' => array('uri'),
+        'notice_uri_key' => array('uri'),
     ),
     'foreign keys' => array(
-        'profile_id' => array('profile' => 'id'),
-        'reply_to' => array('notice', 'id'),
-        'conversation' => array('conversation', 'id'), # note... used to refer to notice.id
-        'repeat_of' => array('notice', 'id'), # @fixme: what about repeats of deleted notices?
+        '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'),
@@ -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'),
 );
@@ -235,8 +246,8 @@ $schema['reply'] = array(
     ),
     'primary key' => array('notice_id', 'profile_id'),
     'foreign keys' => array(
-        'notice_id' => array('notice' => 'id'),
-        'profile_id' => array('profile' => 'id'),
+        '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'),
@@ -253,8 +264,8 @@ $schema['fave'] = array(
     ),
     'primary key' => array('notice_id', 'user_id'),
     'foreign keys' => array(
-        'notice_id' => array('notice', 'id'),
-        'user_id' => array('profile', 'id'), // note: formerly referenced notice.id, but we can now record remote users' favorites
+        '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'),
@@ -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,11 +301,11 @@ $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(
-        'consumer_key' => array('consumer' => 'consumer_key'),
+        'token_consumer_key_fkey' => array('consumer', array('consumer_key'=> 'consumer_key')),
     ),
 );
 
@@ -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,15 +339,15 @@ $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(
-        'oauth_application_name_idx' => array('name'), // in the long run, we should perhaps not force these unique, and use another source id
+        '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(
-        'owner' => array('profile' => 'id'), // Are remote users allowed to create oauth application records?
-        'consumer_key' => array('consumer' => 'consumer_key'),
+        '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')),
     ),
 );
 
@@ -347,12 +358,12 @@ $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(
-        'profile_id' => array('profile' => 'id'),
-        'application_id' => array('oauth_application' => 'id'),
+        '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')),
     ),
 );
 
@@ -377,7 +388,7 @@ $schema['oid_nonces'] = array(
         'salt' => array('type' => 'char', 'length' => 40),
     ),
     'unique keys' => array(
-        'oid_nonces_server_url_type_idx' => array(array('server_url', 255), 'timestamp', 'salt'),
+        'oid_nonces_server_url_timestamp_salt_key' => array(array('server_url', 255), 'timestamp', 'salt'),
     ),
 );
 
@@ -390,11 +401,11 @@ $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(
-        'user_id' => array('user' => 'id'),
+        'confirm_address_user_id_fkey' => array('user', array('user_id' => 'id')),
     ),
 );
 
@@ -402,11 +413,11 @@ $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(
-        'user_id' => array('user' => 'id'),
+        'remember_me_user_id_fkey' => array('user', array('user_id' => 'id')),
     ),
 );
 
@@ -433,7 +444,7 @@ $schema['notice_tag'] = array(
     ),
     'primary key' => array('tag', 'notice_id'),
     'foreign keys' => array(
-        'notice_id' => array('notice' => 'id'),
+        'notice_tag_notice_id_fkey' => array('notice', array('notice_id' => 'id')),
     ),
     'indexes' => array(
         'notice_tag_created_idx' => array('created'),
@@ -449,11 +460,11 @@ $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(
-        'foreign_service_name_idx' => array('name'),
+        'foreign_service_name_key' => array('name'),
     ),
 );
 
@@ -464,14 +475,14 @@ $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(
-        'service' => array('foreign_service' => 'id'),
+        'foreign_user_service_fkey' => array('foreign_service', array('service' => 'id')),
     ),
     'unique keys' => array(
-        'foreign_user_uri_idx' => array('uri'),
+        'foreign_user_uri_key' => array('uri'),
     ),
 );
 
@@ -487,13 +498,13 @@ $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(
-        'user_id' => array('user' => 'id'),
-        'foreign_id' => array('foreign_user' => 'id'),
-        'service' => array('foreign_service' => 'id'),
+        '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'),
@@ -503,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(
-        'service' => array('foreign_service' => 'id'),
-        'subscriber' => array('foreign_user' => 'id'),
-        'subscribed' => array('foreign_user' => 'id'),
+        '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('subscriber'),
-        'foreign_subscription_subscribed_idx' => array('subscribed'),
+        'foreign_subscription_subscriber_idx' => array('service', 'subscriber'),
+        'foreign_subscription_subscribed_idx' => array('service', 'subscribed'),
     ),
 );
 
@@ -529,7 +540,7 @@ $schema['invitation'] = array(
     ),
     'primary key' => array('code'),
     'foreign keys' => array(
-        'user_id' => array('user' => 'id'),
+        'invitation_user_id_fkey' => array('user', array('user_id' => 'id')),
     ),
     'indexes' => array(
         'invitation_address_idx' => array('address', 'address_type'),
@@ -547,16 +558,16 @@ $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'),
-    'unique key' => array(
-        'message_uri_idx' => array('uri'),
+    'unique keys' => array(
+        'message_uri_key' => array('uri'),
     ),
     'foreign keys' => array(
-        'from_profile' => array('profile' => 'id'),
-        'to_profile' => array('profile' => 'id'),
+        '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.
@@ -577,8 +588,8 @@ $schema['notice_inbox'] = array(
     ),
     'primary key' => array('user_id', 'notice_id'),
     'foreign keys' => array(
-        'user_id' => array('user' => 'id'),
-        'notice_id' => array('notice' => 'id'),
+        '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'),
@@ -590,12 +601,12 @@ $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(
-        'tagger' => array('user' => 'id'),
-        'tagged' => array('profile' => 'id'),
+        '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'),
@@ -608,11 +619,11 @@ $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(
-        'blocker' => array('user' => 'id'),
-        'blocked' => array('profile' => 'id'),
+        'profile_block_blocker_fkey' => array('user', array('blocker' => 'id')),
+        'profile_block_blocked_fkey' => array('profile', array('blocked' => 'id')),
     ),
     'primary key' => array('blocker', 'blocked'),
 );
@@ -634,17 +645,17 @@ $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 key' => array(
-        'user_uri_idx' => array('uri'),
+    'unique keys' => array(
+        'user_group_uri_key' => array('uri'),
     ),
     'foreign keys' => array(
-        'design_id' => array('design' => 'id'),
+        'user_group_design_id_fkey' => array('design', array('design_id' => 'id')),
     ),
     'indexes' => array(
         'user_group_nickname_idx' => array('nickname'),
@@ -658,12 +669,12 @@ $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(
-        'group_id' => array('user_group' => 'id'),
-        'profile_id' => array('profile' => 'id'),
+        '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?
@@ -682,8 +693,8 @@ $schema['related_group'] = array(
     ),
     'primary key' => array('group_id', 'related_group_id'),
     'foreign keys' => array(
-        'group_id' => array('user_group' => 'id'),
-        'related_group_id' => array('user_group' => 'id'),
+        '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')),
     ),
 );
 
@@ -696,8 +707,8 @@ $schema['group_inbox'] = array(
     ),
     'primary key' => array('group_id', 'notice_id'),
     'foreign keys' => array(
-        'group_id' => array('user_group' => 'id'),
-        'notice_id' => array('notice' => 'id'),
+        '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'),
@@ -716,11 +727,11 @@ $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(
-        'file_url_idx' => array('url'),
+        'file_url_key' => array('url'),
     ),
 );
 
@@ -739,11 +750,11 @@ $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(
-         'file_id' => array('file' => 'id'),
+         'file_oembed_file_id_fkey' => array('file', array('file_id' => 'id')),
     ),
 );
 
@@ -753,11 +764,11 @@ $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(
-         'file_id' => array('file' => 'id'),
+         'file_redirection_file_id_fkey' => array('file' => array('file_id' => 'id')),
     ),
 );
 
@@ -767,14 +778,14 @@ $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(
-        'file_id' => array('file' => 'id'),
+        'file_thumbnail_file_id_fkey' => array('file', array('file_id' => 'id')),
     ),
     'unique keys' => array(
-        'file_thumbnail_url_idx' => array('url'),
+        'file_thumbnail_url_key' => array('url'),
     ),
 );
 
@@ -782,12 +793,12 @@ $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(
-        'file_id' => array('file' => 'id'),
-        'post_id' => array('notice' => 'id'),
+        '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'),
@@ -813,13 +824,13 @@ $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(
-        'group_id' => array('user_group' => 'id'),
-        'blocked' => array('profile' => 'id'),
-        'blocker' => array('user' => 'id'),
+        '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')),
     ),
 );
 
@@ -827,11 +838,11 @@ $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(
-        'group_id' => array('user_group' => 'id'),
+        'group_alias_group_id_fkey' => array('user_group', array('group_id' => 'id')),
     ),
     'indexes' => array(
         'group_alias_group_id_idx' => array('group_id'),
@@ -843,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(
@@ -861,7 +872,7 @@ $schema['deleted_notice'] = array(
     ),
     'primary key' => array('id'),
     'unique keys' => array(
-        'deleted_notice_uri_idx' => array('uri'),
+        'deleted_notice_uri_key' => array('uri'),
     ),
     'indexes' => array(
         'deleted_notice_profile_id_idx' => array('profile_id'),
@@ -870,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'),
@@ -885,7 +896,7 @@ $schema['profile_role'] = array(
     ),
     'primary key' => array('profile_id', 'role'),
     'foreign keys' => array(
-        'profile_id' => array('profile' => 'id'),
+        'profile_role_profile_id_fkey' => array('profile', array('profile_id' => 'id')),
     ),
 );
 
@@ -894,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'),
 );
@@ -904,11 +915,11 @@ $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(
-        'user_id' => array('user' => 'id'),
+        'login_token_user_id_fkey' => array('user', array('user_id' => 'id')),
     ),
 );
 
@@ -917,11 +928,11 @@ $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(
-        'user_id' => array('user' => 'id'),
+        'user_location_prefs_user_id_fkey' => array('user', array('user_id' => 'id')),
     ),
 );
 
@@ -932,7 +943,7 @@ $schema['inbox'] = array(
     ),
     'primary key' => array('user_id'),
     'foreign keys' => array(
-        'user_id' => array('user' => 'id'),
+        'inbox_user_id_fkey' => array('user', array('user_id' => 'id')),
     ),
 );
 
@@ -946,15 +957,15 @@ $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(
         'transport_screenname_key' => array('transport', 'screenname'),
     ),
     'foreign keys' => array(
-        'user_id' => array('user' => 'id'),
+        'user_im_prefs_user_id_fkey' => array('user', array('user_id' => 'id')),
     ),
 );
 
@@ -963,11 +974,11 @@ $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(
-        'conversation_uri_idx' => array('uri'),
+        'conversation_uri_key' => array('uri'),
     ),
 );
 
@@ -978,29 +989,39 @@ $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(
-        'group_id' => array('user_group' => 'id'),
+        'local_group_group_id_fkey' => array('user_group', array('group_id' => 'id')),
     ),
     'unique keys' => array(
-        'local_group_nickname_idx' => array('nickname'),
+        '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'),
+        '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_id' => array('user' => 'id'),
+        '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'),
 );