1: <?php
  2: /**
  3:  * This class provides an SQL driver for the Horde group system.
  4:  *
  5:  * Copyright 1999-2012 Horde LLC (http://www.horde.org/)
  6:  *
  7:  * See the enclosed file COPYING for license information (LGPL). If you
  8:  * did not receive this file, see http://www.horde.org/licenses/lgpl21.
  9:  *
 10:  * @author   Duck <duck@obala.net>
 11:  * @author   Jan Schneider <jan@horde.org>
 12:  * @category Horde
 13:  * @license  http://www.horde.org/licenses/lgpl21 LGPL 2.1
 14:  * @package  Group
 15:  */
 16: class Horde_Group_Sql extends Horde_Group_Base
 17: {
 18:     /**
 19:      * Handle for the current database connection.
 20:      *
 21:      * @var Horde_Db_Adapter
 22:      */
 23:     protected $_db;
 24: 
 25:     /**
 26:      * Constructor.
 27:      */
 28:     public function __construct($params)
 29:     {
 30:         if (!isset($params['db'])) {
 31:             throw new Horde_Group_Exception('The \'db\' parameter is missing.');
 32:         }
 33:         $this->_db = $params['db'];
 34:     }
 35: 
 36:     /**
 37:      * Returns whether the group backend is read-only.
 38:      *
 39:      * @return boolean
 40:      */
 41:     public function readOnly()
 42:     {
 43:         return false;
 44:     }
 45: 
 46:     /**
 47:      * Creates a new group.
 48:      *
 49:      * @param string $name   A group name.
 50:      * @param string $email  The group's email address.
 51:      *
 52:      * @return mixed  The ID of the created group.
 53:      * @throws Horde_Group_Exception
 54:      */
 55:     public function create($name, $email = null)
 56:     {
 57:         try {
 58:             return $this->_db->insert(
 59:                 'INSERT INTO horde_groups (group_name, group_email, group_parents) VALUES (?, ?, ?)',
 60:                 array($name, $email, ''));
 61:         } catch (Horde_Db_Exception $e) {
 62:             throw new Horde_Group_Exception($e);
 63:         }
 64:     }
 65: 
 66:     /**
 67:      * Renames a group.
 68:      *
 69:      * @param mixed $gid    A group ID.
 70:      * @param string $name  The new name.
 71:      *
 72:      * @throws Horde_Group_Exception
 73:      */
 74:     public function rename($gid, $name)
 75:     {
 76:         try {
 77:             return $this->_db->update(
 78:                 'UPDATE horde_groups SET group_name = ? WHERE group_uid = ?',
 79:                 array($name, $gid));
 80:         } catch (Horde_Db_Exception $e) {
 81:             throw new Horde_Group_Exception($e);
 82:         }
 83:     }
 84: 
 85:     /**
 86:      * Removes a group.
 87:      *
 88:      * @param mixed $gid  A group ID.
 89:      *
 90:      * @throws Horde_Group_Exception
 91:      */
 92:     public function remove($gid)
 93:     {
 94:         try {
 95:             $this->_db->beginDbTransaction();
 96:             $this->_db->delete(
 97:                 'DELETE FROM horde_groups_members WHERE group_uid = ?',
 98:                 array($gid));
 99:             $this->_db->delete(
100:                 'DELETE FROM horde_groups WHERE group_uid = ?',
101:                 array($gid));
102:             $this->_db->commitDbTransaction();
103:         } catch (Horde_Db_Exception $e) {
104:             throw new Horde_Group_Exception($e);
105:         }
106:     }
107: 
108:     /**
109:      * Checks if a group exists.
110:      *
111:      * @param mixed $gid  A group ID.
112:      *
113:      * @return boolean  True if the group exists.
114:      * @throws Horde_Group_Exception
115:      */
116:     public function exists($gid)
117:     {
118:         try {
119:             return (bool)$this->_db->selectValue(
120:                 'SELECT 1 FROM horde_groups WHERE group_uid = ?',
121:                 array($gid));
122:         } catch (Horde_Db_Exception $e) {
123:             throw new Horde_Group_Exception($e);
124:         }
125:     }
126: 
127:     /**
128:      * Returns a group name.
129:      *
130:      * @param mixed $gid  A group ID.
131:      *
132:      * @return string  The group's name.
133:      * @throws Horde_Group_Exception
134:      */
135:     public function getName($gid)
136:     {
137:         try {
138:             return $this->_db->selectValue(
139:                 'SELECT group_name FROM horde_groups WHERE group_uid = ?',
140:                 array($gid));
141:         } catch (Horde_Db_Exception $e) {
142:             throw new Horde_Group_Exception($e);
143:         }
144:     }
145: 
146:     /**
147:      * Returns all available attributes of a group.
148:      *
149:      * @param mixed $gid  A group ID.
150:      *
151:      * @return array  The group's date.
152:      * @throws Horde_Group_Exception
153:      * @throws Horde_Exception_NotFound
154:      */
155:     public function getData($gid)
156:     {
157:         try {
158:             $result = $this->_db->selectOne(
159:                 'SELECT * FROM horde_groups WHERE group_uid = ?',
160:                 array($gid));
161:             if (!$result) {
162:                 throw new Horde_Exception_NotFound('Group with the ID ' . $gid . ' not found');
163:             }
164:         } catch (Horde_Db_Exception $e) {
165:             throw new Horde_Group_Exception($e);
166:         }
167:         $data = array();
168:         foreach ($result as $attribute => $value) {
169:             $data[preg_replace('/^group_/', '', $attribute)] = $value;
170:         }
171:         return $data;
172:     }
173: 
174:     /**
175:      * Sets one or more attributes of a group.
176:      *
177:      * @param mixed $gid               A group ID.
178:      * @param array|string $attribute  An attribute name or a hash of
179:      *                                 attributes.
180:      * @param string $value            An attribute value if $attribute is a
181:      *                                 string.
182:      *
183:      * @throws Horde_Group_Exception
184:      */
185:     public function setData($gid, $attribute, $value = null)
186:     {
187:         $attributes = is_array($attribute)
188:             ? $attribute
189:             : array($attribute => $value);
190:         $updates = array();
191:         foreach ($attributes as $attribute => $value) {
192:             $updates[] = $this->_db->quoteColumnName('group_' . $attribute)
193:                 . ' = ' . $this->_db->quote($value);
194:         }
195:         try {
196:             $this->_db->execute('UPDATE horde_groups SET ' . implode(', ', $updates) . ' WHERE group_uid = ?',
197:                                 array($gid));
198:         } catch (Horde_Db_Exception $e) {
199:             throw new Horde_Group_Exception($e);
200:         }
201:     }
202: 
203:     /**
204:      * Returns a list of all groups a user may see, with IDs as keys and names
205:      * as values.
206:      *
207:      * @param string $member  Only return groups that this user is a member of.
208:      *
209:      * @return array  All existing groups.
210:      * @throws Horde_Group_Exception
211:      */
212:     public function listAll($member = null)
213:     {
214:         if (!is_null($member)) {
215:             return $this->listGroups($member);
216:         }
217: 
218:         try {
219:             return $this->_db->selectAssoc('SELECT group_uid, group_name FROM horde_groups');
220:         } catch (Horde_Db_Exception $e) {
221:             throw new Horde_Group_Exception($e);
222:         }
223:     }
224: 
225:     /**
226:      * Returns a list of users in a group.
227:      *
228:      * @param mixed $gid  A group ID.
229:      *
230:      * @return array  List of group users.
231:      * @throws Horde_Group_Exception
232:      */
233:     public function listUsers($gid)
234:     {
235:         try {
236:             return $this->_db->selectValues(
237:                 'SELECT user_uid FROM horde_groups_members WHERE group_uid = ? ORDER BY user_uid ASC',
238:                 array($gid));
239:         } catch (Horde_Db_Exception $e) {
240:             throw new Horde_Group_Exception($e);
241:         }
242:     }
243: 
244:     /**
245:      * Returns a list of groups a user belongs to.
246:      *
247:      * @param string $user  A user name.
248:      *
249:      * @return array  A list of groups, with IDs as keys and names as values.
250:      * @throws Horde_Group_Exception
251:      */
252:     public function listGroups($user)
253:     {
254:         try {
255:             return $this->_db->selectAssoc(
256:                 'SELECT g.group_uid AS group_uid, g.group_name AS group_name FROM horde_groups g, horde_groups_members m WHERE m.user_uid = ? AND g.group_uid = m.group_uid ORDER BY g.group_name',
257:                 array($user));
258:         } catch (Horde_Db_Exception $e) {
259:             throw new Horde_Group_Exception($e);
260:         }
261:     }
262: 
263:     /**
264:      * Add a user to a group.
265:      *
266:      * @param mixed $gid    A group ID.
267:      * @param string $user  A user name.
268:      *
269:      * @throws Horde_Group_Exception
270:      */
271:     public function addUser($gid, $user)
272:     {
273:         try {
274:             $this->_db->insert(
275:                 'INSERT INTO horde_groups_members (group_uid, user_uid) VALUES (?, ?)', array($gid, $user));
276:         } catch (Horde_Db_Exception $e) {
277:             throw new Horde_Group_Exception($e);
278:         }
279:     }
280: 
281:     /**
282:      * Removes a user from a group.
283:      *
284:      * @param mixed $gid    A group ID.
285:      * @param string $user  A user name.
286:      *
287:      * @throws Horde_Group_Exception
288:      */
289:     public function removeUser($gid, $user)
290:     {
291:         try {
292:             $this->_db->delete(
293:                 'DELETE FROM horde_groups_members WHERE group_uid = ? AND user_uid = ?',
294:                 array($gid, $user));
295:         } catch (Horde_Db_Exception $e) {
296:             throw new Horde_Group_Exception($e);
297:         }
298:     }
299: 
300:     /**
301:      * Searches for group names.
302:      *
303:      * @param string $name  A search string.
304:      *
305:      * @return array  A list of matching groups, with IDs as keys and names as
306:      *                values.
307:      * @throws Horde_Group_Exception
308:      */
309:     public function search($name)
310:     {
311:         try {
312:             return $this->_db->selectAssoc(
313:                 'SELECT group_uid, group_name FROM horde_groups WHERE group_name LIKE ?',
314:                 array('%' . $name . '%'));
315:         } catch (Horde_Db_Exception $e) {
316:             throw new Horde_Group_Exception($e);
317:         }
318:     }
319: }
320: