class NoteStorage
{
protected $urlGen;
+ protected $username;
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()
*
- * @param string $username User name
- *
* @return SyncData New synchronization statistics
*/
- protected function getNewSyncData($username)
+ protected function getNewSyncData()
{
$syncdata = new SyncData();
- $syncdata->initNew($username);
+ $syncdata->initNew($this->username);
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
* 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)
*/
- public function loadSyncData($username)
+ public function loadSyncData()
{
$row = \OC_DB::executeAudited(
'SELECT * FROM `*PREFIX*grauphel_syncdata`'
. ' WHERE `syncdata_user` = ?',
- array($username)
+ array($this->username)
)->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'];
/**
* Save synchronization data for the given user.
*
- * @param string $username User name
* @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` = ?',
- array($username)
+ array($this->username)
)->fetchRow();
if ($row === false) {
. '(`syncdata_user`, `syncdata_latest_sync_revision`, `syncdata_current_sync_guid`)'
. ' VALUES(?, ?, ?)';
$params = array(
- $username,
+ $this->username,
$syncdata->latestSyncRevision,
$syncdata->currentSyncGuid
);
. ' `' . implode('` = ?, `', array_keys($data)) . '` = ?'
. ' WHERE `syncdata_user` = ?';
$params = array_values($data);
- $params[] = $username;
+ $params[] = $this->username;
}
\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.
*
- * @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
*/
- 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` = ?',
- array($username, $guid)
+ array($this->username, $guid)
)->fetchRow();
if ($row === false) {
'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 LIKE ? OR note_tags LIKE ? OR note_content LIKE ?)';
+ $sqlTplNot = ' AND NOT (note_title LIKE ? OR note_tags LIKE ? OR note_content LIKE ?)';
+ $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.
*
- * @param string $username User name
- * @param object $note Note to save
+ * @param object $note Note to save
*
* @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` = ?',
- array($username, $note->guid)
+ array($this->username, $note->guid)
)->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), '?')) . ')';
. '`' . 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);
/**
* 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
*/
- public function delete($username, $guid)
+ public function delete($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)
+ );
}
/**
* 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
+ * @param boolean $includeDate Load the last modification date or not
*
* @return array Array of short note objects
*/
- public function loadNotesOverview($username, $since = null)
- {
- $result = \OC_DB::executeAudited(
- 'SELECT `note_guid`, `note_title`, `note_last_sync_revision`'
+ public function loadNotesOverview(
+ $since = null, $rawtag = null, $includeDate = false
+ ) {
+ $result = \OC_DB::executeAudited(
+ 'SELECT `note_guid`, `note_title`, `note_last_sync_revision`, `note_tags`'
+ . ', `note_last_change_date`'
. ' FROM `*PREFIX*grauphel_notes`'
. ' WHERE note_user = ?',
- array($username)
- );
+ array($this->username)
+ );
- $notes = array();
- while ($row = $result->fetchRow()) {
+ $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;
}
- $notes[] = array(
+ if ($rawtag !== null && strpos($row['note_tags'], $jsRawtag) === false) {
+ continue;
+ }
+ $note = array(
'guid' => $row['note_guid'],
'ref' => array(
'api-ref' => $this->urlGen->getAbsoluteURL(
$this->urlGen->linkToRoute(
'grauphel.api.note',
array(
- 'username' => $username,
+ 'username' => $this->username,
+ 'guid' => $row['note_guid']
+ )
+ )
+ ),
+ 'href' => $this->urlGen->getAbsoluteURL(
+ $this->urlGen->linkToRoute(
+ 'grauphel.gui.note',
+ array(
'guid' => $row['note_guid']
)
)
),
- 'href' => null,//FIXME
),
'title' => $row['note_title'],
);
- }
+ if ($includeDate) {
+ $note['last-change-date'] = $row['note_last_change_date'];
+ }
+ $notes[] = $note;
+ }
return $notes;
}
* 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
*/
- public function loadNotesFull($username, $since = null)
+ public function loadNotesFull($since = null)
{
- $result = \OC_DB::executeAudited(
+ $result = \OC_DB::executeAudited(
'SELECT * FROM `*PREFIX*grauphel_notes`'
. ' WHERE note_user = ?',
- array($username)
- );
+ array($this->username)
+ );
- $notes = array();
- while ($row = $result->fetchRow()) {
+ $notes = array();
+ while ($row = $result->fetchRow()) {
if ($since !== null && $row['note_last_sync_revision'] <= $since) {
continue;
}
$notes[] = $this->noteFromRow($row);
- }
+ }
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'],
'note-content-version' => $row['note_content_version'],
- 'open-on-startup' => $row['note_open_on_startup'],
- 'pinned' => $row['note_pinned'],
+ 'open-on-startup' => (bool) $row['note_open_on_startup'],
+ 'pinned' => (bool) $row['note_pinned'],
'tags' => json_decode($row['note_tags']),
- 'last-sync-revision' => $row['note_last_sync_revision'],
+ 'last-sync-revision' => (int) $row['note_last_sync_revision'],
);
}
{
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'},
git.cweiske.de / grauphel.git / blobdiff