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

CREATE TABLE jiveUser (
Matt Tucker's avatar
Matt Tucker committed
6
  username              NVARCHAR(32)    NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
7 8 9 10 11
  password              NVARCHAR(32)    NOT NULL,
  name                  NVARCHAR(100),
  email                 VARCHAR(100),
  creationDate          CHAR(15)        NOT NULL,
  modificationDate      CHAR(15)        NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
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              NVARCHAR(32)    NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
19
  name                  NVARCHAR(100)   NOT NULL,
20
  propValue             NVARCHAR(2000)  NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
21
  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              NVARCHAR(32)    NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
27 28 29
  name                  NVARCHAR(100)   NOT NULL,
  namespace             NVARCHAR(200)   NOT NULL,
  value                 NTEXT           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              NVARCHAR(32)    NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
36 37 38
  messageID             INTEGER         NOT NULL,
  creationDate          CHAR(15)        NOT NULL,
  messageSize           INTEGER         NOT NULL,
39
  message               NTEXT           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              NVARCHAR(32)    NOT NULL,
  jid                   NVARCHAR(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                  NVARCHAR(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             NVARCHAR(255)   NOT NULL,
  CONSTRAINT jiveRosterGroups_pk PRIMARY KEY (rosterID, rank)
);
CREATE INDEX jiveRosterGroups_rosterid_idx ON jiveRosterGroups (rosterID ASC);
ALTER TABLE jiveRosterGroups ADD CONSTRAINT jiveRosterGroups_rosterID_fk FOREIGN KEY (rosterID) REFERENCES jiveRoster;


CREATE TABLE jiveVCard (
Matt Tucker's avatar
Matt Tucker committed
68
  username              NVARCHAR(32)    NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
69
  name                  NVARCHAR(100)   NOT NULL,
70
  propValue             NVARCHAR(2000)  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 75
);


CREATE TABLE jiveGroup (
76
  groupName             NVARCHAR(50)   NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
77
  description           NVARCHAR(255),
78
  CONSTRAINT group_pk PRIMARY KEY (groupName)
Matt Tucker's avatar
Matt Tucker committed
79 80 81 82
);


CREATE TABLE jiveGroupProp (
83
   groupName            NVARCHAR(50)   NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
84
   name                 NVARCHAR(100)   NOT NULL,
85
   propValue            NVARCHAR(2000)  NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
86
   CONSTRAINT jiveGroupProp_pk PRIMARY KEY (groupName, name)
Matt Tucker's avatar
Matt Tucker committed
87 88 89 90
);


CREATE TABLE jiveGroupUser (
91
  groupName             NVARCHAR(50)   NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
92
  username              NVARCHAR(32)    NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
93
  administrator         INTEGER         NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
94
  CONSTRAINT jiveGroupUser_pk PRIMARY KEY (groupName, username, administrator)
Matt Tucker's avatar
Matt Tucker committed
95 96
);

97

Matt Tucker's avatar
Matt Tucker committed
98 99 100 101 102 103
CREATE TABLE jiveID (
  idType                INTEGER         NOT NULL,
  id                    INTEGER         NOT NULL,
  CONSTRAINT jiveID_pk PRIMARY KEY (idType)
);

Matt Tucker's avatar
Matt Tucker committed
104

105 106 107 108 109 110
CREATE TABLE jiveProperty (
  name         NVARCHAR(100) NOT NULL,
  propValue   NTEXT NOT NULL,
  CONSTRAINT jiveProperty_pk PRIMARY KEY (name)
);

Matt Tucker's avatar
Matt Tucker committed
111 112 113 114 115 116 117

CREATE TABLE jiveVersion (
  majorVersion  INTEGER  NOT NULL,
  minorVersion  INTEGER  NOT NULL
);


Matt Tucker's avatar
Matt Tucker committed
118 119 120 121
/* MUC Tables */

CREATE TABLE mucRoom (
  roomID              INT           NOT NULL,
122 123
  creationDate        CHAR(15)      NOT NULL,
  modificationDate    CHAR(15)      NOT NULL,
124
  name                NVARCHAR(50)  NOT NULL,
125
  naturalName         NVARCHAR(255) NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
126
  description         NVARCHAR(255),
127
  lockedDate          CHAR(15)      NOT NULL,
128
  emptyDate           CHAR(15)      NULL,
Matt Tucker's avatar
Matt Tucker committed
129 130 131 132
  canChangeSubject    INT           NOT NULL,
  maxUsers            INT           NOT NULL,
  publicRoom          INT           NOT NULL,
  moderated           INT           NOT NULL,
133
  membersOnly         INT           NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
134 135 136 137 138 139 140 141 142 143 144 145 146
  canInvite           INT           NOT NULL,
  password            NVARCHAR(50)  NULL,
  canDiscoverJID      INT           NOT NULL,
  logEnabled          INT           NOT NULL,
  subject             NVARCHAR(100) NULL,
  rolesToBroadcast    INT           NOT NULL,
  CONSTRAINT mucRoom__pk PRIMARY KEY (name)
);

CREATE INDEX mucRoom_roomID_idx on mucRoom(roomID);

CREATE TABLE mucAffiliation (
  roomID              INT            NOT NULL,
147
  jid                 NVARCHAR(424) NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
148 149 150 151 152 153
  affiliation         INT            NOT NULL,
  CONSTRAINT mucAffiliation__pk PRIMARY KEY (roomID,jid)
);

CREATE TABLE mucMember (
  roomID              INT            NOT NULL,
154
  jid                 NVARCHAR(424) NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
155
  nickname            NVARCHAR(255)  NULL,
156 157 158 159 160
  firstName           NVARCHAR(100)  NULL,
  lastName            NVARCHAR(100)  NULL,
  url                 NVARCHAR(100)  NULL,
  email               NVARCHAR(100)  NULL,
  faqentry            NVARCHAR(100)  NULL,
Matt Tucker's avatar
Matt Tucker committed
161 162 163 164 165
  CONSTRAINT mucMember__pk PRIMARY KEY (roomID,jid)
);

CREATE TABLE mucConversationLog (
  roomID              INT            NOT NULL,
166
  sender              NVARCHAR(1024) NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
167 168 169
  nickname            NVARCHAR(255)  NULL,
  time                CHAR(15)       NOT NULL,
  subject             NVARCHAR(255)  NULL,
170
  body                NTEXT          NULL
Matt Tucker's avatar
Matt Tucker committed
171
);
172
CREATE INDEX mucLog_time_idx ON mucConversationLog (time);
Matt Tucker's avatar
Matt Tucker committed
173 174 175 176 177 178 179

/* 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
180 181
INSERT INTO jiveVersion (majorVersion, minorVersion) VALUES (2, 1);

Matt Tucker's avatar
Matt Tucker committed
182 183 184
/* Entry for admin user */
INSERT INTO jiveUser (username, password, name, email, creationDate, modificationDate)
    VALUES ('admin', 'admin', 'Administrator', 'admin@example.com', '0', '0');