We are no longer offering accounts on this server. Consider https://gitlab.freedesktop.org/ as a place to host projects.

core.php 65.5 KB
Newer Older
1 2
<?php

Brion Vibber's avatar
Brion Vibber committed
3
/**
4 5 6 7 8 9 10 11 12 13 14 15
 *
 * 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
Brion Vibber's avatar
Brion Vibber committed
16 17
 * 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.
18 19 20 21 22 23 24 25 26 27 28 29 30
 *
 * 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?
 */
31 32

$schema['profile'] = array(
Brion Vibber's avatar
Brion Vibber committed
33
    'description' => 'local and remote users have profiles',
34 35 36 37 38 39 40 41
    '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'),
Brion Vibber's avatar
Brion Vibber committed
42 43
        'lat' => array('type' => 'numeric', 'precision' => 10, 'scale' => 7, 'description' => 'latitude'),
        'lon' => array('type' => 'numeric', 'precision' => 10, 'scale' => 7, 'description' => 'longitude'),
44 45 46 47
        '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'),
Brion Vibber's avatar
Brion Vibber committed
48
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
49 50 51 52 53
    ),
    'primary key' => array('id'),
    'indexes' => array(
        'profile_nickname_idx' => array('nickname'),
    ),
54
    'fulltext indexes' => array(
Brion Vibber's avatar
Brion Vibber committed
55
        'nickname' => array('nickname', 'fullname', 'location', 'bio', 'homepage')
56
    ),
57 58 59 60
);

$schema['avatar'] = array(
    'fields' => array(
Brion Vibber's avatar
Brion Vibber committed
61
        'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table'),
Brion Vibber's avatar
Brion Vibber committed
62
        'original' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'uploaded by user or generated?'),
63 64 65 66 67 68
        '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'),
Brion Vibber's avatar
Brion Vibber committed
69
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
70 71 72
    ),
    'primary key' => array('profile_id', 'width', 'height'),
    'unique keys' => array(
73
        'avatar_url_key' => array('url'),
74 75
    ),
    'foreign keys' => array(
76
        'avatar_profile_id_fkey' => array('profile', array('profile_id' => 'id')),
77 78 79 80 81 82 83 84
    ),
    'indexes' => array(
        'avatar_profile_id_idx' => array('profile_id'),
    ),
);

$schema['sms_carrier'] = array(
    'fields' => array(
85
        'id' => array('type' => 'int', 'not null' => true, 'description' => 'primary key for SMS carrier'),
86 87 88
        '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'),
Brion Vibber's avatar
Brion Vibber committed
89
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
90 91 92
    ),
    'primary key' => array('id'),
    'unique keys' => array(
93
        'sms_carrier_name_key' => array('name'),
94 95 96 97 98 99
    ),
);

$schema['user'] = array(
    'description' => 'local users',
    'fields' => array(
100
        'id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table'),
101 102 103 104
        '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'),
Brion Vibber's avatar
Brion Vibber committed
105 106 107 108 109 110
        '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'),
111 112
        'language' => array('type' => 'varchar', 'length' => 50, 'description' => 'preferred language'),
        'timezone' => array('type' => 'varchar', 'length' => 50, 'description' => 'timezone'),
Brion Vibber's avatar
Brion Vibber committed
113
        'emailpost' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Post by email'),
114 115
        'sms' => array('type' => 'varchar', 'length' => 64, 'description' => 'sms phone number'),
        'carrier' => array('type' => 'int', 'description' => 'foreign key to sms_carrier'),
Brion Vibber's avatar
Brion Vibber committed
116 117
        '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'),
118 119
        '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'),
Brion Vibber's avatar
Brion Vibber committed
120
        'autosubscribe' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'automatically subscribe to users who subscribe to us'),
121
        'subscribe_policy' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => '0 = anybody can subscribe; 1 = require approval'),
122
        'urlshorteningservice' => array('type' => 'varchar', 'length' => 50, 'default' => 'internal', 'description' => 'service to use for auto-shortening URLs'),
