4 * PDO principal backend
6 * This is a simple principal backend that maps exactly to the users table, as
7 * used by Sabre_DAV_Auth_Backend_PDO.
9 * It assumes all principals are in a single collection. The default collection
10 * is 'principals/', but this can be overriden.
14 * @copyright Copyright (C) 2007-2012 Rooftop Solutions. All rights reserved.
15 * @author Evert Pot (http://www.rooftopsolutions.nl/)
16 * @license http://code.google.com/p/sabredav/wiki/License Modified BSD License
18 class Sabre_DAVACL_PrincipalBackend_PDO implements Sabre_DAVACL_IPrincipalBackend {
28 * PDO table name for 'principals'
35 * PDO table name for 'group members'
39 protected $groupMembersTableName;
42 * A list of additional fields to support
46 protected $fieldMap = array(
49 * This property can be used to display the users' real name.
51 '{DAV:}displayname' => array(
52 'dbField' => 'displayname',
56 * This property is actually used by the CardDAV plugin, where it gets
57 * mapped to {http://calendarserver.orgi/ns/}me-card.
59 * The reason we don't straight-up use that property, is because
60 * me-card is defined as a property on the users' addressbook
63 '{http://sabredav.org/ns}vcard-url' => array(
64 'dbField' => 'vcardurl',
67 * This is the users' primary email-address.
69 '{http://sabredav.org/ns}email-address' => array(
75 * Sets up the backend.
78 * @param string $tableName
79 * @param string $groupMembersTableName
81 public function __construct(PDO $pdo, $tableName = 'principals', $groupMembersTableName = 'groupmembers') {
84 $this->tableName = $tableName;
85 $this->groupMembersTableName = $groupMembersTableName;
91 * Returns a list of principals based on a prefix.
93 * This prefix will often contain something like 'principals'. You are only
94 * expected to return principals that are in this base path.
96 * You are expected to return at least a 'uri' for every user, you can
97 * return any additional properties if you wish so. Common properties are:
99 * {http://sabredav.org/ns}email-address - This is a custom SabreDAV
100 * field that's actualy injected in a number of other properties. If
101 * you have an email address, use this property.
103 * @param string $prefixPath
106 public function getPrincipalsByPrefix($prefixPath) {
112 foreach($this->fieldMap as $key=>$value) {
113 $fields[] = $value['dbField'];
115 $result = $this->pdo->query('SELECT '.implode(',', $fields).' FROM '. $this->tableName);
117 $principals = array();
119 while($row = $result->fetch(PDO::FETCH_ASSOC)) {
121 // Checking if the principal is in the prefix
122 list($rowPrefix) = Sabre_DAV_URLUtil::splitPath($row['uri']);
123 if ($rowPrefix !== $prefixPath) continue;
126 'uri' => $row['uri'],
128 foreach($this->fieldMap as $key=>$value) {
129 if ($row[$value['dbField']]) {
130 $principal[$key] = $row[$value['dbField']];
133 $principals[] = $principal;
142 * Returns a specific principal, specified by it's path.
143 * The returned structure should be the exact same as from
144 * getPrincipalsByPrefix.
146 * @param string $path
149 public function getPrincipalByPath($path) {
156 foreach($this->fieldMap as $key=>$value) {
157 $fields[] = $value['dbField'];
159 $stmt = $this->pdo->prepare('SELECT '.implode(',', $fields).' FROM '. $this->tableName . ' WHERE uri = ?');
160 $stmt->execute(array($path));
162 $row = $stmt->fetch(PDO::FETCH_ASSOC);
167 'uri' => $row['uri'],
169 foreach($this->fieldMap as $key=>$value) {
170 if ($row[$value['dbField']]) {
171 $principal[$key] = $row[$value['dbField']];
179 * Updates one ore more webdav properties on a principal.
181 * The list of mutations is supplied as an array. Each key in the array is
182 * a propertyname, such as {DAV:}displayname.
184 * Each value is the actual value to be updated. If a value is null, it
187 * This method should be atomic. It must either completely succeed, or
188 * completely fail. Success and failure can simply be returned as 'true' or
191 * It is also possible to return detailed failure information. In that case
192 * an array such as this should be returned:
196 * '{DAV:}prop1' => null,
199 * '{DAV:}prop2' => null,
202 * '{DAV:}prop3' => null,
205 * '{DAV:}prop4' => null,
209 * In this previous example prop1 was successfully updated or deleted, and
210 * prop2 was succesfully created.
212 * prop3 failed to update due to '403 Forbidden' and because of this prop4
213 * also could not be updated with '424 Failed dependency'.
215 * This last example was actually incorrect. While 200 and 201 could appear
216 * in 1 response, if there's any error (403) the other properties should
217 * always fail with 423 (failed dependency).
219 * But anyway, if you don't want to scratch your head over this, just
220 * return true or false.
222 * @param string $path
223 * @param array $mutations
226 public function updatePrincipal($path, $mutations) {
228 $updateAble = array();
229 foreach($mutations as $key=>$value) {
231 // We are not aware of this field, we must fail.
232 if (!isset($this->fieldMap[$key])) {
241 // Adding the rest to the response as a 424
242 foreach($mutations as $subKey=>$subValue) {
243 if ($subKey !== $key) {
244 $response[424][$subKey] = null;
250 $updateAble[$this->fieldMap[$key]['dbField']] = $value;
254 // No fields to update
255 $query = "UPDATE " . $this->tableName . " SET ";
258 foreach($updateAble as $key => $value) {
263 $query.= "$key = :$key ";
265 $query.='WHERE uri = :uri';
266 $stmt = $this->pdo->prepare($query);
267 $updateAble['uri'] = $path;
268 $stmt->execute($updateAble);
275 * This method is used to search for principals matching a set of
278 * This search is specifically used by RFC3744's principal-property-search
279 * REPORT. You should at least allow searching on
280 * http://sabredav.org/ns}email-address.
282 * The actual search should be a unicode-non-case-sensitive search. The
283 * keys in searchProperties are the WebDAV property names, while the values
284 * are the property values to search on.
286 * If multiple properties are being searched on, the search should be
289 * This method should simply return an array with full principal uri's.
291 * If somebody attempted to search on a property the backend does not
292 * support, you should simply return 0 results.
294 * You can also just return 0 results if you choose to not support
295 * searching at all, but keep in mind that this may stop certain features
298 * @param string $prefixPath
299 * @param array $searchProperties
302 public function searchPrincipals($prefixPath, array $searchProperties) {
304 $query = 'SELECT uri FROM ' . $this->tableName . ' WHERE 1=1 ';
306 foreach($searchProperties as $property => $value) {
310 case '{DAV:}displayname' :
311 $query.=' AND displayname LIKE ?';
312 $values[] = '%' . $value . '%';
314 case '{http://sabredav.org/ns}email-address' :
315 $query.=' AND email LIKE ?';
316 $values[] = '%' . $value . '%';
319 // Unsupported property
325 $stmt = $this->pdo->prepare($query);
326 $stmt->execute($values);
328 $principals = array();
329 while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
331 // Checking if the principal is in the prefix
332 list($rowPrefix) = Sabre_DAV_URLUtil::splitPath($row['uri']);
333 if ($rowPrefix !== $prefixPath) continue;
335 $principals[] = $row['uri'];
344 * Returns the list of members for a group-principal
346 * @param string $principal
349 public function getGroupMemberSet($principal) {
351 $principal = $this->getPrincipalByPath($principal);
352 if (!$principal) throw new Sabre_DAV_Exception('Principal not found');
354 $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 = ?');
355 $stmt->execute(array($principal['id']));
358 while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
359 $result[] = $row['uri'];
366 * Returns the list of groups a principal is a member of
368 * @param string $principal
371 public function getGroupMembership($principal) {
373 $principal = $this->getPrincipalByPath($principal);
374 if (!$principal) throw new Sabre_DAV_Exception('Principal not found');
376 $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 = ?');
377 $stmt->execute(array($principal['id']));
380 while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
381 $result[] = $row['uri'];
388 * Updates the list of group members for a group principal.
390 * The principals should be passed as a list of uri's.
392 * @param string $principal
393 * @param array $members
396 public function setGroupMemberSet($principal, array $members) {
398 // Grabbing the list of principal id's.
399 $stmt = $this->pdo->prepare('SELECT id, uri FROM '.$this->tableName.' WHERE uri IN (? ' . str_repeat(', ? ', count($members)) . ');');
400 $stmt->execute(array_merge(array($principal), $members));
402 $memberIds = array();
405 while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
406 if ($row['uri'] == $principal) {
407 $principalId = $row['id'];
409 $memberIds[] = $row['id'];
412 if (!$principalId) throw new Sabre_DAV_Exception('Principal not found');
414 // Wiping out old members
415 $stmt = $this->pdo->prepare('DELETE FROM '.$this->groupMembersTableName.' WHERE principal_id = ?;');
416 $stmt->execute(array($principalId));
418 foreach($memberIds as $memberId) {
420 $stmt = $this->pdo->prepare('INSERT INTO '.$this->groupMembersTableName.' (principal_id, member_id) VALUES (?, ?);');
421 $stmt->execute(array($principalId, $memberId));