X-Git-Url: https://git.cweiske.de/grauphel.git/blobdiff_plain/3e3dfcc65e13be5a49423bb90fc12e67f6b613dd..9501f181f621e9023a9cb27cbbc85afb2743eeca:/lib/notestorage.php diff --git a/lib/notestorage.php b/lib/notestorage.php index d9ceaab..2ae9fcb 100644 --- a/lib/notestorage.php +++ b/lib/notestorage.php @@ -26,12 +26,18 @@ namespace OCA\Grauphel\Lib; */ class NoteStorage { + /** + * @var \OCP\IDBConnection + */ + protected $db; + protected $urlGen; protected $username; public function __construct($urlGen) { - $this->urlGen = $urlGen; + $this->urlGen = $urlGen; + $this->db = \OC::$server->getDatabaseConnection(); } public function setUsername($username) @@ -54,14 +60,14 @@ class NoteStorage public function getTags() { - $result = \OC_DB::executeAudited( + $result = $this->db->executeQuery( 'SELECT `note_tags` FROM `*PREFIX*grauphel_notes`' . ' WHERE note_user = ?', array($this->username) ); $tags = array(); - while ($row = $result->fetchRow()) { + while ($row = $result->fetch()) { $tags = array_merge($tags, json_decode($row['note_tags'])); } return array_unique($tags); @@ -131,15 +137,15 @@ class NoteStorage */ public function loadSyncData() { - $row = \OC_DB::executeAudited( + $row = $this->db->executeQuery( 'SELECT * FROM `*PREFIX*grauphel_syncdata`' . ' WHERE `syncdata_user` = ?', array($this->username) - )->fetchRow(); + )->fetch(); if ($row === false) { - $syncdata = $this->getNewSyncData($this->username); - $this->saveSyncData($this->username, $syncdata); + $syncdata = $this->getNewSyncData(); + $this->saveSyncData($syncdata); } else { $syncdata = new SyncData(); $syncdata->latestSyncRevision = (int) $row['syncdata_latest_sync_revision']; @@ -158,11 +164,11 @@ class NoteStorage */ public function saveSyncData(SyncData $syncdata) { - $row = \OC_DB::executeAudited( + $row = $this->db->executeQuery( 'SELECT * FROM `*PREFIX*grauphel_syncdata`' . ' WHERE `syncdata_user` = ?', array($this->username) - )->fetchRow(); + )->fetch(); if ($row === false) { //INSERT @@ -186,7 +192,23 @@ class NoteStorage $params = array_values($data); $params[] = $this->username; } - \OC_DB::executeAudited($sql, $params); + $this->db->executeQuery($sql, $params); + } + + /** + * Delete synchronization data for the given user. + * + * @param SyncData $syncdata Synchronization data object + * + * @return void + */ + public function deleteSyncData() + { + $this->db->executeQuery( + 'DELETE FROM `*PREFIX*grauphel_syncdata`' + . ' WHERE `syncdata_user` = ?', + array($this->username) + ); } /** @@ -199,11 +221,11 @@ class NoteStorage */ public function load($guid, $createNew = true) { - $row = \OC_DB::executeAudited( + $row = $this->db->executeQuery( 'SELECT * FROM `*PREFIX*grauphel_notes`' . ' WHERE `note_user` = ? AND `note_guid` = ?', array($this->username, $guid) - )->fetchRow(); + )->fetch(); if ($row === false) { if (!$createNew) { @@ -225,10 +247,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 = $this->db->executeQuery( + 'SELECT note_guid FROM `*PREFIX*grauphel_notes`' + . ' WHERE `note_user` = ? AND `note_title` = ?', + array($this->username, htmlspecialchars($title)) + )->fetch(); + + 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 = $this->db->executeQuery( + '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->fetch()) { + $notes[] = $row; + } + return $notes; + } + /** * Save a note into storage. * @@ -238,11 +332,11 @@ class NoteStorage */ public function save($note) { - $row = \OC_DB::executeAudited( + $row = $this->db->executeQuery( 'SELECT * FROM `*PREFIX*grauphel_notes`' . ' WHERE `note_user` = ? AND `note_guid` = ?', array($this->username, $note->guid) - )->fetchRow(); + )->fetch(); $data = $this->rowFromNote($note); if ($row === false) { @@ -261,7 +355,7 @@ class NoteStorage $params[] = $this->username; $params[] = $note->guid; } - \OC_DB::executeAudited($sql, $params); + $this->db->executeQuery($sql, $params); } /** @@ -273,41 +367,70 @@ class NoteStorage */ public function delete($guid) { - \OC_DB::executeAudited( + $this->db->executeQuery( 'DELETE FROM `*PREFIX*grauphel_notes`' . ' WHERE `note_user` = ? AND `note_guid` = ?', array($this->username, $guid) ); } + /** + * Delete all notes from storage. + * + * @return void + */ + public function deleteAll() + { + $this->db->executeQuery( + '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 tags + * @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 > ?'; + } + if ($rawtag == 'grauphel:special:all') { + $rawtag = null; + } else if ($rawtag == 'grauphel:special:untagged') { + $jsRawtag = json_encode(array()); + } else { + $jsRawtag = json_encode($rawtag); + } + if ($rawtag !== null) { + $sqlData[] = '%' . $jsRawtag . '%'; + $sql .= ' AND note_tags LIKE ?'; + } + + $result = $this->db->executeQuery($sql, $sqlData); $notes = array(); - $jsRawtag = json_encode($rawtag); - 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( + while ($row = $result->fetch()) { + $note = array( 'guid' => $row['note_guid'], 'ref' => array( 'api-ref' => $this->urlGen->getAbsoluteURL( @@ -319,10 +442,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; @@ -338,14 +472,14 @@ class NoteStorage */ public function loadNotesFull($since = null) { - $result = \OC_DB::executeAudited( + $result = $this->db->executeQuery( 'SELECT * FROM `*PREFIX*grauphel_notes`' . ' WHERE note_user = ?', array($this->username) ); $notes = array(); - while ($row = $result->fetchRow()) { + while ($row = $result->fetch()) { if ($since !== null && $row['note_last_sync_revision'] <= $since) { continue; } @@ -355,14 +489,24 @@ class NoteStorage return $notes; } + protected function fixDate($date) + { + if (strlen($date) == 32) { + //Bug in grauphel 0.1.1; date fields in DB had only 32 instead of 33 + // characters. The last digit of the time zone was missing + $date .= '0'; + } + return $date; + } + protected function noteFromRow($row) { return (object) array( 'guid' => $row['note_guid'], - 'create-date' => $row['note_create_date'], - 'last-change-date' => $row['note_last_change_date'], - 'last-metadata-change-date' => $row['note_last_metadata_change_date'], + 'create-date' => $this->fixDate($row['note_create_date']), + 'last-change-date' => $this->fixDate($row['note_last_change_date']), + 'last-metadata-change-date' => $this->fixDate($row['note_last_metadata_change_date']), 'title' => $row['note_title'], 'note-content' => $row['note_content'], @@ -380,17 +524,17 @@ 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' => $note->{'open-on-startup'}, - 'note_pinned' => $note->pinned, + + 'note_open_on_startup' => (int) $note->{'open-on-startup'}, + 'note_pinned' => (int) $note->pinned, 'note_tags' => json_encode($note->tags), 'note_last_sync_revision' => $note->{'last-sync-revision'},