WebCalendar Database Documentation

http://www.k5n.us/webcalendar.php

Craig Knudsen, Craig@k5n.us

Unknown Version,   $Id$

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_access_function

Specifies what WebCalendar functions a user can access. Each function has a corresponding numeric value (specified in the file includes/access.php). For example, view event is 0, so the very first character in the cal_permissions column is either a "Y" if this user can view events or a "N" if they cannot.

Column Name Type Length Null Default Description
cal_login VARCHAR 25 N   user login
cal_permissions VARCHAR 64 N   a string of 'Y' or 'N' for the various functions


webcal_access_user

Specifies which users can access another user's calendar.

Column Name Type Length Null Default Description
cal_login VARCHAR 25 N   the current user who is attempting to look at another user's calendar
cal_other_user VARCHAR 25 N   the login of the other user whose calendar the current user wants to access
cal_can_view INT   N '0' can current user view events on the other user's calendar?
cal_can_edit INT   N '0' can current user edit events on the other user's calendar?
cal_can_approve INT   N '0' can current user approve events on the other user's calendar?
cal_can_invite CHAR 1 Y 'Y' can current user see other user in Participant lists?
cal_can_email CHAR 1 Y 'Y' can current user send emails to other user?
cal_see_time_only CHAR 1 Y 'N' can current user can only see time of other user?


webcal_asst

Define assitant/boss relationship.

Column Name Type Length Null Default Description
cal_boss VARCHAR 25 N   user login of boss
cal_assistant VARCHAR 25 N   user login of assistant


webcal_blob

This table stores event attachments and comments.

Column Name Type Length Null Default Description
cal_blob_id INT   N   Unique identifier for this object
cal_id INT   Y   event id (if applicable)
cal_login VARCHAR 25 Y   login of user who created
cal_name VARCHAR 30 Y   filename of object (not used for comments)
cal_description VARCHAR 128 Y   description of what the object is (subject for comment)
cal_size INT   Y   size of object (not used for comment)
cal_mime_type VARCHAR 50 Y   MIME type of object (as specified by browser during upload) (not used for comment)
cal_type CHAR 1 N   type of object: C=Comment, A=Attachment
cal_mod_date INT   N   date added (in YYYYMMDD format)
cal_mod_time INT   N   time added in HHMMSS format
cal_blob LONGBLOB   Y   binary data for object


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 Name Type Length Null Default Description
cat_id INT   N   unique category id
cat_owner VARCHAR 25 Y   user login of category owner. If this is NULL, then it is a global category
cat_name VARCHAR 80 N   category name
cat_color VARCHAR 8 Y   RGB color for category


webcal_config

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

Column Name Type Length Null Default Description
cal_setting VARCHAR 50 N   setting name
cal_value VARCHAR 100 Y   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 Name Type Length Null Default Description
cal_id INT   N   cal_id is unique integer id for event
cal_group_id INT   Y   cal_group_id: the parent event id if this event is overriding an occurrence of a repeating event
cal_ext_for_id INT   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_by VARCHAR 25 N   user login of user that created the event
cal_date INT   N   date of event (in YYYYMMDD format)
cal_time INT   Y   event time (in HHMMSS format)
cal_mod_date INT   Y   date the event was last modified (in YYYYMMDD format)
cal_mod_time INT   Y   time the event was last modified (in HHMMSS format)
cal_duration INT   N   duration of event in minutes
cal_due_date INT   Y NULL Task due date
cal_due_time INT   Y NULL Task due time
cal_priority INT   Y 5 event priority: 1=Low, 2=Med, 3=High
cal_type CHAR 1 Y 'E' 'E' = Event, 'M' = Repeating event, 'T' = Task
cal_access CHAR 1 Y 'P' 'P' = Public, 'R' = Private (others cannot see the event), 'C' = Confidential (others can see time allocated but not what it is)
cal_name VARCHAR 80 N   brief description of event
cal_location varchar 100 Y NULL location of event
cal_url varchar 100 Y NULL URL of event
cal_completed INT   Y NULL date task completed
cal_description TEXT   Y   full description of event


webcal_entry_categories

Contains category foreign keys to enable multiple categories for each event or task

Column Name Type Length Null Default Description
cal_id INT   N 0 id of event. Not unique
cat_id INT   N 0 id of category. Not unique
cat_order INT   N 0 order that user requests their categories appear. Globals are always last
cat_owner varchar 25 Y NULL user that owns this record. Global categories will be NULL


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 Name Type Length Null Default Description
cal_id INT   N 0 event id
cal_fullname VARCHAR 50 N   external user fill name
cal_email VARCHAR 75 Y   external user email (for sending a reminder)


webcal_entry_log

Activity log for an event.

Column Name Type Length Null Default Description
cal_log_id INT   N   unique id of this log entry
cal_entry_id INT   N   event id
cal_login VARCHAR 25 N   user who performed this action
cal_user_cal VARCHAR 25 Y   user of calendar affected
cal_type CHAR 1 N   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_date INT   N   date in YYYYMMDD format
cal_time INT   Y   time in HHMMSS format
cal_text TEXT   Y   optional text


