3 /* local and remote users have profiles */
5 $schema['profile'] = array(
7 'id' => array('type' => 'serial', 'not null' => true, 'description' => 'unique identifier'),
8 'nickname' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'nickname or username'),
9 'fullname' => array('type' => 'varchar', 'length' => 255, 'description' => 'display name'),
10 'profileurl' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL, cached so we dont regenerate'),
11 'homepage' => array('type' => 'varchar', 'length' => 255, 'description' => 'identifying URL'),
12 'bio' => array('type' => 'text', 'description' => 'descriptive biography'),
13 'location' => array('type' => 'varchar', 'length' => 255, 'description' => 'physical location'),
14 'lat' => array('type' => 'decimal', 'length' => '10,7', 'description' => 'latitude'),
15 'lon' => array('type' => 'decimal', 'length => '10,7', 'description' => 'longitude'),
16 'location_id' => array('type' => 'int', 'description' => 'location id if possible'),
17 'location_ns' => array('type' => 'int', 'description' => 'namespace for location'),
19 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
20 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
22 'primary key' => array('id'),
24 'profile_nickname_idx' => array('nickname'),
25 'FULLTEXT' => array('nickname', 'fullname', 'location', 'bio', 'homepage') // ??
27 // Any way to specify that this table needs to be myisam if using the fulltext?
30 $schema['avatar'] = array(
32 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table',
33 'original' => array('type' => 'boolean', 'default' => false, 'description' => 'uploaded by user or generated?'),
34 'width' => array('type' => 'int', 'not null' => true, 'description' => 'image width'),
35 'height' => array('type' => 'int', 'not null' => true, 'description' => 'image height'),
36 'mediatype' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'file type'),
37 'filename' => array('type' => 'varchar', 'length' => 255, 'description' => 'local filename, if local'),
38 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'avatar location'),
39 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
40 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
42 'primary key' => array('profile_id', 'width', 'height'),
43 'unique keys' => array(
44 'avatar_url_idx' => array('url'),
46 'foreign keys' => array(
47 'profile_id' => array('profile' => 'id'),
50 'avatar_profile_id_idx' => array('profile_id'),
54 $schema['sms_carrier'] = array(
56 'id' => array('type' => 'int', 'description' => 'primary key for SMS carrier'),
57 'name' => array('type' => 'varchar', 'length' => 64, 'description' => 'name of the carrier'),
58 'email_pattern' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'sprintf pattern for making an email address from a phone number'),
59 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
60 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
62 'primary key' => array('id'),
63 'unique keys' => array(
64 'sms_carrier_name_idx' => array('name'),
68 $schema['user'] = array(
69 'description' => 'local users',
71 'id' => array('type' => 'int', 'description' => 'foreign key to profile table'),
72 'nickname' => array('type' => 'varchar', 'length' => 64, 'description' => 'nickname or username, duped in profile'),
73 'password' => array('type' => 'varchar', 'length' => 255, 'description' => 'salted password, can be null for OpenID users'),
74 'email' => array('type' => 'varchar', 'length' => 255, 'description' => 'email address for password recovery etc.'),
75 'incomingemail' => array('type' => 'varchar', 'length' => 255, 'description' => 'email address for post-by-email'),
76 'emailnotifysub' => array('type' => 'bool', 'default' => 1, 'description' => 'Notify by email of subscriptions'),
77 'emailnotifyfav' => array('type' => 'bool', 'default' => 1, 'description' => 'Notify by email of favorites'),
78 'emailnotifynudge' => array('type' => 'bool', 'default' => 1, 'description' => 'Notify by email of nudges'),
79 'emailnotifymsg' => array('type' => 'bool', 'default' => 1, 'description' => 'Notify by email of direct messages'),
80 'emailnotifyattn' => array('type' => 'bool', 'default' => 1, 'description' => 'Notify by email of @-replies'),
81 'emailmicroid' => array('type' => 'bool', 'default' => 1, 'description' => 'whether to publish email microid'),
82 'language' => array('type' => 'varchar', 'length' => 50, 'description' => 'preferred language'),
83 'timezone' => array('type' => 'varchar', 'length' => 50, 'description' => 'timezone'),
84 'emailpost' => array('type' => 'bool', 'default' => 1, 'description' => 'Post by email'),
85 'sms' => array('type' => 'varchar', 'length' => 64, 'description' => 'sms phone number'),
86 'carrier' => array('type' => 'int', 'description' => 'foreign key to sms_carrier'),
87 'smsnotify' => array('type' => 'bool', 'default' => 0, 'description' => 'whether to send notices to SMS'),
88 'smsreplies' => array('type' => 'bool', 'default' => 0, 'description' => 'whether to send notices to SMS on replies'),
89 'smsemail' => array('type' => 'varchar', 'length' => 255, 'description' => 'built from sms and carrier'),
90 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier, usually a tag URI'),
91 'autosubscribe' => array('type' => 'bool', 'default' => 0, 'description' => 'automatically subscribe to users who subscribe to us'),
92 'urlshorteningservice' => array('type' => 'varchar', 'length' => 50, default 'ur1.ca' 'description' => 'service to use for auto-shortening URLs'),
93 'inboxed' => array('type' => 'bool', 'default' => 0, 'description' => 'has an inbox been created for this user?'),
94 'design_id' => array('type' => 'int', 'description' => 'id of a design'),
95 'viewdesigns' => array('type' => 'bool', 'default' => 1, 'description' => 'whether to view user-provided designs'),
97 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
98 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
100 'primary key' => array('id'),
101 'unique keys' => array(
102 'user_name_idx' => array('name'),
103 'user_email_idx' => array('email'),
104 'user_incomingemail_idx' => array('incomingemail'),
105 'user_sms_idx' => array('sms'),
106 'user_uri_idx' => array('uri'),
108 'foreign keys' => array(
109 'id' => array('profile' => 'id'),
110 'carrier' => array('sms_carrier' => 'id'),
111 'design_id' => array('desgin' => 'id'),
114 'user_smsemail_idx' => array('smsemail'),
118 $schema['remote_profile'] = array(
119 'description' => 'remote people (OMB)',
121 'id' => array('type' => 'int', 'description' => 'foreign key to profile table'),
122 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier, usually a tag URI'),
123 'postnoticeurl' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL we use for posting notices'),
124 'updateprofileurl' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL we use for updates to this profile'),
125 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
126 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
128 'primary key' => array('id'),
129 'unique keys' => array(
130 'remote_profile_uri_idx' => array('uri'),
132 'foreign keys' => array(
133 'id' => array('profile' => 'id'),
137 $schema['subscription'] = array(
139 'subscriber' => array('type' => 'int', 'not null' => true, 'description' => 'profile listening'),
140 'subscribed' => array('type' => 'int', 'not null' => true, 'description' => 'profile being listened to'),
141 'jabber' => array('type' => 'bool', 'default' => 1, 'description' => 'deliver jabber messages'),
142 'sms' => array('type' => 'bool', 'default' => 1, 'description' => 'deliver sms messages'),
143 'token' => array('type' => 'varchar', 'length' => 255, 'description' => 'authorization token'),
144 'secret' => array('type' => 'varchar', 'length' => 255, 'description' => 'token secret'),
145 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
146 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
148 'primary key' => array('subscriber', 'subscribed'),
150 'subscription_subscriber_idx' => array('subscriber', 'created'),
151 'subscription_subscribed_idx' => array('subscribed', 'created'),
152 'subscription_token_idx' => array('token'),
156 $schema['notice'] = array(
158 'id' => array('type' => 'serial', 'description' => 'unique identifier'),
159 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'who made the update'),
160 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier, usually a tag URI'),
161 'content' => array('type' => 'text', 'description' => 'update content'),
162 'rendered' => array('type' => 'text', 'description' => 'HTML version of the content'),
163 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL of any attachment (image, video, bookmark, whatever)'),
164 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
165 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
166 'reply_to' => array('type' => 'int', 'description' => 'notice replied to (usually a guess)'),
167 'is_local' => array('type' => 'bool', 'default' => 0, 'description' => 'notice was generated by a user'),
168 'source' => array('type' => 'varchar', 'length' => 32, 'description' => 'source of comment, like "web", "im", or "clientname"'),
169 'conversation' => array('type' => 'int', 'description' => 'id of root notice in this conversation'),
170 'lat' => array('type' => 'numeric', 'precision' => 10, 'scale' => 7, 'description' => 'latitude'),
171 'lon' => array('type' => 'numeric', 'precision' => 10, 'scale' => 7, 'description' => 'longitude'),
172 'location_id' => array('type' => 'int', 'description' => 'location id if possible'),
173 'location_ns' => array('type' => 'int', 'description' => 'namespace for location'),
174 'repeat_of' => array('type' => 'int', 'description' => 'notice this is a repeat of'),
176 'primary key' => array('id'),
177 'unique keys' => array(
178 'notice_uri_idx' => array('uri'),
180 'foreign keys' => array(
181 'profile_id' => array('profile' => 'id'),
182 'reply_to' => array('notice', 'id'),
183 'conversation' => array('conversation', 'id'), # note... used to refer to notice.id
184 'repeat_of' => array('notice', 'id'), # @fixme: what about repeats of deleted notices?
187 'notice_profile_id_idx' => array('profile_id,created,id'),
188 'notice_conversation_idx' => array('conversation'),
189 'notice_created_idx' => array('created'),
190 'notice_replyto_idx' => array('reply_to'),
191 'notice_repeatof_idx' => array('repeat_of'),
192 'FULLTEXT' => array('content'),
196 $schema['notice_source'] = array(
198 'code' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'source code'),
199 'name' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'name of the source'),
200 'url' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'url to link to'),
201 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'date this record was created'),
202 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
204 'primary key' => array('code'),
207 $schema['reply'] = array(
209 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice that is the reply'),
210 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'profile replied to'),
211 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
212 'replied_id' => array('type' => 'int', 'description' => 'notice replied to (not used, see notice.reply_to)'),
214 'primary key' => array('notice_id', 'profile_id'),
215 'foreign keys' => array(
216 'notice_id' => array('notice' => 'id'),
217 'profile_id' => array('profile' => 'id'),
220 'reply_notice_id_idx' => array('notice_id'),
221 'reply_profile_id_idx' => array('profile_id'),
222 'reply_replied_id_idx' => array('replied_id'),
226 $schema['fave'] = array(
228 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice that is the favorite'),
229 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who likes this notice'),
230 'modified' => array('type' => 'timestamp', 'not null' => true, 'description' => 'date this record was modified'),
232 'primary key' => array('notice_id', 'user_id'),
233 'foreign keys' => array(
234 'notice_id' => array('notice', 'id'),
235 'user_id' => array('profile', 'id'), // note: formerly referenced notice.id, but we can now record remote users' favorites
238 'fave_notice_id_idx' => array('notice_id'),
239 'fave_user_id_idx' => array('user_id', 'modified'),
240 'fave_modified_idx' => array('modified'),
244 /* tables for OAuth */
246 $schema['consumer'] = array(
247 'description' => 'OAuth consumer record',
249 'consumer_key' => array('type' => 'varchar', 'length' => 255, 'description' => 'unique identifier, root URL'),
250 'consumer_secret' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'secret value'),
251 'seed' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'seed for new tokens by this consumer'),
253 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
254 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
256 'primary key' => array('consumer_key'),
259 $schema['token'] = array(
260 'description' => 'OAuth token record',
262 'consumer_key' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'unique identifier, root URL'),
263 'tok' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'identifying value'),
264 'secret' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'secret value'),
265 'type' => array('type' => 'bool', 'not null' => true, 'default' => 0, 'description' => 'request or access'),
266 'state' => array('type' => 'bool', 'default' => 0, 'description' => 'for requests, 0 = initial, 1 = authorized, 2 = used'),
267 'verifier' => array('type' => 'varchar', 'length' => 255, 'description' => 'verifier string for OAuth 1.0a'),
268 'verified_callback' => array('type' => 'varchar', 'length' => 255, 'description' => 'verified callback URL for OAuth 1.0a'),
270 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
271 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
273 'primary key' => array('consumer_key', 'tok'),
274 'foreign keys' => array(
275 'consumer_key' => array('consumer' => 'consumer_key'),
279 $schema['nonce'] = array(
280 'description' => 'OAuth nonce record',
282 'consumer_key' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'unique identifier, root URL'),
283 'tok' => array('type' => 'char', 'length' => 32, 'description' => 'buggy old value, ignored'),
284 'nonce' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'nonce'),
285 'ts' => array('type' => 'datetime', 'not null' => true, 'description' => 'timestamp sent'),
287 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
288 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
290 'primary key' => array('consumer_key', 'ts', 'nonce'),
293 $schema['oauth_application'] = array(
294 'description' => 'OAuth application registration record',
296 'id' => array('type' => 'serial', 'description' => 'unique identifier'),
297 'owner' => array('type' => 'int', 'not null' => true, 'description' => 'owner of the application'),
298 'consumer_key' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'application consumer key',
299 'name' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'name of the application'),
300 'description' => array('type' => 'varchar', 'length' => 255, 'description' => 'description of the application'),
301 'icon' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'application icon'),
302 'source_url' => array('type' => 'varchar', 'length' => 255, 'description' => 'application homepage - used for source link'),
303 'organization' => array('type' => 'varchar', 'length' => 255, 'description' => 'name of the organization running the application'),
304 'homepage' => array('type' => 'varchar', 'length' => 255, 'description' => 'homepage for the organization'),
305 'callback_url' => array('type' => 'varchar', 'length' => 255, 'description' => 'url to redirect to after authentication'),
306 'type' => array('type' => 'bool', 'default' => 0, 'description' => 'type of app, 1 = browser, 2 = desktop'),
307 'access_type' => array('type' => 'bool', 'default' => 0, 'description' => 'default access type, bit 1 = read, bit 2 = write'),
308 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
309 'modified' => array('type' => 'timestamp', comment 'date this record was modified'),
311 'primary key' => array('id'),
312 'unique keys' => array(
313 'oauth_application_name_idx' => array('name'), // in the long run, we should perhaps not force these unique, and use another source id
315 'foreign keys' => array(
316 'owner' => array('profile' => 'id'), // Are remote users allowed to create oauth application records?
317 'consumer_key' => array('consumer' => 'consumer_key'),
321 $schema['oauth_application_user'] = array(
323 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'user of the application'),
324 'application_id' => array('type' => 'int', 'not null' => true, 'description' => 'id of the application'),
325 'access_type' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'access type, bit 1 = read, bit 2 = write'),
326 'token' => array('type' => 'varchar', 'length' => 255, 'description' => 'request or access token'),
327 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
328 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
330 'primary key' => array('profile_id', 'application_id'),
331 'foreign keys' => array(
332 'profile_id' => array('profile' => 'id'),
333 'application_id' => array('oauth_application' => 'id'),
337 /* These are used by JanRain OpenID library */
339 $schema['oid_associations'] = array(
341 'server_url' => array('type' => 'blob'),
342 'handle' => array('type' => 'varchar', 'length' => 255, character set latin1,
343 'secret' => array('type' => 'blob'),
344 'issued' => array('type' => 'int'),
345 'lifetime' => array('type' => 'int'),
346 'assoc_type' => array('type' => 'varchar', 'length' => 64),
348 'primary key' => array(array('server_url', 255), 'handle'),
351 $schema['oid_nonces'] = array(
353 'server_url' => array('type' => 'varchar', 'length' => 2047),
354 'timestamp' => array('type' => 'int'),
355 'salt' => array('type' => 'char', 'length' => 40),
357 'unique keys' => array(
358 'oid_nonces_server_url_type_idx' => array('server_url', 255), 'timestamp', 'salt'),
362 $schema['confirm_address'] = array(
364 'code' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'good random code'),
365 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who requested confirmation' references user (id),
366 'address' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'address (email, xmpp, SMS, etc.)'),
367 'address_extra' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'carrier ID, for SMS'),
368 'address_type' => array('type' => 'varchar', 'length' => 8, 'not null' => true, 'description' => 'address type ("email", "xmpp", "sms")'),
369 'claimed' => array('type' => 'datetime', 'description' => 'date this was claimed for queueing'),
370 'sent' => array('type' => 'datetime', 'description' => 'date this was sent for queueing'),
371 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
373 'primary key' => array('code'),
376 $schema['remember_me'] = array(
378 'code' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'good random code'),
379 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who is logged in' references user (id),
380 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified',
382 'primary key' => array('code'),
385 $schema['queue_item'] = array(
387 'id' => array('type' => 'serial', 'description' => 'unique identifier'),
388 'frame' => array('type' => 'blob', 'not null' => true, 'description' => 'data: object reference or opaque string'),
389 'transport' => array('type' => 'varchar', 'length' => 8, 'not null' => true, 'description' => 'queue for what? "email", "xmpp", "sms", "irc", ...'),
390 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
391 'claimed' => array('type' => 'datetime', 'description' => 'date this item was claimed'),
393 'primary key' => array('id'),
395 'queue_item_created_idx' => array('created'),
400 $schema['notice_tag'] = array(
402 'tag' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'hash tag associated with this notice'),
403 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice tagged' references notice (id),
404 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
406 'primary key' => array('tag', 'notice_id'),
408 'notice_tag_created_idx' => array('created'),
409 'notice_tag_notice_id_idx' => array('notice_id'),
413 /* Synching with foreign services */
415 $schema['foreign_service'] = array(
417 'id' => array('type' => 'int', 'not null' => true, 'description' => 'numeric key for service'),
418 'name' => array('type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'name of the service'),
419 'description' => array('type' => 'varchar', 'length' => 255, 'description' => 'description'),
420 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
421 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified',
423 'primary key' => array('id'),
424 'unique keys' => array(
425 'foreign_service_name_idx' => 'name',
429 $schema['foreign_user'] = array(
431 'id' => array('type' => 'bigint', 'not null' => true, 'description' => 'unique numeric key on foreign service'),
432 'service' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to service' references foreign_service(id),
433 'uri' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'identifying URI'),
434 'nickname' => array('type' => 'varchar', 'length' => 255, 'description' => 'nickname on foreign service'),
435 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
436 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
438 'primary key' => array('id', 'service'),
439 'unique keys' => array(
440 'foreign_user_uri_idx' => 'uri',
444 $schema['foreign_link'] = array(
446 'user_id' => array('type' => 'int', 'description' => 'link to user on this system, if exists' references user (id),
447 'foreign_id' => array('type' => 'int', 'size' => 'big', 'unsigned' => true, 'description' => 'link to user on foreign service, if exists' references foreign_user(id),
448 'service' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to service' references foreign_service(id),
449 'credentials' => array('type' => 'varchar', 'length' => 255, 'description' => 'authc credentials, typically a password'),
450 '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'),
451 'friendsync' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 2 'description' => 'friend synchronization, bit 1 = sync outgoing, bit 2 = sync incoming'),
452 'profilesync' => array('type' => 'int', 'size' => 'tiny', 'not null' => true, 'default' => 1, 'description' => 'profile synchronization, bit 1 = sync outgoing, bit 2 = sync incoming'),
453 'last_noticesync' => array('type' => 'datetime', 'description' => 'last time notices were imported'),
454 'last_friendsync' => array('type' => 'datetime', 'description' => 'last time friends were imported'),
455 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
456 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
458 'primary key' => array('user_id', 'foreign_id', 'service'),
460 'foreign_user_user_id_idx' => array('user_id'),
464 $schema['foreign_subscription'] = array(
466 'service', 'type' => 'int', 'not null' => true, 'description' => 'service where relationship happens' references foreign_service(id),
467 'subscriber', 'type' => 'int', 'not null' => true, 'description' => 'subscriber on foreign service' references foreign_user (id),
468 'subscribed', 'type' => 'int', 'not null' => true, 'description' => 'subscribed user' references foreign_user (id),
469 'created', 'type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
471 'primary key' => array('service', 'subscriber', 'subscribed'),
473 'foreign_subscription_subscriber_idx' => array('subscriber'),
474 'foreign_subscription_subscribed_idx' => array('subscribed'),
478 $schema['invitation'] = array(
480 'code', 'type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'random code for an invitation'),
481 'user_id', 'type' => 'int', 'not null' => true, 'description' => 'who sent the invitation' references user (id),
482 'address', 'type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'invitation sent to'),
483 'address_type', 'type' => 'varchar', 'length' => 8, 'not null' => true, 'description' => 'address type ("email", "xmpp", "sms")'),
484 'created', 'type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
486 'primary key' => array('code'),
488 'invitation_address_idx' => ('address', 'address_type'),
489 'invitation_user_id_idx' => ('user_id'),
493 $schema['message'] = array(
495 'id' => array('type' => 'serial', 'description' => 'unique identifier'),
496 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universally unique identifier'),
497 'from_profile' => array('type' => 'int', 'not null' => true, 'description' => 'who the message is from' references profile (id),
498 'to_profile' => array('type' => 'int', 'not null' => true, 'description' => 'who the message is to' references profile (id),
499 'content' => array('type' => 'text', 'description' => 'message content'),
500 'rendered' => array('type' => 'text', 'description' => 'HTML version of the content'),
501 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL of any attachment (image, video, bookmark, whatever)'),
502 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
503 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
504 'source' => array('type' => 'varchar', 'length' => 32, 'description' => 'source of comment, like "web", "im", or "clientname"'),
506 'primary key' => array('id'),
507 'unique key' => array(
508 'message_uri_idx' => array('uri'),
511 // @fixme these are really terrible indexes, since you can only sort on one of them at a time.
512 // looks like we really need a (to_profile, created) for inbox and a (from_profile, created) for outbox
513 'message_from_idx' => array('from_profile'),
514 'message_to_idx' => array('to_profile'),
515 'message_created_idx' => array('created'),
519 $schema['notice_inbox'] = array(
521 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user receiving the message' references user (id),
522 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice received' references notice (id),
523 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice was created'),
524 'source' => array('type' => 'bool', 'default' => 1, 'description' => 'reason it is in the inbox, 1=subscription'),
526 'primary key' => array('user_id', 'notice_id'),
528 'notice_inbox_notice_id_idx' => ('notice_id'),
532 $schema['profile_tag'] = array(
534 'tagger' => array('type' => 'int', 'not null' => true, 'description' => 'user making the tag' references user (id),
535 'tagged' => array('type' => 'int', 'not null' => true, 'description' => 'profile tagged' references profile (id),
536 'tag' => array('type' => 'varchar', 'length' => 64, 'not null' => true, 'description' => 'hash tag associated with this notice'),
537 'modified' => array('type' => 'timestamp', 'description' => 'date the tag was added'),
539 'primary key' => array('tagger', 'tagged', 'tag'),
541 'profile_tag_modified_idx' => array('modified'),
542 'profile_tag_tagger_tag_idx' => array('tagger', 'tag'),
543 'profile_tag_tagged_idx' => array('tagged'),
547 $schema['profile_block'] = array(
549 'blocker' => array('type' => 'int', 'not null' => true, 'description' => 'user making the block' references user (id),
550 'blocked' => array('type' => 'int', 'not null' => true, 'description' => 'profile that is blocked' references profile (id),
551 'modified' => array('type' => 'timestamp', 'description' => 'date of blocking'),
553 'primary key' => array('blocker', 'blocked'),
556 $schema['user_group'] = array(
558 'id' => array('type' => 'serial', 'description' => 'unique identifier'),
560 'nickname' => array('type' => 'varchar', 'length' => 64, 'description' => 'nickname for addressing'),
561 'fullname' => array('type' => 'varchar', 'length' => 255, 'description' => 'display name'),
562 'homepage' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL, cached so we dont regenerate'),
563 'description' => array('type' => 'text', 'description' => 'group description'),
564 'location' => array('type' => 'varchar', 'length' => 255, 'description' => 'related physical location, if any'),
566 'original_logo' => array('type' => 'varchar', 'length' => 255, 'description' => 'original size logo'),
567 'homepage_logo' => array('type' => 'varchar', 'length' => 255, 'description' => 'homepage (profile) size logo'),
568 'stream_logo' => array('type' => 'varchar', 'length' => 255, 'description' => 'stream-sized logo'),
569 'mini_logo' => array('type' => 'varchar', 'length' => 255, 'description' => 'mini logo'),
570 'design_id' => array('type' => 'int', 'description' => 'id of a design' references design(id),
572 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
573 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
575 'uri' => array('type' => 'varchar', 'length' => 255, 'description' => 'universal identifier'),
576 'mainpage' => array('type' => 'varchar', 'length' => 255, 'description' => 'page for group info to link to'),
578 'primary key' => array('id'),
579 'unique key' => array(
580 'user_uri_idx' => array('uri'),
583 'user_group_nickname_idx' => array('nickname'),
587 $schema['group_member'] = array(
589 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to user_group' references user_group (id),
590 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to profile table' references profile (id),
591 'is_admin' => array('type' => 'boolean', 'default' => false, 'description' => 'is this user an admin?'),
593 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
594 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
596 'primary key' => array('group_id', 'profile_id'),
598 // @fixme probably we want a (profile_id, created) index here?
599 'group_member_profile_id_idx' => array('profile_id'),
600 'group_member_created_idx' => array('created'),
604 $schema['related_group'] = array(
606 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to user_group' references user_group (id),
607 'related_group_id' => array('type' => 'int', 'not null' => true, 'description' => 'foreign key to user_group' references user_group (id),
609 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
611 'primary key' => array('group_id', 'related_group_id'),
614 $schema['group_inbox'] = array(
616 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'group receiving the message' references user_group (id),
617 'notice_id' => array('type' => 'int', 'not null' => true, 'description' => 'notice received' references notice (id),
618 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice was created'),
620 'primary key' => array('group_id', 'notice_id'),
622 'group_inbox_created_idx' => array('created'),
623 'group_inbox_notice_id_idx' => array('notice_id'),
627 $schema['file'] = array(
629 'id' => array('type' => 'serial'),
630 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'destination URL after following redirections'),
631 'mimetype' => array('type' => 'varchar', 'length' => 50, 'description' => 'mime type of resource'),
632 'size' => array('type' => 'int', 'description' => 'size of resource when available'),
633 'title' => array('type' => 'varchar', 'length' => 255, 'description' => 'title of resource when available'),
634 'date' => array('type' => 'int', 'description' => 'date of resource according to http query'),
635 'protected' => array('type' => 'int', 'description' => 'true when URL is private (needs login)'),
636 'filename' => array('type' => 'varchar', 'length' => 255, 'description' => 'if a local file, name of the file'),
638 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
640 'primary key' => array('id'),
641 'unique keys' => array(
642 'file_url_idx' => array('url'),
646 $schema['file_oembed'] = array(
648 'file_id' => array('type' => 'int', 'description' => 'oEmbed for that URL/file' references file (id),
649 'version' => array('type' => 'varchar', 'length' => 20, 'description' => 'oEmbed spec. version'),
650 'type' => array('type' => 'varchar', 'length' => 20, 'description' => 'oEmbed type: photo, video, link, rich'),
651 'mimetype' => array('type' => 'varchar', 'length' => 50, 'description' => 'mime type of resource'),
652 'provider' => array('type' => 'varchar', 'length' => 50, 'description' => 'name of this oEmbed provider'),
653 'provider_url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL of this oEmbed provider'),
654 'width' => array('type' => 'int', 'description' => 'width of oEmbed resource when available'),
655 'height' => array('type' => 'int', 'description' => 'height of oEmbed resource when available'),
656 'html' => array('type' => 'text', 'description' => 'html representation of this oEmbed resource when applicable'),
657 'title' => array('type' => 'varchar', 'length' => 255, 'description' => 'title of oEmbed resource when available'),
658 'author_name' => array('type' => 'varchar', 'length' => 50, 'description' => 'author name for this oEmbed resource'),
659 'author_url' => array('type' => 'varchar', 'length' => 255, 'description' => 'author URL for this oEmbed resource'),
660 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL for this oEmbed resource when applicable (photo, link)'),
661 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'
663 'primary key' => array('file_id'),
666 $schema['file_redirection'] = array(
668 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'short URL (or any other kind of redirect) for file (id)'),
669 'file_id' => array('type' => 'int', 'description' => 'short URL for what URL/file' references file (id),
670 'redirections' => array('type' => 'int', 'description' => 'redirect count'),
671 'httpcode' => array('type' => 'int', 'description' => 'HTTP status code (20x, 30x, etc.)'),
672 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'
674 'primary key' => array('url'),
677 $schema['file_thumbnail'] = array(
679 'file_id' => array('type' => 'int', 'description' => 'thumbnail for what URL/file' references file (id),
680 'url' => array('type' => 'varchar', 'length' => 255, 'description' => 'URL of thumbnail'),
681 'width' => array('type' => 'int', 'description' => 'width of thumbnail'),
682 'height' => array('type' => 'int', 'description' => 'height of thumbnail'),
683 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
684 'primary key' => array('file_id'),
685 'unique keys' => array(
686 'file_thumbnail_url_idx' => array('url'),
690 $schema['file_to_post'] = array(
692 'file_id' => array('type' => 'int', 'description' => 'id of URL/file' references file (id),
693 'post_id' => array('type' => 'int', 'description' => 'id of the notice it belongs to' references notice (id),
694 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
696 'primary key' => array('file_id', 'post_id'),
698 'post_id_idx' => array('post_id'),
702 $schema['design'] = array(
704 'id' => array('type' => 'serial', 'description' => 'design ID'),
705 'backgroundcolor' => array('type' => 'int', 'description' => 'main background color'),
706 'contentcolor' => array('type' => 'int', 'description' => 'content area background color'),
707 'sidebarcolor' => array('type' => 'int', 'description' => 'sidebar background color'),
708 'textcolor' => array('type' => 'int', 'description' => 'text color'),
709 'linkcolor' => array('type' => 'int', 'description' => 'link color'),
710 'backgroundimage' => array('type' => 'varchar', 'length' => 255, 'description' => 'background image, if any'),
711 'disposition' => array('type' => 'int', 'size' => 'tiny', 'default' => 1, 'description' => 'bit 1 = hide background image, bit 2 = display background image, bit 4 = tile background image'
713 'primary key' => array('id'),
716 $schema['group_block'] = array(
718 'group_id' => array('type' => 'int', 'not null' => true, 'description' => 'group profile is blocked from' references user_group (id),
719 'blocked' => array('type' => 'int', 'not null' => true, 'description' => 'profile that is blocked' references profile (id),
720 'blocker' => array('type' => 'int', 'not null' => true, 'description' => 'user making the block' references user (id),
721 'modified' => array('type' => 'timestamp', 'description' => 'date of blocking'),
723 'primary key' => array('group_id', 'blocked'),
726 $schema['group_alias'] = array(
728 'alias' => array('type' => 'varchar', 'length' => 64, 'description' => 'additional nickname for the group'),
729 'group_id' => array('type' => 'int 'not null' => true, 'description' => 'group profile is blocked from' references user_group (id),
730 'modified' => array('type' => 'timestamp 'description' => 'date alias was created'),
731 'primary key' => array('alias'),
733 'group_alias_group_id_idx' => array('group_id'),
737 $schema['session'] = array(
739 'id' => array('type' => 'varchar', 'length' => 32, primary key 'description' => 'session ID'),
740 'session_data' => array('type' => 'text', 'description' => 'session data'),
741 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
742 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
745 index session_modified_idx (modified)
749 $schema['deleted_notice'] = array(
751 'id' => array('type' => 'int', primary key 'description' => 'identity of notice'),
752 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'author of the notice'),
753 'uri' => array('type' => 'varchar', 'length' => 255, unique key 'description' => 'universally unique identifier, usually a tag URI'),
754 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice record was created'),
755 'deleted' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the notice record was created'),
758 index deleted_notice_profile_id_idx (profile_id)
762 $schema['config'] = array(
764 'section' => array('type' => 'varchar', 'length' => 32, 'description' => 'configuration section'),
765 'setting' => array('type' => 'varchar', 'length' => 32, 'description' => 'configuration setting'),
766 'value' => array('type' => 'varchar', 'length' => 255, 'description' => 'configuration value'),
768 'primary key' => array(section, setting)
772 $schema['profile_role'] = array(
774 'profile_id' => array('type' => 'int', 'not null' => true, 'description' => 'account having the role' references profile (id),
775 'role ', 'type' => 'varchar', 'length' => 32, 'not null' => true, 'description' => 'string representing the role'),
776 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the role was granted'),
778 'primary key' => array(profile_id, role)
782 $schema['location_namespace'] = array(
784 'id' => array('type' => 'int', primary key 'description' => 'identity for this namespace'),
785 'description' => array('type' => 'varchar', 'length' => 255, 'description' => 'description of the namespace'),
786 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date the record was created'),
787 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'
791 $schema['login_token'] = array(
793 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user owning this token' references user (id),
794 'token' => array('type' => 'char', 'length' => 32, 'not null' => true, 'description' => 'token useable for logging in'),
795 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
796 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
798 'primary key' => array(user_id)
801 $schema['user_location_prefs'] = array(
803 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user who has the preference' references user (id),
804 'share_location' => array('type' => 'bool', 'default' => 1, 'description' => 'Whether to share location data'),
805 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
806 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
808 'primary key' => array(user_id)
811 $schema['inbox'] = array(
813 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user receiving the notice' references user (id),
814 'notice_ids' => array('type' => 'blob', 'description' => 'packed list of notice ids'),
816 'primary key' => array(user_id)
820 // @fixme possibly swap this for a more general prefs table?
821 $schema['user_im_prefs'] = array(
823 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user',
824 'screenname' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'screenname on this service'),
825 'transport' => array('type' => 'varchar', 'length' => 255, 'not null' => true, 'description' => 'transport (ex xmpp, aim)'),
826 'notify' => array('type' => 'bool', 'not null' => true, 'default' => 0, 'description' => 'Notify when a new notice is sent'),
827 'replies' => array('type' => 'bool', 'not null' => true, 'default' => 0, 'description' => 'Send replies from people not subscribed to'),
828 'microid' => array('type' => 'bool', 'not null' => true, 'default' => 1, 'description' => 'Publish a MicroID'),
829 'updatefrompresence' => array('type' => 'bool', 'not null' => true, 'default' => 0, 'description' => 'Send replies from people not subscribed to.'),
830 'created' => array('type' => 'timestamp', 'not null' => true, DEFAULT CURRENT_TIMESTAMP 'description' => 'date this record was created'),
831 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
833 'primary key' => array('user_id', 'transport'),
834 'unique keys' => array(
835 'transport_screenname_key' => array('transport', 'screenname'),
837 'foreign keys' => array(
838 'user_id' => array('user' => 'id'),
842 $schema['conversation'] = array(
844 'id' => array('type' => 'serial', 'description' => 'unique identifier'),
845 'uri' => array('type' => 'varchar', 'length' => 225, 'description' => 'URI of the conversation'),
846 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
847 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'
849 'primary key' => array('id'),
850 'unique keys' => array(
851 'conversation_uri_idx' => array('uri'),
855 $schema['local_group'] = array(
856 'description' => 'Record for a user group on the local site, with some additional info not in user_group',
858 'group_id' => array('type' => 'int', 'description' => 'group represented'),
859 'nickname' => array('type' => 'varchar', 'length' => 64, 'description' => 'group represented'),
861 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
862 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'
864 'primary key' => array('group_id'),
865 'foreign keys' => array(
866 'group_id' => array('user_group' => 'id'),
868 'unique keys' => array(
869 'local_group_nickname_idx' => array('nickname'),
873 $schema['user_urlshortener_prefs'] = array(
875 'user_id' => array('type' => 'int', 'not null' => true, 'description' => 'user'),
876 'urlshorteningservice' => array('type' => 'varchar', 'length' => 50, 'default' => 'ur1.ca', 'description' => 'service to use for auto-shortening URLs'),
877 'maxurllength' => array('type' => 'int', 'not null' => true, 'description' => 'urls greater than this length will be shortened, 0 = always, null = never'),
878 'maxnoticelength' => array('type' => 'int', 'not null' => true, 'description' => 'notices with content greater than this value will have all urls shortened, 0 = always, null = never'),
880 'created' => array('type' => 'datetime', 'not null' => true, 'description' => 'date this record was created'),
881 'modified' => array('type' => 'timestamp', 'description' => 'date this record was modified'),
883 'primary key' => array('user_id'),
884 'foreign keys' => array(
885 'user_id' => array('user' => 'id'),