PageRenderTime 156ms CodeModel.GetById 59ms app.highlight 39ms RepoModel.GetById 45ms app.codeStats 1ms

/lib/Sabre/CalDAV/Backend/PDO.php

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