-
Notifications
You must be signed in to change notification settings - Fork 0
/
tables.my.sql
208 lines (190 loc) · 8.86 KB
/
tables.my.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
#
# MySQL MRBS table creation script
#
# Notes:
# (1) If you have decided to change the prefix of your tables from 'mrbs_'
# to something else using $db_tbl_prefix then you must edit each
# 'CREATE TABLE', 'INSERT INTO' and 'REFERENCES' line below to replace
# 'mrbs_' with your new table prefix. A global replace of 'mrbs_' is
# sufficient.
#
# (2) If you add new fields then you should also change the global variable
# $standard_fields. Note that if you are just adding custom fields for
# a single site then this is not necessary.
CREATE TABLE mrbs_area
(
id int NOT NULL auto_increment,
disabled tinyint(1) DEFAULT 0 NOT NULL,
area_name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
sort_key varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' NOT NULL,
timezone varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
area_admin_email text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
resolution int,
default_duration int,
default_duration_all_day tinyint(1) DEFAULT 0 NOT NULL,
morningstarts int,
morningstarts_minutes int,
eveningends int,
eveningends_minutes int,
private_enabled tinyint(1),
private_default tinyint(1),
private_mandatory tinyint(1),
private_override varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
min_create_ahead_enabled tinyint(1),
min_create_ahead_secs int,
max_create_ahead_enabled tinyint(1),
max_create_ahead_secs int,
min_delete_ahead_enabled tinyint(1),
min_delete_ahead_secs int,
max_delete_ahead_enabled tinyint(1),
max_delete_ahead_secs int,
max_per_day_enabled tinyint(1) DEFAULT 0 NOT NULL,
max_per_day int DEFAULT 0 NOT NULL,
max_per_week_enabled tinyint(1) DEFAULT 0 NOT NULL,
max_per_week int DEFAULT 0 NOT NULL,
max_per_month_enabled tinyint(1) DEFAULT 0 NOT NULL,
max_per_month int DEFAULT 0 NOT NULL,
max_per_year_enabled tinyint(1) DEFAULT 0 NOT NULL,
max_per_year int DEFAULT 0 NOT NULL,
max_per_future_enabled tinyint(1) DEFAULT 0 NOT NULL,
max_per_future int DEFAULT 0 NOT NULL,
max_duration_enabled tinyint(1) DEFAULT 0 NOT NULL,
max_duration_secs int DEFAULT 0 NOT NULL,
max_duration_periods int DEFAULT 0 NOT NULL,
custom_html text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
approval_enabled tinyint(1),
reminders_enabled tinyint(1),
enable_periods tinyint(1),
periods text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
confirmation_enabled tinyint(1),
confirmed_default tinyint(1),
PRIMARY KEY (id),
UNIQUE KEY uq_area_name (area_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE mrbs_room
(
id int NOT NULL auto_increment,
disabled tinyint(1) DEFAULT 0 NOT NULL,
area_id int DEFAULT '0' NOT NULL,
room_name varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' NOT NULL,
sort_key varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' NOT NULL,
description varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
capacity int DEFAULT '0' NOT NULL,
room_admin_email text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
custom_html text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (id),
FOREIGN KEY (area_id)
REFERENCES mrbs_area(id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
UNIQUE KEY uq_room_name (area_id, room_name),
KEY idxSortKey (sort_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE mrbs_repeat
(
id int NOT NULL auto_increment,
start_time int DEFAULT '0' NOT NULL,
end_time int DEFAULT '0' NOT NULL,
rep_type int DEFAULT '0' NOT NULL,
end_date int DEFAULT '0' NOT NULL,
rep_opt varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' NOT NULL,
room_id int DEFAULT '1' NOT NULL,
timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
create_by varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' NOT NULL,
modified_by varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' NOT NULL,
name varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' NOT NULL,
type char DEFAULT 'E' NOT NULL,
description text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
rep_num_weeks smallint NULL,
month_absolute smallint DEFAULT NULL,
month_relative varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
status tinyint unsigned NOT NULL DEFAULT 0,
reminded int,
info_time int,
info_user varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
info_text text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
ical_uid varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' NOT NULL,
ical_sequence smallint DEFAULT 0 NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (room_id)
REFERENCES mrbs_room(id)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE mrbs_entry
(
id int NOT NULL auto_increment,
start_time int DEFAULT '0' NOT NULL,
end_time int DEFAULT '0' NOT NULL,
entry_type int DEFAULT '0' NOT NULL,
repeat_id int DEFAULT NULL,
room_id int DEFAULT '1' NOT NULL,
timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
create_by varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' NOT NULL,
modified_by varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' NOT NULL,
name varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' NOT NULL,
type char DEFAULT 'E' NOT NULL,
description text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
status tinyint unsigned NOT NULL DEFAULT 0,
reminded int,
info_time int,
info_user varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
info_text text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
ical_uid varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' NOT NULL,
ical_sequence smallint DEFAULT 0 NOT NULL,
ical_recur_id varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (id),
FOREIGN KEY (room_id)
REFERENCES mrbs_room(id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
FOREIGN KEY (repeat_id)
REFERENCES mrbs_repeat(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
KEY idxStartTime (start_time),
KEY idxEndTime (end_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE mrbs_variables
(
id int NOT NULL auto_increment,
variable_name varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
variable_content text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (id),
UNIQUE KEY uq_variable_name (variable_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE mrbs_zoneinfo
(
id int NOT NULL auto_increment,
timezone varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '' NOT NULL,
outlook_compatible tinyint unsigned NOT NULL DEFAULT 0,
vtimezone text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
last_updated int NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY uq_timezone (timezone, outlook_compatible)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE mrbs_sessions
(
id varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
access int unsigned DEFAULT NULL,
data text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (id),
KEY idxAccess (access)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE mrbs_users
(
/* The first four fields are required. Don't remove. */
id int NOT NULL auto_increment,
level smallint DEFAULT '0' NOT NULL, /* play safe and give no rights */
name varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
password_hash varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
email varchar(75) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
timestamp timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
last_login int DEFAULT '0' NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ( 'db_version', '59');
INSERT INTO mrbs_variables (variable_name, variable_content)
VALUES ( 'local_db_version', '1');