Fixing the upgrade procedure of WebCalendar 1.2 for PostgreSQL 7.4

When trying to upgrade WebCalendar to 1.2, I fell on a series of errors due to the fact that the upgrade script relies on a dependency to PostgreSQL 8.1, which allows much more ALTER TABLE syntaxes than PostgreSQL 7.4. Nevertheless, there are ways to make it work by updating the upgrade script a little to suit the new syntax. I'm giving bits of code here, which are directly based on the www/install/sql/update-postgres.sql script, and altered to make it work. This script can be executed manually as many times as you want with the PostgreSQL user you use for Webcalendar. Something like

psql -U webcalendar -W webcalendar < upgrade-postgres.sql

Please note that using the "postgres" user will inevitably create permissions problems that you might have to fix later using something like:

GRANT ALL PRIVILEGES ON DATABASE webcalendar TO webcalendar;

or something more specific if that doesn't fix it.

The "TYPE" syntax error

On line 156 of the update-postgres.sql script, you will find the following query:

ALTER TABLE webcal_user ALTER COLUMN cal_passwd TYPE VARCHAR(32);

The ALTER TABLE - ALTER COLUMN with a TYPE constraint, however, is unknown in Postgresql 7.4, so you need to change this line. Here is the code I used to replace it (quite long as you will see)

/* Query in PG 7.4 form */ /*ALTER TABLE webcal_user ALTER COLUMN cal_passwd TYPE VARCHAR(32); */ BEGIN; ALTER TABLE webcal_user ADD COLUMN new_col VARCHAR(32); UPDATE webcal_user SET new_col = CAST(cal_passwd AS VARCHAR(32)); ALTER TABLE webcal_user DROP COLUMN cal_passwd; ALTER TABLE webcal_user RENAME COLUMN new_col TO cal_passwd; COMMIT; VACUUM FULL webcal_user;

The VACUUM FULL command at the end allows for a complete memory clean-up which would otherwise not happen.

The UPDATE TABLE error for webcal_user

You will find the following lines around line 305:

ALTER TABLE webcal_user ADD COLUMN cal_enabled CHAR(1); UPDATE TABLE webcal_user SET cal_enabled = 'Y';

This one is easy: the "UPDATE TABLE" syntax is not allowed in PG 7.4. You simply have to remove the "TABLE" keyword:

ALTER TABLE webcal_user ADD COLUMN cal_enabled CHAR(1); UPDATE webcal_user SET cal_enabled = 'Y';

The NOT NULL constraint in webcal_entry_log problem

This one seems to be a real bug somewhere in the creation procedure for PostgreSQL tables. In fact, if you look into the tables-postgres.sql file, you will find that the webcal_entry_log.cal_user_cal column has a NOT NULL constraint, while in the equivalent script for MySQL, NULL values are authorized. This is tricky to fix (and uses the same procedure as the "TYPE" syntax bugfix above. In order to place it more or less in the right place, I have tried to put it around the same line as a query on the webcal_entry_log table in the upgrade-mysql.sql script (which is around line 75, or just before a comment talking about version 1.1.0).

/* Fix not null constraint in webcal_entry_log.cal_user_cal */ BEGIN; ALTER TABLE webcal_entry_log ADD COLUMN new_col VARCHAR(25) NULL DEFAULT NULL; UPDATE webcal_entry_log SET new_col = CAST(cal_user_cal AS VARCHAR(25)); ALTER TABLE webcal_entry_log DROP COLUMN cal_user_cal; ALTER TABLE webcal_entry_log RENAME COLUMN new_col TO cal_user_cal; COMMIT; VACUUM FULL webcal_entry_log;

The webcal_nonuser_cals.cal_is_public missing column

For some reason, the cal_is_public field had not been created by the upgrade procedure, so I had to add it manually (I did that around line 200)

ALTER TABLE webcal_nonuser_cals ADD cal_is_public CHAR(1); ALTER TABLE webcal_nonuser_cals ALTER cal_is_public SET DEFAULT 'N'; ALTER TABLE webcal_nonuser_cals ALTER cal_is_public SET NOT NULL;

The webcal_entry_repeats.cal_wkst DEFAULT constraint

Around line 240, you will find the following query

ALTER TABLE webcal_entry_repeats ADD cal_wkst CHAR(2) DEFAULT 'MO';

Because default values constraints cannot be assigned directly in an ALTER TABLE query, you have to split this line

/* Update to upgrade in PG 7.4 */ /* ALTER TABLE webcal_entry_repeats ADD cal_wkst CHAR(2) DEFAULT 'MO'; */ ALTER TABLE webcal_entry_repeats ADD cal_wkst CHAR(2); ALTER TABLE webcal_entry_repeats ALTER COLUMN cal_wkst SET DEFAULT 'MO';

You should now be alright with the SQL upgrade procedure.

Encoding

You might (but this has *nothing* to do with the above) run into an encoding problem whereby the accentuated characters are transformed into little squares with question marks. This is generally due to the fact that Webcalendar only deals with ISO charsets, while your Apache configuration might be set to a default encoding of UTF-8. Make sure there is an Apache's VirtualHost defined with an encoding of ISO-8859-1 if you are going to use this encoding.