Brion Vibber's avatar
Brion Vibber committed
123
        'inboxed' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'has an inbox been created for this user?'),
124
        'design_id' => array('type' => 'int', 'description' => 'id of a design'),
Brion Vibber's avatar
Brion Vibber committed
125
        'viewdesigns' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'whether to view user-provided designs'),
126
        'private_stream' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'whether to limit all notices to followers only'),
127 128

        'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
Brion Vibber's avatar
Brion Vibber committed
129
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
130 131 132
    ),
    'primary key' => array('id'),
    'unique keys' => array(
133 134 135 136 137
        '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'),
138 139
    ),
    'foreign keys' => array(
140 141 142
        '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')),
143 144 145 146 147 148 149 150 151
    ),
    'indexes' => array(
        'user_smsemail_idx' => array('smsemail'),
    ),
);

$schema['remote_profile'] = array(
    'description' => 'remote people (OMB)',
    'fields' => array(
152
        'id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table'),
153 154 155 156
        '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'),
Brion Vibber's avatar
Brion Vibber committed
157
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
158 159 160
    ),
    'primary key' => array('id'),
    'unique keys' => array(
161
        'remote_profile_uri_key' => array('uri'),
162 163
    ),
    'foreign keys' => array(
164
        'remote_profile_id_fkey' => array('profile', array('id' => 'id')),
165 166 167 168 169 170 171
    ),
);

$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'),
Brion Vibber's avatar
Brion Vibber committed
172 173
        'jabber' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'deliver jabber messages'),
        'sms' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'deliver sms messages'),
174 175 176
        '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'),
Brion Vibber's avatar
Brion Vibber committed
177
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
178 179 180 181 182 183 184 185 186 187 188
    ),
    '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(
189
        'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
190 191 192 193 194 195
        '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'),
Brion Vibber's avatar
Brion Vibber committed
196
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
197
        'reply_to' => array('type' => 'int', 'description' => 'notice replied to (usually a guess)'),
Brion Vibber's avatar
Brion Vibber committed
198
        'is_local' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'notice was generated by a user'),
199 200 201 202 203 204 205
        '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'),
206
        'object_type' => array('type' => 'varchar', 'length' => 255, 'description' => 'URI representing activity streams object type', 'default' => 'http://activitystrea.ms/schema/1.0/note'),
Evan Prodromou's avatar
Evan Prodromou committed
207 208 209
        'scope' => array('type' => 'int',
                         'default' => '1',
                         'description' => 'bit map for distribution scope; 0 = everywhere; 1 = this server only; 2 = addressees; 4 = followers'),
210 211 212
    ),
    'primary key' => array('id'),
    'unique keys' => array(
213
        'notice_uri_key' => array('uri'),
214 215
    ),
    'foreign keys' => array(
216 217 218 219
        '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?
220 221
    ),
    'indexes' => array(
Brion Vibber's avatar
Brion Vibber committed
222
        'notice_profile_id_idx' => array('profile_id', 'created', 'id'),
223 224 225 226 227
        'notice_conversation_idx' => array('conversation'),
        'notice_created_idx' => array('created'),
        'notice_replyto_idx' => array('reply_to'),
        'notice_repeatof_idx' => array('repeat_of'),
    ),
Brion Vibber's avatar
Brion Vibber committed
228 229 230
    'fulltext indexes' => array(
        'content' => array('content'),
    )
231 232 233 234 235 236 237 238
);

$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'),
Brion Vibber's avatar
Brion Vibber committed
239
        'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
Brion Vibber's avatar
Brion Vibber committed
240
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
241 242 243 244 245 246 247 248
    ),
    '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'),
Brion Vibber's avatar
Brion Vibber committed
249
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
250 251 252 253
        '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(
254 255
        'reply_notice_id_fkey' => array('notice', array('notice_id' => 'id')),
        'reply_profile_id_fkey' => array('profile', array('profile_id' => 'id')),
256 257 258 259 260 261 262 263 264 265 266 267
    ),
    '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'),
