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/DatabaseDAO.php | 70 +++++++++++++++++++++++++++++----------------- 1 file changed, 45 insertions(+), 25 deletions(-) (limited to 'app/Models/DatabaseDAO.php') diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php index b8e5577e4..54076d7a9 100644 --- a/app/Models/DatabaseDAO.php +++ b/app/Models/DatabaseDAO.php @@ -4,6 +4,16 @@ * This class is used to test database is well-constructed. */ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { + + //MySQL error codes + const ER_BAD_FIELD_ERROR = '42S22'; + const ER_BAD_TABLE_ERROR = '42S02'; + const ER_TRUNCATED_WRONG_VALUE_FOR_FIELD = '1366'; + + //MySQL InnoDB maximum index length for UTF8MB4 + //https://dev.mysql.com/doc/refman/8.0/en/innodb-restrictions.html + const LENGTH_INDEX_UNICODE = 191; + public function tablesAreCorrect() { $sql = 'SHOW TABLES'; $stm = $this->bd->prepare($sql); @@ -14,6 +24,9 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { $this->prefix . 'category' => false, $this->prefix . 'feed' => false, $this->prefix . 'entry' => false, + $this->prefix . 'entrytmp' => false, + $this->prefix . 'tag' => false, + $this->prefix . 'entrytag' => false, ); foreach ($res as $value) { $tables[array_pop($value)] = true; @@ -43,7 +56,7 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { public function categoryIsCorrect() { return $this->checkTable('category', array( - 'id', 'name' + 'id', 'name', )); } @@ -51,14 +64,33 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { return $this->checkTable('feed', array( 'id', 'url', 'category', 'name', 'website', 'description', 'lastUpdate', 'priority', 'pathEntries', 'httpAuth', 'error', 'keep_history', 'ttl', 'attributes', - 'cache_nbEntries', 'cache_nbUnreads' + 'cache_nbEntries', 'cache_nbUnreads', )); } public function entryIsCorrect() { return $this->checkTable('entry', array( - 'id', 'guid', 'title', 'author', 'content_bin', 'link', 'date', 'is_read', - 'is_favorite', 'id_feed', 'tags' + 'id', 'guid', 'title', 'author', 'content_bin', 'link', 'date', 'lastSeen', 'hash', 'is_read', + 'is_favorite', 'id_feed', 'tags', + )); + } + + public function entrytmpIsCorrect() { + return $this->checkTable('entrytmp', array( + 'id', 'guid', 'title', 'author', 'content_bin', 'link', 'date', 'lastSeen', 'hash', 'is_read', + 'is_favorite', 'id_feed', 'tags', + )); + } + + public function tagIsCorrect() { + return $this->checkTable('tag', array( + 'id', 'name', 'attributes', + )); + } + + public function entrytagIsCorrect() { + return $this->checkTable('entrytag', array( + 'id_tag', 'id_entry', )); } @@ -97,28 +129,16 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { public function optimize() { $ok = true; - - $sql = 'OPTIMIZE TABLE `' . $this->prefix . 'entry`'; //MySQL - $stm = $this->bd->prepare($sql); - $ok &= $stm != false; - if ($stm) { - $ok &= $stm->execute(); - } - - $sql = 'OPTIMIZE TABLE `' . $this->prefix . 'feed`'; //MySQL - $stm = $this->bd->prepare($sql); - $ok &= $stm != false; - if ($stm) { - $ok &= $stm->execute(); + $tables = array('category', 'feed', 'entry', 'entrytmp', 'tag', 'entrytag'); + + foreach ($tables as $table) { + $sql = 'OPTIMIZE TABLE `' . $this->prefix . $table . '`'; //MySQL + $stm = $this->bd->prepare($sql); + $ok &= $stm != false; + if ($stm) { + $ok &= $stm->execute(); + } } - - $sql = 'OPTIMIZE TABLE `' . $this->prefix . 'category`'; //MySQL - $stm = $this->bd->prepare($sql); - $ok &= $stm != false; - if ($stm) { - $ok &= $stm->execute(); - } - return $ok; } } -- 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/DatabaseDAO.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 From c57aade0f22205c40792184b78f5071b5c769a8b Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Fri, 26 Oct 2018 21:05:00 +0200 Subject: Introduce a routine for minor DB maintenance (#2080) --- app/Controllers/entryController.php | 3 +-- app/Controllers/feedController.php | 6 ++++++ app/Models/DatabaseDAO.php | 4 ++++ 3 files changed, 11 insertions(+), 2 deletions(-) (limited to 'app/Models/DatabaseDAO.php') diff --git a/app/Controllers/entryController.php b/app/Controllers/entryController.php index 78ddbf085..fc0af0639 100755 --- a/app/Controllers/entryController.php +++ b/app/Controllers/entryController.php @@ -207,9 +207,8 @@ class FreshRSS_entry_Controller extends Minz_ActionController { $feedDAO->updateCachedValues(); - //Minor DB checks: $databaseDAO = FreshRSS_Factory::createDatabaseDAO(); - $databaseDAO->ensureCaseInsensitiveGuids(); //FreshRSS 1.12 + $databaseDAO->minorDbMaintenance(); invalidateHttpCache(); Minz_Request::good(_t('feedback.sub.purge_completed', $nb_total), array( diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index 2c8cdaa5c..f2b1b8960 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -481,6 +481,9 @@ class FreshRSS_feed_Controller extends Minz_ActionController { if ($entryDAO->inTransaction()) { $entryDAO->commit(); } + + $databaseDAO = FreshRSS_Factory::createDatabaseDAO(); + $databaseDAO->minorDbMaintenance(); } return array($updated_feeds, reset($feeds), $nb_new_articles); } @@ -511,6 +514,9 @@ class FreshRSS_feed_Controller extends Minz_ActionController { $entryDAO->commitNewEntries(); $feedDAO->updateCachedValues(); $entryDAO->commit(); + + $databaseDAO = FreshRSS_Factory::createDatabaseDAO(); + $databaseDAO->minorDbMaintenance(); } else { list($updated_feeds, $feed, $nb_new_articles) = self::actualizeFeed($id, $url, $force, null, false, $noCommit); } diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php index dbd328bf7..b331eccc3 100644 --- a/app/Models/DatabaseDAO.php +++ b/app/Models/DatabaseDAO.php @@ -160,4 +160,8 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { } return $ok; } + + public function minorDbMaintenance() { + $this->ensureCaseInsensitiveGuids(); + } } -- cgit v1.2.3