Add database management page

[grauphel.git] / lib / notestorage.php

diff --git a/lib/notestorage.php b/lib/notestorage.php
index 63e1516f896c9a72af8d096adb06b66986c9895d..fb6803029f780b8a00b31c8f6b54685d40daf435 100644 (file)
--- a/lib/notestorage.php
+++ b/lib/notestorage.php
@@ -27,26 +27,46 @@ namespace OCA\Grauphel\Lib;
 class NoteStorage
 {
     protected $urlGen;
 class NoteStorage
 {
     protected $urlGen;

+    protected $username;

 
     public function __construct($urlGen)
     {
 
     public function __construct($urlGen)
     {

-        $this->urlGen = $urlGen;
+        $this->urlGen   = $urlGen;

     }
     }

+
+    public function setUsername($username)
+    {
+        $this->username = $username;
+    }
+

     /**
      * Create a new sync data object for fresh users.
      * Used by loadSyncData()
      *
     /**
      * Create a new sync data object for fresh users.
      * Used by loadSyncData()
      *

-     * @param string $username User name
-     *

      * @return SyncData New synchronization statistics
      */
      * @return SyncData New synchronization statistics
      */

-    protected function getNewSyncData($username)
+    protected function getNewSyncData()

     {
         $syncdata = new SyncData();
     {
         $syncdata = new SyncData();

-        $syncdata->initNew($username);
+        $syncdata->initNew($this->username);

         return $syncdata;
     }
 
         return $syncdata;
     }
 

+    public function getTags()
+    {
+        $result = \OC_DB::executeAudited(
+            'SELECT `note_tags` FROM `*PREFIX*grauphel_notes`'
+            . ' WHERE note_user = ?',
+            array($this->username)
+        );
+
+        $tags = array();
+        while ($row = $result->fetchRow()) {
+            $tags = array_merge($tags, json_decode($row['note_tags']));
+        }
+        return array_unique($tags);
+    }
+

     /**
      * Updates the given $note object with data from $noteUpdate.
      * Sets the last-sync-revision to $syncRevision
     /**
      * Updates the given $note object with data from $noteUpdate.
      * Sets the last-sync-revision to $syncRevision


@@ -107,21 +127,19 @@ class NoteStorage
      * Loads synchronization data for the given user.
      * Creates fresh sync data if there are none for the user.
      *
      * Loads synchronization data for the given user.
      * Creates fresh sync data if there are none for the user.
      *

-     * @param string $username User name
-     *

      * @return SyncData Synchronization statistics (revision, sync guid)
      */
      * @return SyncData Synchronization statistics (revision, sync guid)
      */

-    public function loadSyncData($username)
+    public function loadSyncData()

     {
         $row = \OC_DB::executeAudited(
             'SELECT * FROM `*PREFIX*grauphel_syncdata`'
             . ' WHERE `syncdata_user` = ?',
     {
         $row = \OC_DB::executeAudited(
             'SELECT * FROM `*PREFIX*grauphel_syncdata`'
             . ' WHERE `syncdata_user` = ?',

-            array($username)
+            array($this->username)

         )->fetchRow();
 
         if ($row === false) {
         )->fetchRow();
 
         if ($row === false) {

-            $syncdata = $this->getNewSyncData($username);
-            $this->saveSyncData($username, $syncdata);
+            $syncdata = $this->getNewSyncData();
+            $this->saveSyncData($syncdata);

         } else {
             $syncdata = new SyncData();
             $syncdata->latestSyncRevision = (int) $row['syncdata_latest_sync_revision'];
         } else {
             $syncdata = new SyncData();
             $syncdata->latestSyncRevision = (int) $row['syncdata_latest_sync_revision'];


@@ -134,17 +152,16 @@ class NoteStorage
     /**
      * Save synchronization data for the given user.
      *
     /**
      * Save synchronization data for the given user.
      *

-     * @param string   $username User name

      * @param SyncData $syncdata Synchronization data object
      *
      * @return void
      */
      * @param SyncData $syncdata Synchronization data object
      *
      * @return void
      */

-    public function saveSyncData($username, SyncData $syncdata)
+    public function saveSyncData(SyncData $syncdata)

     {
         $row = \OC_DB::executeAudited(
             'SELECT * FROM `*PREFIX*grauphel_syncdata`'
             . ' WHERE `syncdata_user` = ?',
     {
         $row = \OC_DB::executeAudited(
             'SELECT * FROM `*PREFIX*grauphel_syncdata`'
             . ' WHERE `syncdata_user` = ?',

-            array($username)
+            array($this->username)

         )->fetchRow();
 
         if ($row === false) {
         )->fetchRow();
 
         if ($row === false) {


@@ -153,7 +170,7 @@ class NoteStorage
                 . '(`syncdata_user`, `syncdata_latest_sync_revision`, `syncdata_current_sync_guid`)'
                 . ' VALUES(?, ?, ?)';
             $params = array(
                 . '(`syncdata_user`, `syncdata_latest_sync_revision`, `syncdata_current_sync_guid`)'
                 . ' VALUES(?, ?, ?)';
             $params = array(

-                $username,
+                $this->username,

                 $syncdata->latestSyncRevision,
                 $syncdata->currentSyncGuid
             );
                 $syncdata->latestSyncRevision,
                 $syncdata->currentSyncGuid
             );


@@ -167,26 +184,41 @@ class NoteStorage
                 . ' `' . implode('` = ?, `', array_keys($data)) . '` = ?'
                 . ' WHERE `syncdata_user` = ?';
             $params = array_values($data);
                 . ' `' . implode('` = ?, `', array_keys($data)) . '` = ?'
                 . ' WHERE `syncdata_user` = ?';
             $params = array_values($data);

-            $params[] = $username;
+            $params[] = $this->username;

         }
         \OC_DB::executeAudited($sql, $params);
     }
 
         }
         \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.
      *
     /**
      * Load a note from the storage.
      *

-     * @param string  $username  User name

      * @param string  $guid      Note identifier
      * @param boolean $createNew Create a new note if it does not exist
      *
      * @return object Note object, NULL if !$createNew and note does not exist
      */
      * @param string  $guid      Note identifier
      * @param boolean $createNew Create a new note if it does not exist
      *
      * @return object Note object, NULL if !$createNew and note does not exist
      */

-    public function load($username, $guid, $createNew = true)
+    public function load($guid, $createNew = true)

     {
         $row = \OC_DB::executeAudited(
             'SELECT * FROM `*PREFIX*grauphel_notes`'
             . ' WHERE `note_user` = ? AND `note_guid` = ?',
     {
         $row = \OC_DB::executeAudited(
             'SELECT * FROM `*PREFIX*grauphel_notes`'
             . ' WHERE `note_user` = ? AND `note_guid` = ?',

-            array($username, $guid)
+            array($this->username, $guid)

         )->fetchRow();
 
         if ($row === false) {
         )->fetchRow();
 
         if ($row === false) {


@@ -209,30 +241,29 @@ class NoteStorage
                 'tags'            => array(),
             );
         }
                 'tags'            => array(),
             );
         }

-        
+

         return $this->noteFromRow($row);
     }
 
     /**
      * Save a note into storage.
      *
         return $this->noteFromRow($row);
     }
 
     /**
      * Save a note into storage.
      *

-     * @param string $username User name
-     * @param object $note     Note to save
+     * @param object $note Note to save

      *
      * @return void
      */
      *
      * @return void
      */

-    public function save($username, $note)
+    public function save($note)

     {
         $row = \OC_DB::executeAudited(
             'SELECT * FROM `*PREFIX*grauphel_notes`'
             . ' WHERE `note_user` = ? AND `note_guid` = ?',
     {
         $row = \OC_DB::executeAudited(
             'SELECT * FROM `*PREFIX*grauphel_notes`'
             . ' WHERE `note_user` = ? AND `note_guid` = ?',

-            array($username, $note->guid)
+            array($this->username, $note->guid)

         )->fetchRow();
 
         $data = $this->rowFromNote($note);
         if ($row === false) {
             //INSERT
         )->fetchRow();
 
         $data = $this->rowFromNote($note);
         if ($row === false) {
             //INSERT

-            $data['note_user'] = $username;
+            $data['note_user'] = $this->username;

             $sql = 'INSERT INTO `*PREFIX*grauphel_notes`'
                 . ' (`' . implode('`, `', array_keys($data)) . '`)'
                 . ' VALUES(' . implode(', ', array_fill(0, count($data), '?')) . ')';
             $sql = 'INSERT INTO `*PREFIX*grauphel_notes`'
                 . ' (`' . implode('`, `', array_keys($data)) . '`)'
                 . ' VALUES(' . implode(', ', array_fill(0, count($data), '?')) . ')';


@@ -243,7 +274,7 @@ class NoteStorage
                 . '`' . implode('` = ?, `', array_keys($data)) . '` = ?'
                 . ' WHERE `note_user` = ? AND `note_guid` = ?';
             $params = array_values($data);
                 . '`' . implode('` = ?, `', array_keys($data)) . '` = ?'
                 . ' WHERE `note_user` = ? AND `note_guid` = ?';
             $params = array_values($data);

-            $params[] = $username;
+            $params[] = $this->username;

             $params[] = $note->guid;
         }
         \OC_DB::executeAudited($sql, $params);
             $params[] = $note->guid;
         }
         \OC_DB::executeAudited($sql, $params);


@@ -252,17 +283,30 @@ class NoteStorage
     /**
      * Delete a note from storage.
      *
     /**
      * Delete a note from storage.
      *

-     * @param string $username User name
-     * @param object $guid     ID of the note
+     * @param object $guid ID of the note

      *
      * @return void
      */
      *
      * @return void
      */

-    public function delete($username, $guid)
+    public function delete($guid)

     {
         \OC_DB::executeAudited(
             'DELETE FROM `*PREFIX*grauphel_notes`'
             . ' WHERE `note_user` = ? AND `note_guid` = ?',
     {
         \OC_DB::executeAudited(
             'DELETE FROM `*PREFIX*grauphel_notes`'
             . ' WHERE `note_user` = ? AND `note_guid` = ?',

-            array($username, $guid)
+            array($this->username, $guid)
+        );
+    }
+
+    /**
+     * Delete all notes from storage.
+     *
+     * @return void
+     */
+    public function deleteAll()
+    {
+        \OC_DB::executeAudited(
+            'DELETE FROM `*PREFIX*grauphel_notes`'
+            . ' WHERE `note_user` = ?',
+            array($this->username)

         );
     }
 
         );
     }
 


@@ -270,25 +314,37 @@ 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 string  $username User name
-     * @param integer $since    Revision number after which the notes changed
+     * @param integer $since  Revision number after which the notes changed
+     * @param string  $rawtag Filter by tag. Special tags:
+     *                        - grauphel:special:all
+     *                        - grauphel:special:untagged

      *
      * @return array Array of short note objects
      */
      *
      * @return array Array of short note objects
      */

-    public function loadNotesOverview($username, $since = null)
+    public function loadNotesOverview($since = null, $rawtag = null)

     {
         $result = \OC_DB::executeAudited(
     {
         $result = \OC_DB::executeAudited(

-            'SELECT `note_guid`, `note_title`, `note_last_sync_revision`'
+            'SELECT `note_guid`, `note_title`, `note_last_sync_revision`, `note_tags`'

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

-            array($username)
+            array($this->username)

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

+        if ($rawtag == 'grauphel:special:all') {
+            $rawtag = null;
+        } else if ($rawtag == 'grauphel:special:untagged') {
+            $jsRawtag = json_encode(array());
+        } else {
+            $jsRawtag = json_encode($rawtag);
+        }

         while ($row = $result->fetchRow()) {
             if ($since !== null && $row['note_last_sync_revision'] <= $since) {
                 continue;
             }
         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(
                 'guid' => $row['note_guid'],
                 'ref'  => array(
             $notes[] = array(
                 'guid' => $row['note_guid'],
                 'ref'  => array(


@@ -296,7 +352,7 @@ class NoteStorage
                         $this->urlGen->linkToRoute(
                             'grauphel.api.note',
                             array(
                         $this->urlGen->linkToRoute(
                             'grauphel.api.note',
                             array(

-                                'username' => $username,
+                                'username' => $this->username,

                                 'guid' => $row['note_guid']
                             )
                         )
                                 'guid' => $row['note_guid']
                             )
                         )


@@ -314,17 +370,16 @@ class NoteStorage
      * Load notes for the given user in full form.
      * Optionally only those changed after $since revision
      *
      * Load notes for the given user in full form.
      * Optionally only those changed after $since revision
      *

-     * @param string  $username User name
-     * @param integer $since    Revision number after which the notes changed
+     * @param integer $since Revision number after which the notes changed

      *
      * @return array Array of full note objects
      */
      *
      * @return array Array of full note objects
      */

-    public function loadNotesFull($username, $since = null)
+    public function loadNotesFull($since = null)

     {
         $result = \OC_DB::executeAudited(
             'SELECT * FROM `*PREFIX*grauphel_notes`'
             . ' WHERE note_user = ?',
     {
         $result = \OC_DB::executeAudited(
             'SELECT * FROM `*PREFIX*grauphel_notes`'
             . ' WHERE note_user = ?',

-            array($username)
+            array($this->username)

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


@@ -338,14 +393,24 @@ class NoteStorage
         return $notes;
     }
 
         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'],
 
     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'],
 
             'title'                => $row['note_title'],
             'note-content'         => $row['note_content'],


@@ -363,17 +428,17 @@ class NoteStorage
     {
         return array(
             'note_guid'  => $note->guid,
     {
         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_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'},
             'note_tags'            => json_encode($note->tags),
 
             'note_last_sync_revision' => $note->{'last-sync-revision'},