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

4 files changed, 106 insertions, 135 deletions

diff --git a/lib/Minz/ModelPdo.php b/lib/Minz/ModelPdo.php
index 4d5e47da9..3fabb73c8 100644
--- a/lib/Minz/ModelPdo.php
+++ b/lib/Minz/ModelPdo.php
@@ -6,45 +6,35 @@
 
 /**
  * La classe Model_sql représente le modèle interragissant avec les bases de données
- * Seul la connexion MySQL est prise en charge pour le moment
  */
 class Minz_ModelPdo {
 
 	/**
 	 * Partage la connexion à la base de données entre toutes les instances.
 	 */
-	public static $useSharedBd = true;
-	private static $sharedBd = null;
+	public static $usesSharedPdo = true;
+	private static $sharedPdo = null;
 	private static $sharedPrefix;
 	private static $sharedCurrentUser;
 
-	/**
-	 * $bd variable représentant la base de données
-	 */
-	protected $bd;
-
+	protected $pdo;
 	protected $current_user;
-	protected $prefix;
 
 	/**
 	 * Créé la connexion à la base de données à l'aide des variables
 	 * HOST, BASE, USER et PASS définies dans le fichier de configuration
 	 */
-	public function __construct($currentUser = null, $currentPrefix = null, $currentDb = null) {
+	public function __construct($currentUser = null, $currentPdo = null) {
 		if ($currentUser === null) {
 			$currentUser = Minz_Session::param('currentUser');
 		}
-		if ($currentPrefix !== null) {
-			$this->prefix = $currentPrefix;
-		}
-		if ($currentDb != null) {
-			$this->bd = $currentDb;
+		if ($currentPdo != null) {
+			$this->pdo = $currentPdo;
 			return;
 		}
-		if (self::$useSharedBd && self::$sharedBd != null &&
-			($currentUser == null || $currentUser === self::$sharedCurrentUser)) {
-			$this->bd = self::$sharedBd;
-			$this->prefix = self::$sharedPrefix;
+		if (self::$usesSharedPdo && self::$sharedPdo != null &&
+			($currentUser == '' || $currentUser === self::$sharedCurrentUser)) {
+			$this->pdo = self::$sharedPdo;
 			$this->current_user = self::$sharedCurrentUser;
 			return;
 		}
@@ -54,35 +44,39 @@ class Minz_ModelPdo {
 		$conf = Minz_Configuration::get('system');
 		$db = $conf->db;
 
-		$driver_options = isset($conf->db['pdo_options']) && is_array($conf->db['pdo_options']) ? $conf->db['pdo_options'] : array();
+		$driver_options = isset($db['pdo_options']) && is_array($db['pdo_options']) ? $db['pdo_options'] : [];
 		$dbServer = parse_url('db://' . $db['host']);
+		$dsn = '';
 
 		try {
 			switch ($db['type']) {
 				case 'mysql':
-					$string = 'mysql:host=' . (empty($dbServer['host']) ? $db['host'] : $dbServer['host']) . ';dbname=' . $db['base'] . ';charset=utf8mb4';
+					$dsn = 'mysql:host=' . (empty($dbServer['host']) ? $db['host'] : $dbServer['host']) . ';charset=utf8mb4';
+					if (!empty($db['base'])) {
+						$dsn .= ';dbname=' . $db['base'];
+					}
 					if (!empty($dbServer['port'])) {
-						$string .= ';port=' . $dbServer['port'];
+						$dsn .= ';port=' . $dbServer['port'];
 					}
 					$driver_options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES utf8mb4';
-					$this->prefix = $db['prefix'] . $currentUser . '_';
-					$this->bd = new MinzPDOMySql($string, $db['user'], $db['password'], $driver_options);
-					$this->bd->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
+					$this->pdo = new MinzPDOMySql($dsn, $db['user'], $db['password'], $driver_options);
+					$this->pdo->setPrefix($db['prefix'] . $currentUser . '_');
 					break;
 				case 'sqlite':
-					$string = 'sqlite:' . join_path(DATA_PATH, 'users', $currentUser, 'db.sqlite');
-					$this->prefix = '';
-					$this->bd = new MinzPDOSQLite($string, $db['user'], $db['password'], $driver_options);
-					$this->bd->exec('PRAGMA foreign_keys = ON;');
+					$dsn = 'sqlite:' . join_path(DATA_PATH, 'users', $currentUser, 'db.sqlite');
+					$this->pdo = new MinzPDOSQLite($dsn, $db['user'], $db['password'], $driver_options);
+					$this->pdo->setPrefix('');
 					break;
 				case 'pgsql':
-					$string = 'pgsql:host=' . (empty($dbServer['host']) ? $db['host'] : $dbServer['host']) . ';dbname=' . $db['base'];
+					$dsn = 'pgsql:host=' . (empty($dbServer['host']) ? $db['host'] : $dbServer['host']);
+					if (!empty($db['base'])) {
+						$dsn .= ';dbname=' . $db['base'];
+					}
 					if (!empty($dbServer['port'])) {
-						$string .= ';port=' . $dbServer['port'];
+						$dsn .= ';port=' . $dbServer['port'];
 					}
-					$this->prefix = $db['prefix'] . $currentUser . '_';
-					$this->bd = new MinzPDOPGSQL($string, $db['user'], $db['password'], $driver_options);
-					$this->bd->exec("SET NAMES 'UTF8';");
+					$this->pdo = new MinzPDOPGSQL($dsn, $db['user'], $db['password'], $driver_options);
+					$this->pdo->setPrefix($db['prefix'] . $currentUser . '_');
 					break;
 				default:
 					throw new Minz_PDOConnectionException(
@@ -91,69 +85,86 @@ class Minz_ModelPdo {
 					);
 					break;
 			}
-			self::$sharedBd = $this->bd;
-			self::$sharedPrefix = $this->prefix;
+			self::$sharedPdo = $this->pdo;
 		} catch (Exception $e) {
 			throw new Minz_PDOConnectionException(
-				$string,
+				$dsn,
 				$db['user'], Minz_Exception::ERROR
 			);
 		}
 	}
 
 	public function beginTransaction() {
-		$this->bd->beginTransaction();
+		$this->pdo->beginTransaction();
 	}
 	public function inTransaction() {
-		return $this->bd->inTransaction();
+		return $this->pdo->inTransaction();
 	}
 	public function commit() {
-		$this->bd->commit();
+		$this->pdo->commit();
 	}
 	public function rollBack() {
-		$this->bd->rollBack();
+		$this->pdo->rollBack();
 	}
 
 	public static function clean() {
-		self::$sharedBd = null;
+		self::$sharedPdo = null;
 		self::$sharedCurrentUser = '';
-		self::$sharedPrefix = '';
 	}
 }
 
 abstract class MinzPDO extends PDO {
-	private static function check($statement) {
+	public function __construct($dsn, $username = null, $passwd = null, $options = null) {
+		parent::__construct($dsn, $username, $passwd, $options);
+		$this->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
+    }
+
+	abstract public function dbType();
+
+	private $prefix = '';
+	public function prefix() { return $this->prefix; }
+	public function setPrefix($prefix) { $this->prefix = $prefix; }
+
+	private function autoPrefix($sql) {
+		return str_replace('`_', '`' . $this->prefix, $sql);
+	}
+
+	protected function preSql($statement) {
 		if (preg_match('/^(?:UPDATE|INSERT|DELETE)/i', $statement)) {
 			invalidateHttpCache();
 		}
+		return $this->autoPrefix($statement);
 	}
 
-	protected function compatibility($statement) {
-		return $statement;
+	public function lastInsertId($name = null) {
+		if ($name != null) {
+			$name = $this->preSql($name);
+		}
+		return parent::lastInsertId($name);
 	}
 
-	abstract public function dbType();
-
 	public function prepare($statement, $driver_options = array()) {
-		MinzPDO::check($statement);
-		$statement = $this->compatibility($statement);
+		$statement = $this->preSql($statement);
 		return parent::prepare($statement, $driver_options);
 	}
 
 	public function exec($statement) {
-		MinzPDO::check($statement);
-		$statement = $this->compatibility($statement);
+		$statement = $this->preSql($statement);
 		return parent::exec($statement);
 	}
 
 	public function query($statement) {
-		MinzPDO::check($statement);
-		$statement = $this->compatibility($statement);
+		$statement = $this->preSql($statement);
 		return parent::query($statement);
 	}
 }
 
 class MinzPDOMySql extends MinzPDO {
+	public function __construct($dsn, $username = null, $passwd = null, $options = null) {
+		parent::__construct($dsn, $username, $passwd, $options);
+		$this->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
+	}
+
 	public function dbType() {
 		return 'mysql';
 	}
@@ -164,6 +175,11 @@ class MinzPDOMySql extends MinzPDO {
 }
 
 class MinzPDOSQLite extends MinzPDO {
+	public function __construct($dsn, $username = null, $passwd = null, $options = null) {
+		parent::__construct($dsn, $username, $passwd, $options);
+		$this->exec('PRAGMA foreign_keys = ON;');
+    }
+
 	public function dbType() {
 		return 'sqlite';
 	}
@@ -174,11 +190,17 @@ class MinzPDOSQLite extends MinzPDO {
 }
 
 class MinzPDOPGSQL extends MinzPDO {
+	public function __construct($dsn, $username = null, $passwd = null, $options = null) {
+		parent::__construct($dsn, $username, $passwd, $options);
+		$this->exec("SET NAMES 'UTF8';");
+    }
+
 	public function dbType() {
 		return 'pgsql';
 	}
 
-	protected function compatibility($statement) {
+	protected function preSql($statement) {
+		$statement = parent::preSql($statement);
 		return str_replace(array('`', ' LIKE '), array('"', ' ILIKE '), $statement);
 	}
 }
diff --git a/lib/favicons.php b/lib/favicons.php
index 7a2d1187e..bc82b57b9 100644
--- a/lib/favicons.php
+++ b/lib/favicons.php
@@ -1,6 +1,6 @@
 <?php
-$favicons_dir = DATA_PATH . '/favicons/';
-$default_favicon = PUBLIC_PATH . '/themes/icons/default_favicon.ico';
+const FAVICONS_DIR = DATA_PATH . '/favicons/';
+const DEFAULT_FAVICON = PUBLIC_PATH . '/themes/icons/default_favicon.ico';
 
 function isImgMime($content) {
 	//Based on https://github.com/ArthurHoaro/favicon/blob/3a4f93da9bb24915b21771eb7873a21bde26f5d1/src/Favicon/Favicon.php#L311-L319
@@ -31,18 +31,14 @@ function downloadHttp(&$url, $curlOptions = array()) {
 		return '';
 	}
 	$ch = curl_init($url);
-	curl_setopt_array($ch, array(
+	curl_setopt_array($ch, [
 			CURLOPT_RETURNTRANSFER => true,
 			CURLOPT_TIMEOUT => 15,
 			CURLOPT_USERAGENT => FRESHRSS_USERAGENT,
 			CURLOPT_MAXREDIRS => 10,
-		));
-	if (version_compare(PHP_VERSION, '5.6.0') >= 0 || ini_get('open_basedir') == '') {
-		curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);	//Keep option separated for open_basedir PHP bug 65646
-	}
-	if (defined('CURLOPT_ENCODING')) {
-		curl_setopt($ch, CURLOPT_ENCODING, '');	//Enable all encodings
-	}
+			CURLOPT_FOLLOWLOCATION => true,
+			CURLOPT_ENCODING => '',	//Enable all encodings
+		]);
 	curl_setopt_array($ch, $curlOptions);
 	$response = curl_exec($ch);
 	$info = curl_getinfo($ch);
@@ -89,7 +85,6 @@ function searchFavicon(&$url) {
 }
 
 function download_favicon($url, $dest) {
-	global $default_favicon;
 	$url = trim($url);
 	$favicon = searchFavicon($url);
 	if ($favicon == '') {
@@ -109,5 +104,5 @@ function download_favicon($url, $dest) {
 		}
 	}
 	return ($favicon != '' && file_put_contents($dest, $favicon)) ||
-		@copy($default_favicon, $dest);
+		@copy(DEFAULT_FAVICON, $dest);
 }
diff --git a/lib/lib_install.php b/lib/lib_install.php
index 17defccf6..ed361eb39 100644
--- a/lib/lib_install.php
+++ b/lib/lib_install.php
@@ -78,69 +78,24 @@ function generateSalt() {
 	return sha1(uniqid(mt_rand(), true).implode('', stat(__FILE__)));
 }
 
-function checkDb(&$dbOptions) {
-	$dsn = '';
-	$driver_options = null;
-	prepareSyslog();
-	try {
-		switch ($dbOptions['type']) {
-		case 'mysql':
-			include_once(APP_PATH . '/SQL/install.sql.mysql.php');
-			$driver_options = array(
-				PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4'
-			);
-			try {	// on ouvre une connexion juste pour créer la base si elle n'existe pas
-				$dsn = 'mysql:host=' . $dbOptions['host'] . ';';
-				$c = new PDO($dsn, $dbOptions['user'], $dbOptions['password'], $driver_options);
-				$sql = sprintf(SQL_CREATE_DB, $dbOptions['base']);
-				$res = $c->query($sql);
-			} catch (PDOException $e) {
-				syslog(LOG_DEBUG, 'FreshRSS MySQL warning: ' . $e->getMessage());
-			}
-			// on écrase la précédente connexion en sélectionnant la nouvelle BDD
-			$dsn = 'mysql:host=' . $dbOptions['host'] . ';dbname=' . $dbOptions['base'];
-			break;
-		case 'sqlite':
-			include_once(APP_PATH . '/SQL/install.sql.sqlite.php');
-			$path = join_path(USERS_PATH, $dbOptions['default_user']);
-			if (!is_dir($path)) {
-				mkdir($path);
-			}
-			$dsn = 'sqlite:' . join_path($path, 'db.sqlite');
-			$driver_options = array(
-				PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
-			);
-			break;
-		case 'pgsql':
-			include_once(APP_PATH . '/SQL/install.sql.pgsql.php');
-			$driver_options = array(
-				PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
-			);
-			try {	// on ouvre une connexion juste pour créer la base si elle n'existe pas
-				$dsn = 'pgsql:host=' . $dbOptions['host'] . ';dbname=postgres';
-				$c = new PDO($dsn, $dbOptions['user'], $dbOptions['password'], $driver_options);
-				$sql = sprintf(SQL_CREATE_DB, $dbOptions['base']);
-				$res = $c->query($sql);
-			} catch (PDOException $e) {
-				syslog(LOG_DEBUG, 'FreshRSS PostgreSQL warning: ' . $e->getMessage());
-			}
-			// on écrase la précédente connexion en sélectionnant la nouvelle BDD
-			$dsn = 'pgsql:host=' . $dbOptions['host'] . ';dbname=' . $dbOptions['base'];
-			break;
-		default:
-			return false;
-		}
-
-		$c = new PDO($dsn, $dbOptions['user'], $dbOptions['password'], $driver_options);
-		$res = $c->query('SELECT 1');
-	} catch (PDOException $e) {
-		$dsn = '';
-		syslog(LOG_DEBUG, 'FreshRSS SQL warning: ' . $e->getMessage());
-		$dbOptions['error'] = $e->getMessage();
+function checkDb() {
+	$conf = FreshRSS_Context::$system_conf;
+	$db = $conf->db;
+	if (empty($db['pdo_options'])) {
+		$db['pdo_options'] = [];
 	}
-	$dbOptions['dsn'] = $dsn;
-	$dbOptions['options'] = $driver_options;
-	return $dsn != '';
+	$db['pdo_options'][PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
+	$dbBase = isset($db['base']) ? $db['base'] : '';
+
+	$db['base'] = '';	//First connection without database name to create the database
+	Minz_ModelPdo::$usesSharedPdo = false;
+	$databaseDAO = FreshRSS_Factory::createDatabaseDAO();
+	$databaseDAO->create();
+
+	$db['base'] = $dbBase;	//New connection with the database name
+	$databaseDAO = FreshRSS_Factory::createDatabaseDAO();
+	Minz_ModelPdo::$usesSharedPdo = true;
+	return $databaseDAO->testConnection();
 }
 
 function deleteInstall() {
diff --git a/lib/lib_rss.php b/lib/lib_rss.php
index 1bba60c36..854126b54 100644
--- a/lib/lib_rss.php
+++ b/lib/lib_rss.php
@@ -535,17 +535,16 @@ function _i($icon, $url_only = false) {
 }
 
 
-$SHORTCUT_KEYS = array(	//No const for < PHP 5.6 compatibility
+const SHORTCUT_KEYS = [
 			'0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
 			'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm',
 			'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',
 			'F1', 'F2', 'F3', 'F4', 'F5', 'F6', 'F7', 'F8', 'F9', 'F10', 'F11', 'F12',
 			'ArrowDown', 'ArrowLeft', 'ArrowRight', 'ArrowUp', 'Backspace', 'Delete',
 			'End', 'Enter', 'Escape', 'Home', 'Insert', 'PageDown', 'PageUp', 'Space', 'Tab',
-		);
+		];
 
 function validateShortcutList($shortcuts) {
-	global $SHORTCUT_KEYS;
 	$legacy = array(
 			'down' => 'ArrowDown', 'left' => 'ArrowLeft', 'page_down' => 'PageDown', 'page_up' => 'PageUp',
 			'right' => 'ArrowRight', 'up' => 'ArrowUp',
@@ -554,17 +553,17 @@ function validateShortcutList($shortcuts) {
 	$shortcuts_ok = array();
 
 	foreach ($shortcuts as $key => $value) {
-		if (in_array($value, $SHORTCUT_KEYS)) {
+		if (in_array($value, SHORTCUT_KEYS)) {
 			$shortcuts_ok[$key] = $value;
 		} elseif (isset($legacy[$value])) {
 			$shortcuts_ok[$key] = $legacy[$value];
 		} else {	//Case-insensitive search
 			if ($upper === null) {
-				$upper = array_map('strtoupper', $SHORTCUT_KEYS);
+				$upper = array_map('strtoupper', SHORTCUT_KEYS);
 			}
 			$i = array_search(strtoupper($value), $upper);
 			if ($i !== false) {
-				$shortcuts_ok[$key] = $SHORTCUT_KEYS[$i];
+				$shortcuts_ok[$key] = SHORTCUT_KEYS[$i];
 			}
 		}
 	}