Rework SQL field length (#5788)

* SQL auto-update field length
Follow-up of https://github.com/FreshRSS/FreshRSS/pull/5756
Only for PostgreSQL and MySQL / MariaDB. Not possible for SQLite

* Account for MySQL 65535
Partial revert of https://github.com/FreshRSS/FreshRSS/pull/5756
> The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead.

3 files changed, 74 insertions, 40 deletions

diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index b79450943..598349167 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -26,8 +26,8 @@ CREATE TABLE IF NOT EXISTS `_feed` (
 	`description` TEXT,
 	`lastUpdate` BIGINT DEFAULT 0,
 	`priority` TINYINT(2) NOT NULL DEFAULT 10,
-	`pathEntries` VARCHAR(65535) DEFAULT NULL,
-	`httpAuth` VARCHAR(1024) DEFAULT NULL,
+	`pathEntries` VARCHAR(4096) DEFAULT NULL,
+	`httpAuth` VARCHAR(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
 	`error` BOOLEAN DEFAULT 0,
 	`ttl` INT NOT NULL DEFAULT 0,	-- v0.7.3
 	`attributes` TEXT,	-- v1.11.0
@@ -43,17 +43,17 @@ ENGINE = INNODB;
 CREATE TABLE IF NOT EXISTS `_entry` (
 	`id` BIGINT NOT NULL,	-- v0.7
 	`guid` VARCHAR(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,	-- Maximum for UNIQUE is 767B
-	`title` VARCHAR(65535) NOT NULL,
-	`author` VARCHAR(65535),
+	`title` VARCHAR(8192) NOT NULL,
+	`author` VARCHAR(1024),
 	`content_bin` MEDIUMBLOB,	-- v0.7
-	`link` VARCHAR(32768) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
+	`link` VARCHAR(16383) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
 	`date` BIGINT,
 	`lastSeen` BIGINT DEFAULT 0,
 	`hash` BINARY(16),	-- v1.1.1
 	`is_read` BOOLEAN NOT NULL DEFAULT 0,
 	`is_favorite` BOOLEAN NOT NULL DEFAULT 0,
 	`id_feed` INT,	-- 1.20.0
-	`tags` VARCHAR(65535),
+	`tags` VARCHAR(2048),
 	`attributes` TEXT,	-- v1.20.0
 	PRIMARY KEY (`id`),
 	FOREIGN KEY (`id_feed`) REFERENCES `_feed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
@@ -70,17 +70,17 @@ INSERT IGNORE INTO `_category` (id, name) VALUES(1, "Uncategorized");
 CREATE TABLE IF NOT EXISTS `_entrytmp` (	-- v1.7
 	`id` BIGINT NOT NULL,
 	`guid` VARCHAR(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
-	`title` VARCHAR(65535) NOT NULL,
-	`author` VARCHAR(65535),
+	`title` VARCHAR(8192) NOT NULL,
+	`author` VARCHAR(1024),
 	`content_bin` MEDIUMBLOB,
-	`link` VARCHAR(32768) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
+	`link` VARCHAR(16383) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
 	`date` BIGINT,
 	`lastSeen` BIGINT DEFAULT 0,
 	`hash` BINARY(16),
 	`is_read` BOOLEAN NOT NULL DEFAULT 0,
 	`is_favorite` BOOLEAN NOT NULL DEFAULT 0,
 	`id_feed` INT,	-- 1.20.0
-	`tags` VARCHAR(65535),
+	`tags` VARCHAR(2048),
 	`attributes` TEXT,	-- v1.20.0
 	PRIMARY KEY (`id`),
 	FOREIGN KEY (`id_feed`) REFERENCES `_feed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
@@ -113,13 +113,27 @@ $GLOBALS['SQL_DROP_TABLES'] = <<<'SQL'
 DROP TABLE IF EXISTS `_entrytag`, `_tag`, `_entrytmp`, `_entry`, `_feed`, `_category`;
 SQL;
 
-$GLOBALS['SQL_UPDATE_YEAR_2038'] = <<<'SQL'
-ALTER TABLE `_entry`	-- v1.23
+$GLOBALS['SQL_UPDATE_MINOR'] = <<<'SQL'
+ALTER TABLE `_feed`
+	MODIFY COLUMN `lastUpdate` BIGINT DEFAULT 0,
+	MODIFY COLUMN `pathEntries` VARCHAR(4096),
+	MODIFY COLUMN `httpAuth` VARCHAR(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL;
+ALTER TABLE `_entry`
 	MODIFY COLUMN `date` BIGINT,
-	MODIFY COLUMN `lastSeen` BIGINT DEFAULT 0;
+	MODIFY COLUMN `lastSeen` BIGINT DEFAULT 0,
+	MODIFY COLUMN `guid` VARCHAR(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
+	MODIFY COLUMN `title` VARCHAR(8192) NOT NULL,
+	MODIFY COLUMN `author` VARCHAR(1024),
+	MODIFY COLUMN `link` VARCHAR(16383) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
+	MODIFY COLUMN `tags` VARCHAR(2048);
 ALTER TABLE `_entrytmp`
 	MODIFY COLUMN `date` BIGINT,
-	MODIFY COLUMN `lastSeen` BIGINT DEFAULT 0;
-ALTER TABLE `_feed`
-	MODIFY COLUMN `lastUpdate` BIGINT DEFAULT 0;
+	MODIFY COLUMN `lastSeen` BIGINT DEFAULT 0,
+	MODIFY COLUMN `guid` VARCHAR(767) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
+	MODIFY COLUMN `title` VARCHAR(8192) NOT NULL,
+	MODIFY COLUMN `author` VARCHAR(1024),
+	MODIFY COLUMN `link` VARCHAR(16383) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
+	MODIFY COLUMN `tags` VARCHAR(2048);
+ALTER TABLE `_tag`
+	MODIFY COLUMN `name` VARCHAR(191) NOT NULL;
 SQL;
diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php
index e08d0e1ef..038e8e070 100644
--- a/app/SQL/install.sql.pgsql.php
+++ b/app/SQL/install.sql.pgsql.php
@@ -23,7 +23,7 @@ CREATE TABLE IF NOT EXISTS `_feed` (
 	"description" TEXT,
 	"lastUpdate" BIGINT DEFAULT 0,
 	"priority" SMALLINT NOT NULL DEFAULT 10,
-	"pathEntries" VARCHAR(65535) DEFAULT NULL,
+	"pathEntries" VARCHAR(4096) DEFAULT NULL,
 	"httpAuth" VARCHAR(1024) DEFAULT NULL,
 	"error" SMALLINT DEFAULT 0,
 	"ttl" INT NOT NULL DEFAULT 0,
@@ -38,17 +38,17 @@ CREATE INDEX IF NOT EXISTS `_priority_index` ON `_feed` ("priority");
 CREATE TABLE IF NOT EXISTS `_entry` (
 	"id" BIGINT NOT NULL PRIMARY KEY,
 	"guid" VARCHAR(767) NOT NULL,
-	"title" VARCHAR(65535) NOT NULL,
-	"author" VARCHAR(65535),
+	"title" VARCHAR(8192) NOT NULL,
+	"author" VARCHAR(1024),
 	"content" TEXT,
-	"link" VARCHAR(32768) NOT NULL,
+	"link" VARCHAR(16383) NOT NULL,
 	"date" BIGINT,
 	"lastSeen" BIGINT DEFAULT 0,
 	"hash" BYTEA,
 	"is_read" SMALLINT NOT NULL DEFAULT 0,
 	"is_favorite" SMALLINT NOT NULL DEFAULT 0,
 	"id_feed" INT,	-- 1.20.0
-	"tags" VARCHAR(65535),
+	"tags" VARCHAR(2048),
 	"attributes" TEXT,	-- v1.20.0
 	FOREIGN KEY ("id_feed") REFERENCES `_feed` ("id") ON DELETE CASCADE ON UPDATE CASCADE,
 	UNIQUE ("id_feed","guid")
@@ -66,17 +66,17 @@ INSERT INTO `_category` (id, name)
 CREATE TABLE IF NOT EXISTS `_entrytmp` (	-- v1.7
 	"id" BIGINT NOT NULL PRIMARY KEY,
 	"guid" VARCHAR(767) NOT NULL,
-	"title" VARCHAR(65535) NOT NULL,
-	"author" VARCHAR(65535),
+	"title" VARCHAR(8192) NOT NULL,
+	"author" VARCHAR(1024),
 	"content" TEXT,
-	"link" VARCHAR(32768) NOT NULL,
+	"link" VARCHAR(16383) NOT NULL,
 	"date" BIGINT,
 	"lastSeen" BIGINT DEFAULT 0,
 	"hash" BYTEA,
 	"is_read" SMALLINT NOT NULL DEFAULT 0,
 	"is_favorite" SMALLINT NOT NULL DEFAULT 0,
 	"id_feed" INT,	-- 1.20.0
-	"tags" VARCHAR(65535),
+	"tags" VARCHAR(2048),
 	"attributes" TEXT,	-- v1.20.0
 	FOREIGN KEY ("id_feed") REFERENCES `_feed` ("id") ON DELETE CASCADE ON UPDATE CASCADE,
 	UNIQUE ("id_feed","guid")
@@ -102,13 +102,30 @@ $GLOBALS['SQL_DROP_TABLES'] = <<<'SQL'
 DROP TABLE IF EXISTS `_entrytag`, `_tag`, `_entrytmp`, `_entry`, `_feed`, `_category`;
 SQL;
 
-$GLOBALS['SQL_UPDATE_YEAR_2038'] = <<<'SQL'
-ALTER TABLE `_entry`	-- v1.23
+$GLOBALS['SQL_UPDATE_MINOR'] = <<<'SQL'
+ALTER TABLE `_category`
+	ALTER COLUMN "name" SET DATA TYPE VARCHAR(191);
+ALTER TABLE `_feed`
+	ALTER COLUMN "name" SET DATA TYPE VARCHAR(191),
+	ALTER COLUMN "lastUpdate" SET DATA TYPE BIGINT,
+	ALTER COLUMN "pathEntries" SET DATA TYPE VARCHAR(4096),
+	ALTER COLUMN "httpAuth" SET DATA TYPE VARCHAR(1024);
+ALTER TABLE `_entry`
 	ALTER COLUMN "date" SET DATA TYPE BIGINT,
-	ALTER COLUMN "lastSeen" SET DATA TYPE BIGINT;
+	ALTER COLUMN "lastSeen" SET DATA TYPE BIGINT,
+	ALTER COLUMN "guid" SET DATA TYPE VARCHAR(767),
+	ALTER COLUMN "title" SET DATA TYPE VARCHAR(8192),
+	ALTER COLUMN "author" SET DATA TYPE VARCHAR(1024),
+	ALTER COLUMN "link" SET DATA TYPE VARCHAR(16383),
+	ALTER COLUMN "tags" SET DATA TYPE VARCHAR(2048);
 ALTER TABLE `_entrytmp`
 	ALTER COLUMN "date" SET DATA TYPE BIGINT,
-	ALTER COLUMN "lastSeen" SET DATA TYPE BIGINT;
-ALTER TABLE `_feed`
-	ALTER COLUMN "lastUpdate" SET DATA TYPE BIGINT;
+	ALTER COLUMN "lastSeen" SET DATA TYPE BIGINT,
+	ALTER COLUMN "guid" SET DATA TYPE VARCHAR(767),
+	ALTER COLUMN "title" SET DATA TYPE VARCHAR(8192),
+	ALTER COLUMN "author" SET DATA TYPE VARCHAR(1024),
+	ALTER COLUMN "link" SET DATA TYPE VARCHAR(16383),
+	ALTER COLUMN "tags" SET DATA TYPE VARCHAR(2048);
+ALTER TABLE `_tag`
+	ALTER COLUMN "name" SET DATA TYPE VARCHAR(191);
 SQL;
diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php
index 2796b63e9..e0c8b506e 100644
--- a/app/SQL/install.sql.sqlite.php
+++ b/app/SQL/install.sql.sqlite.php
@@ -24,7 +24,7 @@ CREATE TABLE IF NOT EXISTS `feed` (
 	`description` TEXT,
 	`lastUpdate` BIGINT DEFAULT 0,
 	`priority` TINYINT(2) NOT NULL DEFAULT 10,
-	`pathEntries` VARCHAR(65535) DEFAULT NULL,
+	`pathEntries` VARCHAR(4096) DEFAULT NULL,
 	`httpAuth` VARCHAR(1024) DEFAULT NULL,
 	`error` BOOLEAN DEFAULT 0,
 	`ttl` INT NOT NULL DEFAULT 0,
@@ -39,17 +39,17 @@ CREATE INDEX IF NOT EXISTS feed_priority_index ON `feed`(`priority`);
 CREATE TABLE IF NOT EXISTS `entry` (
 	`id` BIGINT NOT NULL,
 	`guid` VARCHAR(767) NOT NULL,
-	`title` VARCHAR(65535) NOT NULL,
-	`author` VARCHAR(65535),
+	`title` VARCHAR(8192) NOT NULL,
+	`author` VARCHAR(1024),
 	`content` TEXT,
-	`link` VARCHAR(32768) NOT NULL,
+	`link` VARCHAR(16383) NOT NULL,
 	`date` BIGINT,
 	`lastSeen` BIGINT DEFAULT 0,
 	`hash` BINARY(16),	-- v1.1.1
 	`is_read` BOOLEAN NOT NULL DEFAULT 0,
 	`is_favorite` BOOLEAN NOT NULL DEFAULT 0,
 	`id_feed` INTEGER,	-- 1.20.0
-	`tags` VARCHAR(65535),
+	`tags` VARCHAR(2048),
 	`attributes` TEXT,	-- v1.20.0
 	PRIMARY KEY (`id`),
 	FOREIGN KEY (`id_feed`) REFERENCES `feed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
@@ -65,17 +65,17 @@ INSERT OR IGNORE INTO `category` (id, name) VALUES(1, "Uncategorized");
 CREATE TABLE IF NOT EXISTS `entrytmp` (	-- v1.7
 	`id` BIGINT NOT NULL,
 	`guid` VARCHAR(767) NOT NULL,
-	`title` VARCHAR(65535) NOT NULL,
-	`author` VARCHAR(65535),
+	`title` VARCHAR(8192) NOT NULL,
+	`author` VARCHAR(1024),
 	`content` TEXT,
-	`link` VARCHAR(32768) NOT NULL,
+	`link` VARCHAR(16383) NOT NULL,
 	`date` BIGINT,
 	`lastSeen` BIGINT DEFAULT 0,
 	`hash` BINARY(16),
 	`is_read` BOOLEAN NOT NULL DEFAULT 0,
 	`is_favorite` BOOLEAN NOT NULL DEFAULT 0,
 	`id_feed` INTEGER,	-- 1.20.0
-	`tags` VARCHAR(65535),
+	`tags` VARCHAR(2048),
 	`attributes` TEXT,	-- v1.20.0
 	PRIMARY KEY (`id`),
 	FOREIGN KEY (`id_feed`) REFERENCES `feed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
@@ -107,3 +107,6 @@ DROP TABLE IF EXISTS `entry`;
 DROP TABLE IF EXISTS `feed`;
 DROP TABLE IF EXISTS `category`;
 SQL;
+
+$GLOBALS['SQL_UPDATE_MINOR'] = <<<'SQL'
+SQL;