diff options
Diffstat (limited to 'calendar/lib/SabreDAV/lib/OldSabre/CalDAV/Backend/PDO.php')
| -rw-r--r-- | calendar/lib/SabreDAV/lib/OldSabre/CalDAV/Backend/PDO.php | 691 |
1 files changed, 691 insertions, 0 deletions
diff --git a/calendar/lib/SabreDAV/lib/OldSabre/CalDAV/Backend/PDO.php b/calendar/lib/SabreDAV/lib/OldSabre/CalDAV/Backend/PDO.php new file mode 100644 index 0000000..104deea --- /dev/null +++ b/calendar/lib/SabreDAV/lib/OldSabre/CalDAV/Backend/PDO.php @@ -0,0 +1,691 @@ +<?php + +namespace OldSabre\CalDAV\Backend; + +use OldSabre\VObject; +use OldSabre\CalDAV; +use OldSabre\DAV; + +/** + * PDO CalDAV backend + * + * This backend is used to store calendar-data in a PDO database, such as + * sqlite or MySQL + * + * @copyright Copyright (C) 2007-2015 fruux GmbH (https://fruux.com/). + * @author Evert Pot (http://evertpot.com/) + * @license http://sabre.io/license/ Modified BSD License + */ +class PDO extends AbstractBackend { + + /** + * We need to specify a max date, because we need to stop *somewhere* + * + * On 32 bit system the maximum for a signed integer is 2147483647, so + * MAX_DATE cannot be higher than date('Y-m-d', 2147483647) which results + * in 2038-01-19 to avoid problems when the date is converted + * to a unix timestamp. + */ + const MAX_DATE = '2038-01-01'; + + /** + * pdo + * + * @var \PDO + */ + protected $pdo; + + /** + * The table name that will be used for calendars + * + * @var string + */ + protected $calendarTableName; + + /** + * The table name that will be used for calendar objects + * + * @var string + */ + protected $calendarObjectTableName; + + /** + * List of CalDAV properties, and how they map to database fieldnames + * Add your own properties by simply adding on to this array. + * + * Note that only string-based properties are supported here. + * + * @var array + */ + public $propertyMap = array( + '{DAV:}displayname' => 'displayname', + '{urn:ietf:params:xml:ns:caldav}calendar-description' => 'description', + '{urn:ietf:params:xml:ns:caldav}calendar-timezone' => 'timezone', + '{http://apple.com/ns/ical/}calendar-order' => 'calendarorder', + '{http://apple.com/ns/ical/}calendar-color' => 'calendarcolor', + ); + + /** + * Creates the backend + * + * @param \PDO $pdo + * @param string $calendarTableName + * @param string $calendarObjectTableName + */ + public function __construct(\PDO $pdo, $calendarTableName = 'calendars', $calendarObjectTableName = 'calendarobjects') { + + $this->pdo = $pdo; + $this->calendarTableName = $calendarTableName; + $this->calendarObjectTableName = $calendarObjectTableName; + + } + + /** + * Returns a list of calendars for a principal. + * + * Every project is an array with the following keys: + * * id, a unique id that will be used by other functions to modify the + * calendar. This can be the same as the uri or a database key. + * * uri, which the basename of the uri with which the calendar is + * accessed. + * * principaluri. The owner of the calendar. Almost always the same as + * principalUri passed to this method. + * + * Furthermore it can contain webdav properties in clark notation. A very + * common one is '{DAV:}displayname'. + * + * @param string $principalUri + * @return array + */ + public function getCalendarsForUser($principalUri) { + + $fields = array_values($this->propertyMap); + $fields[] = 'id'; + $fields[] = 'uri'; + $fields[] = 'ctag'; + $fields[] = 'components'; + $fields[] = 'principaluri'; + $fields[] = 'transparent'; + + // Making fields a comma-delimited list + $fields = implode(', ', $fields); + $stmt = $this->pdo->prepare("SELECT " . $fields . " FROM ".$this->calendarTableName." WHERE principaluri = ? ORDER BY calendarorder ASC"); + $stmt->execute(array($principalUri)); + + $calendars = array(); + while($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { + + $components = array(); + if ($row['components']) { + $components = explode(',',$row['components']); + } + + $calendar = array( + 'id' => $row['id'], + 'uri' => $row['uri'], + 'principaluri' => $row['principaluri'], + '{' . CalDAV\Plugin::NS_CALENDARSERVER . '}getctag' => $row['ctag']?$row['ctag']:'0', + '{' . CalDAV\Plugin::NS_CALDAV . '}supported-calendar-component-set' => new CalDAV\Property\SupportedCalendarComponentSet($components), + '{' . CalDAV\Plugin::NS_CALDAV . '}schedule-calendar-transp' => new CalDAV\Property\ScheduleCalendarTransp($row['transparent']?'transparent':'opaque'), + ); + + + foreach($this->propertyMap as $xmlName=>$dbName) { + $calendar[$xmlName] = $row[$dbName]; + } + + $calendars[] = $calendar; + + } + + return $calendars; + + } + + /** + * Creates a new calendar for a principal. + * + * If the creation was a success, an id must be returned that can be used to reference + * this calendar in other methods, such as updateCalendar + * + * @param string $principalUri + * @param string $calendarUri + * @param array $properties + * @return string + */ + public function createCalendar($principalUri, $calendarUri, array $properties) { + + $fieldNames = array( + 'principaluri', + 'uri', + 'ctag', + 'transparent', + ); + $values = array( + ':principaluri' => $principalUri, + ':uri' => $calendarUri, + ':ctag' => 1, + ':transparent' => 0, + ); + + // Default value + $sccs = '{urn:ietf:params:xml:ns:caldav}supported-calendar-component-set'; + $fieldNames[] = 'components'; + if (!isset($properties[$sccs])) { + $values[':components'] = 'VEVENT,VTODO'; + } else { + if (!($properties[$sccs] instanceof CalDAV\Property\SupportedCalendarComponentSet)) { + throw new DAV\Exception('The ' . $sccs . ' property must be of type: \OldSabre\CalDAV\Property\SupportedCalendarComponentSet'); + } + $values[':components'] = implode(',',$properties[$sccs]->getValue()); + } + $transp = '{' . CalDAV\Plugin::NS_CALDAV . '}schedule-calendar-transp'; + if (isset($properties[$transp])) { + $values[':transparent'] = $properties[$transp]->getValue()==='transparent'; + } + + foreach($this->propertyMap as $xmlName=>$dbName) { + if (isset($properties[$xmlName])) { + + $values[':' . $dbName] = $properties[$xmlName]; + $fieldNames[] = $dbName; + } + } + + $stmt = $this->pdo->prepare("INSERT INTO ".$this->calendarTableName." (".implode(', ', $fieldNames).") VALUES (".implode(', ',array_keys($values)).")"); + $stmt->execute($values); + + return $this->pdo->lastInsertId(); + + } + + /** + * Updates properties for a calendar. + * + * The mutations array uses the propertyName in clark-notation as key, + * and the array value for the property value. In the case a property + * should be deleted, the property value will be null. + * + * This method must be atomic. If one property cannot be changed, the + * entire operation must fail. + * + * If the operation was successful, true can be returned. + * If the operation failed, false can be returned. + * + * Deletion of a non-existent property is always successful. + * + * Lastly, it is optional to return detailed information about any + * failures. In this case an array should be returned with the following + * structure: + * + * array( + * 403 => array( + * '{DAV:}displayname' => null, + * ), + * 424 => array( + * '{DAV:}owner' => null, + * ) + * ) + * + * In this example it was forbidden to update {DAV:}displayname. + * (403 Forbidden), which in turn also caused {DAV:}owner to fail + * (424 Failed Dependency) because the request needs to be atomic. + * + * @param string $calendarId + * @param array $mutations + * @return bool|array + */ + public function updateCalendar($calendarId, array $mutations) { + + $newValues = array(); + $result = array( + 200 => array(), // Ok + 403 => array(), // Forbidden + 424 => array(), // Failed Dependency + ); + + $hasError = false; + + foreach($mutations as $propertyName=>$propertyValue) { + + switch($propertyName) { + case '{' . CalDAV\Plugin::NS_CALDAV . '}schedule-calendar-transp' : + $fieldName = 'transparent'; + $newValues[$fieldName] = $propertyValue->getValue()==='transparent'; + break; + default : + // Checking the property map + if (!isset($this->propertyMap[$propertyName])) { + // We don't know about this property. + $hasError = true; + $result[403][$propertyName] = null; + unset($mutations[$propertyName]); + continue; + } + + $fieldName = $this->propertyMap[$propertyName]; + $newValues[$fieldName] = $propertyValue; + } + + } + + // If there were any errors we need to fail the request + if ($hasError) { + // Properties has the remaining properties + foreach($mutations as $propertyName=>$propertyValue) { + $result[424][$propertyName] = null; + } + + // Removing unused statuscodes for cleanliness + foreach($result as $status=>$properties) { + if (is_array($properties) && count($properties)===0) unset($result[$status]); + } + + return $result; + + } + + // Success + + // Now we're generating the sql query. + $valuesSql = array(); + foreach($newValues as $fieldName=>$value) { + $valuesSql[] = $fieldName . ' = ?'; + } + $valuesSql[] = 'ctag = ctag + 1'; + + $stmt = $this->pdo->prepare("UPDATE " . $this->calendarTableName . " SET " . implode(', ',$valuesSql) . " WHERE id = ?"); + $newValues['id'] = $calendarId; + $stmt->execute(array_values($newValues)); + + return true; + + } + + /** + * Delete a calendar and all it's objects + * + * @param string $calendarId + * @return void + */ + public function deleteCalendar($calendarId) { + + $stmt = $this->pdo->prepare('DELETE FROM '.$this->calendarObjectTableName.' WHERE calendarid = ?'); + $stmt->execute(array($calendarId)); + + $stmt = $this->pdo->prepare('DELETE FROM '.$this->calendarTableName.' WHERE id = ?'); + $stmt->execute(array($calendarId)); + + } + + /** + * Returns all calendar objects within a calendar. + * + * Every item contains an array with the following keys: + * * id - unique identifier which will be used for subsequent updates + * * calendardata - The iCalendar-compatible calendar data + * * uri - a unique key which will be used to construct the uri. This can be any arbitrary string. + * * lastmodified - a timestamp of the last modification time + * * etag - An arbitrary string, surrounded by double-quotes. (e.g.: + * ' "abcdef"') + * * calendarid - The calendarid as it was passed to this function. + * * size - The size of the calendar objects, in bytes. + * + * Note that the etag is optional, but it's highly encouraged to return for + * speed reasons. + * + * The calendardata is also optional. If it's not returned + * 'getCalendarObject' will be called later, which *is* expected to return + * calendardata. + * + * If neither etag or size are specified, the calendardata will be + * used/fetched to determine these numbers. If both are specified the + * amount of times this is needed is reduced by a great degree. + * + * @param string $calendarId + * @return array + */ + public function getCalendarObjects($calendarId) { + + $stmt = $this->pdo->prepare('SELECT id, uri, lastmodified, etag, calendarid, size FROM '.$this->calendarObjectTableName.' WHERE calendarid = ?'); + $stmt->execute(array($calendarId)); + + $result = array(); + foreach($stmt->fetchAll(\PDO::FETCH_ASSOC) as $row) { + $result[] = array( + 'id' => $row['id'], + 'uri' => $row['uri'], + 'lastmodified' => $row['lastmodified'], + 'etag' => '"' . $row['etag'] . '"', + 'calendarid' => $row['calendarid'], + 'size' => (int)$row['size'], + ); + } + + return $result; + + } + + /** + * Returns information from a single calendar object, based on it's object + * uri. + * + * The returned array must have the same keys as getCalendarObjects. The + * 'calendardata' object is required here though, while it's not required + * for getCalendarObjects. + * + * This method must return null if the object did not exist. + * + * @param string $calendarId + * @param string $objectUri + * @return array|null + */ + public function getCalendarObject($calendarId,$objectUri) { + + $stmt = $this->pdo->prepare('SELECT id, uri, lastmodified, etag, calendarid, size, calendardata FROM '.$this->calendarObjectTableName.' WHERE calendarid = ? AND uri = ?'); + $stmt->execute(array($calendarId, $objectUri)); + $row = $stmt->fetch(\PDO::FETCH_ASSOC); + + if(!$row) return null; + + return array( + 'id' => $row['id'], + 'uri' => $row['uri'], + 'lastmodified' => $row['lastmodified'], + 'etag' => '"' . $row['etag'] . '"', + 'calendarid' => $row['calendarid'], + 'size' => (int)$row['size'], + 'calendardata' => $row['calendardata'], + ); + + } + + + /** + * Creates a new calendar object. + * + * It is possible return an etag from this function, which will be used in + * the response to this PUT request. Note that the ETag must be surrounded + * by double-quotes. + * + * However, you should only really return this ETag if you don't mangle the + * calendar-data. If the result of a subsequent GET to this object is not + * the exact same as this request body, you should omit the ETag. + * + * @param mixed $calendarId + * @param string $objectUri + * @param string $calendarData + * @return string|null + */ + public function createCalendarObject($calendarId,$objectUri,$calendarData) { + + $extraData = $this->getDenormalizedData($calendarData); + + $stmt = $this->pdo->prepare('INSERT INTO '.$this->calendarObjectTableName.' (calendarid, uri, calendardata, lastmodified, etag, size, componenttype, firstoccurence, lastoccurence) VALUES (?,?,?,?,?,?,?,?,?)'); + $stmt->execute(array( + $calendarId, + $objectUri, + $calendarData, + time(), + $extraData['etag'], + $extraData['size'], + $extraData['componentType'], + $extraData['firstOccurence'], + $extraData['lastOccurence'], + )); + $stmt = $this->pdo->prepare('UPDATE '.$this->calendarTableName.' SET ctag = ctag + 1 WHERE id = ?'); + $stmt->execute(array($calendarId)); + + return '"' . $extraData['etag'] . '"'; + + } + + /** + * Updates an existing calendarobject, based on it's uri. + * + * It is possible return an etag from this function, which will be used in + * the response to this PUT request. Note that the ETag must be surrounded + * by double-quotes. + * + * However, you should only really return this ETag if you don't mangle the + * calendar-data. If the result of a subsequent GET to this object is not + * the exact same as this request body, you should omit the ETag. + * + * @param mixed $calendarId + * @param string $objectUri + * @param string $calendarData + * @return string|null + */ + public function updateCalendarObject($calendarId,$objectUri,$calendarData) { + + $extraData = $this->getDenormalizedData($calendarData); + + $stmt = $this->pdo->prepare('UPDATE '.$this->calendarObjectTableName.' SET calendardata = ?, lastmodified = ?, etag = ?, size = ?, componenttype = ?, firstoccurence = ?, lastoccurence = ? WHERE calendarid = ? AND uri = ?'); + $stmt->execute(array($calendarData,time(), $extraData['etag'], $extraData['size'], $extraData['componentType'], $extraData['firstOccurence'], $extraData['lastOccurence'] ,$calendarId,$objectUri)); + $stmt = $this->pdo->prepare('UPDATE '.$this->calendarTableName.' SET ctag = ctag + 1 WHERE id = ?'); + $stmt->execute(array($calendarId)); + + return '"' . $extraData['etag'] . '"'; + + } + + /** + * Parses some information from calendar objects, used for optimized + * calendar-queries. + * + * Returns an array with the following keys: + * * etag + * * size + * * componentType + * * firstOccurence + * * lastOccurence + * + * @param string $calendarData + * @return array + */ + protected function getDenormalizedData($calendarData) { + + $vObject = VObject\Reader::read($calendarData); + $componentType = null; + $component = null; + $firstOccurence = null; + $lastOccurence = null; + foreach($vObject->getComponents() as $component) { + if ($component->name!=='VTIMEZONE') { + $componentType = $component->name; + break; + } + } + if (!$componentType) { + throw new \OldSabre\DAV\Exception\BadRequest('Calendar objects must have a VJOURNAL, VEVENT or VTODO component'); + } + if ($componentType === 'VEVENT') { + $firstOccurence = $component->DTSTART->getDateTime()->getTimeStamp(); + // Finding the last occurence is a bit harder + if (!isset($component->RRULE)) { + if (isset($component->DTEND)) { + $lastOccurence = $component->DTEND->getDateTime()->getTimeStamp(); + } elseif (isset($component->DURATION)) { + $endDate = clone $component->DTSTART->getDateTime(); + $endDate->add(VObject\DateTimeParser::parse($component->DURATION->getValue())); + $lastOccurence = $endDate->getTimeStamp(); + } elseif (!$component->DTSTART->hasTime()) { + $endDate = clone $component->DTSTART->getDateTime(); + $endDate->modify('+1 day'); + $lastOccurence = $endDate->getTimeStamp(); + } else { + $lastOccurence = $firstOccurence; + } + } else { + $it = new VObject\RecurrenceIterator($vObject, (string)$component->UID); + $maxDate = new \DateTime(self::MAX_DATE); + if ($it->isInfinite()) { + $lastOccurence = $maxDate->getTimeStamp(); + } else { + $end = $it->getDtEnd(); + while($it->valid() && $end < $maxDate) { + $end = $it->getDtEnd(); + $it->next(); + + } + $lastOccurence = $end->getTimeStamp(); + } + + } + } + + return array( + 'etag' => md5($calendarData), + 'size' => strlen($calendarData), + 'componentType' => $componentType, + 'firstOccurence' => $firstOccurence, + 'lastOccurence' => $lastOccurence, + ); + + } + + /** + * Deletes an existing calendar object. + * + * @param string $calendarId + * @param string $objectUri + * @return void + */ + public function deleteCalendarObject($calendarId,$objectUri) { + + $stmt = $this->pdo->prepare('DELETE FROM '.$this->calendarObjectTableName.' WHERE calendarid = ? AND uri = ?'); + $stmt->execute(array($calendarId,$objectUri)); + $stmt = $this->pdo->prepare('UPDATE '. $this->calendarTableName .' SET ctag = ctag + 1 WHERE id = ?'); + $stmt->execute(array($calendarId)); + + } + + /** + * Performs a calendar-query on the contents of this calendar. + * + * The calendar-query is defined in RFC4791 : CalDAV. Using the + * calendar-query it is possible for a client to request a specific set of + * object, based on contents of iCalendar properties, date-ranges and + * iCalendar component types (VTODO, VEVENT). + * + * This method should just return a list of (relative) urls that match this + * query. + * + * The list of filters are specified as an array. The exact array is + * documented by \OldSabre\CalDAV\CalendarQueryParser. + * + * Note that it is extremely likely that getCalendarObject for every path + * returned from this method will be called almost immediately after. You + * may want to anticipate this to speed up these requests. + * + * This method provides a default implementation, which parses *all* the + * iCalendar objects in the specified calendar. + * + * This default may well be good enough for personal use, and calendars + * that aren't very large. But if you anticipate high usage, big calendars + * or high loads, you are strongly adviced to optimize certain paths. + * + * The best way to do so is override this method and to optimize + * specifically for 'common filters'. + * + * Requests that are extremely common are: + * * requests for just VEVENTS + * * requests for just VTODO + * * requests with a time-range-filter on a VEVENT. + * + * ..and combinations of these requests. It may not be worth it to try to + * handle every possible situation and just rely on the (relatively + * easy to use) CalendarQueryValidator to handle the rest. + * + * Note that especially time-range-filters may be difficult to parse. A + * time-range filter specified on a VEVENT must for instance also handle + * recurrence rules correctly. + * A good example of how to interprete all these filters can also simply + * be found in \OldSabre\CalDAV\CalendarQueryFilter. This class is as correct + * as possible, so it gives you a good idea on what type of stuff you need + * to think of. + * + * This specific implementation (for the PDO) backend optimizes filters on + * specific components, and VEVENT time-ranges. + * + * @param string $calendarId + * @param array $filters + * @return array + */ + public function calendarQuery($calendarId, array $filters) { + + $result = array(); + $validator = new \OldSabre\CalDAV\CalendarQueryValidator(); + + $componentType = null; + $requirePostFilter = true; + $timeRange = null; + + // if no filters were specified, we don't need to filter after a query + if (!$filters['prop-filters'] && !$filters['comp-filters']) { + $requirePostFilter = false; + } + + // Figuring out if there's a component filter + if (count($filters['comp-filters']) > 0 && !$filters['comp-filters'][0]['is-not-defined']) { + $componentType = $filters['comp-filters'][0]['name']; + + // Checking if we need post-filters + if (!$filters['prop-filters'] && !$filters['comp-filters'][0]['comp-filters'] && !$filters['comp-filters'][0]['time-range'] && !$filters['comp-filters'][0]['prop-filters']) { + $requirePostFilter = false; + } + // There was a time-range filter + if ($componentType == 'VEVENT' && isset($filters['comp-filters'][0]['time-range'])) { + $timeRange = $filters['comp-filters'][0]['time-range']; + + // If start time OR the end time is not specified, we can do a + // 100% accurate mysql query. + if (!$filters['prop-filters'] && !$filters['comp-filters'][0]['comp-filters'] && !$filters['comp-filters'][0]['prop-filters'] && (!$timeRange['start'] || !$timeRange['end'])) { + $requirePostFilter = false; + } + } + + } + + if ($requirePostFilter) { + $query = "SELECT uri, calendardata FROM ".$this->calendarObjectTableName." WHERE calendarid = :calendarid"; + } else { + $query = "SELECT uri FROM ".$this->calendarObjectTableName." WHERE calendarid = :calendarid"; + } + + $values = array( + 'calendarid' => $calendarId, + ); + + if ($componentType) { + $query.=" AND componenttype = :componenttype"; + $values['componenttype'] = $componentType; + } + + if ($timeRange && $timeRange['start']) { + $query.=" AND lastoccurence > :startdate"; + $values['startdate'] = $timeRange['start']->getTimeStamp(); + } + if ($timeRange && $timeRange['end']) { + $query.=" AND firstoccurence < :enddate"; + $values['enddate'] = $timeRange['end']->getTimeStamp(); + } + + $stmt = $this->pdo->prepare($query); + $stmt->execute($values); + + $result = array(); + while($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { + if ($requirePostFilter) { + if (!$this->validateFilterForObject($row, $filters)) { + continue; + } + } + $result[] = $row['uri']; + + } + + return $result; + + } +} |