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/SQL/install.sql.mysql.php | 101 ++++++++++++++++++++-------------- app/SQL/install.sql.pgsql.php | 36 ++++++++++-- app/SQL/install.sql.sqlite.php | 122 ++++++++++++++++++----------------------- 3 files changed, 145 insertions(+), 114 deletions(-) (limited to 'app/SQL') diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index 747a0a6b3..eb454b1a3 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -4,7 +4,7 @@ define('SQL_CREATE_DB', 'CREATE DATABASE IF NOT EXISTS %1$s DEFAULT CHARACTER SE define('SQL_CREATE_TABLES', ' CREATE TABLE IF NOT EXISTS `%1$scategory` ( `id` SMALLINT NOT NULL AUTO_INCREMENT, -- v0.7 - `name` varchar(191) NOT NULL, + `name` VARCHAR(' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') NOT NULL, -- Max index length for Unicode is 191 characters (767 bytes) PRIMARY KEY (`id`), UNIQUE KEY (`name`) -- v0.7 ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci @@ -12,21 +12,21 @@ 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 NOT NULL, `category` SMALLINT DEFAULT 0, -- v0.7 - `name` varchar(191) NOT NULL, - `website` varchar(255) CHARACTER SET latin1, - `description` text, - `lastUpdate` int(11) DEFAULT 0, -- Until year 2038 - `priority` tinyint(2) NOT NULL DEFAULT 10, - `pathEntries` varchar(511) DEFAULT NULL, - `httpAuth` varchar(511) DEFAULT NULL, - `error` boolean DEFAULT 0, + `name` VARCHAR(' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') NOT NULL, + `website` VARCHAR(255) CHARACTER SET latin1, + `description` TEXT, + `lastUpdate` INT(11) DEFAULT 0, -- Until year 2038 + `priority` TINYNT(2) NOT NULL DEFAULT 10, + `pathEntries` VARCHAR(511) DEFAULT NULL, + `httpAuth` VARCHAR(511) DEFAULT NULL, + `error` BOOLEAN DEFAULT 0, `keep_history` MEDIUMINT NOT NULL DEFAULT -2, -- v0.7 `ttl` INT NOT NULL DEFAULT 0, -- v0.7.3 `attributes` TEXT, -- v1.11.0 - `cache_nbEntries` int DEFAULT 0, -- v0.7 - `cache_nbUnreads` int DEFAULT 0, -- v0.7 + `cache_nbEntries` INT DEFAULT 0, -- v0.7 + `cache_nbUnreads` INT DEFAULT 0, -- v0.7 PRIMARY KEY (`id`), FOREIGN KEY (`category`) REFERENCES `%1$scategory`(`id`) ON DELETE SET NULL ON UPDATE CASCADE, UNIQUE KEY (`url`), -- v0.7 @@ -37,19 +37,19 @@ CREATE TABLE IF NOT EXISTS `%1$sfeed` ( 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 - `title` varchar(255) NOT NULL, - `author` varchar(255), - `content_bin` blob, -- v0.7 - `link` varchar(1023) CHARACTER SET latin1 NOT NULL, - `date` int(11), -- Until year 2038 + `id` BIGINT NOT NULL, -- v0.7 + `guid` VARCHAR(760) CHARACTER SET latin1 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, + `date` INT(11), -- Until year 2038 `lastSeen` INT(11) DEFAULT 0, -- v1.1.1, Until year 2038 `hash` BINARY(16), -- v1.1.1 - `is_read` boolean NOT NULL DEFAULT 0, - `is_favorite` boolean NOT NULL DEFAULT 0, + `is_read` BOOLEAN NOT NULL DEFAULT 0, + `is_favorite` BOOLEAN NOT NULL DEFAULT 0, `id_feed` SMALLINT, -- v0.7 - `tags` varchar(1023), + `tags` VARCHAR(1023), PRIMARY KEY (`id`), FOREIGN KEY (`id_feed`) REFERENCES `%1$sfeed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE KEY (`id_feed`,`guid`), -- v0.7 @@ -65,19 +65,19 @@ 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, - `title` varchar(255) NOT NULL, - `author` varchar(255), - `content_bin` blob, - `link` varchar(1023) CHARACTER SET latin1 NOT NULL, - `date` int(11), + `id` BIGINT NOT NULL, + `guid` VARCHAR(760) CHARACTER SET latin1 NOT NULL, + `title` VARCHAR(255) NOT NULL, + `author` VARCHAR(255), + `content_bin` BLOB, + `link` VARCHAR(1023) CHARACTER SET latin1 NOT NULL, + `date` INT(11), `lastSeen` INT(11) DEFAULT 0, `hash` BINARY(16), - `is_read` boolean NOT NULL DEFAULT 0, - `is_favorite` boolean NOT NULL DEFAULT 0, + `is_read` BOOLEAN NOT NULL DEFAULT 0, + `is_favorite` BOOLEAN NOT NULL DEFAULT 0, `id_feed` SMALLINT, - `tags` varchar(1023), + `tags` VARCHAR(1023), PRIMARY KEY (`id`), FOREIGN KEY (`id_feed`) REFERENCES `%1$sfeed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE KEY (`id_feed`,`guid`), @@ -88,25 +88,46 @@ ENGINE = INNODB; CREATE INDEX `entry_feed_read_index` ON `%1$sentry`(`id_feed`,`is_read`); -- v1.7 Located here to be auto-added '); +define('SQL_CREATE_TABLE_TAGS', ' +CREATE TABLE IF NOT EXISTS `%1$stag` ( -- v1.12 + `id` SMALLINT NOT NULL AUTO_INCREMENT, + `name` VARCHAR(63) NOT NULL, + `attributes` TEXT, + PRIMARY KEY (`id`), + UNIQUE KEY (`name`) +) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci +ENGINE = INNODB; + +CREATE TABLE IF NOT EXISTS `%1$sentrytag` ( -- v1.12 + `id_tag` SMALLINT, + `id_entry` BIGINT, + PRIMARY KEY (`id_tag`,`id_entry`), + FOREIGN KEY (`id_tag`) REFERENCES `%1$stag`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (`id_entry`) REFERENCES `%1$sentry`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX (`id_entry`) +) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci +ENGINE = INNODB; +'); + define('SQL_INSERT_FEEDS', ' -INSERT IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) VALUES("http://freshrss.org/feeds/all.atom.xml", 1, "FreshRSS.org", "http://freshrss.org/", "FreshRSS, a free, self-hostable aggregator…", 86400); +INSERT IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) VALUES("https://freshrss.org/feeds/all.atom.xml", 1, "FreshRSS.org", "https://freshrss.org/", "FreshRSS, a free, self-hostable aggregator…", 86400); INSERT IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) VALUES("https://github.com/FreshRSS/FreshRSS/releases.atom", 1, "FreshRSS @ GitHub", "https://github.com/FreshRSS/FreshRSS/", "FreshRSS releases @ GitHub", 86400); '); -define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS `%1$sentrytmp`, `%1$sentry`, `%1$sfeed`, `%1$scategory`'); +define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS `%1$sentrytag`, `%1$stag`, `%1$sentrytmp`, `%1$sentry`, `%1$sfeed`, `%1$scategory`'); define('SQL_UPDATE_UTF8MB4', ' -ALTER DATABASE `%2$s` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; +ALTER DATABASE `%2$s` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- v1.5.0 ALTER TABLE `%1$scategory` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -UPDATE `%1$scategory` SET name=SUBSTRING(name,1,190) WHERE LENGTH(name) > 191; -ALTER TABLE `%1$scategory` MODIFY `name` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; +UPDATE `%1$scategory` SET name=SUBSTRING(name,1,' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') WHERE LENGTH(name) > ' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . '; +ALTER TABLE `%1$scategory` MODIFY `name` VARCHAR(' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; OPTIMIZE TABLE `%1$scategory`; ALTER TABLE `%1$sfeed` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -UPDATE `%1$sfeed` SET name=SUBSTRING(name,1,190) WHERE LENGTH(name) > 191; -ALTER TABLE `%1$sfeed` MODIFY `name` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; -ALTER TABLE `%1$sfeed` MODIFY `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; +UPDATE `%1$sfeed` SET name=SUBSTRING(name,1,' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') WHERE LENGTH(name) > ' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . '; +ALTER TABLE `%1$sfeed` MODIFY `name` VARCHAR(' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; +ALTER TABLE `%1$sfeed` MODIFY `description` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; OPTIMIZE TABLE `%1$sfeed`; ALTER TABLE `%1$sentry` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php index b80fbf1e7..b956ebd2f 100644 --- a/app/SQL/install.sql.pgsql.php +++ b/app/SQL/install.sql.pgsql.php @@ -10,16 +10,16 @@ $SQL_CREATE_TABLES = array( 'CREATE TABLE IF NOT EXISTS "%1$sfeed" ( "id" SERIAL PRIMARY KEY, - "url" varchar(511) UNIQUE NOT NULL, + "url" VARCHAR(511) UNIQUE NOT NULL, "category" SMALLINT DEFAULT 0, "name" VARCHAR(255) NOT NULL, "website" VARCHAR(255), - "description" text, + "description" TEXT, "lastUpdate" INT DEFAULT 0, "priority" SMALLINT NOT NULL DEFAULT 10, "pathEntries" VARCHAR(511) DEFAULT NULL, "httpAuth" VARCHAR(511) DEFAULT NULL, - "error" smallint DEFAULT 0, + "error" SMALLINT DEFAULT 0, "keep_history" INT NOT NULL DEFAULT -2, "ttl" INT NOT NULL DEFAULT 0, "attributes" TEXT, -- v1.11.0 @@ -52,7 +52,10 @@ $SQL_CREATE_TABLES = array( 'CREATE INDEX %1$sis_read_index ON "%1$sentry" ("is_read");', 'CREATE INDEX %1$sentry_lastSeen_index ON "%1$sentry" ("lastSeen");', -'INSERT INTO "%1$scategory" (id, name) SELECT 1, \'%2$s\' WHERE NOT EXISTS (SELECT id FROM "%1$scategory" WHERE id = 1) RETURNING nextval(\'%1$scategory_id_seq\');', +'INSERT INTO "%1$scategory" (id, name) + SELECT 1, \'%2$s\' + WHERE NOT EXISTS (SELECT id FROM "%1$scategory" WHERE id = 1) + RETURNING nextval(\'%1$scategory_id_seq\');', ); global $SQL_CREATE_TABLE_ENTRYTMP; @@ -79,10 +82,31 @@ $SQL_CREATE_TABLE_ENTRYTMP = array( 'CREATE INDEX %1$sentry_feed_read_index ON "%1$sentry" ("id_feed","is_read");', //v1.7 ); +global $SQL_CREATE_TABLE_TAGS; +$SQL_CREATE_TABLE_TAGS = array( +'CREATE TABLE IF NOT EXISTS "%1$stag" ( -- v1.12 + "id" SERIAL PRIMARY KEY, + "name" VARCHAR(63) UNIQUE NOT NULL, + "attributes" TEXT +);', +'CREATE TABLE IF NOT EXISTS "%1$sentrytag" ( + "id_tag" SMALLINT, + "id_entry" BIGINT, + PRIMARY KEY ("id_tag","id_entry"), + FOREIGN KEY ("id_tag") REFERENCES "%1$stag" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY ("id_entry") REFERENCES "%1$sentry" ("id") ON DELETE CASCADE ON UPDATE CASCADE +);', +'CREATE INDEX %1$sentrytag_id_entry_index ON "%1$sentrytag" ("id_entry");', +); + global $SQL_INSERT_FEEDS; $SQL_INSERT_FEEDS = array( -'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) SELECT \'http://freshrss.org/feeds/all.atom.xml\', 1, \'FreshRSS.org\', \'http://freshrss.org/\', \'FreshRSS, a free, self-hostable aggregator…\', 86400 WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'http://freshrss.org/feeds/all.atom.xml\');', -'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) SELECT \'https://github.com/FreshRSS/FreshRSS/releases.atom\', 1, \'FreshRSS @ GitHub\', \'https://github.com/FreshRSS/FreshRSS/\', \'FreshRSS releases @ GitHub\', 86400 WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'https://github.com/FreshRSS/FreshRSS/releases.atom\');', +'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) + SELECT \'https://freshrss.org/feeds/all.atom.xml\', 1, \'FreshRSS.org\', \'https://freshrss.org/\', \'FreshRSS, a free, self-hostable aggregator…\', 86400 + WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'https://freshrss.org/feeds/all.atom.xml\');', +'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) + SELECT \'https://github.com/FreshRSS/FreshRSS/releases.atom\', 1, \'FreshRSS @ GitHub\', \'https://github.com/FreshRSS/FreshRSS/\', \'FreshRSS releases @ GitHub\', 86400 + WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = \'https://github.com/FreshRSS/FreshRSS/releases.atom\');', ); define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS "%1$sentrytmp", "%1$sentry", "%1$sfeed", "%1$scategory"'); diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index cbfb719e5..1babe7d86 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -3,27 +3,27 @@ global $SQL_CREATE_TABLES; $SQL_CREATE_TABLES = array( 'CREATE TABLE IF NOT EXISTS `category` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, - `name` varchar(255) NOT NULL, + `name` VARCHAR(255) NOT NULL, UNIQUE (`name`) );', 'CREATE TABLE IF NOT EXISTS `feed` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, - `url` varchar(511) NOT NULL, + `url` VARCHAR(511) NOT NULL, `category` SMALLINT DEFAULT 0, - `name` varchar(255) NOT NULL, - `website` varchar(255), - `description` text, - `lastUpdate` int(11) DEFAULT 0, -- Until year 2038 - `priority` tinyint(2) NOT NULL DEFAULT 10, - `pathEntries` varchar(511) DEFAULT NULL, - `httpAuth` varchar(511) DEFAULT NULL, - `error` boolean DEFAULT 0, + `name` VARCHAR(255) NOT NULL, + `website` VARCHAR(255), + `description` TEXT, + `lastUpdate` INT(11) DEFAULT 0, -- Until year 2038 + `priority` TINYINT(2) NOT NULL DEFAULT 10, + `pathEntries` VARCHAR(511) DEFAULT NULL, + `httpAuth` VARCHAR(511) DEFAULT NULL, + `error` BOOLEAN DEFAULT 0, `keep_history` MEDIUMINT NOT NULL DEFAULT -2, `ttl` INT NOT NULL DEFAULT 0, `attributes` TEXT, -- v1.11.0 - `cache_nbEntries` int DEFAULT 0, - `cache_nbUnreads` int DEFAULT 0, + `cache_nbEntries` INT DEFAULT 0, + `cache_nbUnreads` INT DEFAULT 0, FOREIGN KEY (`category`) REFERENCES `category`(`id`) ON DELETE SET NULL ON UPDATE CASCADE, UNIQUE (`url`) );', @@ -32,19 +32,19 @@ $SQL_CREATE_TABLES = array( 'CREATE INDEX IF NOT EXISTS feed_keep_history_index ON `feed`(`keep_history`);', 'CREATE TABLE IF NOT EXISTS `entry` ( - `id` bigint NOT NULL, - `guid` varchar(760) NOT NULL, - `title` varchar(255) NOT NULL, - `author` varchar(255), - `content` text, - `link` varchar(1023) NOT NULL, - `date` int(11), -- Until year 2038 + `id` BIGINT NOT NULL, + `guid` VARCHAR(760) NOT NULL, + `title` VARCHAR(255) NOT NULL, + `author` VARCHAR(255), + `content` TEXT, + `link` VARCHAR(1023) 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 - `is_read` boolean NOT NULL DEFAULT 0, - `is_favorite` boolean NOT NULL DEFAULT 0, + `is_read` BOOLEAN NOT NULL DEFAULT 0, + `is_favorite` BOOLEAN NOT NULL DEFAULT 0, `id_feed` SMALLINT, - `tags` varchar(1023), + `tags` VARCHAR(1023), PRIMARY KEY (`id`), FOREIGN KEY (`id_feed`) REFERENCES `feed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE (`id_feed`,`guid`) @@ -59,19 +59,19 @@ $SQL_CREATE_TABLES = array( global $SQL_CREATE_TABLE_ENTRYTMP; $SQL_CREATE_TABLE_ENTRYTMP = array( 'CREATE TABLE IF NOT EXISTS `entrytmp` ( -- v1.7 - `id` bigint NOT NULL, - `guid` varchar(760) NOT NULL, - `title` varchar(255) NOT NULL, - `author` varchar(255), - `content` text, - `link` varchar(1023) NOT NULL, - `date` int(11), + `id` BIGINT NOT NULL, + `guid` VARCHAR(760) NOT NULL, + `title` VARCHAR(255) NOT NULL, + `author` VARCHAR(255), + `content` TEXT, + `link` VARCHAR(1023) NOT NULL, + `date` INT(11), `lastSeen` INT(11) DEFAULT 0, `hash` BINARY(16), - `is_read` boolean NOT NULL DEFAULT 0, - `is_favorite` boolean NOT NULL DEFAULT 0, + `is_read` BOOLEAN NOT NULL DEFAULT 0, + `is_favorite` BOOLEAN NOT NULL DEFAULT 0, `id_feed` SMALLINT, - `tags` varchar(1023), + `tags` VARCHAR(1023), PRIMARY KEY (`id`), FOREIGN KEY (`id_feed`) REFERENCES `feed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE (`id_feed`,`guid`) @@ -81,44 +81,30 @@ $SQL_CREATE_TABLE_ENTRYTMP = array( 'CREATE INDEX IF NOT EXISTS `entry_feed_read_index` ON `entry`(`id_feed`,`is_read`);', //v1.7 ); +global $SQL_CREATE_TABLE_TAGS; +$SQL_CREATE_TABLE_TAGS = array( +'CREATE TABLE IF NOT EXISTS `tag` ( -- v1.12 + `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, + `name` VARCHAR(63) NOT NULL, + `attributes` TEXT, + UNIQUE (`name`) +);', +'CREATE TABLE IF NOT EXISTS `entrytag` ( + `id_tag` SMALLINT, + `id_entry` SMALLINT, + PRIMARY KEY (`id_tag`,`id_entry`), + FOREIGN KEY (`id_tag`) REFERENCES `tag` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (`id_entry`) REFERENCES `entry` (`id`) ON DELETE CASCADE ON UPDATE CASCADE +);', +'CREATE INDEX entrytag_id_entry_index ON `entrytag` (`id_entry`);', +); + global $SQL_INSERT_FEEDS; $SQL_INSERT_FEEDS = array( -'INSERT OR IGNORE INTO `feed` - ( - url, - category, - name, - website, - description, - ttl - ) - VALUES - ( - "http://freshrss.org/feeds/all.atom.xml", - 1, - "FreshRSS.org", - "http://freshrss.org/", - "FreshRSS, a free, self-hostable aggregator…", - 86400 - );', -'INSERT OR IGNORE INTO `feed` - ( - url, - category, - name, - website, - description, - ttl - ) - VALUES - ( - "https://github.com/FreshRSS/FreshRSS/releases.atom", - 1, - "FreshRSS releases", - "https://github.com/FreshRSS/FreshRSS/", - "FreshRSS releases @ GitHub", - 86400 - );', +'INSERT OR IGNORE INTO `feed` (url, category, name, website, description, ttl) + VALUES ("https://freshrss.org/feeds/all.atom.xml", 1, "FreshRSS.org", "https://freshrss.org/", "FreshRSS, a free, self-hostable aggregator…", 86400);', +'INSERT OR IGNORE INTO `feed` (url, category, name, website, description, ttl) + VALUES ("https://github.com/FreshRSS/FreshRSS/releases.atom", 1, "FreshRSS releases", "https://github.com/FreshRSS/FreshRSS/", "FreshRSS releases @ GitHub", 86400);', ); define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS `entrytmp`, `entry`, `feed`, `category`'); -- cgit v1.2.3 From c8b54ae807f583723748b5a8cebf9925fb288f9d Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 14 Oct 2018 13:48:59 +0200 Subject: Fix MySQL create table feeds (#2047) https://github.com/FreshRSS/FreshRSS/issues/2042 --- app/Controllers/userController.php | 2 +- app/Models/EntryDAO.php | 2 +- app/SQL/install.sql.mysql.php | 2 +- 3 files changed, 3 insertions(+), 3 deletions(-) (limited to 'app/SQL') diff --git a/app/Controllers/userController.php b/app/Controllers/userController.php index 75a4303d6..2f066e25f 100644 --- a/app/Controllers/userController.php +++ b/app/Controllers/userController.php @@ -166,7 +166,7 @@ class FreshRSS_user_Controller extends Minz_ActionController { $entryDAO = FreshRSS_Factory::createEntryDao($this->view->current_user); $this->view->nb_articles = $entryDAO->count(); - $databaseDAO = FreshRSS_Factory::createDatabaseDAO(); + $databaseDAO = FreshRSS_Factory::createDatabaseDAO($this->view->current_user); $this->view->size_user = $databaseDAO->size(); } } diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index a86de67d6..a01c2227b 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -992,7 +992,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $stm = $this->bd->prepare($sql); $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); - return $res[0]; + return isset($res[0]) ? $res[0] : 0; } public function countNotRead($minPriority = null) { $sql = 'SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e'; diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index eb454b1a3..222f7e8a7 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -18,7 +18,7 @@ CREATE TABLE IF NOT EXISTS `%1$sfeed` ( `website` VARCHAR(255) CHARACTER SET latin1, `description` TEXT, `lastUpdate` INT(11) DEFAULT 0, -- Until year 2038 - `priority` TINYNT(2) NOT NULL DEFAULT 10, + `priority` TINYINT(2) NOT NULL DEFAULT 10, `pathEntries` VARCHAR(511) DEFAULT NULL, `httpAuth` VARCHAR(511) DEFAULT NULL, `error` BOOLEAN DEFAULT 0, -- cgit v1.2.3 From 6a686daafa526d4b0a247d6db407edca540e5083 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Wed, 24 Oct 2018 13:07:39 +0200 Subject: Allow dot in username (#2062) * Allow dot in username https://github.com/FreshRSS/FreshRSS/issues/2061 * Missing quotes for special chars in PostgreSQL --- app/Controllers/userController.php | 2 +- app/Models/CategoryDAO.php | 2 +- app/SQL/install.sql.mysql.php | 2 +- app/SQL/install.sql.pgsql.php | 24 ++++++++++++------------ app/SQL/install.sql.sqlite.php | 2 +- 5 files changed, 16 insertions(+), 16 deletions(-) (limited to 'app/SQL') diff --git a/app/Controllers/userController.php b/app/Controllers/userController.php index 95859c92c..2338c8b2a 100644 --- a/app/Controllers/userController.php +++ b/app/Controllers/userController.php @@ -38,7 +38,7 @@ class FreshRSS_user_Controller extends Minz_ActionController { * The username is also used as folder name, file name, and part of SQL table name. * '_' is a reserved internal username. */ - const USERNAME_PATTERN = '[0-9a-zA-Z_]{2,38}|[0-9a-zA-Z]'; + const USERNAME_PATTERN = '[0-9a-zA-Z_][0-9a-zA-Z_.]{1,38}|[0-9a-zA-Z]'; public static function checkUsername($username) { return preg_match('/^' . self::USERNAME_PATTERN . '$/', $username) === 1; diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php index 0519fc4c7..ba7eb765e 100644 --- a/app/Models/CategoryDAO.php +++ b/app/Models/CategoryDAO.php @@ -158,7 +158,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable $sql = 'INSERT INTO `' . $this->prefix . 'category`(id, name) VALUES(?, ?)'; if (parent::$sharedDbType === 'pgsql') { //Force call to nextval() - $sql .= " RETURNING nextval('" . $this->prefix . "category_id_seq');"; + $sql .= ' RETURNING nextval(\'"' . $this->prefix . 'category_id_seq"\');'; } $stm = $this->bd->prepare($sql); diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index 222f7e8a7..1fc7e44d3 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -1,5 +1,5 @@ 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/SQL') 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