1 /* local and remote users have profiles */
5 id integer auto_increment primary key comment 'unique identifier',
6 nickname varchar(64) not null comment 'nickname or username',
7 fullname varchar(255) comment 'display name',
8 profileurl varchar(255) comment 'URL, cached so we dont regenerate',
9 homepage varchar(255) comment 'identifying URL',
10 bio text comment 'descriptive biography',
11 location varchar(255) comment 'physical location',
12 lat decimal(10,7) comment 'latitude',
13 lon decimal(10,7) comment 'longitude',
14 location_id integer comment 'location id if possible',
15 location_ns integer comment 'namespace for location',
17 created datetime not null comment 'date this record was created',
18 modified timestamp comment 'date this record was modified',
20 index profile_nickname_idx (nickname),
21 FULLTEXT(nickname, fullname, location, bio, homepage)
22 ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
25 profile_id integer not null comment 'foreign key to profile table' references profile (id),
26 original boolean default false comment 'uploaded by user or generated?',
27 width integer not null comment 'image width',
28 height integer not null comment 'image height',
29 mediatype varchar(32) not null comment 'file type',
30 filename varchar(255) null comment 'local filename, if local',
31 url varchar(255) unique key comment 'avatar location',
32 created datetime not null comment 'date this record was created',
33 modified timestamp comment 'date this record was modified',
35 constraint primary key (profile_id, width, height),
36 index avatar_profile_id_idx (profile_id)
37 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
39 create table sms_carrier (
40 id integer primary key comment 'primary key for SMS carrier',
41 name varchar(64) unique key comment 'name of the carrier',
42 email_pattern varchar(255) not null comment 'sprintf pattern for making an email address from a phone number',
43 created datetime not null comment 'date this record was created',
44 modified timestamp comment 'date this record was modified'
45 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
51 id integer primary key comment 'foreign key to profile table' references profile (id),
52 nickname varchar(64) unique key comment 'nickname or username, duped in profile',
53 password varchar(255) comment 'salted password, can be null for OpenID users',
54 email varchar(255) unique key comment 'email address for password recovery etc.',
55 incomingemail varchar(255) unique key comment 'email address for post-by-email',
56 emailnotifysub tinyint default 1 comment 'Notify by email of subscriptions',
57 emailnotifyfav tinyint default 1 comment 'Notify by email of favorites',
58 emailnotifynudge tinyint default 1 comment 'Notify by email of nudges',
59 emailnotifymsg tinyint default 1 comment 'Notify by email of direct messages',
60 emailnotifyattn tinyint default 1 comment 'Notify by email of @-replies',
61 emailmicroid tinyint default 1 comment 'whether to publish email microid',
62 language varchar(50) comment 'preferred language',
63 timezone varchar(50) comment 'timezone',
64 emailpost tinyint default 1 comment 'Post by email',
65 jabber varchar(255) unique key comment 'jabber ID for notices',
66 jabbernotify tinyint default 0 comment 'whether to send notices to jabber',
67 jabberreplies tinyint default 0 comment 'whether to send notices to jabber on replies',
68 jabbermicroid tinyint default 1 comment 'whether to publish xmpp microid',
69 updatefrompresence tinyint default 0 comment 'whether to record updates from Jabber presence notices',
70 sms varchar(64) unique key comment 'sms phone number',
71 carrier integer comment 'foreign key to sms_carrier' references sms_carrier (id),
72 smsnotify tinyint default 0 comment 'whether to send notices to SMS',
73 smsreplies tinyint default 0 comment 'whether to send notices to SMS on replies',
74 smsemail varchar(255) comment 'built from sms and carrier',
75 uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI',
76 autosubscribe tinyint default 0 comment 'automatically subscribe to users who subscribe to us',
77 urlshorteningservice varchar(50) default 'ur1.ca' comment 'service to use for auto-shortening URLs',
78 inboxed tinyint default 0 comment 'has an inbox been created for this user?',
79 design_id integer comment 'id of a design' references design(id),
80 viewdesigns tinyint default 1 comment 'whether to view user-provided designs',
82 created datetime not null comment 'date this record was created',
83 modified timestamp comment 'date this record was modified',
85 index user_smsemail_idx (smsemail)
86 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
90 create table remote_profile (
91 id integer primary key comment 'foreign key to profile table' references profile (id),
92 uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI',
93 postnoticeurl varchar(255) comment 'URL we use for posting notices',
94 updateprofileurl varchar(255) comment 'URL we use for updates to this profile',
95 created datetime not null comment 'date this record was created',
96 modified timestamp comment 'date this record was modified'
97 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
99 create table subscription (
100 subscriber integer not null comment 'profile listening',
101 subscribed integer not null comment 'profile being listened to',
102 jabber tinyint default 1 comment 'deliver jabber messages',
103 sms tinyint default 1 comment 'deliver sms messages',
104 token varchar(255) comment 'authorization token',
105 secret varchar(255) comment 'token secret',
106 created datetime not null comment 'date this record was created',
107 modified timestamp comment 'date this record was modified',
109 constraint primary key (subscriber, subscribed),
110 index subscription_subscriber_idx (subscriber, created),
111 index subscription_subscribed_idx (subscribed, created),
112 index subscription_token_idx (token)
113 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
115 create table notice (
116 id integer auto_increment primary key comment 'unique identifier',
117 profile_id integer not null comment 'who made the update' references profile (id),
118 uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI',
119 content text comment 'update content',
120 rendered text comment 'HTML version of the content',
121 url varchar(255) comment 'URL of any attachment (image, video, bookmark, whatever)',
122 created datetime not null comment 'date this record was created',
123 modified timestamp comment 'date this record was modified',
124 reply_to integer comment 'notice replied to (usually a guess)' references notice (id),
125 is_local tinyint default 0 comment 'notice was generated by a user',
126 source varchar(32) comment 'source of comment, like "web", "im", or "clientname"',
127 conversation integer comment 'id of root notice in this conversation' references notice (id),
128 lat decimal(10,7) comment 'latitude',
129 lon decimal(10,7) comment 'longitude',
130 location_id integer comment 'location id if possible',
131 location_ns integer comment 'namespace for location',
132 repeat_of integer comment 'notice this is a repeat of' references notice (id),
134 index notice_profile_id_idx (profile_id,created,id),
135 index notice_conversation_idx (conversation),
136 index notice_created_idx (created),
137 index notice_replyto_idx (reply_to),
138 index notice_repeatof_idx (repeat_of),
140 ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
142 create table notice_source (
143 code varchar(32) primary key not null comment 'source code',
144 name varchar(255) not null comment 'name of the source',
145 url varchar(255) not null comment 'url to link to',
146 created datetime not null comment 'date this record was created',
147 modified timestamp comment 'date this record was modified'
148 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
151 notice_id integer not null comment 'notice that is the reply' references notice (id),
152 profile_id integer not null comment 'profile replied to' references profile (id),
153 modified timestamp not null comment 'date this record was modified',
154 replied_id integer comment 'notice replied to (not used, see notice.reply_to)',
156 constraint primary key (notice_id, profile_id),
157 index reply_notice_id_idx (notice_id),
158 index reply_profile_id_idx (profile_id),
159 index reply_replied_id_idx (replied_id)
161 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
164 notice_id integer not null comment 'notice that is the favorite' references notice (id),
165 user_id integer not null comment 'user who likes this notice' references user (id),
166 modified timestamp not null comment 'date this record was modified',
168 constraint primary key (notice_id, user_id),
169 index fave_notice_id_idx (notice_id),
170 index fave_user_id_idx (user_id,modified),
171 index fave_modified_idx (modified)
173 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
175 /* tables for OAuth */
177 create table consumer (
178 consumer_key varchar(255) primary key comment 'unique identifier, root URL',
179 consumer_secret varchar(255) not null comment 'secret value',
180 seed char(32) not null comment 'seed for new tokens by this consumer',
182 created datetime not null comment 'date this record was created',
183 modified timestamp comment 'date this record was modified'
184 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
187 consumer_key varchar(255) not null comment 'unique identifier, root URL' references consumer (consumer_key),
188 tok char(32) not null comment 'identifying value',
189 secret char(32) not null comment 'secret value',
190 type tinyint not null default 0 comment 'request or access',
191 state tinyint default 0 comment 'for requests, 0 = initial, 1 = authorized, 2 = used',
192 verifier varchar(255) comment 'verifier string for OAuth 1.0a',
193 verified_callback varchar(255) comment 'verified callback URL for OAuth 1.0a',
195 created datetime not null comment 'date this record was created',
196 modified timestamp comment 'date this record was modified',
198 constraint primary key (consumer_key, tok)
199 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
202 consumer_key varchar(255) not null comment 'unique identifier, root URL',
203 tok char(32) null comment 'buggy old value, ignored',
204 nonce char(32) not null comment 'nonce',
205 ts datetime not null comment 'timestamp sent',
207 created datetime not null comment 'date this record was created',
208 modified timestamp comment 'date this record was modified',
210 constraint primary key (consumer_key, ts, nonce)
211 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
213 create table oauth_application (
214 id integer auto_increment primary key comment 'unique identifier',
215 owner integer not null comment 'owner of the application' references profile (id),
216 consumer_key varchar(255) not null comment 'application consumer key' references consumer (consumer_key),
217 name varchar(255) not null comment 'name of the application',
218 description varchar(255) comment 'description of the application',
219 icon varchar(255) not null comment 'application icon',
220 source_url varchar(255) comment 'application homepage - used for source link',
221 organization varchar(255) comment 'name of the organization running the application',
222 homepage varchar(255) comment 'homepage for the organization',
223 callback_url varchar(255) comment 'url to redirect to after authentication',
224 type tinyint default 0 comment 'type of app, 1 = browser, 2 = desktop',
225 access_type tinyint default 0 comment 'default access type, bit 1 = read, bit 2 = write',
226 created datetime not null comment 'date this record was created',
227 modified timestamp comment 'date this record was modified'
228 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
230 create table oauth_application_user (
231 profile_id integer not null comment 'user of the application' references profile (id),
232 application_id integer not null comment 'id of the application' references oauth_application (id),
233 access_type tinyint default 0 comment 'access type, bit 1 = read, bit 2 = write, bit 3 = revoked',
234 token varchar(255) comment 'request or access token',
235 created datetime not null comment 'date this record was created',
236 modified timestamp comment 'date this record was modified',
237 constraint primary key (profile_id, application_id)
238 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
240 /* These are used by JanRain OpenID library */
242 create table oid_associations (
244 handle VARCHAR(255) character set latin1,
248 assoc_type VARCHAR(64),
249 PRIMARY KEY (server_url(255), handle)
250 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
252 create table oid_nonces (
253 server_url VARCHAR(2047),
256 UNIQUE (server_url(255), timestamp, salt)
257 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
259 create table confirm_address (
260 code varchar(32) not null primary key comment 'good random code',
261 user_id integer not null comment 'user who requested confirmation' references user (id),
262 address varchar(255) not null comment 'address (email, Jabber, SMS, etc.)',
263 address_extra varchar(255) not null comment 'carrier ID, for SMS',
264 address_type varchar(8) not null comment 'address type ("email", "jabber", "sms")',
265 claimed datetime comment 'date this was claimed for queueing',
266 sent datetime comment 'date this was sent for queueing',
267 modified timestamp comment 'date this record was modified'
268 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
270 create table remember_me (
271 code varchar(32) not null primary key comment 'good random code',
272 user_id integer not null comment 'user who is logged in' references user (id),
273 modified timestamp comment 'date this record was modified'
274 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
276 create table queue_item (
278 notice_id integer not null comment 'notice queued' references notice (id),
279 transport varchar(8) not null comment 'queue for what? "email", "jabber", "sms", "irc", ...',
280 created datetime not null comment 'date this record was created',
281 claimed datetime comment 'date this item was claimed',
283 constraint primary key (notice_id, transport),
284 index queue_item_created_idx (created)
286 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
289 create table notice_tag (
290 tag varchar( 64 ) not null comment 'hash tag associated with this notice',
291 notice_id integer not null comment 'notice tagged' references notice (id),
292 created datetime not null comment 'date this record was created',
294 constraint primary key (tag, notice_id),
295 index notice_tag_created_idx (created),
296 index notice_tag_notice_id_idx (notice_id)
297 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
299 /* Synching with foreign services */
301 create table foreign_service (
302 id int not null primary key comment 'numeric key for service',
303 name varchar(32) not null unique key comment 'name of the service',
304 description varchar(255) comment 'description',
305 created datetime not null comment 'date this record was created',
306 modified timestamp comment 'date this record was modified'
307 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
309 create table foreign_user (
310 id bigint not null comment 'unique numeric key on foreign service',
311 service int not null comment 'foreign key to service' references foreign_service(id),
312 uri varchar(255) not null unique key comment 'identifying URI',
313 nickname varchar(255) comment 'nickname on foreign service',
314 created datetime not null comment 'date this record was created',
315 modified timestamp comment 'date this record was modified',
317 constraint primary key (id, service)
318 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
320 create table foreign_link (
321 user_id int comment 'link to user on this system, if exists' references user (id),
322 foreign_id bigint unsigned comment 'link to user on foreign service, if exists' references foreign_user(id),
323 service int not null comment 'foreign key to service' references foreign_service(id),
324 credentials varchar(255) comment 'authc credentials, typically a password',
325 noticesync tinyint not null default 1 comment 'notice synchronization, bit 1 = sync outgoing, bit 2 = sync incoming, bit 3 = filter local replies',
326 friendsync tinyint not null default 2 comment 'friend synchronization, bit 1 = sync outgoing, bit 2 = sync incoming',
327 profilesync tinyint not null default 1 comment 'profile synchronization, bit 1 = sync outgoing, bit 2 = sync incoming',
328 last_noticesync datetime default null comment 'last time notices were imported',
329 last_friendsync datetime default null comment 'last time friends were imported',
330 created datetime not null comment 'date this record was created',
331 modified timestamp comment 'date this record was modified',
333 constraint primary key (user_id, foreign_id, service),
334 index foreign_user_user_id_idx (user_id)
335 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
337 create table foreign_subscription (
338 service int not null comment 'service where relationship happens' references foreign_service(id),
339 subscriber int not null comment 'subscriber on foreign service' references foreign_user (id),
340 subscribed int not null comment 'subscribed user' references foreign_user (id),
341 created datetime not null comment 'date this record was created',
343 constraint primary key (service, subscriber, subscribed),
344 index foreign_subscription_subscriber_idx (subscriber),
345 index foreign_subscription_subscribed_idx (subscribed)
346 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
348 create table invitation (
349 code varchar(32) not null primary key comment 'random code for an invitation',
350 user_id int not null comment 'who sent the invitation' references user (id),
351 address varchar(255) not null comment 'invitation sent to',
352 address_type varchar(8) not null comment 'address type ("email", "jabber", "sms")',
353 created datetime not null comment 'date this record was created',
355 index invitation_address_idx (address, address_type),
356 index invitation_user_id_idx (user_id)
357 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
359 create table message (
360 id integer auto_increment primary key comment 'unique identifier',
361 uri varchar(255) unique key comment 'universally unique identifier',
362 from_profile integer not null comment 'who the message is from' references profile (id),
363 to_profile integer not null comment 'who the message is to' references profile (id),
364 content text comment 'message content',
365 rendered text comment 'HTML version of the content',
366 url varchar(255) comment 'URL of any attachment (image, video, bookmark, whatever)',
367 created datetime not null comment 'date this record was created',
368 modified timestamp comment 'date this record was modified',
369 source varchar(32) comment 'source of comment, like "web", "im", or "clientname"',
371 index message_from_idx (from_profile),
372 index message_to_idx (to_profile),
373 index message_created_idx (created)
374 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
376 create table notice_inbox (
377 user_id integer not null comment 'user receiving the message' references user (id),
378 notice_id integer not null comment 'notice received' references notice (id),
379 created datetime not null comment 'date the notice was created',
380 source tinyint default 1 comment 'reason it is in the inbox, 1=subscription',
382 constraint primary key (user_id, notice_id),
383 index notice_inbox_notice_id_idx (notice_id)
384 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
386 create table profile_tag (
387 tagger integer not null comment 'user making the tag' references user (id),
388 tagged integer not null comment 'profile tagged' references profile (id),
389 tag varchar(64) not null comment 'hash tag associated with this notice',
390 modified timestamp comment 'date the tag was added',
392 constraint primary key (tagger, tagged, tag),
393 index profile_tag_modified_idx (modified),
394 index profile_tag_tagger_tag_idx (tagger, tag),
395 index profile_tag_tagged_idx (tagged)
396 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
398 create table profile_block (
399 blocker integer not null comment 'user making the block' references user (id),
400 blocked integer not null comment 'profile that is blocked' references profile (id),
401 modified timestamp comment 'date of blocking',
403 constraint primary key (blocker, blocked)
405 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
407 create table user_group (
408 id integer auto_increment primary key comment 'unique identifier',
410 nickname varchar(64) unique key comment 'nickname for addressing',
411 fullname varchar(255) comment 'display name',
412 homepage varchar(255) comment 'URL, cached so we dont regenerate',
413 description text comment 'group description',
414 location varchar(255) comment 'related physical location, if any',
416 original_logo varchar(255) comment 'original size logo',
417 homepage_logo varchar(255) comment 'homepage (profile) size logo',
418 stream_logo varchar(255) comment 'stream-sized logo',
419 mini_logo varchar(255) comment 'mini logo',
420 design_id integer comment 'id of a design' references design(id),
422 created datetime not null comment 'date this record was created',
423 modified timestamp comment 'date this record was modified',
425 index user_group_nickname_idx (nickname)
427 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
429 create table group_member (
430 group_id integer not null comment 'foreign key to user_group' references user_group (id),
431 profile_id integer not null comment 'foreign key to profile table' references profile (id),
432 is_admin boolean default false comment 'is this user an admin?',
434 created datetime not null comment 'date this record was created',
435 modified timestamp comment 'date this record was modified',
437 constraint primary key (group_id, profile_id),
438 index group_member_profile_id_idx (profile_id),
439 index group_member_created_idx (created)
441 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
443 create table related_group (
444 group_id integer not null comment 'foreign key to user_group' references user_group (id),
445 related_group_id integer not null comment 'foreign key to user_group' references user_group (id),
447 created datetime not null comment 'date this record was created',
449 constraint primary key (group_id, related_group_id)
451 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
453 create table group_inbox (
454 group_id integer not null comment 'group receiving the message' references user_group (id),
455 notice_id integer not null comment 'notice received' references notice (id),
456 created datetime not null comment 'date the notice was created',
458 constraint primary key (group_id, notice_id),
459 index group_inbox_created_idx (created)
461 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
465 id integer primary key auto_increment,
466 url varchar(255) comment 'destination URL after following redirections',
467 mimetype varchar(50) comment 'mime type of resource',
468 size integer comment 'size of resource when available',
469 title varchar(255) comment 'title of resource when available',
470 date integer(11) comment 'date of resource according to http query',
471 protected integer(1) comment 'true when URL is private (needs login)',
472 filename varchar(255) comment 'if a local file, name of the file',
474 modified timestamp comment 'date this record was modified',
477 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
479 create table file_oembed (
480 file_id integer primary key comment 'oEmbed for that URL/file' references file (id),
481 version varchar(20) comment 'oEmbed spec. version',
482 type varchar(20) comment 'oEmbed type: photo, video, link, rich',
483 mimetype varchar(50) comment 'mime type of resource',
484 provider varchar(50) comment 'name of this oEmbed provider',
485 provider_url varchar(255) comment 'URL of this oEmbed provider',
486 width integer comment 'width of oEmbed resource when available',
487 height integer comment 'height of oEmbed resource when available',
488 html text comment 'html representation of this oEmbed resource when applicable',
489 title varchar(255) comment 'title of oEmbed resource when available',
490 author_name varchar(50) comment 'author name for this oEmbed resource',
491 author_url varchar(255) comment 'author URL for this oEmbed resource',
492 url varchar(255) comment 'URL for this oEmbed resource when applicable (photo, link)',
493 modified timestamp comment 'date this record was modified'
495 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
497 create table file_redirection (
499 url varchar(255) primary key comment 'short URL (or any other kind of redirect) for file (id)',
500 file_id integer comment 'short URL for what URL/file' references file (id),
501 redirections integer comment 'redirect count',
502 httpcode integer comment 'HTTP status code (20x, 30x, etc.)',
503 modified timestamp comment 'date this record was modified'
505 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
507 create table file_thumbnail (
509 file_id integer primary key comment 'thumbnail for what URL/file' references file (id),
510 url varchar(255) comment 'URL of thumbnail',
511 width integer comment 'width of thumbnail',
512 height integer comment 'height of thumbnail',
513 modified timestamp comment 'date this record was modified',
516 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
518 create table file_to_post (
520 file_id integer comment 'id of URL/file' references file (id),
521 post_id integer comment 'id of the notice it belongs to' references notice (id),
522 modified timestamp comment 'date this record was modified',
524 constraint primary key (file_id, post_id)
526 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
528 create table design (
529 id integer primary key auto_increment comment 'design ID',
530 backgroundcolor integer comment 'main background color',
531 contentcolor integer comment 'content area background color',
532 sidebarcolor integer comment 'sidebar background color',
533 textcolor integer comment 'text color',
534 linkcolor integer comment 'link color',
535 backgroundimage varchar(255) comment 'background image, if any',
536 disposition tinyint default 1 comment 'bit 1 = hide background image, bit 2 = display background image, bit 4 = tile background image'
537 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
539 create table group_block (
540 group_id integer not null comment 'group profile is blocked from' references user_group (id),
541 blocked integer not null comment 'profile that is blocked' references profile (id),
542 blocker integer not null comment 'user making the block' references user (id),
543 modified timestamp comment 'date of blocking',
545 constraint primary key (group_id, blocked)
547 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
549 create table group_alias (
551 alias varchar(64) primary key comment 'additional nickname for the group',
552 group_id integer not null comment 'group profile is blocked from' references user_group (id),
553 modified timestamp comment 'date alias was created',
555 index group_alias_group_id_idx (group_id)
557 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
559 create table session (
561 id varchar(32) primary key comment 'session ID',
562 session_data text comment 'session data',
563 created datetime not null comment 'date this record was created',
564 modified timestamp comment 'date this record was modified',
566 index session_modified_idx (modified)
568 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
570 create table deleted_notice (
572 id integer primary key comment 'identity of notice',
573 profile_id integer not null comment 'author of the notice',
574 uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI',
575 created datetime not null comment 'date the notice record was created',
576 deleted datetime not null comment 'date the notice record was created',
578 index deleted_notice_profile_id_idx (profile_id)
580 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
582 create table config (
584 section varchar(32) comment 'configuration section',
585 setting varchar(32) comment 'configuration setting',
586 value varchar(255) comment 'configuration value',
588 constraint primary key (section, setting)
590 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
592 create table profile_role (
594 profile_id integer not null comment 'account having the role' references profile (id),
595 role varchar(32) not null comment 'string representing the role',
596 created datetime not null comment 'date the role was granted',
598 constraint primary key (profile_id, role)
600 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
602 create table location_namespace (
604 id integer primary key comment 'identity for this namespace',
605 description varchar(255) comment 'description of the namespace',
606 created datetime not null comment 'date the record was created',
607 modified timestamp comment 'date this record was modified'
609 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
611 create table login_token (
612 user_id integer not null comment 'user owning this token' references user (id),
613 token char(32) not null comment 'token useable for logging in',
614 created datetime not null comment 'date this record was created',
615 modified timestamp comment 'date this record was modified',
617 constraint primary key (user_id)
618 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
620 create table user_location_prefs (
621 user_id integer not null comment 'user who has the preference' references user (id),
622 share_location tinyint default 1 comment 'Whether to share location data',
623 created datetime not null comment 'date this record was created',
624 modified timestamp comment 'date this record was modified',
626 constraint primary key (user_id)
627 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
631 user_id integer not null comment 'user receiving the notice' references user (id),
632 notice_ids blob comment 'packed list of notice ids',
634 constraint primary key (user_id)
636 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;