Brion Vibber's avatar
Brion Vibber committed
268
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
269 270 271
    ),
    'primary key' => array('notice_id', 'user_id'),
    'foreign keys' => array(
272 273
        '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
274 275 276 277 278 279 280 281 282 283 284 285 286
    ),
    '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(
287
        'consumer_key' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'unique identifier, root URL'),
288 289 290 291
        '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'),
Brion Vibber's avatar
Brion Vibber committed
292
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
293 294 295 296 297 298 299 300 301 302
    ),
    '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'),
Brion Vibber's avatar
Brion Vibber committed
303 304
        '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'),
305 306 307 308
        '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'),
Brion Vibber's avatar
Brion Vibber committed
309
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
310 311 312
    ),
    'primary key' => array('consumer_key', 'tok'),
    'foreign keys' => array(
313
        'token_consumer_key_fkey' => array('consumer', array('consumer_key'=> 'consumer_key')),
314 315 316 317 318 319 320 321 322 323 324 325
    ),
);

$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'),
Brion Vibber's avatar
Brion Vibber committed
326
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
327 328 329 330 331 332 333
    ),
    'primary key' => array('consumer_key', 'ts', 'nonce'),
);

$schema['oauth_application'] = array(
    'description' => 'OAuth application registration record',
    'fields' => array(
334
        'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
335
        'owner' => array('type' => 'int', 'not null' => true, 'description' => 'owner of the application'),
Brion Vibber's avatar
Brion Vibber committed
336
        'consumer_key' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'application consumer key'),
337 338 339 340 341 342 343
        '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'),
Brion Vibber's avatar
Brion Vibber committed
344 345
        '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'),
346
        'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
Brion Vibber's avatar
Brion Vibber committed
347
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
348 349 350
    ),
    'primary key' => array('id'),
    'unique keys' => array(
351
        'oauth_application_name_key' => array('name'), // in the long run, we should perhaps not force these unique, and use another source id
352 353
    ),
    'foreign keys' => array(
354 355
        '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')),
356 357 358 359 360 361 362 363 364 365
    ),
);

$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'),
Brion Vibber's avatar
Brion Vibber committed
366
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
367 368 369
    ),
    'primary key' => array('profile_id', 'application_id'),
    'foreign keys' => array(
370 371
        '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')),
372 373 374 375 376 377 378
    ),
);

/* These are used by JanRain OpenID library */

$schema['oid_associations'] = array(
    'fields' => array(
379 380
        'server_url' => array('type' => 'blob', 'not null' => true),
        'handle' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'default' => ''), // character set latin1,
381 382 383 384 385 386 387 388 389 390 391 392 393 394 395
        '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(
396
        'oid_nonces_server_url_timestamp_salt_key' => array(array('server_url', 255), 'timestamp', 'salt'),
397 398 399 400 401 402
    ),
);

$schema['confirm_address'] = array(
    'fields' => array(
        'code' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'good random code'),
Brion Vibber's avatar
Brion Vibber committed
403
        'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who requested confirmation'),
404 405 406 407 408
        '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'),
Brion Vibber's avatar
Brion Vibber committed
409
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
410 411
    ),
    'primary key' => array('code'),
Brion Vibber's avatar
Brion Vibber committed
412
    'foreign keys' => array(
413
        'confirm_address_user_id_fkey' => array('user', array('user_id' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
414
    ),
415 416 417 418 419
);

$schema['remember_me'] = array(
    'fields' => array(
        'code' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'good random code'),
Brion Vibber's avatar
Brion Vibber committed
420
        'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who is logged in'),
Brion Vibber's avatar
Brion Vibber committed
421
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
422 423
    ),
    'primary key' => array('code'),
