X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=db%2Flaconica.sql;h=c2cd887deecae2363a87f15f4130e3f3528c632a;hb=1d610d3c6f9bb15a5cea93758ddd8d0ce64099f6;hp=42e427d0bd7da44034398ef9fba5a183d608dc35;hpb=9c3fdeeafb83899af5b5e1fc15147d0a5fa9043f;p=quix0rs-gnu-social.git diff --git a/db/laconica.sql b/db/laconica.sql index 42e427d0bd..c2cd887dee 100644 --- a/db/laconica.sql +++ b/db/laconica.sql @@ -31,7 +31,7 @@ create table avatar ( ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; create table sms_carrier ( - id integer auto_increment primary key comment 'primary key for SMS carrier', + id integer primary key comment 'primary key for SMS carrier', name varchar(64) unique key comment 'name of the carrier', email_pattern varchar(255) not null comment 'sprintf pattern for making an email address from a phone number', created datetime not null comment 'date this record was created', @@ -50,6 +50,7 @@ create table user ( emailnotifyfav tinyint default 1 comment 'Notify by email of favorites', emailnotifynudge tinyint default 1 comment 'Notify by email of nudges', emailnotifymsg tinyint default 1 comment 'Notify by email of direct messages', + emailnotifyattn tinyint default 1 comment 'Notify by email of @-replies', emailmicroid tinyint default 1 comment 'whether to publish email microid', language varchar(50) comment 'preferred language', timezone varchar(50) comment 'timezone', @@ -88,6 +89,8 @@ create table remote_profile ( create table subscription ( subscriber integer not null comment 'profile listening', subscribed integer not null comment 'profile being listened to', + jabber tinyint default 1 comment 'deliver jabber messages', + sms tinyint default 1 comment 'deliver sms messages', token varchar(255) comment 'authorization token', secret varchar(255) comment 'token secret', created datetime not null comment 'date this record was created', @@ -168,7 +171,7 @@ create table token ( tok char(32) not null comment 'identifying value', secret char(32) not null comment 'secret value', type tinyint not null default 0 comment 'request or access', - state tinyint default 0 comment 'for requests; 0 = initial, 1 = authorized, 2 = used', + state tinyint default 0 comment 'for requests, 0 = initial, 1 = authorized, 2 = used', created datetime not null comment 'date this record was created', modified timestamp comment 'date this record was modified', @@ -256,7 +259,8 @@ create table notice_tag ( created datetime not null comment 'date this record was created', constraint primary key (tag, notice_id), - index notice_tag_created_idx (created) + index notice_tag_created_idx (created), + index notice_tag_notice_id_idx (notice_id) ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; /* Synching with foreign services */ @@ -340,7 +344,7 @@ create table notice_inbox ( user_id integer not null comment 'user receiving the message' references user (id), notice_id integer not null comment 'notice received' references notice (id), created datetime not null comment 'date the notice was created', - source tinyint default 1 comment 'reason it is in the inbox; 1=subscription', + source tinyint default 1 comment 'reason it is in the inbox, 1=subscription', constraint primary key (user_id, notice_id), index notice_inbox_notice_id_idx (notice_id) @@ -354,7 +358,8 @@ create table profile_tag ( constraint primary key (tagger, tagged, tag), index profile_tag_modified_idx (modified), - index profile_tag_tagger_tag_idx (tagger, tag) + index profile_tag_tagger_tag_idx (tagger, tag), + index profile_tag_tagged_idx (tagged) ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; create table profile_block ( @@ -366,3 +371,62 @@ create table profile_block ( constraint primary key (blocker, blocked) ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table user_group ( + + id integer auto_increment primary key comment 'unique identifier', + + nickname varchar(64) unique key comment 'nickname for addressing', + fullname varchar(255) comment 'display name', + homepage varchar(255) comment 'URL, cached so we dont regenerate', + description varchar(140) comment 'descriptive biography', + location varchar(255) comment 'related physical location, if any', + + original_logo varchar(255) comment 'original size logo', + homepage_logo varchar(255) comment 'homepage (profile) size logo', + stream_logo varchar(255) comment 'stream-sized logo', + mini_logo varchar(255) comment 'mini logo', + + created datetime not null comment 'date this record was created', + modified timestamp comment 'date this record was modified', + + index user_group_nickname_idx (nickname) + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table group_member ( + + group_id integer not null comment 'foreign key to user_group' references user_group (id), + profile_id integer not null comment 'foreign key to profile table' references profile (id), + is_admin boolean default false comment 'is this user an admin?', + + created datetime not null comment 'date this record was created', + modified timestamp comment 'date this record was modified', + + constraint primary key (group_id, profile_id), + index group_member_profile_id_idx (profile_id), + index group_member_created_idx (created) + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table related_group ( + + group_id integer not null comment 'foreign key to user_group' references user_group (id), + related_group_id integer not null comment 'foreign key to user_group' references user_group (id), + + created datetime not null comment 'date this record was created', + + constraint primary key (group_id, related_group_id) + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table group_inbox ( + group_id integer not null comment 'group receiving the message' references user_group (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 (group_id, notice_id), + index group_inbox_created_idx (created) + +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; +