Published 2008-10-01 20:15:00

For this weeks hack, I've been looking at Lightning, the calendaring extension to thunderbird.

What brought this on was we where discussing a bug tracker for the Anti-spam / Mail management software, and none of the software that i've seen for this either is elegantly written, easy to set up or simple to use.

To me the 'ideal' way to handle this, having played around with Lightning, is to have a calender provider (that's shared) and have all the key people able to drag a 'requirement email' into the TODO task list, or similar. Where people can submit bug's by signing up to a calendar/todo list and just add it as a task.

Obviously not suited to all types of projects..., but I think you get the jist that using lightning to edit these bugs would be pretty compelling due to it's integration with your primary mailer.

State of the current providers.

So to see if this could work, I had a look at the current list of network providers available. ICAL, CalDav, Sun's Protocol, and there's a google provider.

On the face of things it looks like that's quite a choice, but if you look closely at all of these, one thing stands out like a sore thumb. The backend server to deal with all these protocols is an absolute nightmare to create, or modify to suit other types of applications (think of a shipping managment application that we could integrate order delivery information with the calendar, so you could add notes about the delivery etc. via the calendar item.).

Part of the core problem with the existing protocols can be summed up with one dirty word "XML"! - CalDav and google being the worst of this. If you look at the problem today and tried to solve it, the first thing that would come to mind would be JSON/REST. It's the perfect set of protocols to ensure that backend server would be trivial to write, and the front end would not be such a mess. (especially as Lightning's written mostly in Javascript anyway).

Hacking a provider

There are really only two examples I could find of calendar providers for lightning. Google is one, and there is also an extension called hcalendar - that pulls calendar's in from web pages (as far as I could see). The second of these two proved to be the goldmine for creating a JSON calendar provider extension.

hcalendar provides most of the code needed to implement any kind of provider, it only implements the getItems() method. However it has stub's for all the others.

You can browse through the code here, calJSONCalendar.js, I did run into a few issues with Async requests though, as It looks like you need to implement a request object (as per caldav and google calendar), otherwise you run into problems with transactions on updating. So as a simple workaround, the extension uses sync requests for updates and adding.

How it works


getItems() - get a list of calender or todo items.

is a GET request, with params
- aCount = max number of resulsts
- aRangeStart = date start ISO format
- aRangeEnd = date end ISO format
- caltype = calendar data type (C = calendar, T= todo)
- filter (not yet)
eg.
http://your_calendar?caltype=C&aCount=0&aRangeStart=2008-01-01....

this returns a json array something like this.
[
{
id: 1, // your data base id (ignored)
uid: "XXXXXXXXXXXXXXXXX",
dtstart: "2008-10-10 12:12:12",
dtend: "2008-10-10 12:12:12",
caltype : 'C',
ical : " <<< Calender data in ical format >>"

// all the other data is optional (if you do not have a ical record, it can build
// most of the event data from properties

privacy: "default";
created: "2008.....",
last_modified: "2008....."
},
......
]

modifyItem() / addItem()

is a POST request, with the params pretty much the same as the json data above (with the ical record in the ical POST entry) - the resulting entry is returned by the call in the same format as above. At present ical is the main thing that is posted, however I think it needs to send quite a bit of the generic information.

deleteItem()

is a POST request, with the property _delete_={uid},
(currently we ignore the response) - it should probably be
{ success: true|false, msgid : 1, msg: "A string error" }

the body of this message contains a example server implementation for the extension. - you can download the extension here: jsoncalendar-0.2.xpi, (REQUIRES LIGHTNING 0.9!!) or have a look at my svn repo JsonCalendarProvider for how to develop test it.

Read on for the SERVER BACKEND CODE...

Backend Code


Database Table:
CREATE TABLE `Cal` (
`id` int(11) NOT NULL auto_increment,
`dtstart` datetime NOT NULL,
`dtend` datetime NOT NULL,
`uid` varchar(32) NOT NULL,
`ical` text NOT NULL,
`privicy` varchar(32) NOT NULL,
`owner` INT(11) NOT NULL,
`calname` VARCHAR(128) NOT NULL DEFAULT '',
`last_modified` datetime default NULL,
`created` datetime default NULL,
caltype VARCHAR(1) DEFAULT 'C',
PRIMARY KEY (`id`),
KEY `new_index` (`dtstart`,`dtend`,`uid`, owner, calname),
KEY `uid` (`uid`)
)
Example PHP script (depends on various things, but you should be able to implement your own based on the ideas here)

