WebCalendar Upgrading Notes

Below are the steps needed to upgrade from a previous version. Select your current version from the list below. If you are more than one version behind (i.e. the current version is v0.9.45, and you're using 0.9.39), click the "next.." link at the end of each section to move to the next version. Always follow the versions in sequence.

I'm using..

• 0.9.45 or 1.0RC1
• 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.36 or 0.9.35
• 0.9.34 - 0.9.27
• 0.9.26 - 0.9.22
• 0.9.21 - 0.9.14
• 0.9.13 or 0.9.12
• 0.9.11 - 0.9.07
• 0.9.06 - 0.9.01
• 0.9

To upgrade from v0.9 to v0.9.01:

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 to v0.9.07:

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 v0.9.12:

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 to v0.9.14:

A new table was added to support repeating events. Look at tables-mysql.sql, tables-oracle.sql, or tables-postgres.sql. Execute the SQL for creating the webcal_entry_repeats. For MySQL, the SQL is:

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

To upgrade from v0.9.14 - v0.9.21 to v0.9.22:

A new table was added to support layering. Look at tables-mysql.sql, tables-oracle.sql, or tables-postgres.sql. Execute the SQL for creating the webcal_entry_repeats. For MySQL, the SQL is:

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

To upgrade from v0.9.22 - v0.9.26 to v0.9.27:

Two new tables were added for custom event fields and reminders. Look at tables-mysql.sql, tables-oracle.sql, or tables-postgres.sql. Execute the SQL for creating webcal_site_extras and webcal_reminder_log. For MySQL and PostgreSQL, 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 )
);

For Oracle, 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 LONG,
	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 to v0.9.35:

Six new tables were added for group support, views, system settings and activity logs. Look at tables-mysql.sql, tables-oracle.sql, or tables-postgres.sql for these new tables: webcal_group, webcal_group_user, webcal_view, webcal_view_user, wecbal_config, webcal_entry_log. After adding these tables, be sure to go to the System Settings page (admin.php) since you will be missing some config stuff in your database that you can add from the System Settings page. For MySQL and PostgreSQL, the SQL is:

CREATE TABLE webcal_group (
	cal_group_id INT NOT NULL,
	cal_owner VARCHAR(25) NULL,
	cal_name VARCHAR(50) NOT NULL,
	cal_last_update INT NOT 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_owner VARCHAR(25) NOT NULL,
	cal_name VARCHAR(50) 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_entry_id INT NOT NULL,
	cal_login VARCHAR(25) NOT NULL,
	cal_type CHAR(1) NOT NULL,
	cal_date INT NOT NULL,
	cal_time INT NULL,
	cal_text TEXT,
	PRIMARY KEY ( cal_log_id )
);

To upgrade from v0.9.35 or v0.9.36 to v0.9.37:

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

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 to v0.9.38:

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

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

To upgrade from v0.9.38 to v0.9.39:

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 to v0.9.40:

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 and PostgreSQL:

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;

For Oracle, use VARCHAR2 instead of VARCHAR.

To upgrade from v0.9.40 to v0.9.41:

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

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

For Oracle, use VARCHAR2 instead of VARCHAR and LONG instead of TEXT.

To upgrade from v0.9.41 to v0.9.42:

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

CREATE TABLE webcal_report (
	cal_login VARCHAR(25) NOT NULL,
	cal_report_id INT NOT NULL,
	cal_is_global CHAR(1) DEFAULT 'N' NOT NULL,
	cal_report_type VARCHAR(20) NOT NULL,
	cal_include_header CHAR(1) DEFAULT 'Y' NOT NULL,
	cal_report_name VARCHAR(50) NOT NULL,
	cal_time_range INT NOT NULL,
	cal_user VARCHAR(25) NULL,
	cal_allow_nav CHAR(1) DEFAULT 'Y',
	cal_cat_id INT NULL,
	cal_include_empty CHAR(1) DEFAULT 'N',
	cal_show_in_trailer CHAR(1) DEFAULT 'N',
	cal_update_date INT NOT 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_import_type VARCHAR(15) NOT NULL,
	cal_external_id VARCHAR(200) NULL,
	PRIMARY KEY  ( cal_id, cal_login )
);

For Oracle, use VARCHAR2 instead of VARCHAR.

To upgrade from v0.9.42 to v0.9.43:

User passwords are now stored using md5 and require the webcal_user table to be altered to accomodate 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_name VARCHAR(50) NULL,
	cal_date INT NOT NULL,
	cal_type VARCHAR(10) NOT NULL,
	cal_login VARCHAR(25) NULL,
	PRIMARY KEY ( cal_import_id )
);
CREATE TABLE webcal_import_data (
	cal_import_id INT NOT NULL,
	cal_id INT NOT NULL,
	cal_login VARCHAR(25) NOT NULL,
	cal_import_type VARCHAR(15) NOT NULL,
	cal_external_id VARCHAR(200) NULL,
	PRIMARY KEY  ( cal_id, cal_login )
);

Postgres does not allow you to modify an existing table column. Instead of the initial ALTER command above, issue the following commands below. You will not need to run the convert_passwords script below since this will set all user's passwords to 'admin'.

ALTER TABLE webcal_user RENAME COLUMN cal_passwd to cal_oldpass;
ALTER TABLE webcal_user ADD COLUMN cal_passwd VARCHAR(32) NULL;
UPDATE webcal_user SET cal_passwd = '21232f297a57a5a743894a0e4a801fc3';

For Oracle, use VARCHAR2 instead of VARCHAR. On very old MySQL installations (not sure which version), if you get a parse error, you can try the following instead:

ALTER TABLE webcal_user CHANGE cal_passwd cal_passwd VARCHAR(32) NULL;

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 either run this from the command line (if you have a standalone version of PHP compiled):

cd tools
php convert_passwords.php

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

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

To upgrade from v0.9.43 to v0.9.44:

No manual changes required for upgrading from 0.9.43 to 0.9.44.

To upgrade from v0.9.44 to v0.9.45:

There are no database changes from 0.9.44 to 0.9.45. However, the database settings were moved from includes/config.php to a new file includes/settings.php. This new file does not exist until you create it.

The first time you attempt to access WebCalendar with the new 0.9.45 files, your browser will be redirected to a web-based admin page for configuring the database settings. You will need to make sure the includes directory is writable by the web server user. The simplest solution is to make it writable by all users until you have correctly setup your database connection. Then, you can change it to more restrictive permissions.

After saving your database settings, be sure to setup an install password via this same web page so that other users may not change your database settings.

To upgrade from v0.9.45 or 1.0RC1 to v1.0RC2:

There are no database changes from 0.9.45 to 1.0RC2.

To upgrade from v1.0RC2 to v1.0RC3 or 1.0.X:

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

ALTER TABLE webcal_view ADD cal_is_global CHAR(1) DEFAULT 'N' NOT NULL;
UPDATE webcal_user_pref SET cal_value = 'day.php' WHERE cal_value = 'day' 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 = 'month.php' WHERE cal_value = 'month' AND cal_setting = 'STARTVIEW';
UPDATE webcal_user_pref SET cal_value = 'year.php' WHERE cal_value = 'year' AND cal_setting = 'STARTVIEW';
UPDATE webcal_config SET cal_value = 'week.php' WHERE cal_setting = 'STARTVIEW';