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.sqlite.php | 122 ++++++++++++++++++----------------------- 1 file changed, 54 insertions(+), 68 deletions(-) (limited to 'app/SQL/install.sql.sqlite.php') 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 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/install.sql.sqlite.php') 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 @@