WebCalendar Upgrading Notes

Important News:A major improvement beginning with Version 1.1 is the addition of an automated installation script. This script will guide you through the installation process and help identify any problem areas that might prevent successful installation or operation of WebCalendar.

• If upgrading, the script will attempt to determine your current installation version and bring your database up to date.
• If this is a new installation, the installation script will create your database and setup the required tables for you. It can then create a default Administrator account and add the basic configuration data to get you up and running.

This installation script was tested primarily using MySQL and Apache on a dedicated server. If using an ISP or a CPANEL installer, your mileage may vary. If problems arise, you can always follow the instruction below as in previous versions and setup your database manually. As with any upgrade, it's always a good idea to backup your data prior to installation.

Another major upgrade to WebCalendar for v1.1 is the implementation of full timezone support. In previous versions, all date/time data was stored based on server time and users set their 'time offset' relative to server time. Now, all date/time data will be stored in the database as Greenwich Mean Time (GMT) and users will be able to select a timezone based on their geographical location.

Having true timezone information available within WebCalendar enables the system to correct for Daylight Savings Time (DST) even if users are in different timezones. The database houses timezone information from 1970 to 2038 and can calculate the appropriate GMT offset required to allow users to see events in their true 'local time'.

The installation script will perform the initial import of the timezone data and guide you through the one-time conversion required to get your existing data converted to GMT.

Launch the Automatic Installation Script

Upgrading Steps

With the new 1.1 install wizard, you will no longer have to be troubled with uploading SQL files to phpMyAdmin or executing SQL commands yourself. Your database will be upgraded automatically from your current older WebCalendar installation. Follow the steps below to upgrade to WebCalendar 1.1 from an older version of WebCalendar.

1. Make a backup of your current WebCalendar database. This can be done a couple of different ways.
   • If you have access to phpMyAdmin, you can use the export function:
     • Startup phpMyAdmin
     • Select the database from the pulldown on the left under the label "Databases". (This will be the same database name used in your includes/settings.php file in your old WebCalendar install.)
     • Click on the "Export" tab.
     • It's best to use "SQL" for the format so it can be easily imported again.
     • Select "zipped" for compression. If you don't do this, you will just see the SQL in your browser window, and you will have to cut and paste this into a text editor to save it.
     • Click on the "Go" button at the bottom of the page.
   • If you have access to a MySQL command line (typically via shell access on a Linux server), you can use the mysqldump command:
       mysqldump -uUSERNAME -pPASSWORD DATABASE > dumpfile.sql
     Of course, replace USERNAME, PASSWORD and DATABASE from the values in your includes/settings.php file from your old WebCalendar install.
2. Make a backup of your current WebCalendar files on the server. You would typically do this with an FTP client (like ).
3. Install the new WebCalendar files in a new directory on your server. How you do this will depend on what type of access you have to your server. It is best to not overwrite your old WebCalendar install. The unpacked/unzipped files will create a directory with the current WebCalendar version name in it.
4. Optional: If you prefer to use a simple name (like "webcalendar" rather than "WebCalendar-1.1.6"), then you can rename the directory after you've installed the files. A good way to do this might be to rename your old webcalendar install to something like "webcalendar-oldinstall" and rename the new install to be the same name as your old one.
   Note: If you are planning on renaming the directory, it is best to do this before you proceed to the automated install.
5. Change the permissions the permissions of the includes directory. If you are doing this from FTP, change directories to the new webcalendar directory and use the following command:
     chmod 777 includes
6. Download a copy of your old includes/settings.php file from the old WebCalendar install and have it handy so you can enter the same values in your upgrade process.
7. You're now ready to start the install/upgrade wizard. Point your browser to the web server where you have installed the files. You only need to specify the webcalendar directory to get to the wizard. Because there is no includes/settings.php file in the new install, you will be redirected to the install/upgrade wizard.
8. Once the wizard is complete, it's a good idea to change your includes permissions back to what they were originally for better security.

