initial merge of pubsub odbc, compilation pass ok

SVN Revision: 2437

2 files changed, 106 insertions, 0 deletions

diff --git a/src/odbc/mysql.sql b/src/odbc/mysql.sql
index dfbf69437..be182bf5f 100644
--- a/src/odbc/mysql.sql
+++ b/src/odbc/mysql.sql
@@ -158,3 +158,58 @@ CREATE TABLE roster_version (
 -- ALTER TABLE rosterusers ADD COLUMN askmessage text AFTER ask;
 -- UPDATE rosterusers SET askmessage = '';
 -- ALTER TABLE rosterusers ALTER COLUMN askmessage SET NOT NULL;
+
+CREATE TABLE pubsub_node (
+  host text,
+  node text,
+  parent text,
+  type text,
+  nodeid bigint auto_increment primary key
+) CHARACTER SET utf8;
+CREATE INDEX i_pubsub_node_parent ON pubsub_node(parent(120));
+CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node(host(20), node(120));
+
+CREATE TABLE pubsub_node_option (
+  nodeid bigint,
+  name text,
+  val text
+) CHARACTER SET utf8;
+CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option(nodeid);
+ALTER TABLE `pubsub_node_option` ADD FOREIGN KEY (`nodeid`) REFERENCES `ejabberd`.`pubsub_node` (`nodeid`) ON DELETE CASCADE;
+
+CREATE TABLE pubsub_node_owner (
+  nodeid bigint,
+  owner text
+) CHARACTER SET utf8;
+CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner(nodeid);
+ALTER TABLE `pubsub_node_owner` ADD FOREIGN KEY (`nodeid`) REFERENCES `ejabberd`.`pubsub_node` (`nodeid`) ON DELETE CASCADE;
+
+CREATE TABLE pubsub_state (
+  nodeid bigint,
+  jid text,
+  affiliation character(1),
+  subscription character(1),
+  stateid bigint auto_increment primary key
+) CHARACTER SET utf8;
+CREATE INDEX i_pubsub_state_jid ON pubsub_state(jid(60));
+CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state(nodeid, jid(60));
+ALTER TABLE `pubsub_state` ADD FOREIGN KEY (`nodeid`) REFERENCES `ejabberd`.`pubsub_node` (`nodeid`) ON DELETE CASCADE;
+
+CREATE TABLE pubsub_item (
+  nodeid bigint,
+  itemid text, 
+  publisher text,
+  creation text,
+  modification text,
+  payload text
+) CHARACTER SET utf8;
+CREATE INDEX i_pubsub_item_itemid ON pubsub_item(itemid(36));
+CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item(nodeid, itemid(36));
+ALTER TABLE `pubsub_item` ADD FOREIGN KEY (`nodeid`) REFERENCES `ejabberd`.`pubsub_node` (`nodeid`) ON DELETE CASCADE;
+
+CREATE TABLE pubsub_subscription_opt (
+  subid text,
+  opt_name varchar(32),
+  opt_value text
+);
+CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt(subid(32), opt_name(32));
diff --git a/src/odbc/pg.sql b/src/odbc/pg.sql
index 2273ad954..d02a9154e 100644
--- a/src/odbc/pg.sql
+++ b/src/odbc/pg.sql
@@ -162,3 +162,54 @@ CREATE TABLE roster_version (
 -- ALTER TABLE rosterusers ADD COLUMN askmessage text;
 -- UPDATE rosterusers SET askmessage = '';
 -- ALTER TABLE rosterusers ALTER COLUMN askmessage SET NOT NULL;
+
+CREATE TABLE pubsub_node (
+  host text,
+  node text,
+  parent text,
+  "type" text,
+  nodeid SERIAL UNIQUE
+);
+CREATE INDEX i_pubsub_node_parent ON pubsub_node USING btree (parent);
+CREATE UNIQUE INDEX i_pubsub_node_tuple ON pubsub_node USING btree (host, node);
+
+CREATE TABLE pubsub_node_option (
+  nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
+  name text,
+  val text
+);
+CREATE INDEX i_pubsub_node_option_nodeid ON pubsub_node_option USING btree (nodeid);
+
+CREATE TABLE pubsub_node_owner (
+  nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
+  owner text
+);
+CREATE INDEX i_pubsub_node_owner_nodeid ON pubsub_node_owner USING btree (nodeid);
+
+CREATE TABLE pubsub_state (
+  nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
+  jid text,
+  affiliation character(1),
+  subscription character(1),
+  stateid SERIAL UNIQUE
+);
+CREATE INDEX i_pubsub_state_jid ON pubsub_state USING btree (jid);
+CREATE UNIQUE INDEX i_pubsub_state_tuple ON pubsub_state USING btree (nodeid, jid);
+
+CREATE TABLE pubsub_item (
+  nodeid bigint REFERENCES pubsub_node(nodeid) ON DELETE CASCADE,
+  itemid text, 
+  publisher text,
+  creation text,
+  modification text,
+  payload text
+);
+CREATE INDEX i_pubsub_item_itemid ON pubsub_item USING btree (itemid);
+CREATE UNIQUE INDEX i_pubsub_item_tuple ON pubsub_item USING btree (nodeid, itemid);
+
+CREATE TABLE pubsub_subscription_opt (
+  subid text,
+  opt_name varchar(32),
+  opt_value text
+);
+CREATE UNIQUE INDEX i_pubsub_subscription_opt ON pubsub_subscription_opt USING btree (subid, opt_name);