Stable IDs during SQL import (#7988)

* Stable IDs during SQL import
Follow-up of https://github.com/FreshRSS/FreshRSS/pull/7949
Make sure that the original category IDs, feed IDs, and label IDs are kept identical during an SQL import.
Avoid breaking everything referring to categories, feeds, labels by their IDs such as searches and third-party extensions.

* Fix export of default category

12 files changed, 158 insertions, 55 deletions

diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php
index 49d461dd3..4c88e6b65 100644
--- a/app/Models/CategoryDAO.php
+++ b/app/Models/CategoryDAO.php
@@ -5,6 +5,10 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo {
 
 	public const DEFAULTCATEGORYID = 1;
 
+	public function sqlResetSequence(): bool {
+		return true;	// Nothing to do for MySQL
+	}
+
 	public function resetDefaultCategoryName(): bool {
 		//FreshRSS 1.15.1
 		$stm = $this->pdo->prepare('UPDATE `_category` SET name = :name WHERE id = :id');
@@ -101,15 +105,23 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo {
 	}
 
 	/**
-	 * @param array{name:string,id?:int,kind?:int,lastUpdate?:int,error?:int|bool,attributes?:string|array<string,mixed>} $valuesTmp
+	 * @param array{id?:int,name:string,kind?:int,lastUpdate?:int,error?:int|bool,attributes?:string|array<string,mixed>} $valuesTmp
 	 */
 	public function addCategory(array $valuesTmp): int|false {
-		// TRIM() to provide a type hint as text
+		if (empty($valuesTmp['id'])) {	// Auto-generated ID
+			$sql = <<<'SQL'
+INSERT INTO `_category`(name, kind, attributes)
+SELECT * FROM (SELECT :name1 AS name, 1*:kind AS kind, :attributes AS attributes) c2
+SQL;
+		} else {
+			$sql = <<<'SQL'
+INSERT INTO `_category`(id, name, kind, attributes)
+SELECT * FROM (SELECT 1*:id AS id, :name1 AS name, 1*:kind AS kind, :attributes AS attributes) c2
+SQL;
+		}
 		// No tag of the same name
-		$sql = <<<'SQL'
-INSERT INTO `_category`(kind, name, attributes)
-SELECT * FROM (SELECT ABS(?) AS kind, TRIM(?) AS name, TRIM(?) AS attributes) c2
-WHERE NOT EXISTS (SELECT 1 FROM `_tag` WHERE name = TRIM(?))
+		$sql .= "\n" . <<<'SQL'
+WHERE NOT EXISTS (SELECT 1 FROM `_tag` WHERE name = :name2)
 SQL;
 		$stm = $this->pdo->prepare($sql);
 
@@ -117,16 +129,25 @@ SQL;
 		if (!isset($valuesTmp['attributes'])) {
 			$valuesTmp['attributes'] = [];
 		}
-		$values = [
-			$valuesTmp['kind'] ?? FreshRSS_Category::KIND_NORMAL,
-			$valuesTmp['name'],
-			is_string($valuesTmp['attributes']) ? $valuesTmp['attributes'] : json_encode($valuesTmp['attributes'], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE),
-			$valuesTmp['name'],
-		];
-
-		if ($stm !== false && $stm->execute($values) && $stm->rowCount() > 0) {
-			$catId = $this->pdo->lastInsertId('`_category_id_seq`');
-			return $catId === false ? false : (int)$catId;
+		if ($stm !== false) {
+			if (!empty($valuesTmp['id'])) {
+				$stm->bindValue(':id', $valuesTmp['id'], PDO::PARAM_INT);
+			}
+			$stm->bindValue(':name1', $valuesTmp['name'], PDO::PARAM_STR);
+			$stm->bindValue(':kind', $valuesTmp['kind'] ?? FreshRSS_Category::KIND_NORMAL, PDO::PARAM_INT);
+			$attributes = is_string($valuesTmp['attributes']) ? $valuesTmp['attributes'] :
+				json_encode($valuesTmp['attributes'], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
+			$stm->bindValue(':attributes', $attributes, PDO::PARAM_STR);
+			$stm->bindValue(':name2', $valuesTmp['name'], PDO::PARAM_STR);
+		}
+		if ($stm !== false && $stm->execute() && $stm->rowCount() > 0) {
+			if (empty($valuesTmp['id'])) {
+				// Auto-generated ID
+				$catId = $this->pdo->lastInsertId('`_category_id_seq`');
+				return $catId === false ? false : (int)$catId;
+			}
+			$this->sqlResetSequence();
+			return $valuesTmp['id'];
 		} else {
 			$info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
 			/** @var array{0:string,1:int,2:string} $info */
@@ -207,9 +228,6 @@ SQL;
 	}
 
 	public function deleteCategory(int $id): int|false {
-		if ($id <= self::DEFAULTCATEGORYID) {
-			return false;
-		}
 		$sql = 'DELETE FROM `_category` WHERE id=:id';
 		$stm = $this->pdo->prepare($sql);
 		if ($stm !== false && $stm->bindParam(':id', $id, PDO::PARAM_INT) && $stm->execute()) {
@@ -355,10 +373,6 @@ SQL;
 			$cat = new FreshRSS_Category(_t('gen.short.default_category'), self::DEFAULTCATEGORYID);
 
 			$sql = 'INSERT INTO `_category`(id, name) VALUES(?, ?)';
-			if ($this->pdo->dbType() === 'pgsql') {
-				//Force call to nextval()
-				$sql .= " RETURNING nextval('`_category_id_seq`');";
-			}
 			$stm = $this->pdo->prepare($sql);
 
 			$values = [
@@ -368,6 +382,7 @@ SQL;
 
 			if ($stm !== false && $stm->execute($values)) {
 				$catId = $this->pdo->lastInsertId('`_category_id_seq`');
+				$this->sqlResetSequence();
 				return $catId === false ? false : (int)$catId;
 			} else {
 				$info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
diff --git a/app/Models/CategoryDAOPGSQL.php b/app/Models/CategoryDAOPGSQL.php
new file mode 100644
index 000000000..c2c6be66d
--- /dev/null
+++ b/app/Models/CategoryDAOPGSQL.php
@@ -0,0 +1,13 @@
+<?php
+declare(strict_types=1);
+
+final class FreshRSS_CategoryDAOPGSQL extends FreshRSS_CategoryDAO {
+
+	#[\Override]
+	public function sqlResetSequence(): bool {
+		$sql = <<<'SQL'
+SELECT setval('`_category_id_seq`', COALESCE(MAX(id), 0) + 1, false) FROM `_category`
+SQL;
+		return $this->pdo->exec($sql) !== false;
+	}
+}
diff --git a/app/Models/CategoryDAOSQLite.php b/app/Models/CategoryDAOSQLite.php
index f4db76299..ee59d0754 100644
--- a/app/Models/CategoryDAOSQLite.php
+++ b/app/Models/CategoryDAOSQLite.php
@@ -3,6 +3,11 @@ declare(strict_types=1);
 
 class FreshRSS_CategoryDAOSQLite extends FreshRSS_CategoryDAO {
 
+	#[\Override]
+	public function sqlResetSequence(): bool {
+		return true;	// Nothing to do for SQLite
+	}
+
 	/** @param array{0:string,1:int,2:string} $errorInfo */
 	#[\Override]
 	protected function autoUpdateDb(array $errorInfo): bool {
diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php
index a33d38e76..3cd76ea0a 100644
--- a/app/Models/DatabaseDAO.php
+++ b/app/Models/DatabaseDAO.php
@@ -409,19 +409,17 @@ SQL;
 		$userTo->createUser();
 
 		$catTo->beginTransaction();
+		$catTo->deleteCategory(FreshRSS_CategoryDAO::DEFAULTCATEGORYID);
+		$catTo->sqlResetSequence();
 		foreach ($catFrom->selectAll() as $category) {
-			$cat = $catTo->searchByName($category['name']);	//Useful for the default category
-			if ($cat != null) {
-				$catId = $cat->id();
-			} else {
-				$catId = $catTo->addCategory($category);
-				if ($catId == false) {
-					$error = 'Error during SQLite copy of categories!';
-					return self::stdError($error);
-				}
+			$catId = $catTo->addCategory($category);
+			if ($catId === false) {
+				$error = 'Error during SQLite copy of categories!';
+				return self::stdError($error);
 			}
 			$idMaps['c' . $category['id']] = $catId;
 		}
+		$catTo->sqlResetSequence();
 		foreach ($feedFrom->selectAll() as $feed) {
 			$feed['category'] = empty($idMaps['c' . $feed['category']]) ? FreshRSS_CategoryDAO::DEFAULTCATEGORYID : $idMaps['c' . $feed['category']];
 			$feedId = $feedTo->addFeed($feed);
@@ -431,6 +429,7 @@ SQL;
 			}
 			$idMaps['f' . $feed['id']] = $feedId;
 		}
+		$feedTo->sqlResetSequence();
 		$catTo->commit();
 
 		$nbEntries = $entryFrom->count();
@@ -483,6 +482,7 @@ SQL;
 				}
 			}
 		}
+		$tagTo->sqlResetSequence();
 		$tagTo->commit();
 
 		return true;
diff --git a/app/Models/Factory.php b/app/Models/Factory.php
index 6c140475e..30465ec04 100644
--- a/app/Models/Factory.php
+++ b/app/Models/Factory.php
@@ -16,6 +16,7 @@ class FreshRSS_Factory {
 	public static function createCategoryDao(?string $username = null): FreshRSS_CategoryDAO {
 		return match (FreshRSS_Context::systemConf()->db['type'] ?? '') {
 			'sqlite' => new FreshRSS_CategoryDAOSQLite($username),
+			'pgsql' => new FreshRSS_CategoryDAOPGSQL($username),
 			default => new FreshRSS_CategoryDAO($username),
 		};
 	}
@@ -26,6 +27,7 @@ class FreshRSS_Factory {
 	public static function createFeedDao(?string $username = null): FreshRSS_FeedDAO {
 		return match (FreshRSS_Context::systemConf()->db['type'] ?? '') {
 			'sqlite' => new FreshRSS_FeedDAOSQLite($username),
+			'pgsql' => new FreshRSS_FeedDAOPGSQL($username),
 			default => new FreshRSS_FeedDAO($username),
 		};
 	}
diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index a81c38eef..65d834936 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -3,6 +3,10 @@ declare(strict_types=1);
 
 class FreshRSS_FeedDAO extends Minz_ModelPdo {
 
+	public function sqlResetSequence(): bool {
+		return true;	// Nothing to do for MySQL
+	}
+
 	protected function addColumn(string $name): bool {
 		if ($this->pdo->inTransaction()) {
 			$this->pdo->commit();
@@ -34,12 +38,21 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo {
 	}
 
 	/**
-	 * @param array{url:string,kind:int,category:int,name:string,website:string,description:string,lastUpdate:int,priority?:int,
+	 * @param array{id?:int,url:string,kind:int,category:int,name:string,website:string,description:string,lastUpdate:int,priority?:int,
 	 * 	pathEntries?:string,httpAuth:string,error:int|bool,ttl?:int,attributes?:string|array<string|mixed>} $valuesTmp
 	 */
 	public function addFeed(array $valuesTmp): int|false {
-		$sql = 'INSERT INTO `_feed` (url, kind, category, name, website, description, `lastUpdate`, priority, `pathEntries`, `httpAuth`, error, ttl, attributes)
-				VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
+		if (empty($valuesTmp['id'])) {	// Auto-generated ID
+			$sql = <<<'SQL'
+INSERT INTO `_feed` (url, kind, category, name, website, description, `lastUpdate`, priority, `pathEntries`, `httpAuth`, error, ttl, attributes)
+VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+SQL;
+		} else {
+			$sql = <<<'SQL'
+INSERT INTO `_feed` (id, url, kind, category, name, website, description, `lastUpdate`, priority, `pathEntries`, `httpAuth`, error, ttl, attributes)
+VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+SQL;
+		}
 		$stm = $this->pdo->prepare($sql);
 
 		$valuesTmp['url'] = safe_ascii($valuesTmp['url']);
@@ -51,7 +64,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo {
 			$valuesTmp['attributes'] = [];
 		}
 
-		$values = [
+		$values = empty($valuesTmp['id']) ? [] : [$valuesTmp['id']];
+		$values = array_merge($values, [
 			$valuesTmp['url'],
 			$valuesTmp['kind'] ?? FreshRSS_Feed::KIND_RSS,
 			$valuesTmp['category'],
@@ -65,11 +79,16 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo {
 			isset($valuesTmp['error']) ? (int)$valuesTmp['error'] : 0,
 			isset($valuesTmp['ttl']) ? (int)$valuesTmp['ttl'] : FreshRSS_Feed::TTL_DEFAULT,
 			is_string($valuesTmp['attributes']) ? $valuesTmp['attributes'] : json_encode($valuesTmp['attributes'], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE),
-		];
+		]);
 
 		if ($stm !== false && $stm->execute($values)) {
-			$feedId = $this->pdo->lastInsertId('`_feed_id_seq`');
-			return $feedId === false ? false : (int)$feedId;
+			if (empty($valuesTmp['id'])) {
+				// Auto-generated ID
+				$feedId = $this->pdo->lastInsertId('`_feed_id_seq`');
+				return $feedId === false ? false : (int)$feedId;
+			}
+			$this->sqlResetSequence();
+			return $valuesTmp['id'];
 		} else {
 			$info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
 			/** @var array{0:string,1:int,2:string} $info */
diff --git a/app/Models/FeedDAOPGSQL.php b/app/Models/FeedDAOPGSQL.php
new file mode 100644
index 000000000..f436a2ec4
--- /dev/null
+++ b/app/Models/FeedDAOPGSQL.php
@@ -0,0 +1,13 @@
+<?php
+declare(strict_types=1);
+
+class FreshRSS_FeedDAOPGSQL extends FreshRSS_FeedDAO {
+
+	#[\Override]
+	public function sqlResetSequence(): bool {
+		$sql = <<<'SQL'
+SELECT setval('`_feed_id_seq`', COALESCE(MAX(id), 0) + 1, false) FROM `_feed`
+SQL;
+		return $this->pdo->exec($sql) !== false;
+	}
+}
diff --git a/app/Models/FeedDAOSQLite.php b/app/Models/FeedDAOSQLite.php
index 42915a493..a12fb2adb 100644
--- a/app/Models/FeedDAOSQLite.php
+++ b/app/Models/FeedDAOSQLite.php
@@ -3,6 +3,11 @@ declare(strict_types=1);
 
 class FreshRSS_FeedDAOSQLite extends FreshRSS_FeedDAO {
 
+	#[\Override]
+	public function sqlResetSequence(): bool {
+		return true;	// Nothing to do for SQLite
+	}
+
 	/** @param array{0:string,1:int,2:string} $errorInfo */
 	#[\Override]
 	protected function autoUpdateDb(array $errorInfo): bool {
diff --git a/app/Models/TagDAO.php b/app/Models/TagDAO.php
index 05b01f94a..4555018d0 100644
--- a/app/Models/TagDAO.php
+++ b/app/Models/TagDAO.php
@@ -7,16 +7,28 @@ class FreshRSS_TagDAO extends Minz_ModelPdo {
 		return 'IGNORE';
 	}
 
+	public function sqlResetSequence(): bool {
+		return true;	// Nothing to do for MySQL
+	}
+
 	/**
-	 * @param array{'id'?:int,'name':string,'attributes'?:array<string,mixed>} $valuesTmp
+	 * @param array{id?:int,name:string,attributes?:array<string,mixed>} $valuesTmp
 	 */
 	public function addTag(array $valuesTmp): int|false {
-		// TRIM() gives a text type hint to PostgreSQL
-		// No category of the same name
-		$sql = <<<'SQL'
+		if (empty($valuesTmp['id'])) {	// Auto-generated ID
+			$sql = <<<'SQL'
 INSERT INTO `_tag`(name, attributes)
-SELECT * FROM (SELECT TRIM(?) as name, TRIM(?) as attributes) t2
-WHERE NOT EXISTS (SELECT 1 FROM `_category` WHERE name = TRIM(?))
+SELECT * FROM (SELECT :name1 AS name, :attributes AS attributes) t2
+SQL;
+		} else {
+			$sql = <<<'SQL'
+INSERT INTO `_tag`(id, name, attributes)
+SELECT * FROM (SELECT 1*:id AS id, :name1 AS name, :attributes AS attributes) t2
+SQL;
+		}
+		// No category of the same name
+		$sql .= "\n" . <<<'SQL'
+WHERE NOT EXISTS (SELECT 1 FROM `_category` WHERE name = :name2)
 SQL;
 		$stm = $this->pdo->prepare($sql);
 
@@ -24,15 +36,21 @@ SQL;
 		if (!isset($valuesTmp['attributes'])) {
 			$valuesTmp['attributes'] = [];
 		}
-		$values = [
-			$valuesTmp['name'],
-			is_string($valuesTmp['attributes']) ? $valuesTmp['attributes'] : json_encode($valuesTmp['attributes'], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE),
-			$valuesTmp['name'],
-		];
-
-		if ($stm !== false && $stm->execute($values) && $stm->rowCount() > 0) {
-			$tagId = $this->pdo->lastInsertId('`_tag_id_seq`');
-			return $tagId === false ? false : (int)$tagId;
+		if ($stm !== false) {
+			$stm->bindValue(':id', empty($valuesTmp['id']) ? null : $valuesTmp['id'], PDO::PARAM_INT);
+			$stm->bindValue(':name1', $valuesTmp['name'], PDO::PARAM_STR);
+			$stm->bindValue(':name2', $valuesTmp['name'], PDO::PARAM_STR);
+			$stm->bindValue(':attributes', is_string($valuesTmp['attributes']) ? $valuesTmp['attributes'] :
+				json_encode($valuesTmp['attributes'], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE), PDO::PARAM_STR);
+		}
+		if ($stm !== false && $stm->execute() && $stm->rowCount() > 0) {
+			if (empty($valuesTmp['id'])) {
+				// Auto-generated ID
+				$tagId = $this->pdo->lastInsertId('`_tag_id_seq`');
+				return $tagId === false ? false : (int)$tagId;
+			}
+			$this->sqlResetSequence();
+			return $valuesTmp['id'];
 		} else {
 			$info = $stm === false ? $this->pdo->errorInfo() : $stm->errorInfo();
 			Minz_Log::error('SQL error ' . __METHOD__ . json_encode($info));
diff --git a/app/Models/TagDAOPGSQL.php b/app/Models/TagDAOPGSQL.php
index 35688c2d0..827fc557b 100644
--- a/app/Models/TagDAOPGSQL.php
+++ b/app/Models/TagDAOPGSQL.php
@@ -7,4 +7,12 @@ class FreshRSS_TagDAOPGSQL extends FreshRSS_TagDAO {
 	public function sqlIgnore(): string {
 		return '';	//TODO
 	}
+
+	#[\Override]
+	public function sqlResetSequence(): bool {
+		$sql = <<<'SQL'
+SELECT setval('`_tag_id_seq`', COALESCE(MAX(id), 0) + 1, false) FROM `_tag`
+SQL;
+		return $this->pdo->exec($sql) !== false;
+	}
 }
diff --git a/app/Models/TagDAOSQLite.php b/app/Models/TagDAOSQLite.php
index 69584edf0..43bb9cdcd 100644
--- a/app/Models/TagDAOSQLite.php
+++ b/app/Models/TagDAOSQLite.php
@@ -7,4 +7,9 @@ class FreshRSS_TagDAOSQLite extends FreshRSS_TagDAO {
 	public function sqlIgnore(): string {
 		return 'OR IGNORE';
 	}
+
+	#[\Override]
+	public function sqlResetSequence(): bool {
+		return true;	// Nothing to do for SQLite
+	}
 }
diff --git a/p/api/greader.php b/p/api/greader.php
index 88afb3656..b983bfa90 100644
--- a/p/api/greader.php
+++ b/p/api/greader.php
@@ -933,7 +933,7 @@ final class GReaderAPI {
 			if ($cat != null) {
 				$feedDAO = FreshRSS_Factory::createFeedDao();
 				$feedDAO->changeCategory($cat->id(), 0);
-				if ($cat->id() > 1) {
+				if ($cat->id() > FreshRSS_CategoryDAO::DEFAULTCATEGORYID) {
 					$categoryDAO->deleteCategory($cat->id());
 				}
 				exit('OK');