Note: Due to large number of database types that WebCalendar can support, it would be impractical to list all the SQL variations here. All SQL listed is taken from the upgrade-mysq.sql file used during the automatic installation process. If you are using a database other then MySQL, you may want refer to the appropriate upgrade-xxxxx.sql file in the install/sql folder.

My previous install was...

• 1.1.0-CVS or 1.1.0a-CVS
• 1.0RC3 or 1.0.0
• 0.9.45, 1.0RC1 or 1.0RC2
• 0.9.44
• 0.9.43
• 0.9.42
• 0.9.41
• 0.9.40
• 0.9.39
• 0.9.38
• 0.9.37
• 0.9.35 - 0.9.36
• 0.9.27 - 0.9.34
• 0.9.22 - 0.9.26
• 0.9.14 - 0.9.21
• 0.9.12 - 0.9.13
• 0.9.07 - 0.9.11
• 0.9.01 - 0.9.06
• 0.9

To upgrade from v0.9

You need to create the table cal_user_pref in tables.sql. You need to create the table cal_entry_user in tables.sql that was mistakenly created as "cal_event_user" in the 0.9 release.

To upgrade from v0.9.01

Entirely new tables are used. Use the following commands to convert your existing MySQL tables to the new tables:

cd tools
./upgrade_to_0.9.7.pl
mysql intranet < commands.sql
    

where "intranet" is the name of the MySQL database that contains your WebCalendar tables.

To upgrade from v0.9.07 - v0.9.11

To fix a bug in the handing of events at midnight, all the entries with NULL for cal_time are changed to -1. Use the following SQL command:

UPDATE webcal_entry SET cal_time = -1 WHERE cal_time is null;

To upgrade from v0.9.12 or v0.9.13

A new table was added to support repeating events. Use the following SQL command:

CREATE TABLE webcal_entry_repeats (
  cal_id INT DEFAULT '0' NOT NULL,
  cal_days CHAR(7),
  cal_end INT,
  cal_frequency INT DEFAULT '1',
  cal_type VARCHAR(20),
  PRIMARY KEY (cal_id)
);
    

To upgrade from v0.9.14 - v0.9.21

A new table was added to support layering. For MySQL, the SQL is:

CREATE TABLE webcal_user_layers (
  cal_login varchar(25) NOT NULL,
  cal_layeruser varchar(25) NOT NULL,
  cal_color varchar(25) NULL,
  cal_dups CHAR(1) DEFAULT 'N',
  cal_layerid INT DEFAULT '0' NOT NULL,
  PRIMARY KEY ( cal_login, cal_layeruser )
);
    

To upgrade from v0.9.22 - v0.9.26

Two new tables were added for custom event fields and reminders. For MySQL the SQL is:

CREATE TABLE webcal_site_extras (
  cal_id INT DEFAULT '0' NOT NULL,
  cal_name VARCHAR(25) NOT NULL,
  cal_type INT NOT NULL,
  cal_date INT DEFAULT '0',
  cal_remind INT DEFAULT '0',
  cal_data TEXT,
  PRIMARY KEY ( cal_id, cal_name, cal_type )
);
CREATE TABLE webcal_reminder_log (
  cal_id INT DEFAULT '0' NOT NULL,
  cal_name VARCHAR(25) NOT NULL,
  cal_event_date INT NOT NULL DEFAULT 0,
  cal_last_sent INT NOT NULL DEFAULT 0,
  PRIMARY KEY ( cal_id, cal_name, cal_event_date )
);
    

You will also need to setup the tools/send_reminders.php script to be run periodically. I would recommend once an hour. For Linux/UNIX, this is simple. Just use cron and add a line to your crontab file that looks like:

1 * * * * cd /some/directory/webcalendar/tools; ./send_reminders.php

This will tell cron to run the script at one minute after the hour. Windows users will have to find another way to run the script. There are ports/look-a-likes of cron for Windows, so look around.

To upgrade from v0.9.27 - v0.9.34

Six new tables were added for group support, views, system settings and activity logs. For MySQL the SQL is:

CREATE TABLE webcal_group (
  cal_group_id INT NOT NULL,
  cal_last_update INT NOT NULL,
  cal_name VARCHAR(50) NOT NULL,
  cal_owner VARCHAR(25) NULL,
  PRIMARY KEY ( cal_group_id )
);
CREATE TABLE webcal_group_user (
  cal_group_id INT NOT NULL,
  cal_login VARCHAR(25) NOT NULL,
  PRIMARY KEY ( cal_group_id, cal_login )
);
CREATE TABLE webcal_view (
  cal_view_id INT NOT NULL,
  cal_name VARCHAR(50) NOT NULL,
  cal_owner VARCHAR(25) NOT NULL,
  cal_view_type CHAR(1),
  PRIMARY KEY ( cal_view_id )
);
CREATE TABLE webcal_view_user (
  cal_view_id INT NOT NULL,
  cal_login VARCHAR(25) NOT NULL,
  PRIMARY KEY ( cal_view_id, cal_login )
);
CREATE TABLE webcal_config (
  cal_setting VARCHAR(50) NOT NULL,
  cal_value VARCHAR(50) NULL,
  PRIMARY KEY ( cal_setting )
);
CREATE TABLE webcal_entry_log (
  cal_log_id INT NOT NULL,
  cal_date INT NOT NULL,
  cal_entry_id INT NOT NULL,
  cal_login VARCHAR(25) NOT NULL,
  cal_time INT NULL,
  cal_type CHAR(1) NOT NULL,
  cal_text TEXT,
  PRIMARY KEY ( cal_log_id )
);
    

To upgrade from v0.9.35 or v0.9.36

The webcal_entry_log table was modified, and a new table webcal_entry_repeats_not was created. Use the following SQL for MySQL:

ALTER TABLE webcal_entry_log ADD cal_user_cal VARCHAR(25);
CREATE TABLE webcal_entry_repeats_not (
  cal_id INT NOT NULL,
  cal_date INT NOT NULL,
  PRIMARY KEY ( cal_id, cal_date )
);
    

To upgrade from v0.9.37

The webcal_entry_user table was modified, and a new table webcal_categories was created. Use the following SQL for MySQL:

ALTER TABLE webcal_entry_user ADD cal_category INT DEFAULT NULL;
CREATE TABLE webcal_categories (
  cat_id INT NOT NULL,
  cat_name VARCHAR(80) NOT NULL,
  cat_owner VARCHAR(25),
  PRIMARY KEY ( cat_id )
);
    

To upgrade from v0.9.38

The names of the date settings in the database were modified. All old data settings need to be removed from the database.

DELETE FROM webcal_config WHERE cal_setting LIKE 'DATE_FORMAT%';
DELETE FROM webcal_user_pref WHERE cal_setting LIKE 'DATE_FORMAT%';
    

To upgrade from v0.9.39

Two new tables were created: webcal_asst and webcal_entry_ext_user. And the column cal_ext_for_id was added to the webcal_entry table. Use the following SQL for MySQL:

CREATE TABLE webcal_asst (
  cal_boss VARCHAR(25) NOT NULL,
  cal_assistant VARCHAR(25) NOT NULL,
  PRIMARY KEY ( cal_boss, cal_assistant )
);
CREATE TABLE webcal_entry_ext_user (
  cal_id INT DEFAULT 0 NOT NULL,
  cal_fullname VARCHAR(50) NOT NULL,
  cal_email VARCHAR(75) NULL,
  PRIMARY KEY ( cal_id, cal_fullname )
);
ALTER TABLE webcal_entry ADD cal_ext_for_id INT NULL;
    

To upgrade from v0.9.40

One new table was added: webcal_nonuser_cals. Use the following SQL for MySQL:

CREATE TABLE webcal_nonuser_cals (
  cal_login VARCHAR(25) NOT NULL,
  cal_admin VARCHAR(25) NOT NULL,
  cal_firstname VARCHAR(25),
  cal_lastname VARCHAR(25),
  PRIMARY KEY ( cal_login )
);
    

To upgrade from v0.9.41

Three new tables were added: webcal_report, webcal_report_template, and webcal_import_data. Use the following SQL for MySQL:

CREATE TABLE webcal_report (
  cal_report_id INT NOT NULL,
  cal_allow_nav CHAR(1) DEFAULT 'Y',
  cal_cat_id INT NULL,
  cal_include_empty CHAR(1) DEFAULT 'N',
  cal_include_header CHAR(1) DEFAULT 'Y' NOT NULL,
  cal_is_global CHAR(1) DEFAULT 'N' NOT NULL,
  cal_login VARCHAR(25) NOT NULL,
  cal_report_name VARCHAR(50) NOT NULL,
  cal_report_type VARCHAR(20) NOT NULL,
  cal_show_in_trailer CHAR(1) DEFAULT 'N',
  cal_time_range INT NOT NULL,
  cal_update_date INT NOT NULL,
  cal_user VARCHAR(25) NULL,
  PRIMARY KEY ( cal_report_id )
);
CREATE TABLE webcal_report_template (
  cal_report_id INT NOT NULL,
  cal_template_type CHAR(1) NOT NULL,
  cal_template_text TEXT,
  PRIMARY KEY ( cal_report_id, cal_template_type )
);
CREATE TABLE webcal_import_data (
  cal_id int NOT NULL,
  cal_login VARCHAR(25) NOT NULL,
  cal_external_id VARCHAR(200) NULL,
  cal_import_type VARCHAR(15) NOT NULL,
  PRIMARY KEY  ( cal_id, cal_login )
);
    

To upgrade from v0.9.42

User passwords are now stored using md5 and require the webcal_user table to be altered to accommodate larger password data. Use the following SQL for MySQL:

ALTER TABLE webcal_user MODIFY cal_passwd VARCHAR(32) NULL;
DROP TABLE webcal_import_data;
CREATE TABLE webcal_import (
  cal_import_id INT NOT NULL,
  cal_date INT NOT NULL,
  cal_login VARCHAR(25) NULL,
  cal_name VARCHAR(50) NULL,
  cal_type VARCHAR(10) NOT NULL,
  PRIMARY KEY ( cal_import_id )
);
CREATE TABLE webcal_import_data (
  cal_id INT NOT NULL,
  cal_login VARCHAR(25) NOT NULL,
  cal_external_id VARCHAR(200) NULL,
  cal_import_id INT NOT NULL,
  cal_import_type VARCHAR(15) NOT NULL,
  PRIMARY KEY  ( cal_id, cal_login )
);
    

Next, you will need to run the script found in the tools subdirectory. This will convert all your passwords from plain text to md5. You can run this from the command line (if you have a standalone version of PHP compiled):

cd tools
php convert_passwords.php
    

Or, if you do not have a standalone version of PHP, you can just type in the URL to access the script in your browser:

http://yourcalendarurl/tools/convert_passwords.php

You may safely delete the file /tools/convert_passwords.php after successfully performing this step.

Delete all webcalendar_login browser cookies. Details should be available on your local browser help section.

To upgrade from v0.9.43 - v1.0RC2

The webcal_view table was modified. Execute the following SQL to update your database:

UPDATE webcal_config SET cal_value = 'week.php' WHERE cal_setting = 'STARTVIEW';
UPDATE webcal_user_pref SET cal_value = 'day.php'
  WHERE cal_value = 'day' AND cal_setting = 'STARTVIEW';
UPDATE webcal_user_pref SET cal_value = 'month.php'
  WHERE cal_value = 'month' AND cal_setting = 'STARTVIEW';
UPDATE webcal_user_pref SET cal_value = 'week.php'
  WHERE cal_value = 'week' AND cal_setting = 'STARTVIEW';
