1: <?php
  2: /**
  3:  * Mnemo storage implementation for Horde's Horde_Db database abstraction
  4:  * layer.
  5:  *
  6:  * Copyright 2001-2012 Horde LLC (http://www.horde.org/)
  7:  *
  8:  * See the enclosed file LICENSE for license information (ASL). If you
  9:  * did not receive this file, see http://www.horde.org/licenses/apache.
 10:  *
 11:  * @author  Chuck Hagenbuch <chuck@horde.org>
 12:  * @author  Michael J. Rubinsky <mrubinsk@horde.org>
 13:  * @package Mnemo
 14:  */
 15: class Mnemo_Driver_Sql extends Mnemo_Driver
 16: {
 17:     /**
 18:      * The database connection object.
 19:      *
 20:      * @var Horde_Db_Adapter
 21:      */
 22:     protected $_db;
 23: 
 24:     /**
 25:      * Share table name
 26:      *
 27:      * @var string
 28:      */
 29:     protected $_table;
 30: 
 31:     /**
 32:      * Charset
 33:      *
 34:      * @var string
 35:      */
 36:     protected $_charset;
 37: 
 38:     /**
 39:      * Construct a new SQL storage object.
 40:      *
 41:      * @param string $notepad  The name of the notepad to load/save notes from.
 42:      * @param array $params    The connection parameters
 43:      *
 44:      * @throws InvalidArguementException
 45:      */
 46:     public function __construct($notepad, $params = array())
 47:     {
 48:         if (empty($params['db']) || empty($params['table'])) {
 49:             throw new InvalidArgumentException('Missing required connection parameter(s).');
 50:         }
 51:         $this->_notepad = $notepad;
 52:         $this->_db = $params['db'];
 53:         $this->_table = $params['table'];
 54:         $this->_charset = $params['charset'];
 55:     }
 56: 
 57:     /**
 58:      * Retrieve one note from the database.
 59:      *
 60:      * @param string $noteId      The ID of the note to retrieve.
 61:      * @param string $passphrase  A passphrase with which this note was
 62:      *                            supposed to be encrypted.
 63:      *
 64:      * @return array  The array of note attributes.
 65:      * @throws Mnemo_Exception
 66:      * @throws Horde_Exception_NotFound
 67:      */
 68:     public function get($noteId, $passphrase = null)
 69:     {
 70:         $query = 'SELECT * FROM ' . $this->_table .
 71:                  ' WHERE memo_owner = ? AND memo_id = ?';
 72:         $values = array($this->_notepad, $noteId);
 73:         try {
 74:             $row = $this->_db->selectOne($query, $values);
 75:         } catch (Horde_Db_Exception $e) {
 76:             throw new Mnemo_Exception($e->getMessage());
 77:         }
 78: 
 79:         if (!count($row)) {
 80:             throw new Horde_Exception_NotFound(_("Not Found"));
 81:         }
 82: 
 83:         return $this->_buildNote($row, $passphrase);
 84:     }
 85: 
 86:     /**
 87:      * Retrieve one note from the database by UID.
 88:      *
 89:      * @param string $uid         The UID of the note to retrieve.
 90:      * @param string $passphrase  A passphrase with which this note was
 91:      *                            supposed to be encrypted.
 92:      *
 93:      * @return array  The array of note attributes.
 94:      * @throws Mnemo_Exception
 95:      * @throws Horde_Exception_NotFound
 96:      */
 97:     public function getByUID($uid, $passphrase = null)
 98:     {
 99:         $query = 'SELECT * FROM ' . $this->_table . ' WHERE memo_uid = ?';
100:         $values = array($uid);
101:         try {
102:             $row = $this->_db->selectOne($query, $values);
103:         } catch (Horde_Db_Exception $e) {
104:             throw new Mnemo_Exception($e->getMessage());
105:         }
106: 
107:         if (!count($row)) {
108:             throw new Horde_Exception_NotFound('Not found');
109:         }
110:         $this->_notepad = $row['memo_owner'];
111: 
112:         return $this->_buildNote($row, $passphrase);
113:     }
114: 
115:     /**
116:      * Add a note to the backend storage.
117:      *
118:      * @param string $desc        The first line of the note.
119:      * @param string $body        The whole note body.
120:      * @param string $category    The category of the note.
121:      * @param string $uid         A Unique Identifier for the note.
122:      * @param string $passphrase  The passphrase to encrypt the note with.
123:      *
124:      * @return string  The unique ID of the new note.
125:      * @throws Mnemo_Exception
126:      */
127:     public function add($desc, $body, $category = '', $uid = null, $passphrase = null)
128:     {
129:         $noteId = strval(new Horde_Support_Randomid());
130: 
131:         if ($passphrase) {
132:             $body = $this->_encrypt($body, $passphrase);
133:             Mnemo::storePassphrase($noteId, $passphrase);
134:         }
135: 
136:         if (is_null($uid)) {
137:             $uid = strval(new Horde_Support_Uuid());
138:         }
139: 
140:         $query = 'INSERT INTO ' . $this->_table .
141:                  ' (memo_owner, memo_id, memo_desc, memo_body, memo_category, memo_uid)' .
142:                  ' VALUES (?, ?, ?, ?, ?, ?)';
143:         $values = array($this->_notepad,
144:                         $noteId,
145:                         Horde_String::convertCharset($desc, 'UTF-8', $this->_charset),
146:                         Horde_String::convertCharset($body, 'UTF-8', $this->_charset),
147:                         Horde_String::convertCharset($category, 'UTF-8', $this->_charset),
148:                         Horde_String::convertCharset($uid, 'UTF-8', $this->_charset));
149: 
150:         try {
151:             $this->_db->insert($query, $values);
152:         } catch (Horde_Db_Exception $e) {
153:             throw new Mnemo_Exception($e->getMessage());
154:         }
155: 
156:         // Log the creation of this item in the history log.
157:         // @TODO: Inject the history driver
158:         $history = $GLOBALS['injector']->getInstance('Horde_History');
159:         $history->log('mnemo:' . $this->_notepad . ':' . $uid, array('action' => 'add'), true);
160: 
161:         return $noteId;
162:     }
163: 
164:     /**
165:      * Modify an existing note.
166:      *
167:      * @param string $noteId      The note to modify.
168:      * @param string $desc        The description (long) of the note.
169:      * @param string $body        The description (long) of the note.
170:      * @param string $category    The category of the note.
171:      * @param string $passphrase  The passphrase to encrypt the note with.
172:      *
173:      * @throws Mnemo_Exception
174:      */
175:     public function modify($noteId, $desc, $body, $category = null, $passphrase = null)
176:     {
177:         if ($passphrase) {
178:             $body = $this->_encrypt($body, $passphrase);
179:             Mnemo::storePassphrase($noteId, $passphrase);
180:         }
181: 
182:         $query  = 'UPDATE ' . $this->_table . ' SET memo_desc = ?, memo_body = ?';
183:         $values = array(Horde_String::convertCharset($desc, 'UTF-8', $this->_charset),
184:                         Horde_String::convertCharset($body, 'UTF-8', $this->_charset));
185: 
186:         // Don't change the category if it isn't provided.
187:         // @TODO: Category -> Tags
188:         if (!is_null($category)) {
189:             $query .= ', memo_category = ?';
190:             $values[] = Horde_String::convertCharset($category, 'UTF-8', $this->_charset);
191:         }
192:         $query .= ' WHERE memo_owner = ? AND memo_id = ?';
193:         array_push($values, $this->_notepad, $noteId);
194: 
195:         try {
196:             $this->_db->update($query, $values);
197:         } catch (Horde_Db_Exception $e) {
198:             throw new Mnemo_Exception($e->getMessage());
199:         }
200:         // Log the modification of this item in the history log.
201:         $note = $this->get($noteId);
202:         if (!empty($note['uid'])) {
203:             $history = $GLOBALS['injector']->getInstance('Horde_History');
204:             $history->log('mnemo:' . $this->_notepad . ':' . $note['uid'], array('action' => 'modify'), true);
205:         }
206:     }
207: 
208:     /**
209:      * Move a note to a new notepad.
210:      *
211:      * @param string $noteId      The note to move.
212:      * @param string $newNotepad  The new notepad.
213:      *
214:      * @throws Mnemo_Exception
215:      */
216:     public function move($noteId, $newNotepad)
217:     {
218:         // Get the note's details for use later.
219:         $note = $this->get($noteId);
220: 
221:         $query = 'UPDATE ' . $this->_table .
222:                  ' SET memo_owner = ?' .
223:                  ' WHERE memo_owner = ? AND memo_id = ?';
224:         $values = array($newNotepad, $this->_notepad, $noteId);
225:         try {
226:             $result = $this->_db->update($query, $values);
227:         } catch (Horde_Db_Exception $e) {
228:             throw new Mnemo_Exception($e->getMessage());
229:         }
230: 
231:         // Log the moving of this item in the history log.
232:         if (!empty($note['uid'])) {
233:             $history = $GLOBALS['injector']->getInstance('Horde_History');
234:             $history->log('mnemo:' . $this->_notepad . ':' . $note['uid'], array('action' => 'delete'), true);
235:             $history->log('mnemo:' . $newNotepad . ':' . $note['uid'], array('action' => 'add'), true);
236:         }
237:     }
238: 
239:     /**
240:      * Delete a note permanently
241:      *
242:      * @param string $noteId  The note to delete.
243:      *
244:      * @throws Mnemo_Exception
245:      */
246:     public function delete($noteId)
247:     {
248:         // Get the note's details for use later.
249:         $note = $this->get($noteId);
250: 
251:         $query = 'DELETE FROM ' . $this->_table .
252:                  ' WHERE memo_owner = ? AND memo_id = ?';
253:         $values = array($this->_notepad, $noteId);
254: 
255:         try {
256:             $this->_db->delete($query, $values);
257:         } catch (Horde_Db_Exception $e) {
258:             throw new Mnemo_Exception($e->getMessage());
259:         }
260: 
261:         // Log the deletion of this item in the history log.
262:         if (!empty($note['uid'])) {
263:             $history = $GLOBALS['injector']->getInstance('Horde_History');
264:             $history->log('mnemo:' . $this->_notepad . ':' . $note['uid'], array('action' => 'delete'), true);
265:         }
266:     }
267: 
268:     /**
269:      * Remove ALL notes belonging to the curernt user.
270:      *
271:      * @throws Mnemo_Exception
272:      */
273:     public function deleteAll()
274:     {
275:         $query = sprintf('DELETE FROM %s WHERE memo_owner = ?',
276:              $this->_table);
277:         $values = array($this->_notepad);
278: 
279:         try {
280:             $this->_db->delete($query, $values);
281:         } catch (Horde_Db_Exception $e) {
282:             throw new Mnemo_Exception($e->getMessage());
283:         }
284:     }
285: 
286:     /**
287:      * Retrieves all of the notes from $this->_notepad from the
288:      * database.
289:      *
290:      * @throws Mnemo_Exception
291:      */
292:     public function retrieve()
293:     {
294:         $query = sprintf('SELECT * FROM %s WHERE memo_owner = ?', $this->_table);
295:         $values = array($this->_notepad);
296: 
297:         try {
298:             $rows = $this->_db->selectAll($query, $values);
299:         } catch (Horde_Db_Exception $e) {
300:             throw new Mnemo_Exception($e->getMessage());
301:         }
302: 
303:         // Store the retrieved values in a fresh $memos list.
304:         $this->_memos = array();
305:         foreach ($rows as $row) {
306:             $this->_memos[$row['memo_id']] = $this->_buildNote($row);
307:         }
308:     }
309: 
310:     /**
311:      *
312:      * @param array $row           Hash of the note data, db keys.
313:      * @param string  $passphrase  The encryption passphrase.
314:      *
315:      * @return array a Task hash.
316:      * @throws Mnemo_Exception
317:      */
318:     protected function _buildNote($row, $passphrase = null)
319:     {
320:         // Make sure notes always have a UID.
321:         if (empty($row['memo_uid'])) {
322:             $row['memo_uid'] = strval(new Horde_Support_Guid());
323: 
324:             $query = 'UPDATE ' . $this->_table .
325:                 ' SET memo_uid = ?' .
326:                 ' WHERE memo_owner = ? AND memo_id = ?';
327:             $values = array($row['memo_uid'], $row['memo_owner'], $row['memo_id']);
328:             try {
329:                 $this->_db->update($query, $values);
330:             } catch (Horde_Db_Exception $e) {
331:                 throw new Mnemo_Exception($e->getMessage());
332:             }
333:         }
334: 
335:         // Decrypt note if requested.
336:         $encrypted = false;
337:         $body = Horde_String::convertCharset($row['memo_body'], $this->_charset, 'UTF-8');
338:         if (strpos($body, '-----BEGIN PGP MESSAGE-----') === 0) {
339:             $encrypted = true;
340:             if (empty($passphrase)) {
341:                 $passphrase = Mnemo::getPassphrase($row['memo_id']);
342:             }
343:             if (empty($passphrase)) {
344:                 $body = new Mnemo_Exception(_("This note has been encrypted."), Mnemo::ERR_NO_PASSPHRASE);
345:             } else {
346:                 try {
347:                     $body = $this->_decrypt($body, $passphrase);
348:                     $body = $body->message;
349:                 } catch (Mnemo_Exception $e) {
350:                     $body = $e;
351:                 }
352:                 Mnemo::storePassphrase($row['memo_id'], $passphrase);
353:             }
354:         }
355: 
356:         // Create a new task based on $row's values.
357:         return array('memolist_id' => $row['memo_owner'],
358:                      'memo_id' => $row['memo_id'],
359:                      'uid' => Horde_String::convertCharset($row['memo_uid'], $this->_charset, 'UTF-8'),
360:                      'desc' => Horde_String::convertCharset($row['memo_desc'], $this->_charset, 'UTF-8'),
361:                      'body' => $body,
362:                      'category' => Horde_String::convertCharset($row['memo_category'], $this->_charset, 'UTF-8'),
363:                      'encrypted' => $encrypted);
364:     }
365: }
366: