/************************************************
* This is the schema for Oracle.
*
* The following code is used to remove the tables:
*
* DROP TABLE "authreg" CASCADE CONSTRAINTS;
* DROP TABLE "active" CASCADE CONSTRAINTS;
* DROP TABLE "logout" CASCADE CONSTRAINTS;
* DROP TABLE "roster-items" CASCADE CONSTRAINTS;
* DROP TABLE "roster-groups" CASCADE CONSTRAINTS;
* DROP TABLE "vcard" CASCADE CONSTRAINTS;
* DROP TABLE "queue" CASCADE CONSTRAINTS;
* DROP TABLE "private" CASCADE CONSTRAINTS;
* DROP TABLE "motd-message" CASCADE CONSTRAINTS;
* DROP TABLE "motd-times" CASCADE CONSTRAINTS;
* DROP TABLE "disco-items" CASCADE CONSTRAINTS;
* DROP TABLE "privacy-default" CASCADE CONSTRAINTS;
* DROP TABLE "privacy-items" CASCADE CONSTRAINTS;
* DROP TABLE "vacation-settings" CASCADE CONSTRAINTS;
* DROP SEQUENCE "seq-active";
* DROP SEQUENCE "seq-logout";
* DROP SEQUENCE "seq-roster-items";
* DROP SEQUENCE "seq-roster-groups";
* DROP SEQUENCE "seq-vcard";
* DROP SEQUENCE "seq-queue";
* DROP SEQUENCE "seq-private";
* DROP SEQUENCE "seq-motd-message";
* DROP SEQUENCE "seq-motd-times";
* DROP SEQUENCE "seq-disco-items";
* DROP SEQUENCE "seq-privacy-default";
* DROP SEQUENCE "seq-privacy-items";
* DROP SEQUENCE "seq-vacation-settings";
*/
CREATE TABLE "authreg" (
"username" varchar2(256),
"realm" varchar2(256),
"password" varchar2(256),
"token" varchar2(10),
"sequence" number,
"hash" varchar2(40) );
CREATE SEQUENCE "seq-active" INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE "seq-logout" INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE "seq-roster-items" INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE "seq-roster-groups" INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE "seq-vcard" INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE "seq-queue" INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE "seq-private" INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE "seq-motd-message" INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE "seq-motd-times" INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE "seq-disco-items" INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE "seq-privacy-default" INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE "seq-privacy-items" INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
CREATE SEQUENCE "seq-vacation-settings" INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER;
/*
* Session manager tables
*
*
* Active (seen) users
* Used by: core
*/
CREATE TABLE "active" (
"collection-owner" varchar2(4000),
"object-sequence" number,
"time" number );
CREATE OR REPLACE TRIGGER "active-object-sequence"
BEFORE INSERT
ON "active"
FOR EACH ROW
BEGIN
IF :NEW."object-sequence" IS NULL THEN
SELECT "seq-active".NextVal INTO :NEW."object-sequence" FROM dual;
END IF;
END;
/
SHOW ERRORS;
ALTER TABLE "active" ADD (
PRIMARY KEY ("collection-owner"));
/*
* Logout times
* Used by: mod_iq_last
*/
CREATE TABLE "logout" (
"collection-owner" varchar2(4000),
"object-sequence" number,
"time" number );
CREATE OR REPLACE TRIGGER "logout-object-sequence"
BEFORE INSERT
ON "logout"
FOR EACH ROW
BEGIN
IF :NEW."object-sequence" IS NULL THEN
SELECT "seq-logout".NextVal INTO :NEW."object-sequence" FROM dual;
END IF;
END;
/
SHOW ERRORS;
ALTER TABLE "logout" ADD (
PRIMARY KEY ("collection-owner"));
/*
* Roster items
* Used by: mod_roster
*/
CREATE TABLE "roster-items" (
"collection-owner" varchar2(4000),
"object-sequence" number,
"jid" varchar2(4000),
"name" varchar2(4000),
"to" char(1),
"from" char(1),
"ask" number );
CREATE OR REPLACE TRIGGER "roster-items-object-sequence"
BEFORE INSERT
ON "roster-items"
FOR EACH ROW
BEGIN
IF :NEW."object-sequence" IS NULL THEN
SELECT "seq-roster-items".NextVal INTO :NEW."object-sequence" FROM dual;
END IF;
END;
/
SHOW ERRORS;
CREATE INDEX "roster-items-collection-owner"
ON "roster-items"("collection-owner");
CREATE INDEX "roster-items-jid"
ON "roster-items"("jid");
/*
* Roster groups
* Used by: mod_roster
*/
CREATE TABLE "roster-groups" (
"collection-owner" varchar2(4000),
"object-sequence" number,
"jid" varchar2(4000),
"group" varchar2(4000) );
CREATE OR REPLACE TRIGGER "roster-groups-object-sequence"
BEFORE INSERT
ON "roster-groups"
FOR EACH ROW
BEGIN
IF :NEW."object-sequence" IS NULL THEN
SELECT "seq-roster-groups".NextVal INTO :NEW."object-sequence" FROM dual;
END IF;
END;
/
SHOW ERRORS;
CREATE INDEX "roster-groups-collection-owner"
ON "roster-groups"("collection-owner");
CREATE INDEX "roster-groups-jid"
ON "roster-groups"("jid");
/*
* vCard (user profile information)
* Used by: mod_iq_vcard
*/
CREATE TABLE "vcard" (
"collection-owner" varchar2(4000),
"object-sequence" number,
"fn" varchar2(4000),
"nickname" varchar2(4000),
"url" varchar2(4000),
"tel" varchar2(4000),
"email" varchar2(4000),
"title" varchar2(4000),
"role" varchar2(4000),
"bday" varchar2(4000),
"desc" varchar2(4000),
"n-given" varchar2(4000),
"n-family" varchar2(4000),
"adr-street" varchar2(4000),
"adr-extadd" varchar2(4000),
"adr-locality" varchar2(4000),
"adr-region" varchar2(4000),
"adr-pcode" varchar2(4000),
"adr-country" varchar2(4000),
"org-orgname" varchar2(4000),
"org-orgunit" varchar2(4000) );
CREATE OR REPLACE TRIGGER "vcard-object-sequence"
BEFORE INSERT
ON "vcard"
FOR EACH ROW
BEGIN
IF :NEW."object-sequence" IS NULL THEN
SELECT "seq-vcard".NextVal INTO :NEW."object-sequence" FROM dual;
END IF;
END;
/
SHOW ERRORS;
CREATE INDEX "vcard-collection-owner"
ON "vcard"("collection-owner");
/*
* Offline message queue
* Used by: mod_offline
*/
CREATE TABLE "queue" (
"collection-owner" varchar2(4000),
"object-sequence" number,
"xml" varchar2(4000) );
CREATE OR REPLACE TRIGGER "queue-object-sequence"
BEFORE INSERT
ON "queue"
FOR EACH ROW
BEGIN
IF :NEW."object-sequence" IS NULL THEN
SELECT "seq-queue".NextVal INTO :NEW."object-sequence" FROM dual;
END IF;
END;
/
SHOW ERRORS;
CREATE INDEX "queue-collection-owner"
ON "queue"("collection-owner");
/*
* Private XML storage
* Used by: mod_iq_private
*/
CREATE TABLE "private" (
"collection-owner" varchar2(4000),
"object-sequence" number,
"ns" varchar2(4000),
"xml" varchar2(4000) );
CREATE OR REPLACE TRIGGER "private-object-sequence"
BEFORE INSERT
ON "private"
FOR EACH ROW
BEGIN
IF :NEW."object-sequence" IS NULL THEN
SELECT "seq-private".NextVal INTO :NEW."object-sequence" FROM dual;
END IF;
END;
/
SHOW ERRORS;
CREATE INDEX "private-collection-owner"
ON "private"("collection-owner");
/*
* Message Of The Day (MOTD) messages (announcements)
* Used by: mod_announce
*/
CREATE TABLE "motd-message" (
"collection-owner" varchar2(4000),
"object-sequence" number,
"xml" varchar2(4000) );
CREATE OR REPLACE TRIGGER "motd-message-object-sequence"
BEFORE INSERT
ON "motd-message"
FOR EACH ROW
BEGIN
IF :NEW."object-sequence" IS NULL THEN
SELECT "seq-motd-message".NextVal INTO :NEW."object-sequence" FROM dual;
END IF;
END;
/
SHOW ERRORS;
ALTER TABLE "motd-message" ADD (
PRIMARY KEY ("collection-owner"));
/*
* Times of last MOTD message for each user
* Used by: mod_announce
*/
CREATE TABLE "motd-times" (
"collection-owner" varchar2(4000),
"object-sequence" number,
"time" number );
CREATE OR REPLACE TRIGGER "motd-times-object-sequence"
BEFORE INSERT
ON "motd-times"
FOR EACH ROW
BEGIN
IF :NEW."object-sequence" IS NULL THEN
SELECT "seq-motd-times".NextVal INTO :NEW."object-sequence" FROM dual;
END IF;
END;
/
SHOW ERRORS;
ALTER TABLE "motd-times" ADD (
PRIMARY KEY ("collection-owner"));
/*
* User-published discovery items
* Used by: mod_disco_publish
*/
CREATE TABLE "disco-items" (
"collection-owner" varchar2(4000),
"object-sequence" number,
"jid" varchar2(4000),
"name" varchar2(4000),
"node" varchar2(4000) );
CREATE OR REPLACE TRIGGER "disco-items-object-sequence"
BEFORE INSERT
ON "disco-items"
FOR EACH ROW
BEGIN
IF :NEW."object-sequence" IS NULL THEN
SELECT "seq-disco-items".NextVal INTO :NEW."object-sequence" FROM dual;
END IF;
END;
/
SHOW ERRORS;
CREATE INDEX "disco-items-collection-owner"
ON "disco-items"("collection-owner");
/*
* Default privacy list
* Used by: mod_privacy
*/
CREATE TABLE "privacy-default" (
"collection-owner" varchar2(4000),
"object-sequence" number,
"default" varchar2(4000) );
CREATE OR REPLACE TRIGGER "privacy-default-object-seq"
BEFORE INSERT
ON "privacy-default"
FOR EACH ROW
BEGIN
IF :NEW."object-sequence" IS NULL THEN
SELECT "seq-privacy-default".NextVal INTO :NEW."object-sequence" FROM dual;
END IF;
END;
/
SHOW ERRORS;
ALTER TABLE "privacy-default" ADD (
PRIMARY KEY ("collection-owner"));
/*
* Privacy lists
* Used by: mod_privacy
*/
CREATE TABLE "privacy-items" (
"collection-owner" varchar2(4000),
"object-sequence" number,
"list" varchar2(4000),
"type" varchar2(4000),
"value" varchar2(4000),
"deny" char(1),
"order" number,
"block" number );
CREATE OR REPLACE TRIGGER "privacy-items-object-sequence"
BEFORE INSERT
ON "privacy-items"
FOR EACH ROW
BEGIN
IF :NEW."object-sequence" IS NULL THEN
SELECT "seq-privacy-items".NextVal INTO :NEW."object-sequence" FROM dual;
END IF;
END;
/
SHOW ERRORS;
CREATE INDEX "privacy-items-collection-owner"
ON "privacy-items"("collection-owner");
/*
* Vacation settings
* Used by: mod_vacation
*/
CREATE TABLE "vacation-settings" (
"collection-owner" varchar2(4000),
"object-sequence" number,
"start" number,
"end" number,
"message" varchar2(4000) );
CREATE OR REPLACE TRIGGER "vacation-settings-object-seq"
BEFORE INSERT
ON "vacation-settings"
FOR EACH ROW
BEGIN
IF :NEW."object-sequence" IS NULL THEN
SELECT "seq-vacation-settings".NextVal INTO :NEW."object-sequence" FROM dual;
END IF;
END;
/
SHOW ERRORS;
ALTER TABLE "vacation-settings" ADD (
PRIMARY KEY ("collection-owner"));