webcal_entry_repeats

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

Column Name Type Length Null Default Description
cal_id INT   N 0 event id
cal_type VARCHAR 20 Y   type of repeating:
  • daily - repeats daily
  • monthlyByDate - repeats on same day of the month
  • monthlyBySetPos - repeats based on position within other ByXXX values
  • monthlyByDay - repeats on specified weekday (2nd Monday, for example)
  • weekly - repeats every week
  • yearly - repeats on same date every year
cal_end INT   Y   end date for repeating event (in YYYYMMDD format)
cal_endtime INT   Y NULL
cal_frequency INT   Y 1 frequency of repeat: 1 = every, 2 = every other, 3 = every 3rd, etc.
cal_days CHAR 7 Y   NO LONGER USED. We'll leave it in for now
cal_bymonth varchar 50 Y NULL the following columns are values as specified in RFC2445
cal_bymonthday varchar 100 Y NULL
cal_byday varchar 100 Y NULL
cal_bysetpos varchar 50 Y NULL
cal_byweekno varchar 50 Y NULL
cal_byyearday varchar 50 Y NULL
cal_wkst char 2 Y 'MO'
cal_count INT   Y NULL


webcal_entry_repeats_not

This table specifies which dates in a repeating event have either been deleted, included, 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 Name Type Length Null Default Description
cal_id INT   N   event id of repeating event
cal_date INT   N   cal_date: date event should not repeat (in YYYYMMDD format)
cal_exdate int 1 N 1 indicates whether this record is an exclusion (1) or inclusion (0)


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 Name Type Length Null Default Description
cal_id INT   N 0 event id
cal_login VARCHAR 25 N   participant in the event
cal_status CHAR 1 Y 'A' status of event for this user:
  • A=Accepted
  • C=Completed
  • D=Deleted
  • P=In-Progress
  • R=Rejected/Declined
  • W=Waiting
cal_category INT   Y NULL category of the event for this user
cal_percent INT   N 0 Task percentage of completion for this user's task


webcal_group

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

Column Name Type Length Null Default Description
cal_group_id INT   N   unique group id
cal_owner VARCHAR 25 Y   user login of user that created this group
cal_name VARCHAR 50 N   name of the group
cal_last_update INT   N   date last updated (in YYYYMMDD format)


webcal_group_user

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

Column Name Type Length Null Default Description
cal_group_id INT   N   group id
cal_login VARCHAR 25 N   user login


webcal_ical_data

This table supports the CalDAV eTag attribute. When CalDAV wants to update a record, he will pass an "eTag" which is a hash of the data. We can't verify the eTag unless we have the original data, including fields that we don't care about, in the order that they arrived from CalDAV. When an event is edited locally, this record is deleted, so there is not chance of an eTag match.

Column Name Type Length Null Default Description
cal_id INT   N   event_id
cal_section INT   N   perhaps this is a multi-part iCal record. Describe which piece this is.
cal_ical varchar 1024 N   The ical record as received via CalDAV


webcal_import

Used to track import data (one row per import)

Column Name Type Length Null Default Description
cal_import_id INT   N   unique id for import
cal_name VARCHAR 50 Y   name of import (optional)
cal_date INT   N   date of import (YYYYMMDD format)
cal_type VARCHAR 10 N   type of import (ical, vcal, palm, outlookcsv)
cal_login VARCHAR 25 Y   user who performed the import


webcal_import_data

Used to track import data (one row per event)

Column Name Type Length Null Default Description
cal_import_id INT   N   import id (from webcal_import table)
cal_id INT   N   event id in WebCalendar
cal_login VARCHAR 25 N   user login
cal_import_type VARCHAR 15 N   type of import: 'palm', 'vcal', 'ical' or 'outlookcsv'
cal_external_id VARCHAR 200 Y   external id used in external calendar system (for example, UID in iCal)


webcal_nonuser_cals

Defines non-user calendars.

Column Name Type Length Null Default Description
cal_login VARCHAR 25 N   the unique id for the calendar
cal_lastname VARCHAR 25 Y   calendar's last name
cal_firstname VARCHAR 25 Y   calendar's first name
cal_admin VARCHAR 25 N   who is the calendar administrator
cal_is_public CHAR 1 N 'N' can this nonuser calendar be a public calendar (no login required)
cal_url VARCHAR 255 Y NULL url of the remote calendar


webcal_reminders

Stores information about reminders

