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 overriden.
15 *
16 * @copyright Copyright (C) 2007-2015 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 documenation 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
244        $query = 'SELECT uri FROM ' . $this->tableName . ' WHERE 1=1 ';
245        $values = [];
246        foreach ($searchProperties as $property => $value) {
247
248            switch ($property) {
249
250                case '{DAV:}displayname' :
251                    $query .= ' AND displayname LIKE ?';
252                    $values[] = '%' . $value . '%';
253                    break;
254                case '{http://sabredav.org/ns}email-address' :
255                    $query .= ' AND email LIKE ?';
256                    $values[] = '%' . $value . '%';
257                    break;
258                default :
259                    // Unsupported property
260                    return [];
261
262            }
263
264        }
265        $stmt = $this->pdo->prepare($query);
266        $stmt->execute($values);
267
268        $principals = [];
269        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
270
271            // Checking if the principal is in the prefix
272            list($rowPrefix) = URLUtil::splitPath($row['uri']);
273            if ($rowPrefix !== $prefixPath) continue;
274
275            $principals[] = $row['uri'];
276
277        }
278
279        return $principals;
280
281    }
282
283    /**
284     * Returns the list of members for a group-principal
285     *
286     * @param string $principal
287     * @return array
288     */
289    function getGroupMemberSet($principal) {
290
291        $principal = $this->getPrincipalByPath($principal);
292        if (!$principal) throw new DAV\Exception('Principal not found');
293
294        $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 = ?');
295        $stmt->execute([$principal['id']]);
296
297        $result = [];
298        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
299            $result[] = $row['uri'];
300        }
301        return $result;
302
303    }
304
305    /**
306     * Returns the list of groups a principal is a member of
307     *
308     * @param string $principal
309     * @return array
310     */
311    function getGroupMembership($principal) {
312
313        $principal = $this->getPrincipalByPath($principal);
314        if (!$principal) throw new DAV\Exception('Principal not found');
315
316        $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 = ?');
317        $stmt->execute([$principal['id']]);
318
319        $result = [];
320        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
321            $result[] = $row['uri'];
322        }
323        return $result;
324
325    }
326
327    /**
328     * Updates the list of group members for a group principal.
329     *
330     * The principals should be passed as a list of uri's.
331     *
332     * @param string $principal
333     * @param array $members
334     * @return void
335     */
336    function setGroupMemberSet($principal, array $members) {
337
338        // Grabbing the list of principal id's.
339        $stmt = $this->pdo->prepare('SELECT id, uri FROM ' . $this->tableName . ' WHERE uri IN (? ' . str_repeat(', ? ', count($members)) . ');');
340        $stmt->execute(array_merge([$principal], $members));
341
342        $memberIds = [];
343        $principalId = null;
344
345        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
346            if ($row['uri'] == $principal) {
347                $principalId = $row['id'];
348            } else {
349                $memberIds[] = $row['id'];
350            }
351        }
352        if (!$principalId) throw new DAV\Exception('Principal not found');
353
354        // Wiping out old members
355        $stmt = $this->pdo->prepare('DELETE FROM ' . $this->groupMembersTableName . ' WHERE principal_id = ?;');
356        $stmt->execute([$principalId]);
357
358        foreach ($memberIds as $memberId) {
359
360            $stmt = $this->pdo->prepare('INSERT INTO ' . $this->groupMembersTableName . ' (principal_id, member_id) VALUES (?, ?);');
361            $stmt->execute([$principalId, $memberId]);
362
363        }
364
365    }
366
367    /**
368     * Creates a new principal.
369     *
370     * This method receives a full path for the new principal. The mkCol object
371     * contains any additional webdav properties specified during the creation
372     * of the principal.
373     *
374     * @param string $path
375     * @param MkCol $mkCol
376     * @return void
377     */
378    function createPrincipal($path, MkCol $mkCol) {
379
380        $stmt = $this->pdo->prepare('INSERT INTO ' . $this->tableName . ' (uri) VALUES (?)');
381        $stmt->execute([$path]);
382        $this->updatePrincipal($path, $mkCol);
383
384    }
385
386}
387