User Tools

Site Tools


nupusi:webcalendar:database

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.14 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.

webcal_asst

Define assitant/boss relationship.

Column Name Type Length Null Default Description
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 Name Type Length Null Default Description
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 Name Type Length Null Default Description
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:

  • webcal_entry_user - lists participants in the event and specifies the status (accepted, rejected) and category of each participant.
  • webcal_entry_repeats - contains information if the event repeats.
  • webcal_entry_repeats_not - specifies which dates the repeating event does not repeat (because they were deleted or modified for just that date by the user)
  • webcal_entry_log - provides a history of changes to this event.
  • webcal_site_extras - stores event data as defined in site_extras.php (such as reminders and other custom event fields).
Column Name Type Length Null Default Description
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 Y2event 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 Name Type Length Null Default Description
cal_idINT N0event 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 Name Type Length Null Default Description
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 Name Type Length Null Default Description
cal_idINT N0event 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 Y1frequency 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 Name Type Length Null Default Description
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 Name Type Length Null Default Description
cal_idINT N0event id
cal_loginVARCHAR25N participant in the event
cal_statusCHAR1Y'A'status of event for this user:
* A=Accepted
* R=Rejected
* W=Waiting
cal_categoryINT YNULLcategory of the event for this user

webcal_group

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

Column Name Type Length Null Default Description
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 Name Type Length Null Default Description
cal_group_idINT N group id
cal_loginVARCHAR25N user login

webcal_import

Used to track import data (one row per import)

Column Name Type Length Null Default Description
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 Name Type Length Null Default Description
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 Name Type Length Null Default Description
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 Name Type Length Null Default Description
cal_idINT N0event id
cal_nameVARCHAR25N extra type (see site_extras.php)
cal_event_dateINT N0the event date we are sending reminder for (in YYYYMMDD format)
cal_last_sentINT N0the date/time we last sent a reminder (in UNIX time format)

webcal_report

Defines a custom report created by a user.

Column Name Type Length Null Default Description
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:
00 = tomorrow
11 = today
22 = yesterday
33 = day before yesterday
1010 = next week
1111 = current week
1212 = last week
1313 = week before last
2020 = next week and week after
2121 = current week and next week
2222 = last week and this week
2323 = last two weeks
3030 = next month
3131 = current month
3232 = last month
3333 = month before last
4040 = next year
4141 = current year
4242 = last year
4343 = 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 Name Type Length Null Default Description
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 Name Type Length Null Default Description
cal_idINT N0event 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 Y0only used for $EXTRA_DATE type fields (in YYYYMMDD format)
cal_remindINT Y0how many minutes before event should a reminder be sent
cal_dataTEXT Y used to store text data

webcal_user

Defines a WebCalendar user.

Column Name Type Length Null Default Description
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 Name Type Length Null Default Description
cal_layeridINT N0unique 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 Name Type Length Null Default Description
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 Name Type Length Null Default Description
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 Name Type Length Null Default Description
cal_view_idINT N view id
cal_loginVARCHAR25N a user in the view
nupusi/webcalendar/database.txt · Last modified: 2008/12/06 13:56 (external edit)