[Sommaire] [Imprimer]

   1  /*
   2   * Description:
   3   * This file is used to create all tables used by WebCalendar and
   4   * initialize some of those tables with the required data.
   5   *
   6   * The comments in the table definitions will be parsed to
   7   * generate a document (in HTML) that describes these tables.
   8   *
   9   * History:
  10   * 21-Oct-2002 Added this file header and additional comments
  11   *   below.
  12   */
  13  
  14  /*
  15   * Defines a WebCalendar user.
  16   */
  17  CREATE TABLE webcal_user (
  18    /* the unique user login */
  19    cal_login VARCHAR(25) NOT NULL,
  20    /* the user's password. (not used for http or ldap authentication) */
  21    cal_passwd VARCHAR(32),
  22    /* user's last name */
  23    cal_lastname VARCHAR(25),
  24    /* user's first name */
  25    cal_firstname VARCHAR(25),
  26    /* is the user a WebCalendar administrator ('Y' = yes, 'N' = no) */
  27    cal_is_admin CHAR(1) DEFAULT 'N',
  28    /* user's email address */
  29    cal_email VARCHAR(75) NULL,
  30    PRIMARY KEY ( cal_login )
  31  );
  32  
  33  # create a default admin user
  34  INSERT INTO webcal_user ( cal_login, cal_passwd, cal_lastname, cal_firstname, cal_is_admin ) VALUES ( 'admin', '21232f297a57a5a743894a0e4a801fc3', 'Administrator', 'Default', 'Y' );
  35  
  36  /*
  37   * Defines a calendar event.  Each event in the system has one entry
  38   * in this table unless the event starts before midnight and ends
  39   * after midnight. In that case a secondary event will be created with
  40   * cal_ext_for_id set to the cal_id of the original entry.
  41   * The following tables contain additional information about each
  42   * event:<ul>
  43   * <li><a href="#webcal_entry_user">webcal_entry_user</a> -
  44   *  lists participants in the event and specifies the status (accepted,
  45   *  rejected) and category of each participant.</li>
  46   * <li><a href="#webcal_entry_repeats">webcal_entry_repeats</a> -
  47   *  contains information if the event repeats.</li>
  48   * <li><a href="#webcal_entry_repeats_not">webcal_entry_repeats_not</a> -
  49   *  specifies which dates the repeating event does not repeat (because
  50   *  they were deleted or modified for just that date by the user)</li>
  51   * <li><a href="#webcal_entry_log">webcal_entry_log</a> -
  52   *  provides a history of changes to this event.</li>
  53   * <li><a href="#webcal_site_extras">webcal_site_extras</a> -
  54   *  stores event data as defined in site_extras.php (such as reminders and
  55   *  other custom event fields).</li>
  56   * </ul>
  57   */
  58  CREATE TABLE webcal_entry (
  59    /* cal_id is unique integer id for event */
  60    cal_id INT NOT NULL,
  61    /* cal_group_id: the parent event id if this event is overriding an */
  62    /* occurrence of a repeating event */
  63    cal_group_id INT NULL,
  64    /* used when an event goes past midnight into the */
  65    /* next day, in which case an additional entry in this table */
  66    /* will use this field to indicate the original event cal_id */
  67    cal_ext_for_id INT NULL,
  68    /* user login of user that created the event */
  69    cal_create_by VARCHAR(25) NOT NULL,
  70    /* date of event (in YYYYMMDD format) */
  71    cal_date INT NOT NULL,
  72    /* event time (in HHMMSS format) */
  73    cal_time INT NULL,
  74    /* date the event was last modified (in YYYYMMDD format) */
  75    cal_mod_date INT,
  76    /* time the event was last modified (in HHMMSS format) */
  77    cal_mod_time INT,
  78    /* duration of event in minutes */
  79    cal_duration INT NOT NULL,
  80    /* event priority: 1=Low, 2=Med, 3=High */
  81    cal_priority INT DEFAULT 2,
  82    /* 'E' = Event, 'M' = Repeating event */
  83    cal_type CHAR(1) DEFAULT 'E',
  84    /* 'P' = Public, */
  85    /* 'R' = Confidential (others can see time allocated but not what it is) */
  86    cal_access CHAR(1) DEFAULT 'P',
  87    /* brief description of event */
  88    cal_name VARCHAR(80) NOT NULL,
  89    /* full description of event */
  90    cal_description TEXT,
  91    PRIMARY KEY ( cal_id )
  92  );
  93  
  94  /*
  95   * Defines repeating info about an event.
  96   * The event is defined in <a href="#webcal_entry">webcal_entry</a>.
  97   */
  98  CREATE TABLE webcal_entry_repeats (
  99    /* event id */
 100    cal_id INT DEFAULT 0 NOT NULL,
 101    /* type of repeating:<ul> */
 102    /* <li>daily - repeats daily</li> */
 103    /* <li>monthlyByDate - repeats on same day of the month</li> */
 104    /* <li>monthlyByDayR - repeats on same weekday of the month */
 105    /*   (counting weeks from the end of the month is in last Monday)</li> */
 106    /* <li>monthlyByDay - repeats on specified weekday (2nd Monday, for example)</li> */
 107    /* <li>weekly - repeats every week</li> */
 108    /* <li>yearly - repeats on same date every year</li> */
 109    cal_type VARCHAR(20),
 110    /* end date for repeating event (in YYYYMMDD format) */
 111    cal_end INT,
 112    /* frequency of repeat: 1 = every, 2 = every other, 3 = every 3rd, etc. */
 113    cal_frequency INT DEFAULT 1,
 114    /* which days of the week does it repeat on (only applies when cal_type = 'weekly' */
 115    cal_days CHAR(7),
 116    PRIMARY KEY (cal_id)
 117  );
 118  
 119  /*
 120   * This table specifies which dates in a repeating
 121   * event have either been deleted or replaced with
 122   * a replacement event for that day.  When replaced, the cal_group_id
 123   * (I know... not the best name, but it was not being used) column will
 124   * be set to the original event.  That way the user can delete the original
 125   * event and (at the same time) delete any exception events.
 126   */
 127  CREATE TABLE webcal_entry_repeats_not (
 128    /* event id of repeating event */
 129    cal_id INT NOT NULL,
 130    /* cal_date: date event should not repeat (in YYYYMMDD format) */
 131    cal_date INT NOT NULL,
 132    PRIMARY KEY ( cal_id, cal_date )
 133  );
 134  
 135  /*
 136   * This table associates one or more users with an event by the event id.
 137   * The event can be found in
 138   * <a href="#webcal_entry">webcal_entry</a>.
 139   */
 140  CREATE TABLE webcal_entry_user (
 141    /* event id */
 142    cal_id INT DEFAULT 0 NOT NULL,
 143    /* participant in the event */
 144    cal_login VARCHAR(25) NOT NULL,
 145    /* status of event for this user: <ul> */
 146    /* <li>A=Accepted</li> */
 147    /* <li>R=Rejected</li> */
 148    /* <li>W=Waiting</li>    </ul>*/
 149    cal_status CHAR(1) DEFAULT 'A',
 150    /* category of the event for this user */
 151    cal_category INT DEFAULT NULL,
 152    PRIMARY KEY ( cal_id, cal_login )
 153  );
 154  
 155  /*
 156   * This table associates one or more external users (people who do not
 157   * have a WebCalendar login) with an event by the event id.
 158   * An event must still have at least one WebCalendar user associated
 159   * with it.  This table is not used unless external users are enabled
 160   * in system settings.
 161   * The event can be found in
 162   * <a href="#webcal_entry">webcal_entry</a>.
 163   */
 164  CREATE TABLE webcal_entry_ext_user (
 165    /* event id */
 166    cal_id INT DEFAULT 0 NOT NULL,
 167    /* external user fill name */
 168    cal_fullname VARCHAR(50) NOT NULL,
 169    /* external user email (for sending a reminder) */
 170    cal_email VARCHAR(75) NULL,
 171    PRIMARY KEY ( cal_id, cal_fullname )
 172  );
 173  
 174  /*
 175   * Specify preferences for a user.
 176   * Most preferences are set via pref.php.
 177   * Values in this table are loaded after system settings
 178   * found in <a href="#webcal_config">webcal_config</a>.
 179   */
 180  CREATE TABLE webcal_user_pref (
 181    /* user login */
 182    cal_login VARCHAR(25) NOT NULL,
 183    /* setting name */
 184    cal_setting VARCHAR(25) NOT NULL,
 185    /* setting value */
 186    cal_value VARCHAR(100) NULL,
 187    PRIMARY KEY ( cal_login, cal_setting )
 188  );
 189  
 190  /*
 191   * Define layers for a user.
 192   */
 193  CREATE TABLE webcal_user_layers (
 194    /* unique layer id */
 195    cal_layerid INT DEFAULT 0 NOT NULL,
 196    /* login of owner of this layer */
 197    cal_login VARCHAR(25) NOT NULL,
 198    /* login name of user that this layer represents */
 199    cal_layeruser VARCHAR(25) NOT NULL,
 200    /* color to display this layer in */
 201    cal_color VARCHAR(25) NULL,
 202    /* show duplicates ('N' or 'Y') */
 203    cal_dups CHAR(1) DEFAULT 'N',
 204    PRIMARY KEY ( cal_login, cal_layeruser )
 205  );
 206  
 207  /*
 208   * This table holds data for site extra fields
 209   * (customized in site_extra.php).
 210   */
 211  CREATE TABLE webcal_site_extras (
 212    /* event id */
 213    cal_id INT DEFAULT 0 NOT NULL,
 214    /* the brief name of this type (first field in $site_extra array) */
 215    cal_name VARCHAR(25) NOT NULL,
 216    /* $EXTRA_URL, $EXTRA_DATE, etc. */
 217    cal_type INT NOT NULL,
 218    /* only used for $EXTRA_DATE type fields (in YYYYMMDD format) */
 219    cal_date INT DEFAULT 0,
 220    /* how many minutes before event should a reminder be sent */
 221    cal_remind INT DEFAULT 0,
 222    /* used to store text data */
 223    cal_data TEXT,
 224    PRIMARY KEY ( cal_id, cal_name, cal_type )
 225  );
 226  
 227  /*
 228   * This table keeps a history of when reminders get sent.
 229   */
 230  CREATE TABLE webcal_reminder_log (
 231    /* event id */
 232    cal_id INT DEFAULT 0 NOT NULL,
 233    /* extra type (see site_extras.php) */
 234    cal_name VARCHAR(25) NOT NULL,
 235    /* the event date we are sending reminder for (in YYYYMMDD format) */
 236    cal_event_date INT NOT NULL DEFAULT 0,
 237    /* the date/time we last sent a reminder (in UNIX time format) */
 238    cal_last_sent INT NOT NULL DEFAULT 0,
 239    PRIMARY KEY ( cal_id, cal_name, cal_event_date )
 240  );
 241  
 242  /*
 243   * Define a group.  Group members can be found in
 244   * <a href="#webcal_group_user">webcal_group_user</a>.
 245   */
 246  CREATE TABLE webcal_group (
 247    /* unique group id */
 248    cal_group_id INT NOT NULL,
 249    /* user login of user that created this group */
 250    cal_owner VARCHAR(25) NULL,
 251    /* name of the group */
 252    cal_name VARCHAR(50) NOT NULL,
 253    /* date last updated (in YYYYMMDD format) */
 254    cal_last_update INT NOT NULL,
 255    PRIMARY KEY ( cal_group_id )
 256  );
 257  
 258  /*
 259   * Specify users in a group.  The group is defined in
 260   * <a href="#webcal_group">webcal_group</a>.
 261   */
 262  CREATE TABLE webcal_group_user (
 263    /* group id */
 264    cal_group_id INT NOT NULL,
 265    /* user login */
 266    cal_login VARCHAR(25) NOT NULL,
 267    PRIMARY KEY ( cal_group_id, cal_login )
 268  );
 269  
 270  /*
 271   * A "view" allows a user to put the calendars of multiple users all on
 272   * one page.  A "view" is valid only for the owner (cal_owner) of the
 273   * view.  Users for the view are in
 274   * <a href="#webcal_view_user">webcal_view_user</a>.
 275   */
 276  CREATE TABLE webcal_view (
 277    /* unique view id */
 278    cal_view_id INT NOT NULL,
 279    /* login name of owner of this view */
 280    cal_owner VARCHAR(25) NOT NULL,
 281    /* name of view */
 282    cal_name VARCHAR(50) NOT NULL,
 283    /* "W" for week view, "D" for day view, "M" for month view */
 284    cal_view_type CHAR(1),
 285    /* is this a global view (can it be accessed by other users) ('Y' or 'N') */
 286    cal_is_global CHAR(1) DEFAULT 'N' NOT NULL,
 287    PRIMARY KEY ( cal_view_id )
 288  );
 289  
 290  /*
 291   * Specify users in a view. See <a href="#webcal_view">webcal_view</a>.
 292   */
 293  CREATE TABLE webcal_view_user (
 294    /* view id */
 295    cal_view_id INT NOT NULL,
 296    /* a user in the view */
 297    cal_login VARCHAR(25) NOT NULL,
 298    PRIMARY KEY ( cal_view_id, cal_login )
 299  );
 300  
 301  /*
 302   * System settings (set by the admin interface in admin.php)
 303   */
 304  CREATE TABLE webcal_config (
 305    /* setting name */
 306    cal_setting VARCHAR(50) NOT NULL,
 307    /* setting value */
 308    cal_value VARCHAR(100) NULL,
 309    PRIMARY KEY ( cal_setting )
 310  );
 311  
 312  # default settings
 313  INSERT INTO webcal_config ( cal_setting, cal_value )
 314    VALUES ( 'application_name', 'WebCalendar' );
 315  INSERT INTO webcal_config ( cal_setting, cal_value )
 316    VALUES ( 'LANGUAGE', 'Browser-defined' );
 317  INSERT INTO webcal_config ( cal_setting, cal_value )
 318    VALUES ( 'demo_mode', 'N' );
 319  INSERT INTO webcal_config ( cal_setting, cal_value )
 320    VALUES ( 'require_approvals', 'Y' );
 321  INSERT INTO webcal_config ( cal_setting, cal_value )
 322    VALUES ( 'groups_enabled', 'N' );
 323  INSERT INTO webcal_config ( cal_setting, cal_value )
 324    VALUES ( 'user_sees_only_his_groups', 'N' );
 325  INSERT INTO webcal_config ( cal_setting, cal_value )
 326    VALUES ( 'categories_enabled', 'N' );
 327  INSERT INTO webcal_config ( cal_setting, cal_value )
 328    VALUES ( 'allow_conflicts', 'N' );
 329  INSERT INTO webcal_config ( cal_setting, cal_value )
 330    VALUES ( 'conflict_repeat_months', '6' );
 331  INSERT INTO webcal_config ( cal_setting, cal_value )
 332    VALUES ( 'disable_priority_field', 'N' );
 333  INSERT INTO webcal_config ( cal_setting, cal_value )
 334    VALUES ( 'disable_access_field', 'N' );
 335  INSERT INTO webcal_config ( cal_setting, cal_value )
 336    VALUES ( 'disable_participants_field', 'N' );
 337  INSERT INTO webcal_config ( cal_setting, cal_value )
 338    VALUES ( 'disable_repeating_field', 'N' );
 339  INSERT INTO webcal_config ( cal_setting, cal_value )
 340    VALUES ( 'allow_view_other', 'Y' );
 341  INSERT INTO webcal_config ( cal_setting, cal_value )
 342    VALUES ( 'email_fallback_from', 'youremailhere' );
 343  INSERT INTO webcal_config ( cal_setting, cal_value )
 344    VALUES ( 'remember_last_login', 'Y' );
 345  INSERT INTO webcal_config ( cal_setting, cal_value )
 346    VALUES ( 'allow_color_customization', 'Y' );
 347  INSERT INTO webcal_config ( cal_setting, cal_value )
 348    VALUES ('BGCOLOR','#FFFFFF');
 349  INSERT INTO webcal_config ( cal_setting, cal_value )
 350    VALUES ('TEXTCOLOR','#000000');
 351  INSERT INTO webcal_config ( cal_setting, cal_value )
 352    VALUES ('H2COLOR','#000000');
 353  INSERT INTO webcal_config ( cal_setting, cal_value )
 354    VALUES ('CELLBG','#C0C0C0');
 355  INSERT INTO webcal_config ( cal_setting, cal_value )
 356    VALUES ('WEEKENDBG','#D0D0D0');
 357  INSERT INTO webcal_config ( cal_setting, cal_value )
 358    VALUES ('TABLEBG','#000000');
 359  INSERT INTO webcal_config ( cal_setting, cal_value )
 360    VALUES ('THBG','#FFFFFF');
 361  INSERT INTO webcal_config ( cal_setting, cal_value )
 362    VALUES ('THFG','#000000');
 363  INSERT INTO webcal_config ( cal_setting, cal_value )
 364    VALUES ('POPUP_FG','#000000');
 365  INSERT INTO webcal_config ( cal_setting, cal_value )
 366    VALUES ('POPUP_BG','#FFFFFF');
 367  INSERT INTO webcal_config ( cal_setting, cal_value )
 368    VALUES ('TODAYCELLBG','#FFFF33');
 369  INSERT INTO webcal_config ( cal_setting, cal_value )
 370    VALUES ( 'STARTVIEW', 'week.php' );
 371  INSERT INTO webcal_config ( cal_setting, cal_value )
 372    VALUES ( 'WEEK_START', '0' );
 373  INSERT INTO webcal_config ( cal_setting, cal_value )
 374    VALUES ( 'TIME_FORMAT', '12' );
 375  INSERT INTO webcal_config ( cal_setting, cal_value )
 376    VALUES ( 'DISPLAY_UNAPPROVED', 'Y' );
 377  INSERT INTO webcal_config ( cal_setting, cal_value )
 378    VALUES ( 'DISPLAY_WEEKNUMBER', 'Y' );
 379  INSERT INTO webcal_config ( cal_setting, cal_value )
 380    VALUES ( 'WORK_DAY_START_HOUR', '8' );
 381  INSERT INTO webcal_config ( cal_setting, cal_value )
 382    VALUES ( 'WORK_DAY_END_HOUR', '17' );
 383  INSERT INTO webcal_config ( cal_setting, cal_value )
 384    VALUES ( 'send_email', 'N' );
 385  INSERT INTO webcal_config ( cal_setting, cal_value )
 386    VALUES ( 'EMAIL_REMINDER', 'Y' );
 387  INSERT INTO webcal_config ( cal_setting, cal_value )
 388    VALUES ( 'EMAIL_EVENT_ADDED', 'Y' );
 389  INSERT INTO webcal_config ( cal_setting, cal_value )
 390    VALUES ( 'EMAIL_EVENT_UPDATED', 'Y' );
 391  INSERT INTO webcal_config ( cal_setting, cal_value )
 392    VALUES ( 'EMAIL_EVENT_DELETED', 'Y' );
 393  INSERT INTO webcal_config ( cal_setting, cal_value )
 394    VALUES ( 'EMAIL_EVENT_REJECTED', 'Y' );
 395  INSERT INTO webcal_config ( cal_setting, cal_value )
 396    VALUES ('auto_refresh', 'N');
 397  INSERT INTO webcal_config ( cal_setting, cal_value )
 398    VALUES ('nonuser_enabled', 'N');
 399  INSERT INTO webcal_config ( cal_setting, cal_value )
 400    VALUES ('allow_html_description', 'N');
 401  INSERT INTO webcal_config ( cal_setting, cal_value )
 402    VALUES ('reports_enabled', 'N');
 403  INSERT INTO webcal_config ( cal_setting, cal_value )
 404    VALUES ('DISPLAY_WEEKENDS', 'Y');
 405  INSERT INTO webcal_config ( cal_setting, cal_value )
 406    VALUES ('DISPLAY_DESC_PRINT_DAY', 'N');
 407  INSERT INTO webcal_config ( cal_setting, cal_value )
 408    VALUES ('DATE_FORMAT', '__month__ __dd__, __yyyy__');
 409  INSERT INTO webcal_config ( cal_setting, cal_value )
 410    VALUES ('TIME_SLOTS', '12');
 411  INSERT INTO webcal_config ( cal_setting, cal_value )
 412    VALUES ('TIMED_EVT_LEN', 'D');
 413  INSERT INTO webcal_config ( cal_setting, cal_value )
 414    VALUES ('PUBLISH_ENABLED', 'N');
 415  INSERT INTO webcal_config ( cal_setting, cal_value )
 416    VALUES ('DATE_FORMAT_MY', '__month__ __yyyy__');
 417  INSERT INTO webcal_config ( cal_setting, cal_value )
 418    VALUES ('DATE_FORMAT_MD', '__month__ __dd__');
 419  INSERT INTO webcal_config ( cal_setting, cal_value )
 420    VALUES ('CUSTOM_SCRIPT', 'N');
 421  INSERT INTO webcal_config ( cal_setting, cal_value )
 422    VALUES ('CUSTOM_HEADER', 'N');
 423  INSERT INTO webcal_config ( cal_setting, cal_value )
 424    VALUES ('CUSTOM_TRAILER', 'N');
 425  INSERT INTO webcal_config ( cal_setting, cal_value )
 426    VALUES ('bold_days_in_year', 'Y');
 427  INSERT INTO webcal_config ( cal_setting, cal_value )
 428    VALUES ('site_extras_in_popup', 'N');
 429  INSERT INTO webcal_config ( cal_setting, cal_value )
 430    VALUES ('add_link_in_views', 'Y');
 431  INSERT INTO webcal_config ( cal_setting, cal_value )
 432    VALUES ('allow_conflict_override', 'Y');
 433  INSERT INTO webcal_config ( cal_setting, cal_value )
 434    VALUES ('limit_appts', 'N');
 435  INSERT INTO webcal_config ( cal_setting, cal_value )
 436    VALUES ('limit_appts_number', '6');
 437  INSERT INTO webcal_config ( cal_setting, cal_value )
 438    VALUES ('public_access', 'N');
 439  INSERT INTO webcal_config ( cal_setting, cal_value )
 440    VALUES ('public_access_default_visible', 'N');
 441  INSERT INTO webcal_config ( cal_setting, cal_value )
 442    VALUES ('public_access_default_selected', 'N');
 443  INSERT INTO webcal_config ( cal_setting, cal_value )
 444    VALUES ('public_access_others', 'N');
 445  INSERT INTO webcal_config ( cal_setting, cal_value )
 446    VALUES ('public_access_can_add', 'N');
 447  INSERT INTO webcal_config ( cal_setting, cal_value )
 448    VALUES ('public_access_add_needs_approval', 'Y');
 449  INSERT INTO webcal_config ( cal_setting, cal_value )
 450    VALUES ('public_access_view_part', 'N');
 451  INSERT INTO webcal_config ( cal_setting, cal_value )
 452    VALUES ('nonuser_at_top', 'Y');
 453  INSERT INTO webcal_config ( cal_setting, cal_value )
 454    VALUES ('allow_external_users', 'N');
 455  INSERT INTO webcal_config ( cal_setting, cal_value )
 456    VALUES ('external_notifications', 'N');
 457  INSERT INTO webcal_config ( cal_setting, cal_value )
 458    VALUES ('external_reminders', 'N');
 459  INSERT INTO webcal_config ( cal_setting, cal_value )
 460    VALUES ('enable_gradients', 'N');
 461  
 462  
 463  /*
 464   * Activity log for an event.
 465   */
 466  CREATE TABLE webcal_entry_log (
 467    /* unique id of this log entry */
 468    cal_log_id INT NOT NULL,
 469    /* event id */
 470    cal_entry_id INT NOT NULL,
 471    /* user who performed this action */
 472    cal_login VARCHAR(25) NOT NULL,
 473    /* user of calendar affected */
 474    cal_user_cal VARCHAR(25) NULL,
 475    /* log types:  <ul> */
 476    /* <li>C: Created</li>  */
 477    /* <li>A: Approved/Confirmed by user</li>  */
 478    /* <li>R: Rejected by user</li>  */
 479    /* <li>U: Updated by user</li>  */
 480    /* <li>M: Mail Notification sent</li>  */
 481    /* <li>E: Reminder sent</li>     </ul>*/
 482    cal_type CHAR(1) NOT NULL,
 483    /* date in YYYYMMDD format */
 484    cal_date INT NOT NULL,
 485    /* time in HHMMSS format */
 486    cal_time INT NULL,
 487    /* optional text */
 488    cal_text TEXT,
 489    PRIMARY KEY ( cal_log_id )
 490  );
 491  
 492  /*
 493   * Defines user categories.
 494   * Categories can be specific to a user or global.  When a category is global,
 495   * the cat_owner field will be NULL.  (Only an admin user can create
 496   * a global category.)
 497   */
 498  CREATE TABLE webcal_categories (
 499    /* unique category id */
 500    cat_id INT NOT NULL,
 501    /* user login of category owner. */
 502    /* If this is NULL, then it is a global category */
 503    cat_owner VARCHAR(25) NULL,
 504    /* category name */
 505    cat_name VARCHAR(80) NOT NULL,
 506    PRIMARY KEY ( cat_id )
 507  );
 508  
 509  /*
 510   * Define assitant/boss relationship.
 511   */
 512  CREATE TABLE webcal_asst (
 513    /* user login of boss */
 514    cal_boss VARCHAR(25) NOT NULL,
 515    /* user login of assistant */
 516    cal_assistant VARCHAR(25) NOT NULL,
 517    PRIMARY KEY ( cal_boss, cal_assistant )
 518  );
 519  
 520  /*
 521   * Defines non-user calendars.
 522   */
 523  CREATE TABLE webcal_nonuser_cals (
 524    /* the unique id for the calendar */
 525    cal_login VARCHAR(25) NOT NULL,
 526    /* calendar's last name */
 527    cal_lastname VARCHAR(25) NULL,
 528    /* calendar's first name */
 529    cal_firstname VARCHAR(25) NULL,
 530    /* who is the calendar administrator */
 531    cal_admin VARCHAR(25) NOT NULL,
 532    PRIMARY KEY ( cal_login )
 533  );
 534  
 535  /*
 536   * Used to track import data (one row per import)
 537   */
 538  CREATE TABLE webcal_import (
 539    /* unique id for import */
 540    cal_import_id INT NOT NULL,
 541    /* name of import (optional) */
 542    cal_name VARCHAR(50) NULL,
 543    /* date of import (YYYYMMDD format) */
 544    cal_date INT NOT NULL,
 545    /* type of import (ical, vcal, palm) */
 546    cal_type VARCHAR(10) NOT NULL,
 547    /* user who performed the import */
 548    cal_login VARCHAR(25) NULL,
 549    PRIMARY KEY ( cal_import_id )
 550  );
 551  
 552  /*
 553   * Used to track import data (one row per event)
 554   */
 555  CREATE TABLE webcal_import_data (
 556    /* import id (from webcal_import table) */
 557    cal_import_id INT NOT NULL,
 558    /* event id in WebCalendar */
 559    cal_id INT NOT NULL,
 560    /* user login */
 561    cal_login VARCHAR(25) NOT NULL,
 562    /* type of import: 'palm', 'vcal', 'ical' or 'publish' */
 563    cal_import_type VARCHAR(15) NOT NULL,
 564    /* external id used in external calendar system (for example, UID in iCal) */
 565    cal_external_id VARCHAR(200) NULL,
 566    PRIMARY KEY  ( cal_id, cal_login )
 567  );
 568  
 569  
 570  /*
 571   * Defines a custom report created by a user.
 572   */
 573  CREATE TABLE webcal_report (
 574    /* creator of report */
 575    cal_login VARCHAR(25) NOT NULL,
 576    /* unique id of this report */
 577    cal_report_id INT NOT NULL,
 578    /* is this a global report (can it be accessed by other users) ('Y' or 'N') */
 579    cal_is_global CHAR(1) DEFAULT 'N' NOT NULL,
 580    /* format of report (html, plain or csv) */
 581    cal_report_type VARCHAR(20) NOT NULL,
 582    /* if cal_report_type is 'html', should the default HTML header and */
 583    /* trailer be included? ('Y' or 'N') */
 584    cal_include_header CHAR(1) DEFAULT 'Y' NOT NULL,
 585    /* name of the report */
 586    cal_report_name VARCHAR(50) NOT NULL,
 587    /* time range for report:  <ul> */
 588    /* <li>0 = tomorrow</li> */
 589    /* <li>1 = today</li> */
 590    /* <li>2 = yesterday</li> */
 591    /* <li>3 = day before yesterday</li> */
 592    /* <li>10 = next week</li> */
 593    /* <li>11 = current week</li> */
 594    /* <li>12 = last week</li> */
 595    /* <li>13 = week before last</li> */
 596    /* <li>20 = next week and week after</li> */
 597    /* <li>21 = current week and next week</li> */
 598    /* <li>22 = last week and this week</li> */
 599    /* <li>23 = last two weeks</li> */
 600    /* <li>30 = next month</li> */
 601    /* <li>31 = current month</li> */
 602    /* <li>32 = last month</li> */
 603    /* <li>33 = month before last</li> */
 604    /* <li>40 = next year</li> */
 605    /* <li>41 = current year</li> */
 606    /* <li>42 = last year</li> */
 607    /* <li>43 = year before last</li> */
 608    /* </ul> */
 609    cal_time_range INT NOT NULL,
 610    /* user calendar to display (NULL indicates current user) */
 611    cal_user VARCHAR(25) NULL,
 612    /* allow user to navigate to different dates with next/previous ('Y' or 'N') */
 613    cal_allow_nav CHAR(1) DEFAULT 'Y',
 614    /* category to filter on (optional) */
 615    cal_cat_id INT NULL,
 616    /* include empty dates in report ('Y' or 'N') */
 617    cal_include_empty CHAR(1) DEFAULT 'N',
 618    /* include a link for this report in the "Go to" section of the navigation */
 619    /* in the page trailer ('Y' or 'N') */
 620    cal_show_in_trailer CHAR(1) DEFAULT 'N',
 621    /* date created or last updated (in YYYYMMDD format) */
 622    cal_update_date INT NOT NULL,
 623    PRIMARY KEY ( cal_report_id )
 624  );
 625  
 626  /*
 627   * Defines one of the templates used for a report.
 628   * Each report has three templates:
 629   * <ol>
 630   * <li>Page template - Defines the entire page (except for header and
 631   *   footer).  The following variables can be defined:
 632   *   <ul>
 633   *     <li>${days}<sup>*</sup> - the HTML of all dates (generated from the Date template)</li>
 634   *   </ul></li>
 635   * <li>Date template - Defines events for one day.  If the report
 636   *   is for a week or month, then the results of each day will be
 637   *   concatenated and used as the ${days} variable in the Page template.
 638   *   The following variables can be defined:
 639   *   <ul>
 640   *     <li>${events}<sup>*</sup> - the HTML of all events
 641   *          for the data (generated from the Event template)</li>
 642   *     <li>${date} - the date</li>
 643   *     <li>${fulldate} - date (includes weekday)</li>
 644   *   </ul></li>
 645   * <li>Event template - Defines a single event.
 646   *      The following variables can be defined:
 647   *   <ul>
 648   *     <li>${name}<sup>*</sup> - Brief Description of event</li>
 649   *     <li>${description} - Full Description of event</li>
 650   *     <li>${date} - Date of event</li>
 651   *     <li>${fulldate} - Date of event (includes weekday)</li>
 652   *     <li>${time} - Time of event (4:00pm - 4:30pm)</li>
 653   *     <li>${starttime} - Start time of event</li>
 654   *     <li>${endtime} - End time of event</li>
 655   *     <li>${duration} - Duration of event (in minutes)</li>
 656   *     <li>${priority} - Priority of event</li>
 657   *     <li>${href} - URL to view event details</li>
 658   *   </ul></li>
 659   * </ol>
 660   * <sup>*</sup> denotes a required template variable
 661   */
 662  CREATE TABLE webcal_report_template (
 663    /* report id (in webcal_report table) */
 664    cal_report_id INT NOT NULL,
 665    /* type of template: <ul> */
 666    /* <li>'P': page template represents entire document</li> */
 667    /* <li>'D': date template represents a single day of events</li> */
 668    /* <li>'E': event template represents a single event</li> */
 669    /* </ul> */
 670    cal_template_type CHAR(1) NOT NULL,
 671    /* text of template */
 672    cal_template_text TEXT,
 673    PRIMARY KEY ( cal_report_id, cal_template_type )
 674  );