9 * @author Christian Weiske <cweiske@cweiske.de>
10 * @copyright 2014 Christian Weiske
11 * @license http://www.gnu.org/licenses/agpl.html GNU AGPL v3
12 * @link http://cweiske.de/grauphel.htm
14 namespace OCA\Grauphel\Lib;
17 * Flat file storage for notes
21 * @author Christian Weiske <cweiske@cweiske.de>
22 * @copyright 2014 Christian Weiske
23 * @license http://www.gnu.org/licenses/agpl.html GNU AGPL v3
24 * @version Release: @package_version@
25 * @link http://cweiske.de/grauphel.htm
30 * @var \OCP\IDBConnection
37 public function __construct($urlGen)
39 $this->urlGen = $urlGen;
40 $this->db = \OC::$server->getDatabaseConnection();
43 public function setUsername($username)
45 $this->username = $username;
49 * Create a new sync data object for fresh users.
50 * Used by loadSyncData()
52 * @return SyncData New synchronization statistics
54 protected function getNewSyncData()
56 $syncdata = new SyncData();
57 $syncdata->initNew($this->username);
61 public function getTags()
63 $result = $this->db->executeQuery(
64 'SELECT `note_tags` FROM `*PREFIX*grauphel_notes`'
65 . ' WHERE note_user = ?',
66 array($this->username)
70 while ($row = $result->fetch()) {
71 $tags = array_merge($tags, json_decode($row['note_tags']));
73 return array_unique($tags);
77 * Updates the given $note object with data from $noteUpdate.
78 * Sets the last-sync-revision to $syncRevision
80 * @param object $note Original note object
81 * @param object $noteUpdate Update note object from a PUT to the API
82 * @param integer $syncRevision Current sync revision number
86 public function update($note, $noteUpdate, $syncRevision)
88 static $updateFields = array(
91 'last-metadata-change-date',
93 'note-content-version',
101 foreach ($updateFields as $field) {
102 $changed[$field] = false;
103 if (isset($noteUpdate->$field)) {
104 if ($note->$field != $noteUpdate->$field) {
105 $note->$field = $noteUpdate->$field;
106 $changed[$field] = true;
111 if (!isset($noteUpdate->{'last-change-date'})
112 && ($changed['title'] || $changed['note-content'])
114 //no idea how to get the microseconds in there
115 $note->{'last-change-date'} = date('c');
118 if (!isset($noteUpdate->{'last-metadata-change-date'})) {
119 //no idea how to get the microseconds in there
120 $note->{'last-metadata-change-date'} = date('c');
123 if (!isset($note->{'create-date'})) {
124 //no idea how to get the microseconds in there
125 $note->{'create-date'} = date('c');
128 if (isset($noteUpdate->{'node-content'})
129 && $note->{'note-content-version'} == 0
131 $note->{'note-content-version'} = 0.3;
134 $note->{'last-sync-revision'} = $syncRevision;
138 * Loads synchronization data for the given user.
139 * Creates fresh sync data if there are none for the user.
141 * @return SyncData Synchronization statistics (revision, sync guid)
143 public function loadSyncData()
145 $row = $this->db->executeQuery(
146 'SELECT * FROM `*PREFIX*grauphel_syncdata`'
147 . ' WHERE `syncdata_user` = ?',
148 array($this->username)
151 if ($row === false) {
152 $syncdata = $this->getNewSyncData();
153 $this->saveSyncData($syncdata);
155 $syncdata = new SyncData();
156 $syncdata->latestSyncRevision = (int) $row['syncdata_latest_sync_revision'];
157 $syncdata->currentSyncGuid = $row['syncdata_current_sync_guid'];
164 * Save synchronization data for the given user.
166 * @param SyncData $syncdata Synchronization data object
170 public function saveSyncData(SyncData $syncdata)
172 $row = $this->db->executeQuery(
173 'SELECT * FROM `*PREFIX*grauphel_syncdata`'
174 . ' WHERE `syncdata_user` = ?',
175 array($this->username)
178 if ($row === false) {
180 $sql = 'INSERT INTO `*PREFIX*grauphel_syncdata`'
181 . '(`syncdata_user`, `syncdata_latest_sync_revision`, `syncdata_current_sync_guid`)'
182 . ' VALUES(?, ?, ?)';
185 $syncdata->latestSyncRevision,
186 $syncdata->currentSyncGuid
191 'syncdata_latest_sync_revision' => $syncdata->latestSyncRevision,
192 'syncdata_current_sync_guid' => $syncdata->currentSyncGuid,
194 $sql = 'UPDATE `*PREFIX*grauphel_syncdata` SET'
195 . ' `' . implode('` = ?, `', array_keys($data)) . '` = ?'
196 . ' WHERE `syncdata_user` = ?';
197 $params = array_values($data);
198 $params[] = $this->username;
200 $this->db->executeQuery($sql, $params);
204 * Delete synchronization data for the given user.
206 * @param SyncData $syncdata Synchronization data object
210 public function deleteSyncData()
212 $this->db->executeQuery(
213 'DELETE FROM `*PREFIX*grauphel_syncdata`'
214 . ' WHERE `syncdata_user` = ?',
215 array($this->username)
220 * Load a note from the storage.
222 * @param string $guid Note identifier
223 * @param boolean $createNew Create a new note if it does not exist
225 * @return object Note object, NULL if !$createNew and note does not exist
227 public function load($guid, $createNew = true)
229 $row = $this->db->executeQuery(
230 'SELECT * FROM `*PREFIX*grauphel_notes`'
231 . ' WHERE `note_user` = ? AND `note_guid` = ?',
232 array($this->username, $guid)
235 if ($row === false) {
239 return (object) array(
242 'create-date' => null,
243 'last-change-date' => null,
244 'last-metadata-change-date' => null,
247 'note-content' => null,
248 'note-content-version' => 0.3,
250 'open-on-startup' => false,
256 return $this->noteFromRow($row);
260 * Load a GUID of a note by the note title.
262 * The note title is stored html-escaped in the database because we
263 * get it that way from tomboy. Thus we have to escape the search
266 * @param string $title Note title.
268 * @return string GUID, NULL if note could not be found
270 public function loadGuidByTitle($title)
272 $row = $this->db->executeQuery(
273 'SELECT note_guid FROM `*PREFIX*grauphel_notes`'
274 . ' WHERE `note_user` = ? AND `note_title` = ?',
275 array($this->username, htmlspecialchars($title))
278 if ($row === false) {
282 return $row['note_guid'];
288 * @param array $keywords arrays of query strings within keys AND and NOT
290 * @return array Database rows with note_guid and note_title
292 public function search($keywordGroups)
294 if (!isset($keywordGroups['AND'])) {
295 $keywordGroups['AND'] = array();
297 if (!isset($keywordGroups['NOT'])) {
298 $keywordGroups['NOT'] = array();
301 $sqlTplAnd = ' AND (`note_title` ILIKE ? OR `note_tags` ILIKE ? OR `note_content` ILIKE ?)';
302 $sqlTplNot = ' AND NOT (`note_title` ILIKE ? OR `note_tags` ILIKE ? OR `note_content` ILIKE ?)';
306 foreach (array('AND', 'NOT') as $group) {
307 $keywords = $keywordGroups[$group];
308 foreach ($keywords as $keyword) {
309 $arData[] = '%' . $keyword . '%';//title
310 $arData[] = '%' . $keyword . '%';//tags
311 $arData[] = '%' . $keyword . '%';//content
315 $result = $this->db->executeQuery(
316 'SELECT `note_guid`, `note_title`'
317 . ' FROM `*PREFIX*grauphel_notes`'
318 . ' WHERE note_user = ?'
319 . str_repeat($sqlTplAnd, count($keywordGroups['AND']))
320 . str_repeat($sqlTplNot, count($keywordGroups['NOT'])),
325 while ($row = $result->fetch()) {
332 * Save a note into storage.
334 * @param object $note Note to save
338 public function save($note)
340 $row = $this->db->executeQuery(
341 'SELECT * FROM `*PREFIX*grauphel_notes`'
342 . ' WHERE `note_user` = ? AND `note_guid` = ?',
343 array($this->username, $note->guid)
346 $data = $this->rowFromNote($note);
347 if ($row === false) {
349 $data['note_user'] = $this->username;
350 $sql = 'INSERT INTO `*PREFIX*grauphel_notes`'
351 . ' (`' . implode('`, `', array_keys($data)) . '`)'
352 . ' VALUES(' . implode(', ', array_fill(0, count($data), '?')) . ')';
353 $params = array_values($data);
356 $sql = 'UPDATE `*PREFIX*grauphel_notes` SET '
357 . '`' . implode('` = ?, `', array_keys($data)) . '` = ?'
358 . ' WHERE `note_user` = ? AND `note_guid` = ?';
359 $params = array_values($data);
360 $params[] = $this->username;
361 $params[] = $note->guid;
363 $this->db->executeQuery($sql, $params);
367 * Delete a note from storage.
369 * @param object $guid ID of the note
373 public function delete($guid)
375 $this->db->executeQuery(
376 'DELETE FROM `*PREFIX*grauphel_notes`'
377 . ' WHERE `note_user` = ? AND `note_guid` = ?',
378 array($this->username, $guid)
383 * Delete all notes from storage.
387 public function deleteAll()
389 $this->db->executeQuery(
390 'DELETE FROM `*PREFIX*grauphel_notes`'
391 . ' WHERE `note_user` = ?',
392 array($this->username)
397 * Load notes for the given user in short form.
398 * Optionally only those changed after $since revision
400 * @param integer $since Revision number after which the notes changed
401 * @param string $rawtag Filter by tag. Special tags:
402 * - grauphel:special:all
403 * - grauphel:special:untagged
404 * @param boolean $includeDate Load the last modification date or not
406 * @return array Array of short note objects
408 public function loadNotesOverview(
409 $since = null, $rawtag = null, $includeDate = false
411 $sql = 'SELECT `note_guid`, `note_title`'
412 . ', `note_last_sync_revision`, `note_tags`'
413 . ', `note_last_change_date`'
414 . ' FROM `*PREFIX*grauphel_notes`'
415 . ' WHERE note_user = ?';
416 $sqlData = array($this->username);
418 if ($since !== null) {
420 $sql .= ' AND note_last_sync_revision > ?';
423 if ($rawtag == 'grauphel:special:all') {
425 } else if ($rawtag == 'grauphel:special:untagged') {
426 $jsRawtag = json_encode(array());
428 $jsRawtag = json_encode($rawtag);
430 if ($rawtag !== null) {
431 $sqlData[] = '%' . $jsRawtag . '%';
432 $sql .= ' AND note_tags LIKE ?';
435 $result = $this->db->executeQuery($sql, $sqlData);
437 while ($row = $result->fetch()) {
439 'guid' => $row['note_guid'],
441 'api-ref' => $this->urlGen->getAbsoluteURL(
442 $this->urlGen->linkToRoute(
445 'username' => $this->username,
446 'guid' => $row['note_guid']
450 'href' => $this->urlGen->getAbsoluteURL(
451 $this->urlGen->linkToRoute(
454 'guid' => $row['note_guid']
459 'title' => $row['note_title'],
462 $note['last-change-date'] = $row['note_last_change_date'];
471 * Load notes for the given user in full form.
472 * Optionally only those changed after $since revision
474 * @param integer $since Revision number after which the notes changed
476 * @return array Array of full note objects
478 public function loadNotesFull($since = null)
480 $result = $this->db->executeQuery(
481 'SELECT * FROM `*PREFIX*grauphel_notes`'
482 . ' WHERE note_user = ?',
483 array($this->username)
487 while ($row = $result->fetch()) {
488 if ($since !== null && $row['note_last_sync_revision'] <= $since) {
491 $notes[] = $this->noteFromRow($row);
497 protected function fixDate($date)
499 if (strlen($date) == 32) {
500 //Bug in grauphel 0.1.1; date fields in DB had only 32 instead of 33
501 // characters. The last digit of the time zone was missing
507 protected function noteFromRow($row)
509 return (object) array(
510 'guid' => $row['note_guid'],
512 'create-date' => $this->fixDate($row['note_create_date']),
513 'last-change-date' => $this->fixDate($row['note_last_change_date']),
514 'last-metadata-change-date' => $this->fixDate($row['note_last_metadata_change_date']),
516 'title' => $row['note_title'],
517 'note-content' => $row['note_content'],
518 'note-content-version' => $row['note_content_version'],
520 'open-on-startup' => (bool) $row['note_open_on_startup'],
521 'pinned' => (bool) $row['note_pinned'],
522 'tags' => json_decode($row['note_tags']),
524 'last-sync-revision' => (int) $row['note_last_sync_revision'],
528 protected function rowFromNote($note)
531 'note_guid' => $note->guid,
532 'note_title' => (string) $note->title,
534 'note_content' => (string) $note->{'note-content'},
535 'note_content_version' => (string) $note->{'note-content-version'},
537 'note_create_date' => $note->{'create-date'},
538 'note_last_change_date' => $note->{'last-change-date'},
539 'note_last_metadata_change_date' => $note->{'last-metadata-change-date'},
541 'note_open_on_startup' => (int) $note->{'open-on-startup'},
542 'note_pinned' => (int) $note->pinned,
543 'note_tags' => json_encode($note->tags),
545 'note_last_sync_revision' => $note->{'last-sync-revision'},