6 * This backend is used to store calendar-data in a PDO database, such as
11 * @copyright Copyright (C) 2007-2012 Rooftop Solutions. All rights reserved.
12 * @author Evert Pot (http://www.rooftopsolutions.nl/)
13 * @license http://code.google.com/p/sabredav/wiki/License Modified BSD License
15 class Sabre_CalDAV_Backend_PDO extends Sabre_CalDAV_Backend_Abstract {
18 * We need to specify a max date, because we need to stop *somewhere*
20 * On 32 bit system the maximum for a signed integer is 2147483647, so
21 * MAX_DATE cannot be higher than date('Y-m-d', 2147483647) which results
22 * in 2038-01-19 to avoid problems when the date is converted
23 * to a unix timestamp.
25 const MAX_DATE = '2038-01-01';
35 * The table name that will be used for calendars
39 protected $calendarTableName;
42 * The table name that will be used for calendar objects
46 protected $calendarObjectTableName;
49 * List of CalDAV properties, and how they map to database fieldnames
51 * Add your own properties by simply adding on to this array
55 public $propertyMap = array(
56 '{DAV:}displayname' => 'displayname',
57 '{urn:ietf:params:xml:ns:caldav}calendar-description' => 'description',
58 '{urn:ietf:params:xml:ns:caldav}calendar-timezone' => 'timezone',
59 '{http://apple.com/ns/ical/}calendar-order' => 'calendarorder',
60 '{http://apple.com/ns/ical/}calendar-color' => 'calendarcolor',
67 * @param string $calendarTableName
68 * @param string $calendarObjectTableName
70 public function __construct(PDO $pdo, $calendarTableName = 'calendars', $calendarObjectTableName = 'calendarobjects') {
73 $this->calendarTableName = $calendarTableName;
74 $this->calendarObjectTableName = $calendarObjectTableName;
79 * Returns a list of calendars for a principal.
81 * Every project is an array with the following keys:
82 * * id, a unique id that will be used by other functions to modify the
83 * calendar. This can be the same as the uri or a database key.
84 * * uri, which the basename of the uri with which the calendar is
86 * * principaluri. The owner of the calendar. Almost always the same as
87 * principalUri passed to this method.
89 * Furthermore it can contain webdav properties in clark notation. A very
90 * common one is '{DAV:}displayname'.
92 * @param string $principalUri
95 public function getCalendarsForUser($principalUri) {
97 $fields = array_values($this->propertyMap);
101 $fields[] = 'components';
102 $fields[] = 'principaluri';
104 // Making fields a comma-delimited list
105 $fields = implode(', ', $fields);
106 $stmt = $this->pdo->prepare("SELECT " . $fields . " FROM ".$this->calendarTableName." WHERE principaluri = ? ORDER BY calendarorder ASC");
107 $stmt->execute(array($principalUri));
109 $calendars = array();
110 while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
112 $components = array();
113 if ($row['components']) {
114 $components = explode(',',$row['components']);
119 'uri' => $row['uri'],
120 'principaluri' => $row['principaluri'],
121 '{' . Sabre_CalDAV_Plugin::NS_CALENDARSERVER . '}getctag' => $row['ctag']?$row['ctag']:'0',
122 '{' . Sabre_CalDAV_Plugin::NS_CALDAV . '}supported-calendar-component-set' => new Sabre_CalDAV_Property_SupportedCalendarComponentSet($components),
126 foreach($this->propertyMap as $xmlName=>$dbName) {
127 $calendar[$xmlName] = $row[$dbName];
130 $calendars[] = $calendar;
139 * Creates a new calendar for a principal.
141 * If the creation was a success, an id must be returned that can be used to reference
142 * this calendar in other methods, such as updateCalendar
144 * @param string $principalUri
145 * @param string $calendarUri
146 * @param array $properties
149 public function createCalendar($principalUri, $calendarUri, array $properties) {
157 ':principaluri' => $principalUri,
158 ':uri' => $calendarUri,
163 $sccs = '{urn:ietf:params:xml:ns:caldav}supported-calendar-component-set';
164 $fieldNames[] = 'components';
165 if (!isset($properties[$sccs])) {
166 $values[':components'] = 'VEVENT,VTODO';
168 if (!($properties[$sccs] instanceof Sabre_CalDAV_Property_SupportedCalendarComponentSet)) {
169 throw new Sabre_DAV_Exception('The ' . $sccs . ' property must be of type: Sabre_CalDAV_Property_SupportedCalendarComponentSet');
171 $values[':components'] = implode(',',$properties[$sccs]->getValue());
174 foreach($this->propertyMap as $xmlName=>$dbName) {
175 if (isset($properties[$xmlName])) {
177 $values[':' . $dbName] = $properties[$xmlName];
178 $fieldNames[] = $dbName;
182 $stmt = $this->pdo->prepare("INSERT INTO ".$this->calendarTableName." (".implode(', ', $fieldNames).") VALUES (".implode(', ',array_keys($values)).")");
183 $stmt->execute($values);
185 return $this->pdo->lastInsertId();
190 * Updates properties for a calendar.
192 * The mutations array uses the propertyName in clark-notation as key,
193 * and the array value for the property value. In the case a property
194 * should be deleted, the property value will be null.
196 * This method must be atomic. If one property cannot be changed, the
197 * entire operation must fail.
199 * If the operation was successful, true can be returned.
200 * If the operation failed, false can be returned.
202 * Deletion of a non-existent property is always successful.
204 * Lastly, it is optional to return detailed information about any
205 * failures. In this case an array should be returned with the following
210 * '{DAV:}displayname' => null,
213 * '{DAV:}owner' => null,
217 * In this example it was forbidden to update {DAV:}displayname.
218 * (403 Forbidden), which in turn also caused {DAV:}owner to fail
219 * (424 Failed Dependency) because the request needs to be atomic.
221 * @param string $calendarId
222 * @param array $mutations
225 public function updateCalendar($calendarId, array $mutations) {
227 $newValues = array();
229 200 => array(), // Ok
230 403 => array(), // Forbidden
231 424 => array(), // Failed Dependency
236 foreach($mutations as $propertyName=>$propertyValue) {
238 // We don't know about this property.
239 if (!isset($this->propertyMap[$propertyName])) {
241 $result[403][$propertyName] = null;
242 unset($mutations[$propertyName]);
246 $fieldName = $this->propertyMap[$propertyName];
247 $newValues[$fieldName] = $propertyValue;
251 // If there were any errors we need to fail the request
253 // Properties has the remaining properties
254 foreach($mutations as $propertyName=>$propertyValue) {
255 $result[424][$propertyName] = null;
258 // Removing unused statuscodes for cleanliness
259 foreach($result as $status=>$properties) {
260 if (is_array($properties) && count($properties)===0) unset($result[$status]);
269 // Now we're generating the sql query.
270 $valuesSql = array();
271 foreach($newValues as $fieldName=>$value) {
272 $valuesSql[] = $fieldName . ' = ?';
274 $valuesSql[] = 'ctag = ctag + 1';
276 $stmt = $this->pdo->prepare("UPDATE " . $this->calendarTableName . " SET " . implode(', ',$valuesSql) . " WHERE id = ?");
277 $newValues['id'] = $calendarId;
278 $stmt->execute(array_values($newValues));
285 * Delete a calendar and all it's objects
287 * @param string $calendarId
290 public function deleteCalendar($calendarId) {
292 $stmt = $this->pdo->prepare('DELETE FROM '.$this->calendarObjectTableName.' WHERE calendarid = ?');
293 $stmt->execute(array($calendarId));
295 $stmt = $this->pdo->prepare('DELETE FROM '.$this->calendarTableName.' WHERE id = ?');
296 $stmt->execute(array($calendarId));
301 * Returns all calendar objects within a calendar.
303 * Every item contains an array with the following keys:
304 * * id - unique identifier which will be used for subsequent updates
305 * * calendardata - The iCalendar-compatible calendar data
306 * * uri - a unique key which will be used to construct the uri. This can be any arbitrary string.
307 * * lastmodified - a timestamp of the last modification time
308 * * etag - An arbitrary string, surrounded by double-quotes. (e.g.:
310 * * calendarid - The calendarid as it was passed to this function.
311 * * size - The size of the calendar objects, in bytes.
313 * Note that the etag is optional, but it's highly encouraged to return for
316 * The calendardata is also optional. If it's not returned
317 * 'getCalendarObject' will be called later, which *is* expected to return
320 * If neither etag or size are specified, the calendardata will be
321 * used/fetched to determine these numbers. If both are specified the
322 * amount of times this is needed is reduced by a great degree.
324 * @param string $calendarId
327 public function getCalendarObjects($calendarId) {
329 $stmt = $this->pdo->prepare('SELECT id, uri, lastmodified, etag, calendarid, size FROM '.$this->calendarObjectTableName.' WHERE calendarid = ?');
330 $stmt->execute(array($calendarId));
333 foreach($stmt->fetchAll(\PDO::FETCH_ASSOC) as $row) {
336 'uri' => $row['uri'],
337 'lastmodified' => $row['lastmodified'],
338 'etag' => '"' . $row['etag'] . '"',
339 'calendarid' => $row['calendarid'],
340 'size' => (int)$row['size'],
349 * Returns information from a single calendar object, based on it's object
352 * The returned array must have the same keys as getCalendarObjects. The
353 * 'calendardata' object is required here though, while it's not required
354 * for getCalendarObjects.
356 * @param string $calendarId
357 * @param string $objectUri
360 public function getCalendarObject($calendarId,$objectUri) {
362 $stmt = $this->pdo->prepare('SELECT id, uri, lastmodified, etag, calendarid, size, calendardata FROM '.$this->calendarObjectTableName.' WHERE calendarid = ? AND uri = ?');
363 $stmt->execute(array($calendarId, $objectUri));
364 $row = $stmt->fetch(\PDO::FETCH_ASSOC);
366 if(!$row) return null;
370 'uri' => $row['uri'],
371 'lastmodified' => $row['lastmodified'],
372 'etag' => '"' . $row['etag'] . '"',
373 'calendarid' => $row['calendarid'],
374 'size' => (int)$row['size'],
375 'calendardata' => $row['calendardata'],
382 * Creates a new calendar object.
384 * It is possible return an etag from this function, which will be used in
385 * the response to this PUT request. Note that the ETag must be surrounded
388 * However, you should only really return this ETag if you don't mangle the
389 * calendar-data. If the result of a subsequent GET to this object is not
390 * the exact same as this request body, you should omit the ETag.
392 * @param mixed $calendarId
393 * @param string $objectUri
394 * @param string $calendarData
395 * @return string|null
397 public function createCalendarObject($calendarId,$objectUri,$calendarData) {
399 $extraData = $this->getDenormalizedData($calendarData);
401 $stmt = $this->pdo->prepare('INSERT INTO '.$this->calendarObjectTableName.' (calendarid, uri, calendardata, lastmodified, etag, size, componenttype, firstoccurence, lastoccurence) VALUES (?,?,?,?,?,?,?,?,?)');
402 $stmt->execute(array(
409 $extraData['componentType'],
410 $extraData['firstOccurence'],
411 $extraData['lastOccurence'],
413 $stmt = $this->pdo->prepare('UPDATE '.$this->calendarTableName.' SET ctag = ctag + 1 WHERE id = ?');
414 $stmt->execute(array($calendarId));
416 return '"' . $extraData['etag'] . '"';
421 * Updates an existing calendarobject, based on it's uri.
423 * It is possible return an etag from this function, which will be used in
424 * the response to this PUT request. Note that the ETag must be surrounded
427 * However, you should only really return this ETag if you don't mangle the
428 * calendar-data. If the result of a subsequent GET to this object is not
429 * the exact same as this request body, you should omit the ETag.
431 * @param mixed $calendarId
432 * @param string $objectUri
433 * @param string $calendarData
434 * @return string|null
436 public function updateCalendarObject($calendarId,$objectUri,$calendarData) {
438 $extraData = $this->getDenormalizedData($calendarData);
440 $stmt = $this->pdo->prepare('UPDATE '.$this->calendarObjectTableName.' SET calendardata = ?, lastmodified = ?, etag = ?, size = ?, componenttype = ?, firstoccurence = ?, lastoccurence = ? WHERE calendarid = ? AND uri = ?');
441 $stmt->execute(array($calendarData,time(), $extraData['etag'], $extraData['size'], $extraData['componentType'], $extraData['firstOccurence'], $extraData['lastOccurence'] ,$calendarId,$objectUri));
442 $stmt = $this->pdo->prepare('UPDATE '.$this->calendarTableName.' SET ctag = ctag + 1 WHERE id = ?');
443 $stmt->execute(array($calendarId));
445 return '"' . $extraData['etag'] . '"';
450 * Parses some information from calendar objects, used for optimized
453 * Returns an array with the following keys:
460 * @param string $calendarData
463 protected function getDenormalizedData($calendarData) {
465 $vObject = Sabre_VObject_Reader::read($calendarData);
466 $componentType = null;
468 $firstOccurence = null;
469 $lastOccurence = null;
470 foreach($vObject->getComponents() as $component) {
471 if ($component->name!=='VTIMEZONE') {
472 $componentType = $component->name;
476 if (!$componentType) {
477 throw new Sabre_DAV_Exception_BadRequest('Calendar objects must have a VJOURNAL, VEVENT or VTODO component');
479 if ($componentType === 'VEVENT') {
480 $firstOccurence = $component->DTSTART->getDateTime()->getTimeStamp();
481 // Finding the last occurence is a bit harder
482 if (!isset($component->RRULE)) {
483 if (isset($component->DTEND)) {
484 $lastOccurence = $component->DTEND->getDateTime()->getTimeStamp();
485 } elseif (isset($component->DURATION)) {
486 $endDate = clone $component->DTSTART->getDateTime();
487 $endDate->add(Sabre_VObject_DateTimeParser::parse($component->DURATION->value));
488 $lastOccurence = $endDate->getTimeStamp();
489 } elseif ($component->DTSTART->getDateType()===Sabre_VObject_Property_DateTime::DATE) {
490 $endDate = clone $component->DTSTART->getDateTime();
491 $endDate->modify('+1 day');
492 $lastOccurence = $endDate->getTimeStamp();
494 $lastOccurence = $firstOccurence;
497 $it = new Sabre_VObject_RecurrenceIterator($vObject, (string)$component->UID);
498 $maxDate = new DateTime(self::MAX_DATE);
499 if ($it->isInfinite()) {
500 $lastOccurence = $maxDate->getTimeStamp();
502 $end = $it->getDtEnd();
503 while($it->valid() && $end < $maxDate) {
504 $end = $it->getDtEnd();
508 $lastOccurence = $end->getTimeStamp();
515 'etag' => md5($calendarData),
516 'size' => strlen($calendarData),
517 'componentType' => $componentType,
518 'firstOccurence' => $firstOccurence,
519 'lastOccurence' => $lastOccurence,
525 * Deletes an existing calendar object.
527 * @param string $calendarId
528 * @param string $objectUri
531 public function deleteCalendarObject($calendarId,$objectUri) {
533 $stmt = $this->pdo->prepare('DELETE FROM '.$this->calendarObjectTableName.' WHERE calendarid = ? AND uri = ?');
534 $stmt->execute(array($calendarId,$objectUri));
535 $stmt = $this->pdo->prepare('UPDATE '. $this->calendarTableName .' SET ctag = ctag + 1 WHERE id = ?');
536 $stmt->execute(array($calendarId));
541 * Performs a calendar-query on the contents of this calendar.
543 * The calendar-query is defined in RFC4791 : CalDAV. Using the
544 * calendar-query it is possible for a client to request a specific set of
545 * object, based on contents of iCalendar properties, date-ranges and
546 * iCalendar component types (VTODO, VEVENT).
548 * This method should just return a list of (relative) urls that match this
551 * The list of filters are specified as an array. The exact array is
552 * documented by Sabre_CalDAV_CalendarQueryParser.
554 * Note that it is extremely likely that getCalendarObject for every path
555 * returned from this method will be called almost immediately after. You
556 * may want to anticipate this to speed up these requests.
558 * This method provides a default implementation, which parses *all* the
559 * iCalendar objects in the specified calendar.
561 * This default may well be good enough for personal use, and calendars
562 * that aren't very large. But if you anticipate high usage, big calendars
563 * or high loads, you are strongly adviced to optimize certain paths.
565 * The best way to do so is override this method and to optimize
566 * specifically for 'common filters'.
568 * Requests that are extremely common are:
569 * * requests for just VEVENTS
570 * * requests for just VTODO
571 * * requests with a time-range-filter on a VEVENT.
573 * ..and combinations of these requests. It may not be worth it to try to
574 * handle every possible situation and just rely on the (relatively
575 * easy to use) CalendarQueryValidator to handle the rest.
577 * Note that especially time-range-filters may be difficult to parse. A
578 * time-range filter specified on a VEVENT must for instance also handle
579 * recurrence rules correctly.
580 * A good example of how to interprete all these filters can also simply
581 * be found in Sabre_CalDAV_CalendarQueryFilter. This class is as correct
582 * as possible, so it gives you a good idea on what type of stuff you need
585 * This specific implementation (for the PDO) backend optimizes filters on
586 * specific components, and VEVENT time-ranges.
588 * @param string $calendarId
589 * @param array $filters
592 public function calendarQuery($calendarId, array $filters) {
595 $validator = new Sabre_CalDAV_CalendarQueryValidator();
597 $componentType = null;
598 $requirePostFilter = true;
601 // if no filters were specified, we don't need to filter after a query
602 if (!$filters['prop-filters'] && !$filters['comp-filters']) {
603 $requirePostFilter = false;
606 // Figuring out if there's a component filter
607 if (count($filters['comp-filters']) > 0 && !$filters['comp-filters'][0]['is-not-defined']) {
608 $componentType = $filters['comp-filters'][0]['name'];
610 // Checking if we need post-filters
611 if (!$filters['prop-filters'] && !$filters['comp-filters'][0]['comp-filters'] && !$filters['comp-filters'][0]['time-range'] && !$filters['comp-filters'][0]['prop-filters']) {
612 $requirePostFilter = false;
614 // There was a time-range filter
615 if ($componentType == 'VEVENT' && isset($filters['comp-filters'][0]['time-range'])) {
616 $timeRange = $filters['comp-filters'][0]['time-range'];
621 if ($requirePostFilter) {
622 $query = "SELECT uri, calendardata FROM ".$this->calendarObjectTableName." WHERE calendarid = :calendarid";
624 $query = "SELECT uri FROM ".$this->calendarObjectTableName." WHERE calendarid = :calendarid";
628 'calendarid' => $calendarId,
631 if ($componentType) {
632 $query.=" AND componenttype = :componenttype";
633 $values['componenttype'] = $componentType;
636 if ($timeRange && $timeRange['start']) {
637 $query.=" AND lastoccurence > :startdate";
638 $values['startdate'] = $timeRange['start']->getTimeStamp();
640 if ($timeRange && $timeRange['end']) {
641 $query.=" AND firstoccurence < :enddate";
642 $values['enddate'] = $timeRange['end']->getTimeStamp();
645 $stmt = $this->pdo->prepare($query);
646 $stmt->execute($values);
649 while($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
650 if ($requirePostFilter) {
651 if (!$this->validateFilterForObject($row, $filters)) {
655 $result[] = $row['uri'];