Brion Vibber's avatar
Brion Vibber committed
424
    'foreign keys' => array(
425
        'remember_me_user_id_fkey' => array('user', array('user_id' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
426
    ),
427 428 429 430
);

$schema['queue_item'] = array(
    'fields' => array(
431
        'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
432
        'frame' => array('type' => 'blob', 'not null' => true, 'description' => 'data: object reference or opaque string'),
Brion Vibber's avatar
Brion Vibber committed
433
        '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.
434 435 436 437 438 439 440 441 442 443
        '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(
Brion Vibber's avatar
Brion Vibber committed
444
    'description' => 'Hash tags',
445 446
    'fields' => array(
        'tag' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'hash tag associated with this notice'),
Brion Vibber's avatar
Brion Vibber committed
447
        'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice tagged'),
448 449 450
        'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
    ),
    'primary key' => array('tag', 'notice_id'),
Brion Vibber's avatar
Brion Vibber committed
451
    'foreign keys' => array(
452
        'notice_tag_notice_id_fkey' => array('notice', array('notice_id' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
453
    ),
454 455 456 457 458 459 460 461 462 463 464 465 466 467
    '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'),
Brion Vibber's avatar
Brion Vibber committed
468
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
469 470 471
    ),
    'primary key' => array('id'),
    'unique keys' => array(
472
        'foreign_service_name_key' => array('name'),
473 474 475 476 477
    ),
);

$schema['foreign_user'] = array(
    'fields' => array(
478
        'id' => array('type' => 'int', 'size' => 'big', 'not null' => true, 'description' => 'unique numeric key on foreign service'),
Brion Vibber's avatar
Brion Vibber committed
479
        'service' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to service'),
480 481 482
        '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'),
Brion Vibber's avatar
Brion Vibber committed
483
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
484 485
    ),
    'primary key' => array('id', 'service'),
Brion Vibber's avatar
Brion Vibber committed
486
    'foreign keys' => array(
487
        'foreign_user_service_fkey' => array('foreign_service', array('service' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
488
    ),
489
    'unique keys' => array(
490
        'foreign_user_uri_key' => array('uri'),
491 492 493 494 495
    ),
);

$schema['foreign_link'] = array(
    'fields' => array(
496 497
        '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'),
Brion Vibber's avatar
Brion Vibber committed
498
        'service' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to service'),
499 500
        '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'),
Brion Vibber's avatar
Brion Vibber committed
501
        'friendsync' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 2, 'description' => 'friend synchronization, bit 1 = sync outgoing, bit 2 = sync incoming'),
502 503 504 505
        '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'),
Brion Vibber's avatar
Brion Vibber committed
506
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
507 508
    ),
    'primary key' => array('user_id', 'foreign_id', 'service'),
Brion Vibber's avatar
Brion Vibber committed
509
    'foreign keys' => array(
510
        'foreign_link_user_id_fkey' => array('user', array('user_id' => 'id')),
511
        'foreign_link_foreign_id_fkey' => array('foreign_user', array('foreign_id' => 'id', 'service' => 'service')),
512
        'foreign_link_service_fkey' => array('foreign_service', array('service' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
513
    ),
514 515 516 517 518 519 520
    'indexes' => array(
        'foreign_user_user_id_idx' => array('user_id'),
    ),
);

$schema['foreign_subscription'] = array(
    'fields' => array(
Brion Vibber's avatar
Brion Vibber committed
521
        'service' => array('type' => 'int', 'not null' => true, 'description' => 'service where relationship happens'),
522 523
        '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'),
Brion Vibber's avatar
Brion Vibber committed
524
        'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
525 526
    ),
    'primary key' => array('service', 'subscriber', 'subscribed'),
Brion Vibber's avatar
Brion Vibber committed
527
    'foreign keys' => array(
528
        'foreign_subscription_service_fkey' => array('foreign_service', array('service' => 'id')),
529 530
        'foreign_subscription_subscriber_fkey' => array('foreign_user', array('subscriber' => 'id', 'service' => 'service')),
        'foreign_subscription_subscribed_fkey' => array('foreign_user', array('subscribed' => 'id', 'service' => 'service')),
Brion Vibber's avatar
Brion Vibber committed
531
    ),
532
    'indexes' => array(
533 534
        'foreign_subscription_subscriber_idx' => array('service', 'subscriber'),
        'foreign_subscription_subscribed_idx' => array('service', 'subscribed'),
535 536 537 538 539
    ),
);

