From e9213043a820095cceeb6cacbfacf08bba9d3142 Mon Sep 17 00:00:00 2001 From: Evan Prodromou Date: Sat, 18 Apr 2009 19:36:25 -0700 Subject: add reply_to index to notice --- db/laconica.sql | 1 + 1 file changed, 1 insertion(+) (limited to 'db/laconica.sql') diff --git a/db/laconica.sql b/db/laconica.sql index a790a3fd2..5b57494d9 100644 --- a/db/laconica.sql +++ b/db/laconica.sql @@ -117,6 +117,7 @@ create table notice ( index notice_profile_id_idx (profile_id), index notice_created_idx (created), + index notice_replyto_idx (reply_to), FULLTEXT(content) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci; -- cgit v1.2.3-54-g00ecf From f798d1ea430d25c2f4a60179c65b39b1257a5340 Mon Sep 17 00:00:00 2001 From: Zach Copley Date: Tue, 28 Apr 2009 17:08:20 -0700 Subject: Added dirty dates to Foreign_link --- classes/Foreign_link.php | 2 ++ classes/laconica.ini | 2 ++ db/laconica.sql | 2 ++ 3 files changed, 6 insertions(+) (limited to 'db/laconica.sql') diff --git a/classes/Foreign_link.php b/classes/Foreign_link.php index 5d9c82a85..606560951 100644 --- a/classes/Foreign_link.php +++ b/classes/Foreign_link.php @@ -17,6 +17,8 @@ class Foreign_link extends Memcached_DataObject public $noticesync; // tinyint(1) not_null default_1 public $friendsync; // tinyint(1) not_null default_2 public $profilesync; // tinyint(1) not_null default_1 + public $last_noticesync; // datetime() + public $last_friendsync; // datetime() public $created; // datetime() not_null public $modified; // timestamp() not_null default_CURRENT_TIMESTAMP diff --git a/classes/laconica.ini b/classes/laconica.ini index dd424bbdd..5a905a4bb 100755 --- a/classes/laconica.ini +++ b/classes/laconica.ini @@ -55,6 +55,8 @@ credentials = 2 noticesync = 145 friendsync = 145 profilesync = 145 +last_noticesync = 14 +last_friendsync = 14 created = 142 modified = 384 diff --git a/db/laconica.sql b/db/laconica.sql index 83d610f0d..d9e21a7b5 100644 --- a/db/laconica.sql +++ b/db/laconica.sql @@ -291,6 +291,8 @@ create table foreign_link ( noticesync tinyint not null default 1 comment 'notice synchronization, bit 1 = sync outgoing, bit 2 = sync incoming, bit 3 = filter local replies', friendsync tinyint not null default 2 comment 'friend synchronization, bit 1 = sync outgoing, bit 2 = sync incoming', profilesync tinyint not null default 1 comment 'profile synchronization, bit 1 = sync outgoing, bit 2 = sync incoming', + last_noticesync datetime default null comment 'last time notices were imported', + last_friendsync datetime default null comment 'last time friends were imported', created datetime not null comment 'date this record was created', modified timestamp comment 'date this record was modified', -- cgit v1.2.3-54-g00ecf From 6c4e26fe61a3c1cada4775a42a929eb884150b2d Mon Sep 17 00:00:00 2001 From: Robin Millette Date: Fri, 15 May 2009 16:17:44 -0400 Subject: Added sql files to handle attachments (not yet tested) Hoping someone will improve the postgres version. --- db/laconica.sql | 59 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ db/laconica_pg.sql | 58 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 117 insertions(+) (limited to 'db/laconica.sql') diff --git a/db/laconica.sql b/db/laconica.sql index d9e21a7b5..344f0ff72 100644 --- a/db/laconica.sql +++ b/db/laconica.sql @@ -425,3 +425,62 @@ create table group_inbox ( ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; +create table file ( + id integer primary key auto_increment, + url varchar(255), mimetype varchar(50), + size integer, + title varchar(255), + date integer(11), + protected integer(1), + + unique(url) +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci; + +create table file_oembed ( + id integer primary key auto_increment, + file_id integer, + 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), + + unique(file_id) +) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci; + +create table file_redirection ( + id integer primary key auto_increment, + url varchar(255), + file_id integer, + redirections integer, + httpcode integer, + + unique(url) +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + +create table file_thumbnail ( + id integer primary key auto_increment, + file_id integer, + url varchar(255), + width integer, + height integer, + + 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, + post_id integer, + + unique(file_id, post_id) +) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; + + diff --git a/db/laconica_pg.sql b/db/laconica_pg.sql index a27a616f2..b213bbd50 100644 --- a/db/laconica_pg.sql +++ b/db/laconica_pg.sql @@ -427,6 +427,64 @@ 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(11), + protected integer(1) +); + +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) +); + + /* Textsearch stuff */ create index textsearch_idx on profile using gist(textsearch); -- cgit v1.2.3-54-g00ecf From 9a7dbbc78118feb5266e78244258a2fbbcc2f405 Mon Sep 17 00:00:00 2001 From: Evan Prodromou Date: Fri, 22 May 2009 20:53:22 -0400 Subject: reformat laconica.sql --- db/laconica.sql | 48 +++++++++++++++++++++++------------------------- 1 file changed, 23 insertions(+), 25 deletions(-) (limited to 'db/laconica.sql') diff --git a/db/laconica.sql b/db/laconica.sql index 344f0ff72..0b20bc172 100644 --- a/db/laconica.sql +++ b/db/laconica.sql @@ -426,12 +426,12 @@ create table group_inbox ( ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; create table file ( - id integer primary key auto_increment, - url varchar(255), mimetype varchar(50), - size integer, - title varchar(255), - date integer(11), - protected integer(1), + id integer primary key auto_increment, + url varchar(255), mimetype varchar(50), + size integer, + title varchar(255), + date integer(11), + protected integer(1), unique(url) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci; @@ -447,40 +447,38 @@ create table file_oembed ( height integer, html text, title varchar(255), - author_name varchar(50), - author_url varchar(255), - url varchar(255), + author_name varchar(50), + author_url varchar(255), + url varchar(255), unique(file_id) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci; create table file_redirection ( - id integer primary key auto_increment, - url varchar(255), - file_id integer, - redirections integer, - httpcode integer, + id integer primary key auto_increment, + url varchar(255), + file_id integer, + redirections integer, + httpcode integer, unique(url) ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; create table file_thumbnail ( - id integer primary key auto_increment, - file_id integer, - url varchar(255), - width integer, - height integer, + id integer primary key auto_increment, + file_id integer, + url varchar(255), + width integer, + height integer, - unique(file_id), + 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, - post_id integer, + id integer primary key auto_increment, + file_id integer, + post_id integer, unique(file_id, post_id) ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; - - -- cgit v1.2.3-54-g00ecf