Allow unlimited-size bios in profiles

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

diff --git a/db/laconica.sql b/db/laconica.sql
index b018afec8ee0f1472261876cb72bf8df7cb2ea31..724877f92e4917e07c8cadeba9231141bff81f59 100644 (file)
--- a/db/laconica.sql
+++ b/db/laconica.sql
@@ -6,7 +6,7 @@ create table profile (
     fullname varchar(255) comment 'display name',
     profileurl varchar(255) comment 'URL, cached so we dont regenerate',
     homepage varchar(255) comment 'identifying URL',
-    bio varchar(140) comment 'descriptive biography',
+    bio text comment 'descriptive biography',
     location varchar(255) comment 'physical location',
     created datetime not null comment 'date this record was created',
     modified timestamp comment 'date this record was modified',
@@ -110,7 +110,7 @@ create table notice (
     id integer auto_increment primary key comment 'unique identifier',
     profile_id integer not null comment 'who made the update' references profile (id),
     uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI',
-    content varchar(140) comment 'update content',
+    content text comment 'update content',
     rendered text comment 'HTML version of the content',
     url varchar(255) comment 'URL of any attachment (image, video, bookmark, whatever)',
     created datetime not null comment 'date this record was created',
@@ -277,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',
@@ -331,7 +331,7 @@ create table message (
     uri varchar(255) unique key comment 'universally unique identifier',
     from_profile integer not null comment 'who the message is from' references profile (id),
     to_profile integer not null comment 'who the message is to' references profile (id),
-    content varchar(140) comment 'message content',
+    content text comment 'message content',
     rendered text comment 'HTML version of the content',
     url varchar(255) comment 'URL of any attachment (image, video, bookmark, whatever)',
     created datetime not null comment 'date this record was created',
@@ -387,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',
@@ -430,6 +431,7 @@ 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',
@@ -437,13 +439,15 @@ create table file (
     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=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
 
 create table file_oembed (
-    id integer primary key auto_increment,
-    file_id integer comment 'oEmbed for that URL/file' references file (id),
+    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',
@@ -455,37 +459,39 @@ create table file_oembed (
     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'
 
-    unique(file_id)
-) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
 
 create table file_redirection (
-    id integer primary key auto_increment,
-    url varchar(255) comment 'short URL (or any other kind of redirect) for file (id)',
+
+    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'
 
-    unique(url)
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
 create table file_thumbnail (
-    id integer primary key auto_increment,
-    file_id integer comment 'thumbnail for what URL/file' references file (id),
+
+    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(file_id),
     unique(url)
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
 create table file_to_post (
-    id integer primary key auto_increment,
+
     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)
 
-    unique(file_id, post_id)
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
 create table design (
@@ -518,3 +524,26 @@ create table group_alias (
    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;
+
+create table deleted_notice (
+
+    id integer primary key comment 'identity of notice',
+    profile_id integer not null comment 'author of the notice',
+    uri varchar(255) unique key comment 'universally unique identifier, usually a tag URI',
+    created datetime not null comment 'date the notice record was created',
+    deleted datetime not null comment 'date the notice record was created',
+
+    index deleted_notice_profile_id_idx (profile_id)
+
+) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;