$schema['invitation'] = array(
    'fields' => array(
Brion Vibber's avatar
Brion Vibber committed
540 541 542 543 544
        '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'),
545
        'registered_user_id' => array('type' => 'int', 'not null' => false, 'description' => 'if the invitation is converted, who the new user is'),
546 547
    ),
    'primary key' => array('code'),
Brion Vibber's avatar
Brion Vibber committed
548
    'foreign keys' => array(
549
        'invitation_user_id_fkey' => array('user', array('user_id' => 'id')),
550
        'invitation_registered_user_id_fkey' => array('user', array('registered_user_id' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
551
    ),
552
    'indexes' => array(
Brion Vibber's avatar
Brion Vibber committed
553 554
        'invitation_address_idx' => array('address', 'address_type'),
        'invitation_user_id_idx' => array('user_id'),
555
        'invitation_registered_user_id_idx' => array('registered_user_id'),
556 557 558 559 560
    ),
);

$schema['message'] = array(
    'fields' => array(
561
        'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
562
        'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier'),
Brion Vibber's avatar
Brion Vibber committed
563 564
        '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'),
565 566 567 568
        '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'),
Brion Vibber's avatar
Brion Vibber committed
569
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
570
        'source' => array('type' => 'varchar', 'length' => 32, 'description' => 'source of comment, like "web", "im", or "clientname"'),
Brion Vibber's avatar
Brion Vibber committed
571
    ),
572
    'primary key' => array('id'),
573 574
    'unique keys' => array(
        'message_uri_key' => array('uri'),
575
    ),
Brion Vibber's avatar
Brion Vibber committed
576
    'foreign keys' => array(
577 578
        'message_from_profile_fkey' => array('profile', array('from_profile' => 'id')),
        'message_to_profile_fkey' => array('profile', array('to_profile' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
579
    ),
580 581 582 583 584 585 586 587 588 589
    '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(
Brion Vibber's avatar
Brion Vibber committed
590
    'description' => 'Obsolete; old entries here are converted to packed entries in the inbox table since 0.9',
591
    'fields' => array(
Brion Vibber's avatar
Brion Vibber committed
592 593
        'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user receiving the message'),
        'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice received'),
594
        'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice was created'),
Brion Vibber's avatar
Brion Vibber committed
595
        'source' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'reason it is in the inbox, 1=subscription'),
596 597
    ),
    'primary key' => array('user_id', 'notice_id'),
Brion Vibber's avatar
Brion Vibber committed
598
    'foreign keys' => array(
599 600
        'notice_inbox_user_id_fkey' => array('user', array('user_id' => 'id')),
        'notice_inbox_notice_id_fkey' => array('notice', array('notice_id' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
601
    ),
602
    'indexes' => array(
Brion Vibber's avatar
Brion Vibber committed
603
        'notice_inbox_notice_id_idx' => array('notice_id'),
604 605 606 607 608
    ),
);

$schema['profile_tag'] = array(
    'fields' => array(
Brion Vibber's avatar
Brion Vibber committed
609 610
        'tagger' => array('type' => 'int', 'not null' => true, 'description' => 'user making the tag'),
        'tagged' => array('type' => 'int', 'not null' => true, 'description' => 'profile tagged'),
611
        'tag' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'hash tag associated with this notice'),
Brion Vibber's avatar
Brion Vibber committed
612
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date the tag was added'),
613 614
    ),
    'primary key' => array('tagger', 'tagged', 'tag'),
Brion Vibber's avatar
Brion Vibber committed
615
    'foreign keys' => array(
Shashi Gowda's avatar
Shashi Gowda committed
616
        'profile_tag_tagger_fkey' => array('profile', array('tagger' => 'id')),
617
        'profile_tag_tagged_fkey' => array('profile', array('tagged' => 'id')),
Shashi Gowda's avatar
Shashi Gowda committed
618
        'profile_tag_tag_fkey' => array('profile_list', array('tag' => 'tag')),
Brion Vibber's avatar
Brion Vibber committed
619
    ),
620 621 622 623 624 625 626
    'indexes' => array(
        'profile_tag_modified_idx' => array('modified'),
        'profile_tag_tagger_tag_idx' => array('tagger', 'tag'),
        'profile_tag_tagged_idx' => array('tagged'),
    ),
);

