1: <?php
  2: /**
  3:  * Copyright 2003-2012 Horde LLC (http://www.horde.org/)
  4:  *
  5:  * See the enclosed file LICENSE for license information (BSD). If you did
  6:  * did not receive this file, see http://cvs.horde.org/co.php/vilma/LICENSE.
  7:  *
  8:  * @author Marko Djukic <marko@oblo.com>
  9:  * @package Vilma
 10:  */
 11: class Vilma_Driver_Sql extends Vilma_Driver
 12: {
 13:     /**
 14:      * @var DB
 15:      */
 16:     protected $_db;
 17: 
 18:     /**
 19:      * Constructor.
 20:      *
 21:      * @param array $params  Any parameters needed for this driver.
 22:      */
 23:     public function __construct($params)
 24:     {
 25:         parent::__construct($params);
 26:         $this->_initialize();
 27:     }
 28: 
 29:     /**
 30:      * Returns the list of domains from the backend.
 31:      *
 32:      * @return array  All the domains and their data in an array.
 33:      */
 34:     public function getDomains()
 35:     {
 36:         $sql = 'SELECT ' . $this->_getTableFields('domains')
 37:             . ' FROM ' . $this->_params['tables']['domains']
 38:             . ' ORDER BY ' . $this->_getTableField('domains', 'domain_name');
 39:         Horde::logMessage($sql, 'DEBUG');
 40:         return $this->_db->getAll($sql, null, DB_FETCHMODE_ASSOC);
 41:     }
 42: 
 43:     /**
 44:      * Returns the specified domain information from the backend.
 45:      *
 46:      * @param integer $domain_id  The id of the domain to fetch.
 47:      *
 48:      * @return array  The domain's information in an array.
 49:      */
 50:     public function getDomain($domain_id)
 51:     {
 52:         $sql = 'SELECT ' . $this->_getTableFields('domains')
 53:             . ' FROM ' . $this->_params['tables']['domains']
 54:             . ' WHERE ' . $this->_getTableField('domains', 'domain_id') . ' = ?';
 55:         Horde::logMessage($sql, 'DEBUG');
 56:         return $this->_db->getRow($sql, array((int)$domain_id), DB_FETCHMODE_ASSOC);
 57:     }
 58: 
 59:     /**
 60:      * Given a domain name returns the information from the backend.
 61:      *
 62:      * @param string $name  The name of the domain to fetch.
 63:      *
 64:      * @return array  The domain's information in an array.
 65:      */
 66:     public function getDomainByName($domain_name)
 67:     {
 68:         $sql = 'SELECT ' . $this->_getTableFields('domains')
 69:             . ' FROM ' . $this->_params['tables']['domains']
 70:             . ' WHERE ' . $this->_getTableField('domains', 'domain_name') . ' = ?';
 71:         Horde::logMessage($sql, 'DEBUG');
 72:         return $this->_db->getRow($sql, array($domain_name), DB_FETCHMODE_ASSOC);
 73:     }
 74: 
 75:     /**
 76:      * Saves a domain with the provided information.
 77:      *
 78:      * @param array $info  Array of details to save the domain.
 79:      */
 80:     protected function _saveDomain($info)
 81:     {
 82:         $record = array('domain_name' => $info['name'],
 83:                         'domain_transport' => $info['transport'],
 84:                         'domain_max_users' => (int)$info['max_users'],
 85:                         'domain_quota' => (int)$info['quota']);
 86: 
 87:         if (empty($info['domain_id'])) {
 88:             $record['domain_id'] = $this->_db->nextId($this->_params['tables']['domains']);
 89:             $values = $this->_prepareRecord('domains', $record);
 90:             $sql = sprintf('INSERT INTO %s (%s) VALUES (%s)',
 91:                            $this->_params['tables']['domains'],
 92:                            implode(', ', array_keys($values)),
 93:                            implode(', ', array_fill(0, count($values), '?')));
 94:             $this->_db->insert($sql, $values);
 95:         } else {
 96:             $values = $this->_prepareRecord('domains', $record);
 97:             $sql = sprintf('UPDATE %s SET %s WHERE %s = ?',
 98:                            $this->_params['tables']['domains'],
 99:                            implode(' = ?, ', array_keys($values)) . ' = ?',
100:                            $this->_getTableField('domains', 'domain_id'));
101:             $values[] = (int)$info['domain_id'];
102:             $this->_db->update($sql, $values);
103:         }
104:     }
105: 
106:     /**
107:      * Deletes a domain.
108:      *
109:      * @param integer $domain_id  The id of the domain to delete.
110:      *
111:      * @throws Vilma_Exception
112:      */
113:     protected function _deleteDomain($domain_id)
114:     {
115:         $domain_record = $this->getDomain($domain_id);
116:         $domain_name = $domain_record['domain_name'];
117: 
118:         /* Delete all virtual emails for this domain. */
119:         $sql = 'DELETE FROM ' . $this->_params['tables']['virtuals'] .
120:                ' WHERE ' . $this->_getTableField('virtuals', 'virtual_email') . ' LIKE ?';
121:         $values = array('%@' . $domain_name);
122:         Horde_Exception_Pear::catchError($this->_db->query($sql, $values));
123: 
124:         /* Delete all users for this domain. */
125:         $sql = 'DELETE FROM ' . $this->_params['tables']['users'] .
126:                ' WHERE ' . $this->_getTableField('users', 'user_name') . ' LIKE ?';
127:         $values = array('%@' . $domain_name);
128:         Horde_Exception_Pear::catchError($this->_db->query($sql, $values));
129: 
130:         /* Finally delete the domain. */
131:         $sql = 'DELETE FROM ' . $this->_params['tables']['domains'] .
132:                ' WHERE ' . $this->_getTableField('domains', 'domain_id') . ' = ?';
133:         $values = array((int)$domain_id);
134: 
135:         Horde::logMessage($sql, 'DEBUG');
136:         return $this->_db->query($sql, $values);
137:     }
138: 
139:     /**
140:      * Returns the current number of users for a domain.
141:      *
142:      * @param string $domain_name  The name of the domain for which to
143:      *                             get the current number of users.
144:      *
145:      * @return integer  The current number of users.
146:      */
147:     public function getDomainNumUsers($domain_name)
148:     {
149:         $sql = 'SELECT count(' . $this->_getTableField('users', 'user_name') . ')'
150:             . ' FROM ' . $this->_params['tables']['users']
151:             . ' WHERE ' . $this->_getTableField('users', 'user_name') . ' LIKE ?';
152:         Horde::logMessage($sql, 'DEBUG');
153:         return $this->_db->getOne($sql, array('%@' . $domain_name));
154:     }
155: 
156:     /**
157:      * Returns all available users, if a domain name is passed then limit the
158:      * list of users only to those users.
159:      *
160:      * @param string $domain  The name of the domain for which to fetch the
161:      *                        users.
162:      *
163:      * @return array  The available users and their stored information.
164:      */
165:     public function getUsers($domain = null)
166:     {
167:         /* Put together the SQL statement. */
168:         if (is_null($domain)) {
169:             /* Fetch all users. */
170:             $sql = 'SELECT ' . $this->_getTableFields('users')
171:                 . ' FROM ' . $this->_params['tables']['users'];
172:             $values = array();
173:         } else {
174:             /* Fetch only users for a domain. */
175:             $user_field =  $this->_getTableField('users', 'user_name');
176:             $sql = 'SELECT ' . $this->_getTableFields('users')
177:                 . ' FROM ' . $this->_params['tables']['users']
178:                 . ' WHERE ' . $user_field . ' LIKE ?'
179:                 . ' ORDER BY ' . $user_field;
180:             $values = array('%@' . $domain);
181:         }
182:         Horde::logMessage($sql, 'DEBUG');
183:         return $this->_db->getAll($sql, $values, DB_FETCHMODE_ASSOC);
184:     }
185: 
186:     /**
187:      * Returns the user information for a given user id.
188:      *
189:      * @param integer $user_id  The id of the user for which to fetch
190:      *                          information.
191:      *
192:      * @return array  The user information.
193:      */
194:     public function getUser($user_id)
195:     {
196:         $sql = 'SELECT ' . $this->_getTableFields('users')
197:             . ' FROM ' . $this->_params['tables']['users']
198:             . ' WHERE ' . $this->_getTableField('users', 'user_id') . ' = ?';
199:         Horde::logMessage($sql, 'DEBUG');
200:         return $this->_db->getRow($sql, array((int)$user_id), DB_FETCHMODE_ASSOC);
201:     }
202: 
203:     /**
204:      * Saves a user to the backend.
205:      *
206:      * @param array $info  The user information to save.
207:      *
208:      * @return string  The user ID.
209:      * @throws Vilma_Exception
210:      */
211:     protected function _saveUser($info)
212:     {
213:         /* Access check (for domainkey). */
214:         $domain = Vilma::stripDomain($info['user_name']);
215:         $this->getDomainByName($domain);
216: 
217:         if (empty($info['user_id'])) {
218:             $info['user_id'] = $this->_db->nextId($this->_params['tables']['users']);
219:             $create = true;
220:         } else {
221:             $create = false;
222:         }
223: 
224:         // Slightly hackish.
225:         $mailboxes = Vilma_MailboxDriver::factory();
226:         $mail_dir_base = $mailboxes->getParam('mail_dir_base');
227:         if (is_null($mail_dir_base)) {
228:             $mail_dir_base = '?';
229:         }
230: 
231:         $tuple = array(
232:             'user_id'         => (int)$info['user_id'],
233:             'user_name'       => $info['user_name'],
234:             'user_full_name'  => $info['user_full_name'],
235:             'user_home_dir'   => $mail_dir_base,
236:             'user_mail_dir'   => $domain . '/' . Vilma::stripUser($info['user_name']) . '/',
237:             'user_mail_quota' => $this->getDomainQuota($domain) * 1024 * 1024,
238:             'user_enabled'    => (int)$info['user_enabled']);
239: 
240:         // UID and GID are slightly hackish (specific to maildrop driver), too.
241:         $tuple['user_uid'] = $mailboxes->getParam('uid');
242:         if (is_null($tuple['user_uid'])) {
243:             $tuple['user_uid'] = -1;
244:         }
245:         $tuple['user_gid'] = $mailboxes->getParam('gid');
246:         if (is_null($tuple['user_gid'])) {
247:             $tuple['user_gid'] = -1;
248:         }
249: 
250:         if (!empty($info['password'])) {
251:             $tuple['user_clear'] = $info['password'];
252:             $tuple['user_crypt'] = crypt($info['password'],
253:                                          substr($info['password'], 0, 2));
254:         } elseif ($create) {
255:             throw new Vilma_Exception(_("Password must be supplied when creating a new user."));
256:         }
257: 
258:         $values = $this->_prepareRecord('users', $tuple);
259:         if ($create) {
260:             $sql = sprintf('INSERT INTO %s (%s) VALUES (%s)',
261:                            $this->_params['tables']['users'],
262:                            implode(', ', array_keys($values)),
263:                            implode(', ', array_fill(0, count($values), '?')));
264:             $this->_db->insert($sql, $values);
265:         } else {
266:             $sql = sprintf('UPDATE %s SET %s WHERE %s = ?',
267:                            $this->_params['tables']['users'],
268:                            implode(' = ?, ', array_keys($values)) . ' = ?',
269:                            $this->_getTableField('users', 'user_id'));
270:             $values[] = (int)$info['user_id'];
271:             $this->_db->update($sql, $values);
272:         }
273: 
274:         return $info['user_id'];
275:     }
276: 
277:     /**
278:      * Deletes a user.
279:      *
280:      * @param integer $user_id  The id of the user to delete.
281:      *
282:      * @throws Vilma_Exception
283:      */
284:     public function deleteUser($user_id)
285:     {
286:         Horde_Exception_Pear::catchError($user = $this->getUser($user_id));
287: 
288:         /* Delete all virtual emails for this user. */
289:         $sql = 'DELETE FROM ' . $this->_params['tables']['virtuals'] .
290:                ' WHERE ' . $this->_getTableField('virtuals', 'virtual_destination') . ' = ?';
291:         $values = array($user['user_name']);
292: 
293:         Horde::logMessage($sql, 'DEBUG');
294:         Horde_Exception_Pear::catchError($this->_db->query($sql, $values));
295: 
296:         /* Delete the actual user. */
297:         $sql = 'DELETE FROM ' . $this->_params['tables']['users'] .
298:                ' WHERE ' . $this->_getTableField('users', 'user_id') . ' = ?';
299:         $values = array((int)$user_id);
300: 
301:         Horde::logMessage($sql, 'DEBUG');
302:         Horde_Exception_Pear::catchError($this->_db->query($sql, $values));
303: 
304:         Vilma_MailboxDriver::factory()
305:             ->deleteMailbox(Vilma::stripUser($user['user_name']),
306:                             Vilma::stripDomain($user['user_name']));
307:     }
308: 
309:     /**
310:      * Returns a list of all users, aliases, or groups and forwards for a
311:      * domain.
312:      *
313:      * @param string $domain      Domain on which to search.
314:      * @param string $type        Only return a specific type. One of 'all',
315:      *                            'user', 'alias','forward', or 'group'.
316:      * @param string $key         Sort list by this key.
317:      * @param integer $direction  Sort direction.
318:      *
319:      * @return array Account information for this domain
320:      */
321:     protected function _getAddresses($domain, $type = 'all')
322:     {
323:         $addresses = array();
324:         if ($type == 'all' || $type == 'user') {
325:             $addresses += $this->getUsers($domain);
326:         }
327:         if ($type == 'all' || $type == 'alias') {
328:             $addresses += $this->getVirtuals($domain);
329:         }
330:         return $addresses;
331:     }
332: 
333:     /**
334:      * Returns available virtual emails.
335:      *
336:      * @param string $filter  If passed a domain then return all virtual emails
337:      *                        for the domain, otherwise if passed a user name
338:      *                        return all virtual emails for that user.
339:      *
340:      * @return array  The available virtual emails.
341:      */
342:     public function getVirtuals($filter)
343:     {
344:         $email_field = $this->_getTableField('virtuals', 'virtual_email');
345:         $destination_field = $this->_getTableField('virtuals', 'virtual_destination');
346: 
347:         /* Check if filtering only for domain. */
348:         if (strpos($filter, '@') === false) {
349:             $where = $email_field . ' LIKE ?';
350:             $values = array('%@' . $filter);
351:         } else {
352:             $where = $destination_field . ' = ?';
353:             $values = array($filter);
354:         }
355: 
356:         $sql = 'SELECT ' . $this->_getTableFields('virtuals')
357:             . ' FROM ' . $this->_params['tables']['virtuals']
358:             . ' WHERE ' . $where
359:             . ' ORDER BY ' . $destination_field . ', ' . $email_field;
360: 
361:         Horde::logMessage($sql, 'DEBUG');
362:         return $this->_db->getAll($sql, $values, DB_FETCHMODE_ASSOC);
363:     }
364: 
365:     /**
366:      * Returns information for a virtual id.
367:      *
368:      * @param integer $virtual_id  The virtual id for which to return
369:      *                             information.
370:      *
371:      * @return array  The virtual email information.
372:      */
373:     public function getVirtual($virtual_id)
374:     {
375:         $sql = 'SELECT ' . $this->_getTableFields('virtuals')
376:             . ' FROM ' . $this->_params['tables']['virtuals']
377:             . ' WHERE ' . $this->_getTableField('virtuals', 'virtual_id') . ' = ?';
378: 
379:         Horde::logMessage($sql, 'DEBUG');
380:         $virtual = $this->_db->getRow($sql, array((int)$virtual_id), DB_FETCHMODE_ASSOC);
381:         $virtual['stripped_email'] = Vilma::stripUser($virtual['virtual_email']);
382: 
383:         return $virtual;
384:     }
385: 
386:     /**
387:      * Saves virtual email address to the backend.
388:      *
389:      * @param array $info     The virtual email data.
390:      * @param string $domain  The name of the domain for this virtual email.
391:      *
392:      * @throws Vilma_Exception
393:      */
394:     public function saveVirtual($info, $domain)
395:     {
396:         /* Access check (for domainkey) */
397:         $this->getDomainByName($domain);
398: 
399:         if (empty($info['virtual_id'])) {
400:             $info['virtual_id'] = $this->_db->nextId($this->_params['tables']['virtuals']);
401:             $sql = 'INSERT INTO ' . $this->_params['tables']['virtuals']
402:                 . ' (' . $this->_getTableField('virtuals', 'virtual_email') . ', '
403:                        . $this->_getTableField('virtuals', 'virtual_destination') . ', '
404:                        . $this->_getTableField('virtuals', 'virtual_id') . ') VALUES (?, ?, ?)';
405:         } else {
406:             $sql = 'UPDATE ' . $this->_params['tables']['virtuals']
407:                 . ' SET ' . $this->_getTableField('virtuals', 'virtual_email') . ' = ?, '
408:                           . $this->_getTableField('virtuals', 'virtual_destination') . ' = ?'
409:                 . ' WHERE ' . $this->_getTableField('virtuals', 'virtual_id') . ' = ?';
410:         }
411:         $values = array($info['stripped_email'] . '@' . $domain,
412:                         $info['virtual_destination'],
413:                         $info['virtual_id']);
414: 
415:         Horde::logMessage($sql, 'DEBUG');
416:         return $this->_db->query($sql, $values);
417:     }
418: 
419:     /**
420:      * Deletes a virtual email.
421:      *
422:      * @param integer $virtual_id  The id of the virtual email to delete.
423:      */
424:     public function deleteVirtual($virtual_id)
425:     {
426:         $sql = 'DELETE FROM ' . $this->_params['tables']['virtuals']
427:             . ' WHERE ' . $this->_getTableField('virtuals', 'virtual_id') . ' = ?';
428:         Horde::logMessage($sql, 'DEBUG');
429:         return $this->_db->query($sql, array($virtual_id));
430:     }
431: 
432:     /**
433:      * Returns the list of fields from a specific table for SQL statements.
434:      *
435:      * @return string
436:      */
437:     protected function _getTableFields($table)
438:     {
439:         if (empty($this->_params['tables'][$table . '_fields'])) {
440:             switch ($table) {
441:             case 'domains':
442:                 return 'domain_id, domain_name, domain_transport, domain_max_users, domain_quota';
443:             default:
444:                 return '*';
445:             }
446:         }
447: 
448:         $domainsFields = $this->_params['tables'][$table . '_fields'];
449:         foreach ($domainsFields as $defaultName => $customName) {
450:             $fields[] = $customName . ' AS ' . $defaultName;
451:         }
452: 
453:         return implode(', ', $fields);
454:     }
455: 
456:     /**
457:      * Returns the real name of a field from a specific table for SQL
458:      * statements.
459:      *
460:      * @return string
461:      */
462:     protected function _getTableField($table, $field)
463:     {
464:         if (empty($this->_params['tables'][$table . '_fields'])) {
465:             return $field;
466:         }
467:         return $this->_params['tables'][$table . '_fields'][$field];
468:     }
469: 
470:     /**
471:      *
472:      *
473:      * @return array
474:      */
475:     protected function _prepareRecord($table, $record)
476:     {
477:         if (empty($this->_params['tables'][$table . '_fields'])) {
478:             return $record;
479:         }
480: 
481:         $domainsFields = $this->_params['tables'][$table . '_fields'];
482:         $newRecord = array();
483:         foreach ($record as $defaultName => $value) {
484:             $newRecord[$domainsFields[$defaultName]] = $record[$defaultName];
485:         }
486:         return $newRecord;
487:     }
488: 
489:     /**
490:      * Initializes this backend, connects to the SQL database.
491:      *
492:      * @throws Vilma_Exception
493:      */
494:     protected function _initialize()
495:     {
496:         try {
497:             $this->_db = $GLOBALS['injector']->getInstance('Horde_Core_Factory_DbPear')->create('rw', 'vilma', 'storage');
498:         } catch (Exception $e) {
499:             throw new Vilma_Exception($e);
500:         }
501: 
502:         /* Use default table names if these are not set. */
503:         if (!isset($this->_params['tables']['domains'])) {
504:             $this->_params['tables']['domains'] = 'vilma_domains';
505:         }
506:         if (!isset($this->_params['tables']['users'])) {
507:             $this->_params['tables']['users'] = 'vilma_users';
508:         }
509:         if (!isset($this->_params['tables']['virtuals'])) {
510:             $this->_params['tables']['virtuals'] = 'vilma_virtuals';
511:         }
512:     }
513: }
514: