db/statusnet.sql

   1 /* local and remote users have profiles */
   2
   3 create table profile (
   4
   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',
  16
  17     created datetime not null comment 'date this record was created',
  18     modified timestamp comment 'date this record was modified',
  19
  20     index profile_nickname_idx (nickname),
  21     FULLTEXT(nickname, fullname, location, bio, homepage)
  22 ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
  23
  24 create table avatar (
  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',
  34
  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;
  38
  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;
  46
  47 /* local users */
  48
  49 create table user (
  50
  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     sms varchar(64) unique key comment 'sms phone number',
  66     carrier integer comment 'foreign key to sms_carrier' references sms_carrier (id),
  67     smsnotify tinyint default 0 comment 'whether to send notices to SMS',
  68     smsreplies tinyint default 0 comment 'whether to send notices to SMS on replies',
  69     smsemail varchar(255) comment 'built from sms and carrier',
  70     uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI',
  71     autosubscribe tinyint default 0 comment 'automatically subscribe to users who subscribe to us',
  72     urlshorteningservice varchar(50) default 'ur1.ca' comment 'service to use for auto-shortening URLs',
  73     inboxed tinyint default 0 comment 'has an inbox been created for this user?',
  74     design_id integer comment 'id of a design' references design(id),
  75     viewdesigns tinyint default 1 comment 'whether to view user-provided designs',
  76
  77     created datetime not null comment 'date this record was created',
  78     modified timestamp comment 'date this record was modified',
  79
  80     index user_smsemail_idx (smsemail)
  81 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
  82
  83 /* remote people */
  84
  85 create table remote_profile (
  86     id integer primary key comment 'foreign key to profile table' references profile (id),
  87     uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI',
  88     postnoticeurl varchar(255) comment 'URL we use for posting notices',
  89     updateprofileurl varchar(255) comment 'URL we use for updates to this profile',
  90     created datetime not null comment 'date this record was created',
  91     modified timestamp comment 'date this record was modified'
  92 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
  93
  94 create table subscription (
  95     subscriber integer not null comment 'profile listening',
  96     subscribed integer not null comment 'profile being listened to',
  97     jabber tinyint default 1 comment 'deliver jabber messages',
  98     sms tinyint default 1 comment 'deliver sms messages',
  99     token varchar(255) comment 'authorization token',
 100     secret varchar(255) comment 'token secret',
 101     created datetime not null comment 'date this record was created',
 102     modified timestamp comment 'date this record was modified',
 103
 104     constraint primary key (subscriber, subscribed),
 105     index subscription_subscriber_idx (subscriber, created),
 106     index subscription_subscribed_idx (subscribed, created),
 107     index subscription_token_idx (token)
 108 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 109
 110 create table notice (
 111     id integer auto_increment primary key comment 'unique identifier',
 112     profile_id integer not null comment 'who made the update' references profile (id),
 113     uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI',
 114     content text comment 'update content',
 115     rendered text comment 'HTML version of the content',
 116     url varchar(255) comment 'URL of any attachment (image, video, bookmark, whatever)',
 117     created datetime not null comment 'date this record was created',
 118     modified timestamp comment 'date this record was modified',
 119     reply_to integer comment 'notice replied to (usually a guess)' references notice (id),
 120     is_local tinyint default 0 comment 'notice was generated by a user',
 121     source varchar(32) comment 'source of comment, like "web", "im", or "clientname"',
 122     conversation integer comment 'id of root notice in this conversation' references notice (id),
 123     lat decimal(10,7) comment 'latitude',
 124     lon decimal(10,7) comment 'longitude',
 125     location_id integer comment 'location id if possible',
 126     location_ns integer comment 'namespace for location',
 127     repeat_of integer comment 'notice this is a repeat of' references notice (id),
 128
 129     index notice_profile_id_idx (profile_id,created,id),
 130     index notice_conversation_idx (conversation),
 131     index notice_created_idx (created),
 132     index notice_replyto_idx (reply_to),
 133     index notice_repeatof_idx (repeat_of),
 134     FULLTEXT(content)
 135 ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
 136
 137 create table notice_source (
 138      code varchar(32) primary key not null comment 'source code',
 139      name varchar(255) not null comment 'name of the source',
 140      url varchar(255) not null comment 'url to link to',
 141      created datetime not null comment 'date this record was created',
 142      modified timestamp comment 'date this record was modified'
 143 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 144
 145 create table reply (
 146     notice_id integer not null comment 'notice that is the reply' references notice (id),
 147     profile_id integer not null comment 'profile replied to' references profile (id),
 148     modified timestamp not null comment 'date this record was modified',
 149     replied_id integer comment 'notice replied to (not used, see notice.reply_to)',
 150
 151     constraint primary key (notice_id, profile_id),
 152     index reply_notice_id_idx (notice_id),
 153     index reply_profile_id_idx (profile_id),
 154     index reply_replied_id_idx (replied_id)
 155
 156 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 157
 158 create table fave (
 159     notice_id integer not null comment 'notice that is the favorite' references notice (id),
 160     user_id integer not null comment 'user who likes this notice' references user (id),
 161     modified timestamp not null comment 'date this record was modified',
 162
 163     constraint primary key (notice_id, user_id),
 164     index fave_notice_id_idx (notice_id),
 165     index fave_user_id_idx (user_id,modified),
 166     index fave_modified_idx (modified)
 167
 168 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 169
 170 /* tables for OAuth */
 171
 172 create table consumer (
 173     consumer_key varchar(255) primary key comment 'unique identifier, root URL',
 174     consumer_secret varchar(255) not null comment 'secret value',
 175     seed char(32) not null comment 'seed for new tokens by this consumer',
 176
 177     created datetime not null comment 'date this record was created',
 178     modified timestamp comment 'date this record was modified'
 179 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 180
 181 create table token (
 182     consumer_key varchar(255) not null comment 'unique identifier, root URL' references consumer (consumer_key),
 183     tok char(32) not null comment 'identifying value',
 184     secret char(32) not null comment 'secret value',
 185     type tinyint not null default 0 comment 'request or access',
 186     state tinyint default 0 comment 'for requests, 0 = initial, 1 = authorized, 2 = used',
 187     verifier varchar(255) comment 'verifier string for OAuth 1.0a',
 188     verified_callback varchar(255) comment 'verified callback URL for OAuth 1.0a',
 189
 190     created datetime not null comment 'date this record was created',
 191     modified timestamp comment 'date this record was modified',
 192
 193     constraint primary key (consumer_key, tok)
 194 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 195
 196 create table nonce (
 197     consumer_key varchar(255) not null comment 'unique identifier, root URL',
 198     tok char(32) null comment 'buggy old value, ignored',
 199     nonce char(32) not null comment 'nonce',
 200     ts datetime not null comment 'timestamp sent',
 201
 202     created datetime not null comment 'date this record was created',
 203     modified timestamp comment 'date this record was modified',
 204
 205     constraint primary key (consumer_key, ts, nonce)
 206 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 207
 208 create table oauth_application (
 209     id integer auto_increment primary key comment 'unique identifier',
 210     owner integer not null comment 'owner of the application' references profile (id),
 211     consumer_key varchar(255) not null comment 'application consumer key' references consumer (consumer_key),
 212     name varchar(255) not null unique key comment 'name of the application',
 213     description varchar(255) comment 'description of the application',
 214     icon varchar(255) not null comment 'application icon',
 215     source_url varchar(255) comment 'application homepage - used for source link',
 216     organization varchar(255) comment 'name of the organization running the application',
 217     homepage varchar(255) comment 'homepage for the organization',
 218     callback_url varchar(255) comment 'url to redirect to after authentication',
 219     type tinyint default 0 comment 'type of app, 1 = browser, 2 = desktop',
 220     access_type tinyint default 0 comment 'default access type, bit 1 = read, bit 2 = write',
 221     created datetime not null comment 'date this record was created',
 222     modified timestamp comment 'date this record was modified'
 223 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 224
 225 create table oauth_application_user (
 226     profile_id integer not null comment 'user of the application' references profile (id),
 227     application_id integer not null comment 'id of the application' references oauth_application (id),
 228     access_type tinyint default 0 comment 'access type, bit 1 = read, bit 2 = write',
 229     token varchar(255) comment 'request or access token',
 230     created datetime not null comment 'date this record was created',
 231     modified timestamp comment 'date this record was modified',
 232     constraint primary key (profile_id, application_id)
 233 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 234
 235 create table oauth_token_association (
 236     profile_id integer not null comment 'user of the application' references profile (id),
 237     application_id integer not null comment 'id of the application' references oauth_application (id),
 238     token varchar(255) comment 'request or access token',
 239     created datetime not null comment 'date this record was created',
 240     modified timestamp comment 'date this record was modified',
 241     constraint primary key (profile_id, application_id, token)
 242 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 243
 244 /* These are used by JanRain OpenID library */
 245
 246 create table oid_associations (
 247     server_url BLOB,
 248     handle VARCHAR(255) character set latin1,
 249     secret BLOB,
 250     issued INTEGER,
 251     lifetime INTEGER,
 252     assoc_type VARCHAR(64),
 253     PRIMARY KEY (server_url(255), handle)
 254 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 255
 256 create table oid_nonces (
 257     server_url VARCHAR(2047),
 258     timestamp INTEGER,
 259     salt CHAR(40),
 260     UNIQUE (server_url(255), timestamp, salt)
 261 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 262
 263 create table confirm_address (
 264     code varchar(32) not null primary key comment 'good random code',
 265     user_id integer not null comment 'user who requested confirmation' references user (id),
 266     address varchar(255) not null comment 'address (email, xmpp, SMS, etc.)',
 267     address_extra varchar(255) not null comment 'carrier ID, for SMS',
 268     address_type varchar(8) not null comment 'address type ("email", "xmpp", "sms")',
 269     claimed datetime comment 'date this was claimed for queueing',
 270     sent datetime comment 'date this was sent for queueing',
 271     modified timestamp comment 'date this record was modified'
 272 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 273
 274 create table remember_me (
 275     code varchar(32) not null primary key comment 'good random code',
 276     user_id integer not null comment 'user who is logged in' references user (id),
 277     modified timestamp comment 'date this record was modified'
 278 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 279
 280 create table queue_item (
 281     id integer auto_increment primary key comment 'unique identifier',
 282     frame blob not null comment 'data: object reference or opaque string',
 283     transport varchar(8) not null comment 'queue for what? "email", "xmpp", "sms", "irc", ...',
 284     created datetime not null comment 'date this record was created',
 285     claimed datetime comment 'date this item was claimed',
 286
 287     index queue_item_created_idx (created)
 288
 289 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 290
 291 /* Hash tags */
 292 create table notice_tag (
 293     tag varchar( 64 ) not null comment 'hash tag associated with this notice',
 294     notice_id integer not null comment 'notice tagged' references notice (id),
 295     created datetime not null comment 'date this record was created',
 296
 297     constraint primary key (tag, notice_id),
 298     index notice_tag_created_idx (created),
 299     index notice_tag_notice_id_idx (notice_id)
 300 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 301
 302 /* Synching with foreign services */
 303
 304 create table foreign_service (
 305      id int not null primary key comment 'numeric key for service',
 306      name varchar(32) not null unique key comment 'name of the service',
 307      description varchar(255) comment 'description',
 308      created datetime not null comment 'date this record was created',
 309      modified timestamp comment 'date this record was modified'
 310 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 311
 312 create table foreign_user (
 313      id bigint not null comment 'unique numeric key on foreign service',
 314      service int not null comment 'foreign key to service' references foreign_service(id),
 315      uri varchar(255) not null unique key comment 'identifying URI',
 316      nickname varchar(255) comment 'nickname on foreign service',
 317      created datetime not null comment 'date this record was created',
 318      modified timestamp comment 'date this record was modified',
 319
 320      constraint primary key (id, service)
 321 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 322
 323 create table foreign_link (
 324      user_id int comment 'link to user on this system, if exists' references user (id),
 325      foreign_id bigint unsigned comment 'link to user on foreign service, if exists' references foreign_user(id),
 326      service int not null comment 'foreign key to service' references foreign_service(id),
 327      credentials varchar(255) comment 'authc credentials, typically a password',
 328      noticesync tinyint not null default 1 comment 'notice synchronization, bit 1 = sync outgoing, bit 2 = sync incoming, bit 3 = filter local replies',
 329      friendsync tinyint not null default 2 comment 'friend synchronization, bit 1 = sync outgoing, bit 2 = sync incoming',
 330      profilesync tinyint not null default 1 comment 'profile synchronization, bit 1 = sync outgoing, bit 2 = sync incoming',
 331      last_noticesync datetime default null comment 'last time notices were imported',
 332      last_friendsync datetime default null comment 'last time friends were imported',
 333      created datetime not null comment 'date this record was created',
 334      modified timestamp comment 'date this record was modified',
 335
 336      constraint primary key (user_id, foreign_id, service),
 337      index foreign_user_user_id_idx (user_id)
 338 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 339
 340 create table foreign_subscription (
 341      service int not null comment 'service where relationship happens' references foreign_service(id),
 342      subscriber int not null comment 'subscriber on foreign service' references foreign_user (id),
 343      subscribed int not null comment 'subscribed user' references foreign_user (id),
 344      created datetime not null comment 'date this record was created',
 345
 346      constraint primary key (service, subscriber, subscribed),
 347      index foreign_subscription_subscriber_idx (subscriber),
 348      index foreign_subscription_subscribed_idx (subscribed)
 349 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 350
 351 create table invitation (
 352      code varchar(32) not null primary key comment 'random code for an invitation',
 353      user_id int not null comment 'who sent the invitation' references user (id),
 354      address varchar(255) not null comment 'invitation sent to',
 355      address_type varchar(8) not null comment 'address type ("email", "xmpp", "sms")',
 356      created datetime not null comment 'date this record was created',
 357
 358      index invitation_address_idx (address, address_type),
 359      index invitation_user_id_idx (user_id)
 360 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 361
 362 create table message (
 363     id integer auto_increment primary key comment 'unique identifier',
 364     uri varchar(255) unique key comment 'universally unique identifier',
 365     from_profile integer not null comment 'who the message is from' references profile (id),
 366     to_profile integer not null comment 'who the message is to' references profile (id),
 367     content text comment 'message content',
 368     rendered text comment 'HTML version of the content',
 369     url varchar(255) comment 'URL of any attachment (image, video, bookmark, whatever)',
 370     created datetime not null comment 'date this record was created',
 371     modified timestamp comment 'date this record was modified',
 372     source varchar(32) comment 'source of comment, like "web", "im", or "clientname"',
 373
 374     index message_from_idx (from_profile),
 375     index message_to_idx (to_profile),
 376     index message_created_idx (created)
 377 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
 378
 379 create table notice_inbox (
 380     user_id integer not null comment 'user receiving the message' references user (id),
 381     notice_id integer not null comment 'notice received' references notice (id),
 382     created datetime not null comment 'date the notice was created',
 383     source tinyint default 1 comment 'reason it is in the inbox, 1=subscription',
 384
 385     constraint primary key (user_id, notice_id),
 386     index notice_inbox_notice_id_idx (notice_id)
 387 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 388
 389 create table profile_tag (
 390    tagger integer not null comment 'user making the tag' references user (id),
 391    tagged integer not null comment 'profile tagged' references profile (id),
 392    tag varchar(64) not null comment 'hash tag associated with this notice',
 393    modified timestamp comment 'date the tag was added',
 394
 395    constraint primary key (tagger, tagged, tag),
 396    index profile_tag_modified_idx (modified),
 397    index profile_tag_tagger_tag_idx (tagger, tag),
 398    index profile_tag_tagged_idx (tagged)
 399 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 400
 401 create table profile_block (
 402    blocker integer not null comment 'user making the block' references user (id),
 403    blocked integer not null comment 'profile that is blocked' references profile (id),
 404    modified timestamp comment 'date of blocking',
 405
 406    constraint primary key (blocker, blocked)
 407
 408 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 409
 410 create table user_group (
 411     id integer auto_increment primary key comment 'unique identifier',
 412
 413     nickname varchar(64) comment 'nickname for addressing',
 414     fullname varchar(255) comment 'display name',
 415     homepage varchar(255) comment 'URL, cached so we dont regenerate',
 416     description text comment 'group description',
 417     location varchar(255) comment 'related physical location, if any',
 418
 419     original_logo varchar(255) comment 'original size logo',
 420     homepage_logo varchar(255) comment 'homepage (profile) size logo',
 421     stream_logo varchar(255) comment 'stream-sized logo',
 422     mini_logo varchar(255) comment 'mini logo',
 423     design_id integer comment 'id of a design' references design(id),
 424
 425     created datetime not null comment 'date this record was created',
 426     modified timestamp comment 'date this record was modified',
 427
 428     uri varchar(255) unique key comment 'universal identifier',
 429     mainpage varchar(255) comment 'page for group info to link to',
 430
 431     index user_group_nickname_idx (nickname)
 432
 433 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
 434
 435 create table group_member (
 436     group_id integer not null comment 'foreign key to user_group' references user_group (id),
 437     profile_id integer not null comment 'foreign key to profile table' references profile (id),
 438     is_admin boolean default false comment 'is this user an admin?',
 439
 440     created datetime not null comment 'date this record was created',
 441     modified timestamp comment 'date this record was modified',
 442
 443     constraint primary key (group_id, profile_id),
 444     index group_member_profile_id_idx (profile_id),
 445     index group_member_created_idx (created)
 446
 447 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 448
 449 create table related_group (
 450     group_id integer not null comment 'foreign key to user_group' references user_group (id),
 451     related_group_id integer not null comment 'foreign key to user_group' references user_group (id),
 452
 453     created datetime not null comment 'date this record was created',
 454
 455     constraint primary key (group_id, related_group_id)
 456
 457 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 458
 459 create table group_inbox (
 460     group_id integer not null comment 'group receiving the message' references user_group (id),
 461     notice_id integer not null comment 'notice received' references notice (id),
 462     created datetime not null comment 'date the notice was created',
 463
 464     constraint primary key (group_id, notice_id),
 465     index group_inbox_created_idx (created),
 466     index group_inbox_notice_id_idx (notice_id)
 467
 468 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 469
 470 create table file (
 471
 472     id integer primary key auto_increment,
 473     url varchar(255) comment 'destination URL after following redirections',
 474     mimetype varchar(50) comment 'mime type of resource',
 475     size integer comment 'size of resource when available',
 476     title varchar(255) comment 'title of resource when available',
 477     date integer(11) comment 'date of resource according to http query',
 478     protected integer(1) comment 'true when URL is private (needs login)',
 479     filename varchar(255) comment 'if a local file, name of the file',
 480
 481     modified timestamp comment 'date this record was modified',
 482
 483     unique(url)
 484 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
 485
 486 create table file_oembed (
 487     file_id integer primary key comment 'oEmbed for that URL/file' references file (id),
 488     version varchar(20) comment 'oEmbed spec. version',
 489     type varchar(20) comment 'oEmbed type: photo, video, link, rich',
 490     mimetype varchar(50) comment 'mime type of resource',
 491     provider varchar(50) comment 'name of this oEmbed provider',
 492     provider_url varchar(255) comment 'URL of this oEmbed provider',
 493     width integer comment 'width of oEmbed resource when available',
 494     height integer comment 'height of oEmbed resource when available',
 495     html text comment 'html representation of this oEmbed resource when applicable',
 496     title varchar(255) comment 'title of oEmbed resource when available',
 497     author_name varchar(50) comment 'author name for this oEmbed resource',
 498     author_url varchar(255) comment 'author URL for this oEmbed resource',
 499     url varchar(255) comment 'URL for this oEmbed resource when applicable (photo, link)',
 500     modified timestamp comment 'date this record was modified'
 501
 502 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
 503
 504 create table file_redirection (
 505
 506     url varchar(255) primary key comment 'short URL (or any other kind of redirect) for file (id)',
 507     file_id integer comment 'short URL for what URL/file' references file (id),
 508     redirections integer comment 'redirect count',
 509     httpcode integer comment 'HTTP status code (20x, 30x, etc.)',
 510     modified timestamp comment 'date this record was modified'
 511
 512 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 513
 514 create table file_thumbnail (
 515
 516     file_id integer primary key comment 'thumbnail for what URL/file' references file (id),
 517     url varchar(255) comment 'URL of thumbnail',
 518     width integer comment 'width of thumbnail',
 519     height integer comment 'height of thumbnail',
 520     modified timestamp comment 'date this record was modified',
 521
 522     unique(url)
 523 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 524
 525 create table file_to_post (
 526
 527     file_id integer comment 'id of URL/file' references file (id),
 528     post_id integer comment 'id of the notice it belongs to' references notice (id),
 529     modified timestamp comment 'date this record was modified',
 530
 531     constraint primary key (file_id, post_id),
 532     index post_id_idx (post_id)
 533
 534 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 535
 536 create table design (
 537     id integer primary key auto_increment comment 'design ID',
 538     backgroundcolor integer comment 'main background color',
 539     contentcolor integer comment 'content area background color',
 540     sidebarcolor integer comment 'sidebar background color',
 541     textcolor integer comment 'text color',
 542     linkcolor integer comment 'link color',
 543     backgroundimage varchar(255) comment 'background image, if any',
 544     disposition tinyint default 1 comment 'bit 1 = hide background image, bit 2 = display background image, bit 4 = tile background image'
 545 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 546
 547 create table group_block (
 548    group_id integer not null comment 'group profile is blocked from' references user_group (id),
 549    blocked integer not null comment 'profile that is blocked' references profile (id),
 550    blocker integer not null comment 'user making the block' references user (id),
 551    modified timestamp comment 'date of blocking',
 552
 553    constraint primary key (group_id, blocked)
 554
 555 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 556
 557 create table group_alias (
 558
 559    alias varchar(64) primary key comment 'additional nickname for the group',
 560    group_id integer not null comment 'group profile is blocked from' references user_group (id),
 561    modified timestamp comment 'date alias was created',
 562
 563    index group_alias_group_id_idx (group_id)
 564
 565 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 566
 567 create table session (
 568
 569     id varchar(32) primary key comment 'session ID',
 570     session_data text comment 'session data',
 571     created datetime not null comment 'date this record was created',
 572     modified timestamp comment 'date this record was modified',
 573
 574     index session_modified_idx (modified)
 575
 576 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 577
 578 create table deleted_notice (
 579
 580     id integer primary key comment 'identity of notice',
 581     profile_id integer not null comment 'author of the notice',
 582     uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI',
 583     created datetime not null comment 'date the notice record was created',
 584     deleted datetime not null comment 'date the notice record was created',
 585
 586     index deleted_notice_profile_id_idx (profile_id)
 587
 588 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 589
 590 create table config (
 591
 592     section varchar(32) comment 'configuration section',
 593     setting varchar(32) comment 'configuration setting',
 594     value varchar(255) comment 'configuration value',
 595
 596     constraint primary key (section, setting)
 597
 598 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 599
 600 create table profile_role (
 601
 602     profile_id integer not null comment 'account having the role' references profile (id),
 603     role    varchar(32) not null comment 'string representing the role',
 604     created datetime not null comment 'date the role was granted',
 605
 606     constraint primary key (profile_id, role)
 607
 608 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 609
 610 create table location_namespace (
 611
 612     id integer primary key comment 'identity for this namespace',
 613     description varchar(255) comment 'description of the namespace',
 614     created datetime not null comment 'date the record was created',
 615     modified timestamp comment 'date this record was modified'
 616
 617 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 618
 619 create table login_token (
 620     user_id integer not null comment 'user owning this token' references user (id),
 621     token char(32) not null comment 'token useable for logging in',
 622     created datetime not null comment 'date this record was created',
 623     modified timestamp comment 'date this record was modified',
 624
 625     constraint primary key (user_id)
 626 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 627
 628 create table user_location_prefs (
 629     user_id integer not null comment 'user who has the preference' references user (id),
 630     share_location tinyint default 1 comment 'Whether to share location data',
 631     created datetime not null comment 'date this record was created',
 632     modified timestamp comment 'date this record was modified',
 633
 634     constraint primary key (user_id)
 635 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 636
 637 create table inbox (
 638
 639     user_id integer not null comment 'user receiving the notice' references user (id),
 640     notice_ids blob comment 'packed list of notice ids',
 641
 642     constraint primary key (user_id)
 643
 644 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 645
 646 create table user_im_prefs (
 647     user_id integer not null comment 'user' references user (id),
 648     screenname varchar(255) not null comment 'screenname on this service',
 649     transport varchar(255) not null comment 'transport (ex xmpp, aim)',
 650     notify tinyint(1) not null default 0 comment 'Notify when a new notice is sent',
 651     replies tinyint(1) not null default 0 comment 'Send replies  from people not subscribed to',
 652     microid tinyint(1) not null default 1 comment 'Publish a MicroID',
 653     updatefrompresence tinyint(1) not null default 0 comment 'Send replies  from people not subscribed to.',
 654     created timestamp not null DEFAULT CURRENT_TIMESTAMP comment 'date this record was created',
 655     modified timestamp comment 'date this record was modified',
 656
 657     constraint primary key (user_id, transport),
 658     constraint unique key `transport_screenname_key` ( `transport` , `screenname` )
 659 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 660
 661 create table conversation (
 662     id integer auto_increment primary key comment 'unique identifier',
 663     uri varchar(225) unique comment 'URI of the conversation',
 664     created datetime not null comment 'date this record was created',
 665     modified timestamp comment 'date this record was modified'
 666 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 667
 668 create table local_group (
 669
 670    group_id integer primary key comment 'group represented' references user_group (id),
 671    nickname varchar(64) unique key comment 'group represented',
 672
 673    created datetime not null comment 'date this record was created',
 674    modified timestamp comment 'date this record was modified'
 675
 676 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 677
 678 create table user_urlshortener_prefs (
 679
 680    user_id integer not null comment 'user' references user (id),
 681    urlshorteningservice varchar(50) default 'ur1.ca' comment 'service to use for auto-shortening URLs',
 682    maxurllength integer not null comment 'urls greater than this length will be shortened, 0 = always, null = never',
 683    maxnoticelength integer not null comment 'notices with content greater than this value will have all urls shortened, 0 = always, null = never',
 684
 685    created datetime not null comment 'date this record was created',
 686    modified timestamp comment 'date this record was modified',
 687
 688    constraint primary key (user_id)
 689 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;