7 * Drupal docs don't list a bool type, but it might be nice to use rather than 'tinyint'
8 * Note however that we use bitfields and things as well in tinyints, and PG's
9 * "bool" type isn't 100% compatible with 0/1 checks. Just keeping tinyints. :)
13 * MySQL 'timestamp' columns were formerly used for 'modified' files for their
14 * auto-updating properties. This didn't play well with changes to cache usage
15 * in 0.9.x, as we don't know the timestamp value at INSERT time and never
16 * have a chance to load it up again before caching.
18 * Current code should be setting 'created' and 'modified' fields explicitly;
19 * this also avoids mismatches between server and client timezone settings.
23 * got one or two things wanting a custom charset setting on a field?
25 * foreign keys are kinda funky...
26 * those specified in inline syntax (as all in the original .sql) are NEVER ENFORCED on mysql
27 * those made with an explicit 'foreign key' WITHIN INNODB and IF there's a proper index, do get enforced
28 * double-check what we've been doing on postgres?
31 $schema['profile'] = array(
32 'description' => 'local and remote users have profiles',
34 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
35 'nickname' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'nickname or username'),
36 'fullname' => array('type' => 'varchar', 'length' => 255, 'description' => 'display name'),
37 'profileurl' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL, cached so we dont regenerate'),
38 'homepage' => array('type' => 'varchar', 'length' => 255, 'description' => 'identifying URL'),
39 'bio' => array('type' => 'text', 'description' => 'descriptive biography'),
40 'location' => array('type' => 'varchar', 'length' => 255, 'description' => 'physical location'),
41 'lat' => array('type' => 'numeric', 'precision' => 10, 'scale' => 7, 'description' => 'latitude'),
42 'lon' => array('type' => 'numeric', 'precision' => 10, 'scale' => 7, 'description' => 'longitude'),
43 'location_id' => array('type' => 'int', 'description' => 'location id if possible'),
44 'location_ns' => array('type' => 'int', 'description' => 'namespace for location'),
46 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
47 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
49 'primary key' => array('id'),
51 'profile_nickname_idx' => array('nickname'),
53 'fulltext indexes' => array(
54 'nickname' => array('nickname', 'fullname', 'location', 'bio', 'homepage')
58 $schema['avatar'] = array(
60 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table'),
61 'original' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'uploaded by user or generated?'),
62 'width' => array('type' => 'int', 'not null' => true, 'description' => 'image width'),
63 'height' => array('type' => 'int', 'not null' => true, 'description' => 'image height'),
64 'mediatype' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'file type'),
65 'filename' => array('type' => 'varchar', 'length' => 255, 'description' => 'local filename, if local'),
66 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'avatar location'),
67 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
68 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
70 'primary key' => array('profile_id', 'width', 'height'),
71 'unique keys' => array(
72 'avatar_url_key' => array('url'),
74 'foreign keys' => array(
75 'avatar_profile_id_fkey' => array('profile', array('profile_id' => 'id')),
78 'avatar_profile_id_idx' => array('profile_id'),
82 $schema['sms_carrier'] = array(
84 'id' => array('type' => 'int', 'not null' => true, 'description' => 'primary key for SMS carrier'),
85 'name' => array('type' => 'varchar', 'length' => 64, 'description' => 'name of the carrier'),
86 'email_pattern' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'sprintf pattern for making an email address from a phone number'),
87 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
88 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
90 'primary key' => array('id'),
91 'unique keys' => array(
92 'sms_carrier_name_key' => array('name'),
96 $schema['user'] = array(
97 'description' => 'local users',
99 'id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table'),
100 'nickname' => array('type' => 'varchar', 'length' => 64, 'description' => 'nickname or username, duped in profile'),
101 'password' => array('type' => 'varchar', 'length' => 255, 'description' => 'salted password, can be null for OpenID users'),
102 'email' => array('type' => 'varchar', 'length' => 255, 'description' => 'email address for password recovery etc.'),
103 'incomingemail' => array('type' => 'varchar', 'length' => 255, 'description' => 'email address for post-by-email'),
104 'emailnotifysub' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Notify by email of subscriptions'),
105 'emailnotifyfav' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Notify by email of favorites'),
106 'emailnotifynudge' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Notify by email of nudges'),
107 'emailnotifymsg' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Notify by email of direct messages'),
108 'emailnotifyattn' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Notify by email of @-replies'),
109 'emailmicroid' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'whether to publish email microid'),
110 'language' => array('type' => 'varchar', 'length' => 50, 'description' => 'preferred language'),
111 'timezone' => array('type' => 'varchar', 'length' => 50, 'description' => 'timezone'),
112 'emailpost' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Post by email'),
113 'sms' => array('type' => 'varchar', 'length' => 64, 'description' => 'sms phone number'),
114 'carrier' => array('type' => 'int', 'description' => 'foreign key to sms_carrier'),
115 'smsnotify' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'whether to send notices to SMS'),
116 'smsreplies' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'whether to send notices to SMS on replies'),
117 'smsemail' => array('type' => 'varchar', 'length' => 255, 'description' => 'built from sms and carrier'),
118 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier, usually a tag URI'),
119 'autosubscribe' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'automatically subscribe to users who subscribe to us'),
120 'urlshorteningservice' => array('type' => 'varchar', 'length' => 50, 'default' => 'ur1.ca', 'description' => 'service to use for auto-shortening URLs'),
121 'inboxed' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'has an inbox been created for this user?'),
122 'design_id' => array('type' => 'int', 'description' => 'id of a design'),
123 'viewdesigns' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'whether to view user-provided designs'),
125 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
126 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
128 'primary key' => array('id'),
129 'unique keys' => array(
130 'user_nickname_key' => array('nickname'),
131 'user_email_key' => array('email'),
132 'user_incomingemail_key' => array('incomingemail'),
133 'user_sms_key' => array('sms'),
134 'user_uri_key' => array('uri'),
136 'foreign keys' => array(
137 'user_id_fkey' => array('profile', array('id' => 'id')),
138 'user_carrier_fkey' => array('sms_carrier', array('carrier' => 'id')),
139 'user_design_id_fkey' => array('design', array('design_id' => 'id')),
142 'user_smsemail_idx' => array('smsemail'),
146 $schema['remote_profile'] = array(
147 'description' => 'remote people (OMB)',
149 'id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table'),
150 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier, usually a tag URI'),
151 'postnoticeurl' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL we use for posting notices'),
152 'updateprofileurl' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL we use for updates to this profile'),
153 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
154 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
156 'primary key' => array('id'),
157 'unique keys' => array(
158 'remote_profile_uri_key' => array('uri'),
160 'foreign keys' => array(
161 'remote_profile_id_fkey' => array('profile', array('id' => 'id')),
165 $schema['subscription'] = array(
167 'subscriber' => array('type' => 'int', 'not null' => true, 'description' => 'profile listening'),
168 'subscribed' => array('type' => 'int', 'not null' => true, 'description' => 'profile being listened to'),
169 'jabber' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'deliver jabber messages'),
170 'sms' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'deliver sms messages'),
171 'token' => array('type' => 'varchar', 'length' => 255, 'description' => 'authorization token'),
172 'secret' => array('type' => 'varchar', 'length' => 255, 'description' => 'token secret'),
173 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
174 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
176 'primary key' => array('subscriber', 'subscribed'),
178 'subscription_subscriber_idx' => array('subscriber', 'created'),
179 'subscription_subscribed_idx' => array('subscribed', 'created'),
180 'subscription_token_idx' => array('token'),
184 $schema['notice'] = array(
186 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
187 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'who made the update'),
188 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier, usually a tag URI'),
189 'content' => array('type' => 'text', 'description' => 'update content'),
190 'rendered' => array('type' => 'text', 'description' => 'HTML version of the content'),
191 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL of any attachment (image, video, bookmark, whatever)'),
192 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
193 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
194 'reply_to' => array('type' => 'int', 'description' => 'notice replied to (usually a guess)'),
195 'is_local' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'notice was generated by a user'),
196 'source' => array('type' => 'varchar', 'length' => 32, 'description' => 'source of comment, like "web", "im", or "clientname"'),
197 'conversation' => array('type' => 'int', 'description' => 'id of root notice in this conversation'),
198 'lat' => array('type' => 'numeric', 'precision' => 10, 'scale' => 7, 'description' => 'latitude'),
199 'lon' => array('type' => 'numeric', 'precision' => 10, 'scale' => 7, 'description' => 'longitude'),
200 'location_id' => array('type' => 'int', 'description' => 'location id if possible'),
201 'location_ns' => array('type' => 'int', 'description' => 'namespace for location'),
202 'repeat_of' => array('type' => 'int', 'description' => 'notice this is a repeat of'),
204 'primary key' => array('id'),
205 'unique keys' => array(
206 'notice_uri_key' => array('uri'),
208 'foreign keys' => array(
209 'notice_profile_id_fkey' => array('profile', array('profile_id' => 'id')),
210 'notice_reply_to_fkey' => array('notice', array('reply_to' => 'id')),
211 'notice_conversation_fkey' => array('conversation', array('conversation' => 'id')), # note... used to refer to notice.id
212 'notice_repeat_of_fkey' => array('notice', array('repeat_of' => 'id')), # @fixme: what about repeats of deleted notices?
215 'notice_profile_id_idx' => array('profile_id', 'created', 'id'),
216 'notice_conversation_idx' => array('conversation'),
217 'notice_created_idx' => array('created'),
218 'notice_replyto_idx' => array('reply_to'),
219 'notice_repeatof_idx' => array('repeat_of'),
221 'fulltext indexes' => array(
222 'content' => array('content'),
226 $schema['notice_source'] = array(
228 'code' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'source code'),
229 'name' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'name of the source'),
230 'url' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'url to link to'),
231 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'date this record was created'),
232 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
234 'primary key' => array('code'),
237 $schema['reply'] = array(
239 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice that is the reply'),
240 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'profile replied to'),
241 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
242 'replied_id' => array('type' => 'int', 'description' => 'notice replied to (not used, see notice.reply_to)'),
244 'primary key' => array('notice_id', 'profile_id'),
245 'foreign keys' => array(
246 'reply_notice_id_fkey' => array('notice', array('notice_id' => 'id')),
247 'reply_profile_id_fkey' => array('profile', array('profile_id' => 'id')),
250 'reply_notice_id_idx' => array('notice_id'),
251 'reply_profile_id_idx' => array('profile_id'),
252 'reply_replied_id_idx' => array('replied_id'),
256 $schema['fave'] = array(
258 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice that is the favorite'),
259 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who likes this notice'),
260 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
262 'primary key' => array('notice_id', 'user_id'),
263 'foreign keys' => array(
264 'fave_notice_id_fkey' => array('notice', array('notice_id' => 'id')),
265 'fave_user_id_fkey' => array('profile', array('user_id' => 'id')), // note: formerly referenced notice.id, but we can now record remote users' favorites
268 'fave_notice_id_idx' => array('notice_id'),
269 'fave_user_id_idx' => array('user_id', 'modified'),
270 'fave_modified_idx' => array('modified'),
274 /* tables for OAuth */
276 $schema['consumer'] = array(
277 'description' => 'OAuth consumer record',
279 'consumer_key' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'unique identifier, root URL'),
280 'consumer_secret' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'secret value'),
281 'seed' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'seed for new tokens by this consumer'),
283 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
284 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
286 'primary key' => array('consumer_key'),
289 $schema['token'] = array(
290 'description' => 'OAuth token record',
292 'consumer_key' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'unique identifier, root URL'),
293 'tok' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'identifying value'),
294 'secret' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'secret value'),
295 'type' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 0, 'description' => 'request or access'),
296 'state' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'for requests, 0 = initial, 1 = authorized, 2 = used'),
297 'verifier' => array('type' => 'varchar', 'length' => 255, 'description' => 'verifier string for OAuth 1.0a'),
298 'verified_callback' => array('type' => 'varchar', 'length' => 255, 'description' => 'verified callback URL for OAuth 1.0a'),
300 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
301 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
303 'primary key' => array('consumer_key', 'tok'),
304 'foreign keys' => array(
305 'token_consumer_key_fkey' => array('consumer', array('consumer_key'=> 'consumer_key')),
309 $schema['nonce'] = array(
310 'description' => 'OAuth nonce record',
312 'consumer_key' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'unique identifier, root URL'),
313 'tok' => array('type' => 'char', 'length' => 32, 'description' => 'buggy old value, ignored'),
314 'nonce' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'nonce'),
315 'ts' => array('type' => 'datetime', 'not null' => true, 'description' => 'timestamp sent'),
317 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
318 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
320 'primary key' => array('consumer_key', 'ts', 'nonce'),
323 $schema['oauth_application'] = array(
324 'description' => 'OAuth application registration record',
326 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
327 'owner' => array('type' => 'int', 'not null' => true, 'description' => 'owner of the application'),
328 'consumer_key' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'application consumer key'),
329 'name' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'name of the application'),
330 'description' => array('type' => 'varchar', 'length' => 255, 'description' => 'description of the application'),
331 'icon' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'application icon'),
332 'source_url' => array('type' => 'varchar', 'length' => 255, 'description' => 'application homepage - used for source link'),
333 'organization' => array('type' => 'varchar', 'length' => 255, 'description' => 'name of the organization running the application'),
334 'homepage' => array('type' => 'varchar', 'length' => 255, 'description' => 'homepage for the organization'),
335 'callback_url' => array('type' => 'varchar', 'length' => 255, 'description' => 'url to redirect to after authentication'),
336 'type' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'type of app, 1 = browser, 2 = desktop'),
337 'access_type' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'default access type, bit 1 = read, bit 2 = write'),
338 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
339 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
341 'primary key' => array('id'),
342 'unique keys' => array(
343 'oauth_application_name_key' => array('name'), // in the long run, we should perhaps not force these unique, and use another source id
345 'foreign keys' => array(
346 'oauth_application_owner_fkey' => array('profile', array('owner' => 'id')), // Are remote users allowed to create oauth application records?
347 'oauth_application_consumer_key_fkey' => array('consumer', array('consumer_key' => 'consumer_key')),
351 $schema['oauth_application_user'] = array(
353 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'user of the application'),
354 'application_id' => array('type' => 'int', 'not null' => true, 'description' => 'id of the application'),
355 'access_type' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'access type, bit 1 = read, bit 2 = write'),
356 'token' => array('type' => 'varchar', 'length' => 255, 'description' => 'request or access token'),
357 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
358 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
360 'primary key' => array('profile_id', 'application_id'),
361 'foreign keys' => array(
362 'oauth_application_user_profile_id_fkey' => array('profile', array('profile_id' => 'id')),
363 'oauth_application_user_application_id_fkey' => array('oauth_application', array('application_id' => 'id')),
367 /* These are used by JanRain OpenID library */
369 $schema['oid_associations'] = array(
371 'server_url' => array('type' => 'blob', 'not null' => true),
372 'handle' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'default' => ''), // character set latin1,
373 'secret' => array('type' => 'blob'),
374 'issued' => array('type' => 'int'),
375 'lifetime' => array('type' => 'int'),
376 'assoc_type' => array('type' => 'varchar', 'length' => 64),
378 'primary key' => array(array('server_url', 255), 'handle'),
381 $schema['oid_nonces'] = array(
383 'server_url' => array('type' => 'varchar', 'length' => 2047),
384 'timestamp' => array('type' => 'int'),
385 'salt' => array('type' => 'char', 'length' => 40),
387 'unique keys' => array(
388 'oid_nonces_server_url_timestamp_salt_key' => array(array('server_url', 255), 'timestamp', 'salt'),
392 $schema['confirm_address'] = array(
394 'code' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'good random code'),
395 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who requested confirmation'),
396 'address' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'address (email, xmpp, SMS, etc.)'),
397 'address_extra' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'carrier ID, for SMS'),
398 'address_type' => array('type' => 'varchar', 'length' => 8, 'not null' => true, 'description' => 'address type ("email", "xmpp", "sms")'),
399 'claimed' => array('type' => 'datetime', 'description' => 'date this was claimed for queueing'),
400 'sent' => array('type' => 'datetime', 'description' => 'date this was sent for queueing'),
401 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
403 'primary key' => array('code'),
404 'foreign keys' => array(
405 'confirm_address_user_id_fkey' => array('user', array('user_id' => 'id')),
409 $schema['remember_me'] = array(
411 'code' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'good random code'),
412 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who is logged in'),
413 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
415 'primary key' => array('code'),
416 'foreign keys' => array(
417 'remember_me_user_id_fkey' => array('user', array('user_id' => 'id')),
421 $schema['queue_item'] = array(
423 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
424 'frame' => array('type' => 'blob', 'not null' => true, 'description' => 'data: object reference or opaque string'),
425 '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.
426 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
427 'claimed' => array('type' => 'datetime', 'description' => 'date this item was claimed'),
429 'primary key' => array('id'),
431 'queue_item_created_idx' => array('created'),
435 $schema['notice_tag'] = array(
436 'description' => 'Hash tags',
438 'tag' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'hash tag associated with this notice'),
439 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice tagged'),
440 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
442 'primary key' => array('tag', 'notice_id'),
443 'foreign keys' => array(
444 'notice_tag_notice_id_fkey' => array('notice', array('notice_id' => 'id')),
447 'notice_tag_created_idx' => array('created'),
448 'notice_tag_notice_id_idx' => array('notice_id'),
452 /* Synching with foreign services */
454 $schema['foreign_service'] = array(
456 'id' => array('type' => 'int', 'not null' => true, 'description' => 'numeric key for service'),
457 'name' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'name of the service'),
458 'description' => array('type' => 'varchar', 'length' => 255, 'description' => 'description'),
459 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
460 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
462 'primary key' => array('id'),
463 'unique keys' => array(
464 'foreign_service_name_key' => array('name'),
468 $schema['foreign_user'] = array(
470 'id' => array('type' => 'int', 'size' => 'big', 'not null' => true, 'description' => 'unique numeric key on foreign service'),
471 'service' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to service'),
472 'uri' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'identifying URI'),
473 'nickname' => array('type' => 'varchar', 'length' => 255, 'description' => 'nickname on foreign service'),
474 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
475 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
477 'primary key' => array('id', 'service'),
478 'foreign keys' => array(
479 'foreign_user_service_fkey' => array('foreign_service', array('service' => 'id')),
481 'unique keys' => array(
482 'foreign_user_uri_key' => array('uri'),
486 $schema['foreign_link'] = array(
488 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'link to user on this system, if exists'),
489 'foreign_id' => array('type' => 'int', 'size' => 'big', 'unsigned' => true, 'not null' => true, 'description' => 'link to user on foreign service, if exists'),
490 'service' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to service'),
491 'credentials' => array('type' => 'varchar', 'length' => 255, 'description' => 'authc credentials, typically a password'),
492 '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'),
493 'friendsync' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 2, 'description' => 'friend synchronization, bit 1 = sync outgoing, bit 2 = sync incoming'),
494 'profilesync' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 1, 'description' => 'profile synchronization, bit 1 = sync outgoing, bit 2 = sync incoming'),
495 'last_noticesync' => array('type' => 'datetime', 'description' => 'last time notices were imported'),
496 'last_friendsync' => array('type' => 'datetime', 'description' => 'last time friends were imported'),
497 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
498 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
500 'primary key' => array('user_id', 'foreign_id', 'service'),
501 'foreign keys' => array(
502 'foreign_link_user_id_fkey' => array('user', array('user_id' => 'id')),
503 'foreign_link_foreign_id_fkey' => array('foreign_user', array('foreign_id' => 'id', 'service' => 'service')),
504 'foreign_link_service_fkey' => array('foreign_service', array('service' => 'id')),
507 'foreign_user_user_id_idx' => array('user_id'),
511 $schema['foreign_subscription'] = array(
513 'service' => array('type' => 'int', 'not null' => true, 'description' => 'service where relationship happens'),
514 'subscriber' => array('type' => 'int', 'size' => 'big', 'not null' => true, 'description' => 'subscriber on foreign service'),
515 'subscribed' => array('type' => 'int', 'size' => 'big', 'not null' => true, 'description' => 'subscribed user'),
516 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
518 'primary key' => array('service', 'subscriber', 'subscribed'),
519 'foreign keys' => array(
520 'foreign_subscription_service_fkey' => array('foreign_service', array('service' => 'id')),
521 'foreign_subscription_subscriber_fkey' => array('foreign_user', array('subscriber' => 'id', 'service' => 'service')),
522 'foreign_subscription_subscribed_fkey' => array('foreign_user', array('subscribed' => 'id', 'service' => 'service')),
525 'foreign_subscription_subscriber_idx' => array('service', 'subscriber'),
526 'foreign_subscription_subscribed_idx' => array('service', 'subscribed'),
530 $schema['invitation'] = array(
532 'code' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'random code for an invitation'),
533 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'who sent the invitation'),
534 'address' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'invitation sent to'),
535 'address_type' => array('type' => 'varchar', 'length' => 8, 'not null' => true, 'description' => 'address type ("email", "xmpp", "sms")'),
536 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
538 'primary key' => array('code'),
539 'foreign keys' => array(
540 'invitation_user_id_fkey' => array('user', array('user_id' => 'id')),
543 'invitation_address_idx' => array('address', 'address_type'),
544 'invitation_user_id_idx' => array('user_id'),
548 $schema['message'] = array(
550 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
551 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier'),
552 'from_profile' => array('type' => 'int', 'not null' => true, 'description' => 'who the message is from'),
553 'to_profile' => array('type' => 'int', 'not null' => true, 'description' => 'who the message is to'),
554 'content' => array('type' => 'text', 'description' => 'message content'),
555 'rendered' => array('type' => 'text', 'description' => 'HTML version of the content'),
556 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL of any attachment (image, video, bookmark, whatever)'),
557 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
558 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
559 'source' => array('type' => 'varchar', 'length' => 32, 'description' => 'source of comment, like "web", "im", or "clientname"'),
561 'primary key' => array('id'),
562 'unique keys' => array(
563 'message_uri_key' => array('uri'),
565 'foreign keys' => array(
566 'message_from_profile_fkey' => array('profile', array('from_profile' => 'id')),
567 'message_to_profile_fkey' => array('profile', array('to_profile' => 'id')),
570 // @fixme these are really terrible indexes, since you can only sort on one of them at a time.
571 // looks like we really need a (to_profile, created) for inbox and a (from_profile, created) for outbox
572 'message_from_idx' => array('from_profile'),
573 'message_to_idx' => array('to_profile'),
574 'message_created_idx' => array('created'),
578 $schema['notice_inbox'] = array(
579 'description' => 'Obsolete; old entries here are converted to packed entries in the inbox table since 0.9',
581 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user receiving the message'),
582 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice received'),
583 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice was created'),
584 'source' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'reason it is in the inbox, 1=subscription'),
586 'primary key' => array('user_id', 'notice_id'),
587 'foreign keys' => array(
588 'notice_inbox_user_id_fkey' => array('user', array('user_id' => 'id')),
589 'notice_inbox_notice_id_fkey' => array('notice', array('notice_id' => 'id')),
592 'notice_inbox_notice_id_idx' => array('notice_id'),
596 $schema['profile_tag'] = array(
598 'tagger' => array('type' => 'int', 'not null' => true, 'description' => 'user making the tag'),
599 'tagged' => array('type' => 'int', 'not null' => true, 'description' => 'profile tagged'),
600 'tag' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'hash tag associated with this notice'),
601 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the tag was added'),
603 'primary key' => array('tagger', 'tagged', 'tag'),
604 'foreign keys' => array(
605 'profile_tag_tagger_fkey' => array('user', array('tagger' => 'id')),
606 'profile_tag_tagged_fkey' => array('profile', array('tagged' => 'id')),
609 'profile_tag_modified_idx' => array('modified'),
610 'profile_tag_tagger_tag_idx' => array('tagger', 'tag'),
611 'profile_tag_tagged_idx' => array('tagged'),
615 $schema['profile_block'] = array(
617 'blocker' => array('type' => 'int', 'not null' => true, 'description' => 'user making the block'),
618 'blocked' => array('type' => 'int', 'not null' => true, 'description' => 'profile that is blocked'),
619 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date of blocking'),
621 'foreign keys' => array(
622 'profile_block_blocker_fkey' => array('user', array('blocker' => 'id')),
623 'profile_block_blocked_fkey' => array('profile', array('blocked' => 'id')),
625 'primary key' => array('blocker', 'blocked'),
628 $schema['user_group'] = array(
630 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
632 'nickname' => array('type' => 'varchar', 'length' => 64, 'description' => 'nickname for addressing'),
633 'fullname' => array('type' => 'varchar', 'length' => 255, 'description' => 'display name'),
634 'homepage' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL, cached so we dont regenerate'),
635 'description' => array('type' => 'text', 'description' => 'group description'),
636 'location' => array('type' => 'varchar', 'length' => 255, 'description' => 'related physical location, if any'),
638 'original_logo' => array('type' => 'varchar', 'length' => 255, 'description' => 'original size logo'),
639 'homepage_logo' => array('type' => 'varchar', 'length' => 255, 'description' => 'homepage (profile) size logo'),
640 'stream_logo' => array('type' => 'varchar', 'length' => 255, 'description' => 'stream-sized logo'),
641 'mini_logo' => array('type' => 'varchar', 'length' => 255, 'description' => 'mini logo'),
642 'design_id' => array('type' => 'int', 'description' => 'id of a design'),
644 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
645 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
647 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universal identifier'),
648 'mainpage' => array('type' => 'varchar', 'length' => 255, 'description' => 'page for group info to link to'),
650 'primary key' => array('id'),
651 'unique keys' => array(
652 'user_group_uri_key' => array('uri'),
654 'foreign keys' => array(
655 'user_group_design_id_fkey' => array('design', array('design_id' => 'id')),
658 'user_group_nickname_idx' => array('nickname'),
662 $schema['group_member'] = array(
664 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to user_group'),
665 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table'),
666 'is_admin' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'is this user an admin?'),
668 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
669 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
671 'primary key' => array('group_id', 'profile_id'),
672 'foreign keys' => array(
673 'group_member_group_id_fkey' => array('user_group', array('group_id' => 'id')),
674 'group_member_profile_id_fkey' => array('profile', array('profile_id' => 'id')),
677 // @fixme probably we want a (profile_id, created) index here?
678 'group_member_profile_id_idx' => array('profile_id'),
679 'group_member_created_idx' => array('created'),
683 $schema['related_group'] = array(
684 // @fixme description for related_group?
686 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to user_group'),
687 'related_group_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to user_group'),
689 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
691 'primary key' => array('group_id', 'related_group_id'),
692 'foreign keys' => array(
693 'related_group_group_id_fkey' => array('user_group', array('group_id' => 'id')),
694 'related_group_related_group_id_fkey' => array('user_group', array('related_group_id' => 'id')),
698 $schema['group_inbox'] = array(
699 'description' => 'Many-many table listing notices posted to a given group, or which groups a given notice was posted to.',
701 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'group receiving the message'),
702 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice received'),
703 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice was created'),
705 'primary key' => array('group_id', 'notice_id'),
706 'foreign keys' => array(
707 'group_inbox_group_id_fkey' => array('user_group', array('group_id' => 'id')),
708 'group_inbox_notice_id_fkey' => array('notice', array('notice_id' => 'id')),
711 'group_inbox_created_idx' => array('created'),
712 'group_inbox_notice_id_idx' => array('notice_id'),
716 $schema['file'] = array(
718 'id' => array('type' => 'serial', 'not null' => true),
719 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'destination URL after following redirections'),
720 'mimetype' => array('type' => 'varchar', 'length' => 50, 'description' => 'mime type of resource'),
721 'size' => array('type' => 'int', 'description' => 'size of resource when available'),
722 'title' => array('type' => 'varchar', 'length' => 255, 'description' => 'title of resource when available'),
723 'date' => array('type' => 'int', 'description' => 'date of resource according to http query'),
724 'protected' => array('type' => 'int', 'description' => 'true when URL is private (needs login)'),
725 'filename' => array('type' => 'varchar', 'length' => 255, 'description' => 'if a local file, name of the file'),
727 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
729 'primary key' => array('id'),
730 'unique keys' => array(
731 'file_url_key' => array('url'),
735 $schema['file_oembed'] = array(
737 'file_id' => array('type' => 'int', 'not null' => true, 'description' => 'oEmbed for that URL/file'),
738 'version' => array('type' => 'varchar', 'length' => 20, 'description' => 'oEmbed spec. version'),
739 'type' => array('type' => 'varchar', 'length' => 20, 'description' => 'oEmbed type: photo, video, link, rich'),
740 'mimetype' => array('type' => 'varchar', 'length' => 50, 'description' => 'mime type of resource'),
741 'provider' => array('type' => 'varchar', 'length' => 50, 'description' => 'name of this oEmbed provider'),
742 'provider_url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL of this oEmbed provider'),
743 'width' => array('type' => 'int', 'description' => 'width of oEmbed resource when available'),
744 'height' => array('type' => 'int', 'description' => 'height of oEmbed resource when available'),
745 'html' => array('type' => 'text', 'description' => 'html representation of this oEmbed resource when applicable'),
746 'title' => array('type' => 'varchar', 'length' => 255, 'description' => 'title of oEmbed resource when available'),
747 'author_name' => array('type' => 'varchar', 'length' => 50, 'description' => 'author name for this oEmbed resource'),
748 'author_url' => array('type' => 'varchar', 'length' => 255, 'description' => 'author URL for this oEmbed resource'),
749 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL for this oEmbed resource when applicable (photo, link)'),
750 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
752 'primary key' => array('file_id'),
753 'foreign keys' => array(
754 'file_oembed_file_id_fkey' => array('file', array('file_id' => 'id')),
758 $schema['file_redirection'] = array(
760 'url' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'short URL (or any other kind of redirect) for file (id)'),
761 'file_id' => array('type' => 'int', 'description' => 'short URL for what URL/file'),
762 'redirections' => array('type' => 'int', 'description' => 'redirect count'),
763 'httpcode' => array('type' => 'int', 'description' => 'HTTP status code (20x, 30x, etc.)'),
764 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
766 'primary key' => array('url'),
767 'foreign keys' => array(
768 'file_redirection_file_id_fkey' => array('file' => array('file_id' => 'id')),
772 $schema['file_thumbnail'] = array(
774 'file_id' => array('type' => 'int', 'not null' => true, 'description' => 'thumbnail for what URL/file'),
775 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL of thumbnail'),
776 'width' => array('type' => 'int', 'description' => 'width of thumbnail'),
777 'height' => array('type' => 'int', 'description' => 'height of thumbnail'),
778 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
780 'primary key' => array('file_id'),
781 'foreign keys' => array(
782 'file_thumbnail_file_id_fkey' => array('file', array('file_id' => 'id')),
784 'unique keys' => array(
785 'file_thumbnail_url_key' => array('url'),
789 $schema['file_to_post'] = array(
791 'file_id' => array('type' => 'int', 'not null' => true, 'description' => 'id of URL/file'),
792 'post_id' => array('type' => 'int', 'not null' => true, 'description' => 'id of the notice it belongs to'),
793 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
795 'primary key' => array('file_id', 'post_id'),
796 'foreign keys' => array(
797 'file_to_post_file_id_fkey' => array('file', array('file_id' => 'id')),
798 'file_to_post_post_id_fkey' => array('notice', array('post_id' => 'id')),
801 'post_id_idx' => array('post_id'),
805 $schema['design'] = array(
807 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'design ID'),
808 'backgroundcolor' => array('type' => 'int', 'description' => 'main background color'),
809 'contentcolor' => array('type' => 'int', 'description' => 'content area background color'),
810 'sidebarcolor' => array('type' => 'int', 'description' => 'sidebar background color'),
811 'textcolor' => array('type' => 'int', 'description' => 'text color'),
812 'linkcolor' => array('type' => 'int', 'description' => 'link color'),
813 'backgroundimage' => array('type' => 'varchar', 'length' => 255, 'description' => 'background image, if any'),
814 'disposition' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'bit 1 = hide background image, bit 2 = display background image, bit 4 = tile background image'),
816 'primary key' => array('id'),
819 $schema['group_block'] = array(
821 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'group profile is blocked from'),
822 'blocked' => array('type' => 'int', 'not null' => true, 'description' => 'profile that is blocked'),
823 'blocker' => array('type' => 'int', 'not null' => true, 'description' => 'user making the block'),
824 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date of blocking'),
826 'primary key' => array('group_id', 'blocked'),
827 'foreign keys' => array(
828 'group_block_group_id_fkey' => array('user_group', array('group_id' => 'id')),
829 'group_block_blocked_fkey' => array('profile', array('blocked' => 'id')),
830 'group_block_blocker_fkey' => array('user', array('blocker' => 'id')),
834 $schema['group_alias'] = array(
836 'alias' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'additional nickname for the group'),
837 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'group profile is blocked from'),
838 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date alias was created'),
840 'primary key' => array('alias'),
841 'foreign keys' => array(
842 'group_alias_group_id_fkey' => array('user_group', array('group_id' => 'id')),
845 'group_alias_group_id_idx' => array('group_id'),
849 $schema['session'] = array(
851 'id' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'session ID'),
852 'session_data' => array('type' => 'text', 'description' => 'session data'),
853 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
854 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
856 'primary key' => array('id'),
858 'session_modified_idx' => array('modified'),
862 $schema['deleted_notice'] = array(
864 'id' => array('type' => 'int', 'not null' => true, 'description' => 'identity of notice'),
865 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'author of the notice'),
866 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier, usually a tag URI'),
867 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice record was created'),
868 'deleted' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice record was created'),
870 'primary key' => array('id'),
871 'unique keys' => array(
872 'deleted_notice_uri_key' => array('uri'),
875 'deleted_notice_profile_id_idx' => array('profile_id'),
879 $schema['config'] = array(
881 'section' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'default' => '', 'description' => 'configuration section'),
882 'setting' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'default' => '', 'description' => 'configuration setting'),
883 'value' => array('type' => 'varchar', 'length' => 255, 'description' => 'configuration value'),
885 'primary key' => array('section', 'setting'),
888 $schema['profile_role'] = array(
890 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'account having the role'),
891 'role' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'string representing the role'),
892 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the role was granted'),
894 'primary key' => array('profile_id', 'role'),
895 'foreign keys' => array(
896 'profile_role_profile_id_fkey' => array('profile', array('profile_id' => 'id')),
900 $schema['location_namespace'] = array(
902 'id' => array('type' => 'int', 'not null' => true, 'description' => 'identity for this namespace'),
903 'description' => array('type' => 'varchar', 'length' => 255, 'description' => 'description of the namespace'),
904 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the record was created'),
905 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
907 'primary key' => array('id'),
910 $schema['login_token'] = array(
912 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user owning this token'),
913 'token' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'token useable for logging in'),
914 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
915 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
917 'primary key' => array('user_id'),
918 'foreign keys' => array(
919 'login_token_user_id_fkey' => array('user', array('user_id' => 'id')),
923 $schema['user_location_prefs'] = array(
925 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who has the preference'),
926 'share_location' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'Whether to share location data'),
927 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
928 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
930 'primary key' => array('user_id'),
931 'foreign keys' => array(
932 'user_location_prefs_user_id_fkey' => array('user', array('user_id' => 'id')),
936 $schema['inbox'] = array(
938 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user receiving the notice'),
939 'notice_ids' => array('type' => 'blob', 'description' => 'packed list of notice ids'),
941 'primary key' => array('user_id'),
942 'foreign keys' => array(
943 'inbox_user_id_fkey' => array('user', array('user_id' => 'id')),
947 // @fixme possibly swap this for a more general prefs table?
948 $schema['user_im_prefs'] = array(
950 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user'),
951 'screenname' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'screenname on this service'),
952 'transport' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'transport (ex xmpp, aim)'),
953 'notify' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 0, 'description' => 'Notify when a new notice is sent'),
954 'replies' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 0, 'description' => 'Send replies from people not subscribed to'),
955 'microid' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 1, 'description' => 'Publish a MicroID'),
956 'updatefrompresence' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 0, 'description' => 'Send replies from people not subscribed to.'),
957 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
958 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
960 'primary key' => array('user_id', 'transport'),
961 'unique keys' => array(
962 'transport_screenname_key' => array('transport', 'screenname'),
964 'foreign keys' => array(
965 'user_im_prefs_user_id_fkey' => array('user', array('user_id' => 'id')),
969 $schema['conversation'] = array(
971 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
972 'uri' => array('type' => 'varchar', 'length' => 225, 'description' => 'URI of the conversation'),
973 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
974 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
976 'primary key' => array('id'),
977 'unique keys' => array(
978 'conversation_uri_key' => array('uri'),
982 $schema['local_group'] = array(
983 'description' => 'Record for a user group on the local site, with some additional info not in user_group',
985 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'group represented'),
986 'nickname' => array('type' => 'varchar', 'length' => 64, 'description' => 'group represented'),
988 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
989 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
991 'primary key' => array('group_id'),
992 'foreign keys' => array(
993 'local_group_group_id_fkey' => array('user_group', array('group_id' => 'id')),
995 'unique keys' => array(
996 'local_group_nickname_key' => array('nickname'),
1000 $schema['user_urlshortener_prefs'] = array(
1002 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user'),
1003 'urlshorteningservice' => array('type' => 'varchar', 'length' => 50, 'default' => 'ur1.ca', 'description' => 'service to use for auto-shortening URLs'),
1004 'maxurllength' => array('type' => 'int', 'not null' => true, 'description' => 'urls greater than this length will be shortened, 0 = always, null = never'),
1005 'maxnoticelength' => array('type' => 'int', 'not null' => true, 'description' => 'notices with content greater than this value will have all urls shortened, 0 = always, null = never'),
1007 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
1008 'modified' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was modified'),
1010 'primary key' => array('user_id'),
1011 'foreign keys' => array(
1012 'user_urlshortener_prefs_user_id_fkey' => array('user', array('user_id' => 'id')),