[Sommaire] [Imprimer]

   1  
   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 )
  10  );
  11  
  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' );
  14  
  15  
  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
  22   * cal_access:
  23   * P=Public
  24   * C=Confidential (others can see time allocated but not what it is)
  25   */
  26  CREATE TABLE webcal_entry (
  27    cal_id INT NOT NULL,
  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,
  32    cal_time INT NULL,
  33    cal_mod_date INT,
  34    cal_mod_time INT,
  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 )
  42  );
  43  
  44  
  45  CREATE TABLE webcal_entry_repeats (
  46     cal_id INT DEFAULT '0' NOT NULL,
  47     cal_type VARCHAR2(20),
  48     cal_end INT,
  49     cal_frequency INT DEFAULT '1',
  50     cal_days CHAR(7),
  51     PRIMARY KEY (cal_id)
  52  );
  53  
  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 (
  63    cal_id INT NOT NULL,
  64    cal_date INT NOT NULL,
  65    PRIMARY KEY ( cal_id, cal_date )
  66  );
  67  
  68  
  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)
  77  );
  78  
  79  
  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 )
  86  );
  87  
  88  
  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 )
  95  );
  96  
  97  
  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 )
 106  );
 107  
 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',
 124    cal_data LONG,
 125    PRIMARY KEY ( cal_id, cal_name, cal_type )
 126  );
 127  
 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 )
 141  );
 142  
 143  /* Group support */
 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 )
 151  );
 152  
 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 )
 158  );
 159  
 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 */
 162  /* view. */
 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 )
 171  );
 172  
 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 )
 177  );
 178  
 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 )
 184  );
 185  
 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');
 335  
 336  
 337  
 338  /* activity log for an event */
 339  /* log types (cal_type): */
 340  /*   C: Created */
 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,
 353    cal_time INT NULL,
 354    cal_text VARCHAR2(1024),
 355    PRIMARY KEY ( cal_log_id )
 356  );
 357  
 358  /* user categories */
 359  CREATE TABLE webcal_categories (
 360    cat_id INT NOT NULL,
 361    cat_owner VARCHAR2(25),
 362    cat_name VARCHAR2(80) NOT NULL,
 363    PRIMARY KEY ( cat_id )
 364  );
 365  
 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 )
 370  );
 371  
 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 )
 378  );
 379  
 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 )
 387  );
 388  
 389  CREATE TABLE webcal_import_data (
 390    cal_import_id INT NOT NULL,
 391    cal_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 )
 396  );
 397  
 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',
 408    cal_cat_id INT NULL,
 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 )
 413  );
 414  
 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 )
 420  );
 421  
 422  
 423  
 424