Add MAM (XEP-0313) support

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);