Last user modified (#7886)

* feat: Add user modified functionality

Closes https://github.com/FreshRSS/FreshRSS/issues/7862

Changes proposed in this pull request:

This is an implementation of the proposed feature. It allows entries to have a new field that will be updated whenever an item is marked as read/unread or bookmark/removed from bookmarks. And a new sort criteria to sort by it.

How to test the feature manually:

1. Mark items from a feed as read/unread
2. Mark items from a feed as bookmark / remove bookmark
3. Sort by the new criteria

* feat: Add sort functionality
* feat: Add sort nav button
* fix: Use correct migrations
* fix: Add internationalization
* fix: Linter errors
* chore: PR comments

* Update app/i18n/fr/index.php
Co-authored-by: Alexandre Alapetite <alexandre@alapetite.fr>

* Update app/i18n/pl/index.php
Co-authored-by: Inverle <inverle@proton.me>

* Update app/i18n/nl/index.php
Co-authored-by: Frans de Jonge <fransdejonge@gmail.com>

* make fix-all
* Fixes
* More fixes sort
* Fix wrong index
* Fix unneeded column
* Fix auto-create indexes
* Some copilot suggestions
* One more fix
Co-authored-by: Alexandre Alapetite <alexandre@alapetite.fr>

---------

Co-authored-by: Alexandre Alapetite <alexandre@alapetite.fr>
Co-authored-by: Inverle <inverle@proton.me>
Co-authored-by: Frans de Jonge <fransdejonge@gmail.com>

3 files changed, 21 insertions, 0 deletions

diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index 40553608a..5e8d092c5 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -49,6 +49,7 @@ CREATE TABLE IF NOT EXISTS `_entry` (
 	`link` VARCHAR(16383) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
 	`date` BIGINT,
 	`lastSeen` BIGINT DEFAULT 0,
+	`lastUserModified` BIGINT DEFAULT 0,	-- v1.28.0
 	`hash` BINARY(16),	-- v1.1.1
 	`is_read` BOOLEAN NOT NULL DEFAULT 0,
 	`is_favorite` BOOLEAN NOT NULL DEFAULT 0,
@@ -61,6 +62,7 @@ CREATE TABLE IF NOT EXISTS `_entry` (
 	INDEX (`is_favorite`),	-- v0.7
 	INDEX (`is_read`),	-- v0.7
 	INDEX `entry_lastSeen_index` (`lastSeen`),	-- v1.1.1
+	INDEX `entry_last_user_modified_index` (`lastUserModified`),	-- v1.28.0
 	INDEX `entry_feed_read_index` (`id_feed`,`is_read`)	-- v1.7
 ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
 ENGINE = INNODB;
@@ -109,6 +111,11 @@ CREATE TABLE IF NOT EXISTS `_entrytag` (	-- v1.12
 ENGINE = INNODB;
 SQL;
 
+$GLOBALS['ALTER_TABLE_ENTRY_LAST_USER_MODIFIED'] = <<<'SQL'
+ALTER TABLE `_entry` ADD `lastUserModified` BIGINT DEFAULT 0;	-- 1.28.0
+CREATE INDEX IF NOT EXISTS `entry_last_user_modified_index` ON `_entry` (`lastUserModified`);	-- //v1.28.0
+SQL;
+
 $GLOBALS['SQL_DROP_TABLES'] = <<<'SQL'
 DROP TABLE IF EXISTS `_entrytag`, `_tag`, `_entrytmp`, `_entry`, `_feed`, `_category`;
 SQL;
diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php
index 5ea59b828..557a42a34 100644
--- a/app/SQL/install.sql.pgsql.php
+++ b/app/SQL/install.sql.pgsql.php
@@ -44,6 +44,7 @@ CREATE TABLE IF NOT EXISTS `_entry` (
 	"link" VARCHAR(16383) NOT NULL,
 	"date" BIGINT,
 	"lastSeen" BIGINT DEFAULT 0,
+	"lastUserModified" BIGINT DEFAULT 0,
 	"hash" BYTEA,
 	"is_read" SMALLINT NOT NULL DEFAULT 0,
 	"is_favorite" SMALLINT NOT NULL DEFAULT 0,
@@ -57,6 +58,7 @@ CREATE INDEX IF NOT EXISTS `_is_favorite_index` ON `_entry` ("is_favorite");
 CREATE INDEX IF NOT EXISTS `_is_read_index` ON `_entry` ("is_read");
 CREATE INDEX IF NOT EXISTS `_entry_lastSeen_index` ON `_entry` ("lastSeen");
 CREATE INDEX IF NOT EXISTS `_entry_feed_read_index` ON `_entry` ("id_feed","is_read");	-- v1.7
+CREATE INDEX IF NOT EXISTS `_entry_last_user_modified_index` ON `_entry` ("lastUserModified");	-- v1.28.0
 
 INSERT INTO `_category` (id, name)
 	SELECT 1, 'Uncategorized'
@@ -98,6 +100,11 @@ CREATE TABLE IF NOT EXISTS `_entrytag` (
 CREATE INDEX IF NOT EXISTS `_entrytag_id_entry_index` ON `_entrytag` ("id_entry");
 SQL;
 
+$GLOBALS['ALTER_TABLE_ENTRY_LAST_USER_MODIFIED'] = <<<'SQL'
+ALTER TABLE `_entry` ADD `lastUserModified` BIGINT DEFAULT 0;	-- 1.28.0
+CREATE INDEX IF NOT EXISTS `_entry_last_user_modified_index` ON `_entry` (`lastUserModified`);
+SQL;
+
 $GLOBALS['SQL_DROP_TABLES'] = <<<'SQL'
 DROP TABLE IF EXISTS `_entrytag`, `_tag`, `_entrytmp`, `_entry`, `_feed`, `_category`;
 SQL;
diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php
index 1deb627d3..55de33f71 100644
--- a/app/SQL/install.sql.sqlite.php
+++ b/app/SQL/install.sql.sqlite.php
@@ -45,6 +45,7 @@ CREATE TABLE IF NOT EXISTS `entry` (
 	`link` VARCHAR(16383) NOT NULL,
 	`date` BIGINT,
 	`lastSeen` BIGINT DEFAULT 0,
+	`lastUserModified` BIGINT DEFAULT 0,	-- v1.28.0
 	`hash` BINARY(16),	-- v1.1.1
 	`is_read` BOOLEAN NOT NULL DEFAULT 0,
 	`is_favorite` BOOLEAN NOT NULL DEFAULT 0,
@@ -59,6 +60,7 @@ CREATE INDEX IF NOT EXISTS entry_is_favorite_index ON `entry`(`is_favorite`);
 CREATE INDEX IF NOT EXISTS entry_is_read_index ON `entry`(`is_read`);
 CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `entry`(`lastSeen`);	-- //v1.1.1
 CREATE INDEX IF NOT EXISTS entry_feed_read_index ON `entry`(`id_feed`,`is_read`);	-- v1.7
+CREATE INDEX IF NOT EXISTS entry_last_user_modified_index ON `entry` (`lastUserModified`);	-- //v1.28.0
 
 INSERT OR IGNORE INTO `category` (id, name) VALUES(1, 'Uncategorized');
 
@@ -99,6 +101,11 @@ CREATE TABLE IF NOT EXISTS `entrytag` (
 CREATE INDEX IF NOT EXISTS entrytag_id_entry_index ON `entrytag` (`id_entry`);
 SQL;
 
+$GLOBALS['ALTER_TABLE_ENTRY_LAST_USER_MODIFIED'] = <<<'SQL'
+ALTER TABLE `entry` ADD `lastUserModified` BIGINT DEFAULT 0;	-- 1.28.0
+CREATE INDEX IF NOT EXISTS entry_last_user_modified_index ON `entry` (`lastUserModified`);
+SQL;
+
 $GLOBALS['SQL_DROP_TABLES'] = <<<'SQL'
 DROP TABLE IF EXISTS `entrytag`;
 DROP TABLE IF EXISTS `tag`;