1: <?php
  2: /**
  3:  * @package Alarm
  4:  *
  5:  * Copyright 2007-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: 
 11: /**
 12:  * The Horde_Alarm_sql class is a Horde_Alarm storage implementation using the
 13:  * PEAR DB package.
 14:  *
 15:  * The table structure can be created by the
 16:  * horde/scripts/sql/horde_alarm.sql script.
 17:  *
 18:  * @author  Jan Schneider <jan@horde.org>
 19:  * @package Alarm
 20:  */
 21: class Horde_Alarm_Sql extends Horde_Alarm
 22: {
 23:     /**
 24:      * Handle for the current database connection.
 25:      *
 26:      * @var Horde_Db_Adapter
 27:      */
 28:     protected $_db;
 29: 
 30:     /**
 31:      * Constructor.
 32:      *
 33:      * @param array $params  Configuration parameters:
 34:      * <pre>
 35:      * 'db' - (Horde_Db_Adapter) [REQUIRED] The DB instance.
 36:      * 'table' - (string) The name of the tokens table in 'database'.
 37:      *           DEFAULT: 'horde_alarms'
 38:      * </pre>
 39:      *
 40:      * @throws Horde_Alarm_Exception
 41:      */
 42:     public function __construct(array $params = array())
 43:     {
 44:         if (!isset($params['db'])) {
 45:             throw new Horde_Alarm_Exception('Missing db parameter.');
 46:         }
 47:         $this->_db = $params['db'];
 48:         unset($params['db']);
 49: 
 50:         $params = array_merge(array(
 51:             'table' => 'horde_alarms'
 52:         ), $params);
 53: 
 54:         parent::__construct($params);
 55:     }
 56: 
 57:     /**
 58:      * Returns a list of alarms from the backend.
 59:      *
 60:      * @param string $user      Return alarms for this user, all users if
 61:      *                          null, or global alarms if empty.
 62:      * @param Horde_Date $time  The time when the alarms should be active.
 63:      *
 64:      * @return array  A list of alarm hashes.
 65:      * @throws Horde_Alarm_Exception
 66:      */
 67:     protected function _list($user, Horde_Date $time)
 68:     {
 69:         $query = sprintf('SELECT alarm_id, alarm_uid, alarm_start, alarm_end, alarm_methods, alarm_params, alarm_title, alarm_text, alarm_snooze, alarm_internal FROM %s WHERE alarm_dismissed = 0 AND ((alarm_snooze IS NULL AND alarm_start <= ?) OR alarm_snooze <= ?) AND (alarm_end IS NULL OR alarm_end >= ?)%s ORDER BY alarm_start, alarm_end',
 70:                          $this->_params['table'],
 71:                          is_null($user) ? '' : ' AND (alarm_uid IS NULL OR alarm_uid = ? OR alarm_uid = ?)');
 72:         $dt = $time->setTimezone('UTC')->format('Y-m-d\TH:i:s');
 73:         $values = array($dt, $dt, $dt);
 74:         if (!is_null($user)) {
 75:             $values[] = '';
 76:             $values[] = (string)$user;
 77:         }
 78: 
 79: 
 80:         try {
 81:             $result = $this->_db->selectAll($query, $values);
 82:         } catch (Horde_Db_Exception $e) {
 83:             throw new Horde_Alarm_Exception($e);
 84:         }
 85: 
 86:         $alarms = array();
 87:         foreach ($result as $val) {
 88:             $alarms[] = $this->_getHash($val);
 89:         }
 90: 
 91:         return $alarms;
 92:     }
 93: 
 94:     /**
 95:      * Returns a list of all global alarms from the backend.
 96:      *
 97:      * @return array  A list of alarm hashes.
 98:      * @throws Horde_Alarm_Exception
 99:      */
100:     protected function _global()
101:     {
102:         $query = sprintf('SELECT alarm_id, alarm_uid, alarm_start, alarm_end, alarm_methods, alarm_params, alarm_title, alarm_text, alarm_snooze, alarm_internal FROM %s WHERE alarm_uid IS NULL OR alarm_uid = \'\' ORDER BY alarm_start, alarm_end',
103:                          $this->_params['table']);
104: 
105:         try {
106:             $result = $this->_db->selectAll($query);
107:         } catch (Horde_Db_Exception $e) {
108:             throw new Horde_Alarm_Exception($e);
109:         }
110: 
111:         $alarms = array();
112:         foreach ($result as $val) {
113:             $alarms[] = $this->_getHash($val);
114:         }
115: 
116:         return $alarms;
117:     }
118: 
119:     /**
120:      */
121:     protected function _getHash(array $alarm)
122:     {
123:         $params = base64_decode($alarm['alarm_params']);
124:         if (!strlen($params) && strlen($alarm['alarm_params'])) {
125:             $params = $alarm['alarm_params'];
126:         }
127:         return array(
128:             'id' => $alarm['alarm_id'],
129:             'user' => $alarm['alarm_uid'],
130:             'start' => new Horde_Date($alarm['alarm_start'], 'UTC'),
131:             'end' => empty($alarm['alarm_end']) ? null : new Horde_Date($alarm['alarm_end'], 'UTC'),
132:             'methods' => @unserialize($alarm['alarm_methods']),
133:             'params' => @unserialize($params),
134:             'title' => $this->_fromDriver($alarm['alarm_title']),
135:             'text' => $this->_fromDriver($alarm['alarm_text']),
136:             'snooze' => empty($alarm['alarm_snooze']) ? null : new Horde_Date($alarm['alarm_snooze'], 'UTC'),
137:             'internal' => empty($alarm['alarm_internal']) ? null : @unserialize($alarm['alarm_internal'])
138:         );
139:     }
140: 
141:     /**
142:      * Returns an alarm hash from the backend.
143:      *
144:      * @param string $id    The alarm's unique id.
145:      * @param string $user  The alarm's user
146:      *
147:      * @return array  An alarm hash.
148:      * @throws Horde_Alarm_Exception
149:      */
150:     protected function _get($id, $user)
151:     {
152:         $query = sprintf('SELECT alarm_id, alarm_uid, alarm_start, alarm_end, alarm_methods, alarm_params, alarm_title, alarm_text, alarm_snooze, alarm_internal FROM %s WHERE alarm_id = ? AND %s',
153:                          $this->_params['table'],
154:                          !empty($user) ? 'alarm_uid = ?' : '(alarm_uid = ? OR alarm_uid IS NULL)');
155: 
156:         try {
157:             $alarm = $this->_db->selectOne($query, array($id, $user));
158:         } catch (Horde_Db_Exception $e) {
159:             throw new Horde_Alarm_Exception($e);
160:         }
161: 
162:         if (empty($alarm)) {
163:             throw new Horde_Alarm_Exception('Alarm not found');
164:         }
165: 
166:         return $this->_getHash($alarm);
167:     }
168: 
169:     /**
170:      * Adds an alarm hash to the backend.
171:      *
172:      * @param array $alarm  An alarm hash.
173:      *
174:      * @throws Horde_Alarm_Exception
175:      */
176:     protected function _add(array $alarm)
177:     {
178:         $query = sprintf('INSERT INTO %s (alarm_id, alarm_uid, alarm_start, alarm_end, alarm_methods, alarm_params, alarm_title, alarm_text, alarm_snooze) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)', $this->_params['table']);
179:         $values = array(
180:             $alarm['id'],
181:             isset($alarm['user']) ? $alarm['user'] : '',
182:             (string)$alarm['start']->setTimezone('UTC'),
183:             empty($alarm['end']) ? null : (string)$alarm['end']->setTimezone('UTC'),
184:             serialize($alarm['methods']),
185:             base64_encode(serialize($alarm['params'])),
186:             $this->_toDriver($alarm['title']),
187:             empty($alarm['text']) ? null : $this->_toDriver($alarm['text']),
188:             null
189:         );
190: 
191:         try {
192:             $this->_db->insert($query, $values);
193:         } catch (Horde_Db_Exception $e) {
194:             throw new Horde_Alarm_Exception($e);
195:         }
196:     }
197: 
198:     /**
199:      * Updates an alarm hash in the backend.
200:      *
201:      * @param array $alarm         An alarm hash.
202:      * @param boolean $keepsnooze  Whether to keep the snooze value unchanged.
203:      *
204:      * @throws Horde_Alarm_Exception
205:      */
206:     protected function _update(array $alarm, $keepsnooze = false)
207:     {
208:         $query = sprintf('UPDATE %s set alarm_start = ?, alarm_end = ?, alarm_methods = ?, alarm_params = ?, alarm_title = ?, alarm_text = ?%s WHERE alarm_id = ? AND %s',
209:                          $this->_params['table'],
210:                          $keepsnooze ? '' : ', alarm_snooze = NULL, alarm_dismissed = 0',
211:                          isset($alarm['user']) ? 'alarm_uid = ?' : '(alarm_uid = ? OR alarm_uid IS NULL)');
212:         $values = array((string)$alarm['start']->setTimezone('UTC'),
213:                         empty($alarm['end']) ? null : (string)$alarm['end']->setTimezone('UTC'),
214:                         serialize($alarm['methods']),
215:                         base64_encode(serialize($alarm['params'])),
216:                         $this->_toDriver($alarm['title']),
217:                         empty($alarm['text'])
218:                               ? null
219:                               : $this->_toDriver($alarm['text']),
220:                         $alarm['id'],
221:                         isset($alarm['user']) ? $alarm['user'] : '');
222: 
223:         try {
224:             $this->_db->update($query, $values);
225:         } catch (Horde_Db_Exception $e) {
226:             throw new Horde_Alarm_Exception($e);
227:         }
228:     }
229: 
230:     /**
231:      * Updates internal alarm properties, i.e. properties not determined by
232:      * the application setting the alarm.
233:      *
234:      * @param string $id       The alarm's unique id.
235:      * @param string $user     The alarm's user
236:      * @param array $internal  A hash with the internal data.
237:      *
238:      * @throws Horde_Alarm_Exception
239:      */
240:     public function internal($id, $user, array $internal)
241:     {
242:         $query = sprintf('UPDATE %s set alarm_internal = ? WHERE alarm_id = ? AND %s',
243:                          $this->_params['table'],
244:                          !empty($user) ? 'alarm_uid = ?' : '(alarm_uid = ? OR alarm_uid IS NULL)');
245:         $values = array(serialize($internal), $id, $user);
246: 
247:         try {
248:             $this->_db->update($query, $values);
249:         } catch (Horde_Db_Exception $e) {
250:             throw new Horde_Alarm_Exception($e);
251:         }
252:     }
253: 
254:     /**
255:      * Returns whether an alarm with the given id exists already.
256:      *
257:      * @param string $id    The alarm's unique id.
258:      * @param string $user  The alarm's user
259:      *
260:      * @return boolean  True if the specified alarm exists.
261:      * @throws Horde_Alarm_Exception
262:      */
263:     protected function _exists($id, $user)
264:     {
265:         $query = sprintf('SELECT 1 FROM %s WHERE alarm_id = ? AND %s',
266:                          $this->_params['table'],
267:                          !empty($user) ? 'alarm_uid = ?' : '(alarm_uid = ? OR alarm_uid IS NULL)');
268: 
269:         try {
270:             return ($this->_db->selectValue($query, array($id, $user)) == 1);
271:         } catch (Horde_Db_Exception $e) {
272:             throw new Horde_Alarm_Exception($e);
273:         }
274:     }
275: 
276:     /**
277:      * Delays (snoozes) an alarm for a certain period.
278:      *
279:      * @param string $id          The alarm's unique id.
280:      * @param string $user        The alarm's user
281:      * @param Horde_Date $snooze  The snooze time.
282:      *
283:      * @throws Horde_Alarm_Exception
284:      */
285:     protected function _snooze($id, $user, Horde_Date $snooze)
286:     {
287:         $query = sprintf('UPDATE %s set alarm_snooze = ? WHERE alarm_id = ? AND %s',
288:                          $this->_params['table'],
289:                          !empty($user) ? 'alarm_uid = ?' : '(alarm_uid = ? OR alarm_uid IS NULL)');
290:         $values = array((string)$snooze->setTimezone('UTC'), $id, $user);
291: 
292:         try {
293:             $this->_db->update($query, $values);
294:         } catch (Horde_Db_Exception $e) {
295:             throw new Horde_Alarm_Exception($e);
296:         }
297:     }
298: 
299:     /**
300:      * Returns whether an alarm is snoozed.
301:      *
302:      * @param string $id        The alarm's unique id.
303:      * @param string $user      The alarm's user
304:      * @param Horde_Date $time  The time when the alarm may be snoozed.
305:      *
306:      * @return boolean  True if the alarm is snoozed.
307:      * @throws Horde_Alarm_Exception
308:      */
309:     protected function _isSnoozed($id, $user, Horde_Date $time)
310:     {
311:         $query = sprintf('SELECT 1 FROM %s WHERE alarm_id = ? AND %s AND (alarm_dismissed = 1 OR (alarm_snooze IS NOT NULL AND alarm_snooze >= ?))',
312:                          $this->_params['table'],
313:                          !empty($user) ? 'alarm_uid = ?' : '(alarm_uid = ? OR alarm_uid IS NULL)');
314: 
315:         try {
316:             return $this->_db->selectValue($query, array($id, $user, (string)$time->setTimezone('UTC')));
317:         } catch (Horde_Db_Exception $e) {
318:             throw new Horde_Alarm_Exception($e);
319:         }
320:     }
321: 
322:     /**
323:      * Dismisses an alarm.
324:      *
325:      * @param string $id          The alarm's unique id.
326:      * @param string $user        The alarm's user
327:      *
328:      * @throws Horde_Alarm_Exception
329:      */
330:     protected function _dismiss($id, $user)
331:     {
332:         $query = sprintf('UPDATE %s set alarm_dismissed = 1 WHERE alarm_id = ? AND %s',
333:                          $this->_params['table'],
334:                          !empty($user) ? 'alarm_uid = ?' : '(alarm_uid = ? OR alarm_uid IS NULL)');
335:         $values = array($id, $user);
336: 
337:         try {
338:             $this->_db->update($query, $values);
339:         } catch (Horde_Db_Exception $e) {
340:             throw new Horde_Alarm_Exception($e);
341:         }
342:     }
343: 
344:     /**
345:      * Deletes an alarm from the backend.
346:      *
347:      * @param string $id    The alarm's unique id.
348:      * @param string $user  The alarm's user. All users' alarms if null.
349:      *
350:      * @throws Horde_Alarm_Exception
351:      */
352:     protected function _delete($id, $user = null)
353:     {
354:         $query = sprintf('DELETE FROM %s WHERE alarm_id = ?', $this->_params['table']);
355:         $values = array($id);
356:         if (!is_null($user)) {
357:             $query .= empty($user)
358:                 ? ' AND (alarm_uid IS NULL OR alarm_uid = ?)'
359:                 : ' AND alarm_uid = ?';
360:             $values[] = $user;
361:         }
362: 
363:         try {
364:             $this->_db->delete($query, $values);
365:         } catch (Horde_Db_Exception $e) {
366:             throw new Horde_Alarm_Exception($e);
367:         }
368:     }
369: 
370:     /**
371:      * Garbage collects old alarms in the backend.
372:      */
373:     protected function _gc()
374:     {
375:         $query = sprintf('DELETE FROM %s WHERE alarm_end IS NOT NULL AND alarm_end < ?', $this->_params['table']);
376:         $end = new Horde_Date(time());
377:         $this->_db->delete($query, array((string)$end->setTimezone('UTC')));
378:     }
379: 
380:     /**
381:      * Initialization tasks.
382:      *
383:      * @throws Horde_Alarm_Exception
384:      */
385:     public function initialize()
386:     {
387:         /* Handle any database specific initialization code to run. */
388:         switch ($this->_db->adapterName()) {
389:         case 'PDO_Oci':
390:             $query = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'";
391:             $db->execute($query);
392:             break;
393: 
394:         case 'PDO_PostrgreSQL':
395:             $query = "SET datestyle TO 'iso'";
396:             $db->execute($query);
397:             break;
398:         }
399:     }
400: 
401:     /**
402:      * Converts a value from the driver's charset.
403:      *
404:      * @param mixed $value  Value to convert.
405:      *
406:      * @return mixed  Converted value.
407:      */
408:     protected function _fromDriver($value)
409:     {
410:         return Horde_String::convertCharset($value, $this->_params['charset'], 'UTF-8');
411:     }
412: 
413:     /**
414:      * Converts a value to the driver's charset.
415:      *
416:      * @param mixed $value  Value to convert.
417:      *
418:      * @return mixed  Converted value.
419:      */
420:     protected function _toDriver($value)
421:     {
422:         return Horde_String::convertCharset($value, 'UTF-8', $this->_params['charset']);
423:     }
424: 
425: }
426: