1<?php 2 3use \Sabre\VObject; 4use \Sabre\CalDAV; 5use \Sabre\DAV; 6use \Sabre\DAV\Exception\Forbidden; 7/** 8 * PDO CalDAV backend for DokuWiki - based on Sabre's CalDAV backend 9 * 10 * This backend is used to store calendar-data in a PDO database, such as 11 * sqlite or MySQL 12 * 13 * @copyright Copyright (C) 2007-2015 fruux GmbH (https://fruux.com/). 14 * @author Evert Pot (http://evertpot.com/) 15 * @license http://sabre.io/license/ Modified BSD License 16 */ 17class DokuWikiSabreCalendarBackend extends \Sabre\CalDAV\Backend\AbstractBackend { 18 19 /** 20 * We need to specify a max date, because we need to stop *somewhere* 21 * 22 * On 32 bit system the maximum for a signed integer is 2147483647, so 23 * MAX_DATE cannot be higher than date('Y-m-d', 2147483647) which results 24 * in 2038-01-19 to avoid problems when the date is converted 25 * to a unix timestamp. 26 */ 27 const MAX_DATE = '2038-01-01'; 28 29 /** 30 * pdo 31 * 32 * @var \PDO 33 */ 34 protected $pdo; 35 36 /** 37 * DokuWiki PlugIn Helper 38 */ 39 protected $hlp = null; 40 /** 41 * The table name that will be used for calendars 42 * 43 * @var string 44 */ 45 public $calendarTableName = 'calendars'; 46 47 48 /** 49 * The table name that will be used for DokuWiki Calendar-Page mapping 50 * 51 * @var string 52 */ 53 public $calendarDokuwikiMappingTableName = 'pagetocalendarmapping'; 54 /** 55 * The table name that will be used for calendar objects 56 * 57 * @var string 58 */ 59 public $calendarObjectTableName = 'calendarobjects'; 60 61 /** 62 * The table name that will be used for tracking changes in calendars. 63 * 64 * @var string 65 */ 66 public $calendarChangesTableName = 'calendarchanges'; 67 68 /** 69 * The table name that will be used inbox items. 70 * 71 * @var string 72 */ 73 public $schedulingObjectTableName = 'schedulingobjects'; 74 75 /** 76 * The table name that will be used for calendar subscriptions. 77 * 78 * @var string 79 */ 80 public $calendarSubscriptionsTableName = 'calendarsubscriptions'; 81 82 /** 83 * List of CalDAV properties, and how they map to database fieldnames 84 * Add your own properties by simply adding on to this array. 85 * 86 * Note that only string-based properties are supported here. 87 * 88 * @var array 89 */ 90 public $propertyMap = [ 91 '{DAV:}displayname' => 'displayname', 92 '{urn:ietf:params:xml:ns:caldav}calendar-description' => 'description', 93 '{urn:ietf:params:xml:ns:caldav}calendar-timezone' => 'timezone', 94 '{http://apple.com/ns/ical/}calendar-order' => 'calendarorder', 95 '{http://apple.com/ns/ical/}calendar-color' => 'calendarcolor', 96 ]; 97 98 /** 99 * List of subscription properties, and how they map to database fieldnames. 100 * 101 * @var array 102 */ 103 public $subscriptionPropertyMap = [ 104 '{DAV:}displayname' => 'displayname', 105 '{http://apple.com/ns/ical/}refreshrate' => 'refreshrate', 106 '{http://apple.com/ns/ical/}calendar-order' => 'calendarorder', 107 '{http://apple.com/ns/ical/}calendar-color' => 'calendarcolor', 108 '{http://calendarserver.org/ns/}subscribed-strip-todos' => 'striptodos', 109 '{http://calendarserver.org/ns/}subscribed-strip-alarms' => 'stripalarms', 110 '{http://calendarserver.org/ns/}subscribed-strip-attachments' => 'stripattachments', 111 ]; 112 113 /** 114 * Creates the backend 115 * 116 * @param \PDO $pdo 117 */ 118 function __construct(\PDO $pdo) { 119 120 $this->pdo = $pdo; 121 $this->hlp =& plugin_load('helper', 'davcal'); 122 123 } 124 125 /** 126 * Returns a list of calendars for a principal. 127 * 128 * Every project is an array with the following keys: 129 * * id, a unique id that will be used by other functions to modify the 130 * calendar. This can be the same as the uri or a database key. 131 * * uri. This is just the 'base uri' or 'filename' of the calendar. 132 * * principaluri. The owner of the calendar. Almost always the same as 133 * principalUri passed to this method. 134 * 135 * Furthermore it can contain webdav properties in clark notation. A very 136 * common one is '{DAV:}displayname'. 137 * 138 * Many clients also require: 139 * {urn:ietf:params:xml:ns:caldav}supported-calendar-component-set 140 * For this property, you can just return an instance of 141 * Sabre\CalDAV\Xml\Property\SupportedCalendarComponentSet. 142 * 143 * If you return {http://sabredav.org/ns}read-only and set the value to 1, 144 * ACL will automatically be put in read-only mode. 145 * 146 * @param string $principalUri 147 * @return array 148 */ 149 function getCalendarsForUser($principalUri) { 150 151 $fields = array_values($this->propertyMap); 152 $fields[] = 'id'; 153 $fields[] = 'uri'; 154 $fields[] = 'synctoken'; 155 $fields[] = 'components'; 156 $fields[] = 'principaluri'; 157 $fields[] = 'transparent'; 158 159 $idInfo = $this->hlp->getCalendarIdsForUser($principalUri); 160 $idFilter = array_keys($idInfo); 161 // Making fields a comma-delimited list 162 $fields = implode(', ', $fields); 163 $stmt = $this->pdo->prepare("SELECT " . $fields . " FROM " . $this->calendarTableName . " ORDER BY calendarorder ASC"); 164 $stmt->execute(); 165 166 $calendars = []; 167 while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { 168 // Filter Calendars by the array returned by the DokuWiki Auth system 169 if(array_search($row['id'], $idFilter) === false) 170 continue; 171 $components = []; 172 if ($row['components']) { 173 $components = explode(',', $row['components']); 174 } 175 176 $calendar = [ 177 'id' => $row['id'], 178 'uri' => $row['uri'], 179 'principaluri' => $principalUri,//$row['principaluri'], // Overwrite principaluri from database, we actually don't need it. 180 '{' . CalDAV\Plugin::NS_CALENDARSERVER . '}getctag' => 'http://sabre.io/ns/sync/' . ($row['synctoken'] ? $row['synctoken'] : '0'), 181 '{http://sabredav.org/ns}sync-token' => $row['synctoken'] ? $row['synctoken'] : '0', 182 '{' . CalDAV\Plugin::NS_CALDAV . '}supported-calendar-component-set' => new CalDAV\Xml\Property\SupportedCalendarComponentSet($components), 183 '{' . CalDAV\Plugin::NS_CALDAV . '}schedule-calendar-transp' => new CalDAV\Xml\Property\ScheduleCalendarTransp($row['transparent'] ? 'transparent' : 'opaque'), 184 ]; 185 if($idInfo[$row['id']]['readonly'] === true) 186 $calendar['{http://sabredav.org/ns}read-only'] = '1'; 187 188 189 foreach ($this->propertyMap as $xmlName => $dbName) { 190 $calendar[$xmlName] = $row[$dbName]; 191 } 192 193 $calendars[] = $calendar; 194 195 } 196 197 return $calendars; 198 199 } 200 201 /** 202 * Creates a new calendar for a principal. 203 * 204 * If the creation was a success, an id must be returned that can be used 205 * to reference this calendar in other methods, such as updateCalendar. 206 * 207 * @param string $principalUri 208 * @param string $calendarUri 209 * @param array $properties 210 * @return string 211 */ 212 function createCalendar($principalUri, $calendarUri, array $properties) { 213 214 return false; 215 /* 216 $fieldNames = [ 217 'principaluri', 218 'uri', 219 'synctoken', 220 'transparent', 221 ]; 222 $values = [ 223 ':principaluri' => $principalUri, 224 ':uri' => $calendarUri, 225 ':synctoken' => 1, 226 ':transparent' => 0, 227 ]; 228 229 // Default value 230 $sccs = '{urn:ietf:params:xml:ns:caldav}supported-calendar-component-set'; 231 $fieldNames[] = 'components'; 232 if (!isset($properties[$sccs])) { 233 $values[':components'] = 'VEVENT,VTODO'; 234 } else { 235 if (!($properties[$sccs] instanceof CalDAV\Xml\Property\SupportedCalendarComponentSet)) { 236 throw new DAV\Exception('The ' . $sccs . ' property must be of type: \Sabre\CalDAV\Xml\Property\SupportedCalendarComponentSet'); 237 } 238 $values[':components'] = implode(',', $properties[$sccs]->getValue()); 239 } 240 $transp = '{' . CalDAV\Plugin::NS_CALDAV . '}schedule-calendar-transp'; 241 if (isset($properties[$transp])) { 242 $values[':transparent'] = $properties[$transp]->getValue() === 'transparent'; 243 } 244 245 foreach ($this->propertyMap as $xmlName => $dbName) { 246 if (isset($properties[$xmlName])) { 247 248 $values[':' . $dbName] = $properties[$xmlName]; 249 $fieldNames[] = $dbName; 250 } 251 } 252 253 $stmt = $this->pdo->prepare("INSERT INTO " . $this->calendarTableName . " (" . implode(', ', $fieldNames) . ") VALUES (" . implode(', ', array_keys($values)) . ")"); 254 $stmt->execute($values); 255 256 return $this->pdo->lastInsertId(); 257 */ 258 } 259 260 /** 261 * Updates properties for a calendar. 262 * 263 * The list of mutations is stored in a Sabre\DAV\PropPatch object. 264 * To do the actual updates, you must tell this object which properties 265 * you're going to process with the handle() method. 266 * 267 * Calling the handle method is like telling the PropPatch object "I 268 * promise I can handle updating this property". 269 * 270 * Read the PropPatch documenation for more info and examples. 271 * 272 * @param string $calendarId 273 * @param \Sabre\DAV\PropPatch $propPatch 274 * @return void 275 */ 276 function updateCalendar($calendarId, \Sabre\DAV\PropPatch $propPatch) { 277 278 $supportedProperties = array_keys($this->propertyMap); 279 $supportedProperties[] = '{' . CalDAV\Plugin::NS_CALDAV . '}schedule-calendar-transp'; 280 281 $propPatch->handle($supportedProperties, function($mutations) use ($calendarId) { 282 $newValues = []; 283 foreach ($mutations as $propertyName => $propertyValue) { 284 285 switch ($propertyName) { 286 case '{' . CalDAV\Plugin::NS_CALDAV . '}schedule-calendar-transp' : 287 $fieldName = 'transparent'; 288 $newValues[$fieldName] = $propertyValue->getValue() === 'transparent'; 289 break; 290 default : 291 $fieldName = $this->propertyMap[$propertyName]; 292 $newValues[$fieldName] = $propertyValue; 293 break; 294 } 295 296 } 297 $valuesSql = []; 298 foreach ($newValues as $fieldName => $value) { 299 $valuesSql[] = $fieldName . ' = ?'; 300 } 301 302 $stmt = $this->pdo->prepare("UPDATE " . $this->calendarTableName . " SET " . implode(', ', $valuesSql) . " WHERE id = ?"); 303 $newValues['id'] = $calendarId; 304 $stmt->execute(array_values($newValues)); 305 306 $this->addChange($calendarId, "", 2); 307 308 return true; 309 310 }); 311 312 } 313 314 /** 315 * Delete a calendar and all it's objects 316 * 317 * @param string $calendarId 318 * @return void 319 */ 320 function deleteCalendar($calendarId) { 321 322 /* 323 $stmt = $this->pdo->prepare('DELETE FROM ' . $this->calendarObjectTableName . ' WHERE calendarid = ?'); 324 $stmt->execute([$calendarId]); 325 326 $stmt = $this->pdo->prepare('DELETE FROM ' . $this->calendarTableName . ' WHERE id = ?'); 327 $stmt->execute([$calendarId]); 328 329 $stmt = $this->pdo->prepare('DELETE FROM ' . $this->calendarChangesTableName . ' WHERE calendarid = ?'); 330 $stmt->execute([$calendarId]); 331 */ 332 333 } 334 335 /** 336 * Returns all calendar objects within a calendar. 337 * 338 * Every item contains an array with the following keys: 339 * * calendardata - The iCalendar-compatible calendar data 340 * * uri - a unique key which will be used to construct the uri. This can 341 * be any arbitrary string, but making sure it ends with '.ics' is a 342 * good idea. This is only the basename, or filename, not the full 343 * path. 344 * * lastmodified - a timestamp of the last modification time 345 * * etag - An arbitrary string, surrounded by double-quotes. (e.g.: 346 * ' "abcdef"') 347 * * size - The size of the calendar objects, in bytes. 348 * * component - optional, a string containing the type of object, such 349 * as 'vevent' or 'vtodo'. If specified, this will be used to populate 350 * the Content-Type header. 351 * 352 * Note that the etag is optional, but it's highly encouraged to return for 353 * speed reasons. 354 * 355 * The calendardata is also optional. If it's not returned 356 * 'getCalendarObject' will be called later, which *is* expected to return 357 * calendardata. 358 * 359 * If neither etag or size are specified, the calendardata will be 360 * used/fetched to determine these numbers. If both are specified the 361 * amount of times this is needed is reduced by a great degree. 362 * 363 * @param string $calendarId 364 * @return array 365 */ 366 function getCalendarObjects($calendarId) { 367 368 $stmt = $this->pdo->prepare('SELECT id, uri, lastmodified, etag, calendarid, size, componenttype FROM ' . $this->calendarObjectTableName . ' WHERE calendarid = ?'); 369 $stmt->execute([$calendarId]); 370 371 $result = []; 372 foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) as $row) { 373 $result[] = [ 374 'id' => $row['id'], 375 'uri' => $row['uri'], 376 'lastmodified' => $row['lastmodified'], 377 'etag' => '"' . $row['etag'] . '"', 378 'calendarid' => $row['calendarid'], 379 'size' => (int)$row['size'], 380 'component' => strtolower($row['componenttype']), 381 ]; 382 } 383 384 return $result; 385 386 } 387 388 /** 389 * Returns information from a single calendar object, based on it's object 390 * uri. 391 * 392 * The object uri is only the basename, or filename and not a full path. 393 * 394 * The returned array must have the same keys as getCalendarObjects. The 395 * 'calendardata' object is required here though, while it's not required 396 * for getCalendarObjects. 397 * 398 * This method must return null if the object did not exist. 399 * 400 * @param string $calendarId 401 * @param string $objectUri 402 * @return array|null 403 */ 404 function getCalendarObject($calendarId, $objectUri) { 405 406 $stmt = $this->pdo->prepare('SELECT id, uri, lastmodified, etag, calendarid, size, calendardata, componenttype FROM ' . $this->calendarObjectTableName . ' WHERE calendarid = ? AND uri = ?'); 407 $stmt->execute([$calendarId, $objectUri]); 408 $row = $stmt->fetch(\PDO::FETCH_ASSOC); 409 410 if (!$row) return null; 411 412 return [ 413 'id' => $row['id'], 414 'uri' => $row['uri'], 415 'lastmodified' => $row['lastmodified'], 416 'etag' => '"' . $row['etag'] . '"', 417 'calendarid' => $row['calendarid'], 418 'size' => (int)$row['size'], 419 'calendardata' => $row['calendardata'], 420 'component' => strtolower($row['componenttype']), 421 ]; 422 423 } 424 425 /** 426 * Returns a list of calendar objects. 427 * 428 * This method should work identical to getCalendarObject, but instead 429 * return all the calendar objects in the list as an array. 430 * 431 * If the backend supports this, it may allow for some speed-ups. 432 * 433 * @param mixed $calendarId 434 * @param array $uris 435 * @return array 436 */ 437 function getMultipleCalendarObjects($calendarId, array $uris) { 438 439 $query = 'SELECT id, uri, lastmodified, etag, calendarid, size, calendardata, componenttype FROM ' . $this->calendarObjectTableName . ' WHERE calendarid = ? AND uri IN ('; 440 // Inserting a whole bunch of question marks 441 $query .= implode(',', array_fill(0, count($uris), '?')); 442 $query .= ')'; 443 444 $stmt = $this->pdo->prepare($query); 445 $stmt->execute(array_merge([$calendarId], $uris)); 446 447 $result = []; 448 while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { 449 450 $result[] = [ 451 'id' => $row['id'], 452 'uri' => $row['uri'], 453 'lastmodified' => $row['lastmodified'], 454 'etag' => '"' . $row['etag'] . '"', 455 'calendarid' => $row['calendarid'], 456 'size' => (int)$row['size'], 457 'calendardata' => $row['calendardata'], 458 'component' => strtolower($row['componenttype']), 459 ]; 460 461 } 462 return $result; 463 464 } 465 466 467 /** 468 * Creates a new calendar object. 469 * 470 * The object uri is only the basename, or filename and not a full path. 471 * 472 * It is possible return an etag from this function, which will be used in 473 * the response to this PUT request. Note that the ETag must be surrounded 474 * by double-quotes. 475 * 476 * However, you should only really return this ETag if you don't mangle the 477 * calendar-data. If the result of a subsequent GET to this object is not 478 * the exact same as this request body, you should omit the ETag. 479 * 480 * @param mixed $calendarId 481 * @param string $objectUri 482 * @param string $calendarData 483 * @return string|null 484 */ 485 function createCalendarObject($calendarId, $objectUri, $calendarData) { 486 487 $extraData = $this->getDenormalizedData($calendarData); 488 489 $stmt = $this->pdo->prepare('INSERT INTO ' . $this->calendarObjectTableName . ' (calendarid, uri, calendardata, lastmodified, etag, size, componenttype, firstoccurence, lastoccurence, uid) VALUES (?,?,?,?,?,?,?,?,?,?)'); 490 $stmt->execute([ 491 $calendarId, 492 $objectUri, 493 $calendarData, 494 time(), 495 $extraData['etag'], 496 $extraData['size'], 497 $extraData['componentType'], 498 $extraData['firstOccurence'], 499 $extraData['lastOccurence'], 500 $extraData['uid'], 501 ]); 502 $this->addChange($calendarId, $objectUri, 1); 503 504 return '"' . $extraData['etag'] . '"'; 505 506 } 507 508 /** 509 * Updates an existing calendarobject, based on it's uri. 510 * 511 * The object uri is only the basename, or filename and not a full path. 512 * 513 * It is possible return an etag from this function, which will be used in 514 * the response to this PUT request. Note that the ETag must be surrounded 515 * by double-quotes. 516 * 517 * However, you should only really return this ETag if you don't mangle the 518 * calendar-data. If the result of a subsequent GET to this object is not 519 * the exact same as this request body, you should omit the ETag. 520 * 521 * @param mixed $calendarId 522 * @param string $objectUri 523 * @param string $calendarData 524 * @return string|null 525 */ 526 function updateCalendarObject($calendarId, $objectUri, $calendarData) { 527 528 $extraData = $this->getDenormalizedData($calendarData); 529 530 $stmt = $this->pdo->prepare('UPDATE ' . $this->calendarObjectTableName . ' SET calendardata = ?, lastmodified = ?, etag = ?, size = ?, componenttype = ?, firstoccurence = ?, lastoccurence = ?, uid = ? WHERE calendarid = ? AND uri = ?'); 531 $stmt->execute([$calendarData, time(), $extraData['etag'], $extraData['size'], $extraData['componentType'], $extraData['firstOccurence'], $extraData['lastOccurence'], $extraData['uid'], $calendarId, $objectUri]); 532 533 $this->addChange($calendarId, $objectUri, 2); 534 535 return '"' . $extraData['etag'] . '"'; 536 537 } 538 539 /** 540 * Parses some information from calendar objects, used for optimized 541 * calendar-queries. 542 * 543 * Returns an array with the following keys: 544 * * etag - An md5 checksum of the object without the quotes. 545 * * size - Size of the object in bytes 546 * * componentType - VEVENT, VTODO or VJOURNAL 547 * * firstOccurence 548 * * lastOccurence 549 * * uid - value of the UID property 550 * 551 * @param string $calendarData 552 * @return array 553 */ 554 protected function getDenormalizedData($calendarData) { 555 556 $vObject = VObject\Reader::read($calendarData); 557 $componentType = null; 558 $component = null; 559 $firstOccurence = null; 560 $lastOccurence = null; 561 $uid = null; 562 foreach ($vObject->getComponents() as $component) { 563 if ($component->name !== 'VTIMEZONE') { 564 $componentType = $component->name; 565 $uid = (string)$component->UID; 566 break; 567 } 568 } 569 if (!$componentType) { 570 throw new \Sabre\DAV\Exception\BadRequest('Calendar objects must have a VJOURNAL, VEVENT or VTODO component'); 571 } 572 if ($componentType === 'VEVENT') { 573 $firstOccurence = $component->DTSTART->getDateTime()->getTimeStamp(); 574 // Finding the last occurence is a bit harder 575 if (!isset($component->RRULE)) { 576 if (isset($component->DTEND)) { 577 $lastOccurence = $component->DTEND->getDateTime()->getTimeStamp(); 578 } elseif (isset($component->DURATION)) { 579 $endDate = clone $component->DTSTART->getDateTime(); 580 $endDate->add(VObject\DateTimeParser::parse($component->DURATION->getValue())); 581 $lastOccurence = $endDate->getTimeStamp(); 582 } elseif (!$component->DTSTART->hasTime()) { 583 $endDate = clone $component->DTSTART->getDateTime(); 584 $endDate->modify('+1 day'); 585 $lastOccurence = $endDate->getTimeStamp(); 586 } else { 587 $lastOccurence = $firstOccurence; 588 } 589 } else { 590 $it = new VObject\Recur\EventIterator($vObject, (string)$component->UID); 591 $maxDate = new \DateTime(self::MAX_DATE); 592 if ($it->isInfinite()) { 593 $lastOccurence = $maxDate->getTimeStamp(); 594 } else { 595 $end = $it->getDtEnd(); 596 while ($it->valid() && $end < $maxDate) { 597 $end = $it->getDtEnd(); 598 $it->next(); 599 600 } 601 $lastOccurence = $end->getTimeStamp(); 602 } 603 604 } 605 } 606 607 return [ 608 'etag' => md5($calendarData), 609 'size' => strlen($calendarData), 610 'componentType' => $componentType, 611 'firstOccurence' => $firstOccurence, 612 'lastOccurence' => $lastOccurence, 613 'uid' => $uid, 614 ]; 615 616 } 617 618 /** 619 * Deletes an existing calendar object. 620 * 621 * The object uri is only the basename, or filename and not a full path. 622 * 623 * @param string $calendarId 624 * @param string $objectUri 625 * @return void 626 */ 627 function deleteCalendarObject($calendarId, $objectUri) { 628 629 $stmt = $this->pdo->prepare('DELETE FROM ' . $this->calendarObjectTableName . ' WHERE calendarid = ? AND uri = ?'); 630 $stmt->execute([$calendarId, $objectUri]); 631 632 $this->addChange($calendarId, $objectUri, 3); 633 634 } 635 636 /** 637 * Performs a calendar-query on the contents of this calendar. 638 * 639 * The calendar-query is defined in RFC4791 : CalDAV. Using the 640 * calendar-query it is possible for a client to request a specific set of 641 * object, based on contents of iCalendar properties, date-ranges and 642 * iCalendar component types (VTODO, VEVENT). 643 * 644 * This method should just return a list of (relative) urls that match this 645 * query. 646 * 647 * The list of filters are specified as an array. The exact array is 648 * documented by \Sabre\CalDAV\CalendarQueryParser. 649 * 650 * Note that it is extremely likely that getCalendarObject for every path 651 * returned from this method will be called almost immediately after. You 652 * may want to anticipate this to speed up these requests. 653 * 654 * This method provides a default implementation, which parses *all* the 655 * iCalendar objects in the specified calendar. 656 * 657 * This default may well be good enough for personal use, and calendars 658 * that aren't very large. But if you anticipate high usage, big calendars 659 * or high loads, you are strongly adviced to optimize certain paths. 660 * 661 * The best way to do so is override this method and to optimize 662 * specifically for 'common filters'. 663 * 664 * Requests that are extremely common are: 665 * * requests for just VEVENTS 666 * * requests for just VTODO 667 * * requests with a time-range-filter on a VEVENT. 668 * 669 * ..and combinations of these requests. It may not be worth it to try to 670 * handle every possible situation and just rely on the (relatively 671 * easy to use) CalendarQueryValidator to handle the rest. 672 * 673 * Note that especially time-range-filters may be difficult to parse. A 674 * time-range filter specified on a VEVENT must for instance also handle 675 * recurrence rules correctly. 676 * A good example of how to interprete all these filters can also simply 677 * be found in \Sabre\CalDAV\CalendarQueryFilter. This class is as correct 678 * as possible, so it gives you a good idea on what type of stuff you need 679 * to think of. 680 * 681 * This specific implementation (for the PDO) backend optimizes filters on 682 * specific components, and VEVENT time-ranges. 683 * 684 * @param string $calendarId 685 * @param array $filters 686 * @return array 687 */ 688 function calendarQuery($calendarId, array $filters) { 689 690 $componentType = null; 691 $requirePostFilter = true; 692 $timeRange = null; 693 694 // if no filters were specified, we don't need to filter after a query 695 if (!$filters['prop-filters'] && !$filters['comp-filters']) { 696 $requirePostFilter = false; 697 } 698 699 // Figuring out if there's a component filter 700 if (count($filters['comp-filters']) > 0 && !$filters['comp-filters'][0]['is-not-defined']) { 701 $componentType = $filters['comp-filters'][0]['name']; 702 703 // Checking if we need post-filters 704 if (!$filters['prop-filters'] && !$filters['comp-filters'][0]['comp-filters'] && !$filters['comp-filters'][0]['time-range'] && !$filters['comp-filters'][0]['prop-filters']) { 705 $requirePostFilter = false; 706 } 707 // There was a time-range filter 708 if ($componentType == 'VEVENT' && isset($filters['comp-filters'][0]['time-range'])) { 709 $timeRange = $filters['comp-filters'][0]['time-range']; 710 711 // If start time OR the end time is not specified, we can do a 712 // 100% accurate mysql query. 713 if (!$filters['prop-filters'] && !$filters['comp-filters'][0]['comp-filters'] && !$filters['comp-filters'][0]['prop-filters'] && (!$timeRange['start'] || !$timeRange['end'])) { 714 $requirePostFilter = false; 715 } 716 } 717 718 } 719 720 if ($requirePostFilter) { 721 $query = "SELECT uri, calendardata FROM " . $this->calendarObjectTableName . " WHERE calendarid = :calendarid"; 722 } else { 723 $query = "SELECT uri FROM " . $this->calendarObjectTableName . " WHERE calendarid = :calendarid"; 724 } 725 726 $values = [ 727 'calendarid' => $calendarId, 728 ]; 729 730 if ($componentType) { 731 $query .= " AND componenttype = :componenttype"; 732 $values['componenttype'] = $componentType; 733 } 734 735 if ($timeRange && $timeRange['start']) { 736 $query .= " AND lastoccurence > :startdate"; 737 $values['startdate'] = $timeRange['start']->getTimeStamp(); 738 } 739 if ($timeRange && $timeRange['end']) { 740 $query .= " AND firstoccurence < :enddate"; 741 $values['enddate'] = $timeRange['end']->getTimeStamp(); 742 } 743 744 $stmt = $this->pdo->prepare($query); 745 $stmt->execute($values); 746 747 $result = []; 748 while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { 749 if ($requirePostFilter) { 750 if (!$this->validateFilterForObject($row, $filters)) { 751 continue; 752 } 753 } 754 $result[] = $row['uri']; 755 756 } 757 758 return $result; 759 760 } 761 762 /** 763 * Searches through all of a users calendars and calendar objects to find 764 * an object with a specific UID. 765 * 766 * This method should return the path to this object, relative to the 767 * calendar home, so this path usually only contains two parts: 768 * 769 * calendarpath/objectpath.ics 770 * 771 * If the uid is not found, return null. 772 * 773 * This method should only consider * objects that the principal owns, so 774 * any calendars owned by other principals that also appear in this 775 * collection should be ignored. 776 * 777 * @param string $principalUri 778 * @param string $uid 779 * @return string|null 780 */ 781 function getCalendarObjectByUID($principalUri, $uid) { 782 783 $query = <<<SQL 784SELECT 785 calendars.uri AS calendaruri, calendarobjects.uri as objecturi 786FROM 787 $this->calendarObjectTableName AS calendarobjects 788LEFT JOIN 789 $this->calendarTableName AS calendars 790 ON calendarobjects.calendarid = calendars.id 791WHERE 792 calendars.principaluri = ? 793 AND 794 calendarobjects.uid = ? 795SQL; 796 797 $stmt = $this->pdo->prepare($query); 798 $stmt->execute([$principalUri, $uid]); 799 800 if ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { 801 return $row['calendaruri'] . '/' . $row['objecturi']; 802 } 803 804 } 805 806 /** 807 * The getChanges method returns all the changes that have happened, since 808 * the specified syncToken in the specified calendar. 809 * 810 * This function should return an array, such as the following: 811 * 812 * [ 813 * 'syncToken' => 'The current synctoken', 814 * 'added' => [ 815 * 'new.txt', 816 * ], 817 * 'modified' => [ 818 * 'modified.txt', 819 * ], 820 * 'deleted' => [ 821 * 'foo.php.bak', 822 * 'old.txt' 823 * ] 824 * ]; 825 * 826 * The returned syncToken property should reflect the *current* syncToken 827 * of the calendar, as reported in the {http://sabredav.org/ns}sync-token 828 * property this is needed here too, to ensure the operation is atomic. 829 * 830 * If the $syncToken argument is specified as null, this is an initial 831 * sync, and all members should be reported. 832 * 833 * The modified property is an array of nodenames that have changed since 834 * the last token. 835 * 836 * The deleted property is an array with nodenames, that have been deleted 837 * from collection. 838 * 839 * The $syncLevel argument is basically the 'depth' of the report. If it's 840 * 1, you only have to report changes that happened only directly in 841 * immediate descendants. If it's 2, it should also include changes from 842 * the nodes below the child collections. (grandchildren) 843 * 844 * The $limit argument allows a client to specify how many results should 845 * be returned at most. If the limit is not specified, it should be treated 846 * as infinite. 847 * 848 * If the limit (infinite or not) is higher than you're willing to return, 849 * you should throw a Sabre\DAV\Exception\TooMuchMatches() exception. 850 * 851 * If the syncToken is expired (due to data cleanup) or unknown, you must 852 * return null. 853 * 854 * The limit is 'suggestive'. You are free to ignore it. 855 * 856 * @param string $calendarId 857 * @param string $syncToken 858 * @param int $syncLevel 859 * @param int $limit 860 * @return array 861 */ 862 function getChangesForCalendar($calendarId, $syncToken, $syncLevel, $limit = null) { 863 864 // Current synctoken 865 $stmt = $this->pdo->prepare('SELECT synctoken FROM ' . $this->calendarTableName . ' WHERE id = ?'); 866 $stmt->execute([ $calendarId ]); 867 $currentToken = $stmt->fetchColumn(0); 868 869 if (is_null($currentToken)) return null; 870 871 $result = [ 872 'syncToken' => $currentToken, 873 'added' => [], 874 'modified' => [], 875 'deleted' => [], 876 ]; 877 878 if ($syncToken) { 879 880 $query = "SELECT uri, operation FROM " . $this->calendarChangesTableName . " WHERE synctoken >= ? AND synctoken < ? AND calendarid = ? ORDER BY synctoken"; 881 if ($limit > 0) $query .= " LIMIT " . (int)$limit; 882 883 // Fetching all changes 884 $stmt = $this->pdo->prepare($query); 885 $stmt->execute([$syncToken, $currentToken, $calendarId]); 886 887 $changes = []; 888 889 // This loop ensures that any duplicates are overwritten, only the 890 // last change on a node is relevant. 891 while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { 892 893 $changes[$row['uri']] = $row['operation']; 894 895 } 896 897 foreach ($changes as $uri => $operation) { 898 899 switch ($operation) { 900 case 1 : 901 $result['added'][] = $uri; 902 break; 903 case 2 : 904 $result['modified'][] = $uri; 905 break; 906 case 3 : 907 $result['deleted'][] = $uri; 908 break; 909 } 910 911 } 912 } else { 913 // No synctoken supplied, this is the initial sync. 914 $query = "SELECT uri FROM " . $this->calendarObjectTableName . " WHERE calendarid = ?"; 915 $stmt = $this->pdo->prepare($query); 916 $stmt->execute([$calendarId]); 917 918 $result['added'] = $stmt->fetchAll(\PDO::FETCH_COLUMN); 919 } 920 return $result; 921 922 } 923 924 /** 925 * Adds a change record to the calendarchanges table. 926 * 927 * @param mixed $calendarId 928 * @param string $objectUri 929 * @param int $operation 1 = add, 2 = modify, 3 = delete. 930 * @return void 931 */ 932 protected function addChange($calendarId, $objectUri, $operation) { 933 934 $stmt = $this->pdo->prepare('INSERT INTO ' . $this->calendarChangesTableName . ' (uri, synctoken, calendarid, operation) SELECT ?, synctoken, ?, ? FROM ' . $this->calendarTableName . ' WHERE id = ?'); 935 $stmt->execute([ 936 $objectUri, 937 $calendarId, 938 $operation, 939 $calendarId 940 ]); 941 $stmt = $this->pdo->prepare('UPDATE ' . $this->calendarTableName . ' SET synctoken = synctoken + 1 WHERE id = ?'); 942 $stmt->execute([ 943 $calendarId 944 ]); 945 946 } 947 948 /** 949 * Returns a list of subscriptions for a principal. 950 * 951 * Every subscription is an array with the following keys: 952 * * id, a unique id that will be used by other functions to modify the 953 * subscription. This can be the same as the uri or a database key. 954 * * uri. This is just the 'base uri' or 'filename' of the subscription. 955 * * principaluri. The owner of the subscription. Almost always the same as 956 * principalUri passed to this method. 957 * * source. Url to the actual feed 958 * 959 * Furthermore, all the subscription info must be returned too: 960 * 961 * 1. {DAV:}displayname 962 * 2. {http://apple.com/ns/ical/}refreshrate 963 * 3. {http://calendarserver.org/ns/}subscribed-strip-todos (omit if todos 964 * should not be stripped). 965 * 4. {http://calendarserver.org/ns/}subscribed-strip-alarms (omit if alarms 966 * should not be stripped). 967 * 5. {http://calendarserver.org/ns/}subscribed-strip-attachments (omit if 968 * attachments should not be stripped). 969 * 7. {http://apple.com/ns/ical/}calendar-color 970 * 8. {http://apple.com/ns/ical/}calendar-order 971 * 9. {urn:ietf:params:xml:ns:caldav}supported-calendar-component-set 972 * (should just be an instance of 973 * Sabre\CalDAV\Property\SupportedCalendarComponentSet, with a bunch of 974 * default components). 975 * 976 * @param string $principalUri 977 * @return array 978 */ 979 function getSubscriptionsForUser($principalUri) { 980 981 $fields = array_values($this->subscriptionPropertyMap); 982 $fields[] = 'id'; 983 $fields[] = 'uri'; 984 $fields[] = 'source'; 985 $fields[] = 'principaluri'; 986 $fields[] = 'lastmodified'; 987 988 // Making fields a comma-delimited list 989 $fields = implode(', ', $fields); 990 $stmt = $this->pdo->prepare("SELECT " . $fields . " FROM " . $this->calendarSubscriptionsTableName . " WHERE principaluri = ? ORDER BY calendarorder ASC"); 991 $stmt->execute([$principalUri]); 992 993 $subscriptions = []; 994 while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { 995 996 $subscription = [ 997 'id' => $row['id'], 998 'uri' => $row['uri'], 999 'principaluri' => $row['principaluri'], 1000 'source' => $row['source'], 1001 'lastmodified' => $row['lastmodified'], 1002 1003 '{' . CalDAV\Plugin::NS_CALDAV . '}supported-calendar-component-set' => new CalDAV\Xml\Property\SupportedCalendarComponentSet(['VTODO', 'VEVENT']), 1004 ]; 1005 1006 foreach ($this->subscriptionPropertyMap as $xmlName => $dbName) { 1007 if (!is_null($row[$dbName])) { 1008 $subscription[$xmlName] = $row[$dbName]; 1009 } 1010 } 1011 1012 $subscriptions[] = $subscription; 1013 1014 } 1015 1016 return $subscriptions; 1017 1018 } 1019 1020 /** 1021 * Creates a new subscription for a principal. 1022 * 1023 * If the creation was a success, an id must be returned that can be used to reference 1024 * this subscription in other methods, such as updateSubscription. 1025 * 1026 * @param string $principalUri 1027 * @param string $uri 1028 * @param array $properties 1029 * @return mixed 1030 */ 1031 function createSubscription($principalUri, $uri, array $properties) { 1032 1033 $fieldNames = [ 1034 'principaluri', 1035 'uri', 1036 'source', 1037 'lastmodified', 1038 ]; 1039 1040 if (!isset($properties['{http://calendarserver.org/ns/}source'])) { 1041 throw new Forbidden('The {http://calendarserver.org/ns/}source property is required when creating subscriptions'); 1042 } 1043 1044 $values = [ 1045 ':principaluri' => $principalUri, 1046 ':uri' => $uri, 1047 ':source' => $properties['{http://calendarserver.org/ns/}source']->getHref(), 1048 ':lastmodified' => time(), 1049 ]; 1050 1051 foreach ($this->subscriptionPropertyMap as $xmlName => $dbName) { 1052 if (isset($properties[$xmlName])) { 1053 1054 $values[':' . $dbName] = $properties[$xmlName]; 1055 $fieldNames[] = $dbName; 1056 } 1057 } 1058 1059 $stmt = $this->pdo->prepare("INSERT INTO " . $this->calendarSubscriptionsTableName . " (" . implode(', ', $fieldNames) . ") VALUES (" . implode(', ', array_keys($values)) . ")"); 1060 $stmt->execute($values); 1061 1062 return $this->pdo->lastInsertId(); 1063 1064 } 1065 1066 /** 1067 * Updates a subscription 1068 * 1069 * The list of mutations is stored in a Sabre\DAV\PropPatch object. 1070 * To do the actual updates, you must tell this object which properties 1071 * you're going to process with the handle() method. 1072 * 1073 * Calling the handle method is like telling the PropPatch object "I 1074 * promise I can handle updating this property". 1075 * 1076 * Read the PropPatch documenation for more info and examples. 1077 * 1078 * @param mixed $subscriptionId 1079 * @param \Sabre\DAV\PropPatch $propPatch 1080 * @return void 1081 */ 1082 function updateSubscription($subscriptionId, DAV\PropPatch $propPatch) { 1083 1084 $supportedProperties = array_keys($this->subscriptionPropertyMap); 1085 $supportedProperties[] = '{http://calendarserver.org/ns/}source'; 1086 1087 $propPatch->handle($supportedProperties, function($mutations) use ($subscriptionId) { 1088 1089 $newValues = []; 1090 1091 foreach ($mutations as $propertyName => $propertyValue) { 1092 1093 if ($propertyName === '{http://calendarserver.org/ns/}source') { 1094 $newValues['source'] = $propertyValue->getHref(); 1095 } else { 1096 $fieldName = $this->subscriptionPropertyMap[$propertyName]; 1097 $newValues[$fieldName] = $propertyValue; 1098 } 1099 1100 } 1101 1102 // Now we're generating the sql query. 1103 $valuesSql = []; 1104 foreach ($newValues as $fieldName => $value) { 1105 $valuesSql[] = $fieldName . ' = ?'; 1106 } 1107 1108 $stmt = $this->pdo->prepare("UPDATE " . $this->calendarSubscriptionsTableName . " SET " . implode(', ', $valuesSql) . ", lastmodified = ? WHERE id = ?"); 1109 $newValues['lastmodified'] = time(); 1110 $newValues['id'] = $subscriptionId; 1111 $stmt->execute(array_values($newValues)); 1112 1113 return true; 1114 1115 }); 1116 1117 } 1118 1119 /** 1120 * Deletes a subscription 1121 * 1122 * @param mixed $subscriptionId 1123 * @return void 1124 */ 1125 function deleteSubscription($subscriptionId) { 1126 1127 $stmt = $this->pdo->prepare('DELETE FROM ' . $this->calendarSubscriptionsTableName . ' WHERE id = ?'); 1128 $stmt->execute([$subscriptionId]); 1129 1130 } 1131 1132 /** 1133 * Returns a single scheduling object. 1134 * 1135 * The returned array should contain the following elements: 1136 * * uri - A unique basename for the object. This will be used to 1137 * construct a full uri. 1138 * * calendardata - The iCalendar object 1139 * * lastmodified - The last modification date. Can be an int for a unix 1140 * timestamp, or a PHP DateTime object. 1141 * * etag - A unique token that must change if the object changed. 1142 * * size - The size of the object, in bytes. 1143 * 1144 * @param string $principalUri 1145 * @param string $objectUri 1146 * @return array 1147 */ 1148 function getSchedulingObject($principalUri, $objectUri) { 1149 1150 $stmt = $this->pdo->prepare('SELECT uri, calendardata, lastmodified, etag, size FROM ' . $this->schedulingObjectTableName . ' WHERE principaluri = ? AND uri = ?'); 1151 $stmt->execute([$principalUri, $objectUri]); 1152 $row = $stmt->fetch(\PDO::FETCH_ASSOC); 1153 1154 if (!$row) return null; 1155 1156 return [ 1157 'uri' => $row['uri'], 1158 'calendardata' => $row['calendardata'], 1159 'lastmodified' => $row['lastmodified'], 1160 'etag' => '"' . $row['etag'] . '"', 1161 'size' => (int)$row['size'], 1162 ]; 1163 1164 } 1165 1166 /** 1167 * Returns all scheduling objects for the inbox collection. 1168 * 1169 * These objects should be returned as an array. Every item in the array 1170 * should follow the same structure as returned from getSchedulingObject. 1171 * 1172 * The main difference is that 'calendardata' is optional. 1173 * 1174 * @param string $principalUri 1175 * @return array 1176 */ 1177 function getSchedulingObjects($principalUri) { 1178 1179 $stmt = $this->pdo->prepare('SELECT id, calendardata, uri, lastmodified, etag, size FROM ' . $this->schedulingObjectTableName . ' WHERE principaluri = ?'); 1180 $stmt->execute([$principalUri]); 1181 1182 $result = []; 1183 foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) as $row) { 1184 $result[] = [ 1185 'calendardata' => $row['calendardata'], 1186 'uri' => $row['uri'], 1187 'lastmodified' => $row['lastmodified'], 1188 'etag' => '"' . $row['etag'] . '"', 1189 'size' => (int)$row['size'], 1190 ]; 1191 } 1192 1193 return $result; 1194 1195 } 1196 1197 /** 1198 * Deletes a scheduling object 1199 * 1200 * @param string $principalUri 1201 * @param string $objectUri 1202 * @return void 1203 */ 1204 function deleteSchedulingObject($principalUri, $objectUri) { 1205 1206 $stmt = $this->pdo->prepare('DELETE FROM ' . $this->schedulingObjectTableName . ' WHERE principaluri = ? AND uri = ?'); 1207 $stmt->execute([$principalUri, $objectUri]); 1208 1209 } 1210 1211 /** 1212 * Creates a new scheduling object. This should land in a users' inbox. 1213 * 1214 * @param string $principalUri 1215 * @param string $objectUri 1216 * @param string $objectData 1217 * @return void 1218 */ 1219 function createSchedulingObject($principalUri, $objectUri, $objectData) { 1220 1221 $stmt = $this->pdo->prepare('INSERT INTO ' . $this->schedulingObjectTableName . ' (principaluri, calendardata, uri, lastmodified, etag, size) VALUES (?, ?, ?, ?, ?, ?)'); 1222 $stmt->execute([$principalUri, $objectData, $objectUri, time(), md5($objectData), strlen($objectData) ]); 1223 1224 } 1225 1226} 1227