Calendar Manager Documentation

Calendar Manager Overview

The Calendar Manager was designed to schedule events in the CSE conference rooms. It is also capable of managing more general events.

The web interface allows users to view monthly schedules and daily schedules. Appointments can be added and deleted. Recurring appointments can be added. Appointments can be weekly, bi-weekly, or monthly. They can also be scheduled to occur on the same week of the month. Appointments can be scheduled for an entire semester.

By default, appointments are not allowed to overlap. If a user attempts to add an appointment that conflicts with another, an error message is displayed. If a recurring appointment conflicts with an appointment in the future, the appointments up until the conflict are added and the user is warned of the conflict.

When deleting a recurring appointment, users have the option to delete a single instance of the appointment, or all remaining instances of the appointment.

The web interface has a view only mode that permits people to view schedules but not modify them in any way.

Calendars can be made secure by means of htaccess. In a secured calendar there are four different user roles. "Viewers" can view the schedules. "Users" can add appointments and delete appointments they have added. "Owners" can add appointments and delete any appointments. "Administrators" can add users to the calendar, change passwords, and change user roles.

In a secured calendar, "Administrators" and "Owners" can add private appointments. The description of a private appointment can only be seen by other "Administrators" and "Owners".

Directory Structure and Web Security

All of the files associated with the Calendar Manager are in /user/web/htdocs/calendar.

The main components are the cgi scripts in /calendar/cgi-bin. The base contents of this directory are:

-rwsr-sr-x   1 calend44 nobody      7329 Dec 15 11:56 caladmin2.1
-rwxr-xr-x   1 calend44 manager     2012 Dec 14 17:46 calendarindex
-rw-------   1 calend44 manager        9 Oct 20 17:17 calpasswd
-rwxr-xr-x   1 calend44 manager     9114 Dec 15 18:15 mysqlCal.pm
-rwsr-sr-x   1 calend44 manager     7779 Nov 22 14:23 updatecal2.1
lrwxrwxrwx   1 root     other          9 Nov 22 13:48 viewcal2.1 -> webcal2.1
-rwsr-xr-x   1 calend44 manager    21598 Dec 15 13:35 webcal2.1

caladmin2.1, updatecal2.1 and webcal2.1 are the main cgi scripts. mysqlCal.pm is a perl module that handles the database interactions. calendarindex generates the list of calendars. These are all described below.

Each calendar has its own subdirectory in /calendar/cgi-bin, e.g. /calendar/cgi-bin/eb3105/. The contents of this directory are the following for all calendars:

lrwxrwxrwx   1 root     other         14 Nov 22 14:44 caladmin2.1 -> ../caladmin2.1
-rw-r--r--   1 calend44 nobody     44752 Dec 16 08:23 log
lrwxrwxrwx   1 root     other         15 Nov 22 14:44 updatecal2.1 -> ../updatecal2.1
lrwxrwxrwx   1 root     other         12 Nov 22 14:44 webcal2.1 -> ../webcal2.1

/calendar/cgi-bin/webcal2.1 is never invoked directly. Instead a specific calendar script /calendar/cgi-bin/calendar_name/webcal2.1 is referenced.

log is a log file of modifications to the calendar. It indicates who made what changes when.

There are two additional files that appear in a calendar's directory. .name is the name of the calendar. It is used by calendarindex when displaying the available calendars. The existence of a .view file indicates that a calendar is world viewable.

Security

The Calendar Manager uses .htaccess to provide secure access to calendars. Each calendar can have a .htaccess and .htpasswd in its cgi-bin subdirectory. This will prevent anyone from running the scripts unless they have a valid username and password. The standard .htaccess file looks like:

AuthUserFile /user/web/htdocs/calendar/cgi-bin/eb3105/.htpasswd
AuthGroupFile /dev/null
AuthName "Password Authentication to eb3105 Calendar"
AuthType Basic
require valid-user

The htaccess protocol sets the CGI environment variable REMOTE_USER. This is used by webcal2.1 and other scripts to determine who is using the calendar and what privileges they have.

The main cgi scripts all run setuid as user calend44. This is necessary to access the mysql password, which is only readable by calend44. This is to prevent other unauthorized scripts from using the mysqlCal.pm module to read and modify the calendars.

Aesthetics

For aesthetic reasons, it is nice to have url's of the form http://www.cse.msu.edu/calendar/cal work. This url resolves to the calendar cal.

Due to strange quirks in apache when handling htaccess security, accomplishing this more difficult. To make this work the following had to be done. This line was added to /user/web/apache/etc/srm.conf

ScriptAliasMatch /calendar/(.+)/ /user/web/htdocs/calendar/cgi-bin/$1/webcal2.1

Also, for each calendar cal the directory /user/web/htdocs/calendar/cal must exist.

Ideally, to avoid using ScriptAliasMatch, each calendar should have a directory and a index.html file containing the following:

