messenger_mysql.sql 5.32 KB
Newer Older
Matt Tucker's avatar
Matt Tucker committed
1 2 3 4 5
# $RCSfile$
# $Revision$
# $Date$

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

CREATE TABLE jiveUserProp (
17
  username              VARCHAR(32)     NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
18 19
  name                  VARCHAR(100)    NOT NULL,
  propValue             TEXT            NOT NULL,
20
  PRIMARY KEY (username, name)
Matt Tucker's avatar
Matt Tucker committed
21 22
);

23
CREATE TABLE jiveGroup (
24
  groupName              VARCHAR(50)     NOT NULL,
25
  description           VARCHAR(255),
26
  PRIMARY KEY (groupName)
27
);
Matt Tucker's avatar
Matt Tucker committed
28

29
CREATE TABLE jiveGroupProp (
Matt Tucker's avatar
Matt Tucker committed
30
  groupName             VARCHAR(50)     NOT NULL,
31 32
  name                  VARCHAR(100)    NOT NULL,
  propValue             TEXT            NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
33
  PRIMARY KEY (groupName, name)
34 35 36
);

CREATE TABLE jiveGroupUser (
Matt Tucker's avatar
Matt Tucker committed
37
  groupName             VARCHAR(50)     NOT NULL,
38
  username              VARCHAR(32)     NOT NULL,
39
  administrator         TINYINT         NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
40
  PRIMARY KEY (groupName, username, administrator)
41 42 43 44
);

CREATE TABLE jivePrivate (
  username              VARCHAR(32)     NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
45 46 47
  name                  VARCHAR(100)    NOT NULL,
  namespace             VARCHAR(200)    NOT NULL,
  value                 TEXT            NOT NULL,
48
  PRIMARY KEY (username, name, namespace)
Matt Tucker's avatar
Matt Tucker committed
49 50 51
);

CREATE TABLE jiveOffline (
52
  username              VARCHAR(32)     NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
53 54 55 56
  messageID             BIGINT          NOT NULL,
  creationDate          CHAR(15)        NOT NULL,
  messageSize           INTEGER         NOT NULL,
  message               TEXT            NOT NULL,
57
  PRIMARY KEY (username, messageID)
Matt Tucker's avatar
Matt Tucker committed
58 59 60 61
);

CREATE TABLE jiveRoster (
  rosterID              BIGINT          NOT NULL,
62
  username              VARCHAR(32)     NOT NULL,
63
  jid                   TEXT            NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
64 65 66 67 68
  sub                   TINYINT         NOT NULL,
  ask                   TINYINT         NOT NULL,
  recv                  TINYINT         NOT NULL,
  nick                  VARCHAR(255),
  PRIMARY KEY (rosterID),
69
  INDEX jiveRoster_unameid_idx (username)
Matt Tucker's avatar
Matt Tucker committed
70 71 72 73 74 75 76 77 78 79 80
);

CREATE TABLE jiveRosterGroups (
  rosterID              BIGINT          NOT NULL,
  rank                  TINYINT         NOT NULL,
  groupName             VARCHAR(255)    NOT NULL,
  PRIMARY KEY (rosterID, rank),
  INDEX jiveRosterGroup_rosterid_idx (rosterID)
);

CREATE TABLE jiveVCard (
81
  username              VARCHAR(32)     NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
82 83
  name                  VARCHAR(100)    NOT NULL,
  propValue             TEXT            NOT NULL,
84
  PRIMARY KEY (username, name)
Matt Tucker's avatar
Matt Tucker committed
85 86 87 88 89 90 91 92
);

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

93
CREATE TABLE jiveProperty (
94 95
  name        VARCHAR(100)              NOT NULL,
  propValue   TEXT                      NOT NULL,
96 97 98
  PRIMARY KEY (name)
);

Matt Tucker's avatar
Matt Tucker committed
99 100 101 102 103 104

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

Matt Tucker's avatar
Matt Tucker committed
105 106 107 108
# MUC Tables

CREATE TABLE mucRoom (
  roomID              BIGINT        NOT NULL,
109 110
  creationDate        CHAR(15)      NOT NULL,
  modificationDate    CHAR(15)      NOT NULL,
111
  name                VARCHAR(50)   NOT NULL,
112
  naturalName         VARCHAR(255)  NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
113
  description         VARCHAR(255),
114
  lockedDate          CHAR(15)      NOT NULL,
115
  emptyDate           CHAR(15)      NULL,
Matt Tucker's avatar
Matt Tucker committed
116 117 118 119
  canChangeSubject    TINYINT       NOT NULL,
  maxUsers            INTEGER       NOT NULL,
  publicRoom          TINYINT       NOT NULL,
  moderated           TINYINT       NOT NULL,
120
  membersOnly         TINYINT       NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
121 122 123 124 125 126 127 128 129 130 131 132
  canInvite           TINYINT       NOT NULL,
  password            VARCHAR(50)   NULL,
  canDiscoverJID      TINYINT       NOT NULL,
  logEnabled          TINYINT       NOT NULL,
  subject             VARCHAR(100)  NULL,
  rolesToBroadcast    TINYINT       NOT NULL,
  PRIMARY KEY (name),
  INDEX mucRoom_roomid_idx (roomID)
);

CREATE TABLE mucAffiliation (
  roomID              BIGINT        NOT NULL,
133
  jid                 TEXT          NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
134 135 136 137 138 139
  affiliation         TINYINT       NOT NULL,
  PRIMARY KEY (roomID,jid(70))
);

CREATE TABLE mucMember (
  roomID              BIGINT        NOT NULL,
140
  jid                 TEXT          NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
141
  nickname            VARCHAR(255)  NULL,
142 143 144 145 146
  firstName           VARCHAR(100)  NULL,
  lastName            VARCHAR(100)  NULL,
  url                 VARCHAR(100)  NULL,
  email               VARCHAR(100)  NULL,
  faqentry            VARCHAR(100)  NULL,
Matt Tucker's avatar
Matt Tucker committed
147 148 149 150 151
  PRIMARY KEY (roomID,jid(70))
);

CREATE TABLE mucConversationLog (
  roomID              BIGINT        NOT NULL,
152
  sender              TEXT          NOT NULL,
Matt Tucker's avatar
Matt Tucker committed
153 154 155
  nickname            VARCHAR(255)  NULL,
  time                CHAR(15)      NOT NULL,
  subject             VARCHAR(255)  NULL,
156
  body                TEXT          NULL,
157
  INDEX mucLog_time_idx (time)
Matt Tucker's avatar
Matt Tucker committed
158 159 160 161 162 163 164 165
);

# 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
166 167
INSERT INTO jiveVersion (majorVersion, minorVersion) VALUES (2, 1);

Matt Tucker's avatar
Matt Tucker committed
168
# Entry for admin user
169 170
INSERT INTO jiveUser (username, password, name, email, creationDate, modificationDate)
    VALUES ('admin', 'admin', 'Administrator', 'admin@example.com', '0', '0');