Migration of localhost DAViCal server to Hosted cPanel Solution

In my earlier tutorial openSUSE 11.2 DAViCal Installation I went through the steps to install a DAViCal server on a local box. I now want to migrate this installation on to my hosted Badzilla website under the path http://caldav.badzilla.co.uk

This tutorial is to a large extent contingent upon your hosts. You will need a host that offers the Linux platform with PHP (with php5-pgsql extension library) and Postgresql (with the SQL and the PL/pgSQL languages installed) and an Apache webserver, and in addition if you want to use a subdomain, then that facility will have to be present too. A large proportion of companies offering hosted solutions use the cPanel product, including my own hosts. As a consequence, these instructions are based upon my experience of establishing DAViCal on a particular cPanel installation. It should be remembered that no two hosts are the same, and similarly, no two cPanel hosts are the same. Therefore anyone embarking upon replicating my steps may be required to use some nifty footwork along the way to enable DAViCal on their particular cPanel host.

SubdomainSubdomainWe'll start by setting up the subdomain because it is reliant on the propagation of DNS. We click on Domains->Subdomains and add the subdomain in the box provided (left) giving the result on the right.

Now back to the local configuration. Create a zipped tarball of the /srv/www/htdocs/davical structure with

laptop4:~ # cd /srv/www/htdocs/davical
laptop4:/srv/www/htdocs/davical # tar cf davical.tar *
laptop4:/srv/www/htdocs/davical # gzip davical.tar
laptop4:/srv/www/htdocs/davical # ls -las davical*
2168 -rw-r--r-- 1 root root 2213463 Apr  3 21:33 davical.tar.gz
laptop4:/srv/www/htdocs/davical #

Copy and expand this tarball into /public_html/caldav on the host. Delete the public_html/caldav/config/config.php which contains our localhost configuration. We will recreate a configuration for the hosted solution later.

Somehow we need to set a path in the DAViCal source code to the AWL Utilities code. I am using a hosted solution so editing the php.ini is out of the question since I don't have access. I also cannot change the contents of httpd.conf. The final configuration option would be to place a .htaccess file in my subdomain root containing the necessary php_value directive. Unfortunately that option isn't available either for most?all? cPanel users. My research leads me believe that certainly in the majority of cPanel installations the suPHP Apache module is used as opposed to the normal mod_php for security reasons. suPHP specifically prohibits the php_value directive (See here for confirmation; refer to last paragraph).

This is seriously bad news and means a more creative and less elegant solution is necessary. PHP offers the init_set() function which can add additional include paths during the execution of PHP scripts. Of course the problem with this approach is the code will need to be added to all PHP landing pages. The precise syntax being:

<?php
ini_set
('include_path',ini_get('include_path').':../awl/inc:'); 
?>

The following source files need this code adding at the very top. All files are in the htdocs subdirectory. So we open each in turn with our favourite editor and add the init_set() call.
admin.php
caldav.php
collection.php
freebusy.php
help.php
index.php
public.php
relationship_types.php
setup.php
tools.php
upgrade.php

working!You should now be able to check that the DAViCal system starts up, albeit without database and configuration. On my system I point a browser at caldav.badzilla.co.uk/htdocs. Hopefully you'll see the DAViCal Not Configured screen.

It would be ideal then to run the create-database.sh script to create the database. That is not an option on my host - the execution of shell scripts is not allowed, so another approach is required.

There are two possibilities here:

  1. Dump the localhost database and import into host database
  2. Import individual sql files into host database

1. Dump the localhost database and import into host database

Before you attempt this - don't waste a few hours of your life like I did. A dump of the localhost database and import into the hosted database will only work if the PostgreSQL versions at both ends are the same. If they are not - and mine weren't - it could fail and mine did. I was migrating from PostgreSQL 8.4 (local) to 8.1.11 (host) and got syntax error at or near "OWNED BY" errors.

On the assumption you have checked this and everything is ok, you need to dump the davical database from the local machine and edit the dump script so it can be imported into phpPgAdmin on the cPanel host and then executed. Before you can do that, you need to create a database and its accounts on the hosted site. On my site that cannot be achieved through phpPgAdmin - databases and accounts have to be created using the cPanel front end by clicking on the PostgreSQL icon on the cPanel homepage.

In addition, because users have to share their PostgreSQL databases with other users, unique prefixes are added to database names and account name. This will avoid clashes with other users on the host. Ok, so we create a database named davical; the creation process will add my cPanel account prefix so a database of xxxxxxx_davical is actually created, where the 'x' characters represent the actual prefix. Similarly, two accounts are created named davical_app and davical_dba. In the creation process these are renamed xxxxxxx_davicalapp and xxxxxxx_davicaldba.

To dump the DAViCal Postgresql database, which is rather conveniently called davical, use the pg_dump command.

