X-Git-Url: https://git.cweiske.de/grauphel.git/blobdiff_plain/21e7415fe5a418ef6dd7f29d3749b11cc9adaa7d..b816eb1d59d1f16a8f85d31d6b5ea2826648a332:/lib/notestorage.php diff --git a/lib/notestorage.php b/lib/notestorage.php index 7449417..27bb702 100644 --- a/lib/notestorage.php +++ b/lib/notestorage.php @@ -189,6 +189,22 @@ class NoteStorage \OC_DB::executeAudited($sql, $params); } + /** + * Delete synchronization data for the given user. + * + * @param SyncData $syncdata Synchronization data object + * + * @return void + */ + public function deleteSyncData() + { + \OC_DB::executeAudited( + 'DELETE FROM `*PREFIX*grauphel_syncdata`' + . ' WHERE `syncdata_user` = ?', + array($this->username) + ); + } + /** * Load a note from the storage. * @@ -225,10 +241,82 @@ class NoteStorage 'tags' => array(), ); } - + return $this->noteFromRow($row); } + /** + * Load a GUID of a note by the note title. + * + * The note title is stored html-escaped in the database because we + * get it that way from tomboy. Thus we have to escape the search + * input, too. + * + * @param string $title Note title. + * + * @return string GUID, NULL if note could not be found + */ + public function loadGuidByTitle($title) + { + $row = \OC_DB::executeAudited( + 'SELECT note_guid FROM `*PREFIX*grauphel_notes`' + . ' WHERE `note_user` = ? AND `note_title` = ?', + array($this->username, htmlspecialchars($title)) + )->fetchRow(); + + if ($row === false) { + return null; + } + + return $row['note_guid']; + } + + /** + * Search for a note + * + * @param array $keywords arrays of query strings within keys AND and NOT + * + * @return array Database rows with note_guid and note_title + */ + public function search($keywordGroups) + { + if (!isset($keywordGroups['AND'])) { + $keywordGroups['AND'] = array(); + } + if (!isset($keywordGroups['NOT'])) { + $keywordGroups['NOT'] = array(); + } + + $sqlTplAnd = ' AND (note_title ILIKE ? OR note_tags ILIKE ? OR note_content ILIKE ?)'; + $sqlTplNot = ' AND NOT (note_title ILIKE ? OR note_tags ILIKE ? OR note_content ILIKE ?)'; + $arData = array( + $this->username + ); + foreach (array('AND', 'NOT') as $group) { + $keywords = $keywordGroups[$group]; + foreach ($keywords as $keyword) { + $arData[] = '%' . $keyword . '%';//title + $arData[] = '%' . $keyword . '%';//tags + $arData[] = '%' . $keyword . '%';//content + } + } + + $result = \OC_DB::executeAudited( + 'SELECT `note_guid`, `note_title`' + . ' FROM `*PREFIX*grauphel_notes`' + . ' WHERE note_user = ?' + . str_repeat($sqlTplAnd, count($keywordGroups['AND'])) + . str_repeat($sqlTplNot, count($keywordGroups['NOT'])), + $arData + ); + + $notes = array(); + while ($row = $result->fetchRow()) { + $notes[] = $row; + } + return $notes; + } + /** * Save a note into storage. * @@ -280,27 +368,47 @@ class NoteStorage ); } + /** + * Delete all notes from storage. + * + * @return void + */ + public function deleteAll() + { + \OC_DB::executeAudited( + 'DELETE FROM `*PREFIX*grauphel_notes`' + . ' WHERE `note_user` = ?', + array($this->username) + ); + } + /** * Load notes for the given user in short form. * Optionally only those changed after $since revision * - * @param integer $since Revision number after which the notes changed - * @param string $rawtag Filter by tag. Special tags: - * - grauphel:special:all - * - grauphel:special:untagged + * @param integer $since Revision number after which the notes changed + * @param string $rawtag Filter by tag. Special tags: + * - grauphel:special:all + * - grauphel:special:untagged + * @param boolean $includeDate Load the last modification date or not * * @return array Array of short note objects */ - public function loadNotesOverview($since = null, $rawtag = null) - { - $result = \OC_DB::executeAudited( - 'SELECT `note_guid`, `note_title`, `note_last_sync_revision`, `note_tags`' + public function loadNotesOverview( + $since = null, $rawtag = null, $includeDate = false + ) { + $sql = 'SELECT `note_guid`, `note_title`' + . ', `note_last_sync_revision`, `note_tags`' + . ', `note_last_change_date`' . ' FROM `*PREFIX*grauphel_notes`' - . ' WHERE note_user = ?', - array($this->username) - ); + . ' WHERE note_user = ?'; + $sqlData = array($this->username); + + if ($since !== null) { + $sqlData[] = $since; + $sql .= ' AND note_last_sync_revision > ?'; + } - $notes = array(); if ($rawtag == 'grauphel:special:all') { $rawtag = null; } else if ($rawtag == 'grauphel:special:untagged') { @@ -308,14 +416,15 @@ class NoteStorage } else { $jsRawtag = json_encode($rawtag); } + if ($rawtag !== null) { + $sqlData[] = '%' . $jsRawtag . '%'; + $sql .= ' AND note_tags LIKE ?'; + } + + $result = \OC_DB::executeAudited($sql, $sqlData); + $notes = array(); while ($row = $result->fetchRow()) { - if ($since !== null && $row['note_last_sync_revision'] <= $since) { - continue; - } - if ($rawtag !== null && strpos($row['note_tags'], $jsRawtag) === false) { - continue; - } - $notes[] = array( + $note = array( 'guid' => $row['note_guid'], 'ref' => array( 'api-ref' => $this->urlGen->getAbsoluteURL( @@ -327,10 +436,21 @@ class NoteStorage ) ) ), - 'href' => null,//FIXME + 'href' => $this->urlGen->getAbsoluteURL( + $this->urlGen->linkToRoute( + 'grauphel.gui.note', + array( + 'guid' => $row['note_guid'] + ) + ) + ), ), 'title' => $row['note_title'], ); + if ($includeDate) { + $note['last-change-date'] = $row['note_last_change_date']; + } + $notes[] = $note; } return $notes; @@ -398,15 +518,15 @@ class NoteStorage { return array( 'note_guid' => $note->guid, - 'note_title' => $note->title, + 'note_title' => (string) $note->title, - 'note_content' => $note->{'note-content'}, - 'note_content_version' => $note->{'note-content-version'}, + 'note_content' => (string) $note->{'note-content'}, + 'note_content_version' => (string) $note->{'note-content-version'}, 'note_create_date' => $note->{'create-date'}, 'note_last_change_date' => $note->{'last-change-date'}, 'note_last_metadata_change_date' => $note->{'last-metadata-change-date'}, - + 'note_open_on_startup' => (int) $note->{'open-on-startup'}, 'note_pinned' => (int) $note->pinned, 'note_tags' => json_encode($note->tags),