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.pgsql.php | 36 ++++++++++++++++++++++++++++++------ 1 file changed, 30 insertions(+), 6 deletions(-) (limited to 'app/SQL/install.sql.pgsql.php') 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"'); -- 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.pgsql.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 @@