#37: Use public database interface; fixes graupel on ownCloud 9

[grauphel.git] / lib / notestorage.php

diff --git a/lib/notestorage.php b/lib/notestorage.php
index 67baa202e3293e5486746ad81f3e3799f4b2e20c..2ae9fcb405702a9728f63488b268fe5933ae56c2 100644 (file)
--- a/lib/notestorage.php
+++ b/lib/notestorage.php
@@ -26,12 +26,18 @@ namespace OCA\Grauphel\Lib;
  */
 class NoteStorage
 {
  */
 class NoteStorage
 {

+    /**
+     * @var \OCP\IDBConnection
+     */
+    protected $db;
+

     protected $urlGen;
     protected $username;
 
     public function __construct($urlGen)
     {
     protected $urlGen;
     protected $username;
 
     public function __construct($urlGen)
     {

-        $this->urlGen   = $urlGen;
+        $this->urlGen = $urlGen;
+        $this->db     = \OC::$server->getDatabaseConnection();

     }
 
     public function setUsername($username)
     }
 
     public function setUsername($username)


@@ -54,14 +60,14 @@ class NoteStorage
 
     public function getTags()
     {
 
     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();
             '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);
             $tags = array_merge($tags, json_decode($row['note_tags']));
         }
         return array_unique($tags);


@@ -131,11 +137,11 @@ class NoteStorage
      */
     public function loadSyncData()
     {
      */
     public function loadSyncData()
     {

-        $row = \OC_DB::executeAudited(
+        $row = $this->db->executeQuery(

             'SELECT * FROM `*PREFIX*grauphel_syncdata`'
             . ' WHERE `syncdata_user` = ?',
             array($this->username)
             'SELECT * FROM `*PREFIX*grauphel_syncdata`'
             . ' WHERE `syncdata_user` = ?',
             array($this->username)

-        )->fetchRow();
+        )->fetch();

 
         if ($row === false) {
             $syncdata = $this->getNewSyncData();
 
         if ($row === false) {
             $syncdata = $this->getNewSyncData();


@@ -158,11 +164,11 @@ class NoteStorage
      */
     public function saveSyncData(SyncData $syncdata)
     {
      */
     public function saveSyncData(SyncData $syncdata)
     {

-        $row = \OC_DB::executeAudited(
+        $row = $this->db->executeQuery(

             'SELECT * FROM `*PREFIX*grauphel_syncdata`'
             . ' WHERE `syncdata_user` = ?',
             array($this->username)
             'SELECT * FROM `*PREFIX*grauphel_syncdata`'
             . ' WHERE `syncdata_user` = ?',
             array($this->username)

-        )->fetchRow();
+        )->fetch();

 
         if ($row === false) {
             //INSERT
 
         if ($row === false) {
             //INSERT


@@ -186,7 +192,7 @@ class NoteStorage
             $params = array_values($data);
             $params[] = $this->username;
         }
             $params = array_values($data);
             $params[] = $this->username;
         }

-        \OC_DB::executeAudited($sql, $params);
+        $this->db->executeQuery($sql, $params);

     }
 
     /**
     }
 
     /**


@@ -198,7 +204,7 @@ class NoteStorage
      */
     public function deleteSyncData()
     {
      */
     public function deleteSyncData()
     {

-        \OC_DB::executeAudited(
+        $this->db->executeQuery(

             'DELETE FROM `*PREFIX*grauphel_syncdata`'
             . ' WHERE `syncdata_user` = ?',
             array($this->username)
             'DELETE FROM `*PREFIX*grauphel_syncdata`'
             . ' WHERE `syncdata_user` = ?',
             array($this->username)


@@ -215,11 +221,11 @@ class NoteStorage
      */
     public function load($guid, $createNew = true)
     {
      */
     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)
             'SELECT * FROM `*PREFIX*grauphel_notes`'
             . ' WHERE `note_user` = ? AND `note_guid` = ?',
             array($this->username, $guid)

-        )->fetchRow();
+        )->fetch();

 
         if ($row === false) {
             if (!$createNew) {
 
         if ($row === false) {
             if (!$createNew) {


@@ -258,11 +264,11 @@ class NoteStorage
      */
     public function loadGuidByTitle($title)
     {
      */
     public function loadGuidByTitle($title)
     {

-        $row = \OC_DB::executeAudited(
+        $row = $this->db->executeQuery(

             'SELECT note_guid FROM `*PREFIX*grauphel_notes`'
             . ' WHERE `note_user` = ? AND `note_title` = ?',
             array($this->username, htmlspecialchars($title))
             'SELECT note_guid FROM `*PREFIX*grauphel_notes`'
             . ' WHERE `note_user` = ? AND `note_title` = ?',
             array($this->username, htmlspecialchars($title))

-        )->fetchRow();
+        )->fetch();

 
         if ($row === false) {
             return null;
 
         if ($row === false) {
             return null;


@@ -274,31 +280,44 @@ class NoteStorage
     /**
      * Search for a note
      *
     /**
      * Search for a note
      *

-     * @param string $keywords AND-concatenated query strings
+     * @param array $keywords arrays of query strings within keys AND and NOT

      *
      * @return array Database rows with note_guid and note_title
      */
      *
      * @return array Database rows with note_guid and note_title
      */

-    public function search($keywords)
+    public function search($keywordGroups)

     {
     {

-        $sqlWhere = ' AND (note_title LIKE ? OR note_tags LIKE ? OR note_content LIKE ?)';
+        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
         );
         $arData = array(
             $this->username
         );

-        foreach ($keywords as $keyword) {
-            $arData[] = '%' . $keyword . '%';//title
-            $arData[] = '%' . $keyword . '%';//tags
-            $arData[] = '%' . $keyword . '%';//content
+        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(
+
+        $result = $this->db->executeQuery(

             'SELECT `note_guid`, `note_title`'
             . ' FROM `*PREFIX*grauphel_notes`'
             . ' WHERE note_user = ?'
             'SELECT `note_guid`, `note_title`'
             . ' FROM `*PREFIX*grauphel_notes`'
             . ' WHERE note_user = ?'

-            . str_repeat($sqlWhere, count($keywords)),
+            . str_repeat($sqlTplAnd, count($keywordGroups['AND']))
+            . str_repeat($sqlTplNot, count($keywordGroups['NOT'])),

             $arData
         );
 
         $notes = array();
             $arData
         );
 
         $notes = array();

-        while ($row = $result->fetchRow()) {
+        while ($row = $result->fetch()) {

             $notes[] = $row;
         }
         return $notes;
             $notes[] = $row;
         }
         return $notes;


@@ -313,11 +332,11 @@ class NoteStorage
      */
     public function save($note)
     {
      */
     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)
             '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) {
 
         $data = $this->rowFromNote($note);
         if ($row === false) {


@@ -336,7 +355,7 @@ class NoteStorage
             $params[] = $this->username;
             $params[] = $note->guid;
         }
             $params[] = $this->username;
             $params[] = $note->guid;
         }

-        \OC_DB::executeAudited($sql, $params);
+        $this->db->executeQuery($sql, $params);

     }
 
     /**
     }
 
     /**


@@ -348,7 +367,7 @@ class NoteStorage
      */
     public function delete($guid)
     {
      */
     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 FROM `*PREFIX*grauphel_notes`'
             . ' WHERE `note_user` = ? AND `note_guid` = ?',
             array($this->username, $guid)


@@ -362,7 +381,7 @@ class NoteStorage
      */
     public function deleteAll()
     {
      */
     public function deleteAll()
     {

-        \OC_DB::executeAudited(
+        $this->db->executeQuery(

             'DELETE FROM `*PREFIX*grauphel_notes`'
             . ' WHERE `note_user` = ?',
             array($this->username)
             'DELETE FROM `*PREFIX*grauphel_notes`'
             . ' WHERE `note_user` = ?',
             array($this->username)


@@ -373,23 +392,29 @@ class NoteStorage
      * Load notes for the given user in short form.
      * Optionally only those changed after $since revision
      *
      * 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
      */
      *
      * @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`'
             . ' 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') {
         if ($rawtag == 'grauphel:special:all') {
             $rawtag = null;
         } else if ($rawtag == 'grauphel:special:untagged') {


@@ -397,14 +422,15 @@ class NoteStorage
         } else {
             $jsRawtag = json_encode($rawtag);
         }
         } else {
             $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(
+        if ($rawtag !== null) {
+            $sqlData[] = '%' . $jsRawtag . '%';
+            $sql .= ' AND note_tags LIKE ?';
+        }
+
+        $result = $this->db->executeQuery($sql, $sqlData);
+        $notes = array();
+        while ($row = $result->fetch()) {
+            $note = array(

                 'guid' => $row['note_guid'],
                 'ref'  => array(
                     'api-ref' => $this->urlGen->getAbsoluteURL(
                 'guid' => $row['note_guid'],
                 'ref'  => array(
                     'api-ref' => $this->urlGen->getAbsoluteURL(


@@ -416,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'],
             );
                 ),
                 'title' => $row['note_title'],
             );

+            if ($includeDate) {
+                $note['last-change-date'] = $row['note_last_change_date'];
+            }
+            $notes[] = $note;

         }
 
         return $notes;
         }
 
         return $notes;


@@ -435,14 +472,14 @@ class NoteStorage
      */
     public function loadNotesFull($since = null)
     {
      */
     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();
             '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;
             }
             if ($since !== null && $row['note_last_sync_revision'] <= $since) {
                 continue;
             }