1<?php
2
3namespace Sabre\DAVACL\PrincipalBackend;
4
5use Sabre\DAV;
6use Sabre\DAV\MkCol;
7use Sabre\HTTP\URLUtil;
8
9/**
10 * PDO principal backend
11 *
12 *
13 * This backend assumes all principals are in a single collection. The default collection
14 * is 'principals/', but this can be overridden.
15 *
16 * @copyright Copyright (C) fruux GmbH (https://fruux.com/)
17 * @author Evert Pot (http://evertpot.com/)
18 * @license http://sabre.io/license/ Modified BSD License
19 */
20class PDO extends AbstractBackend implements CreatePrincipalSupport {
21
22    /**
23     * PDO table name for 'principals'
24     *
25     * @var string
26     */
27    public $tableName = 'principals';
28
29    /**
30     * PDO table name for 'group members'
31     *
32     * @var string
33     */
34    public $groupMembersTableName = 'groupmembers';
35
36    /**
37     * pdo
38     *
39     * @var PDO
40     */
41    protected $pdo;
42
43    /**
44     * A list of additional fields to support
45     *
46     * @var array
47     */
48    protected $fieldMap = [
49
50        /**
51         * This property can be used to display the users' real name.
52         */
53        '{DAV:}displayname' => [
54            'dbField' => 'displayname',
55        ],
56
57        /**
58         * This is the users' primary email-address.
59         */
60        '{http://sabredav.org/ns}email-address' => [
61            'dbField' => 'email',
62        ],
63    ];
64
65    /**
66     * Sets up the backend.
67     *
68     * @param \PDO $pdo
69     */
70    function __construct(\PDO $pdo) {
71
72        $this->pdo = $pdo;
73
74    }
75
76    /**
77     * Returns a list of principals based on a prefix.
78     *
79     * This prefix will often contain something like 'principals'. You are only
80     * expected to return principals that are in this base path.
81     *
82     * You are expected to return at least a 'uri' for every user, you can
83     * return any additional properties if you wish so. Common properties are:
84     *   {DAV:}displayname
85     *   {http://sabredav.org/ns}email-address - This is a custom SabreDAV
86     *     field that's actualy injected in a number of other properties. If
87     *     you have an email address, use this property.
88     *
89     * @param string $prefixPath
90     * @return array
91     */
92    function getPrincipalsByPrefix($prefixPath) {
93
94        $fields = [
95            'uri',
96        ];
97
98        foreach ($this->fieldMap as $key => $value) {
99            $fields[] = $value['dbField'];
100        }
101        $result = $this->pdo->query('SELECT ' . implode(',', $fields) . '  FROM ' . $this->tableName);
102
103        $principals = [];
104
105        while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
106
107            // Checking if the principal is in the prefix
108            list($rowPrefix) = URLUtil::splitPath($row['uri']);
109            if ($rowPrefix !== $prefixPath) continue;
110
111            $principal = [
112                'uri' => $row['uri'],
113            ];
114            foreach ($this->fieldMap as $key => $value) {
115                if ($row[$value['dbField']]) {
116                    $principal[$key] = $row[$value['dbField']];
117                }
118            }
119            $principals[] = $principal;
120
121        }
122
123        return $principals;
124
125    }
126
127    /**
128     * Returns a specific principal, specified by it's path.
129     * The returned structure should be the exact same as from
130     * getPrincipalsByPrefix.
131     *
132     * @param string $path
133     * @return array
134     */
135    function getPrincipalByPath($path) {
136
137        $fields = [
138            'id',
139            'uri',
140        ];
141
142        foreach ($this->fieldMap as $key => $value) {
143            $fields[] = $value['dbField'];
144        }
145        $stmt = $this->pdo->prepare('SELECT ' . implode(',', $fields) . '  FROM ' . $this->tableName . ' WHERE uri = ?');
146        $stmt->execute([$path]);
147
148        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
149        if (!$row) return;
150
151        $principal = [
152            'id'  => $row['id'],
153            'uri' => $row['uri'],
154        ];
155        foreach ($this->fieldMap as $key => $value) {
156            if ($row[$value['dbField']]) {
157                $principal[$key] = $row[$value['dbField']];
158            }
159        }
160        return $principal;
161
162    }
163
164    /**
165     * Updates one ore more webdav properties on a principal.
166     *
167     * The list of mutations is stored in a Sabre\DAV\PropPatch object.
168     * To do the actual updates, you must tell this object which properties
169     * you're going to process with the handle() method.
170     *
171     * Calling the handle method is like telling the PropPatch object "I
172     * promise I can handle updating this property".
173     *
174     * Read the PropPatch documentation for more info and examples.
175     *
176     * @param string $path
177     * @param DAV\PropPatch $propPatch
178     */
179    function updatePrincipal($path, DAV\PropPatch $propPatch) {
180
181        $propPatch->handle(array_keys($this->fieldMap), function($properties) use ($path) {
182
183            $query = "UPDATE " . $this->tableName . " SET ";
184            $first = true;
185
186            $values = [];
187
188            foreach ($properties as $key => $value) {
189
190                $dbField = $this->fieldMap[$key]['dbField'];
191
192                if (!$first) {
193                    $query .= ', ';
194                }
195                $first = false;
196                $query .= $dbField . ' = :' . $dbField;
197                $values[$dbField] = $value;
198
199            }
200
201            $query .= " WHERE uri = :uri";
202            $values['uri'] = $path;
203
204            $stmt = $this->pdo->prepare($query);
205            $stmt->execute($values);
206
207            return true;
208
209        });
210
211    }
212
213    /**
214     * This method is used to search for principals matching a set of
215     * properties.
216     *
217     * This search is specifically used by RFC3744's principal-property-search
218     * REPORT.
219     *
220     * The actual search should be a unicode-non-case-sensitive search. The
221     * keys in searchProperties are the WebDAV property names, while the values
222     * are the property values to search on.
223     *
224     * By default, if multiple properties are submitted to this method, the
225     * various properties should be combined with 'AND'. If $test is set to
226     * 'anyof', it should be combined using 'OR'.
227     *
228     * This method should simply return an array with full principal uri's.
229     *
230     * If somebody attempted to search on a property the backend does not
231     * support, you should simply return 0 results.
232     *
233     * You can also just return 0 results if you choose to not support
234     * searching at all, but keep in mind that this may stop certain features
235     * from working.
236     *
237     * @param string $prefixPath
238     * @param array $searchProperties
239     * @param string $test
240     * @return array
241     */
242    function searchPrincipals($prefixPath, array $searchProperties, $test = 'allof') {
243        if (count($searchProperties) == 0) return [];    //No criteria
244
245        $query = 'SELECT uri FROM ' . $this->tableName . ' WHERE ';
246        $values = [];
247        foreach ($searchProperties as $property => $value) {
248            switch ($property) {
249                case '{DAV:}displayname' :
250                    $column = "displayname";
251                    break;
252                case '{http://sabredav.org/ns}email-address' :
253                    $column = "email";
254                    break;
255                default :
256                    // Unsupported property
257                    return [];
258            }
259            if (count($values) > 0) $query .= (strcmp($test, "anyof") == 0 ? " OR " : " AND ");
260            $query .= 'lower(' . $column . ') LIKE lower(?)';
261            $values[] = '%' . $value . '%';
262
263        }
264        $stmt = $this->pdo->prepare($query);
265        $stmt->execute($values);
266
267        $principals = [];
268        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
269
270            // Checking if the principal is in the prefix
271            list($rowPrefix) = URLUtil::splitPath($row['uri']);
272            if ($rowPrefix !== $prefixPath) continue;
273
274            $principals[] = $row['uri'];
275
276        }
277
278        return $principals;
279
280    }
281
282    /**
283     * Finds a principal by its URI.
284     *
285     * This method may receive any type of uri, but mailto: addresses will be
286     * the most common.
287     *
288     * Implementation of this API is optional. It is currently used by the
289     * CalDAV system to find principals based on their email addresses. If this
290     * API is not implemented, some features may not work correctly.
291     *
292     * This method must return a relative principal path, or null, if the
293     * principal was not found or you refuse to find it.
294     *
295     * @param string $uri
296     * @param string $principalPrefix
297     * @return string
298     */
299    function findByUri($uri, $principalPrefix) {
300        $value = null;
301        $scheme = null;
302        list($scheme, $value) = explode(":", $uri, 2);
303        if (empty($value)) return null;
304
305        $uri = null;
306        switch ($scheme){
307            case "mailto":
308                $query = 'SELECT uri FROM ' . $this->tableName . ' WHERE lower(email)=lower(?)';
309                $stmt = $this->pdo->prepare($query);
310                $stmt->execute([$value]);
311
312                while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
313                    // Checking if the principal is in the prefix
314                    list($rowPrefix) = URLUtil::splitPath($row['uri']);
315                    if ($rowPrefix !== $principalPrefix) continue;
316
317                    $uri = $row['uri'];
318                    break; //Stop on first match
319                }
320                break;
321            default:
322                //unsupported uri scheme
323                return null;
324        }
325        return $uri;
326    }
327
328    /**
329     * Returns the list of members for a group-principal
330     *
331     * @param string $principal
332     * @return array
333     */
334    function getGroupMemberSet($principal) {
335
336        $principal = $this->getPrincipalByPath($principal);
337        if (!$principal) throw new DAV\Exception('Principal not found');
338
339        $stmt = $this->pdo->prepare('SELECT principals.uri as uri FROM ' . $this->groupMembersTableName . ' AS groupmembers LEFT JOIN ' . $this->tableName . ' AS principals ON groupmembers.member_id = principals.id WHERE groupmembers.principal_id = ?');
340        $stmt->execute([$principal['id']]);
341
342        $result = [];
343        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
344            $result[] = $row['uri'];
345        }
346        return $result;
347
348    }
349
350    /**
351     * Returns the list of groups a principal is a member of
352     *
353     * @param string $principal
354     * @return array
355     */
356    function getGroupMembership($principal) {
357
358        $principal = $this->getPrincipalByPath($principal);
359        if (!$principal) throw new DAV\Exception('Principal not found');
360
361        $stmt = $this->pdo->prepare('SELECT principals.uri as uri FROM ' . $this->groupMembersTableName . ' AS groupmembers LEFT JOIN ' . $this->tableName . ' AS principals ON groupmembers.principal_id = principals.id WHERE groupmembers.member_id = ?');
362        $stmt->execute([$principal['id']]);
363
364        $result = [];
365        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
366            $result[] = $row['uri'];
367        }
368        return $result;
369
370    }
371
372    /**
373     * Updates the list of group members for a group principal.
374     *
375     * The principals should be passed as a list of uri's.
376     *
377     * @param string $principal
378     * @param array $members
379     * @return void
380     */
381    function setGroupMemberSet($principal, array $members) {
382
383        // Grabbing the list of principal id's.
384        $stmt = $this->pdo->prepare('SELECT id, uri FROM ' . $this->tableName . ' WHERE uri IN (? ' . str_repeat(', ? ', count($members)) . ');');
385        $stmt->execute(array_merge([$principal], $members));
386
387        $memberIds = [];
388        $principalId = null;
389
390        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
391            if ($row['uri'] == $principal) {
392                $principalId = $row['id'];
393            } else {
394                $memberIds[] = $row['id'];
395            }
396        }
397        if (!$principalId) throw new DAV\Exception('Principal not found');
398
399        // Wiping out old members
400        $stmt = $this->pdo->prepare('DELETE FROM ' . $this->groupMembersTableName . ' WHERE principal_id = ?;');
401        $stmt->execute([$principalId]);
402
403        foreach ($memberIds as $memberId) {
404
405            $stmt = $this->pdo->prepare('INSERT INTO ' . $this->groupMembersTableName . ' (principal_id, member_id) VALUES (?, ?);');
406            $stmt->execute([$principalId, $memberId]);
407
408        }
409
410    }
411
412    /**
413     * Creates a new principal.
414     *
415     * This method receives a full path for the new principal. The mkCol object
416     * contains any additional webdav properties specified during the creation
417     * of the principal.
418     *
419     * @param string $path
420     * @param MkCol $mkCol
421     * @return void
422     */
423    function createPrincipal($path, MkCol $mkCol) {
424
425        $stmt = $this->pdo->prepare('INSERT INTO ' . $this->tableName . ' (uri) VALUES (?)');
426        $stmt->execute([$path]);
427        $this->updatePrincipal($path, $mkCol);
428
429    }
430
431}
432