Shashi Gowda's avatar
Shashi Gowda committed
627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696
$schema['profile_list'] = array(
    'fields' => array(
        'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
        'tagger' => array('type' => 'int', 'not null' => true, 'description' => 'user making the tag'),
        'tag' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'people tag'),
        'description' => array('type' => 'text', 'description' => 'description of the people tag'),
        'private' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'is this tag private'),

        'created' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date the tag was added'),
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date the tag was modified'),

        'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universal identifier'),
        'mainpage' => array('type' => 'varchar', 'length' => 255, 'description' => 'page to link to'),
        'tagged_count' => array('type' => 'int', 'default' => 0, 'description' => 'number of people tagged with this tag by this user'),
        'subscriber_count' => array('type' => 'int', 'default' => 0, 'description' => 'number of subscribers to this tag'),
    ),
    'primary key' => array('tagger', 'tag'),
    'unique keys' => array(
      'profile_list_id_key' => array('id')
    ),
    'foreign keys' => array(
        'profile_list_tagger_fkey' => array('profile', array('tagger' => 'id')),
    ),
    'indexes' => array(
        'profile_list_modified_idx' => array('modified'),
        'profile_list_tag_idx' => array('tag'),
        'profile_list_tagger_tag_idx' => array('tagger', 'tag'),
        'profile_list_tagged_count_idx' => array('tagged_count'),
        'profile_list_subscriber_count_idx' => array('subscriber_count'),
    ),
);

