PageRenderTime 146ms CodeModel.GetById 81ms app.highlight 15ms RepoModel.GetById 45ms app.codeStats 0ms

/lib/Sabre/CardDAV/Backend/PDO.php

https://github.com/KOLANICH/SabreDAV
PHP | 524 lines | 195 code | 93 blank | 236 comment | 7 complexity | 8dad879b2ec626cb89dda1bf68ac53b4 MD5 | raw file
  1<?php
  2
  3namespace Sabre\CardDAV\Backend;
  4
  5use Sabre\CardDAV;
  6use Sabre\DAV;
  7
  8/**
  9 * PDO CardDAV backend
 10 *
 11 * This CardDAV backend uses PDO to store addressbooks
 12 *
 13 * @copyright Copyright (C) 2007-2013 fruux GmbH (https://fruux.com/).
 14 * @author Evert Pot (http://evertpot.com/)
 15 * @license http://code.google.com/p/sabredav/wiki/License Modified BSD License
 16 */
 17class PDO extends AbstractBackend implements SyncSupport {
 18
 19    /**
 20     * PDO connection
 21     *
 22     * @var PDO
 23     */
 24    protected $pdo;
 25
 26    /**
 27     * The PDO table name used to store addressbooks
 28     */
 29    protected $addressBooksTableName;
 30
 31    /**
 32     * The PDO table name used to store cards
 33     */
 34    protected $cardsTableName;
 35
 36    /**
 37     * The table name that will be used for tracking changes in address books.
 38     *
 39     * @var string
 40     */
 41    protected $addressBookChangesTableName;
 42
 43    /**
 44     * Sets up the object
 45     *
 46     * @param \PDO $pdo
 47     * @param string $addressBooksTableName
 48     * @param string $cardsTableName
 49     */
 50    public function __construct(\PDO $pdo, $addressBooksTableName = 'addressbooks', $cardsTableName = 'cards', $addressBookChangesTableName = 'addressbookchanges') {
 51
 52        $this->pdo = $pdo;
 53        $this->addressBooksTableName = $addressBooksTableName;
 54        $this->cardsTableName = $cardsTableName;
 55        $this->addressBookChangesTableName = $addressBookChangesTableName;
 56
 57    }
 58
 59    /**
 60     * Returns the list of addressbooks for a specific user.
 61     *
 62     * @param string $principalUri
 63     * @return array
 64     */
 65    public function getAddressBooksForUser($principalUri) {
 66
 67        $stmt = $this->pdo->prepare('SELECT id, uri, displayname, principaluri, description, synctoken FROM '.$this->addressBooksTableName.' WHERE principaluri = ?');
 68        $stmt->execute(array($principalUri));
 69
 70        $addressBooks = array();
 71
 72        foreach($stmt->fetchAll() as $row) {
 73
 74            $addressBooks[] = array(
 75                'id'  => $row['id'],
 76                'uri' => $row['uri'],
 77                'principaluri' => $row['principaluri'],
 78                '{DAV:}displayname' => $row['displayname'],
 79                '{' . CardDAV\Plugin::NS_CARDDAV . '}addressbook-description' => $row['description'],
 80                '{http://calendarserver.org/ns/}getctag' => $row['synctoken'],
 81                '{' . CardDAV\Plugin::NS_CARDDAV . '}supported-address-data' =>
 82                    new CardDAV\Property\SupportedAddressData(),
 83                '{DAV:}sync-token' => $row['synctoken']?$row['synctoken']:'0',
 84            );
 85
 86        }
 87
 88        return $addressBooks;
 89
 90    }
 91
 92
 93    /**
 94     * Updates an addressbook's properties
 95     *
 96     * See Sabre\DAV\IProperties for a description of the mutations array, as
 97     * well as the return value.
 98     *
 99     * @param mixed $addressBookId
100     * @param array $mutations
101     * @see Sabre\DAV\IProperties::updateProperties
102     * @return bool|array
103     */
104    public function updateAddressBook($addressBookId, array $mutations) {
105
106        $updates = array();
107
108        foreach($mutations as $property=>$newValue) {
109
110            switch($property) {
111                case '{DAV:}displayname' :
112                    $updates['displayname'] = $newValue;
113                    break;
114                case '{' . CardDAV\Plugin::NS_CARDDAV . '}addressbook-description' :
115                    $updates['description'] = $newValue;
116                    break;
117                default :
118                    // If any unsupported values were being updated, we must
119                    // let the entire request fail.
120                    return false;
121            }
122
123        }
124
125        // No values are being updated?
126        if (!$updates) {
127            return false;
128        }
129
130        $query = 'UPDATE ' . $this->addressBooksTableName . ' SET ';
131        $first = true;
132        foreach($updates as $key=>$value) {
133            if ($first) {
134                $first = false;
135            } else {
136                $query.=', ';
137            }
138            $query.=' `' . $key . '` = :' . $key . ' ';
139        }
140        $query.=' WHERE id = :addressbookid';
141
142        $stmt = $this->pdo->prepare($query);
143        $updates['addressbookid'] = $addressBookId;
144
145        $stmt->execute($updates);
146
147        $this->addChange($addressBookId, "", 2);
148
149        return true;
150
151    }
152
153    /**
154     * Creates a new address book
155     *
156     * @param string $principalUri
157     * @param string $url Just the 'basename' of the url.
158     * @param array $properties
159     * @return void
160     */
161    public function createAddressBook($principalUri, $url, array $properties) {
162
163        $values = array(
164            'displayname' => null,
165            'description' => null,
166            'principaluri' => $principalUri,
167            'uri' => $url,
168        );
169
170        foreach($properties as $property=>$newValue) {
171
172            switch($property) {
173                case '{DAV:}displayname' :
174                    $values['displayname'] = $newValue;
175                    break;
176                case '{' . CardDAV\Plugin::NS_CARDDAV . '}addressbook-description' :
177                    $values['description'] = $newValue;
178                    break;
179                default :
180                    throw new DAV\Exception\BadRequest('Unknown property: ' . $property);
181            }
182
183        }
184
185        $query = 'INSERT INTO ' . $this->addressBooksTableName . ' (uri, displayname, description, principaluri, synctoken) VALUES (:uri, :displayname, :description, :principaluri, 1)';
186        $stmt = $this->pdo->prepare($query);
187        $stmt->execute($values);
188        return $this->pdo->lastInsertId();
189
190    }
191
192    /**
193     * Deletes an entire addressbook and all its contents
194     *
195     * @param int $addressBookId
196     * @return void
197     */
198    public function deleteAddressBook($addressBookId) {
199
200        $stmt = $this->pdo->prepare('DELETE FROM ' . $this->cardsTableName . ' WHERE addressbookid = ?');
201        $stmt->execute([$addressBookId]);
202
203        $stmt = $this->pdo->prepare('DELETE FROM ' . $this->addressBooksTableName . ' WHERE id = ?');
204        $stmt->execute([$addressBookId]);
205
206        $stmt = $this->pdo->prepare('DELETE FROM '.$this->addressBookChangesTableName.' WHERE id = ?');
207        $stmt->execute([$addressBookId]);
208
209    }
210
211    /**
212     * Returns all cards for a specific addressbook id.
213     *
214     * This method should return the following properties for each card:
215     *   * carddata - raw vcard data
216     *   * uri - Some unique url
217     *   * lastmodified - A unix timestamp
218     *
219     * It's recommended to also return the following properties:
220     *   * etag - A unique etag. This must change every time the card changes.
221     *   * size - The size of the card in bytes.
222     *
223     * If these last two properties are provided, less time will be spent
224     * calculating them. If they are specified, you can also ommit carddata.
225     * This may speed up certain requests, especially with large cards.
226     *
227     * @param mixed $addressbookId
228     * @return array
229     */
230    public function getCards($addressbookId) {
231
232        $stmt = $this->pdo->prepare('SELECT id, carddata, uri, lastmodified FROM ' . $this->cardsTableName . ' WHERE addressbookid = ?');
233        $stmt->execute(array($addressbookId));
234
235        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
236
237
238    }
239
240    /**
241     * Returns a specfic card.
242     *
243     * The same set of properties must be returned as with getCards. The only
244     * exception is that 'carddata' is absolutely required.
245     *
246     * If the card does not exist, you must return false.
247     *
248     * @param mixed $addressBookId
249     * @param string $cardUri
250     * @return array
251     */
252    public function getCard($addressBookId, $cardUri) {
253
254        $stmt = $this->pdo->prepare('SELECT id, carddata, uri, lastmodified FROM ' . $this->cardsTableName . ' WHERE addressbookid = ? AND uri = ? LIMIT 1');
255        $stmt->execute(array($addressBookId, $cardUri));
256
257        $result = $stmt->fetch(\PDO::FETCH_ASSOC);
258
259        return $result?$result:false;
260
261    }
262
263    /**
264     * Returns a list of cards.
265     *
266     * This method should work identical to getCard, but instead return all the
267     * cards in the list as an array.
268     *
269     * If the backend supports this, it may allow for some speed-ups.
270     *
271     * @param mixed $addressBookId
272     * @param array $uris
273     * @return array
274     */
275    public function getMultipleCards($addressBookId, array $uris) {
276
277        return array_map(function($uri) use ($addressBookId) {
278            return $this->getCard($addressBookId, $uri);
279        }, $uris);
280
281        $query = 'SELECT id, carddata, uri, lastmodified FROM ' . $this->cardsTableName . ' WHERE addressbookid = ? AND uri = IN (';
282        // Inserting a whole bunch of question marks
283        $query.=implode(',', array_fill(0, count($uris), '?'));
284        $query.=')';
285
286        $stmt = $this->pdo->prepare($query);
287        $stmt->execute(array_merge([$addressBookId], $uris));
288        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
289
290    }
291
292    /**
293     * Creates a new card.
294     *
295     * The addressbook id will be passed as the first argument. This is the
296     * same id as it is returned from the getAddressbooksForUser method.
297     *
298     * The cardUri is a base uri, and doesn't include the full path. The
299     * cardData argument is the vcard body, and is passed as a string.
300     *
301     * It is possible to return an ETag from this method. This ETag is for the
302     * newly created resource, and must be enclosed with double quotes (that
303     * is, the string itself must contain the double quotes).
304     *
305     * You should only return the ETag if you store the carddata as-is. If a
306     * subsequent GET request on the same card does not have the same body,
307     * byte-by-byte and you did return an ETag here, clients tend to get
308     * confused.
309     *
310     * If you don't return an ETag, you can just return null.
311     *
312     * @param mixed $addressBookId
313     * @param string $cardUri
314     * @param string $cardData
315     * @return string|null
316     */
317    public function createCard($addressBookId, $cardUri, $cardData) {
318
319        $stmt = $this->pdo->prepare('INSERT INTO ' . $this->cardsTableName . ' (carddata, uri, lastmodified, addressbookid) VALUES (?, ?, ?, ?)');
320
321        $result = $stmt->execute(array($cardData, $cardUri, time(), $addressBookId));
322
323        $this->addChange($addressBookId, $cardUri, 1);
324
325        return '"' . md5($cardData) . '"';
326
327    }
328
329    /**
330     * Updates a card.
331     *
332     * The addressbook id will be passed as the first argument. This is the
333     * same id as it is returned from the getAddressbooksForUser method.
334     *
335     * The cardUri is a base uri, and doesn't include the full path. The
336     * cardData argument is the vcard body, and is passed as a string.
337     *
338     * It is possible to return an ETag from this method. This ETag should
339     * match that of the updated resource, and must be enclosed with double
340     * quotes (that is: the string itself must contain the actual quotes).
341     *
342     * You should only return the ETag if you store the carddata as-is. If a
343     * subsequent GET request on the same card does not have the same body,
344     * byte-by-byte and you did return an ETag here, clients tend to get
345     * confused.
346     *
347     * If you don't return an ETag, you can just return null.
348     *
349     * @param mixed $addressBookId
350     * @param string $cardUri
351     * @param string $cardData
352     * @return string|null
353     */
354    public function updateCard($addressBookId, $cardUri, $cardData) {
355
356        $stmt = $this->pdo->prepare('UPDATE ' . $this->cardsTableName . ' SET carddata = ?, lastmodified = ? WHERE uri = ? AND addressbookid =?');
357        $stmt->execute(array($cardData, time(), $cardUri, $addressBookId));
358
359        $this->addChange($addressBookId, $cardUri, 2);
360
361        return '"' . md5($cardData) . '"';
362
363    }
364
365    /**
366     * Deletes a card
367     *
368     * @param mixed $addressBookId
369     * @param string $cardUri
370     * @return bool
371     */
372    public function deleteCard($addressBookId, $cardUri) {
373
374        $stmt = $this->pdo->prepare('DELETE FROM ' . $this->cardsTableName . ' WHERE addressbookid = ? AND uri = ?');
375        $stmt->execute(array($addressBookId, $cardUri));
376
377        $this->addChange($addressBookId, $cardUri, 3);
378
379        return $stmt->rowCount()===1;
380
381    }
382
383    /**
384     * The getChanges method returns all the changes that have happened, since
385     * the specified syncToken in the specified address book.
386     *
387     * This function should return an array, such as the following:
388     *
389     * [
390     *   'syncToken' => 'The current synctoken',
391     *   'added'   => [
392     *      'new.txt',
393     *   ],
394     *   'modified'   => [
395     *      'updated.txt',
396     *   ],
397     *   'deleted' => [
398     *      'foo.php.bak',
399     *      'old.txt'
400     *   ]
401     * ];
402     *
403     * The returned syncToken property should reflect the *current* syncToken
404     * of the addressbook, as reported in the {DAV:}sync-token property This is
405     * needed here too, to ensure the operation is atomic.
406     *
407     * If the $syncToken argument is specified as null, this is an initial
408     * sync, and all members should be reported.
409     *
410     * The modified property is an array of nodenames that have changed since
411     * the last token.
412     *
413     * The deleted property is an array with nodenames, that have been deleted
414     * from collection.
415     *
416     * The $syncLevel argument is basically the 'depth' of the report. If it's
417     * 1, you only have to report changes that happened only directly in
418     * immediate descendants. If it's 2, it should also include changes from
419     * the nodes below the child collections. (grandchildren)
420     *
421     * The $limit argument allows a client to specify how many results should
422     * be returned at most. If the limit is not specified, it should be treated
423     * as infinite.
424     *
425     * If the limit (infinite or not) is higher than you're willing to return,
426     * you should throw a Sabre\DAV\Exception\TooMuchMatches() exception.
427     *
428     * If the syncToken is expired (due to data cleanup) or unknown, you must
429     * return null.
430     *
431     * The limit is 'suggestive'. You are free to ignore it.
432     *
433     * @param string $addressBookId
434     * @param string $syncToken
435     * @param int $syncLevel
436     * @param int $limit
437     * @return array
438     */
439    public function getChangesForAddressBook($addressBookId, $syncToken, $syncLevel, $limit = null) {
440
441        // Current synctoken
442        $stmt = $this->pdo->prepare('SELECT synctoken FROM addressbooks WHERE id = ?');
443        $stmt->execute([ $addressBookId ]);
444        $currentToken = $stmt->fetchColumn(0);
445
446        if (is_null($currentToken)) return null;
447
448        $result = [
449            'syncToken' => $currentToken,
450            'added'     => [],
451            'modified'  => [],
452            'deleted'   => [],
453        ];
454
455        if ($syncToken) {
456
457            $query = "SELECT uri, operation FROM " . $this->addressBookChangesTableName . " WHERE synctoken >= ? AND synctoken < ? AND addressbookid = ? ORDER BY synctoken";
458            if ($limit>0) $query.= " LIMIT " . (int)$limit;
459
460            // Fetching all changes
461            $stmt = $this->pdo->prepare($query);
462            $stmt->execute([$syncToken, $currentToken, $addressBookId]);
463
464            $changes = [];
465
466            // This loop ensures that any duplicates are overwritten, only the
467            // last change on a node is relevant.
468            while($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
469
470                $changes[$row['uri']] = $row['operation'];
471
472            }
473
474            foreach($changes as $uri => $operation) {
475
476                switch($operation) {
477                    case 1:
478                        $result['added'][] = $uri;
479                        break;
480                    case 2:
481                        $result['modified'][] = $uri;
482                        break;
483                    case 3:
484                        $result['deleted'][] = $uri;
485                        break;
486                }
487
488            }
489        } else {
490            // No synctoken supplied, this is the initial sync.
491            $query = "SELECT uri FROM cards WHERE addressbookid = ?";
492            $stmt = $this->pdo->prepare($query);
493            $stmt->execute([$addressBookId]);
494
495            $result['added'] = $stmt->fetchAll(\PDO::FETCH_COLUMN);
496        }
497        return $result;
498
499    }
500
501    /**
502     * Adds a change record to the addressbookchanges table.
503     *
504     * @param mixed $addressBookId
505     * @param string $objectUri
506     * @param int $operation 1 = add, 2 = modify, 3 = delete/
507     * @return void
508     */
509    protected function addChange($addressBookId, $objectUri, $operation) {
510
511        $stmt = $this->pdo->prepare('INSERT INTO ' . $this->addressBookChangesTableName .' (uri, synctoken, addressbookid, operation) SELECT ?, synctoken, ?, ? FROM addressbooks WHERE id = ?');
512        $stmt->execute([
513            $objectUri,
514            $addressBookId,
515            $operation,
516            $addressBookId
517        ]);
518        $stmt = $this->pdo->prepare('UPDATE ' . $this->addressBooksTableName . ' SET synctoken = synctoken + 1 WHERE id = ?');
519        $stmt->execute([
520            $addressBookId
521        ]);
522
523    }
524}