2 CREATE TABLE webcal_user (
3 cal_login VARCHAR(25) NOT NULL,
4 cal_passwd VARCHAR(32),
5 cal_lastname VARCHAR(25),
6 cal_firstname VARCHAR(25),
7 cal_is_admin CHAR(1) DEFAULT 'N',
8 cal_email VARCHAR(75) NULL,
9 PRIMARY KEY ( cal_login )
12 /* create a default admin user */
13 INSERT INTO webcal_user ( cal_login, cal_passwd, cal_lastname, cal_firstname, cal_is_admin ) VALUES ( 'admin', '21232f297a57a5a743894a0e4a801fc3', 'Administrator', 'Default', 'Y' );
16 /* Calendar event entry
17 * cal_date is an integer of the format YYYYMMDD
18 * cal_time is an integer of the format HHMM
19 * cal_duration is in minutes
20 * cal_priority: 1=Low, 2=Med, 3=High
21 * cal_type: E=Event ... and not yet implemented: D=Deadline, R=Reminder
24 * C=Confidential (others can see time allocated but not what it is)
26 CREATE TABLE webcal_entry (
28 cal_group_id INT NULL,
29 cal_ext_for_id INT NULL,
30 cal_create_by VARCHAR(25) NOT NULL,
31 cal_date INT NOT NULL,
35 cal_duration INT NOT NULL,
36 cal_priority INT DEFAULT 2,
37 cal_type CHAR(1) DEFAULT 'E',
38 cal_access CHAR(1) DEFAULT 'P',
39 cal_name VARCHAR(80) NOT NULL,
40 cal_description VARCHAR2(1024),
41 PRIMARY KEY ( cal_id )
45 CREATE TABLE webcal_entry_repeats (
46 cal_id INT DEFAULT '0' NOT NULL,
47 cal_type VARCHAR2(20),
49 cal_frequency INT DEFAULT '1',
54 /* This table specifies which dates in a repeating */
55 /* event have either been deleted or replaced with */
56 /* a replacement event for that day. When replaced, the cal_group_id */
57 /* (I know... not the best name, but it wasn't being used) column will */
58 /* be set to the original event. That way the user can delete the original */
59 /* event and (at the same time) delete any exception events. */
60 /* cal_id: event id of repeating event */
61 /* cal_date: date event should not repeat in YYYYMMDD format */
62 CREATE TABLE webcal_entry_repeats_not (
64 cal_date INT NOT NULL,
65 PRIMARY KEY ( cal_id, cal_date )
69 /* associates one or more users with an event by its id */
70 /* cal_status: A=Accepted, R=Rejected, W=Waiting */
71 CREATE TABLE webcal_entry_user (
72 cal_id int DEFAULT 0 NOT NULL,
73 cal_login varchar(25) NOT NULL,
74 cal_status char(1) DEFAULT 'A',
75 cal_category int DEFAULT NULL,
76 PRIMARY KEY (cal_id,cal_login)
80 /* external calendar users */
81 CREATE TABLE webcal_entry_ext_user (
82 cal_id INT DEFAULT 0 NOT NULL,
83 cal_fullname VARCHAR(50) NOT NULL,
84 cal_email VARCHAR(75) NULL,
85 PRIMARY KEY ( cal_id, cal_fullname )
89 /* preferences for a user */
90 CREATE TABLE webcal_user_pref (
91 cal_login varchar(25) NOT NULL,
92 cal_setting varchar(25) NOT NULL,
93 cal_value varchar(100) NULL,
94 PRIMARY KEY ( cal_login, cal_setting )
98 /* layers for a user */
99 CREATE TABLE webcal_user_layers (
100 cal_layerid INT DEFAULT '0' NOT NULL,
101 cal_login VARCHAR(25) NOT NULL,
102 cal_layeruser VARCHAR(25) NOT NULL,
103 cal_color VARCHAR(25) NULL,
104 cal_dups CHAR(1) DEFAULT 'N',
105 PRIMARY KEY ( cal_login, cal_layeruser )
108 /* site extra fields (customized in site_extra.php) */
109 /* cal_id is event id */
110 /* cal_name is the brief name of this type (first field in $site_extra array) */
111 /* cal_type is $EXTRA_URL, $EXTRA_DATE, etc. */
112 /* cal_date is only used for $EXTRA_DATE type fields */
113 /* cal_remind is many minutes before event should a reminder be sent */
114 /* cal_last_remind_date is the last event date (YYYYMMMDD) that a reminder */
115 /* was sent. This is not necessarily the date the msg was sent. It is the */
116 /* date of the event we are sending a reminder for. */
117 /* cal_data is used to store text data */
118 CREATE TABLE webcal_site_extras (
119 cal_id INT DEFAULT '0' NOT NULL,
120 cal_name VARCHAR(25) NOT NULL,
121 cal_type INT NOT NULL,
122 cal_date INT DEFAULT '0',
123 cal_remind INT DEFAULT '0',
125 PRIMARY KEY ( cal_id, cal_name, cal_type )
128 /* Keep a history of when reminders get sent */
129 /* cal_id is event id */
130 /* cal_name is extra type (see site_extras.php) */
131 /* cal_event_date is the event date we are sending reminder for */
132 /* (in YYYYMMDD format) */
133 /* cal_last_sent is the date/time we last sent a reminder */
134 /* (in UNIX time format) */
135 CREATE TABLE webcal_reminder_log (
136 cal_id INT DEFAULT '0' NOT NULL,
137 cal_name VARCHAR(25) NOT NULL,
138 cal_event_date INT DEFAULT '0' NOT NULL,
139 cal_last_sent INT DEFAULT '0' NOT NULL,
140 PRIMARY KEY ( cal_id, cal_name, cal_event_date )
144 /* cal_owner is the login of the creator of the group. */
145 CREATE TABLE webcal_group (
146 cal_group_id INT NOT NULL,
147 cal_owner VARCHAR2(25) NULL,
148 cal_name VARCHAR2(50) NOT NULL,
149 cal_last_update INT NOT NULL,
150 PRIMARY KEY ( cal_group_id )
153 /* Assign users to groups */
154 CREATE TABLE webcal_group_user (
155 cal_group_id INT NOT NULL,
156 cal_login VARCHAR2(25) NOT NULL,
157 PRIMARY KEY ( cal_group_id, cal_login )
160 /* A "view" allows a user to put the calendars of multiple users all on */
161 /* one page. A "view" is valid only for the owner (cal_owner) of the */
163 /* cal_view_type is "W" for week view, "D" for day view, "M" for month view */
164 CREATE TABLE webcal_view (
165 cal_view_id INT NOT NULL,
166 cal_owner VARCHAR2(25) NOT NULL,
167 cal_name VARCHAR2(50) NOT NULL,
168 cal_view_type CHAR(1),
169 cal_is_global CHAR(1) DEFAULT 'N' NOT NULL,
170 PRIMARY KEY ( cal_view_id )
173 CREATE TABLE webcal_view_user (
174 cal_view_id INT NOT NULL,
175 cal_login VARCHAR2(25) NOT NULL,
176 PRIMARY KEY ( cal_view_id, cal_login )
179 /* System settings (set by the admin interface in admin.php) */
180 CREATE TABLE webcal_config (
181 cal_setting VARCHAR2(50) NOT NULL,
182 cal_value VARCHAR2(100) NULL,
183 PRIMARY KEY ( cal_setting )
186 /* default settings */
187 INSERT INTO webcal_config ( cal_setting, cal_value )
188 VALUES ( 'application_name', 'WebCalendar' );
189 INSERT INTO webcal_config ( cal_setting, cal_value )
190 VALUES ( 'LANGUAGE', 'Browser-defined' );
191 INSERT INTO webcal_config ( cal_setting, cal_value )
192 VALUES ( 'demo_mode', 'N' );
193 INSERT INTO webcal_config ( cal_setting, cal_value )
194 VALUES ( 'require_approvals', 'Y' );
195 INSERT INTO webcal_config ( cal_setting, cal_value )
196 VALUES ( 'groups_enabled', 'N' );
197 INSERT INTO webcal_config ( cal_setting, cal_value )
198 VALUES ( 'user_sees_only_his_groups', 'N' );
199 INSERT INTO webcal_config ( cal_setting, cal_value )
200 VALUES ( 'categories_enabled', 'N' );
201 INSERT INTO webcal_config ( cal_setting, cal_value )
202 VALUES ( 'allow_conflicts', 'N' );
203 INSERT INTO webcal_config ( cal_setting, cal_value )
204 VALUES ( 'conflict_repeat_months', '6' );
205 INSERT INTO webcal_config ( cal_setting, cal_value )
206 VALUES ( 'disable_priority_field', 'N' );
207 INSERT INTO webcal_config ( cal_setting, cal_value )
208 VALUES ( 'disable_access_field', 'N' );
209 INSERT INTO webcal_config ( cal_setting, cal_value )
210 VALUES ( 'disable_participants_field', 'N' );
211 INSERT INTO webcal_config ( cal_setting, cal_value )
212 VALUES ( 'disable_repeating_field', 'N' );
213 INSERT INTO webcal_config ( cal_setting, cal_value )
214 VALUES ( 'allow_view_other', 'Y' );
215 INSERT INTO webcal_config ( cal_setting, cal_value )
216 VALUES ( 'email_fallback_from', 'youremailhere' );
217 INSERT INTO webcal_config ( cal_setting, cal_value )
218 VALUES ( 'remember_last_login', 'Y' );
219 INSERT INTO webcal_config ( cal_setting, cal_value )
220 VALUES ( 'allow_color_customization', 'Y' );
221 INSERT INTO webcal_config ( cal_setting, cal_value )
222 VALUES ('BGCOLOR','#FFFFFF');
223 INSERT INTO webcal_config ( cal_setting, cal_value )
224 VALUES ('TEXTCOLOR','#000000');
225 INSERT INTO webcal_config ( cal_setting, cal_value )
226 VALUES ('H2COLOR','#000000');
227 INSERT INTO webcal_config ( cal_setting, cal_value )
228 VALUES ('CELLBG','#C0C0C0');
229 INSERT INTO webcal_config ( cal_setting, cal_value )
230 VALUES ('WEEKENDBG','#D0D0D0');
231 INSERT INTO webcal_config ( cal_setting, cal_value )
232 VALUES ('TABLEBG','#000000');
233 INSERT INTO webcal_config ( cal_setting, cal_value )
234 VALUES ('THBG','#FFFFFF');
235 INSERT INTO webcal_config ( cal_setting, cal_value )
236 VALUES ('THFG','#000000');
237 INSERT INTO webcal_config ( cal_setting, cal_value )
238 VALUES ('POPUP_FG','#000000');
239 INSERT INTO webcal_config ( cal_setting, cal_value )
240 VALUES ('POPUP_BG','#FFFFFF');
241 INSERT INTO webcal_config ( cal_setting, cal_value )
242 VALUES ('TODAYCELLBG','#FFFF33');
243 INSERT INTO webcal_config ( cal_setting, cal_value )
244 VALUES ( 'STARTVIEW', 'week.php' );
245 INSERT INTO webcal_config ( cal_setting, cal_value )
246 VALUES ( 'WEEK_START', '0' );
247 INSERT INTO webcal_config ( cal_setting, cal_value )
248 VALUES ( 'TIME_FORMAT', '12' );
249 INSERT INTO webcal_config ( cal_setting, cal_value )
250 VALUES ( 'DISPLAY_UNAPPROVED', 'Y' );
251 INSERT INTO webcal_config ( cal_setting, cal_value )
252 VALUES ( 'DISPLAY_WEEKNUMBER', 'Y' );
253 INSERT INTO webcal_config ( cal_setting, cal_value )
254 VALUES ( 'WORK_DAY_START_HOUR', '8' );
255 INSERT INTO webcal_config ( cal_setting, cal_value )
256 VALUES ( 'WORK_DAY_END_HOUR', '17' );
257 INSERT INTO webcal_config ( cal_setting, cal_value )
258 VALUES ( 'send_email', 'N' );
259 INSERT INTO webcal_config ( cal_setting, cal_value )
260 VALUES ( 'EMAIL_REMINDER', 'Y' );
261 INSERT INTO webcal_config ( cal_setting, cal_value )
262 VALUES ( 'EMAIL_EVENT_ADDED', 'Y' );
263 INSERT INTO webcal_config ( cal_setting, cal_value )
264 VALUES ( 'EMAIL_EVENT_UPDATED', 'Y' );
265 INSERT INTO webcal_config ( cal_setting, cal_value )
266 VALUES ( 'EMAIL_EVENT_DELETED', 'Y' );
267 INSERT INTO webcal_config ( cal_setting, cal_value )
268 VALUES ( 'EMAIL_EVENT_REJECTED', 'Y' );
269 INSERT INTO webcal_config ( cal_setting, cal_value )
270 VALUES ('auto_refresh', 'N');
271 INSERT INTO webcal_config ( cal_setting, cal_value )
272 VALUES ('nonuser_enabled', 'N');
273 INSERT INTO webcal_config ( cal_setting, cal_value )
274 VALUES ('allow_html_description', 'N');
275 INSERT INTO webcal_config ( cal_setting, cal_value )
276 VALUES ('reports_enabled', 'N');
277 INSERT INTO webcal_config ( cal_setting, cal_value )
278 VALUES ('DISPLAY_WEEKENDS', 'Y');
279 INSERT INTO webcal_config ( cal_setting, cal_value )
280 VALUES ('DISPLAY_DESC_PRINT_DAY', 'N');
281 INSERT INTO webcal_config ( cal_setting, cal_value )
282 VALUES ('DATE_FORMAT', '__month__ __dd__, __yyyy__');
283 INSERT INTO webcal_config ( cal_setting, cal_value )
284 VALUES ('TIME_SLOTS', '12');
285 INSERT INTO webcal_config ( cal_setting, cal_value )
286 VALUES ('TIMED_EVT_LEN', 'D');
287 INSERT INTO webcal_config ( cal_setting, cal_value )
288 VALUES ('PUBLISH_ENABLED', 'N');
289 INSERT INTO webcal_config ( cal_setting, cal_value )
290 VALUES ('DATE_FORMAT_MY', '__month__ __yyyy__');
291 INSERT INTO webcal_config ( cal_setting, cal_value )
292 VALUES ('DATE_FORMAT_MD', '__month__ __dd__');
293 INSERT INTO webcal_config ( cal_setting, cal_value )
294 VALUES ('CUSTOM_SCRIPT', 'N');
295 INSERT INTO webcal_config ( cal_setting, cal_value )
296 VALUES ('CUSTOM_HEADER', 'N');
297 INSERT INTO webcal_config ( cal_setting, cal_value )
298 VALUES ('CUSTOM_TRAILER', 'N');
299 INSERT INTO webcal_config ( cal_setting, cal_value )
300 VALUES ('bold_days_in_year', 'Y');
301 INSERT INTO webcal_config ( cal_setting, cal_value )
302 VALUES ('site_extras_in_popup', 'N');
303 INSERT INTO webcal_config ( cal_setting, cal_value )
304 VALUES ('add_link_in_views', 'Y');
305 INSERT INTO webcal_config ( cal_setting, cal_value )
306 VALUES ('allow_conflict_override', 'Y');
307 INSERT INTO webcal_config ( cal_setting, cal_value )
308 VALUES ('limit_appts', 'N');
309 INSERT INTO webcal_config ( cal_setting, cal_value )
310 VALUES ('limit_appts_number', '6');
311 INSERT INTO webcal_config ( cal_setting, cal_value )
312 VALUES ('public_access', 'N');
313 INSERT INTO webcal_config ( cal_setting, cal_value )
314 VALUES ('public_access_default_visible', 'N');
315 INSERT INTO webcal_config ( cal_setting, cal_value )
316 VALUES ('public_access_default_selected', 'N');
317 INSERT INTO webcal_config ( cal_setting, cal_value )
318 VALUES ('public_access_others', 'N');
319 INSERT INTO webcal_config ( cal_setting, cal_value )
320 VALUES ('public_access_can_add', 'N');
321 INSERT INTO webcal_config ( cal_setting, cal_value )
322 VALUES ('public_access_add_needs_approval', 'Y');
323 INSERT INTO webcal_config ( cal_setting, cal_value )
324 VALUES ('public_access_view_part', 'N');
325 INSERT INTO webcal_config ( cal_setting, cal_value )
326 VALUES ('nonuser_at_top', 'Y');
327 INSERT INTO webcal_config ( cal_setting, cal_value )
328 VALUES ('allow_external_users', 'N');
329 INSERT INTO webcal_config ( cal_setting, cal_value )
330 VALUES ('external_notifications', 'N');
331 INSERT INTO webcal_config ( cal_setting, cal_value )
332 VALUES ('external_reminders', 'N');
333 INSERT INTO webcal_config ( cal_setting, cal_value )
334 VALUES ('enable_gradients', 'N');
338 /* activity log for an event */
339 /* log types (cal_type): */
341 /* A: Approved/Confirmed by user */
342 /* R: Rejected by user */
343 /* U: Updated by user */
344 /* M: Mail Notification sent */
345 /* E: Reminder sent */
346 CREATE TABLE webcal_entry_log (
347 cal_log_id INT NOT NULL,
348 cal_entry_id INT NOT NULL,
349 cal_login VARCHAR2(25) NOT NULL,
350 cal_user_cal VARCHAR2(25) NULL,
351 cal_type CHAR(1) NOT NULL,
352 cal_date INT NOT NULL,
354 cal_text VARCHAR2(1024),
355 PRIMARY KEY ( cal_log_id )
358 /* user categories */
359 CREATE TABLE webcal_categories (
361 cat_owner VARCHAR2(25),
362 cat_name VARCHAR2(80) NOT NULL,
363 PRIMARY KEY ( cat_id )
366 CREATE TABLE webcal_asst (
367 cal_boss VARCHAR2(25) NOT NULL,
368 cal_assistant VARCHAR2(25) NOT NULL,
369 PRIMARY KEY ( cal_boss, cal_assistant )
372 CREATE TABLE webcal_nonuser_cals (
373 cal_login VARCHAR2(25) NOT NULL,
374 cal_lastname VARCHAR2(25) NULL,
375 cal_firstname VARCHAR2(25) NULL,
376 cal_admin VARCHAR2(25) NOT NULL,
377 PRIMARY KEY ( cal_login )
380 CREATE TABLE webcal_import (
381 cal_import_id INT NOT NULL,
382 cal_name VARCHAR2(50) NULL,
383 cal_date INT NOT NULL,
384 cal_type VARCHAR2(10) NOT NULL,
385 cal_login VARCHAR2(25) NULL,
386 PRIMARY KEY ( cal_import_id )
389 CREATE TABLE webcal_import_data (
390 cal_import_id INT NOT NULL,
392 cal_login VARCHAR2(25) NOT NULL,
393 cal_import_type VARCHAR2(15) NOT NULL,
394 cal_external_id VARCHAR2(200) NULL,
395 PRIMARY KEY ( cal_id, cal_login )
398 CREATE TABLE webcal_report (
399 cal_login VARCHAR(25) NOT NULL,
400 cal_report_id INT NOT NULL,
401 cal_is_global CHAR(1) DEFAULT 'N' NOT NULL,
402 cal_report_type VARCHAR(20) NOT NULL,
403 cal_include_header CHAR(1) DEFAULT 'Y' NOT NULL,
404 cal_report_name VARCHAR(50) NOT NULL,
405 cal_time_range INT NOT NULL,
406 cal_user VARCHAR(25) NULL,
407 cal_allow_nav CHAR(1) DEFAULT 'Y',
409 cal_include_empty CHAR(1) DEFAULT 'N',
410 cal_show_in_trailer CHAR(1) DEFAULT 'N',
411 cal_update_date INT NOT NULL,
412 PRIMARY KEY ( cal_report_id )
415 CREATE TABLE webcal_report_template (
416 cal_report_id INT NOT NULL,
417 cal_template_type CHAR(1) NOT NULL,
418 cal_template_text LONG,
419 PRIMARY KEY ( cal_report_id, cal_template_type )