1: <?php
  2: /**
  3:  * Nag storage implementation for PHP's PEAR database abstraction layer.
  4:  *
  5:  * The table structure can be created by the scripts/sql/nag.sql script.
  6:  *
  7:  * See the enclosed file COPYING for license information (GPL). If you
  8:  * did not receive this file, see http://www.horde.org/licenses/gpl.
  9:  *
 10:  * @author  Jon Parise <jon@horde.org>
 11:  * @package Nag
 12:  */
 13: class Nag_Driver_Sql extends Nag_Driver
 14: {
 15:     /**
 16:      * Handle for the current database connection.
 17:      *
 18:      * @var Horde_Db_Adapter
 19:      */
 20:     protected $_db;
 21: 
 22:     /**
 23:      * Constructs a new SQL storage object.
 24:      *
 25:      * @param string $tasklist  The tasklist to load.
 26:      * @param array $params     A hash containing connection parameters.
 27:      */
 28:     public function __construct($tasklist, $params = array())
 29:     {
 30:         $this->_tasklist = $tasklist;
 31:         $this->_params = $params;
 32:         if (!isset($this->_params['table'])) {
 33:             $this->_params['table'] = 'nag_tasks';
 34:         }
 35: 
 36:         // @TODO: Use a bound factory for Nag_Driver and inject this from there.
 37:         $this->_db = $GLOBALS['injector']
 38:             ->getInstance('Horde_Core_Factory_Db')
 39:             ->create('nag', 'storage');
 40:     }
 41: 
 42:     /**
 43:      * Retrieves one task from the database.
 44:      *
 45:      * @param string $taskId  The id of the task to retrieve.
 46:      *
 47:      * @return Nag_Task  A Nag_Task object.
 48:      * @throws Horde_Exception_NotFound
 49:      * @throws Nag_Exception
 50:      */
 51:     public function get($taskId)
 52:     {
 53:         $query = sprintf('SELECT * FROM %s WHERE task_id = ?',
 54:                          $this->_params['table']);
 55:         $values = array($taskId);
 56:         try {
 57:             $row = $this->_db->selectOne($query, $values);
 58:         } catch (Horde_Db_Exception $e) {
 59:             throw new Nag_Exception($e);
 60:         }
 61:         if (!$row) {
 62:             throw new Horde_Exception_NotFound("Task not found");
 63:         }
 64: 
 65:         // Decode and return the task.
 66:         return new Nag_Task($this->_buildTask($row));
 67:     }
 68: 
 69:     /**
 70:      * Retrieves one task from the database by UID.
 71:      *
 72:      * @param string $uid  The UID of the task to retrieve.
 73:      *
 74:      * @return Nag_Task  A Nag_Task object.
 75:      * @throws Horde_Exception_NotFound
 76:      * @throws Nag_Exception
 77:      */
 78:     public function getByUID($uid)
 79:     {
 80:         $query = sprintf('SELECT * FROM %s WHERE task_uid = ?',
 81:                          $this->_params['table']);
 82:         $values = array($uid);
 83:         try {
 84:             $row = $this->_db->selectOne($query, $values);
 85:         } catch (Horde_Db_Exception $e) {
 86:             throw new Nag_Exception($e->getMessage());
 87:         }
 88:         if (!$row) {
 89:             throw new Horde_Exception_NotFound(sprintf(_("Task UID %s not found"), $uid));
 90:         }
 91:         $this->_tasklist = $row['task_owner'];
 92: 
 93:         // Decode and return the task.
 94:         return new Nag_Task($this->_buildTask($row));
 95:     }
 96: 
 97:     /**
 98:      * Adds a task to the backend storage.
 99:      *
100:      * @param string $name        The name (short) of the task.
101:      * @param string $desc        The description (long) of the task.
102:      * @param integer $start      The start date of the task.
103:      * @param integer $due        The due date of the task.
104:      * @param integer $priority   The priority of the task.
105:      * @param float $estimate     The estimated time to complete the task.
106:      * @param integer $completed  The completion state of the task.
107:      * @param string $category    The category of the task.
108:      * @param integer $alarm      The alarm associated with the task.
109:      * @param array $methods      The overridden alarm notification methods.
110:      * @param string $uid         A Unique Identifier for the task.
111:      * @param string $parent      The parent task id.
112:      * @param boolean $private    Whether the task is private.
113:      * @param string $owner       The owner of the event.
114:      * @param string $assignee    The assignee of the event.
115:      *
116:      * @return string  The Nag ID of the new task.
117:      * @throws Nag_Exception
118:      */
119:     protected function _add($name, $desc, $start = 0, $due = 0, $priority = 0,
120:                   $estimate = 0.0, $completed = 0, $category = '', $alarm = 0,
121:                   array $methods = null, $uid = null, $parent = '', $private = false,
122:                   $owner = null, $assignee = null)
123:     {
124:         $taskId = strval(new Horde_Support_Randomid());
125:         if (is_null($uid)) {
126:             $uid = strval(new Horde_Support_Guid());
127:         }
128: 
129:         $query = sprintf(
130:             'INSERT INTO %s (task_owner, task_creator, task_assignee, '
131:             . 'task_id, task_name, task_uid, task_desc, task_start, task_due, '
132:             . 'task_priority, task_estimate, task_completed, task_category, '
133:             . 'task_alarm, task_alarm_methods, task_private, task_parent) '
134:             . 'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
135:             $this->_params['table']);
136:         $values = array($this->_tasklist,
137:                         $owner,
138:                         $assignee,
139:                         $taskId,
140:                         Horde_String::convertCharset($name, 'UTF-8', $this->_params['charset']),
141:                         Horde_String::convertCharset($uid, 'UTF-8', $this->_params['charset']),
142:                         Horde_String::convertCharset($desc, 'UTF-8', $this->_params['charset']),
143:                         (int)$start,
144:                         (int)$due,
145:                         (int)$priority,
146:                         number_format($estimate, 2),
147:                         (int)$completed,
148:                         Horde_String::convertCharset($category, 'UTF-8', $this->_params['charset']),
149:                         (int)$alarm,
150:                         serialize(Horde_String::convertCharset($methods, 'UTF-8', $this->_params['charset'])),
151:                         (int)$private,
152:                         $parent);
153: 
154:         try {
155:             $this->_db->insert($query, $values);
156:         } catch (Horde_Db_Exception $e) {
157:             throw new Nag_Exception($e->getMessage());
158:         }
159: 
160:         return $taskId;
161:     }
162: 
163:     /**
164:      * Modifies an existing task.
165:      *
166:      * @param string $taskId           The task to modify.
167:      * @param string $name             The name (short) of the task.
168:      * @param string $desc             The description (long) of the task.
169:      * @param integer $start           The start date of the task.
170:      * @param integer $due             The due date of the task.
171:      * @param integer $priority        The priority of the task.
172:      * @param float $estimate          The estimated time to complete the task.
173:      * @param integer $completed       The completion state of the task.
174:      * @param string $category         The category of the task.
175:      * @param integer $alarm           The alarm associated with the task.
176:      * @param array $methods           The overridden alarm notification
177:      *                                 methods.
178:      * @param string $parent           The parent task id.
179:      * @param boolean $private         Whether the task is private.
180:      * @param string $owner            The owner of the event.
181:      * @param string $assignee         The assignee of the event.
182:      * @param integer $completed_date  The task's completion date.
183:      *
184:      * @throws Nag_Exception
185:      */
186:     protected function _modify($taskId, $name, $desc, $start = 0, $due = 0,
187:                      $priority = 0, $estimate = 0.0, $completed = 0,
188:                      $category = '', $alarm = 0, array $methods = null,
189:                      $parent = '', $private = false, $owner = null,
190:                      $assignee = null, $completed_date = null)
191:     {
192:         $query = sprintf('UPDATE %s SET' .
193:                          ' task_creator = ?, ' .
194:                          ' task_assignee = ?, ' .
195:                          ' task_name = ?, ' .
196:                          ' task_desc = ?, ' .
197:                          ' task_start = ?, ' .
198:                          ' task_due = ?, ' .
199:                          ' task_priority = ?, ' .
200:                          ' task_estimate = ?, ' .
201:                          ' task_completed = ?, ' .
202:                          ' task_completed_date = ?, ' .
203:                          ' task_category = ?, ' .
204:                          ' task_alarm = ?, ' .
205:                          ' task_alarm_methods = ?, ' .
206:                          ' task_parent = ?, ' .
207:                          ' task_private = ? ' .
208:                          'WHERE task_owner = ? AND task_id = ?',
209:                          $this->_params['table']);
210:         $values = array($owner,
211:                         $assignee,
212:                         Horde_String::convertCharset($name, 'UTF-8', $this->_params['charset']),
213:                         Horde_String::convertCharset($desc, 'UTF-8', $this->_params['charset']),
214:                         (int)$start,
215:                         (int)$due,
216:                         (int)$priority,
217:                         number_format($estimate, 2),
218:                         (int)$completed,
219:                         (int)$completed_date,
220:                         Horde_String::convertCharset($category, 'UTF-8', $this->_params['charset']),
221:                         (int)$alarm,
222:                         serialize(Horde_String::convertCharset($methods, 'UTF-8', $this->_params['charset'])),
223:                         $parent,
224:                         (int)$private,
225:                         $this->_tasklist,
226:                         $taskId);
227: 
228:         try {
229:             $this->_db->update($query, $values);
230:         } catch (Horde_Db_Exception $e) {
231:             throw new Nag_Exception($e->getMessage());
232:         }
233: 
234:         return true;
235:     }
236: 
237:     /**
238:      * Moves a task to a different tasklist.
239:      *
240:      * @param string $taskId       The task to move.
241:      * @param string $newTasklist  The new tasklist.
242:      *
243:      * @throws Nag_Exception
244:      */
245:     protected function _move($taskId, $newTasklist)
246:     {
247:         $query = sprintf('UPDATE %s SET task_owner = ? WHERE task_owner = ? AND task_id = ?',
248:                          $this->_params['table']);
249:         $values = array($newTasklist, $this->_tasklist, $taskId);
250: 
251:         try {
252:             $this->_db->update($query, $values);
253:         } catch (Horde_Db_Exception $e) {
254:             throw new Nag_Exception($e->getMessage());
255:         }
256:     }
257: 
258:     /**
259:      * Deletes a task from the backend.
260:      *
261:      * @param string $taskId  The task to delete.
262:      *
263:      * @throws Nag_Exception
264:      */
265:     protected function _delete($taskId)
266:     {
267:         /* Get the task's details for use later. */
268:         $task = $this->get($taskId);
269: 
270:         $query = sprintf('DELETE FROM %s WHERE task_owner = ? AND task_id = ?',
271:                          $this->_params['table']);
272:         $values = array($this->_tasklist, $taskId);
273: 
274:         try {
275:             $this->_db->delete($query, $values);
276:         } catch (Horde_Db_Exception $e) {
277:             throw Nag_Exception($e->getMessage());
278:         }
279:     }
280: 
281:     /**
282:      * Deletes all tasks from the backend.
283:      *
284:      * @throws Nag_Exception
285:      */
286:     public function deleteAll()
287:     {
288:         $query = sprintf('DELETE FROM %s WHERE task_owner = ?',
289:                          $this->_params['table']);
290:         $values = array($this->_tasklist);
291: 
292:         /* Attempt the delete query. */
293:         try {
294:             $this->_db->delete($query, $values);
295:         } catch (Horde_Db_Exception $e) {
296:             throw new Nag_Exception($e->getMessage());
297:         }
298:     }
299: 
300:     /**
301:      * Retrieves tasks from the database.
302:      *
303:      * @param integer $completed  Which tasks to retrieve (1 = all tasks,
304:      *                            0 = incomplete tasks, 2 = complete tasks,
305:      *                            3 = future tasks, 4 = future and incomplete
306:      *                            tasks).
307:      * @throws Nag_Exception
308:      */
309:     function retrieve($completed = Nag::VIEW_ALL)
310:     {
311:         /* Build the SQL query. */
312:         $query = sprintf('SELECT * FROM %s WHERE task_owner = ?',
313:                          $this->_params['table']);
314:         $values = array($this->_tasklist);
315:         switch ($completed) {
316:         case Nag::VIEW_INCOMPLETE:
317:             $query .= ' AND task_completed = 0 AND (task_start IS NULL OR task_start = 0 OR task_start < ?)';
318:             $values[] = time();
319:             break;
320: 
321:         case Nag::VIEW_COMPLETE:
322:             $query .= ' AND task_completed = 1';
323:             break;
324: 
325:         case Nag::VIEW_FUTURE:
326:             $query .= ' AND task_completed = 0 AND task_start > ?';
327:             $values[] = time();
328:             break;
329: 
330:         case Nag::VIEW_FUTURE_INCOMPLETE:
331:             $query .= ' AND task_completed = 0';
332:             break;
333:         }
334: 
335:         try {
336:             $result = $this->_db->selectAll($query, $values);
337:         } catch (Horde_Db_Exception $e) {
338:             throw new Nag_Exception($e->getMessage());
339:         }
340: 
341:         /* Store the retrieved values in a fresh task list. */
342:         $this->tasks = new Nag_Task();
343:         $dict = array();
344: 
345:         foreach ($result as $row) {
346:             $task = new Nag_Task($this->_buildTask($row));
347: 
348:             /* Add task directly if it is a root task, otherwise store it in
349:              * the dictionary. */
350:             if (empty($row['task_parent'])) {
351:                 $this->tasks->add($task);
352:             } else {
353:                 $dict[$row['task_id']] = $task;
354:             }
355:         }
356: 
357:         /* Build a tree from the subtasks. */
358:         foreach (array_keys($dict) as $key) {
359:             $task = $this->tasks->get($dict[$key]->parent_id);
360:             if ($task) {
361:                 $task->add($dict[$key]);
362:             } elseif (isset($dict[$dict[$key]->parent_id])) {
363:                 $dict[$dict[$key]->parent_id]->add($dict[$key]);
364:             } else {
365:                 $this->tasks->add($dict[$key]);
366:             }
367:         }
368:     }
369: 
370:     /**
371:      * Retrieves sub-tasks from the database.
372:      *
373:      * @param string $parentId  The parent id for the sub-tasks to retrieve.
374:      *
375:      * @return array  List of sub-tasks.
376:      * @throws Nag_Exception
377:      */
378:     public function getChildren($parentId)
379:     {
380:         // Build the SQL query.
381:         $query = sprintf('SELECT * FROM %s WHERE task_owner = ? AND task_parent = ?',
382:                          $this->_params['table']);
383:         $values = array($this->_tasklist, $parentId);
384: 
385:         try {
386:             $result = $this->_db->selectAll($query, $values);
387:         } catch (Horde_Db_Exception $e) {
388:             throw new Nag_Exception($e->getMessage());
389:         }
390: 
391:         // Store the retrieved values in a fresh task list.
392:         $tasks = array();
393:         foreach ($result as $row) {
394:             $task = new Nag_Task($this->_buildTask($row));
395:             $children = $this->getChildren($task->id);
396:             $task->mergeChildren($children);
397:             $tasks[] = $task;
398:         }
399: 
400:         return $tasks;
401:     }
402: 
403:     /**
404:      * Lists all alarms near $date.
405:      *
406:      * @param integer $date  The unix epoch time to check for alarms.
407:      *
408:      * @return array  An array of tasks that have alarms that match.
409:      * @throws Nag_Exception
410:      */
411:     public function listAlarms($date)
412:     {
413:         $q = 'SELECT * FROM ' . $this->_params['table'] .
414:             ' WHERE task_owner = ?' .
415:             ' AND task_alarm > 0' .
416:             ' AND (task_due - (task_alarm * 60) <= ?)' .
417:             ' AND task_completed = 0';
418:         $values = array($this->_tasklist, $date);
419: 
420:         try {
421:             $result = $this->_db->selectAll($q, $values);
422:         } catch (Horde_Db_Exception $e) {
423:             throw new Nag_Exception($e->getMessage());
424:         }
425: 
426:         $tasks = array();
427:         foreach ($result as $row) {
428:             $tasks[$row['task_id']] = new Nag_Task($this->_buildTask($row));
429:         }
430: 
431:         return $tasks;
432:     }
433: 
434:     /**
435:      */
436:     protected function _buildTask($row)
437:     {
438:         // Make sure tasks always have a UID.
439:         if (empty($row['task_uid'])) {
440:             $row['task_uid'] = strval(new Horde_Support_Guid());
441: 
442:             $query = 'UPDATE ' . $this->_params['table'] .
443:                 ' SET task_uid = ?' .
444:                 ' WHERE task_owner = ? AND task_id = ?';
445:             $values = array($row['task_uid'], $row['task_owner'], $row['task_id']);
446: 
447:             try {
448:                 $this->_db->update($query, $values);
449:             } catch (Horde_Db_Exception $e) {}
450:         }
451: 
452:         /* Create a new task based on $row's values. */
453:         return array(
454:             'tasklist_id' => $row['task_owner'],
455:             'task_id' => $row['task_id'],
456:             'uid' => Horde_String::convertCharset($row['task_uid'], $this->_params['charset'], 'UTF-8'),
457:             'parent' => $row['task_parent'],
458:             'owner' => $row['task_creator'],
459:             'assignee' => $row['task_assignee'],
460:             'name' => Horde_String::convertCharset($row['task_name'], $this->_params['charset'], 'UTF-8'),
461:             'desc' => Horde_String::convertCharset($row['task_desc'], $this->_params['charset'], 'UTF-8'),
462:             'category' => Horde_String::convertCharset($row['task_category'], $this->_params['charset'], 'UTF-8'),
463:             'start' => $row['task_start'],
464:             'due' => $row['task_due'],
465:             'priority' => $row['task_priority'],
466:             'estimate' => (float)$row['task_estimate'],
467:             'completed' => $row['task_completed'],
468:             'completed_date' => isset($row['task_completed_date']) ? $row['task_completed_date'] : null,
469:             'alarm' => $row['task_alarm'],
470:             'methods' => Horde_String::convertCharset(@unserialize($row['task_alarm_methods']), $this->_params['charset'], 'UTF-8'),
471:             'private' => $row['task_private']
472:         );
473:     }
474: 
475: }
476: