Merge branch '0.9.x' into 1.0.x

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

diff --git a/db/statusnet.sql b/db/statusnet.sql
index 8946f4d7e2e47c3382fd5be211c0ecb2b3a54772..16d09a11f762a8264df1cdbb90a82da2bdc2b287 100644 (file)
--- a/db/statusnet.sql
+++ b/db/statusnet.sql
@@ -62,11 +62,6 @@ create table user (
     language varchar(50) comment 'preferred language',
     timezone varchar(50) comment 'timezone',
     emailpost tinyint default 1 comment 'Post by email',
-    jabber varchar(255) unique key comment 'jabber ID for notices',
-    jabbernotify tinyint default 0 comment 'whether to send notices to jabber',
-    jabberreplies tinyint default 0 comment 'whether to send notices to jabber on replies',
-    jabbermicroid tinyint default 1 comment 'whether to publish xmpp microid',
-    updatefrompresence tinyint default 0 comment 'whether to record updates from Jabber presence notices',
     sms varchar(64) unique key comment 'sms phone number',
     carrier integer comment 'foreign key to sms_carrier' references sms_carrier (id),
     smsnotify tinyint default 0 comment 'whether to send notices to SMS',
@@ -214,7 +209,7 @@ create table oauth_application (
     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) unique key 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',
@@ -259,9 +254,9 @@ create table oid_nonces (
 create table confirm_address (
     code varchar(32) not null primary key comment 'good random code',
     user_id integer not null comment 'user who requested confirmation' references user (id),
-    address varchar(255) not null comment 'address (email, Jabber, SMS, etc.)',
+    address varchar(255) not null comment 'address (email, xmpp, SMS, etc.)',
     address_extra varchar(255) not null comment 'carrier ID, for SMS',
-    address_type varchar(8) not null comment 'address type ("email", "jabber", "sms")',
+    address_type varchar(8) not null comment 'address type ("email", "xmpp", "sms")',
     claimed datetime comment 'date this was claimed for queueing',
     sent datetime comment 'date this was sent for queueing',
     modified timestamp comment 'date this record was modified'
@@ -276,7 +271,7 @@ create table remember_me (
 create table queue_item (
     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", ...',
+    transport varchar(8) not null comment 'queue for what? "email", "xmpp", "sms", "irc", ...',
     created datetime not null comment 'date this record was created',
     claimed datetime comment 'date this item was claimed',
 
@@ -348,7 +343,7 @@ create table invitation (
      code varchar(32) not null primary key comment 'random code for an invitation',
      user_id int not null comment 'who sent the invitation' references user (id),
      address varchar(255) not null comment 'invitation sent to',
-     address_type varchar(8) not null comment 'address type ("email", "jabber", "sms")',
+     address_type varchar(8) not null comment 'address type ("email", "xmpp", "sms")',
      created datetime not null comment 'date this record was created',
 
      index invitation_address_idx (address, address_type),
@@ -406,7 +401,7 @@ create table profile_block (
 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',
@@ -421,6 +416,9 @@ create table user_group (
     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;
@@ -455,7 +453,8 @@ create table group_inbox (
     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)
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
@@ -520,7 +519,8 @@ create table file_to_post (
     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;
 
@@ -633,3 +633,35 @@ create table inbox (
     constraint primary key (user_id)
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table user_im_prefs (
+    user_id integer not null comment 'user' references user (id),
+    screenname varchar(255) not null comment 'screenname on this service',
+    transport varchar(255) not null comment 'transport (ex xmpp, aim)',
+    notify tinyint(1) not null default 0 comment 'Notify when a new notice is sent',
+    replies tinyint(1) not null default 0 comment 'Send replies  from people not subscribed to',
+    microid tinyint(1) not null default 1 comment 'Publish a MicroID',
+    updatefrompresence tinyint(1) not null default 0 comment 'Send replies  from people not subscribed to.',
+    created timestamp not null DEFAULT CURRENT_TIMESTAMP comment 'date this record was created',
+    modified timestamp comment 'date this record was modified',
+
+    constraint primary key (user_id, transport),
+    constraint unique key `transport_screenname_key` ( `transport` , `screenname` )
+) 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;