From 8ee8a573f1f7e9cc45f9b3c46627d15670f14f3a Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sat, 29 Sep 2018 20:47:17 +0200 Subject: Custom labels (#2027) * First draft of custom tags https://github.com/FreshRSS/FreshRSS/issues/928 https://github.com/FreshRSS/FreshRSS/issues/1367 * SMALLINT to BIGINT for id_entry And uppercase SQL types * Fix layout for unreads * Start UI menu * Change menu order * Clean database helpers https://github.com/FreshRSS/FreshRSS/pull/2027#discussion_r217971535 * Travis rules do not understand PostgreSQL constants Grrr * Tag controller + UI * Add column attributes to tags * Use only favicon for now, for label * Fix styling for different themes * Constant for maximum InnoDB index length in Unicode https://github.com/FreshRSS/FreshRSS/pull/2027#discussion_r219052200 (I would have personnally prefered keeping the readability of a real value instead of a constant, in this case of many SQL fields) * Use FreshRSS_Factory::createCategoryDao * Add view of all articles containing any tag * Fix search in tags * Mark as read tags * Partial auto-update unread tags * More auto update tag unreads * Add tag deletion * Do not purge tagged articles * Minor comment * Fix SQLite and UI bug * Google Reader API support for user tags Add SQL check that tag names must be distinct from category names * whitespace * Add missing API for EasyRSS * Compatibility SQLite Problematic parentheses * Add SQL DISTINCT for cases with multiple tags * Fix for PostgreSQL PostgreSQL needs some additional type hint to avoid "could not determine data type of parameter $1" http://www.postgresql-archive.org/Could-not-determine-data-type-of-parameter-1-tp2171092p2171094.html --- app/Models/TagDAO.php | 310 ++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 310 insertions(+) create mode 100644 app/Models/TagDAO.php (limited to 'app/Models/TagDAO.php') diff --git a/app/Models/TagDAO.php b/app/Models/TagDAO.php new file mode 100644 index 000000000..ad67c1abe --- /dev/null +++ b/app/Models/TagDAO.php @@ -0,0 +1,310 @@ +bd->inTransaction(); + if ($hadTransaction) { + $this->bd->commit(); + } + try { + $db = FreshRSS_Context::$system_conf->db; + require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php'); + Minz_Log::warning('SQL CREATE TABLE tag...'); + if (defined('SQL_CREATE_TABLE_TAGS')) { + $sql = sprintf(SQL_CREATE_TABLE_TAGS, $this->prefix); + $stm = $this->bd->prepare($sql); + $ok = $stm && $stm->execute(); + } else { + global $SQL_CREATE_TABLE_TAGS; + $ok = !empty($SQL_CREATE_TABLE_TAGS); + foreach ($SQL_CREATE_TABLE_TAGS as $instruction) { + $sql = sprintf($instruction, $this->prefix); + $stm = $this->bd->prepare($sql); + $ok &= $stm && $stm->execute(); + } + } + } catch (Exception $e) { + Minz_Log::error('FreshRSS_EntryDAO::createTagTable error: ' . $e->getMessage()); + } + if ($hadTransaction) { + $this->bd->beginTransaction(); + } + return $ok; + } + + protected function autoUpdateDb($errorInfo) { + if (isset($errorInfo[0])) { + if ($errorInfo[0] === FreshRSS_DatabaseDAO::ER_BAD_TABLE_ERROR || $errorInfo[0] === FreshRSS_DatabaseDAOPGSQL::UNDEFINED_TABLE) { + if (stripos($errorInfo[2], 'tag') !== false) { + return $this->createTagTable(); //v1.12.0 + } + } + } + return false; + } + + public function addTag($valuesTmp) { + $sql = 'INSERT INTO `' . $this->prefix . 'tag`(name, attributes) ' + . 'SELECT * FROM (SELECT TRIM(?), TRIM(?)) t2 ' //TRIM() to provide a type hint as text for PostgreSQL + . 'WHERE NOT EXISTS (SELECT 1 FROM `' . $this->prefix . 'category` WHERE name = TRIM(?))'; //No category of the same name + $stm = $this->bd->prepare($sql); + + $valuesTmp['name'] = mb_strcut(trim($valuesTmp['name']), 0, 63, 'UTF-8'); + $values = array( + $valuesTmp['name'], + isset($valuesTmp['attributes']) ? json_encode($valuesTmp['attributes']) : '', + $valuesTmp['name'], + ); + + if ($stm && $stm->execute($values)) { + return $this->bd->lastInsertId('"' . $this->prefix . 'tag_id_seq"'); + } else { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::error('SQL error addTag: ' . $info[2]); + return false; + } + } + + public function addTagObject($tag) { + $tag = $this->searchByName($tag->name()); + if (!$tag) { + $values = array( + 'name' => $tag->name(), + 'attributes' => $tag->attributes(), + ); + return $this->addTag($values); + } + return $tag->id(); + } + + public function updateTag($id, $valuesTmp) { + $sql = 'UPDATE `' . $this->prefix . 'tag` SET name=?, attributes=? WHERE id=? ' + . 'AND NOT EXISTS (SELECT 1 FROM `' . $this->prefix . 'category` WHERE name = ?)'; //No category of the same name + $stm = $this->bd->prepare($sql); + + $valuesTmp['name'] = mb_strcut(trim($valuesTmp['name']), 0, 63, 'UTF-8'); + $values = array( + $valuesTmp['name'], + isset($valuesTmp['attributes']) ? json_encode($valuesTmp['attributes']) : '', + $id, + $valuesTmp['name'], + ); + + if ($stm && $stm->execute($values)) { + return $stm->rowCount(); + } else { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::error('SQL error updateTag: ' . $info[2]); + return false; + } + } + + public function updateTagAttribute($tag, $key, $value) { + if ($tag instanceof FreshRSS_Tag) { + $tag->_attributes($key, $value); + return $this->updateFeed( + $tag->id(), + array('attributes' => $feed->attributes()) + ); + } + return false; + } + + public function deleteTag($id) { + if ($id <= 0) { + return false; + } + $sql = 'DELETE FROM `' . $this->prefix . 'tag` WHERE id=?'; + $stm = $this->bd->prepare($sql); + + $values = array($id); + + if ($stm && $stm->execute($values)) { + return $stm->rowCount(); + } else { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::error('SQL error deleteTag: ' . $info[2]); + return false; + } + } + + public function searchById($id) { + $sql = 'SELECT * FROM `' . $this->prefix . 'tag` WHERE id=?'; + $stm = $this->bd->prepare($sql); + $values = array($id); + $stm->execute($values); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + $tag = self::daoToTag($res); + return isset($tag[0]) ? $tag[0] : null; + } + + public function searchByName($name) { + $sql = 'SELECT * FROM `' . $this->prefix . 'tag` WHERE name=?'; + $stm = $this->bd->prepare($sql); + $values = array($name); + $stm->execute($values); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + $tag = self::daoToTag($res); + return isset($tag[0]) ? $tag[0] : null; + } + + public function listTags($precounts = false) { + if ($precounts) { + $sql = 'SELECT t.id, t.name, count(e.id) AS unreads ' + . 'FROM `' . $this->prefix . 'tag` t ' + . 'LEFT OUTER JOIN `' . $this->prefix . 'entrytag` et ON et.id_tag = t.id ' + . 'LEFT OUTER JOIN `' . $this->prefix . 'entry` e ON et.id_entry = e.id AND e.is_read = 0 ' + . 'GROUP BY t.id ' + . 'ORDER BY t.name'; + } else { + $sql = 'SELECT * FROM `' . $this->prefix . 'tag` ORDER BY name'; + } + + $stm = $this->bd->prepare($sql); + if ($stm && $stm->execute()) { + return self::daoToTag($stm->fetchAll(PDO::FETCH_ASSOC)); + } else { + $info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo(); + if ($this->autoUpdateDb($info)) { + return $this->listTags($precounts); + } + Minz_Log::error('SQL error listTags: ' . $info[2]); + return false; + } + } + + public function count() { + $sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'tag`'; + $stm = $this->bd->prepare($sql); + $stm->execute(); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + return $res[0]['count']; + } + + public function countEntries($id) { + $sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'entrytag` WHERE id_tag=?'; + $stm = $this->bd->prepare($sql); + $values = array($id); + $stm->execute($values); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + return $res[0]['count']; + } + + public function countNotRead($id) { + $sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'entrytag` et ' + . 'INNER JOIN `' . $this->prefix . 'entry` e ON et.id_entry=e.id ' + . 'WHERE et.id_tag=? AND e.is_read=0'; + $stm = $this->bd->prepare($sql); + $values = array($id); + $stm->execute($values); + $res = $stm->fetchAll(PDO::FETCH_ASSOC); + return $res[0]['count']; + } + + public function tagEntry($id_tag, $id_entry, $checked = true) { + if ($checked) { + $sql = 'INSERT ' . $this->sqlIgnore() . ' INTO `' . $this->prefix . 'entrytag`(id_tag, id_entry) VALUES(?, ?)'; + } else { + $sql = 'DELETE FROM `' . $this->prefix . 'entrytag` WHERE id_tag=? AND id_entry=?'; + } + $stm = $this->bd->prepare($sql); + $values = array($id_tag, $id_entry); + + if ($stm && $stm->execute($values)) { + return true; + } else { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::error('SQL error tagEntry: ' . $info[2]); + return false; + } + } + + public function getTagsForEntry($id_entry) { + $sql = 'SELECT t.id, t.name, et.id_entry IS NOT NULL as checked ' + . 'FROM `' . $this->prefix . 'tag` t ' + . 'LEFT OUTER JOIN `' . $this->prefix . 'entrytag` et ON et.id_tag = t.id AND et.id_entry=? ' + . 'ORDER BY t.name'; + + $stm = $this->bd->prepare($sql); + $values = array($id_entry); + + if ($stm && $stm->execute($values)) { + $lines = $stm->fetchAll(PDO::FETCH_ASSOC); + for ($i = count($lines) - 1; $i >= 0; $i--) { + $lines[$i]['id'] = intval($lines[$i]['id']); + $lines[$i]['checked'] = !empty($lines[$i]['checked']); + } + return $lines; + } else { + $info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo(); + if ($this->autoUpdateDb($info)) { + return $this->getTagsForEntry($id_entry); + } + Minz_Log::error('SQL error getTagsForEntry: ' . $info[2]); + return false; + } + } + + //For API + public function getEntryIdsTagNames($entries) { + $sql = 'SELECT et.id_entry, t.name ' + . 'FROM `' . $this->prefix . 'tag` t ' + . 'INNER JOIN `' . $this->prefix . 'entrytag` et ON et.id_tag = t.id'; + + $values = array(); + if (is_array($entries) && count($entries) > 0) { + $sql .= ' AND et.id_entry IN (' . str_repeat('?,', count($entries) - 1). '?)'; + foreach ($entries as $entry) { + $values[] = $entry->id(); + } + } + $stm = $this->bd->prepare($sql); + + if ($stm && $stm->execute($values)) { + $result = array(); + foreach ($stm->fetchAll(PDO::FETCH_ASSOC) as $line) { + $entryId = 'e_' . $line['id_entry']; + $tagName = $line['name']; + if (empty($result[$entryId])) { + $result[$entryId] = array(); + } + $result[$entryId][] = $tagName; + } + return $result; + } else { + $info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo(); + if ($this->autoUpdateDb($info)) { + return $this->getTagNamesEntryIds($id_entry); + } + Minz_Log::error('SQL error getTagNamesEntryIds: ' . $info[2]); + return false; + } + } + + public static function daoToTag($listDAO) { + $list = array(); + if (!is_array($listDAO)) { + $listDAO = array($listDAO); + } + foreach ($listDAO as $key => $dao) { + $tag = new FreshRSS_Tag( + $dao['name'] + ); + $tag->_id($dao['id']); + if (!empty($dao['attributes'])) { + $tag->_attributes('', $dao['attributes']); + } + if (isset($dao['unreads'])) { + $tag->_nbUnread($dao['unreads']); + } + $list[$key] = $tag; + } + return $list; + } +} -- cgit v1.2.3 From 307e6995fec51d368beeada9e1b69c40c3e7d065 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 25 Oct 2018 22:43:13 +0200 Subject: MySQL GUID case sensitive (#2078) * MySQL GUID case sensitive latin1_bin https://github.com/FreshRSS/FreshRSS/issues/2077 * Prepare update for existing bases * Perform DB update during actualize * Reduce frequency slightly * No optimize at the same time * Take advantage of the SQL modifications in 1.12 * Move higher up * Move to purge, which all users can manually call --- app/Controllers/entryController.php | 4 ++++ app/Models/DatabaseDAO.php | 19 +++++++++++++++++++ app/Models/TagDAO.php | 5 +++++ app/SQL/install.sql.mysql.php | 17 +++++++++++------ 4 files changed, 39 insertions(+), 6 deletions(-) (limited to 'app/Models/TagDAO.php') diff --git a/app/Controllers/entryController.php b/app/Controllers/entryController.php index 21d51af34..78ddbf085 100755 --- a/app/Controllers/entryController.php +++ b/app/Controllers/entryController.php @@ -207,6 +207,10 @@ class FreshRSS_entry_Controller extends Minz_ActionController { $feedDAO->updateCachedValues(); + //Minor DB checks: + $databaseDAO = FreshRSS_Factory::createDatabaseDAO(); + $databaseDAO->ensureCaseInsensitiveGuids(); //FreshRSS 1.12 + invalidateHttpCache(); Minz_Request::good(_t('feedback.sub.purge_completed', $nb_total), array( 'c' => 'configure', diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php index 54076d7a9..dbd328bf7 100644 --- a/app/Models/DatabaseDAO.php +++ b/app/Models/DatabaseDAO.php @@ -141,4 +141,23 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { } return $ok; } + + public function ensureCaseInsensitiveGuids() { + $ok = true; + $db = FreshRSS_Context::$system_conf->db; + if ($db['type'] === 'mysql') { + include_once(APP_PATH . '/SQL/install.sql.mysql.php'); + if (defined('SQL_UPDATE_GUID_LATIN1_BIN')) { //FreshRSS 1.12 + try { + $sql = sprintf(SQL_UPDATE_GUID_LATIN1_BIN, $this->prefix); + $stm = $this->bd->prepare($sql); + $ok = $stm->execute(); + } catch (Exception $e) { + $ok = false; + Minz_Log::error('FreshRSS_DatabaseDAO::ensureCaseInsensitiveGuids error: ' . $e->getMessage()); + } + } + } + return $ok; + } } diff --git a/app/Models/TagDAO.php b/app/Models/TagDAO.php index ad67c1abe..1b59c8971 100644 --- a/app/Models/TagDAO.php +++ b/app/Models/TagDAO.php @@ -15,6 +15,11 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { try { $db = FreshRSS_Context::$system_conf->db; require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php'); + + Minz_Log::warning('SQL ALTER GUID case sensitivity...'); + $databaseDAO = FreshRSS_Factory::createDatabaseDAO(); + $databaseDAO->ensureCaseInsensitiveGuids(); + Minz_Log::warning('SQL CREATE TABLE tag...'); if (defined('SQL_CREATE_TABLE_TAGS')) { $sql = sprintf(SQL_CREATE_TABLE_TAGS, $this->prefix); diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index 1fc7e44d3..b3353ac95 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -12,10 +12,10 @@ ENGINE = INNODB; CREATE TABLE IF NOT EXISTS `%1$sfeed` ( `id` SMALLINT NOT NULL AUTO_INCREMENT, -- v0.7 - `url` VARCHAR(511) CHARACTER SET latin1 NOT NULL, + `url` VARCHAR(511) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `category` SMALLINT DEFAULT 0, -- v0.7 `name` VARCHAR(' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') NOT NULL, - `website` VARCHAR(255) CHARACTER SET latin1, + `website` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_bin, `description` TEXT, `lastUpdate` INT(11) DEFAULT 0, -- Until year 2038 `priority` TINYINT(2) NOT NULL DEFAULT 10, @@ -38,11 +38,11 @@ ENGINE = INNODB; CREATE TABLE IF NOT EXISTS `%1$sentry` ( `id` BIGINT NOT NULL, -- v0.7 - `guid` VARCHAR(760) CHARACTER SET latin1 NOT NULL, -- Maximum for UNIQUE is 767B + `guid` VARCHAR(760) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, -- Maximum for UNIQUE is 767B `title` VARCHAR(255) NOT NULL, `author` VARCHAR(255), `content_bin` BLOB, -- v0.7 - `link` VARCHAR(1023) CHARACTER SET latin1 NOT NULL, + `link` VARCHAR(1023) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `date` INT(11), -- Until year 2038 `lastSeen` INT(11) DEFAULT 0, -- v1.1.1, Until year 2038 `hash` BINARY(16), -- v1.1.1 @@ -66,11 +66,11 @@ INSERT IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s"); define('SQL_CREATE_TABLE_ENTRYTMP', ' CREATE TABLE IF NOT EXISTS `%1$sentrytmp` ( -- v1.7 `id` BIGINT NOT NULL, - `guid` VARCHAR(760) CHARACTER SET latin1 NOT NULL, + `guid` VARCHAR(760) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `title` VARCHAR(255) NOT NULL, `author` VARCHAR(255), `content_bin` BLOB, - `link` VARCHAR(1023) CHARACTER SET latin1 NOT NULL, + `link` VARCHAR(1023) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `date` INT(11), `lastSeen` INT(11) DEFAULT 0, `hash` BINARY(16), @@ -136,3 +136,8 @@ ALTER TABLE `%1$sentry` MODIFY `author` VARCHAR(255) CHARACTER SET utf8mb4 COLLA ALTER TABLE `%1$sentry` MODIFY `tags` VARCHAR(1023) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; OPTIMIZE TABLE `%1$sentry`; '); + +define('SQL_UPDATE_GUID_LATIN1_BIN', ' -- v1.12 +ALTER TABLE `%1$sentrytmp` MODIFY `guid` VARCHAR(760) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL; +ALTER TABLE `%1$sentry` MODIFY `guid` VARCHAR(760) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL; +'); -- cgit v1.2.3