PDO refactoring for code simplification (#2522)

* PDO refactor

* Automatic prefix when using the syntax `_tableName`
* Uniformity: MySQL is now PDO::ATTR_EMULATE_PREPARES = false just like SQLite and PostgreSQL, with consequences such as only one statement per query
* Use PDO methods exec(), query(), prepare() + execute() in a more efficient way
* Remove auto-update SQL code for versions older than FreshRSS 1.5 (3 years old)
* The name of the default category is set in PHP instead of in the DB (simplies SQL and allows changing the name according to the FreshRSS language)
* Rename `->bd` to `->pdo` (less of a frenshism, and more informative)
* Fix some requests, which were not compatible with MySQL prepared statements

* Whitespace

* Fix syntax for PostgreSQL sequences

+ MySQL install

* Minor formatting

* Fix lastInsertId for PostgreSQL

* Use PHP 5.6+ const

Take advantage of https://github.com/FreshRSS/FreshRSS/pull/2527
https://www.php.net/manual/en/migration56.new-features.php

* A bit of forgotten PHP 5.6 simplification for cURL

* Forgotten $s

* Mini fix custom user config

https://github.com/FreshRSS/FreshRSS/pull/2490/files#r326290346

* More work on install.php but not finished

* install.php working

* More cleaning of PDO in install

* Even more simplification

Take advantage of PDO->exec() to run multiple statements

* Disallow changing the name of the default category

https://github.com/FreshRSS/FreshRSS/pull/2522#discussion_r326967724

1 files changed, 46 insertions, 58 deletions

diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php
index b0fcb5033..2bae5ef83 100644
--- a/app/Models/CategoryDAO.php
+++ b/app/Models/CategoryDAO.php
@@ -5,10 +5,10 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 	const DEFAULTCATEGORYID = 1;
 
 	public function addCategory($valuesTmp) {
-		$sql = 'INSERT INTO `' . $this->prefix . 'category`(name) '
+		$sql = 'INSERT INTO `_category`(name) '
 		     . 'SELECT * FROM (SELECT TRIM(?)) c2 '	//TRIM() to provide a type hint as text for PostgreSQL
-		     . 'WHERE NOT EXISTS (SELECT 1 FROM `' . $this->prefix . 'tag` WHERE name = TRIM(?))';	//No tag of the same name
-		$stm = $this->bd->prepare($sql);
+		     . 'WHERE NOT EXISTS (SELECT 1 FROM `_tag` WHERE name = TRIM(?))';	//No tag of the same name
+		$stm = $this->pdo->prepare($sql);
 
 		$valuesTmp['name'] = mb_strcut(trim($valuesTmp['name']), 0, FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE, 'UTF-8');
 		$values = array(
@@ -17,7 +17,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 		);
 
 		if ($stm && $stm->execute($values)) {
-			return $this->bd->lastInsertId('"' . $this->prefix . 'category_id_seq"');
+			return $this->pdo->lastInsertId('`_category_id_seq`');
 		} else {
 			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
 			Minz_Log::error('SQL error addCategory: ' . $info[2]);
@@ -39,9 +39,9 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 	}
 
 	public function updateCategory($id, $valuesTmp) {
-		$sql = 'UPDATE `' . $this->prefix . 'category` SET name=? WHERE id=? '
-		     . 'AND NOT EXISTS (SELECT 1 FROM `' . $this->prefix . 'tag` WHERE name = ?)';	//No tag of the same name
-		$stm = $this->bd->prepare($sql);
+		$sql = 'UPDATE `_category` SET name=? WHERE id=? '
+		     . 'AND NOT EXISTS (SELECT 1 FROM `_tag` WHERE name = ?)';	//No tag of the same name
+		$stm = $this->pdo->prepare($sql);
 
 		$valuesTmp['name'] = mb_strcut(trim($valuesTmp['name']), 0, FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE, 'UTF-8');
 		$values = array(
@@ -63,12 +63,10 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 		if ($id <= self::DEFAULTCATEGORYID) {
 			return false;
 		}
-		$sql = 'DELETE FROM `' . $this->prefix . 'category` WHERE id=?';
-		$stm = $this->bd->prepare($sql);
-
-		$values = array($id);
-
-		if ($stm && $stm->execute($values)) {
+		$sql = 'DELETE FROM `_category` WHERE id=:id';
+		$stm = $this->pdo->prepare($sql);
+		$stm->bindParam(':id', $id, PDO::PARAM_INT);
+		if ($stm && $stm->execute()) {
 			return $stm->rowCount();
 		} else {
 			$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
@@ -78,21 +76,18 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 	}
 
 	public function selectAll() {
-		$sql = 'SELECT id, name FROM `' . $this->prefix . 'category`';
-		$stm = $this->bd->prepare($sql);
-		$stm->execute();
+		$sql = 'SELECT id, name FROM `_category`';
+		$stm = $this->pdo->query($sql);
 		while ($row = $stm->fetch(PDO::FETCH_ASSOC)) {
 			yield $row;
 		}
 	}
 
 	public function searchById($id) {
-		$sql = 'SELECT * FROM `' . $this->prefix . 'category` WHERE id=?';
-		$stm = $this->bd->prepare($sql);
-
-		$values = array($id);
-
-		$stm->execute($values);
+		$sql = 'SELECT * FROM `_category` WHERE id=:id';
+		$stm = $this->pdo->prepare($sql);
+		$stm->bindParam(':id', $id, PDO::PARAM_INT);
+		$stm->execute();
 		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
 		$cat = self::daoToCategory($res);
 
@@ -103,18 +98,15 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 		}
 	}
 	public function searchByName($name) {
-		$sql = 'SELECT * FROM `' . $this->prefix . 'category` WHERE name=?';
-		$stm = $this->bd->prepare($sql);
+		$sql = 'SELECT * FROM `_category` WHERE name=:name';
+		$stm = $this->pdo->prepare($sql);
 		if ($stm == false) {
 			return false;
 		}
-
-		$values = array($name);
-
-		$stm->execute($values);
+		$stm->bindParam(':name', $name);
+		$stm->execute();
 		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
 		$cat = self::daoToCategory($res);
-
 		if (isset($cat[0])) {
 			return $cat[0];
 		} else {
@@ -126,26 +118,26 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 		if ($prePopulateFeeds) {
 			$sql = 'SELECT c.id AS c_id, c.name AS c_name, '
 			     . ($details ? 'f.* ' : 'f.id, f.name, f.url, f.website, f.priority, f.error, f.`cache_nbEntries`, f.`cache_nbUnreads`, f.ttl ')
-			     . 'FROM `' . $this->prefix . 'category` c '
-			     . 'LEFT OUTER JOIN `' . $this->prefix . 'feed` f ON f.category=c.id '
+			     . 'FROM `_category` c '
+			     . 'LEFT OUTER JOIN `_feed` f ON f.category=c.id '
 			     . 'WHERE f.priority >= :priority_normal '
 			     . 'GROUP BY f.id, c_id '
 			     . 'ORDER BY c.name, f.name';
-			$stm = $this->bd->prepare($sql);
-			$stm->execute(array(':priority_normal' => FreshRSS_Feed::PRIORITY_NORMAL));
+			$stm = $this->pdo->prepare($sql);
+			$stm->bindValue(':priority_normal', FreshRSS_Feed::PRIORITY_NORMAL, PDO::PARAM_INT);
+			$stm->execute();
 			return self::daoToCategoryPrepopulated($stm->fetchAll(PDO::FETCH_ASSOC));
 		} else {
-			$sql = 'SELECT * FROM `' . $this->prefix . 'category` ORDER BY name';
-			$stm = $this->bd->prepare($sql);
-			$stm->execute();
+			$sql = 'SELECT * FROM `_category` ORDER BY name';
+			$stm = $this->pdo->query($sql);
 			return self::daoToCategory($stm->fetchAll(PDO::FETCH_ASSOC));
 		}
 	}
 
 	public function getDefault() {
-		$sql = 'SELECT * FROM `' . $this->prefix . 'category` WHERE id=' . self::DEFAULTCATEGORYID;
-		$stm = $this->bd->prepare($sql);
-
+		$sql = 'SELECT * FROM `_category` WHERE id=:id';
+		$stm = $this->pdo->prepare($sql);
+		$stm->bindValue(':id', self::DEFAULTCATEGORYID, PDO::PARAM_INT);
 		$stm->execute();
 		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
 		$cat = self::daoToCategory($res);
@@ -167,12 +159,12 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 			$cat = new FreshRSS_Category(_t('gen.short.default_category'));
 			$cat->_id(self::DEFAULTCATEGORYID);
 
-			$sql = 'INSERT INTO `' . $this->prefix . 'category`(id, name) VALUES(?, ?)';
-			if ($this->bd->dbType() === 'pgsql') {
+			$sql = 'INSERT INTO `_category`(id, name) VALUES(?, ?)';
+			if ($this->pdo->dbType() === 'pgsql') {
 				//Force call to nextval()
-				$sql .= ' RETURNING nextval(\'"' . $this->prefix . 'category_id_seq"\');';
+				$sql .= " RETURNING nextval('`_category_id_seq`');";
 			}
-			$stm = $this->bd->prepare($sql);
+			$stm = $this->pdo->prepare($sql);
 
 			$values = array(
 				$cat->id(),
@@ -180,7 +172,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 			);
 
 			if ($stm && $stm->execute($values)) {
-				return $this->bd->lastInsertId('"' . $this->prefix . 'category_id_seq"');
+				return $this->pdo->lastInsertId('`_category_id_seq`');
 			} else {
 				$info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo();
 				Minz_Log::error('SQL error check default category: ' . json_encode($info));
@@ -191,31 +183,27 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable
 	}
 
 	public function count() {
-		$sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'category`';
-		$stm = $this->bd->prepare($sql);
-		$stm->execute();
+		$sql = 'SELECT COUNT(*) AS count FROM `_category`';
+		$stm = $this->pdo->query($sql);
 		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
-
 		return $res[0]['count'];
 	}
 
 	public function countFeed($id) {
-		$sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'feed` WHERE category=?';
-		$stm = $this->bd->prepare($sql);
-		$values = array($id);
-		$stm->execute($values);
+		$sql = 'SELECT COUNT(*) AS count FROM `_feed` WHERE category=:id';
+		$stm = $this->pdo->prepare($sql);
+		$stm->bindParam(':id', $id, PDO::PARAM_INT);
+		$stm->execute();
 		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
-
 		return $res[0]['count'];
 	}
 
 	public function countNotRead($id) {
-		$sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id WHERE category=? AND e.is_read=0';
-		$stm = $this->bd->prepare($sql);
-		$values = array($id);
-		$stm->execute($values);
+		$sql = 'SELECT COUNT(*) AS count FROM `_entry` e INNER JOIN `_feed` f ON e.id_feed=f.id WHERE category=:id AND e.is_read=0';
+		$stm = $this->pdo->prepare($sql);
+		$stm->bindParam(':id', $id, PDO::PARAM_INT);
+		$stm->execute();
 		$res = $stm->fetchAll(PDO::FETCH_ASSOC);
-
 		return $res[0]['count'];
 	}