$schema['profile_tag_inbox'] = array(
    'description' => 'Many-many table listing notices associated with people tags.',
    'fields' => array(
        'profile_tag_id' => array('type' => 'int', 'not null' => true, 'description' => 'people tag 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('profile_tag_id', 'notice_id'),
    'foreign keys' => array(
        'profile_tag_inbox_profile_list_id_fkey' => array('profile_list', array('profile_tag_id' => 'id')),
        'profile_tag_inbox_notice_id_fkey' => array('notice', array('notice_id' => 'id')),
    ),
    'indexes' => array(
        'profile_tag_inbox_created_idx' => array('created'),
        'profile_tag_inbox_profile_tag_id_idx' => array('profile_tag_id'),
    ),
);

$schema['profile_tag_subscription'] = array(
    'fields' => array(
        'profile_tag_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile_tag'),
        'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table'),

        '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('profile_tag_id', 'profile_id'),
    'foreign keys' => array(
        'profile_tag_subscription_profile_list_id_fkey' => array('profile_list', array('profile_tag_id' => 'id')),
        'profile_tag_subscription_profile_id_fkey' => array('profile', array('profile_id' => 'id')),
    ),
    'indexes' => array(
        // @fixme probably we want a (profile_id, created) index here?
        'profile_tag_subscription_profile_id_idx' => array('profile_id'),
        'profile_tag_subscription_created_idx' => array('created'),
    ),
);

697 698
$schema['profile_block'] = array(
    'fields' => array(
Brion Vibber's avatar
Brion Vibber committed
699 700
        'blocker' => array('type' => 'int', 'not null' => true, 'description' => 'user making the block'),
        'blocked' => array('type' => 'int', 'not null' => true, 'description' => 'profile that is blocked'),
Brion Vibber's avatar
Brion Vibber committed
701
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date of blocking'),
702
    ),
Brion Vibber's avatar
Brion Vibber committed
703
    'foreign keys' => array(
704 705
        'profile_block_blocker_fkey' => array('user', array('blocker' => 'id')),
        'profile_block_blocked_fkey' => array('profile', array('blocked' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
706
    ),
707 708 709 710 711
    'primary key' => array('blocker', 'blocked'),
);

$schema['user_group'] = array(
    'fields' => array(
712
        'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
713 714 715 716 717 718 719 720 721 722 723

        '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'),
Brion Vibber's avatar
Brion Vibber committed
724
        'design_id' => array('type' => 'int', 'description' => 'id of a design'),
725 726

        'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
Brion Vibber's avatar
Brion Vibber committed
727
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
728 729 730

        'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universal identifier'),
        'mainpage' => array('type' => 'varchar', 'length' => 255, 'description' => 'page for group info to link to'),
731 732
        'join_policy' => array('type' => 'int', 'size' => 'tiny', 'description' => '0=open; 1=requires admin approval'),      
        'force_scope' => array('type' => 'int', 'size' => 'tiny', 'description' => '0=never,1=sometimes,-1=always'),
733 734
    ),
    'primary key' => array('id'),
735
    'unique keys' => array(
Brion Vibber's avatar
Brion Vibber committed
736
        'user_group_uri_key' => array('uri'),
737
    ),
Brion Vibber's avatar
Brion Vibber committed
738
    'foreign keys' => array(
739
        'user_group_design_id_fkey' => array('design', array('design_id' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
740
    ),
741 742 743 744 745 746 747
    'indexes' => array(
        'user_group_nickname_idx' => array('nickname'),
    ),
);

$schema['group_member'] = array(
    'fields' => array(
Brion Vibber's avatar
Brion Vibber committed
748 749
        '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'),
Brion Vibber's avatar
Brion Vibber committed
750
        'is_admin' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'is this user an admin?'),
751 752

        'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
Brion Vibber's avatar
Brion Vibber committed
753
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
754 755
    ),
    'primary key' => array('group_id', 'profile_id'),
Brion Vibber's avatar
Brion Vibber committed
756
    'foreign keys' => array(
757 758
        'group_member_group_id_fkey' => array('user_group', array('group_id' => 'id')),
        'group_member_profile_id_fkey' => array('profile', array('profile_id' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
759
    ),
760 761 762 763 764 765 766 767
    '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(
Brion Vibber's avatar
Brion Vibber committed
768
    // @fixme description for related_group?
769
    'fields' => array(
Brion Vibber's avatar
Brion Vibber committed
770 771
        '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'),
772 773 774 775

        'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
    ),
    'primary key' => array('group_id', 'related_group_id'),
Brion Vibber's avatar
Brion Vibber committed
776
    'foreign keys' => array(
777 778
        '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')),
Brion Vibber's avatar
Brion Vibber committed
779
    ),
780 781 782
);

$schema['group_inbox'] = array(
Brion Vibber's avatar
Brion Vibber committed
783
    'description' => 'Many-many table listing notices posted to a given group, or which groups a given notice was posted to.',
784
    'fields' => array(
Brion Vibber's avatar
Brion Vibber committed
785 786
        'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'group receiving the message'),
        'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice received'),
787 788 789
        'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice was created'),
    ),
    'primary key' => array('group_id', 'notice_id'),
Brion Vibber's avatar
Brion Vibber committed
790
    'foreign keys' => array(
791 792
        'group_inbox_group_id_fkey' => array('user_group', array('group_id' => 'id')),
        'group_inbox_notice_id_fkey' => array('notice', array('notice_id' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
793
    ),
794 795 796 797 798 799 800 801
    'indexes' => array(
        'group_inbox_created_idx' => array('created'),
        'group_inbox_notice_id_idx' => array('notice_id'),
    ),
);

$schema['file'] = array(
    'fields' => array(
802
        'id' => array('type' => 'serial', 'not null' => true),
803 804 805 806 807 808 809 810
        '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'),

Brion Vibber's avatar
Brion Vibber committed
811
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
812 813 814
    ),
    'primary key' => array('id'),
    'unique keys' => array(
815
        'file_url_key' => array('url'),
816 817 818 819 820
    ),
);

