location_ns integer comment 'namespace for location',
repeat_of integer comment 'notice this is a repeat of' references notice (id),
+ -- For public timeline...
+ index notice_created_id_is_local_idx (created,id,is_local),
+
+ -- For profile timelines...
index notice_profile_id_idx (profile_id,created,id),
- index notice_conversation_idx (conversation),
- index notice_created_idx (created),
+
+ -- For api/statuses/repeats...
+ index notice_repeat_of_created_id_idx (repeat_of, created, id),
+
+ -- For conversation views
+ index notice_conversation_created_id_idx (conversation, created, id),
+
+ -- Are these needed/used?
index notice_replyto_idx (reply_to),
- index notice_repeatof_idx (repeat_of),
+
FULLTEXT(content)
) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
constraint primary key (notice_id, profile_id),
index reply_notice_id_idx (notice_id),
index reply_profile_id_idx (profile_id),
- index reply_replied_id_idx (replied_id)
+ index reply_replied_id_idx (replied_id),
+
+ -- Needed for sorting reply/mentions timelines
+ index reply_profile_id_modified_notice_id_idx (profile_id, modified, notice_id)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
id integer auto_increment primary key comment 'unique identifier',
owner integer not null comment 'owner of the application' references profile (id),
consumer_key varchar(255) not null comment 'application consumer key' references consumer (consumer_key),
- name varchar(255) not null comment 'name of the application',
+ name varchar(255) not null unique key comment 'name of the application',
description varchar(255) comment 'description of the application',
icon varchar(255) not null comment 'application icon',
source_url varchar(255) comment 'application homepage - used for source link',
create table oauth_application_user (
profile_id integer not null comment 'user of the application' references profile (id),
application_id integer not null comment 'id of the application' references oauth_application (id),
- access_type tinyint default 0 comment 'access type, bit 1 = read, bit 2 = write, bit 3 = revoked',
+ access_type tinyint default 0 comment 'access type, bit 1 = read, bit 2 = write',
token varchar(255) comment 'request or access token',
created datetime not null comment 'date this record was created',
modified timestamp comment 'date this record was modified',
constraint primary key (profile_id, application_id)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+create table oauth_token_association (
+ profile_id integer not null comment 'user of the application' references profile (id),
+ application_id integer not null comment 'id of the application' references oauth_application (id),
+ token varchar(255) comment 'request or access token',
+ created datetime not null comment 'date this record was created',
+ modified timestamp comment 'date this record was modified',
+ constraint primary key (profile_id, application_id, token)
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
/* These are used by JanRain OpenID library */
create table oid_associations (
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
create table queue_item (
-
- notice_id integer not null comment 'notice queued' references notice (id),
+ id integer auto_increment primary key comment 'unique identifier',
+ frame blob not null comment 'data: object reference or opaque string',
transport varchar(8) not null comment 'queue for what? "email", "jabber", "sms", "irc", ...',
created datetime not null comment 'date this record was created',
claimed datetime comment 'date this item was claimed',
- constraint primary key (notice_id, transport),
index queue_item_created_idx (created)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
constraint primary key (tag, notice_id),
index notice_tag_created_idx (created),
- index notice_tag_notice_id_idx (notice_id)
+ index notice_tag_notice_id_idx (notice_id),
+
+ -- For sorting tag-filtered public timeline
+ index notice_tag_tag_created_notice_id_idx (tag, created, notice_id)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
/* Synching with foreign services */
create table user_group (
id integer auto_increment primary key comment 'unique identifier',
- nickname varchar(64) unique key comment 'nickname for addressing',
+ nickname varchar(64) comment 'nickname for addressing',
fullname varchar(255) comment 'display name',
homepage varchar(255) comment 'URL, cached so we dont regenerate',
description text comment 'group description',
created datetime not null comment 'date this record was created',
modified timestamp comment 'date this record was modified',
+ uri varchar(255) unique key comment 'universal identifier',
+ mainpage varchar(255) comment 'page for group info to link to',
+
index user_group_nickname_idx (nickname)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
constraint primary key (group_id, profile_id),
index group_member_profile_id_idx (profile_id),
- index group_member_created_idx (created)
+ index group_member_created_idx (created),
+
+ -- To pull up a list of someone's groups in order joined
+ index group_member_profile_id_created_idx (profile_id, created)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
created datetime not null comment 'date the notice was created',
constraint primary key (group_id, notice_id),
- index group_inbox_created_idx (created)
+ index group_inbox_created_idx (created),
+ index group_inbox_notice_id_idx (notice_id),
+
+ -- Needed for sorting group messages by timestamp
+ index group_inbox_group_id_created_notice_id_idx (group_id, created, notice_id)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
post_id integer comment 'id of the notice it belongs to' references notice (id),
modified timestamp comment 'date this record was modified',
- constraint primary key (file_id, post_id)
+ constraint primary key (file_id, post_id),
+ index post_id_idx (post_id)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
role varchar(32) not null comment 'string representing the role',
created datetime not null comment 'date the role was granted',
- constraint primary key (profile_id, role)
+ constraint primary key (profile_id, role),
+ index profile_role_role_created_profile_id_idx (role, created, profile_id)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
constraint primary key (user_id)
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table conversation (
+ id integer auto_increment primary key comment 'unique identifier',
+ uri varchar(225) unique comment 'URI of the conversation',
+ created datetime not null comment 'date this record was created',
+ modified timestamp comment 'date this record was modified'
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table local_group (
+
+ group_id integer primary key comment 'group represented' references user_group (id),
+ nickname varchar(64) unique key comment 'group represented',
+
+ created datetime not null comment 'date this record was created',
+ modified timestamp comment 'date this record was modified'
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
projects / quix0rs-gnu-social.git / blobdiff