<!--#include virtual="/calendar/cgi-bin/cal/webcal2.1"-->

Unfortunately server side includes and htaccess do not work well together. If apache ever fixes this problem, the ScriptAliasMatch line could be removed.

Calendar Manager CGI Code

The Calendar Manager consists of three perl scripts and one perl module. The scripts webcal2.1,updatecal2.1 and caladmin2.1. All the scripts can be found in /user/web/htdocs/calendar/cgi-bin/.

webcal2.1

webcal2.1 generates the web interface. It produces html code that displays the appointment information and the forms used to modify the information. The html generated includes references to webcal2.1 and small amounts of javascript.

This is a high level description of how webcal2.1 works. For all the details, see the code itself. Parameters are passed to webcal2.1 in a slightly nonstandard way. The parameters are sent as a single string separated by ?'s. The cgi reference looks like

webcal2.1?mode?param1?param2?param3

The mode parameter indicates which sort of calendar to display. The other parameters indicate the calendar, time and user. The modes are:

webcal2.1?month?month#?year?day_of_month

webcal2.1?day?date?day_of_week

webcal2.1?dayframe?date?day_of_week

webcal2.1?addframe?date?day_of_week

webcal2.1?delete?date?day_of_week

webcal2.1?deleteframe?date?day_of_week

webcal2.1?update?date?day_of_week

webcal2.1?updateframe?date?day_of_week

webcal2.1?blank

The month mode displays a full month of appointments using a table. The day mode creates a frame document. The top frame is a reference to webcal2.1?dayframe and the bottom frame is a reference to webcal2.1?addframe. The dayframe mode displays all the appointments for a given day. The addframe mode displays a form used to add appointments to the calendar. The delete mode creates a frame document with references to webcal2.1?deleteframe and webcal2.1?blank. The deleteframe mode displays form used to delete appointments. The blank mode displays a blank page. The update mode is similar to the delete mode except that is generates forms to update appointments.

webcal2.1 uses the CGI environment variable SCRIPT_NAME to determine which calendar to access.

viewcal2.1 is simply a link to webcal2.1. viewcal2.1 takes one additional argument: viewcal2.1?calendarname. The additional argument indicates which calendar to access. Unlike webcal2.1 viewcal2.1 is referenced directly. viewcal2.1?calendarname will only display a calendar if /calendar/cgi-bin/calendarname/.view exists.

updatecal2.1

updatecal2.1 updates the calendars. Most of the real work is done by mysqlCal.pm. updatecal2.1 handles insertions, deletions, and modifications. All parameters are passed using the standard CGI mechanism. updatecal2.1 produces html output indicating if the updates were successful or not.

caladmin2.1

caladmin2.1 produces the forms used to modify user permissions, user passwords, etc, and the script also executes the operations.

User information is stored in two locations. The first is in the .htaccess file. caladmin2.1 uses this file to generate the list of users and it modifies this file when users are added, deleted, or there password is changed. The only information not in the .htaccess file is the user roles information. This is stored in the database and is accessed via mysqlCal.pm.

`mysqlCal.pm`

This is the perl module that manages all of the database access. Most of the actual calendar functionality is in mysqlCal.pm.

The following creates a calendar object.

$cal=new mysqlCal($calendarname,$user,$dbuser,$passwd);

$calendarname is the name of the calendar. $user is the calendar user's name. The Calendar Manager uses REMOTE_USER for this value. $dbuser is the name used to connect to the database and $passwd is the password. If these are not supplied, the default user calendar will be used and the password will be read from the file /user/web/htdocs/calendar/cgi-bin/calpasswd. In order for this to work the script must be running as user calend44.

Multiple calendars can be viewed by sending "name1:name2:name3" as the calendar name. This feature has not been tested much.

Appointments can be retrieved as follows:

