1<?php 2 3namespace Sabre\CalDAV\Backend; 4 5use Sabre\VObject; 6use Sabre\CalDAV; 7use Sabre\DAV; 8 9/** 10 * PDO CalDAV backend 11 * 12 * This backend is used to store calendar-data in a PDO database, such as 13 * sqlite or MySQL 14 * 15 * @copyright Copyright (C) 2007-2015 fruux GmbH (https://fruux.com/). 16 * @author Evert Pot (http://evertpot.com/) 17 * @license http://sabre.io/license/ Modified BSD License 18 */ 19class PDO extends AbstractBackend { 20 21 /** 22 * We need to specify a max date, because we need to stop *somewhere* 23 * 24 * On 32 bit system the maximum for a signed integer is 2147483647, so 25 * MAX_DATE cannot be higher than date('Y-m-d', 2147483647) which results 26 * in 2038-01-19 to avoid problems when the date is converted 27 * to a unix timestamp. 28 */ 29 const MAX_DATE = '2038-01-01'; 30 31 /** 32 * pdo 33 * 34 * @var \PDO 35 */ 36 protected $pdo; 37 38 /** 39 * The table name that will be used for calendars 40 * 41 * @var string 42 */ 43 protected $calendarTableName; 44 45 /** 46 * The table name that will be used for calendar objects 47 * 48 * @var string 49 */ 50 protected $calendarObjectTableName; 51 52 /** 53 * List of CalDAV properties, and how they map to database fieldnames 54 * Add your own properties by simply adding on to this array. 55 * 56 * Note that only string-based properties are supported here. 57 * 58 * @var array 59 */ 60 public $propertyMap = array( 61 '{DAV:}displayname' => 'displayname', 62 '{urn:ietf:params:xml:ns:caldav}calendar-description' => 'description', 63 '{urn:ietf:params:xml:ns:caldav}calendar-timezone' => 'timezone', 64 '{http://apple.com/ns/ical/}calendar-order' => 'calendarorder', 65 '{http://apple.com/ns/ical/}calendar-color' => 'calendarcolor', 66 ); 67 68 /** 69 * Creates the backend 70 * 71 * @param \PDO $pdo 72 * @param string $calendarTableName 73 * @param string $calendarObjectTableName 74 */ 75 public function __construct(\PDO $pdo, $calendarTableName = 'calendars', $calendarObjectTableName = 'calendarobjects') { 76 77 $this->pdo = $pdo; 78 $this->calendarTableName = $calendarTableName; 79 $this->calendarObjectTableName = $calendarObjectTableName; 80 81 } 82 83 /** 84 * Returns a list of calendars for a principal. 85 * 86 * Every project is an array with the following keys: 87 * * id, a unique id that will be used by other functions to modify the 88 * calendar. This can be the same as the uri or a database key. 89 * * uri, which the basename of the uri with which the calendar is 90 * accessed. 91 * * principaluri. The owner of the calendar. Almost always the same as 92 * principalUri passed to this method. 93 * 94 * Furthermore it can contain webdav properties in clark notation. A very 95 * common one is '{DAV:}displayname'. 96 * 97 * @param string $principalUri 98 * @return array 99 */ 100 public function getCalendarsForUser($principalUri) { 101 102 $fields = array_values($this->propertyMap); 103 $fields[] = 'id'; 104 $fields[] = 'uri'; 105 $fields[] = 'ctag'; 106 $fields[] = 'components'; 107 $fields[] = 'principaluri'; 108 $fields[] = 'transparent'; 109 110 // Making fields a comma-delimited list 111 $fields = implode(', ', $fields); 112 $stmt = $this->pdo->prepare("SELECT " . $fields . " FROM ".$this->calendarTableName." WHERE principaluri = ? ORDER BY calendarorder ASC"); 113 $stmt->execute(array($principalUri)); 114 115 $calendars = array(); 116 while($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { 117 118 $components = array(); 119 if ($row['components']) { 120 $components = explode(',',$row['components']); 121 } 122 123 $calendar = array( 124 'id' => $row['id'], 125 'uri' => $row['uri'], 126 'principaluri' => $row['principaluri'], 127 '{' . CalDAV\Plugin::NS_CALENDARSERVER . '}getctag' => $row['ctag']?$row['ctag']:'0', 128 '{' . CalDAV\Plugin::NS_CALDAV . '}supported-calendar-component-set' => new CalDAV\Property\SupportedCalendarComponentSet($components), 129 '{' . CalDAV\Plugin::NS_CALDAV . '}schedule-calendar-transp' => new CalDAV\Property\ScheduleCalendarTransp($row['transparent']?'transparent':'opaque'), 130 ); 131 132 133 foreach($this->propertyMap as $xmlName=>$dbName) { 134 $calendar[$xmlName] = $row[$dbName]; 135 } 136 137 $calendars[] = $calendar; 138 139 } 140 141 return $calendars; 142 143 } 144 145 /** 146 * Creates a new calendar for a principal. 147 * 148 * If the creation was a success, an id must be returned that can be used to reference 149 * this calendar in other methods, such as updateCalendar 150 * 151 * @param string $principalUri 152 * @param string $calendarUri 153 * @param array $properties 154 * @return string 155 */ 156 public function createCalendar($principalUri, $calendarUri, array $properties) { 157 158 $fieldNames = array( 159 'principaluri', 160 'uri', 161 'ctag', 162 'transparent', 163 ); 164 $values = array( 165 ':principaluri' => $principalUri, 166 ':uri' => $calendarUri, 167 ':ctag' => 1, 168 ':transparent' => 0, 169 ); 170 171 // Default value 172 $sccs = '{urn:ietf:params:xml:ns:caldav}supported-calendar-component-set'; 173 $fieldNames[] = 'components'; 174 if (!isset($properties[$sccs])) { 175 $values[':components'] = 'VEVENT,VTODO'; 176 } else { 177 if (!($properties[$sccs] instanceof CalDAV\Property\SupportedCalendarComponentSet)) { 178 throw new DAV\Exception('The ' . $sccs . ' property must be of type: \Sabre\CalDAV\Property\SupportedCalendarComponentSet'); 179 } 180 $values[':components'] = implode(',',$properties[$sccs]->getValue()); 181 } 182 $transp = '{' . CalDAV\Plugin::NS_CALDAV . '}schedule-calendar-transp'; 183 if (isset($properties[$transp])) { 184 $values[':transparent'] = $properties[$transp]->getValue()==='transparent'; 185 } 186 187 foreach($this->propertyMap as $xmlName=>$dbName) { 188 if (isset($properties[$xmlName])) { 189 190 $values[':' . $dbName] = $properties[$xmlName]; 191 $fieldNames[] = $dbName; 192 } 193 } 194 195 $stmt = $this->pdo->prepare("INSERT INTO ".$this->calendarTableName." (".implode(', ', $fieldNames).") VALUES (".implode(', ',array_keys($values)).")"); 196 $stmt->execute($values); 197 198 return $this->pdo->lastInsertId(); 199 200 } 201 202 /** 203 * Updates properties for a calendar. 204 * 205 * The mutations array uses the propertyName in clark-notation as key, 206 * and the array value for the property value. In the case a property 207 * should be deleted, the property value will be null. 208 * 209 * This method must be atomic. If one property cannot be changed, the 210 * entire operation must fail. 211 * 212 * If the operation was successful, true can be returned. 213 * If the operation failed, false can be returned. 214 * 215 * Deletion of a non-existent property is always successful. 216 * 217 * Lastly, it is optional to return detailed information about any 218 * failures. In this case an array should be returned with the following 219 * structure: 220 * 221 * array( 222 * 403 => array( 223 * '{DAV:}displayname' => null, 224 * ), 225 * 424 => array( 226 * '{DAV:}owner' => null, 227 * ) 228 * ) 229 * 230 * In this example it was forbidden to update {DAV:}displayname. 231 * (403 Forbidden), which in turn also caused {DAV:}owner to fail 232 * (424 Failed Dependency) because the request needs to be atomic. 233 * 234 * @param string $calendarId 235 * @param array $mutations 236 * @return bool|array 237 */ 238 public function updateCalendar($calendarId, array $mutations) { 239 240 $newValues = array(); 241 $result = array( 242 200 => array(), // Ok 243 403 => array(), // Forbidden 244 424 => array(), // Failed Dependency 245 ); 246 247 $hasError = false; 248 249 foreach($mutations as $propertyName=>$propertyValue) { 250 251 switch($propertyName) { 252 case '{' . CalDAV\Plugin::NS_CALDAV . '}schedule-calendar-transp' : 253 $fieldName = 'transparent'; 254 $newValues[$fieldName] = $propertyValue->getValue()==='transparent'; 255 break; 256 default : 257 // Checking the property map 258 if (!isset($this->propertyMap[$propertyName])) { 259 // We don't know about this property. 260 $hasError = true; 261 $result[403][$propertyName] = null; 262 unset($mutations[$propertyName]); 263 continue; 264 } 265 266 $fieldName = $this->propertyMap[$propertyName]; 267 $newValues[$fieldName] = $propertyValue; 268 } 269 270 } 271 272 // If there were any errors we need to fail the request 273 if ($hasError) { 274 // Properties has the remaining properties 275 foreach($mutations as $propertyName=>$propertyValue) { 276 $result[424][$propertyName] = null; 277 } 278 279 // Removing unused statuscodes for cleanliness 280 foreach($result as $status=>$properties) { 281 if (is_array($properties) && count($properties)===0) unset($result[$status]); 282 } 283 284 return $result; 285 286 } 287 288 // Success 289 290 // Now we're generating the sql query. 291 $valuesSql = array(); 292 foreach($newValues as $fieldName=>$value) { 293 $valuesSql[] = $fieldName . ' = ?'; 294 } 295 $valuesSql[] = 'ctag = ctag + 1'; 296 297 $stmt = $this->pdo->prepare("UPDATE " . $this->calendarTableName . " SET " . implode(', ',$valuesSql) . " WHERE id = ?"); 298 $newValues['id'] = $calendarId; 299 $stmt->execute(array_values($newValues)); 300 301 return true; 302 303 } 304 305 /** 306 * Delete a calendar and all it's objects 307 * 308 * @param string $calendarId 309 * @return void 310 */ 311 public function deleteCalendar($calendarId) { 312 313 $stmt = $this->pdo->prepare('DELETE FROM '.$this->calendarObjectTableName.' WHERE calendarid = ?'); 314 $stmt->execute(array($calendarId)); 315 316 $stmt = $this->pdo->prepare('DELETE FROM '.$this->calendarTableName.' WHERE id = ?'); 317 $stmt->execute(array($calendarId)); 318 319 } 320 321 /** 322 * Returns all calendar objects within a calendar. 323 * 324 * Every item contains an array with the following keys: 325 * * id - unique identifier which will be used for subsequent updates 326 * * calendardata - The iCalendar-compatible calendar data 327 * * uri - a unique key which will be used to construct the uri. This can be any arbitrary string. 328 * * lastmodified - a timestamp of the last modification time 329 * * etag - An arbitrary string, surrounded by double-quotes. (e.g.: 330 * ' "abcdef"') 331 * * calendarid - The calendarid as it was passed to this function. 332 * * size - The size of the calendar objects, in bytes. 333 * 334 * Note that the etag is optional, but it's highly encouraged to return for 335 * speed reasons. 336 * 337 * The calendardata is also optional. If it's not returned 338 * 'getCalendarObject' will be called later, which *is* expected to return 339 * calendardata. 340 * 341 * If neither etag or size are specified, the calendardata will be 342 * used/fetched to determine these numbers. If both are specified the 343 * amount of times this is needed is reduced by a great degree. 344 * 345 * @param string $calendarId 346 * @return array 347 */ 348 public function getCalendarObjects($calendarId) { 349 350 $stmt = $this->pdo->prepare('SELECT id, uri, lastmodified, etag, calendarid, size FROM '.$this->calendarObjectTableName.' WHERE calendarid = ?'); 351 $stmt->execute(array($calendarId)); 352 353 $result = array(); 354 foreach($stmt->fetchAll(\PDO::FETCH_ASSOC) as $row) { 355 $result[] = array( 356 'id' => $row['id'], 357 'uri' => $row['uri'], 358 'lastmodified' => $row['lastmodified'], 359 'etag' => '"' . $row['etag'] . '"', 360 'calendarid' => $row['calendarid'], 361 'size' => (int)$row['size'], 362 ); 363 } 364 365 return $result; 366 367 } 368 369 /** 370 * Returns information from a single calendar object, based on it's object 371 * uri. 372 * 373 * The returned array must have the same keys as getCalendarObjects. The 374 * 'calendardata' object is required here though, while it's not required 375 * for getCalendarObjects. 376 * 377 * This method must return null if the object did not exist. 378 * 379 * @param string $calendarId 380 * @param string $objectUri 381 * @return array|null 382 */ 383 public function getCalendarObject($calendarId,$objectUri) { 384 385 $stmt = $this->pdo->prepare('SELECT id, uri, lastmodified, etag, calendarid, size, calendardata FROM '.$this->calendarObjectTableName.' WHERE calendarid = ? AND uri = ?'); 386 $stmt->execute(array($calendarId, $objectUri)); 387 $row = $stmt->fetch(\PDO::FETCH_ASSOC); 388 389 if(!$row) return null; 390 391 return array( 392 'id' => $row['id'], 393 'uri' => $row['uri'], 394 'lastmodified' => $row['lastmodified'], 395 'etag' => '"' . $row['etag'] . '"', 396 'calendarid' => $row['calendarid'], 397 'size' => (int)$row['size'], 398 'calendardata' => $row['calendardata'], 399 ); 400 401 } 402 403 404 /** 405 * Creates a new calendar object. 406 * 407 * It is possible return an etag from this function, which will be used in 408 * the response to this PUT request. Note that the ETag must be surrounded 409 * by double-quotes. 410 * 411 * However, you should only really return this ETag if you don't mangle the 412 * calendar-data. If the result of a subsequent GET to this object is not 413 * the exact same as this request body, you should omit the ETag. 414 * 415 * @param mixed $calendarId 416 * @param string $objectUri 417 * @param string $calendarData 418 * @return string|null 419 */ 420 public function createCalendarObject($calendarId,$objectUri,$calendarData) { 421 422 $extraData = $this->getDenormalizedData($calendarData); 423 424 $stmt = $this->pdo->prepare('INSERT INTO '.$this->calendarObjectTableName.' (calendarid, uri, calendardata, lastmodified, etag, size, componenttype, firstoccurence, lastoccurence) VALUES (?,?,?,?,?,?,?,?,?)'); 425 $stmt->execute(array( 426 $calendarId, 427 $objectUri, 428 $calendarData, 429 time(), 430 $extraData['etag'], 431 $extraData['size'], 432 $extraData['componentType'], 433 $extraData['firstOccurence'], 434 $extraData['lastOccurence'], 435 )); 436 $stmt = $this->pdo->prepare('UPDATE '.$this->calendarTableName.' SET ctag = ctag + 1 WHERE id = ?'); 437 $stmt->execute(array($calendarId)); 438 439 return '"' . $extraData['etag'] . '"'; 440 441 } 442 443 /** 444 * Updates an existing calendarobject, based on it's uri. 445 * 446 * It is possible return an etag from this function, which will be used in 447 * the response to this PUT request. Note that the ETag must be surrounded 448 * by double-quotes. 449 * 450 * However, you should only really return this ETag if you don't mangle the 451 * calendar-data. If the result of a subsequent GET to this object is not 452 * the exact same as this request body, you should omit the ETag. 453 * 454 * @param mixed $calendarId 455 * @param string $objectUri 456 * @param string $calendarData 457 * @return string|null 458 */ 459 public function updateCalendarObject($calendarId,$objectUri,$calendarData) { 460 461 $extraData = $this->getDenormalizedData($calendarData); 462 463 $stmt = $this->pdo->prepare('UPDATE '.$this->calendarObjectTableName.' SET calendardata = ?, lastmodified = ?, etag = ?, size = ?, componenttype = ?, firstoccurence = ?, lastoccurence = ? WHERE calendarid = ? AND uri = ?'); 464 $stmt->execute(array($calendarData,time(), $extraData['etag'], $extraData['size'], $extraData['componentType'], $extraData['firstOccurence'], $extraData['lastOccurence'] ,$calendarId,$objectUri)); 465 $stmt = $this->pdo->prepare('UPDATE '.$this->calendarTableName.' SET ctag = ctag + 1 WHERE id = ?'); 466 $stmt->execute(array($calendarId)); 467 468 return '"' . $extraData['etag'] . '"'; 469 470 } 471 472 /** 473 * Parses some information from calendar objects, used for optimized 474 * calendar-queries. 475 * 476 * Returns an array with the following keys: 477 * * etag 478 * * size 479 * * componentType 480 * * firstOccurence 481 * * lastOccurence 482 * 483 * @param string $calendarData 484 * @return array 485 */ 486 protected function getDenormalizedData($calendarData) { 487 488 $vObject = VObject\Reader::read($calendarData); 489 $componentType = null; 490 $component = null; 491 $firstOccurence = null; 492 $lastOccurence = null; 493 foreach($vObject->getComponents() as $component) { 494 if ($component->name!=='VTIMEZONE') { 495 $componentType = $component->name; 496 break; 497 } 498 } 499 if (!$componentType) { 500 throw new \Sabre\DAV\Exception\BadRequest('Calendar objects must have a VJOURNAL, VEVENT or VTODO component'); 501 } 502 if ($componentType === 'VEVENT') { 503 $firstOccurence = $component->DTSTART->getDateTime()->getTimeStamp(); 504 // Finding the last occurence is a bit harder 505 if (!isset($component->RRULE)) { 506 if (isset($component->DTEND)) { 507 $lastOccurence = $component->DTEND->getDateTime()->getTimeStamp(); 508 } elseif (isset($component->DURATION)) { 509 $endDate = clone $component->DTSTART->getDateTime(); 510 $endDate->add(VObject\DateTimeParser::parse($component->DURATION->getValue())); 511 $lastOccurence = $endDate->getTimeStamp(); 512 } elseif (!$component->DTSTART->hasTime()) { 513 $endDate = clone $component->DTSTART->getDateTime(); 514 $endDate->modify('+1 day'); 515 $lastOccurence = $endDate->getTimeStamp(); 516 } else { 517 $lastOccurence = $firstOccurence; 518 } 519 } else { 520 $it = new VObject\RecurrenceIterator($vObject, (string)$component->UID); 521 $maxDate = new \DateTime(self::MAX_DATE); 522 if ($it->isInfinite()) { 523 $lastOccurence = $maxDate->getTimeStamp(); 524 } else { 525 $end = $it->getDtEnd(); 526 while($it->valid() && $end < $maxDate) { 527 $end = $it->getDtEnd(); 528 $it->next(); 529 530 } 531 $lastOccurence = $end->getTimeStamp(); 532 } 533 534 } 535 } 536 537 return array( 538 'etag' => md5($calendarData), 539 'size' => strlen($calendarData), 540 'componentType' => $componentType, 541 'firstOccurence' => $firstOccurence, 542 'lastOccurence' => $lastOccurence, 543 ); 544 545 } 546 547 /** 548 * Deletes an existing calendar object. 549 * 550 * @param string $calendarId 551 * @param string $objectUri 552 * @return void 553 */ 554 public function deleteCalendarObject($calendarId,$objectUri) { 555 556 $stmt = $this->pdo->prepare('DELETE FROM '.$this->calendarObjectTableName.' WHERE calendarid = ? AND uri = ?'); 557 $stmt->execute(array($calendarId,$objectUri)); 558 $stmt = $this->pdo->prepare('UPDATE '. $this->calendarTableName .' SET ctag = ctag + 1 WHERE id = ?'); 559 $stmt->execute(array($calendarId)); 560 561 } 562 563 /** 564 * Performs a calendar-query on the contents of this calendar. 565 * 566 * The calendar-query is defined in RFC4791 : CalDAV. Using the 567 * calendar-query it is possible for a client to request a specific set of 568 * object, based on contents of iCalendar properties, date-ranges and 569 * iCalendar component types (VTODO, VEVENT). 570 * 571 * This method should just return a list of (relative) urls that match this 572 * query. 573 * 574 * The list of filters are specified as an array. The exact array is 575 * documented by \Sabre\CalDAV\CalendarQueryParser. 576 * 577 * Note that it is extremely likely that getCalendarObject for every path 578 * returned from this method will be called almost immediately after. You 579 * may want to anticipate this to speed up these requests. 580 * 581 * This method provides a default implementation, which parses *all* the 582 * iCalendar objects in the specified calendar. 583 * 584 * This default may well be good enough for personal use, and calendars 585 * that aren't very large. But if you anticipate high usage, big calendars 586 * or high loads, you are strongly adviced to optimize certain paths. 587 * 588 * The best way to do so is override this method and to optimize 589 * specifically for 'common filters'. 590 * 591 * Requests that are extremely common are: 592 * * requests for just VEVENTS 593 * * requests for just VTODO 594 * * requests with a time-range-filter on a VEVENT. 595 * 596 * ..and combinations of these requests. It may not be worth it to try to 597 * handle every possible situation and just rely on the (relatively 598 * easy to use) CalendarQueryValidator to handle the rest. 599 * 600 * Note that especially time-range-filters may be difficult to parse. A 601 * time-range filter specified on a VEVENT must for instance also handle 602 * recurrence rules correctly. 603 * A good example of how to interprete all these filters can also simply 604 * be found in \Sabre\CalDAV\CalendarQueryFilter. This class is as correct 605 * as possible, so it gives you a good idea on what type of stuff you need 606 * to think of. 607 * 608 * This specific implementation (for the PDO) backend optimizes filters on 609 * specific components, and VEVENT time-ranges. 610 * 611 * @param string $calendarId 612 * @param array $filters 613 * @return array 614 */ 615 public function calendarQuery($calendarId, array $filters) { 616 617 $result = array(); 618 $validator = new \Sabre\CalDAV\CalendarQueryValidator(); 619 620 $componentType = null; 621 $requirePostFilter = true; 622 $timeRange = null; 623 624 // if no filters were specified, we don't need to filter after a query 625 if (!$filters['prop-filters'] && !$filters['comp-filters']) { 626 $requirePostFilter = false; 627 } 628 629 // Figuring out if there's a component filter 630 if (count($filters['comp-filters']) > 0 && !$filters['comp-filters'][0]['is-not-defined']) { 631 $componentType = $filters['comp-filters'][0]['name']; 632 633 // Checking if we need post-filters 634 if (!$filters['prop-filters'] && !$filters['comp-filters'][0]['comp-filters'] && !$filters['comp-filters'][0]['time-range'] && !$filters['comp-filters'][0]['prop-filters']) { 635 $requirePostFilter = false; 636 } 637 // There was a time-range filter 638 if ($componentType == 'VEVENT' && isset($filters['comp-filters'][0]['time-range'])) { 639 $timeRange = $filters['comp-filters'][0]['time-range']; 640 641 // If start time OR the end time is not specified, we can do a 642 // 100% accurate mysql query. 643 if (!$filters['prop-filters'] && !$filters['comp-filters'][0]['comp-filters'] && !$filters['comp-filters'][0]['prop-filters'] && (!$timeRange['start'] || !$timeRange['end'])) { 644 $requirePostFilter = false; 645 } 646 } 647 648 } 649 650 if ($requirePostFilter) { 651 $query = "SELECT uri, calendardata FROM ".$this->calendarObjectTableName." WHERE calendarid = :calendarid"; 652 } else { 653 $query = "SELECT uri FROM ".$this->calendarObjectTableName." WHERE calendarid = :calendarid"; 654 } 655 656 $values = array( 657 'calendarid' => $calendarId, 658 ); 659 660 if ($componentType) { 661 $query.=" AND componenttype = :componenttype"; 662 $values['componenttype'] = $componentType; 663 } 664 665 if ($timeRange && $timeRange['start']) { 666 $query.=" AND lastoccurence > :startdate"; 667 $values['startdate'] = $timeRange['start']->getTimeStamp(); 668 } 669 if ($timeRange && $timeRange['end']) { 670 $query.=" AND firstoccurence < :enddate"; 671 $values['enddate'] = $timeRange['end']->getTimeStamp(); 672 } 673 674 $stmt = $this->pdo->prepare($query); 675 $stmt->execute($values); 676 677 $result = array(); 678 while($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { 679 if ($requirePostFilter) { 680 if (!$this->validateFilterForObject($row, $filters)) { 681 continue; 682 } 683 } 684 $result[] = $row['uri']; 685 686 } 687 688 return $result; 689 690 } 691} 692