summaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/074to080.sql109
-rw-r--r--db/foreign_services.sql3
-rw-r--r--db/innodb.sql2
-rw-r--r--db/laconica.sql116
-rw-r--r--db/laconica_pg.sql78
-rw-r--r--db/notice_source.sql1
-rw-r--r--db/site.sql21
7 files changed, 324 insertions, 6 deletions
diff --git a/db/074to080.sql b/db/074to080.sql
new file mode 100644
index 000000000..ff0819159
--- /dev/null
+++ b/db/074to080.sql
@@ -0,0 +1,109 @@
+alter table user
+ add column design_id integer comment 'id of a design' references design(id),
+ add column viewdesigns tinyint default 1 comment 'whether to view user-provided designs';
+
+alter table notice add column
+ conversation integer comment 'id of root notice in this conversation' references notice (id),
+ add index notice_conversation_idx (conversation);
+
+alter table foreign_user
+ modify column id bigint not null comment 'unique numeric key on foreign service';
+
+alter table foreign_link
+ modify column foreign_id bigint unsigned comment 'link to user on foreign service, if exists';
+
+alter table user_group
+ add column design_id integer comment 'id of a design' references design(id);
+
+create table file (
+ id integer primary key auto_increment,
+ url varchar(255) comment 'destination URL after following redirections',
+ mimetype varchar(50) comment 'mime type of resource',
+ size integer comment 'size of resource when available',
+ title varchar(255) comment 'title of resource when available',
+ date integer(11) comment 'date of resource according to http query',
+ protected integer(1) comment 'true when URL is private (needs login)',
+ filename varchar(255) comment 'if a local file, name of the file',
+ modified timestamp comment 'date this record was modified',
+
+ unique(url)
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+create table file_oembed (
+ file_id integer primary key comment 'oEmbed for that URL/file' references file (id),
+ version varchar(20) comment 'oEmbed spec. version',
+ type varchar(20) comment 'oEmbed type: photo, video, link, rich',
+ provider varchar(50) comment 'name of this oEmbed provider',
+ provider_url varchar(255) comment 'URL of this oEmbed provider',
+ width integer comment 'width of oEmbed resource when available',
+ height integer comment 'height of oEmbed resource when available',
+ html text comment 'html representation of this oEmbed resource when applicable',
+ title varchar(255) comment 'title of oEmbed resource when available',
+ author_name varchar(50) comment 'author name for this oEmbed resource',
+ author_url varchar(255) comment 'author URL for this oEmbed resource',
+ url varchar(255) comment 'URL for this oEmbed resource when applicable (photo, link)',
+ modified timestamp comment 'date this record was modified'
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+create table file_redirection (
+
+ url varchar(255) primary key comment 'short URL (or any other kind of redirect) for file (id)',
+ file_id integer comment 'short URL for what URL/file' references file (id),
+ redirections integer comment 'redirect count',
+ httpcode integer comment 'HTTP status code (20x, 30x, etc.)',
+ modified timestamp comment 'date this record was modified'
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table file_thumbnail (
+
+ file_id integer primary key comment 'thumbnail for what URL/file' references file (id),
+ url varchar(255) comment 'URL of thumbnail',
+ width integer comment 'width of thumbnail',
+ height integer comment 'height of thumbnail',
+ modified timestamp comment 'date this record was modified',
+
+ unique(url)
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table file_to_post (
+
+ file_id integer comment 'id of URL/file' references file (id),
+ 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)
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table design (
+ id integer primary key auto_increment comment 'design ID',
+ backgroundcolor integer comment 'main background color',
+ contentcolor integer comment 'content area background color',
+ sidebarcolor integer comment 'sidebar background color',
+ textcolor integer comment 'text color',
+ linkcolor integer comment 'link color',
+ backgroundimage varchar(255) comment 'background image, if any',
+ disposition tinyint default 1 comment 'bit 1 = hide background image, bit 2 = display background image, bit 4 = tile background image'
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table group_block (
+ group_id integer not null comment 'group profile is blocked from' references user_group (id),
+ blocked integer not null comment 'profile that is blocked' references profile (id),
+ blocker integer not null comment 'user making the block' references user (id),
+ modified timestamp comment 'date of blocking',
+
+ constraint primary key (group_id, blocked)
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table group_alias (
+
+ alias varchar(64) primary key comment 'additional nickname for the group',
+ group_id integer not null comment 'group profile is blocked from' references user_group (id),
+ modified timestamp comment 'date alias was created',
+
+ index group_alias_group_id_idx (group_id)
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
diff --git a/db/foreign_services.sql b/db/foreign_services.sql
index 557ede024..79c04cee5 100644
--- a/db/foreign_services.sql
+++ b/db/foreign_services.sql
@@ -2,4 +2,5 @@ insert into foreign_service
(id, name, description, created)
values
('1','Twitter', 'Twitter Micro-blogging service', now()),
- ('2','Facebook', 'Facebook', now());
+ ('2','Facebook', 'Facebook', now()),
+ ('3','FacebookConnect', 'Facebook Connect', now());
diff --git a/db/innodb.sql b/db/innodb.sql
new file mode 100644
index 000000000..f3ab6cd69
--- /dev/null
+++ b/db/innodb.sql
@@ -0,0 +1,2 @@
+alter table profile drop index nickname, engine=InnoDB;
+alter table notice drop index content, engine=InnoDB;
diff --git a/db/laconica.sql b/db/laconica.sql
index c9730098e..2c04f680a 100644
--- a/db/laconica.sql
+++ b/db/laconica.sql
@@ -41,6 +41,7 @@ create table sms_carrier (
/* local users */
create table user (
+
id integer primary key comment 'foreign key to profile table' references profile (id),
nickname varchar(64) unique key comment 'nickname or username, duped in profile',
password varchar(255) comment 'salted password, can be null for OpenID users',
@@ -69,6 +70,9 @@ create table user (
autosubscribe tinyint default 0 comment 'automatically subscribe to users who subscribe to us',
urlshorteningservice varchar(50) default 'ur1.ca' comment 'service to use for auto-shortening URLs',
inboxed tinyint default 0 comment 'has an inbox been created for this user?',
+ design_id integer comment 'id of a design' references design(id),
+ viewdesigns tinyint default 1 comment 'whether to view user-provided designs',
+
created datetime not null comment 'date this record was created',
modified timestamp comment 'date this record was modified',
@@ -114,8 +118,10 @@ create table notice (
reply_to integer comment 'notice replied to (usually a guess)' references notice (id),
is_local tinyint default 0 comment 'notice was generated by a user',
source varchar(32) comment 'source of comment, like "web", "im", or "clientname"',
+ conversation integer comment 'id of root notice in this conversation' references notice (id),
index notice_profile_id_idx (profile_id),
+ index notice_conversation_idx (conversation),
index notice_created_idx (created),
index notice_replyto_idx (reply_to),
FULLTEXT(content)
@@ -271,7 +277,7 @@ create table foreign_service (
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
create table foreign_user (
- id int not null comment 'unique numeric key on foreign service',
+ id bigint not null comment 'unique numeric key on foreign service',
service int not null comment 'foreign key to service' references foreign_service(id),
uri varchar(255) not null unique key comment 'identifying URI',
nickname varchar(255) comment 'nickname on foreign service',
@@ -283,7 +289,7 @@ create table foreign_user (
create table foreign_link (
user_id int comment 'link to user on this system, if exists' references user (id),
- foreign_id int comment 'link ' references foreign_user(id),
+ foreign_id bigint unsigned comment 'link to user on foreign service, if exists' references foreign_user(id),
service int not null comment 'foreign key to service' references foreign_service(id),
credentials varchar(255) comment 'authc credentials, typically a password',
noticesync tinyint not null default 1 comment 'notice synchronization, bit 1 = sync outgoing, bit 2 = sync incoming, bit 3 = filter local replies',
@@ -381,6 +387,7 @@ create table user_group (
homepage_logo varchar(255) comment 'homepage (profile) size logo',
stream_logo varchar(255) comment 'stream-sized logo',
mini_logo varchar(255) comment 'mini logo',
+ design_id integer comment 'id of a design' references design(id),
created datetime not null comment 'date this record was created',
modified timestamp comment 'date this record was modified',
@@ -423,3 +430,108 @@ create table group_inbox (
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+create table file (
+
+ id integer primary key auto_increment,
+ url varchar(255) comment 'destination URL after following redirections',
+ mimetype varchar(50) comment 'mime type of resource',
+ size integer comment 'size of resource when available',
+ title varchar(255) comment 'title of resource when available',
+ date integer(11) comment 'date of resource according to http query',
+ protected integer(1) comment 'true when URL is private (needs login)',
+ filename varchar(255) comment 'if a local file, name of the file',
+
+ modified timestamp comment 'date this record was modified',
+
+ unique(url)
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+create table file_oembed (
+ file_id integer primary key comment 'oEmbed for that URL/file' references file (id),
+ version varchar(20) comment 'oEmbed spec. version',
+ type varchar(20) comment 'oEmbed type: photo, video, link, rich',
+ provider varchar(50) comment 'name of this oEmbed provider',
+ provider_url varchar(255) comment 'URL of this oEmbed provider',
+ width integer comment 'width of oEmbed resource when available',
+ height integer comment 'height of oEmbed resource when available',
+ html text comment 'html representation of this oEmbed resource when applicable',
+ title varchar(255) comment 'title of oEmbed resource when available',
+ author_name varchar(50) comment 'author name for this oEmbed resource',
+ author_url varchar(255) comment 'author URL for this oEmbed resource',
+ url varchar(255) comment 'URL for this oEmbed resource when applicable (photo, link)',
+ modified timestamp comment 'date this record was modified'
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+create table file_redirection (
+
+ url varchar(255) primary key comment 'short URL (or any other kind of redirect) for file (id)',
+ file_id integer comment 'short URL for what URL/file' references file (id),
+ redirections integer comment 'redirect count',
+ httpcode integer comment 'HTTP status code (20x, 30x, etc.)',
+ modified timestamp comment 'date this record was modified'
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table file_thumbnail (
+
+ file_id integer primary key comment 'thumbnail for what URL/file' references file (id),
+ url varchar(255) comment 'URL of thumbnail',
+ width integer comment 'width of thumbnail',
+ height integer comment 'height of thumbnail',
+ modified timestamp comment 'date this record was modified',
+
+ unique(url)
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table file_to_post (
+
+ file_id integer comment 'id of URL/file' references file (id),
+ 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)
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table design (
+ id integer primary key auto_increment comment 'design ID',
+ backgroundcolor integer comment 'main background color',
+ contentcolor integer comment 'content area background color',
+ sidebarcolor integer comment 'sidebar background color',
+ textcolor integer comment 'text color',
+ linkcolor integer comment 'link color',
+ backgroundimage varchar(255) comment 'background image, if any',
+ disposition tinyint default 1 comment 'bit 1 = hide background image, bit 2 = display background image, bit 4 = tile background image'
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table group_block (
+ group_id integer not null comment 'group profile is blocked from' references user_group (id),
+ blocked integer not null comment 'profile that is blocked' references profile (id),
+ blocker integer not null comment 'user making the block' references user (id),
+ modified timestamp comment 'date of blocking',
+
+ constraint primary key (group_id, blocked)
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table group_alias (
+
+ alias varchar(64) primary key comment 'additional nickname for the group',
+ group_id integer not null comment 'group profile is blocked from' references user_group (id),
+ modified timestamp comment 'date alias was created',
+
+ index group_alias_group_id_idx (group_id)
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
+
+create table session (
+
+ id varchar(32) primary key comment 'session ID',
+ session_data text comment 'session data',
+ created datetime not null comment 'date this record was created',
+ modified timestamp comment 'date this record was modified',
+
+ index session_modified_idx (modified)
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; \ No newline at end of file
diff --git a/db/laconica_pg.sql b/db/laconica_pg.sql
index a27a616f2..dae8b8faf 100644
--- a/db/laconica_pg.sql
+++ b/db/laconica_pg.sql
@@ -116,7 +116,9 @@ create table notice (
modified timestamp /* comment 'date this record was modified' */,
reply_to integer /* comment 'notice replied to (usually a guess)' */ references notice (id) ,
is_local integer default 0 /* comment 'notice was generated by a user' */,
- source varchar(32) /* comment 'source of comment, like "web", "im", or "clientname"' */
+ source varchar(32) /* comment 'source of comment, like "web", "im", or "clientname"' */,
+ conversation integer /*id of root notice in this conversation' */ references notice (id)
+
/* FULLTEXT(content) */
);
@@ -172,7 +174,7 @@ create table token (
tok char(32) not null /* comment 'identifying value' */,
secret char(32) not null /* comment 'secret value' */,
type integer not null default 0 /* comment 'request or access' */,
- state integer default 0 /* comment 'for requests; 0 = initial, 1 = authorized, 2 = used' */,
+ state integer default 0 /* comment 'for requests 0 = initial, 1 = authorized, 2 = used' */,
created timestamp not null default CURRENT_TIMESTAMP /* comment 'date this record was created' */,
modified timestamp /* comment 'date this record was modified' */,
@@ -346,7 +348,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 timestamp not null default CURRENT_TIMESTAMP /* comment 'date the notice was created' */,
- source integer default 1 /* comment 'reason it is in the inbox; 1=subscription' */,
+ source integer default 1 /* comment 'reason it is in the inbox: 1=subscription' */,
primary key (user_id, notice_id)
);
@@ -427,6 +429,76 @@ create table group_inbox (
);
create index group_inbox_created_idx on group_inbox using btree(created);
+
+/*attachments and URLs stuff */
+create sequence file_seq;
+create table file (
+ id bigint default nextval('file_seq') primary key /* comment 'unique identifier' */,
+ url varchar(255) unique,
+ mimetype varchar(50),
+ size integer,
+ title varchar(255),
+ date integer,
+ protected integer
+);
+
+create sequence file_oembed_seq;
+create table file_oembed (
+ id bigint default nextval('file_oembed_seq') primary key /* comment 'unique identifier' */,
+ file_id bigint unique,
+ version varchar(20),
+ type varchar(20),
+ provider varchar(50),
+ provider_url varchar(255),
+ width integer,
+ height integer,
+ html text,
+ title varchar(255),
+ author_name varchar(50),
+ author_url varchar(255),
+ url varchar(255)
+);
+
+create sequence file_redirection_seq;
+create table file_redirection (
+ id bigint default nextval('file_redirection_seq') primary key /* comment 'unique identifier' */,
+ url varchar(255) unique,
+ file_id bigint,
+ redirections integer,
+ httpcode integer
+);
+
+create sequence file_thumbnail_seq;
+create table file_thumbnail (
+ id bigint default nextval('file_thumbnail_seq') primary key /* comment 'unique identifier' */,
+ file_id bigint unique,
+ url varchar(255) unique,
+ width integer,
+ height integer
+);
+
+create sequence file_to_post_seq;
+create table file_to_post (
+ id bigint default nextval('file_to_post_seq') primary key /* comment 'unique identifier' */,
+ file_id bigint,
+ post_id bigint,
+
+ unique(file_id, post_id)
+);
+
+create sequence design_seq;
+create table design (
+ id bigint default nextval('design_seq') /* comment 'design ID'*/,
+ backgroundcolor integer /* comment 'main background color'*/ ,
+ contentcolor integer /*comment 'content area background color'*/ ,
+ sidebarcolor integer /*comment 'sidebar background color'*/ ,
+ textcolor integer /*comment 'text color'*/ ,
+ linkcolor integer /*comment 'link color'*/,
+ backgroundimage varchar(255) /*comment 'background image, if any'*/,
+ disposition int default 1 /*comment 'bit 1 = hide background image, bit 2 = display background image, bit 4 = tile background image'*/,
+ primary key (id)
+);
+
/* Textsearch stuff */
create index textsearch_idx on profile using gist(textsearch);
diff --git a/db/notice_source.sql b/db/notice_source.sql
index 4c3dc2113..983ea9150 100644
--- a/db/notice_source.sql
+++ b/db/notice_source.sql
@@ -51,6 +51,7 @@ VALUES
('twidge','Twidge','http://software.complete.org/twidge', now()),
('twidroid','twidroid','http://www.twidroid.com/', now()),
('twittelator','Twittelator','http://www.stone.com/iPhone/Twittelator/', now()),
+ ('twitter','Twitter','http://twitter.com/', now()),
('twitterfeed','twitterfeed','http://twitterfeed.com/', now()),
('twitterphoto','TwitterPhoto','http://richfish.org/twitterphoto/', now()),
('twitterpm','Net::Twitter','http://search.cpan.org/dist/Net-Twitter/', now()),
diff --git a/db/site.sql b/db/site.sql
new file mode 100644
index 000000000..a9f64e5a5
--- /dev/null
+++ b/db/site.sql
@@ -0,0 +1,21 @@
+/* For managing multiple sites */
+
+create table status_network (
+
+ nickname varchar(64) primary key comment 'nickname',
+ hostname varchar(255) unique key comment 'alternate hostname if any',
+ pathname varchar(255) unique key comment 'alternate pathname if any',
+
+ dbhost varchar(255) comment 'database host',
+ dbuser varchar(255) comment 'database username',
+ dbpass varchar(255) comment 'database password',
+ dbname varchar(255) comment 'database name',
+
+ sitename varchar(255) comment 'display name',
+ theme varchar(255) comment 'theme name',
+ logo varchar(255) comment 'site logo',
+
+ 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_general_ci;
generated by cgit v1.2.3-54-g00ecf (git 2.45.2) at 2024-10-21 07:38:11 +0000