SQL test if minor update is needed (#6692)

* SQL test if minor update is needed
fix https://github.com/FreshRSS/FreshRSS/issues/6592

* Minor syntax

2 files changed, 81 insertions, 54 deletions

diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index 9c989339c..6c766d5c7 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -114,30 +114,46 @@ DROP TABLE IF EXISTS `_entrytag`, `_tag`, `_entrytmp`, `_entry`, `_feed`, `_cate
 SQL;
 
 $GLOBALS['SQL_UPDATE_MINOR'] = <<<'SQL'
-ALTER TABLE `_feed`
-	MODIFY COLUMN `website` TEXT CHARACTER SET latin1 COLLATE latin1_bin,
-	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 `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,
-	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;
-ALTER TABLE `_feed`
-	DROP INDEX IF EXISTS `url`, -- IF EXISTS works with MariaDB but not with MySQL, so needs PHP workaround
-	MODIFY COLUMN `url` VARCHAR(32768) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL;
+DROP PROCEDURE IF EXISTS update_minor;
+CREATE PROCEDURE update_minor()
+BEGIN
+	DECLARE up_to_date INT;
+
+	SELECT COUNT(*) INTO up_to_date FROM information_schema.COLUMNS
+		WHERE TABLE_SCHEMA = DATABASE()
+		AND TABLE_NAME = REPLACE('`_tag`', '`', '')
+		AND COLUMN_NAME = 'name'
+		AND COLUMN_TYPE = 'VARCHAR(191)';
+
+	IF up_to_date = 0 THEN
+		ALTER TABLE `_feed`
+			MODIFY COLUMN `website` TEXT CHARACTER SET latin1 COLLATE latin1_bin,
+			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 `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,
+			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;
+		ALTER TABLE `_feed`
+			DROP INDEX IF EXISTS `url`, -- IF EXISTS works with MariaDB but not with MySQL, so needs PHP workaround
+			MODIFY COLUMN `url` VARCHAR(32768) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL;
+	END IF;
+END;
+CALL update_minor();
+DROP PROCEDURE update_minor;
 SQL;
diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php
index ad14a37a0..5ea59b828 100644
--- a/app/SQL/install.sql.pgsql.php
+++ b/app/SQL/install.sql.pgsql.php
@@ -103,32 +103,43 @@ DROP TABLE IF EXISTS `_entrytag`, `_tag`, `_entrytmp`, `_entry`, `_feed`, `_cate
 SQL;
 
 $GLOBALS['SQL_UPDATE_MINOR'] = <<<'SQL'
-ALTER TABLE `_category`
-	ALTER COLUMN "name" SET DATA TYPE VARCHAR(191);
-ALTER TABLE `_feed`
-	DROP CONSTRAINT IF EXISTS `_feed_url_key`,
-	ALTER COLUMN "url" SET DATA TYPE VARCHAR(32768),
-	ALTER COLUMN "name" SET DATA TYPE VARCHAR(191),
-	ALTER COLUMN "website" SET DATA TYPE VARCHAR(32768),
-	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 "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 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);
+DO $$
+BEGIN
+	IF NOT EXISTS (
+		SELECT 1 FROM information_schema.columns
+		WHERE table_schema = 'public'
+			AND table_name = REPLACE('`_tag`', '"', '')
+			AND column_name = 'name'
+			AND character_maximum_length = 191
+	) THEN
+		ALTER TABLE `_category`
+			ALTER COLUMN "name" SET DATA TYPE VARCHAR(191);
+		ALTER TABLE `_feed`
+			DROP CONSTRAINT IF EXISTS `_feed_url_key`,
+			ALTER COLUMN "url" SET DATA TYPE VARCHAR(32768),
+			ALTER COLUMN "name" SET DATA TYPE VARCHAR(191),
+			ALTER COLUMN "website" SET DATA TYPE VARCHAR(32768),
+			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 "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 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);
+	END IF;
+END $$;
 SQL;