UPDATE webcal_user_pref SET cal_value = 'year.php'
  WHERE cal_value = 'year' AND cal_setting = 'STARTVIEW';
ALTER TABLE webcal_view ADD cal_is_global CHAR(1) NOT NULL DEFAULT 'N';
UPDATE webcal_view SET cal_is_global = 'N';
    

To upgrade from v1.0RC3 - v1.0.0

Two new tables need to be created to support advanced user access control. One new table is needed to store custom user header/footer template information. Execute the following SQL to update your database:

CREATE TABLE webcal_access_user (
  cal_login VARCHAR(25) NOT NULL,
  cal_other_user VARCHAR(25) NOT NULL,
  cal_can_approve CHAR(1) NOT NULL DEFAULT 'N',
  cal_can_delete CHAR(1) NOT NULL DEFAULT 'N',
  cal_can_edit CHAR(1) NOT NULL DEFAULT 'N',
  cal_can_view CHAR(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY ( cal_login, cal_other_user )
);
CREATE TABLE webcal_access_function (
  cal_login VARCHAR(25) NOT NULL,
  cal_permissions VARCHAR(64) NOT NULL,
  PRIMARY KEY ( cal_login )
);
ALTER TABLE webcal_nonuser_cals ADD cal_is_public CHAR(1) NOT NULL DEFAULT 'N';
CREATE TABLE webcal_user_template (
  cal_login VARCHAR(25) NOT NULL,
  cal_type CHAR(1) NOT NULL,
  cal_template_text TEXT,
  PRIMARY KEY ( cal_login, cal_type )
);
    

To upgrade from v1.1.0-CVS or v1.1.0a-CVS

A new table is needed to support multiple categories. In addition, several new columns have been added to webcal_entry and one column added to webcal_entry_user to support VTODO tasks, and to webcal_repeats to support the much improved ical support. A new column was added to webcal_entry_repeats_not to differentiate between exclusion and inclusions. Use the following SQL to update your MySQL database:

ALTER TABLE webcal_entry ADD cal_due_date int(11) default NULL;
ALTER TABLE webcal_entry ADD cal_due_time int(11) default NULL;
ALTER TABLE webcal_entry ADD cal_location varchar(50) default NULL;
ALTER TABLE webcal_entry ADD cal_url varchar(100) default NULL;
ALTER TABLE webcal_entry ADD cal_completed int(11) default NULL;
ALTER TABLE webcal_entry_repeats ADD cal_endtime int(11) default NULL;
ALTER TABLE webcal_entry_repeats ADD cal_byday varchar(100) default NULL;
ALTER TABLE webcal_entry_repeats ADD cal_bymonth varchar(50) default NULL;
ALTER TABLE webcal_entry_repeats ADD cal_bymonthday varchar(100) default NULL;
ALTER TABLE webcal_entry_repeats ADD cal_bysetpos varchar(50) default NULL;
ALTER TABLE webcal_entry_repeats ADD cal_byweekno varchar(50) default NULL;
ALTER TABLE webcal_entry_repeats ADD cal_byyearday varchar(50) default NULL;
ALTER TABLE webcal_entry_repeats ADD cal_count int(11) default NULL;
ALTER TABLE webcal_entry_repeats ADD cal_wkst char(2) default 'MO';
ALTER TABLE webcal_entry_repeats_not ADD cal_exdate int(1) NOT NULL DEFAULT '1';
ALTER TABLE webcal_entry_user ADD cal_percent int(11) NOT NULL DEFAULT '0';
CREATE TABLE webcal_entry_categories (
  cal_id int(11) NOT NULL default '0',
  cat_id int(11) NOT NULL default '0',
  cat_order int(11) NOT NULL default '0',
  cat_owner varchar(25) default NULL
);
    

After you complete manually updating your database, you will still need to run the installation script to perform any necessary data changes needed to convert existing data.