/************************************************
 * 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"));