Column Name Type Length Null Default Description
cal_id INT   N '0'
cal_date INT   N '0' timestamp that specifies send datetime. Use this or cal_offset, but not both
cal_offset INT   N '0' offset in minutes from the selected edge
cal_related CHAR 1 N 'S' S=Start, E=End. Specifies which edge of entry this reminder applies to
cal_before CHAR 1 N 'Y' specifies whether reminder is sent before or after selected edge
cal_last_sent INT   N '0' timestamp of last sent reminder
cal_repeats INT   N '0' number of times to repeat in addition to original occurance
cal_duration INT   N '0' time in ISO 8601 format that specifies time between repeated reminders
cal_times_sent INT   N '0' number of times this reminder has been sent
cal_action VARCHAR 12 N 'EMAIL' action as imported, may be used in the future


webcal_report

Defines a custom report created by a user.

Column Name Type Length Null Default Description
cal_login VARCHAR 25 N   creator of report
cal_report_id INT   N   unique id of this report
cal_is_global CHAR 1 N 'N' is this a global report (can it be accessed by other users) ('Y' or 'N')
cal_report_type VARCHAR 20 N   format of report (html, plain or csv)
cal_include_header CHAR 1 N 'Y' if cal_report_type is 'html', should the DEFAULT HTML header and trailer be included? ('Y' or 'N')
cal_report_name VARCHAR 50 N   name of the report
cal_time_range INT   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_user VARCHAR 25 Y   user calendar to display (NULL indicates current user)
cal_allow_nav CHAR 1 Y 'Y' allow user to navigate to different dates with next/previous ('Y' or 'N')
cal_cat_id INT   Y   category to filter on (optional)
cal_include_empty CHAR 1 Y 'N' include empty dates in report ('Y' or 'N')
cal_show_in_trailer CHAR 1 Y 'N' include a link for this report in the "Go to" section of the navigation in the page trailer ('Y' or 'N')
cal_update_date INT   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 Name Type Length Null Default Description
cal_report_id INT   N   report id (in webcal_report table)
cal_template_type CHAR 1 N   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_text TEXT   Y   text of template


webcal_site_extras

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

Column Name Type Length Null Default Description
cal_id INT   N 0 event id
cal_name VARCHAR 25 N   the brief name of this type (first field in $site_extra array)
cal_type INT   N   EXTRA_URL, EXTRA_DATE, etc.
cal_date INT   Y 0 only used for EXTRA_DATE type fields (in YYYYMMDD format)
cal_remind INT   Y 0 how many minutes before event should a reminder be sent
cal_data TEXT   Y   used to store text data


webcal_timezones



Column Name Type Length Null Default Description
tzid varchar 100 N '' Unique name of timezone, try to use Olsen naming conventions
dtstart varchar 25 Y NULL earliest date this timezone represents YYYYMMDDTHHMMSSZ format
dtend varchar 25 Y NULL last date this timezone represents YYYYMMDDTHHMMSSZ format
vtimezone text   Y   Complete VTIMEZONE text gleaned from imported ics files


webcal_user

Defines a WebCalendar user.

Column Name Type Length Null Default Description
cal_login VARCHAR 25 N   the unique user login
cal_passwd VARCHAR 32 Y   the user's password. (not used for http)
cal_lastname VARCHAR 25 Y   user's last name
cal_firstname VARCHAR 25 Y   user's first name
cal_is_admin CHAR 1 Y 'N' is the user a WebCalendar administrator ('Y' = yes, 'N' = no)
cal_email VARCHAR 75 Y   user's email address
cal_enabled CHAR 1 Y 'Y' allow admin to disable account ('Y' = yes, 'N' = no)
cal_telephone VARCHAR 50 Y   user's telephone
cal_address VARCHAR 75 Y   user's address
cal_title VARCHAR 75 Y   user's title
cal_birthday INT   Y   user's birthday
cal_last_login INT   Y   user's last log in date


webcal_user_layers

Define layers for a user.

Column Name Type Length Null Default Description
cal_layerid INT   N 0 unique layer id
cal_login VARCHAR 25 N   login of owner of this layer
cal_layeruser VARCHAR 25 N   login name of user that this layer represents
cal_color VARCHAR 25 Y   color to display this layer in
cal_dups CHAR 1 Y '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 Name Type Length Null Default Description
cal_login VARCHAR 25 N   user login
cal_setting VARCHAR 25 N   setting name
cal_value VARCHAR 100 Y   setting value


webcal_user_template

This table stores the custom header/stylesheet/trailer. If configured properly, each user (or nonuser cal) can have their own custom header/trailer.

Column Name Type Length Null Default Description
cal_login VARCHAR 25 N   user login (or nonuser cal name), the DEFAULT for all users is stored with the username '__system__'
cal_type CHAR 1 N   type ('H' = header, 'S' = stylesheet/script, 'T' = trailer)
cal_template_text TEXT   Y   text of template


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 Name Type Length Null Default Description
cal_view_id INT   N   unique view id
cal_owner VARCHAR 25 N   login name of owner of this view
cal_name VARCHAR 50 N   name of view
cal_view_type CHAR 1 Y   "W" for week view, "D" for day view, "M" for month view
cal_is_global CHAR 1 N '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 Name Type Length Null Default Description
cal_view_id INT   N   view id
cal_login VARCHAR 25 N   a user in the view