| WebCalendar Version: | 1.2.9 |
|---|---|
| Document Version: | $Revision$ $Date$ |
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.
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
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.
Below are the steps needed to manually upgrade from a previous version. You can ignore everything below if you use the Automated Installation Script. Select the version of your existing install from the list below. If you are more than one version behind (i.e. the new version is v1.1.6, 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.
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.
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.
next...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.
next...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;next...
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)
);
next...
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 )
);
next...
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.
next...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 )
);
next...
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 )
);
next...
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 )
);
next...
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%';
next...
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;
next...
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 )
);
next...
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 )
);
next...
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.
next...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';
next...
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 )
);
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.