diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/lite.sql | 24 | ||||
| -rw-r--r-- | sql/mysql.sql | 25 | ||||
| -rw-r--r-- | sql/pg.sql | 29 |
3 files changed, 75 insertions, 3 deletions
diff --git a/sql/lite.sql b/sql/lite.sql index 21e4df929..461686d1a 100644 --- a/sql/lite.sql +++ b/sql/lite.sql @@ -270,3 +270,27 @@ CREATE TABLE caps_features ( ); CREATE INDEX i_caps_features_node_subnode ON caps_features (node, subnode); + +CREATE TABLE archive ( + username text NOT NULL, + timestamp BIGINT UNSIGNED NOT NULL, + peer text NOT NULL, + bare_peer text NOT NULL, + xml text NOT NULL, + txt text, + id INTEGER PRIMARY KEY AUTOINCREMENT, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE INDEX i_username ON archive(username); +CREATE INDEX i_timestamp ON archive(timestamp); +CREATE INDEX i_peer ON archive(peer); +CREATE INDEX i_bare_peer ON archive(bare_peer); + +CREATE TABLE archive_prefs ( + username text NOT NULL PRIMARY KEY, + def text NOT NULL, + always text NOT NULL, + never text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +); diff --git a/sql/mysql.sql b/sql/mysql.sql index 9596afa7c..fab76b9a2 100644 --- a/sql/mysql.sql +++ b/sql/mysql.sql @@ -85,6 +85,31 @@ CREATE TABLE spool ( CREATE INDEX i_despool USING BTREE ON spool(username); CREATE INDEX i_spool_created_at USING BTREE ON spool(created_at); +CREATE TABLE archive ( + username varchar(250) NOT NULL, + timestamp BIGINT UNSIGNED NOT NULL, + peer varchar(250) NOT NULL, + bare_peer varchar(250) NOT NULL, + xml text NOT NULL, + txt text, + id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=InnoDB CHARACTER SET utf8; + +CREATE FULLTEXT INDEX i_text ON archive(txt); +CREATE INDEX i_username USING BTREE ON archive(username); +CREATE INDEX i_timestamp USING BTREE ON archive(timestamp); +CREATE INDEX i_peer USING BTREE ON archive(peer); +CREATE INDEX i_bare_peer USING BTREE ON archive(bare_peer); + +CREATE TABLE archive_prefs ( + username varchar(250) NOT NULL PRIMARY KEY, + def text NOT NULL, + always text NOT NULL, + never text NOT NULL, + created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +) ENGINE=InnoDB CHARACTER SET utf8; + CREATE TABLE vcard ( username varchar(250) PRIMARY KEY, vcard mediumtext NOT NULL, diff --git a/sql/pg.sql b/sql/pg.sql index 736c4f932..2a052d337 100644 --- a/sql/pg.sql +++ b/sql/pg.sql @@ -85,6 +85,29 @@ CREATE TABLE spool ( CREATE INDEX i_despool ON spool USING btree (username); +CREATE TABLE archive ( + username text NOT NULL, + timestamp BIGINT NOT NULL, + peer text NOT NULL, + bare_peer text NOT NULL, + xml text NOT NULL, + txt text, + id SERIAL, + created_at TIMESTAMP NOT NULL DEFAULT now() +); + +CREATE INDEX i_username ON archive USING btree (username); +CREATE INDEX i_timestamp ON archive USING btree (timestamp); +CREATE INDEX i_peer ON archive USING btree (peer); +CREATE INDEX i_bare_peer ON archive USING btree (bare_peer); + +CREATE TABLE archive_prefs ( + username text NOT NULL PRIMARY KEY, + def text NOT NULL, + always text NOT NULL, + never text NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT now() +); CREATE TABLE vcard ( username text PRIMARY KEY, @@ -299,6 +322,6 @@ CREATE TABLE sm ( info text NOT NULL ); -CREATE UNIQUE INDEX i_sid ON sm USING btree (usec, pid); -CREATE INDEX i_node ON sm USING btree (node); -CREATE INDEX i_username ON sm USING btree (username); +CREATE UNIQUE INDEX i_sm_sid ON sm USING btree (usec, pid); +CREATE INDEX i_sm_node ON sm USING btree (node); +CREATE INDEX i_sm_username ON sm USING btree (username); |