$n=$cal->get_appointments_tie($date,$cond); while ($appt=$n->{NEXT}) { # process $appt }

get_appointments_tie returns a tied reference. $date is a string in the form 'yyyy-mm-dd'. If the date is of the form '>yyyy-mm-dd' or '<yyyy-mm-dd' then all appointments before or after that date retreived. If the date field is blank, all appointments are retreived. $cond is an additional condition. It should be in the form of a legal sql expression. For example, "tag='misc'".

In the above example, $appt is a reference with the following fields:

APPTID       the appointment id number.
DATE         the appointment date.
WHAT         the appointment description.
SHORT        the short description.
LOCATION     the appointment location.
PERIOD       the period of the appointment (weekly, e.g.). 
USER         the user who added the appointment.
SERIESID     the id of the series to which teh appointment belongs.
TAG          the appointment tag (or type).
URL          the url field.
PRIVATE      is the appointment private?
CENSORED     has the appointment been censored?
START        the start time, in military time (1700).
STARTTIME    the start time, in wall clock time (5:00pm).
STARTHOUR    the start hour (17).
END          the end time, in military time.
ENDTIME      the end time, in wall clock time. 
ENDHOUR      the end hourt

get_appointment can be used to retreive this information about a single appointment. get_appointment accepts an appointment id as an argument. Appointments are added with add_appointment.

$result=add_appointment(FIELD1 => 'value1', FIELD2 => 'value2', etc.) Legal fields are the following: DATE, START, END, WHAT, SHORT, LOCATION, TAG, URL, PERIOD, USER, SERIESID, PRIVATE.

update_appointment is similar to add_appointment, except the first argument is the appointment id.

delete_appointment($apptid,$num) deletes an appointment. $num indicates how repeating appointments should be deleted. If $num equals 'all', then all occurences of a repeating appointment after the specified appointment will be deleted. Otherwise, only the specified appointment will be deleted. get_permissions($user) and set_permissions($user,$level) are used to manage user permissions in the calendar. $level is a number describing a users role. The roles are 0 (Administrator), 1 (Owner), 2 (User), 3 (Viewer).

Helper Scripts

/cgi-bin/calendarindex generates the list of available calendars. All calendars with a subdirectory in /cgi-bin will be listed unless there is a file named .test in the subdirectory.

/bin/newcal creates a new calendar. It is an interactive program and should be self explanatory.

Database Description

The Calendar Manager uses a mysql database to store information about calendars and appointments. The database name is calendar. The database consists of the following tables:

calendars
This table contains the list of available calendars.

mysql> show columns from calendars;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| calendarid | int(11)     |      |     | 0       |       |
| name       | varchar(32) | YES  |     | NULL    |       |
| exclusive  | int(11)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

Each calendar has a name and a unique id, The exclusive column indicates if the calendar allows overlapping appointments. The Calendar Manager was originally designed to prevent appointments from overlapping. Setting exclusive to a non-zero value enforces this restriction.

appts
This table contains the appointments information.

mysql> show columns from appts;
+------------+--------------+------+-----+------------+-------+
| Field      | Type         | Null | Key | Default    | Extra |
+------------+--------------+------+-----+------------+-------+
| calendarid | int(11)      |      | MUL | 0          |       |
| appid      | int(11)      |      |     | 0          |       |
| day        | date         |      |     | 0000-00-00 |       |
| start      | time         |      |     | 00:00:00   |       |
| end        | time         | YES  |     | NULL       |       |
| seriesid   | int(11)      | YES  |     | NULL       |       |
| user       | varchar(16)  | YES  |     | NULL       |       |
| period     | char(1)      | YES  |     | NULL       |       |
| what       | varchar(255) | YES  |     | NULL       |       |
| short      | varchar(32)  | YES  |     | NULL       |       |
| location   | varchar(64)  | YES  |     | NULL       |       |
| tag        | varchar(32)  | YES  |     | NULL       |       |
| url        | varchar(255) | YES  |     | NULL       |       |
| private    | char(1)      | YES  |     | NULL       |       |
+------------+--------------+------+-----+------------+-------+

calendarid indicates to which calendar an appointment belongs. Each appointment has an appid that is unique for that calendar. (calendarid,appid) forms a key for this table. The day,start and end columns indicate when the appointment is.

Appointments can belong to series. Each series has an id unique to the calendar, and a period. Each period has a single character code. The periods are Once (1), Weekly (w), Bi-weekly (b), Monthly (m), and Same Week of Month (M).

user indicates which user added the appointment. The Calendar Manager uses the username supplied via htaccess for this column.

what,short,location,tag and url all describe the appointment. The Calendar Manager really only uses what. The other columns were added for other possible applications to use. Because the Calendar Manager was designed to schedule appointments for a given room, the location is usually implied. However the location will be displayed if it is not null.

private indicates a private appointments. The what field of private appointments are only displayed to the owner of the calendar. The what field is also mildly encrypted in the database to prevent casual display of the information.

To speed up the most common queries, the following index exists:

mysql> show index from appts;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+
| appts |          1 | day_index |            1 | calendarid  | A         |        NULL |     NULL |
| appts |          1 | day_index |            2 | day         | A         |        NULL |     NULL |
| appts |          1 | day_index |            3 | start       | A         |        NULL |     NULL |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+

permissions
This table indicates which users have special priviliges on which calendars.

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| calendarid  | int(11)     |      |     | 0       |       |
| user        | varchar(32) |      |     |         |       |
| permissions | int(11)     | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

The Calendar Manager allows different users to have different permissions. The user is determined by the htaccess login. The permissions columns determines the role of the user. The types are 0 (Administrator), 1 (Owner), 2 (User), 3 (Viewer). See Calendar Administration for details about user roles.

The mysql interface can be used to fix strange problems that the web interface is not flexible enough to handle.

To Do

Here are some things that would be nice additions to the calendar manager:

An easy way to clean out old appointments. This can be done easily enough with the mysql interface. However, something could be added to the administration code to clean out old appointments.
An easy way to jump to specific dates.