Merge branch '0.9.x' of git@gitorious.org:statusnet/mainline into 0.9.x

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

diff --git a/db/statusnet.sql b/db/statusnet.sql
index 1524d83957c27815fd2f83986b2b8938f1a80d5a..18abcdfdb2d8b479b2b4489205ab31990a21991b 100644 (file)
--- a/db/statusnet.sql
+++ b/db/statusnet.sql
@@ -107,8 +107,8 @@ create table subscription (
     modified timestamp comment 'date this record was modified',
 
     constraint primary key (subscriber, subscribed),
-    index subscription_subscriber_idx (subscriber),
-    index subscription_subscribed_idx (subscribed),
+    index subscription_subscriber_idx (subscriber, created),
+    index subscription_subscribed_idx (subscribed, created),
     index subscription_token_idx (token)
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
@@ -130,7 +130,7 @@ create table notice (
     location_id integer comment 'location id if possible',
     location_ns integer comment 'namespace for location',
 
-    index notice_profile_id_idx (profile_id),
+    index notice_profile_id_idx (profile_id,created,id),
     index notice_conversation_idx (conversation),
     index notice_created_idx (created),
     index notice_replyto_idx (reply_to),
@@ -165,7 +165,7 @@ create table fave (
 
     constraint primary key (notice_id, user_id),
     index fave_notice_id_idx (notice_id),
-    index fave_user_id_idx (user_id),
+    index fave_user_id_idx (user_id,modified),
     index fave_modified_idx (modified)
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
@@ -557,13 +557,13 @@ create table config (
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
-create table user_role (
+create table profile_role (
 
-    user_id integer not null comment 'user having the role' references user (id),
+    profile_id integer not null comment 'account having the role' references profile (id),
     role    varchar(32) not null comment 'string representing the role',
     created datetime not null comment 'date the role was granted',
 
-    constraint primary key (user_id, role)
+    constraint primary key (profile_id, role)
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
@@ -575,3 +575,13 @@ create table location_namespace (
     modified timestamp comment 'date this record was modified'
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table login_token (
+    user_id integer not null comment 'user owning this token' references user (id),
+    token char(32) not null comment 'token useable for logging in',
+    created datetime not null comment 'date this record was created',
+    modified timestamp comment 'date this record was modified',
+
+    constraint primary key (user_id)
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+