class Cal extends HTML_FlexyFrameWork_Page
{
function sendFail($msg= "empty")
{
header('WWW-Authenticate: Basic realm="Calendar"');
header('HTTP/1.0 401 Unauthorized');
echo $msg;
exit;

}


function getAuth()
{
// use basic auth..
$u = isset($_SERVER['PHP_AUTH_USER']) ? $_SERVER['PHP_AUTH_USER'] : "";
$p = isset($_SERVER['PHP_AUTH_PW']) ? $_SERVER['PHP_AUTH_PW'] : "";
if (empty($u) || empty($p)) {
$this->sendFail();

}
// check the password combo...

$bits = explode('@', $u);

$d = DB_DataObject::factory('Domains');
$d->is_external = 0;
if (!$d->get('domain', $bits[1])) {
$this->sendFail('invalid domain');

}

$user =DB_DataObject::factory('Users');
$user->dom_id = $d->id;
$user->login = $bits[0];
if (!$user->find(true)) {
$this->sendFail('invalid user');
}



if ($user->checkPassword($p)) {
$this->authUser = $user;
return true;

//exit;
}

$this->sendFail('invalid details');

}


function get()
{
if (isset($_GET['POST'])) {
return $this->post();
}

$start =empty($_REQUEST['aRangeStart']) ?
"1970-01-01" : $_REQUEST['aRangeStart'];
$end =empty($_REQUEST['aRangeEnd']) ?
date("Y-m-d",strtotime("TODAY + 1 DAY")) : $_REQUEST['aRangeEnd'];

$d = DB_DataObject::factory('Cal');
if (!empty($_REQUEST['aRangeStart'])) {
$d->whereAdd("dtstart >= '". date("Y-m-d H:i:s", strtotime($start)) ."'");
$d->whereAdd("dtend <= '". date("Y-m-d H:i:s", strtotime($end)) ."'");
$d->caltype = 'C';
} else {
$d->caltype = 'T'; // task..
}


if (!empty($_REQUEST['aCount']) && ($_REQUEST['aCount'] * 1)> 0) {
$d->limit(1 * $_REQUEST['aCount']);
}
$d->owner = $this->authUser->id;
$d->find();
$ret = array();
while ($d->fetch()) {
if (empty($d->uid)) {
$d->uid = md5("mailfort". $d->id); // gen our uid.
}
$ret[] = $d->toArray();
}

require_once 'Services/JSON.php';
$json = new Services_JSON();
echo $json->encode( $ret);
exit;



}
function post()
{
$d = DB_DataObject::factory('Cal');
if (!empty($_REQUEST['_delete_'])) {
$d->owner = $this->authUser->id;
if (! $d->get('uid', $_REQUEST['_delete_'])) {
die("OOPPS");
}
$d->delete();
echo "OK";
exit;
}


if (!empty($_REQUEST['uid'])) {
$d->owner = $this->authUser->id;
if (!$d->get('uid', $_REQUEST['uid'])) {
die("OOPS");
}
}
// check for id...
$d->setFrom($_REQUEST);
$d->owner = $this->authUser->id;
if (empty($d->caltype)) {
if (empty($_REQUEST['dtstart'])) {
$d->caltype = 'T';
} else {
$d->caltype = 'C';
}

}


if (!$d->id) {
$d->insert();
$d->uid = md5("cal". $d->id);
}
$d->update();

require_once 'Services/JSON.php';
$json = new Services_JSON();
echo $json->encode($d->toArray());
exit;

}

}




Mentioned By:
andries.filmer.nl : Calendar JSON MySQL PHP - Andries Filmer (218 referals)
google.com : inurl:blog.php?user= (203 referals)
bugs.horde.org : Tickets :: [#7431] Create a Horde-native Lightning/Sunbird provider (122 referals)
outgoing.mozilla.org : Redirecting to http://www.akbkhome.com/blog.php/View/174/JSON.html (116 referals)
google.com : inurl:blog.php?user (48 referals)
planet-php.org : Planet PHP (39 referals)
google.com : json calendar (36 referals)
google.com : php json (36 referals)
google.com : december (34 referals)
google.com : inurl:/blog.php?user= (32 referals)
www.phpeye.com : JSON based calendar provider for thunderbird and lightning - Alan Knowles|PHP教程|PHP新闻|PHP5|PEAR|PHP框架|PHPEye - Power (29 referals)
groups.google.com : Create a Horde-native Lightning/Sunbird provider - mailing.www.horde-bugs | Google Groups (29 referals)
google.com : inurl:com/blog.php?user= (24 referals)
google.com : http://www.akbkhome.com/blog.php/View/174/JSON_based_calendar_provider_for_thunderbird_and_lightning.html (21 referals)
google.com : json calendar provider (16 referals)
google.com : json ical (14 referals)
google.com : ical json (13 referals)
google.com : easy php calendar nulled (11 referals)
google.com : blog.php?user= (10 referals)
google.com : php json datetime (10 referals)

Comments

Example is slightly dated now
0.3 uses
{ success: true, data: .... }
{ success: false, msg: .... }
as response from the server.
#0 - Alan Knowles ( Link) on 2008-10-05 11:30:30 Delete Comment
calendar
It is what I need :)
#2 - Paul M ( Link) on 2008-10-25 23:43:54 Delete Comment
Error Occured: Invalid Response: "JSON strng from www.filmer.nl/test/json.php" - undefined
This concept looks very good. I don't won't to store my calendar on google and with caldav you can't get it in mysql. So I am trying this addon.

I have de JSON calendar provider in thunderbird installed. When I want to load a JSON file (http://www.filmer.nl/test/json.php) I get the error in the addon console:

Error Occured: Invalid Response: "[ {"id":"1","uid": "2","dtstart": "2008-10-10 12:12:12","dtend": "2008-10-10 12:12:12","caltype":"C","ical":"BEGIN:VCALENDAR\nVERSION:2.0\nBEGIN:VEVENT\nCREATED:20091003T181005Z\nLAST-MODIFIED:20091003T181009Z\nDTSTAMP:20091003T181009Z\nSUMMARY:New Event\nDTSTART;TZID=Europe/Amsterdam:20091002T084500\nDTEND;TZID=Europe/Amsterdam:20091002T094500\nEND:VEVENT\nEND:VCALENDAR"} ] " - undefined

Can somebody confirm that this is a good json-file example. Otherwise can somebody give a tip?

Thanks Andries Filmer
#3 - Andries Filmer ( Link) on 2009-10-04 03:22:05 Delete Comment
Just another php script
Hello Alan,

Thanks very much for making json-calendar-provider.
Finally I have json-calendar-provider working. Maybe someone can use this (different) code.

http://kb.filmer.nl/70/Calendar---JSON---MySQL---PHP
http://kb.filmer.nl/scripts/php/json-calendar-provider-example-php.txt

Still I have some errors with update en insert items in json-calendar-provider and have to refresh the calendar.
#4 - Andries Filmer ( Link) on 2009-11-16 19:05:03 Delete Comment

Add Your Comment

Follow us on