Merge remote-tracking branch 'evan/blogplugin' into newblogplugin

[quix0rs-gnu-social.git] / db / statusnet.sql

diff --git a/db/statusnet.sql b/db/statusnet.sql
index a0c497fff5ccb89bb59074a33dd2c1f59306bc22..29a555948bc219f26cb15b147b03ef2bb1910549 100644 (file)
--- a/db/statusnet.sql
+++ b/db/statusnet.sql
@@ -126,11 +126,21 @@ create table notice (
     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;
 
@@ -151,7 +161,10 @@ create table reply (
     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;
 
@@ -232,6 +245,15 @@ create table oauth_application_user (
     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 (
@@ -287,7 +309,10 @@ create table notice_tag (
 
     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 */
@@ -378,7 +403,7 @@ create table notice_inbox (
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
 create table profile_tag (
-   tagger integer not null comment 'user making the tag' references user (id),
+   tagger integer not null comment 'user making the tag' references profile (id),
    tagged integer not null comment 'profile tagged' references profile (id),
    tag varchar(64) not null comment 'hash tag associated with this notice',
    modified timestamp comment 'date the tag was added',
@@ -389,6 +414,53 @@ create table profile_tag (
    index profile_tag_tagged_idx (tagged)
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
+/* people tag metadata */
+create table profile_list (
+    id integer auto_increment unique key comment 'unique identifier',
+    tagger integer not null comment 'user making the tag' references profile (id),
+    tag varchar(64) not null comment 'hash tag',
+    description text comment 'description for the tag',
+    private tinyint(1) default 0 comment 'is this list private',
+
+    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 tag info info to link to',
+    tagged_count smallint not null default 0 comment 'number of people tagged',
+    subscriber_count smallint not null default 0 comment 'number of people subscribing',
+
+    constraint primary key (tagger, tag),
+    index profile_list_tag_idx (tag),
+    index profile_list_tagged_count_idx (tagged_count),
+    index profile_list_modified_idx (modified),
+    index profile_list_subscriber_count_idx (subscriber_count)
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table profile_tag_inbox (
+    profile_tag_id integer not null comment 'peopletag receiving the message' references profile_tag (id),
+    notice_id integer not null comment 'notice received' references notice (id),
+    created datetime not null comment 'date the notice was created',
+
+    constraint primary key (profile_tag_id, notice_id),
+    index profile_tag_inbox_created_idx (created),
+    index profile_tag_inbox_notice_id_idx (notice_id)
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table profile_tag_subscription (
+    profile_tag_id integer not null comment 'foreign key to profile_tag' references profile_list (id),
+
+    profile_id integer not null comment 'foreign key to profile table' references profile (id),
+    created datetime not null comment 'date this record was created',
+    modified timestamp comment 'date this record was modified',
+
+    constraint primary key (profile_tag_id, profile_id),
+    index profile_tag_subscription_profile_id_idx (profile_id),
+    index profile_tag_subscription_created_idx (created)
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
 create table profile_block (
    blocker integer not null comment 'user making the block' references user (id),
    blocked integer not null comment 'profile that is blocked' references profile (id),
@@ -433,7 +505,10 @@ create table group_member (
 
     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;
 
@@ -454,7 +529,10 @@ create table group_inbox (
 
     constraint primary key (group_id, notice_id),
     index group_inbox_created_idx (created),
-    index group_inbox_notice_id_idx (notice_id)
+    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;
 
@@ -594,7 +672,8 @@ create table profile_role (
     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;