messenger_oracle.sql 5.98 KB
Newer Older
Matt Tucker's avatar
Matt Tucker committed
1 2 3 4 5
REM // $RCSfile$
REM // $Revision$
REM // $Date$

CREATE TABLE jiveUser (
Matt Tucker's avatar
Matt Tucker committed
6
  username              VARCHAR2(32)     NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
7 8 9 10 11
  password              VARCHAR2(32)    NOT NULL,
  name                  VARCHAR2(100),
  email                 VARCHAR2(100),
  creationDate          CHAR(15)        NOT NULL,
  modificationDate      CHAR(15)        NOT NULL,
12
  CONSTRAINT jiveUser_pk PRIMARY KEY (username)
Matt Tucker's avatar
Matt Tucker committed
13 14 15 16 17
);
CREATE INDEX jiveUser_cDate_idx ON jiveUser (creationDate ASC);


CREATE TABLE jiveUserProp (
Matt Tucker's avatar
Matt Tucker committed
18
  username              VARCHAR2(32)    NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
19
  name                  VARCHAR2(100)   NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
20 21
  propValue             VARCHAR2(1024)  NOT NULL,
  CONSTRAINT jiveUserProp_pk PRIMARY KEY (username, name)
Matt Tucker's avatar
Matt Tucker committed
22 23 24 25
);


CREATE TABLE jivePrivate (
Matt Tucker's avatar
Matt Tucker committed
26
  username              VARCHAR2(32)    NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
27 28 29
  name                  VARCHAR2(100)   NOT NULL,
  namespace             VARCHAR2(200)   NOT NULL,
  value                 LONG            NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
30
  CONSTRAINT jivePrivate_pk PRIMARY KEY (username, name, namespace)
Matt Tucker's avatar
Matt Tucker committed
31 32 33 34
);


CREATE TABLE jiveOffline (
Matt Tucker's avatar
Matt Tucker committed
35
  username              VARCHAR2(32)    NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
36 37 38 39
  messageID             INTEGER         NOT NULL,
  creationDate          CHAR(15)        NOT NULL,
  messageSize           INTEGER         NOT NULL,
  message               LONG            NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
40
  CONSTRAINT jiveOffline_pk PRIMARY KEY (username, messageID)
Matt Tucker's avatar
Matt Tucker committed
41 42 43 44 45
);


CREATE TABLE jiveRoster (
  rosterID              INTEGER         NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
46 47
  username              VARCHAR2(32)    NOT NULL,
  jid                   VARCHAR2(1024)  NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
48 49 50 51 52 53
  sub                   INTEGER         NOT NULL,
  ask                   INTEGER         NOT NULL,
  recv                  INTEGER         NOT NULL,
  nick                  VARCHAR2(255),
  CONSTRAINT jiveRoster_pk PRIMARY KEY (rosterID)
);
Matt Tucker's avatar
Matt Tucker committed
54
CREATE INDEX jiveRoster_username_idx ON jiveRoster (username ASC);
Matt Tucker's avatar
Matt Tucker committed
55 56 57 58 59 60 61 62 63 64 65 66 67


CREATE TABLE jiveRosterGroups (
  rosterID              INTEGER         NOT NULL,
  rank                  INTEGER         NOT NULL,
  groupName             VARCHAR2(255)   NOT NULL,
  CONSTRAINT jiveRosterGroups_pk PRIMARY KEY (rosterID, rank)
);
CREATE INDEX jiveRosterGroup_rosterid_idx ON jiveRosterGroups (rosterID ASC);
ALTER TABLE jiveRosterGroups ADD CONSTRAINT jiveRosterGroups_rosterID_fk FOREIGN KEY (rosterID) REFERENCES jiveRoster INITIALLY DEFERRED DEFERRABLE;


CREATE TABLE jiveVCard (
Matt Tucker's avatar
Matt Tucker committed
68
  username              VARCHAR2(32)    NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
69 70
  name                  VARCHAR2(100)   NOT NULL,
  propValue             VARCHAR2(4000)  NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
71
  CONSTRAINT JiveVCard_pk PRIMARY KEY (username, name)
Matt Tucker's avatar
Matt Tucker committed
72 73 74
);

CREATE TABLE jiveGroup (
75
  groupName             VARCHAR2(50)    NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
76
  description           VARCHAR2(255),
Matt Tucker's avatar
Matt Tucker committed
77
  CONSTRAINT jiveGroup_pk PRIMARY KEY (groupName)
Matt Tucker's avatar
Matt Tucker committed
78 79 80
);

CREATE TABLE jiveGroupProp (
Matt Tucker's avatar
Matt Tucker committed
81
  groupName             VARCHAR(50)     NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
82 83
  name                  VARCHAR2(100)   NOT NULL,
  propValue             VARCHAR2(4000)  NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
84
  CONSTRAINT jiveGroupProp_pk PRIMARY KEY (groupName, name)
Matt Tucker's avatar
Matt Tucker committed
85 86 87
);