$schema['file_oembed'] = array(
    'fields' => array(
821
        'file_id' => array('type' => 'int', 'not null' => true, 'description' => 'oEmbed for that URL/file'),
822 823 824 825 826 827 828 829 830 831 832 833
        '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)'),
Brion Vibber's avatar
Brion Vibber committed
834
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
835 836
    ),
    'primary key' => array('file_id'),
Brion Vibber's avatar
Brion Vibber committed
837
    'foreign keys' => array(
838
         'file_oembed_file_id_fkey' => array('file', array('file_id' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
839
    ),
840 841 842 843
);

$schema['file_redirection'] = array(
    'fields' => array(
844
        'url' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'short URL (or any other kind of redirect) for file (id)'),
Brion Vibber's avatar
Brion Vibber committed
845
        'file_id' => array('type' => 'int', 'description' => 'short URL for what URL/file'),
846 847
        'redirections' => array('type' => 'int', 'description' => 'redirect count'),
        'httpcode' => array('type' => 'int', 'description' => 'HTTP status code (20x, 30x, etc.)'),
Brion Vibber's avatar
Brion Vibber committed
848
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
849 850
    ),
    'primary key' => array('url'),
Brion Vibber's avatar
Brion Vibber committed
851
    'foreign keys' => array(
852
         'file_redirection_file_id_fkey' => array('file' => array('file_id' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
853
    ),
854 855 856 857
);

$schema['file_thumbnail'] = array(
    'fields' => array(
858
        'file_id' => array('type' => 'int', 'not null' => true, 'description' => 'thumbnail for what URL/file'),
859 860 861
        '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'),
Brion Vibber's avatar
Brion Vibber committed
862
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
Brion Vibber's avatar
Brion Vibber committed
863
    ),
864
    'primary key' => array('file_id'),
Brion Vibber's avatar
Brion Vibber committed
865
    'foreign keys' => array(
866
        'file_thumbnail_file_id_fkey' => array('file', array('file_id' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
867
    ),
868
    'unique keys' => array(
869
        'file_thumbnail_url_key' => array('url'),
870 871 872 873 874
    ),
);

$schema['file_to_post'] = array(
    'fields' => array(
875 876
        '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'),
Brion Vibber's avatar
Brion Vibber committed
877
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
878 879
    ),
    'primary key' => array('file_id', 'post_id'),
Brion Vibber's avatar
Brion Vibber committed
880
    'foreign keys' => array(
881 882
        'file_to_post_file_id_fkey' => array('file', array('file_id' => 'id')),
        'file_to_post_post_id_fkey' => array('notice', array('post_id' => 'id')),
Brion Vibber's avatar
Brion Vibber committed
883
    ),
884 885 886 887 888 889 890
    'indexes' => array(
        'post_id_idx' => array('post_id'),
    ),
);

$schema['design'] = array(
    'fields' => array(
891
        'id' => array('type' => 'serial', 'not null' => true, 'description' => 'design ID'),
892 893 894 895 896 897
        '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'),
Brion Vibber's avatar
Brion Vibber committed
898
        'disposition' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'bit 1 = hide background image, bit 2 = display background image, bit 4 = tile background image'),
899 900 901 902 903 904
    ),
    'primary key' => array('id'),
);

$schema['group_block'] = array(
    'fields' => array(
Brion Vibber's avatar
Brion Vibber committed
905 906 907
        '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'),
Brion Vibber's avatar
Brion Vibber committed
908
        'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date of blocking'),
909 910
    ),
    'primary key' => array('group_id', 'blocked'),
Brion Vibber's avatar
Brion Vibber committed
911
    'foreign keys' => array(
912 913 914
        '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')),
Brion Vibber's avatar
Brion Vibber committed
915
    ),
916 917 918 919
);

$schema['group_alias'] = array(
    'fields' => array(
920
        'alias' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'additional nickname for the group'),
Brion Vibber's avatar
Brion Vibber committed
921
        'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'group profile is blocked from'),
Brion Vibber's avatar
Brion Vibber committed
922
        'modified'