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
32 public function __construct($urlGen)
34 $this->urlGen = $urlGen;
37 public function setUsername($username)
39 $this->username = $username;
43 * Create a new sync data object for fresh users.
44 * Used by loadSyncData()
46 * @return SyncData New synchronization statistics
48 protected function getNewSyncData()
50 $syncdata = new SyncData();
51 $syncdata->initNew($this->username);
55 public function getTags()
57 $result = \OC_DB::executeAudited(
58 'SELECT `note_tags` FROM `*PREFIX*grauphel_notes`'
59 . ' WHERE note_user = ?',
60 array($this->username)
64 while ($row = $result->fetchRow()) {
65 $tags = array_merge($tags, json_decode($row['note_tags']));
67 return array_unique($tags);
71 * Updates the given $note object with data from $noteUpdate.
72 * Sets the last-sync-revision to $syncRevision
74 * @param object $note Original note object
75 * @param object $noteUpdate Update note object from a PUT to the API
76 * @param integer $syncRevision Current sync revision number
80 public function update($note, $noteUpdate, $syncRevision)
82 static $updateFields = array(
85 'last-metadata-change-date',
87 'note-content-version',
95 foreach ($updateFields as $field) {
96 $changed[$field] = false;
97 if (isset($noteUpdate->$field)) {
98 if ($note->$field != $noteUpdate->$field) {
99 $note->$field = $noteUpdate->$field;
100 $changed[$field] = true;
105 if (!isset($noteUpdate->{'last-change-date'})
106 && ($changed['title'] || $changed['note-content'])
108 //no idea how to get the microseconds in there
109 $note->{'last-change-date'} = date('c');
112 if (!isset($noteUpdate->{'last-metadata-change-date'})) {
113 //no idea how to get the microseconds in there
114 $note->{'last-metadata-change-date'} = date('c');
117 if (isset($noteUpdate->{'node-content'})
118 && $note->{'note-content-version'} == 0
120 $note->{'note-content-version'} = 0.3;
123 $note->{'last-sync-revision'} = $syncRevision;
127 * Loads synchronization data for the given user.
128 * Creates fresh sync data if there are none for the user.
130 * @return SyncData Synchronization statistics (revision, sync guid)
132 public function loadSyncData()
134 $row = \OC_DB::executeAudited(
135 'SELECT * FROM `*PREFIX*grauphel_syncdata`'
136 . ' WHERE `syncdata_user` = ?',
137 array($this->username)
140 if ($row === false) {
141 $syncdata = $this->getNewSyncData();
142 $this->saveSyncData($syncdata);
144 $syncdata = new SyncData();
145 $syncdata->latestSyncRevision = (int) $row['syncdata_latest_sync_revision'];
146 $syncdata->currentSyncGuid = $row['syncdata_current_sync_guid'];
153 * Save synchronization data for the given user.
155 * @param SyncData $syncdata Synchronization data object
159 public function saveSyncData(SyncData $syncdata)
161 $row = \OC_DB::executeAudited(
162 'SELECT * FROM `*PREFIX*grauphel_syncdata`'
163 . ' WHERE `syncdata_user` = ?',
164 array($this->username)
167 if ($row === false) {
169 $sql = 'INSERT INTO `*PREFIX*grauphel_syncdata`'
170 . '(`syncdata_user`, `syncdata_latest_sync_revision`, `syncdata_current_sync_guid`)'
171 . ' VALUES(?, ?, ?)';
174 $syncdata->latestSyncRevision,
175 $syncdata->currentSyncGuid
180 'syncdata_latest_sync_revision' => $syncdata->latestSyncRevision,
181 'syncdata_current_sync_guid' => $syncdata->currentSyncGuid,
183 $sql = 'UPDATE `*PREFIX*grauphel_syncdata` SET'
184 . ' `' . implode('` = ?, `', array_keys($data)) . '` = ?'
185 . ' WHERE `syncdata_user` = ?';
186 $params = array_values($data);
187 $params[] = $this->username;
189 \OC_DB::executeAudited($sql, $params);
193 * Delete synchronization data for the given user.
195 * @param SyncData $syncdata Synchronization data object
199 public function deleteSyncData()
201 \OC_DB::executeAudited(
202 'DELETE FROM `*PREFIX*grauphel_syncdata`'
203 . ' WHERE `syncdata_user` = ?',
204 array($this->username)
209 * Load a note from the storage.
211 * @param string $guid Note identifier
212 * @param boolean $createNew Create a new note if it does not exist
214 * @return object Note object, NULL if !$createNew and note does not exist
216 public function load($guid, $createNew = true)
218 $row = \OC_DB::executeAudited(
219 'SELECT * FROM `*PREFIX*grauphel_notes`'
220 . ' WHERE `note_user` = ? AND `note_guid` = ?',
221 array($this->username, $guid)
224 if ($row === false) {
228 return (object) array(
231 'create-date' => null,
232 'last-change-date' => null,
233 'last-metadata-change-date' => null,
236 'note-content' => null,
237 'note-content-version' => 0.3,
239 'open-on-startup' => false,
245 return $this->noteFromRow($row);
249 * Load a GUID of a note by the note title.
251 * The note title is stored html-escaped in the database because we
252 * get it that way from tomboy. Thus we have to escape the search
255 * @param string $title Note title.
257 * @return string GUID, NULL if note could not be found
259 public function loadGuidByTitle($title)
261 $row = \OC_DB::executeAudited(
262 'SELECT note_guid FROM `*PREFIX*grauphel_notes`'
263 . ' WHERE `note_user` = ? AND `note_title` = ?',
264 array($this->username, htmlspecialchars($title))
267 if ($row === false) {
271 return $row['note_guid'];
277 * @param array $keywords arrays of query strings within keys AND and NOT
279 * @return array Database rows with note_guid and note_title
281 public function search($keywordGroups)
283 if (!isset($keywordGroups['AND'])) {
284 $keywordGroups['AND'] = array();
286 if (!isset($keywordGroups['NOT'])) {
287 $keywordGroups['NOT'] = array();
290 $sqlTplAnd = ' AND (note_title LIKE ? OR note_tags LIKE ? OR note_content LIKE ?)';
291 $sqlTplNot = ' AND NOT (note_title LIKE ? OR note_tags LIKE ? OR note_content LIKE ?)';
295 foreach (array('AND', 'NOT') as $group) {
296 $keywords = $keywordGroups[$group];
297 foreach ($keywords as $keyword) {
298 $arData[] = '%' . $keyword . '%';//title
299 $arData[] = '%' . $keyword . '%';//tags
300 $arData[] = '%' . $keyword . '%';//content
304 $result = \OC_DB::executeAudited(
305 'SELECT `note_guid`, `note_title`'
306 . ' FROM `*PREFIX*grauphel_notes`'
307 . ' WHERE note_user = ?'
308 . str_repeat($sqlTplAnd, count($keywordGroups['AND']))
309 . str_repeat($sqlTplNot, count($keywordGroups['NOT'])),
314 while ($row = $result->fetchRow()) {
321 * Save a note into storage.
323 * @param object $note Note to save
327 public function save($note)
329 $row = \OC_DB::executeAudited(
330 'SELECT * FROM `*PREFIX*grauphel_notes`'
331 . ' WHERE `note_user` = ? AND `note_guid` = ?',
332 array($this->username, $note->guid)
335 $data = $this->rowFromNote($note);
336 if ($row === false) {
338 $data['note_user'] = $this->username;
339 $sql = 'INSERT INTO `*PREFIX*grauphel_notes`'
340 . ' (`' . implode('`, `', array_keys($data)) . '`)'
341 . ' VALUES(' . implode(', ', array_fill(0, count($data), '?')) . ')';
342 $params = array_values($data);
345 $sql = 'UPDATE `*PREFIX*grauphel_notes` SET '
346 . '`' . implode('` = ?, `', array_keys($data)) . '` = ?'
347 . ' WHERE `note_user` = ? AND `note_guid` = ?';
348 $params = array_values($data);
349 $params[] = $this->username;
350 $params[] = $note->guid;
352 \OC_DB::executeAudited($sql, $params);
356 * Delete a note from storage.
358 * @param object $guid ID of the note
362 public function delete($guid)
364 \OC_DB::executeAudited(
365 'DELETE FROM `*PREFIX*grauphel_notes`'
366 . ' WHERE `note_user` = ? AND `note_guid` = ?',
367 array($this->username, $guid)
372 * Delete all notes from storage.
376 public function deleteAll()
378 \OC_DB::executeAudited(
379 'DELETE FROM `*PREFIX*grauphel_notes`'
380 . ' WHERE `note_user` = ?',
381 array($this->username)
386 * Load notes for the given user in short form.
387 * Optionally only those changed after $since revision
389 * @param integer $since Revision number after which the notes changed
390 * @param string $rawtag Filter by tag. Special tags:
391 * - grauphel:special:all
392 * - grauphel:special:untagged
393 * @param boolean $includeDate Load the last modification date or not
395 * @return array Array of short note objects
397 public function loadNotesOverview(
398 $since = null, $rawtag = null, $includeDate = false
400 $result = \OC_DB::executeAudited(
401 'SELECT `note_guid`, `note_title`, `note_last_sync_revision`, `note_tags`'
402 . ', `note_last_change_date`'
403 . ' FROM `*PREFIX*grauphel_notes`'
404 . ' WHERE note_user = ?',
405 array($this->username)
409 if ($rawtag == 'grauphel:special:all') {
411 } else if ($rawtag == 'grauphel:special:untagged') {
412 $jsRawtag = json_encode(array());
414 $jsRawtag = json_encode($rawtag);
416 while ($row = $result->fetchRow()) {
417 if ($since !== null && $row['note_last_sync_revision'] <= $since) {
420 if ($rawtag !== null && strpos($row['note_tags'], $jsRawtag) === false) {
424 'guid' => $row['note_guid'],
426 'api-ref' => $this->urlGen->getAbsoluteURL(
427 $this->urlGen->linkToRoute(
430 'username' => $this->username,
431 'guid' => $row['note_guid']
435 'href' => $this->urlGen->getAbsoluteURL(
436 $this->urlGen->linkToRoute(
439 'guid' => $row['note_guid']
444 'title' => $row['note_title'],
447 $note['last-change-date'] = $row['note_last_change_date'];
456 * Load notes for the given user in full form.
457 * Optionally only those changed after $since revision
459 * @param integer $since Revision number after which the notes changed
461 * @return array Array of full note objects
463 public function loadNotesFull($since = null)
465 $result = \OC_DB::executeAudited(
466 'SELECT * FROM `*PREFIX*grauphel_notes`'
467 . ' WHERE note_user = ?',
468 array($this->username)
472 while ($row = $result->fetchRow()) {
473 if ($since !== null && $row['note_last_sync_revision'] <= $since) {
476 $notes[] = $this->noteFromRow($row);
482 protected function fixDate($date)
484 if (strlen($date) == 32) {
485 //Bug in grauphel 0.1.1; date fields in DB had only 32 instead of 33
486 // characters. The last digit of the time zone was missing
492 protected function noteFromRow($row)
494 return (object) array(
495 'guid' => $row['note_guid'],
497 'create-date' => $this->fixDate($row['note_create_date']),
498 'last-change-date' => $this->fixDate($row['note_last_change_date']),
499 'last-metadata-change-date' => $this->fixDate($row['note_last_metadata_change_date']),
501 'title' => $row['note_title'],
502 'note-content' => $row['note_content'],
503 'note-content-version' => $row['note_content_version'],
505 'open-on-startup' => (bool) $row['note_open_on_startup'],
506 'pinned' => (bool) $row['note_pinned'],
507 'tags' => json_decode($row['note_tags']),
509 'last-sync-revision' => (int) $row['note_last_sync_revision'],
513 protected function rowFromNote($note)
516 'note_guid' => $note->guid,
517 'note_title' => (string) $note->title,
519 'note_content' => (string) $note->{'note-content'},
520 'note_content_version' => (string) $note->{'note-content-version'},
522 'note_create_date' => $note->{'create-date'},
523 'note_last_change_date' => $note->{'last-change-date'},
524 'note_last_metadata_change_date' => $note->{'last-metadata-change-date'},
526 'note_open_on_startup' => (int) $note->{'open-on-startup'},
527 'note_pinned' => (int) $note->pinned,
528 'note_tags' => json_encode($note->tags),
530 'note_last_sync_revision' => $note->{'last-sync-revision'},