CREATE TABLE jiveGroupUser (
Matt Tucker's avatar
Matt Tucker committed
88
  groupName             VARCHAR(50)     NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
89
  username              VARCHAR2(32)    NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
90
  administrator         INTEGER         NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
91
  CONSTRAINT jiveGroupUser PRIMARY KEY (groupName, username, administrator)
Matt Tucker's avatar
Matt Tucker committed
92 93 94 95 96 97 98 99
);

CREATE TABLE jiveID (
  idType                INTEGER         NOT NULL,
  id                    INTEGER         NOT NULL,
  CONSTRAINT jiveID_pk PRIMARY KEY (idType)
);

100 101 102 103 104 105
CREATE TABLE jiveProperty (
  name        VARCHAR2(100) NOT NULL,
  propValue   VARCHAR2(4000) NOT NULL,
  CONSTRAINT jiveProperty_pk PRIMARY KEY (name)
);

Matt Tucker's avatar
Matt Tucker committed
106 107 108 109 110
CREATE TABLE jiveVersion (
  majorVersion  INTEGER  NOT NULL,
  minorVersion  INTEGER  NOT NULL
);

Matt Tucker's avatar
Matt Tucker committed
111 112 113 114
REM // MUC Tables

CREATE TABLE mucRoom(
  roomID              INT           NOT NULL,
115 116
  creationDate        CHAR(15)      NOT NULL,
  modificationDate    CHAR(15)      NOT NULL,
117
  name                VARCHAR2(50)  NOT NULL,
118
  naturalName         VARCHAR2(255) NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
119
  description         VARCHAR2(255),
120
  lockedDate          CHAR(15)      NOT NULL,
121
  emptyDate           CHAR(15)      NULL,
Matt Tucker's avatar
Matt Tucker committed
122 123 124 125
  canChangeSubject    INTEGER       NOT NULL,
  maxUsers            INTEGER       NOT NULL,
  publicRoom          INTEGER       NOT NULL,
  moderated           INTEGER       NOT NULL,
126
  membersOnly         INTEGER       NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
127 128 129 130 131 132 133 134 135 136 137 138
  canInvite           INTEGER       NOT NULL,
  password            VARCHAR2(50)  NULL,
  canDiscoverJID      INTEGER       NOT NULL,
  logEnabled          INTEGER       NOT NULL,
  subject             VARCHAR2(100) NULL,
  rolesToBroadcast    INTEGER       NOT NULL,
  CONSTRAINT mucRoom_pk PRIMARY KEY (name)
);
CREATE INDEX mucRoom_roomid_idx ON mucRoom (roomID);

CREATE TABLE mucAffiliation (
  roomID              INT            NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
139
  jid                 VARCHAR2(1024) NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
140 141 142 143 144 145
  affiliation         INTEGER        NOT NULL,
  CONSTRAINT mucAffiliation_pk PRIMARY KEY (roomID, jid)
);

CREATE TABLE mucMember (
  roomID              INT            NOT NULL,
146
  jid                 VARCHAR2(1024) NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
147
  nickname            VARCHAR2(255)  NULL,
148 149 150 151 152
  firstName           VARCHAR2(100)  NULL,
  lastName            VARCHAR2(100)  NULL,
  url                 VARCHAR2(100)  NULL,
  email               VARCHAR2(100)  NULL,
  faqentry            VARCHAR2(100)  NULL,
Matt Tucker's avatar
Matt Tucker committed
153 154 155 156 157
  CONSTRAINT mucMember_pk PRIMARY KEY (roomID, jid)
);

CREATE TABLE mucConversationLog (
  roomID              INT            NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
158
  sender              VARCHAR2(1024) NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
159 160 161 162 163
  nickname            VARCHAR2(255)  NULL,
  time                CHAR(15)       NOT NULL,
  subject             VARCHAR2(255)  NULL,
  body                VARCHAR2(4000) NULL
);
164
CREATE INDEX mucLog_time_idx ON mucConversationLog (time);
Matt Tucker's avatar
Matt Tucker committed
165 166 167 168 169 170 171

REM // Finally, insert default table values.

INSERT INTO jiveID (idType, id) VALUES (18, 1);
INSERT INTO jiveID (idType, id) VALUES (19, 1);
INSERT INTO jiveID (idType, id) VALUES (23, 1);

Matt Tucker's avatar
Matt Tucker committed
172 173
INSERT INTO jiveVersion (majorVersion, minorVersion) VALUES (2, 1);

Matt Tucker's avatar
Matt Tucker committed
174 175 176
REM // Entry for admin user
INSERT INTO jiveUser (username, password, name, email, creationDate, modificationDate)
    VALUES ('admin', 'admin', 'Administrator', 'admin@example.com', '0', '0');