WebCalendar Database Documentation

Home Page: http://webcalendar.sourceforge.net/
Author: Craig Knudsen, cknudsen@cknudsen.com
Version: v1.0.0
$Id: WebCalendar-Database.html,v 1.15 2005/05/17 13:06:04 cknudsen Exp $
Last updated:$Date: 2005/05/17 13:06:04 $
(by $Author: cknudsen $)
This file is generated from tables-mysql.sql. Below are the definitions of all WebCalendar tables along with some descriptions of how each table is used. Column names shown in red are the primary keys for that table.

If you update the SQL for WebCalendar, use the sql2html.pl script to regenerate this file.


List of Tables




webcal_asst

Define assitant/boss relationship.

Column NameTypeLengthNullDefaultDescription
cal_bossVARCHAR25N  user login of boss
cal_assistantVARCHAR25N  user login of assistant


webcal_categories

Defines user categories. Categories can be specific to a user or global. When a category is global, the cat_owner field will be NULL. (Only an admin user can create a global category.)

Column NameTypeLengthNullDefaultDescription
cat_idINT N  unique category id
cat_ownerVARCHAR25Y  user login of category owner. If this is NULL, then it is a global category
cat_nameVARCHAR80N  category name


webcal_config

System settings (set by the admin interface in admin.php)

Column NameTypeLengthNullDefaultDescription
cal_settingVARCHAR50N  setting name
cal_valueVARCHAR100Y  setting value


webcal_entry

Defines a calendar event. Each event in the system has one entry in this table unless the event starts before midnight and ends after midnight. In that case a secondary event will be created with cal_ext_for_id set to the cal_id of the original entry. The following tables contain additional information about each event:

Column NameTypeLengthNullDefaultDescription
cal_idINT N  cal_id is unique integer id for event
cal_group_idINT Y  cal_group_id: the parent event id if this event is overriding an occurrence of a repeating event
cal_ext_for_idINT Y  used when an event goes past midnight into the next day, in which case an additional entry in this table will use this field to indicate the original event cal_id
cal_create_byVARCHAR25N  user login of user that created the event
cal_dateINT N  date of event (in YYYYMMDD format)
cal_timeINT Y  event time (in HHMMSS format)
cal_mod_dateINT Y  date the event was last modified (in YYYYMMDD format)
cal_mod_timeINT Y  time the event was last modified (in HHMMSS format)
cal_durationINT N  duration of event in minutes
cal_priorityINT Y2 event priority: 1=Low, 2=Med, 3=High
cal_typeCHAR1Y'E' 'E' = Event, 'M' = Repeating event
cal_accessCHAR1Y'P' 'P' = Public, 'R' = Confidential (others can see time allocated but not what it is)
cal_nameVARCHAR80N  brief description of event
cal_descriptionTEXT Y  full description of event


webcal_entry_ext_user

This table associates one or more external users (people who do not have a WebCalendar login) with an event by the event id. An event must still have at least one WebCalendar user associated with it. This table is not used unless external users are enabled in system settings. The event can be found in webcal_entry.

Column NameTypeLengthNullDefaultDescription
cal_idINT N0 event id
cal_fullnameVARCHAR50N  external user fill name
cal_emailVARCHAR75Y  external user email (for sending a reminder)


webcal_entry_log

Activity log for an event.

Column NameTypeLengthNullDefaultDescription
cal_log_idINT N  unique id of this log entry
cal_entry_idINT N  event id
cal_loginVARCHAR25N  user who performed this action
cal_user_calVARCHAR25Y  user of calendar affected
cal_typeCHAR1N  log types:
  • C: Created
  • A: Approved/Confirmed by user
  • R: Rejected by user
  • U: Updated by user
  • M: Mail Notification sent
  • E: Reminder sent
cal_dateINT N  date in YYYYMMDD format
cal_timeINT Y  time in HHMMSS format
cal_textTEXT Y  optional text


webcal_entry_repeats

Defines repeating info about an event. The event is defined in webcal_entry.

Column NameTypeLengthNullDefaultDescription
cal_idINT N0 event id
cal_typeVARCHAR20Y  type of repeating:
  • daily - repeats daily
  • monthlyByDate - repeats on same day of the month
  • monthlyByDayR - repeats on same weekday of the month (counting weeks from the end of the month is in last Monday)
  • monthlyByDay - repeats on specified weekday (2nd Monday, for example)
  • weekly - repeats every week
  • yearly - repeats on same date every year
