1: <?php
  2: /**
  3:  * Horde_Share_Sqlng provides the next-generation SQL backend driver for the
  4:  * Horde_Share library.
  5:  *
  6:  * Copyright 2011-2012 Horde LLC (http://www.horde.org/)
  7:  *
  8:  * See the enclosed file COPYING for license information (LGPL). If you
  9:  * did not receive this file, see http://www.horde.org/licenses/lgpl21.
 10:  *
 11:  * @author  Jan Schneider <jan@horde.org>
 12:  * @package Share
 13:  */
 14: 
 15: /**
 16:  * @package Share
 17:  */
 18: class Horde_Share_Sqlng extends Horde_Share_Sql
 19: {
 20:     /* Serializable version */
 21:     const VERSION = 1;
 22: 
 23:     /**
 24:      * The Horde_Share_Object subclass to instantiate objects as
 25:      *
 26:      * @var string
 27:      */
 28:     protected $_shareObject = 'Horde_Share_Object_Sqlng';
 29: 
 30:     /**
 31:      * A list of available permission.
 32:      *
 33:      * This is necessary to unset certain permission when updating existing
 34:      * share objects.
 35:      *
 36:      * @param array
 37:      */
 38:     protected $_availablePermissions = array();
 39: 
 40:     /**
 41:      *
 42:      * @see Horde_Share_Base::__construct()
 43:      */
 44:     public function __construct($app, $user, Horde_Perms_Base $perms,
 45:                                 Horde_Group_Base $groups)
 46:     {
 47:         parent::__construct($app, $user, $perms, $groups);
 48:         $this->_table = $this->_app . '_sharesng';
 49:     }
 50: 
 51:     /**
 52:      * Passes the available permissions to the share object.
 53:      *
 54:      * @param Horde_Share_Object $object
 55:      */
 56:     public function initShareObject(Horde_Share_Object $object)
 57:     {
 58:         parent::initShareObject($object);
 59:         $object->availablePermissions = array_keys($this->_availablePermissions);
 60:     }
 61: 
 62:     /**
 63:      * Returns an array of all shares that $userid has access to.
 64:      *
 65:      * @param string $userid  The userid of the user to check access for.
 66:      * @param array $params   Additional parameters for the search.
 67:      *<pre>
 68:      *  'perm'          Require this level of permissions. Horde_Perms constant.
 69:      *  'attributes'    Restrict shares to these attributes. A hash or username.
 70:      *  'from'          Offset. Start at this share
 71:      *  'count'         Limit.  Only return this many.
 72:      *  'sort_by'       Sort by attribute.
 73:      *  'direction'     Sort by direction.
 74:      *  'parent'        Start at this share in the hierarchy. Either share_id or
 75:      *                  Horde_Share_Object
 76:      *  'all_levels'    List all levels or just the direct children of parent?
 77:      *</pre>
 78:      *
 79:      * @return array  The shares the user has access to.
 80:      * @throws Horde_Share_Exception
 81:      */
 82:     public function listShares($userid, array $params = array())
 83:     {
 84:         $params = array_merge(array('perm' => Horde_Perms::SHOW,
 85:                                     'attributes' => null,
 86:                                     'from' => 0,
 87:                                     'count' => 0,
 88:                                     'sort_by' => null,
 89:                                     'direction' => 0,
 90:                                     'parent' => null,
 91:                                     'all_levels' => true),
 92:                               $params);
 93: 
 94:         $key = md5(serialize(array($userid, $params)));
 95:         if (isset($this->_listcache[$key])) {
 96:             return $this->_listcache[$key];
 97:         }
 98: 
 99:         $perms = $this->convertBitmaskToArray($params['perm']);
100:         $shareids = null;
101:         if (!empty($userid)) {
102:             list($users, $groups, $shareids) = $this->_getUserAndGroupShares($userid, $perms);
103:         }
104: 
105:         if (is_null($params['sort_by'])) {
106:             $sortfield = 'share_id';
107:         } elseif ($params['sort_by'] == 'owner' || $params['sort_by'] == 'id') {
108:             $sortfield = 'share_' . $params['sort_by'];
109:         } else {
110:             $sortfield = 'attribute_' . $params['sort_by'];
111:         }
112:         $where = $this->_getShareCriteria($userid, $perms, $params['attributes'], $shareids, $params['parent'], $params['all_levels']);
113:         $query = 'SELECT DISTINCT * FROM ' . $this->_table . ' s ' .
114:             (!empty($where) ? ' WHERE ' . $where : '')
115:             . ' ORDER BY ' . $sortfield
116:             . (($params['direction'] == 0) ? ' ASC' : ' DESC');
117: 
118:         $query = $this->_db->addLimitOffset($query, array('limit' => $params['count'], 'offset' => $params['from']));
119: 
120:         try {
121:             $rows = $this->_db->selectAll($query);
122:         } catch (Horde_Db_Exception $e) {
123:             throw new Horde_Share_Exception($e);
124:         }
125: 
126:         $sharelist = array();
127:         foreach ($rows as $share) {
128:             $share = $this->_fromDriverCharset($share);
129:             $this->_loadPermissions($share);
130:             $sharelist[$share['share_name']] = $this->_createObject($share);
131:         }
132: 
133:         // Run the results through the callback, if configured.
134:         if (!empty($this->_callbacks['list'])) {
135:             $sharelist = $this->runCallback('list', array($userid, $sharelist, $params));
136:         }
137:         $this->_listcache[$key] = $sharelist;
138: 
139:         return $this->_listcache[$key];
140:     }
141: 
142:     /**
143:      * Returns the count of all shares that $userid has access to.
144:      *
145:      * @param string  $userid      The userid of the user to check access for.
146:      * @param integer $perm        The level of permissions required.
147:      * @param mixed   $attributes  Restrict the shares counted to those
148:      *                             matching $attributes. An array of
149:      *                             attribute/values pairs or a share owner
150:      *                             username.
151:      * @param mixed  $parent      The share to start searching from
152:      *                            (Horde_Share_Object, share_id, or null)
153:      * @param boolean $allLevels  Return all levels, or just the direct
154:      *                            children of $parent?
155:      *
156:      * @return integer  Number of shares the user has access to.
157:      * @throws Horde_Share_Exception
158:      */
159:     public function countShares($userid, $perm = Horde_Perms::SHOW,
160:         $attributes = null, $parent = null, $allLevels = true)
161:     {
162:         $perms = $this->convertBitmaskToArray($perm);
163:         $shareids = null;
164:         if (!empty($userid)) {
165:             list(, , $shareids) = $this->_getUserAndGroupShares($userid, $perms);
166:         }
167: 
168:         $query = 'SELECT COUNT(DISTINCT share_id) FROM '
169:             . $this->_table . ' s WHERE '
170:             . $this->_getShareCriteria($userid, $perms, $attributes, $shareids, $parent, $allLevels);
171:         try {
172:             return $this->_db->selectValue($query);
173:         } catch (Horde_Db_Exception $e) {
174:             throw new Horde_Share_Exception($e);
175:         }
176:     }
177: 
178:     /**
179:      * Count the number of users who have shares with the given permissions
180:      * for the current user.
181:      *
182:      * @param integer $perm       The level of permissions required.
183:      * @param mixed $parent       The parent share to start looking in.
184:      *                            (Horde_Share_Object, share_id, or null).
185:      * @param boolean $allLevels  Return all levels, or just the direct
186:      *                            children of $parent?
187:      *
188:      * @return integer  Number of users.
189:      * @throws Horde_Share_Exception
190:      */
191:     public function countOwners($perm = Horde_Perms::SHOW, $parent = null, $allLevels = true)
192:     {
193:         $perms = self::convertBitmaskToArray($perm);
194:         $sql = 'SELECT COUNT(DISTINCT(s.share_owner)) FROM ' . $this->_table . ' s WHERE '
195:             . $this->_getShareCriteria($this->_user, $perms, null, $parent, $allLevels);
196: 
197:         try {
198:             $results = $this->_db->selectValue($sql);
199:         } catch (Horde_Db_Exception $e) {
200:             throw new Horde_Share_Exception($e);
201:         }
202: 
203:         return $results;
204:     }
205: 
206:     /**
207:      * Return a list of users who have shares with the given permissions
208:      * for the current user.
209:      *
210:      * @param integer $perm       The level of permissions required.
211:      * @param mixed  $parent      The parent share to start looking in.
212:      *                            (Horde_Share_Object, share_id, or null)
213:      * @param boolean $allLevels  Return all levels, or just the direct
214:      *                            children of $parent? Defaults to all levels.
215:      * @param integer $from       The user to start listing at.
216:      * @param integer $count      The number of users to return.
217:      *
218:      * @return array  List of users.
219:      * @throws Horde_Share_Exception
220:      */
221:     public function listOwners($perm = Horde_Perms::SHOW, $parent = null, $allLevels = true,
222:                                $from = 0, $count = 0)
223:     {
224:         $perms = self::convertBitmaskToArray($perm);
225:         $sql = 'SELECT DISTINCT(s.share_owner) FROM ' . $this->_table . ' s WHERE '
226:             . $this->_getShareCriteria($this->_user, $perms, null, $parent, $allLevels);
227: 
228:         if ($count) {
229:             $sql = $this->_db->addLimitOffset($sql, array('limit' => $count, 'offset' => $from));
230:         }
231: 
232:         try {
233:             $allowners = $this->_db->selectValues($sql);
234:         } catch (Horde_Db_Exception $e) {
235:             throw new Horde_Share_Exception($e);
236:         }
237: 
238:         $owners = array();
239:         foreach ($allowners as $owner) {
240:             if ($this->countShares($this->_user, $perm, $owner, $parent, $allLevels)) {
241:                 $owners[] = $owner;
242:             }
243:         }
244: 
245:         return $owners;
246:     }
247: 
248:     /**
249:      * Converts a bit mask number to a bit mask array.
250:      *
251:      * @param integer  A bit mask.
252:      *
253:      * @return array  The bit mask as an array.
254:      */
255:     static public function convertBitmaskToArray($perm)
256:     {
257:         $perms = array();
258:         for ($bit = 1; $perm; $bit *= 2, $perm >>= 1) {
259:             if ($perm % 2) {
260:                 $perms[] = $bit;
261:             }
262:         }
263:         return $perms;
264:     }
265: 
266:     /**
267:      * Builds a permission bit mask from all columns in a data row prefixed
268:      * with "perm_".
269:      *
270:      * @param array $row     A data row including permission columns.
271:      *
272:      * @return integer  A permission mask.
273:      */
274:     protected function _buildPermsFromRow($row)
275:     {
276:         $perms = 0;
277:         foreach ($row as $column => $value) {
278:             if (substr($column, 0, 5) != 'perm_') {
279:                 continue;
280:             }
281:             $perm = (int)substr($column, 5);
282:             $this->_availablePermissions[$perm] = true;
283:             if ($value) {
284:                 $perms |= $perm;
285:             }
286:         }
287:         return $perms;
288:     }
289: 
290:     /**
291:      * Converts the permissions from the database table format into the
292:      * Horde_Share format.
293:      *
294:      * @param array $data  The share object data to convert.
295:      */
296:     protected function _getSharePerms(&$data)
297:     {
298:         $data['perm']['type'] = 'matrix';
299:         $data['perm']['default'] = $data['perm']['guest'] = $data['perm']['creator'] = 0;
300:         foreach ($data as $column => $value) {
301:             $perm = explode('_', $column, 3);
302:             if ($perm[0] != 'perm' || count($perm) != 3) {
303:                 continue;
304:             }
305:             $permvalue = (int)$perm[2];
306:             $this->_availablePermissions[$permvalue] = true;
307:             if ($value) {
308:                 $data['perm'][$perm[1]] |= $permvalue;
309:             }
310:             unset($data[$column]);
311:         }
312:     }
313: 
314:     /**
315:      * Returns the records and share IDs from the user and group tables that
316:      * match the search criteria.
317:      *
318:      * @param string $userid     The userid of the user to check access for.
319:      * @param array $perms       The level of permissions required.
320:      *
321:      * @return array  A set of user, groups, and shareids.
322:      */
323:     protected function _getUserAndGroupShares($userid, array $perms)
324:     {
325:         $shareids = array();
326: 
327:         // Get users permissions.
328:         $query = 'SELECT * FROM ' . $this->_table
329:             . '_users WHERE user_uid = ' .  $this->_db->quote($userid)
330:             . ' AND (' . $this->_getPermsCriteria('perm', $perms) . ')';
331:         try {
332:             $users = $this->_db->selectAll($query);
333:         } catch (Horde_Db_Exception $e) {
334:             throw new Horde_Share_Exception($e->getMessage());
335:         }
336:         foreach ($users as $user) {
337:             $shareids[] = $user['share_id'];
338:         }
339: 
340:         // Get groups permissions.
341:         $groups = array();
342:         try {
343:             $groupNames = $this->_groups->listGroups($userid);
344:             if ($groupNames) {
345:                 $group_ids = array();
346:                 foreach (array_keys($groupNames) as $id) {
347:                     $group_ids[] = $this->_db->quote((string)$id);
348:                 }
349:                 $query = 'SELECT * FROM ' . $this->_table
350:                     . '_groups WHERE group_uid IN ('
351:                     . implode(',', $group_ids) . ') AND ('
352:                     . $this->_getPermsCriteria('perm', $perms) . ')';
353:                 try {
354:                     $groups = $this->_db->selectAll($query);
355:                 } catch (Horde_Db_Exception $e) {
356:                     throw new Horde_Share_Exception($e->getMessage());
357:                 }
358:                 foreach ($groups as $group) {
359:                     $shareids[] = $group['share_id'];
360:                 }
361:             }
362:         } catch (Horde_Group_Exception $e) {
363:             $this->_logger->err($e);
364:         }
365: 
366:         return array($users, $groups, array_unique($shareids));
367:     }
368: 
369:     /**
370:      * Returns a criteria statement for querying shares.
371:      *
372:      * @param string $userid     The userid of the user to check access for.
373:      * @param array $perms       The level of permissions required.
374:      * @param array $attributes  Restrict the shares returned to those who
375:      *                           have these attribute values.
376:      * @param array $shareids    Additional share IDs from user and group
377:      *                           permissions.
378:      *
379:      * @return string  The criteria string for fetching this user's shares.
380:      */
381:     protected function _getShareCriteria($userid, array $perms, $attributes,
382:                                          $shareids = null, $parent = null,
383:                                          $allLevels = true)
384:     {
385:         /* Convert to driver's keys */
386:         $attributes = $this->_toDriverKeys($attributes);
387: 
388:         /* ...and to driver charset */
389:         $attributes = $this->toDriverCharset($attributes);
390: 
391:         $where = '';
392:         if (empty($userid)) {
393:             $where = $this->_getPermsCriteria('perm_guest', $perms);
394:         } else {
395:             // (owner == $userid)
396:             $where .= 'share_owner = ' . $this->_db->quote($userid);
397: 
398:             // (name == perm_creator and val & $perm)
399:             $where .= ' OR ' . $this->_getPermsCriteria('perm_creator', $perms);
400: 
401:             // (name == perm_creator and val & $perm)
402:             $where .= ' OR ' . $this->_getPermsCriteria('perm_default', $perms);
403: 
404:             if ($shareids) {
405:                 $where .= ' OR share_id IN (' . implode(',', $shareids) . ')';
406:             }
407:         }
408: 
409:         if (is_array($attributes)) {
410:             // Build attribute/key filter.
411:             $where = '(' . $where . ') ';
412:             foreach ($attributes as $key => $value) {
413:                 if (is_array($value)) {
414:                     $value = array_map(array($this->_db, 'quote'), $value);
415:                     $where .= ' AND ' . $key . ' IN (' . implode(', ', $value) . ')';
416:                 } else {
417:                     $where .= ' AND ' . $key . ' = ' . $this->_db->quote($value);
418:                 }
419:             }
420:         } elseif (!empty($attributes)) {
421:             // Restrict to shares owned by the user specified in the
422:             // $attributes string.
423:             $where = '(' . $where . ') AND share_owner = ' . $this->_db->quote($attributes);
424:         }
425: 
426:         // See if we need to filter by parent or get the parent object
427:         if ($parent != null) {
428:             if (!($parent instanceof Horde_Share_Object)) {
429:                 $parent = $this->getShareById($parent);
430:             }
431: 
432:             // Need to append the parent's share id to the list of parents in
433:             // order to search the share_parents field.
434:             $parents = $parent->get('parents') . ':' . $parent->getId();
435:             if ($allLevels) {
436:                 $where_parent = '(share_parents = ' . $this->_db->quote($parents)
437:                     . ' OR share_parents LIKE ' . $this->_db->quote($parents . ':%') . ')';
438:             } else {
439:                 $where_parent = 's.share_parents = ' . $this->_db->quote($parents);
440:             }
441:         } elseif (!$allLevels) {
442:             // No parents, and we only want the root.
443:             $where_parent = "(s.share_parents = '' OR s.share_parents IS NULL)";
444:         }
445:         if (!empty($where_parent)) {
446:             if (empty($where)) {
447:                 $where = $where_parent;
448:             } else {
449:                 $where = '(' . $where . ') AND ' . $where_parent;
450:             }
451:         }
452: 
453:         return $where;
454:     }
455: 
456:     /**
457:      * Builds an ANDed criteria snippet for a set or permissions.
458:      *
459:      * @param string $base  A column name prefix.
460:      * @param array $perms  A list of permissions.
461:      *
462:      * @return string  The generated criteria string.
463:      */
464:     protected function _getPermsCriteria($base, array $perms)
465:     {
466:         $criteria = array();
467:         foreach ($perms as $perm) {
468:             $criteria[] = $base . '_' . $perm . ' = ' . $this->_db->quoteTrue();
469:         }
470:         return implode(' OR ', $criteria);
471:     }
472: }
473: