Auto-update 5038 (#6279)

* Auto-update 5038
https://github.com/FreshRSS/FreshRSS/pull/5038

* PostgreSQL

* Draft for MySQL

* More draft MySQL

* Finalise

* A bit more robust

3 files changed, 30 insertions, 2 deletions

diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php
index ec5500d3e..d0938ca62 100644
--- a/app/Models/DatabaseDAO.php
+++ b/app/Models/DatabaseDAO.php
@@ -232,8 +232,29 @@ SQL;
 		$catDAO->resetDefaultCategoryName();
 
 		include_once(APP_PATH . '/SQL/install.sql.' . $this->pdo->dbType() . '.php');
-		if (!empty($GLOBALS['SQL_UPDATE_MINOR']) && $this->pdo->exec($GLOBALS['SQL_UPDATE_MINOR']) === false) {
-			Minz_Log::error('SQL error ' . __METHOD__ . json_encode($this->pdo->errorInfo()));
+		if (!empty($GLOBALS['SQL_UPDATE_MINOR'])) {
+			$sql = $GLOBALS['SQL_UPDATE_MINOR'];
+			$isMariaDB = false;
+
+			if ($this->pdo->dbType() === 'mysql') {
+				$dbVersion = $this->fetchValue('SELECT version()') ?? '';
+				$isMariaDB = stripos($dbVersion, 'MariaDB') !== false;	// MariaDB includes its name in version, but not MySQL
+				if (!$isMariaDB) {
+					// MySQL does not support `DROP INDEX IF EXISTS` yet https://dev.mysql.com/doc/refman/8.3/en/drop-index.html
+					// but MariaDB does https://mariadb.com/kb/en/drop-index/
+					$sql = str_replace('DROP INDEX IF EXISTS', 'DROP INDEX', $sql);
+				}
+			}
+
+			if ($this->pdo->exec($sql) === false) {
+				$info = $this->pdo->errorInfo();
+				if ($this->pdo->dbType() === 'mysql' &&
+					!$isMariaDB && !empty($info[2]) && (stripos($info[2], "Can't DROP ") !== false)) {
+					// Too bad for MySQL, but ignore error
+					return;
+				}
+				Minz_Log::error('SQL error ' . __METHOD__ . json_encode($this->pdo->errorInfo()));
+			}
 		}
 	}
 
diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index 598349167..9c989339c 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -115,6 +115,7 @@ 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;
@@ -136,4 +137,7 @@ ALTER TABLE `_entrytmp`
 	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;
 SQL;
diff --git a/app/SQL/install.sql.pgsql.php b/app/SQL/install.sql.pgsql.php
index 038e8e070..ad14a37a0 100644
--- a/app/SQL/install.sql.pgsql.php
+++ b/app/SQL/install.sql.pgsql.php
@@ -106,7 +106,10 @@ $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);