cal_endINT Y  end date for repeating event (in YYYYMMDD format)
cal_frequencyINT Y1 frequency of repeat: 1 = every, 2 = every other, 3 = every 3rd, etc.
cal_daysCHAR7Y  which days of the week does it repeat on (only applies when cal_type = 'weekly'


webcal_entry_repeats_not

This table specifies which dates in a repeating event have either been deleted or replaced with a replacement event for that day. When replaced, the cal_group_id (I know... not the best name, but it was not being used) column will be set to the original event. That way the user can delete the original event and (at the same time) delete any exception events.

Column NameTypeLengthNullDefaultDescription
cal_idINT N  event id of repeating event
cal_dateINT N  cal_date: date event should not repeat (in YYYYMMDD format)


webcal_entry_user

This table associates one or more users with an event by the event id. The event can be found in webcal_entry.

Column NameTypeLengthNullDefaultDescription
cal_idINT N0 event id
cal_loginVARCHAR25N  participant in the event
cal_statusCHAR1Y'A' status of event for this user:
  • A=Accepted
  • R=Rejected
  • W=Waiting
cal_categoryINT YNULL category of the event for this user


webcal_group

Define a group. Group members can be found in webcal_group_user.

Column NameTypeLengthNullDefaultDescription
cal_group_idINT N  unique group id
cal_ownerVARCHAR25Y  user login of user that created this group
cal_nameVARCHAR50N  name of the group
cal_last_updateINT N  date last updated (in YYYYMMDD format)


webcal_group_user

Specify users in a group. The group is defined in webcal_group.

Column NameTypeLengthNullDefaultDescription
cal_group_idINT N  group id
cal_loginVARCHAR25N  user login


webcal_import

Used to track import data (one row per import)

Column NameTypeLengthNullDefaultDescription
cal_import_idINT N  unique id for import
cal_nameVARCHAR50Y  name of import (optional)
cal_dateINT N  date of import (YYYYMMDD format)
cal_typeVARCHAR10N  type of import (ical, vcal, palm)
cal_loginVARCHAR25Y  user who performed the import


webcal_import_data

Used to track import data (one row per event)

Column NameTypeLengthNullDefaultDescription
cal_import_idINT N  import id (from webcal_import table)
cal_idINT N  event id in WebCalendar
cal_loginVARCHAR25N  user login
cal_import_typeVARCHAR15N  type of import: 'palm', 'vcal', 'ical' or 'publish'
cal_external_idVARCHAR200Y  external id used in external calendar system (for example, UID in iCal)


webcal_nonuser_cals

Defines non-user calendars.

Column NameTypeLengthNullDefaultDescription
cal_loginVARCHAR25N  the unique id for the calendar
cal_lastnameVARCHAR25Y  calendar's last name
cal_firstnameVARCHAR25Y  calendar's first name
cal_adminVARCHAR25N  who is the calendar administrator


webcal_reminder_log

This table keeps a history of when reminders get sent.

Column NameTypeLengthNullDefaultDescription
cal_idINT N0 event id
cal_nameVARCHAR25N  extra type (see site_extras.php)
cal_event_dateINT N0 the event date we are sending reminder for (in YYYYMMDD format)
cal_last_sentINT N0 the date/time we last sent a reminder (in UNIX time format)


webcal_report

Defines a custom report created by a user.

Column NameTypeLengthNullDefaultDescription
cal_loginVARCHAR25N  creator of report
cal_report_idINT N  unique id of this report
cal_is_globalCHAR1N'N' is this a global report (can it be accessed by other users) ('Y' or 'N')
cal_report_typeVARCHAR20N  format of report (html, plain or csv)
cal_include_headerCHAR1N'Y' if cal_report_type is 'html', should the default HTML header and trailer be included? ('Y' or 'N')
cal_report_nameVARCHAR50N  name of the report
cal_time_rangeINT N  time range for report:
  • 0 = tomorrow
  • 1 = today
  • 2 = yesterday
  • 3 = day before yesterday
  • 10 = next week
  • 11 = current week
  • 12 = last week
  • 13 = week before last
  • 20 = next week and week after
  • 21 = current week and next week
  • 22 = last week and this week
  • 23 = last two weeks
  • 30 = next month
  • 31 = current month
  • 32 = last month
  • 33 = month before last
  • 40 = next year
  • 41 = current year
  • 42 = last year
  • 43 = year before last
cal_userVARCHAR25Y  user calendar to display (NULL indicates current user)
cal_allow_navCHAR1Y'Y' allow user to navigate to different dates with next/previous ('Y' or 'N')
cal_cat_idINT Y  category to filter on (optional)
cal_include_emptyCHAR1Y'N' include empty dates in report ('Y' or 'N')
cal_show_in_trailerCHAR1Y'N' include a link for this report in the "Go to" section of the navigation in the page trailer ('Y' or 'N')
cal_update_dateINT N  date created or last updated (in YYYYMMDD format)


webcal_report_template

Defines one of the templates used for a report. Each report has three templates:
  1. Page template - Defines the entire page (except for header and footer). The following variables can be defined:
    • ${days}* - the HTML of all dates (generated from the Date template)
  2. Date template - Defines events for one day. If the report is for a week or month, then the results of each day will be concatenated and used as the ${days} variable in the Page template. The following variables can be defined:
    • ${events}* - the HTML of all events for the data (generated from the Event template)
    • ${date} - the date
    • ${fulldate} - date (includes weekday)
  3. Event template - Defines a single event. The following variables can be defined:
    • ${name}* - Brief Description of event
    • ${description} - Full Description of event
    • ${date} - Date of event
    • ${fulldate} - Date of event (includes weekday)
    • ${time} - Time of event (4:00pm - 4:30pm)
    • ${starttime} - Start time of event
    • ${endtime} - End time of event
    • ${duration} - Duration of event (in minutes)
    • ${priority} - Priority of event
    • ${href} - URL to view event details
* denotes a required template variable

Column NameTypeLengthNullDefaultDescription
cal_report_idINT N  report id (in webcal_report table)
cal_template_typeCHAR1N  type of template:
  • 'P': page template represents entire document
  • 'D': date template represents a single day of events
  • 'E': event template represents a single event
cal_template_textTEXT Y  text of template


webcal_site_extras

This table holds data for site extra fields (customized in site_extra.php).

Column NameTypeLengthNullDefaultDescription
cal_idINT N0 event id
cal_nameVARCHAR25N  the brief name of this type (first field in $site_extra array)
cal_typeINT N  $EXTRA_URL, $EXTRA_DATE, etc.
cal_dateINT Y0 only used for $EXTRA_DATE type fields (in YYYYMMDD format)
cal_remindINT Y0 how many minutes before event should a reminder be sent
cal_dataTEXT Y  used to store text data


webcal_user

Defines a WebCalendar user.

Column NameTypeLengthNullDefaultDescription
cal_loginVARCHAR25N  the unique user login
cal_passwdVARCHAR32Y  the user's password. (not used for http or ldap authentication)
cal_lastnameVARCHAR25Y  user's last name
cal_firstnameVARCHAR25Y  user's first name
cal_is_adminCHAR1Y'N' is the user a WebCalendar administrator ('Y' = yes, 'N' = no)
cal_emailVARCHAR75Y  user's email address


webcal_user_layers

Define layers for a user.

Column NameTypeLengthNullDefaultDescription
cal_layeridINT N0 unique layer id
cal_loginVARCHAR25N  login of owner of this layer
cal_layeruserVARCHAR25N  login name of user that this layer represents
cal_colorVARCHAR25Y  color to display this layer in
cal_dupsCHAR1Y'N' show duplicates ('N' or 'Y')


webcal_user_pref

Specify preferences for a user. Most preferences are set via pref.php. Values in this table are loaded after system settings found in webcal_config.

Column NameTypeLengthNullDefaultDescription
cal_loginVARCHAR25N  user login
cal_settingVARCHAR25N  setting name
cal_valueVARCHAR100Y  setting value


webcal_view

A "view" allows a user to put the calendars of multiple users all on one page. A "view" is valid only for the owner (cal_owner) of the view. Users for the view are in webcal_view_user.

Column NameTypeLengthNullDefaultDescription
cal_view_idINT N  unique view id
cal_ownerVARCHAR25N  login name of owner of this view
cal_nameVARCHAR50N  name of view
cal_view_typeCHAR1Y  "W" for week view, "D" for day view, "M" for month view
cal_is_globalCHAR1N'N' is this a global view (can it be accessed by other users) ('Y' or 'N')


webcal_view_user

Specify users in a view. See webcal_view.

Column NameTypeLengthNullDefaultDescription
cal_view_idINT N  view id
cal_loginVARCHAR25N  a user in the view