laptop4:~ # pg_dump davical > /tmp/davical.out
laptop4:~ # ls -las /tmp/davical.out
124 -rw-r--r-- 1 root root 119417 Apr  3 21:30 /tmp/davical.out
laptop4:~ #

This gives us the instructions to recreate davcal, but it will not work without a little editing. Open the file for editing and perform the following operations:

Instruction Amendment Rationale
SET standard_conforming_strings = off; Comment out by prefixing with -- Value cannot be changed from system configuration
CREATE PROCEDURAL LANGUAGE plpgsql; Comment out by prefixing with -- Language already exists
ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres; Comment out by prefixing with -- Already set
Global search/replace replace all occurrences of davical_dba with xxxxxxx_davicaldba Account name created by cPanel differs from that in script

Created DBNow import this database into Postgresql on the host's box.
Click on the database name, then on SQL, and click on Browse.. to supply the dumped file /tmp/davical.out we created earlier. Hit Execute when you are ready.

2. Import individual sql files into host database

Using this method, you will still need to create the database and the users on the host machine, just like I explained in option 1.

Like the previous option, go to the SQL screen. Now import and execute the sql files in the order below into the database. Note that some of the files need a little editing by commenting out unwanted SQL statements.

windows/awl-tables.sql
COMMENT OUT FROM & INCLUDING 'GRANT' TO BEFORE THE 'COMMIT' STATEMENT BY PREPENDING --
windows/schema-management.sql
COMMENT OUT THESE LINES BY PREPENDING --
--GRANT SELECT ON awl_db_revision TO general;
davical.sql
supported_locales.sql
views/dav_principal.sql (commment out the initial DROP VIEW command)
caldav_functions.sql
COMMENT OUT THESE LINES BY PREPENDING --
--DROP FUNCTION rename_davical_user( TEXT, TEXT );
--DROP TRIGGER usr_modified ON usr CASCADE;
--DROP TRIGGER collection_modified ON collection CASCADE;
--DROP TRIGGER caldav_data_modified ON caldav_data CASCADE;
--DROP TRIGGER calendar_item_sync_dav_id ON calendar_item CASCADE;
--DROP TRIGGER principal_modified ON principal CASCADE;
--DROP TRIGGER grants_modified ON grants CASCADE;
rrule_functions-8.1.sql (if you are on PostgreSQL 8.1 like me, else rrule_functions.sql
COMMENT OUT THESE LINES BY PREPENDING --
--DROP TYPE rrule_parts CASCADE;
base-data.sql

At this point, options 1 and 2 converge.

We now need to set the permissions for our two database roles. Andrew is quite detailed in the settings, but for the sake of expedience I am going to be a little more cavalier without compromising the security of the server. Our system is already protected by cPanel so I feel we don't need to be as precise. There is a quick way of granting permissions to all tables with

SELECT
  'GRANT ALL ON'||tablename||' TO xxxxxxx_davicalapp;'
FROM
  pg_tables
WHERE
tablename NOT LIKE 'pg_%' AND tablename NOT LIKE 'sql_%';

This reported success on my system yet didn't work, so I completed the task long hand by manually going into all the tables and clicking on Privileges then clicking th check boxes as appropriate. If you are going to use my SQL statement, don't forget that you should do it for both the davicalapp and davicaldba roles. In addition, privileges need to be granted for the Views, Sequences and Functions.

We need to create a configuration file on the host for the DAViCal server with the database connection information inside along with an email address. Go to cPanel File Manager and find config/example-config.php. Copy it to config.php and open for editing. Look for the following:

$c->pg_connect[] = "dbname=davical user=davical_app";
$c->admin_email ='calendar-admin@example.com';

Change these values as appropriate. The dbname will be the one we created in the cPanel front-end which in my case is xxxxxxx_davical, whilst the user in my case is xxxxxxx_davicalapp. You may also need to add a password after the userid if you like me specified one when you set up the account. The email address will be an account to which you have access.

LoginNow you should be able to see the login screen when you point your browser to your domain/htdocs. A temporary admin password needs to be ceated before we go any further. You can of course pick anything you want, or use a password generator. For completeness, we'll do that. To generate a password you will need the command line utility pwgen on your localhost. It doesn't come pre-installed on openSUSE 11.2 but I'm sure you know how to install software by now. So run the utility:

laptop4:~> pwgen
EiKou4ch Phe5ohyu ahf4Magh Feiro9ao Ieshai5k Ca4aeG4i Eiwee9oh eb0esh0I
kema6Doh Xu0chaey aiGhie7u Oaboo9ur Chae6oob eesoRai6 Awuoza1b ahCe5hae
choo4ohY oereen8P tib5oo6T eelaej7B aiquoJ5o Ar2aSi9c cee6EiG3 uidut9Fe
paZaehe5 Ahromoh0 jeeXuTo4 Oon2shuu Eeph0uW7 Iev5wehu oov8Ahqu zeF9aech
Pohb4oow ieGaifu5 Tho6etie Uibee3su fauWoo4a Aemoo1li Toht8eja Iegac6iu
thoiH8la zi6ohb4O RaoTh5ph toF6phoo xae6Ehul shecui7U Eejaz4Pi Naeph4vo
ohN2aiy5 joi8Phei Ohv5loh4 air8ieD0 kae8rieH zoi6Nu2k Oghuuwe6 kahTohm5
to0eiDie Zusee8li OojaL5su iquuaCh3 jaeJah1o ahK3zook Eethai3g jei9Oong
thei7aPo Veeg9Aid ohYi6meu You5aHoo eT3uuRod eiPhah7a ehoLii2h Thauh3Vo
eiVah9Og muu1Boo9 ootajuP1 Iesha1ai ieb6Aezi hi9IenaD ohTaeth7 Eekoh3ci
oongoo8M uov3Veid thoh5Ool aiY8veo7 Boo5zeph Guu2hoo8 Aix6ieXi vie0Oong
siebee4V aisieTh4 uVee4lef iv7shahC equohQu8 Eixa8ohW Soc3thei Geigh8io
ohjieC2x beiXe7ia die8ooM9 ifoosh1P ju9eiSah Iowu3zee ieng8Uph lielah7I
bie2Reiz Uquajee2 ulee5UHo deb2Eeru Pu1zeevo Ea9lal1o kooDoh8l na2Ahz5j
yah2Ro7t xoo2Ooph ohNgohm8 IsiK3tha Ooz6tho0 xieGa4Ah phoo4ooN Nae8aiLo
nie4eiXu athuch9A ooroTh0I ohV3faik kah8teiN Xet2aiqu eNa0xei8 Die7ieGh
poh5waiB EeBu9chi oogheeT5 ewoTei8H ieRie1eo pho8Chah eey4eTh0 ziehoh0C
yoh3Zeet Iengohj7 Ievae6fa wah0Inoi Jah9ju0o eSi9ahju naeh9giG ahG9Aeca
Sohcae8o vieng0Io EeFafe9b ohj7ooS5 eiX7kieb jeeghu5Y AeTah9bu Fouxoog7
fo2Johth ozaequ4G ooha7aiF shegieT4 shaiCh5v ShaeH8Fo aegh0Hae Ohpevox6
laptop4:~>

PasswordPick a password and insert it into the hosted database using the phpPgAdmin utility. Click on Tables->tmp_password->Insert and populate the form as shown to the left. The user_no is 1 (the admin's number), the password is your chosen value, and then click on Insert. And no, I didn't actually use that password in my system!


AdministrationNow log onto the system and click on User Functions->View My Details (left) and change your password.

The next step is to check everything is working ok. I used the Chandler client to check that it could subscribe to a collection. If you need instructions on how to do this, something similar is undertaken in my Connecting-jCaldav-Joomla-Component-to-DAViCal-Server tutorial.

Invalid URLWhen I attempted this, Chandler gave me an Invalid URL diagnstic. You may well not have this problem, but I did so if it happens to you too, these are the steps I took to solve the issue.

I checked the error log file on my cPanel host, called error_log (although yours could be different) and noticed the following error:

PHP Fatal error:  Cannot redeclare apache_request_headers() (previously declared in /home/xxxxxxx/public_html/caldav/awl/inc/AWLUtilities.php:74) in /home/xxxxxxx/public_html/caldav/awl/inc/AWLUtilities.php(74) : eval()'d code on line 10

After inspecting the code I realised we could safely comment out the redefinition - so we need to edit the file and apply the comments. The file is awl/inc/AWLUtilities.php

<?php
if ( !function_exists('apache_request_headers') ) {
 
/**
  * Compatibility so we can use the apache function name and still work with CGI
  * @package awl
  */
  //function apache_request_headers() {
    //eval('
       
function apache_request_headers() {
            foreach(
$_SERVER as $key=>$value) {
                if (
substr($key,0,5)=="HTTP_") {
                   
$key=str_replace(" ","-",ucwords(strtolower(str_replace("_"," ",substr($key,5)))));
                   
$out[$key]=$value;
                }
            }
            return
$out;
        }
   
//');
  //}
}
?>

The comments are applied to the first occurrence of function apache_request_headers, the subsequent eval() function and the corresponding closing braces 2nd and 3rd line from the bottom. The error has been reported to Andrew and will be fixed in the next release of DAViCal, so the likelihood is you won't see this error. The error itself is caused by the fact my hosts run Apache under SUexec whilst the DAViCal package was authored with lighttpd in mind.

Subscribed to CollectionI then re-ran the test and the subscription to my football fixtures collection worked fine.

Your Apache server will probably have to be configured now to accept the PUT and DELETE methods, which under normal circumstances are outlawed for security reasons on publicly hosted servers. Thankfully my host allows a .htaccess file for each subdomain in which overrides can be placed. So do the following:

<Limit PUT DELETE>
Order Allow,Deny
Allow from All
</Limit>

Ok - you should now be done and dusted. Regrettably I am not. The problem is with my host - they kindly installed the PostgreSQL language PL/pgSQL on one of their servers for me. Unfortunately this is non-standard, and since customer accounts such as my own can get moved from server to server for load-balancing reasons, one day my account could end up on a server without PL/pgSQL

Should this happen, I need to know ASAP. The best solution would be to write a PHP script that checks periodically, through a scheduled cron job, whether the language is still present or not. Below is such a script which you can cut and paste, if you need, to your own host. I have called it cron.php and placed it in public_html/caldav/htdocs.

cron.php

<?php
# Copyright <a href="http://www.badzilla.co.uk
#" title="www.badzilla.co.uk
#">www.badzilla.co.uk
#</a> Check that PL/pgSQL is installed - if not, email an error message

#
# Licence GPL. This program may be distributed as per the terms of GPL and all credits
# must be retained
#
# If you find this script useful, please consider a donation to help me fund my web presence
# and encourage me to develop more products to be placed under the terms of GPL
# To donate, go to <a href="http://www.badzilla.co.uk" title="http://www.badzilla.co.uk">http://www.badzilla.co.uk</a> and click on the donation button
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

// replace the following values with your own
$dbname = 'davical';
$dbuser = 'davical_app';
$dbpassword = '';
$host = 'localhost';
$to = 'you@example.com';
$subject = 'Yourhost DAViCal Config Problem';
$message = "The Yourhost caldav database doesn't have the plpgsql language installed.\n Please attend to this asap.\n\n";
$from = 'webmaster@yourhost';
$reply = 'webmaster@yourhost';
$headers = "From: $from\r\nReply-To: $reply\r\nX-Mailer: PHP/" . phpversion();


try {
   
$db = new PDO("pgsql:host=$host;dbname=$dbname", $dbuser, $dbpassword);
   
$rows = $db->query("SELECT COUNT(*) FROM pg_language WHERE lanname = 'plpgsql';")->fetchColumn();
} catch (
PDOException $e) {
   
trigger_error('Database Error: ' . $e->getMessage() . '<br />', E_USER_ERROR);
}

if (
$rows)
    echo
"DAViCal: PL/pgSQL is present";
else
   
mail($to, $subject, $message, $headers);
?>

You now need to run this script on a regular basis. I've decided to run mine once a day at 6 minutes past midnight. Your schedule will probably be different!! The cron entry is listed below, however how you add cron entries will depend on your cPanel installation.

06 0 * * * wget -O - -t 1 http://caldav.badzilla.co.uk/htdocs/cron.php > /dev/null 2>&1

Almost finished! However, one 'to do' and one quick plug required...

To Do


I'm not too sure why Andrew designed DAViCal to run in the htdocs subdirectory, but I'm sure he had his reasons. The problem is without access to the httpd.conf file on a hosted solution it becomes a little awkward, and the base path becomes domain/htdocs/caldav.php. So, the URL requires a rewrite which will be completed using The Definitive Guide to Apache Mod_Rewrite once I've had opportunity to mug up.

DAViCal Consultancy

Setting up both the local and cPanel hosted solutions was quite a challenge and not for the faint-hearted. I hope I have given you every opportunity to follow the instructions and succeed in installing and configuring your server. If however you feel you have neither the time nor the inclination to undertake this yourself, I am available for consultancy. Drop me an email and let's talk further. I am based in the UK but I am able to travel further afield at short notice.



Thanks for a great tutorial.

Thanks for a great tutorial. I've been looking at ways to migrate my local davical server to a hosted server and only just found your tutorial.

I too am trying to migrate davical (1.1.1.2) from openSUSE (12.3 with pgsql9.2) and tried to follow your instructions but ran into a few problems. I was wondering if you still run davical on the hosted server and whether you've done any updates and if anything has changed.

My host runs pgsql8.4 so I need to upload each sql file individually but a lot of the edits you show that are required to some of those files don't exist in my files. I also didn't have some of the php files that you need to add the init_set() call to. So either davical has changed somewhat or my installation of it turned is different to yours.

Unfortunately, as you probaby know, the wiki on davical's site has mostly been lost so it's difficult to get good information for davical now.

I'd be interested in hearing an update if you're still using davical with your host and what your long term experience has been. I don't want to spend a ton of time getting this to work if it's not going to be a reliable solution or if there is some unforeseen issue to make it non feasible.