From fd33d92d413acb5ee48e04d8a78f251e35ef06c5 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 20 Aug 2019 14:55:43 +0200 Subject: Require PHP 5.5+ (#2495) * Require PHP 5.5+ https://github.com/FreshRSS/FreshRSS/issues/2469#issuecomment-522255093 I think it would be reasonable to require PHP 5.5+ for the core of FreshRSS after all. As Frenzie said, WordPress currently requires PHP 5.6.20+, and it is the most popular PHP application. We would loose about 20% of the PHP servers according to https://w3techs.com/technologies/details/pl-php/5/all but I expect this number to drop fast after the release of CentOS 8 (CentOS accounts for 17% of Linux servers https://w3techs.com/technologies/details/os-linux/all/all ). Distributions: * no impact on Ubuntu, Fedora, Alpine, OpenWRT, FreeBSD, OpenSuze, Mageia, as all active versions have PHP > 7 * no impact on OpenSuze, Synology, as all active versions have PHP > 5.5 * we drop Debian 8 Jessie (-2020) - we keep supporting Debian 9 Stretch (2017-06) - current is Debian 10 Buster * we drop Red Hat 7 (-2024) - we keep supporting RHEL 8 (2019-05) * we drop CentOS 7 (-2024) - we will support CentOS 8 (to be released soonish) When dropping older versions, I can better like when it is for a good reason, and there is actually one with PHP 5.5, namely generators (yield) https://php.net/language.generators.overview which I consider using. * Version note for JSON.php * hex2bin * Update .travis.yml Co-Authored-By: Frans de Jonge --- lib/lib_install.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'lib/lib_install.php') diff --git a/lib/lib_install.php b/lib/lib_install.php index 6e4df4e9c..6b9b33240 100644 --- a/lib/lib_install.php +++ b/lib/lib_install.php @@ -6,7 +6,7 @@ Minz_Configuration::register('default_system', join_path(FRESHRSS_PATH, 'config. Minz_Configuration::register('default_user', join_path(FRESHRSS_PATH, 'config-user.default.php')); function checkRequirements($dbType = '') { - $php = version_compare(PHP_VERSION, '5.3.8') >= 0; + $php = version_compare(PHP_VERSION, '5.5.0') >= 0; $minz = file_exists(join_path(LIB_PATH, 'Minz')); $curl = extension_loaded('curl'); $pdo_mysql = extension_loaded('pdo_mysql'); -- cgit v1.2.3 From 38117df976a5b8776a6df483ac7666895691b832 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 17 Sep 2019 20:28:07 +0200 Subject: Require PHP 5.6+ Discussion https://github.com/FreshRSS/FreshRSS/pull/2495#issuecomment-531911473 Needed for constant arrays https://www.php.net/manual/en/migration56.new-features.php which will be used in https://github.com/FreshRSS/FreshRSS/pull/2522 --- .travis.yml | 3 --- CHANGELOG.md | 2 +- README.fr.md | 4 ++-- README.md | 4 ++-- app/install.php | 2 +- app/views/update/checkInstall.phtml | 2 +- docs/en/admins/02_Installation.md | 2 +- docs/en/admins/05_Configuring_email_validation.md | 5 +---- docs/fr/users/01_Installation.md | 2 +- lib/lib_install.php | 2 +- lib/lib_rss.php | 4 ++-- 11 files changed, 13 insertions(+), 19 deletions(-) (limited to 'lib/lib_install.php') diff --git a/.travis.yml b/.travis.yml index 5b0ba705a..aedc526d2 100644 --- a/.travis.yml +++ b/.travis.yml @@ -30,9 +30,6 @@ env: matrix: fast_finish: true include: - # PHP 5.5 only runs on Travis in 14.04 (trusty), not 16.04 (xenial) - - php: "5.5" - dist: trusty - php: "7.2" env: CHECK_TRANSLATION=yes VALIDATE_STANDARD=no - language: node_js diff --git a/CHANGELOG.md b/CHANGELOG.md index 443302273..67103374b 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -6,7 +6,7 @@ * New option to display article authors underneath the article title [#2487](https://github.com/FreshRSS/FreshRSS/pull/2487) * Add e-mail capability [#2476](https://github.com/FreshRSS/FreshRSS/pull/2476) * Compatibility - * Require at least PHP 5.5+ [#2495](https://github.com/FreshRSS/FreshRSS/pull/2495) + * Require at least PHP 5.6+ [#2495](https://github.com/FreshRSS/FreshRSS/pull/2495) * Deployment * Take advantage of `mod_authz_core` instead of `mod_access_compat` when running on Apache 2.4+ [#2461](https://github.com/FreshRSS/FreshRSS/pull/2461) * Docker: Alpine image updated to 3.10 with PHP 7.3.8 and Apache 2.4.41 [#2238](https://github.com/FreshRSS/FreshRSS/pull/2238) diff --git a/README.fr.md b/README.fr.md index ceee48f5b..9644f78de 100644 --- a/README.fr.md +++ b/README.fr.md @@ -43,7 +43,7 @@ FreshRSS n’est fourni avec aucune garantie. * Serveur modeste, par exemple sous Linux ou Windows * Fonctionne même sur un Raspberry Pi 1 avec des temps de réponse < 1s (testé sur 150 flux, 22k articles) * Serveur Web Apache2 (recommandé), ou nginx, lighttpd (non testé sur les autres) -* PHP 5.5+ (PHP 7+ recommandé pour de meilleures performances) +* PHP 5.6+ (PHP 7+ recommandé pour de meilleures performances) * Requis : [cURL](https://secure.php.net/curl), [DOM](https://secure.php.net/dom), [XML](https://secure.php.net/xml), [session](https://secure.php.net/session), [ctype](https://secure.php.net/ctype), et [PDO_MySQL](https://secure.php.net/pdo-mysql) ou [PDO_SQLite](https://secure.php.net/pdo-sqlite) ou [PDO_PGSQL](https://secure.php.net/pdo-pgsql) * Recommandés : [JSON](https://secure.php.net/json), [GMP](https://secure.php.net/gmp) (pour accès API sur plateformes < 64 bits), [IDN](https://secure.php.net/intl.idn) (pour les noms de domaines internationalisés), [mbstring](https://secure.php.net/mbstring) (pour le texte Unicode), [iconv](https://secure.php.net/iconv) (pour conversion d’encodages), [ZIP](https://secure.php.net/zip) (pour import/export), [zlib](https://secure.php.net/zlib) (pour les flux compressés) * MySQL 5.5.3+ (recommandé) ou équivalent MariaDB, ou SQLite 3.7.4+, ou PostgreSQL 9.2+ @@ -121,7 +121,7 @@ Voir la [documentation de la ligne de commande](cli/README.md) pour plus de dét ## Contrôle d’accès Il est requis pour le mode multi-utilisateur, et recommandé dans tous les cas, de limiter l’accès à votre FreshRSS. Au choix : -* En utilisant l’identification par formulaire (requiert JavaScript, et PHP 5.5+ recommandé) +* En utilisant l’identification par formulaire (requiert JavaScript) * En utilisant un contrôle d’accès HTTP défini par votre serveur Web * Voir par exemple la [documentation d’Apache sur l’authentification](https://httpd.apache.org/docs/trunk/howto/auth.html) * Créer dans ce cas un fichier `./p/i/.htaccess` avec un fichier `.htpasswd` correspondant. diff --git a/README.md b/README.md index 6123ae903..829d0306f 100644 --- a/README.md +++ b/README.md @@ -43,7 +43,7 @@ FreshRSS comes with absolutely no warranty. * Light server running Linux or Windows * It even works on Raspberry Pi 1 with response time under a second (tested with 150 feeds, 22k articles) * A web server: Apache2 (recommended), nginx, lighttpd (not tested on others) -* PHP 5.5+ (PHP 7+ recommended for higher performance) +* PHP 5.6+ (PHP 7+ recommended for higher performance) * Required extensions: [cURL](https://secure.php.net/curl), [DOM](https://secure.php.net/dom), [XML](https://secure.php.net/xml), [session](https://secure.php.net/session), [ctype](https://secure.php.net/ctype), and [PDO_MySQL](https://secure.php.net/pdo-mysql) or [PDO_SQLite](https://secure.php.net/pdo-sqlite) or [PDO_PGSQL](https://secure.php.net/pdo-pgsql) * Recommended extensions: [JSON](https://secure.php.net/json), [GMP](https://secure.php.net/gmp) (for API access on 32-bit platforms), [IDN](https://secure.php.net/intl.idn) (for Internationalized Domain Names), [mbstring](https://secure.php.net/mbstring) (for Unicode strings), [iconv](https://secure.php.net/iconv) (for charset conversion), [ZIP](https://secure.php.net/zip) (for import/export), [zlib](https://secure.php.net/zlib) (for compressed feeds) * MySQL 5.5.3+ (recommended) or MariaDB equivalent, or SQLite 3.7.4+, or PostgreSQL 9.2+ @@ -121,7 +121,7 @@ See more commands and git commands in the [Command-Line Interface documentation] ## Access control This is needed if you will be using the multi-user mode, to limit access to FreshRSS. Options Available: -* form authentication (needs JavaScript, and PHP 5.5+ recommended) +* form authentication (needs JavaScript) * HTTP authentication supported by your web server * See [Apache documentation](https://httpd.apache.org/docs/trunk/howto/auth.html) * In that case, create a `./p/i/.htaccess` file with a matching `.htpasswd` file. diff --git a/app/install.php b/app/install.php index a92394d2a..47ec1a8a9 100644 --- a/app/install.php +++ b/app/install.php @@ -432,7 +432,7 @@ function printStep1() {

-

+

diff --git a/app/views/update/checkInstall.phtml b/app/views/update/checkInstall.phtml index e719e53dd..7ebbae869 100644 --- a/app/views/update/checkInstall.phtml +++ b/app/views/update/checkInstall.phtml @@ -9,7 +9,7 @@

Required (32-bit only): GMP
Recommanded: JSON, Zlib, mbstring, iconv, ZipArchive
*For the whole modules list see [Dockerfile](https://github.com/FreshRSS/FreshRSS/blob/master/Docker/Dockerfile-Alpine#L7-L9)* | | | Database | **MySQL 5.5.3+** | SQLite 3.7.4+ | | Browser | **Firefox** | Chrome, Opera, Safari, or IE11+ | diff --git a/docs/en/admins/05_Configuring_email_validation.md b/docs/en/admins/05_Configuring_email_validation.md index 6cc9ca8f5..fe073545c 100644 --- a/docs/en/admins/05_Configuring_email_validation.md +++ b/docs/en/admins/05_Configuring_email_validation.md @@ -6,8 +6,6 @@ by default so you'll have to follow these few steps to verify email addresses. It is intended to administrators who host users and want to be sure to be able to contact them. -Note that this feature only works with PHP >= 5.5. - ## Force email validation In your `data/config.php` file, you'll find a `force_email_validation` item: @@ -15,8 +13,7 @@ set it to `true`. An email field now appears on the registration page and emails are sent when users change their email. You can also enable this feature directly in FreshRSS: `Administration` > -`System configuration` > check `Force email addresses validation`. If the -option doesn't appear, it means that you use PHP < 5.5. +`System configuration` > check `Force email addresses validation`. ## Configure the SMTP server diff --git a/docs/fr/users/01_Installation.md b/docs/fr/users/01_Installation.md index f90d52c54..619bce11b 100644 --- a/docs/fr/users/01_Installation.md +++ b/docs/fr/users/01_Installation.md @@ -7,7 +7,7 @@ Il est toutefois de votre responsabilité de vérifier que votre hébergement pe | Logiciel | Recommandé | Fonctionne aussi avec | | -------- | ----------- | --------------------- | | Serveur web | **Apache 2** | Nginx | - | PHP | **PHP 7+** | PHP 5.5+ | + | PHP | **PHP 7+** | PHP 5.6+ | | Modules PHP | Requis : libxml, cURL, PDO_MySQL, PCRE et ctype
Requis (32 bits seulement) : GMP
Recommandé : JSON, Zlib, mbstring et iconv, ZipArchive
*Pour une liste complète des modules nécessaires voir le [Dockerfile](https://github.com/FreshRSS/FreshRSS/blob/master/Docker/Dockerfile-Alpine#L7-L9)* | | | Base de données | **MySQL 5.5.3+** | SQLite 3.7.4+ | | Navigateur | **Firefox** | Chrome, Opera, Safari, or IE 11+ | diff --git a/lib/lib_install.php b/lib/lib_install.php index 6b9b33240..17defccf6 100644 --- a/lib/lib_install.php +++ b/lib/lib_install.php @@ -6,7 +6,7 @@ Minz_Configuration::register('default_system', join_path(FRESHRSS_PATH, 'config. Minz_Configuration::register('default_user', join_path(FRESHRSS_PATH, 'config-user.default.php')); function checkRequirements($dbType = '') { - $php = version_compare(PHP_VERSION, '5.5.0') >= 0; + $php = version_compare(PHP_VERSION, '5.6.0') >= 0; $minz = file_exists(join_path(LIB_PATH, 'Minz')); $curl = extension_loaded('curl'); $pdo_mysql = extension_loaded('pdo_mysql'); diff --git a/lib/lib_rss.php b/lib/lib_rss.php index 03cfb82ad..b810e1296 100644 --- a/lib/lib_rss.php +++ b/lib/lib_rss.php @@ -1,6 +1,6 @@ Date: Sun, 29 Sep 2019 16:22:50 +0200 Subject: 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 --- app/Controllers/configureController.php | 3 +- app/Controllers/feedController.php | 2 +- app/Controllers/userController.php | 13 +- app/Models/Category.php | 7 +- app/Models/CategoryDAO.php | 104 ++++---- app/Models/DatabaseDAO.php | 92 ++++--- app/Models/DatabaseDAOPGSQL.php | 28 +-- app/Models/DatabaseDAOSQLite.php | 26 +- app/Models/Entry.php | 12 +- app/Models/EntryDAO.php | 341 ++++++++++---------------- app/Models/EntryDAOPGSQL.php | 24 +- app/Models/EntryDAOSQLite.php | 72 +++--- app/Models/Feed.php | 17 +- app/Models/FeedDAO.php | 154 ++++++------ app/Models/FeedDAOSQLite.php | 2 +- app/Models/StatsDAO.php | 47 ++-- app/Models/StatsDAOPGSQL.php | 5 +- app/Models/StatsDAOSQLite.php | 5 +- app/Models/TagDAO.php | 106 ++++----- app/Models/TagDAOSQLite.php | 2 +- app/Models/UserDAO.php | 57 +---- app/SQL/install.sql.mysql.php | 89 +++---- app/SQL/install.sql.pgsql.php | 93 ++++---- app/SQL/install.sql.sqlite.php | 88 +++---- app/install.php | 409 ++++++++++++++------------------ app/views/helpers/category/update.phtml | 5 +- cli/do-install.php | 7 +- lib/Minz/ModelPdo.php | 132 ++++++----- lib/favicons.php | 19 +- lib/lib_install.php | 79 ++---- lib/lib_rss.php | 11 +- p/api/fever.php | 4 +- p/api/greader.php | 20 +- p/f.php | 10 +- 34 files changed, 885 insertions(+), 1200 deletions(-) (limited to 'lib/lib_install.php') diff --git a/app/Controllers/configureController.php b/app/Controllers/configureController.php index b02ad02e4..85ca9da39 100755 --- a/app/Controllers/configureController.php +++ b/app/Controllers/configureController.php @@ -167,8 +167,7 @@ class FreshRSS_configure_Controller extends Minz_ActionController { * tab and up. */ public function shortcutAction() { - global $SHORTCUT_KEYS; - $this->view->list_keys = $SHORTCUT_KEYS; + $this->view->list_keys = SHORTCUT_KEYS; if (Minz_Request::isPost()) { FreshRSS_Context::$user_conf->shortcuts = validateShortcutList(Minz_Request::param('shortcuts')); diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php index c2c0cce37..ea07d96e4 100755 --- a/app/Controllers/feedController.php +++ b/app/Controllers/feedController.php @@ -429,7 +429,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController { $feedDAO->updateLastUpdate($feed->id(), false, $mtime); if ($needFeedCacheRefresh) { - $feedDAO->updateCachedValue($feed->id()); + $feedDAO->updateCachedValues($feed->id()); } if ($entryDAO->inTransaction()) { $entryDAO->commit(); diff --git a/app/Controllers/userController.php b/app/Controllers/userController.php index ab8dfb0b2..6afc91b4e 100644 --- a/app/Controllers/userController.php +++ b/app/Controllers/userController.php @@ -211,16 +211,15 @@ class FreshRSS_user_Controller extends Minz_ActionController { } } - public static function createUser($new_user_name, $email, $passwordPlain, $apiPasswordPlain, $userConfigOverride = array(), $insertDefaultFeeds = true) { - $userConfig = array(); + public static function createUser($new_user_name, $email, $passwordPlain, $apiPasswordPlain = '', $userConfigOverride = [], $insertDefaultFeeds = true) { + $userConfig = []; $customUserConfigPath = join_path(DATA_PATH, 'config-user.custom.php'); if (file_exists($customUserConfigPath)) { $customUserConfig = include($customUserConfigPath); - } - - if (is_array($customUserConfig)) { - $userConfig = $customUserConfig; + if (is_array($customUserConfig)) { + $userConfig = $customUserConfig; + } } if (is_array($userConfigOverride)) { @@ -249,7 +248,7 @@ class FreshRSS_user_Controller extends Minz_ActionController { } if ($ok) { $newUserDAO = FreshRSS_Factory::createUserDao($new_user_name); - $ok &= $newUserDAO->createUser($userConfig['language'], $insertDefaultFeeds); + $ok &= $newUserDAO->createUser($insertDefaultFeeds); $ok &= self::updateUser($new_user_name, $email, $passwordPlain, $apiPasswordPlain); } return $ok; diff --git a/app/Models/Category.php b/app/Models/Category.php index fa711aa66..29c0e586b 100644 --- a/app/Models/Category.php +++ b/app/Models/Category.php @@ -68,8 +68,11 @@ class FreshRSS_Category extends Minz_Model { return $this->hasFeedsWithError; } - public function _id($value) { - $this->id = $value; + public function _id($id) { + $this->id = $id; + if ($id == FreshRSS_CategoryDAO::DEFAULTCATEGORYID) { + $this->_name(_t('gen.short.default_category')); + } } public function _name($value) { $this->name = trim($value); 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']; } diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php index ec84da664..8046e1b5a 100644 --- a/app/Models/DatabaseDAO.php +++ b/app/Models/DatabaseDAO.php @@ -14,19 +14,44 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { //https://dev.mysql.com/doc/refman/8.0/en/innodb-restrictions.html const LENGTH_INDEX_UNICODE = 191; + public function create() { + require_once(APP_PATH . '/SQL/install.sql.' . $this->pdo->dbType() . '.php'); + $db = FreshRSS_Context::$system_conf->db; + + try { + $sql = sprintf(SQL_CREATE_DB, empty($db['base']) ? '' : $db['base']); + return $this->pdo->exec($sql) !== false; + } catch (PDOException $e) { + $_SESSION['bd_error'] = $e->getMessage(); + syslog(LOG_DEBUG, __method__ . ' warning: ' . $e->getMessage()); + return false; + } + } + + public function testConnection() { + try { + $sql = 'SELECT 1'; + $stm = $this->pdo->query($sql); + $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); + return $res != false; + } catch (PDOException $e) { + $_SESSION['bd_error'] = $e->getMessage(); + syslog(LOG_DEBUG, __method__ . ' warning: ' . $e->getMessage()); + return false; + } + } + public function tablesAreCorrect() { - $sql = 'SHOW TABLES'; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query('SHOW TABLES'); $res = $stm->fetchAll(PDO::FETCH_ASSOC); $tables = array( - $this->prefix . 'category' => false, - $this->prefix . 'feed' => false, - $this->prefix . 'entry' => false, - $this->prefix . 'entrytmp' => false, - $this->prefix . 'tag' => false, - $this->prefix . 'entrytag' => false, + $this->pdo->prefix() . 'category' => false, + $this->pdo->prefix() . 'feed' => false, + $this->pdo->prefix() . 'entry' => false, + $this->pdo->prefix() . 'entrytmp' => false, + $this->pdo->prefix() . 'tag' => false, + $this->pdo->prefix() . 'entrytag' => false, ); foreach ($res as $value) { $tables[array_pop($value)] = true; @@ -36,10 +61,8 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { } public function getSchema($table) { - $sql = 'DESC ' . $this->prefix . $table; - $stm = $this->bd->prepare($sql); - $stm->execute(); - + $sql = 'DESC `_' . $table . '`'; + $stm = $this->pdo->query($sql); return $this->listDaoToSchema($stm->fetchAll(PDO::FETCH_ASSOC)); } @@ -119,9 +142,9 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { $values = array($db['base']); if (!$all) { $sql .= ' AND table_name LIKE ?'; - $values[] = $this->prefix . '%'; + $values[] = $this->pdo->prefix() . '%'; } - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); $stm->execute($values); $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); return $res[0]; @@ -132,29 +155,23 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { $tables = array('category', 'feed', 'entry', 'entrytmp', 'tag', 'entrytag'); foreach ($tables as $table) { - $sql = 'OPTIMIZE TABLE `' . $this->prefix . $table . '`'; //MySQL - $stm = $this->bd->prepare($sql); - $ok &= $stm != false; - if ($stm) { - $ok &= $stm->execute(); - } + $sql = 'OPTIMIZE TABLE `_' . $table . '`'; //MySQL + $ok &= ($this->pdo->exec($sql) !== false); } return $ok; } public function ensureCaseInsensitiveGuids() { $ok = true; - if ($this->bd->dbType() === 'mysql') { + if ($this->pdo->dbType() === 'mysql') { include_once(APP_PATH . '/SQL/install.sql.mysql.php'); - if (defined('SQL_UPDATE_GUID_LATIN1_BIN')) { //FreshRSS 1.12 - try { - $sql = sprintf(SQL_UPDATE_GUID_LATIN1_BIN, $this->prefix); - $stm = $this->bd->prepare($sql); - $ok = $stm->execute(); - } catch (Exception $e) { - $ok = false; - Minz_Log::error(__METHOD__ . ' error: ' . $e->getMessage()); - } + + $ok = false; + try { + $ok = $this->pdo->exec(SQL_UPDATE_GUID_LATIN1_BIN) !== false; //FreshRSS 1.12 + } catch (Exception $e) { + $ok = false; + Minz_Log::error(__METHOD__ . ' error: ' . $e->getMessage()); } } return $ok; @@ -195,7 +212,7 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { $error = 'Error: SQLite import file is not readable: ' . $filename; } elseif ($clearFirst) { $userDAO->deleteUser(); - if ($this->bd->dbType() === 'sqlite') { + if ($this->pdo->dbType() === 'sqlite') { //We cannot just delete the .sqlite file otherwise PDO gets buggy. //SQLite is the only one with database-level optimization, instead of at table level. $this->optimize(); @@ -219,18 +236,17 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { try { $sqlite = new MinzPDOSQLite('sqlite:' . $filename); - $sqlite->exec('PRAGMA foreign_keys = ON;'); } catch (Exception $e) { $error = 'Error while initialising SQLite copy: ' . $e->getMessage(); return self::stdError($error); } Minz_ModelPdo::clean(); - $userDAOSQLite = new FreshRSS_UserDAO('', '', $sqlite); - $categoryDAOSQLite = new FreshRSS_CategoryDAO('', '', $sqlite); - $feedDAOSQLite = new FreshRSS_FeedDAOSQLite('', '', $sqlite); - $entryDAOSQLite = new FreshRSS_EntryDAOSQLite('', '', $sqlite); - $tagDAOSQLite = new FreshRSS_TagDAOSQLite('', '', $sqlite); + $userDAOSQLite = new FreshRSS_UserDAO('', $sqlite); + $categoryDAOSQLite = new FreshRSS_CategoryDAO('', $sqlite); + $feedDAOSQLite = new FreshRSS_FeedDAOSQLite('', $sqlite); + $entryDAOSQLite = new FreshRSS_EntryDAOSQLite('', $sqlite); + $tagDAOSQLite = new FreshRSS_TagDAOSQLite('', $sqlite); switch ($mode) { case self::SQLITE_EXPORT: diff --git a/app/Models/DatabaseDAOPGSQL.php b/app/Models/DatabaseDAOPGSQL.php index 8582b5719..60aceacc2 100644 --- a/app/Models/DatabaseDAOPGSQL.php +++ b/app/Models/DatabaseDAOPGSQL.php @@ -13,18 +13,18 @@ class FreshRSS_DatabaseDAOPGSQL extends FreshRSS_DatabaseDAOSQLite { $db = FreshRSS_Context::$system_conf->db; $dbowner = $db['user']; $sql = 'SELECT * FROM pg_catalog.pg_tables where tableowner=?'; - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); $values = array($dbowner); $stm->execute($values); $res = $stm->fetchAll(PDO::FETCH_ASSOC); $tables = array( - $this->prefix . 'category' => false, - $this->prefix . 'feed' => false, - $this->prefix . 'entry' => false, - $this->prefix . 'entrytmp' => false, - $this->prefix . 'tag' => false, - $this->prefix . 'entrytag' => false, + $this->pdo->prefix() . 'category' => false, + $this->pdo->prefix() . 'feed' => false, + $this->pdo->prefix() . 'entry' => false, + $this->pdo->prefix() . 'entrytmp' => false, + $this->pdo->prefix() . 'tag' => false, + $this->pdo->prefix() . 'entrytag' => false, ); foreach ($res as $value) { $tables[array_pop($value)] = true; @@ -35,8 +35,8 @@ class FreshRSS_DatabaseDAOPGSQL extends FreshRSS_DatabaseDAOSQLite { public function getSchema($table) { $sql = 'select column_name as field, data_type as type, column_default as default, is_nullable as null from INFORMATION_SCHEMA.COLUMNS where table_name = ?'; - $stm = $this->bd->prepare($sql); - $stm->execute(array($this->prefix . $table)); + $stm = $this->pdo->prepare($sql); + $stm->execute(array($this->pdo->prefix() . $table)); return $this->listDaoToSchema($stm->fetchAll(PDO::FETCH_ASSOC)); } @@ -53,7 +53,7 @@ class FreshRSS_DatabaseDAOPGSQL extends FreshRSS_DatabaseDAOSQLite { $db = FreshRSS_Context::$system_conf->db; $sql = 'SELECT pg_size_pretty(pg_database_size(?))'; $values = array($db['base']); - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); $stm->execute($values); $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); return $res[0]; @@ -64,12 +64,8 @@ class FreshRSS_DatabaseDAOPGSQL extends FreshRSS_DatabaseDAOSQLite { $tables = array('category', 'feed', 'entry', 'entrytmp', 'tag', 'entrytag'); foreach ($tables as $table) { - $sql = 'VACUUM `' . $this->prefix . $table . '`'; - $stm = $this->bd->prepare($sql); - $ok &= $stm != false; - if ($stm) { - $ok &= $stm->execute(); - } + $sql = 'VACUUM `_' . $table . '`'; + $ok &= ($this->pdo->exec($sql) !== false); } return $ok; } diff --git a/app/Models/DatabaseDAOSQLite.php b/app/Models/DatabaseDAOSQLite.php index a93a209b2..11900979e 100644 --- a/app/Models/DatabaseDAOSQLite.php +++ b/app/Models/DatabaseDAOSQLite.php @@ -6,17 +6,16 @@ class FreshRSS_DatabaseDAOSQLite extends FreshRSS_DatabaseDAO { public function tablesAreCorrect() { $sql = 'SELECT name FROM sqlite_master WHERE type="table"'; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query($sql); $res = $stm->fetchAll(PDO::FETCH_ASSOC); $tables = array( - 'category' => false, - 'feed' => false, - 'entry' => false, - 'entrytmp' => false, - 'tag' => false, - 'entrytag' => false, + $this->pdo->prefix() . 'category' => false, + $this->pdo->prefix() . 'feed' => false, + $this->pdo->prefix() . 'entry' => false, + $this->pdo->prefix() . 'entrytmp' => false, + $this->pdo->prefix() . 'tag' => false, + $this->pdo->prefix() . 'entrytag' => false, ); foreach ($res as $value) { $tables[$value['name']] = true; @@ -27,9 +26,7 @@ class FreshRSS_DatabaseDAOSQLite extends FreshRSS_DatabaseDAO { public function getSchema($table) { $sql = 'PRAGMA table_info(' . $table . ')'; - $stm = $this->bd->prepare($sql); - $stm->execute(); - + $stm = $this->pdo->query($sql); return $this->listDaoToSchema($stm->fetchAll(PDO::FETCH_ASSOC)); } @@ -61,11 +58,6 @@ class FreshRSS_DatabaseDAOSQLite extends FreshRSS_DatabaseDAO { } public function optimize() { - $sql = 'VACUUM'; - $stm = $this->bd->prepare($sql); - if ($stm) { - return $stm->execute(); - } - return false; + return $this->exec('VACUUM') !== false; } } diff --git a/app/Models/Entry.php b/app/Models/Entry.php index 3bb977283..d90f828bc 100644 --- a/app/Models/Entry.php +++ b/app/Models/Entry.php @@ -327,7 +327,7 @@ class FreshRSS_Entry extends Minz_Model { } $ch = curl_init(); - curl_setopt_array($ch, array( + curl_setopt_array($ch, [ CURLOPT_URL => $url, CURLOPT_REFERER => SimplePie_Misc::url_remove_credentials($url), CURLOPT_HTTPHEADER => array('Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8'), @@ -337,13 +337,9 @@ class FreshRSS_Entry extends Minz_Model { //CURLOPT_FAILONERROR => true; CURLOPT_MAXREDIRS => 4, CURLOPT_RETURNTRANSFER => true, - )); - 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, $system_conf->curl_options); if (isset($attributes['ssl_verify'])) { curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, $attributes['ssl_verify'] ? 2 : 0); diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index b13c83d67..fbf160041 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -18,106 +18,22 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return 'hex(' . $x . ')'; } - //TODO: Move the database auto-updates to DatabaseDAO - protected function addColumn($name) { - Minz_Log::warning('FreshRSS_EntryDAO::addColumn: ' . $name); - $hasTransaction = false; - try { - $stm = null; - if ($name === 'lastSeen') { //v1.1.1 - if (!$this->bd->inTransaction()) { - $this->bd->beginTransaction(); - $hasTransaction = true; - } - $stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'entry` ADD COLUMN `lastSeen` INT(11) DEFAULT 0'); - if ($stm && $stm->execute()) { - $stm = $this->bd->prepare('CREATE INDEX entry_lastSeen_index ON `' . $this->prefix . 'entry`(`lastSeen`);'); //"IF NOT EXISTS" does not exist in MySQL 5.7 - if ($stm && $stm->execute()) { - if ($hasTransaction) { - $this->bd->commit(); - } - return true; - } - } - if ($hasTransaction) { - $this->bd->rollBack(); - } - } elseif ($name === 'hash') { //v1.1.1 - $stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'entry` ADD COLUMN hash BINARY(16)'); - return $stm && $stm->execute(); - } - } catch (Exception $e) { - Minz_Log::error('FreshRSS_EntryDAO::addColumn error: ' . $e->getMessage()); - if ($hasTransaction) { - $this->bd->rollBack(); - } - } - return false; - } - - private $triedUpdateToUtf8mb4 = false; - - //TODO: Move the database auto-updates to DatabaseDAO - protected function updateToUtf8mb4() { - if ($this->triedUpdateToUtf8mb4) { - return false; - } - $this->triedUpdateToUtf8mb4 = true; - $db = FreshRSS_Context::$system_conf->db; - if ($this->bd->dbType() === 'mysql') { - include_once(APP_PATH . '/SQL/install.sql.mysql.php'); - if (defined('SQL_UPDATE_UTF8MB4')) { - Minz_Log::warning('Updating MySQL to UTF8MB4...'); //v1.5.0 - $hadTransaction = $this->bd->inTransaction(); - if ($hadTransaction) { - $this->bd->commit(); - } - $ok = false; - try { - $sql = sprintf(SQL_UPDATE_UTF8MB4, $this->prefix, $db['base']); - $stm = $this->bd->prepare($sql); - $ok = $stm->execute(); - } catch (Exception $e) { - Minz_Log::error('FreshRSS_EntryDAO::updateToUtf8mb4 error: ' . $e->getMessage()); - } - if ($hadTransaction) { - $this->bd->beginTransaction(); - //NB: Transaction not starting. Why? (tested on PHP 7.0.8-0ubuntu and MySQL 5.7.13-0ubuntu) - } - return $ok; - } - } - return false; - } - //TODO: Move the database auto-updates to DatabaseDAO protected function createEntryTempTable() { $ok = false; - $hadTransaction = $this->bd->inTransaction(); + $hadTransaction = $this->pdo->inTransaction(); if ($hadTransaction) { - $this->bd->commit(); + $this->pdo->commit(); } try { - require_once(APP_PATH . '/SQL/install.sql.' . $this->bd->dbType() . '.php'); + require_once(APP_PATH . '/SQL/install.sql.' . $this->pdo->dbType() . '.php'); Minz_Log::warning('SQL CREATE TABLE entrytmp...'); - if (defined('SQL_CREATE_TABLE_ENTRYTMP')) { - $sql = sprintf(SQL_CREATE_TABLE_ENTRYTMP, $this->prefix); - $stm = $this->bd->prepare($sql); - $ok = $stm && $stm->execute(); - } else { - global $SQL_CREATE_TABLE_ENTRYTMP; - $ok = !empty($SQL_CREATE_TABLE_ENTRYTMP); - foreach ($SQL_CREATE_TABLE_ENTRYTMP as $instruction) { - $sql = sprintf($instruction, $this->prefix); - $stm = $this->bd->prepare($sql); - $ok &= $stm && $stm->execute(); - } - } + $ok = $this->pdo->exec(SQL_CREATE_TABLE_ENTRYTMP) !== false; } catch (Exception $e) { Minz_Log::error('FreshRSS_EntryDAO::createEntryTempTable error: ' . $e->getMessage()); } if ($hadTransaction) { - $this->bd->beginTransaction(); + $this->pdo->beginTransaction(); } return $ok; } @@ -125,14 +41,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { //TODO: Move the database auto-updates to DatabaseDAO protected function autoUpdateDb($errorInfo) { if (isset($errorInfo[0])) { - if ($errorInfo[0] === FreshRSS_DatabaseDAO::ER_BAD_FIELD_ERROR) { - //autoAddColumn - foreach (array('lastSeen', 'hash') as $column) { - if (stripos($errorInfo[2], $column) !== false) { - return $this->addColumn($column); - } - } - } elseif ($errorInfo[0] === FreshRSS_DatabaseDAO::ER_BAD_TABLE_ERROR) { + if ($errorInfo[0] === FreshRSS_DatabaseDAO::ER_BAD_TABLE_ERROR) { if (stripos($errorInfo[2], 'tag') !== false) { $tagDAO = FreshRSS_Factory::createTagDao(); return $tagDAO->createTagTable(); //v1.12.0 @@ -141,11 +50,6 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } } } - if (isset($errorInfo[1])) { - if ($errorInfo[1] == FreshRSS_DatabaseDAO::ER_TRUNCATED_WRONG_VALUE_FOR_FIELD) { - return $this->updateToUtf8mb4(); //v1.5.0 - } - } return false; } @@ -153,7 +57,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function addEntry($valuesTmp, $useTmpTable = true) { if ($this->addEntryPrepared == null) { - $sql = 'INSERT INTO `' . $this->prefix . ($useTmpTable ? 'entrytmp' : 'entry') . '` (id, guid, title, author, ' + $sql = 'INSERT INTO `_' . ($useTmpTable ? 'entrytmp' : 'entry') . '` (id, guid, title, author, ' . ($this->isCompressed() ? 'content_bin' : 'content') . ', link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags) ' . 'VALUES(:id, :guid, :title, :author, ' @@ -161,7 +65,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { . ', :link, :date, :last_seen, ' . $this->sqlHexDecode(':hash') . ', :is_read, :is_favorite, :id_feed, :tags)'; - $this->addEntryPrepared = $this->bd->prepare($sql); + $this->addEntryPrepared = $this->pdo->prepare($sql); } if ($this->addEntryPrepared) { $this->addEntryPrepared->bindParam(':id', $valuesTmp['id']); @@ -212,22 +116,26 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function commitNewEntries() { - $sql = 'SET @rank=(SELECT MAX(id) - COUNT(*) FROM `' . $this->prefix . 'entrytmp`); ' . //MySQL-specific - 'INSERT IGNORE INTO `' . $this->prefix . 'entry` - ( - id, guid, title, author, content_bin, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags - ) ' . - 'SELECT @rank:=@rank+1 AS id, guid, title, author, content_bin, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags - FROM `' . $this->prefix . 'entrytmp` - ORDER BY date; ' . - 'DELETE FROM `' . $this->prefix . 'entrytmp` WHERE id <= @rank;'; - $hadTransaction = $this->bd->inTransaction(); + $sql = <<<'SQL' +SET @rank=(SELECT MAX(id) - COUNT(*) FROM `_entrytmp`); + +INSERT IGNORE INTO `_entry` ( + id, guid, title, author, content_bin, link, date, `lastSeen`, + hash, is_read, is_favorite, id_feed, tags +) +SELECT @rank:=@rank+1 AS id, guid, title, author, content_bin, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags +FROM `_entrytmp` +ORDER BY date; + +DELETE FROM `_entrytmp` WHERE id <= @rank;'; +SQL; + $hadTransaction = $this->pdo->inTransaction(); if (!$hadTransaction) { - $this->bd->beginTransaction(); + $this->pdo->beginTransaction(); } - $result = $this->bd->exec($sql) !== false; + $result = $this->pdo->exec($sql) !== false; if (!$hadTransaction) { - $this->bd->commit(); + $this->pdo->commit(); } return $result; } @@ -240,7 +148,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } if ($this->updateEntryPrepared === null) { - $sql = 'UPDATE `' . $this->prefix . 'entry` ' + $sql = 'UPDATE `_entry` ' . 'SET title=:title, author=:author, ' . ($this->isCompressed() ? 'content_bin=COMPRESS(:content)' : 'content=:content') . ', link=:link, date=:date, `lastSeen`=:last_seen, ' @@ -248,7 +156,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { . ', ' . ($valuesTmp['is_read'] === null ? '' : 'is_read=:is_read, ') . 'tags=:tags ' . 'WHERE id_feed=:id_feed AND guid=:guid'; - $this->updateEntryPrepared = $this->bd->prepare($sql); + $this->updateEntryPrepared = $this->pdo->prepare($sql); } $valuesTmp['guid'] = substr($valuesTmp['guid'], 0, 760); @@ -309,12 +217,12 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return 0; } FreshRSS_UserDAO::touch(); - $sql = 'UPDATE `' . $this->prefix . 'entry` ' + $sql = 'UPDATE `_entry` ' . 'SET is_favorite=? ' . 'WHERE id IN (' . str_repeat('?,', count($ids) - 1). '?)'; $values = array($is_favorite ? 1 : 0); $values = array_merge($values, $ids); - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); if ($stm && $stm->execute($values)) { return $stm->rowCount(); } else { @@ -336,11 +244,11 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { * @return boolean */ protected function updateCacheUnreads($catId = false, $feedId = false) { - $sql = 'UPDATE `' . $this->prefix . 'feed` f ' + $sql = 'UPDATE `_feed` f ' . 'LEFT OUTER JOIN (' . 'SELECT e.id_feed, ' . 'COUNT(*) AS nbUnreads ' - . 'FROM `' . $this->prefix . 'entry` e ' + . 'FROM `_entry` e ' . 'WHERE e.is_read=0 ' . 'GROUP BY e.id_feed' . ') x ON x.id_feed=f.id ' @@ -359,7 +267,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql .= ' f.category=?'; $values[] = $catId; } - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); if ($stm && $stm->execute($values)) { return true; } else { @@ -393,12 +301,12 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $affected; } - $sql = 'UPDATE `' . $this->prefix . 'entry` ' + $sql = 'UPDATE `_entry` ' . 'SET is_read=? ' . 'WHERE id IN (' . str_repeat('?,', count($ids) - 1). '?)'; $values = array($is_read ? 1 : 0); $values = array_merge($values, $ids); - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error markRead: ' . $info[2]); @@ -410,12 +318,12 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } return $affected; } else { - $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id ' + $sql = 'UPDATE `_entry` e INNER JOIN `_feed` f ON e.id_feed=f.id ' . 'SET e.is_read=?,' . 'f.`cache_nbUnreads`=f.`cache_nbUnreads`' . ($is_read ? '-' : '+') . '1 ' . 'WHERE e.id=? AND e.is_read=?'; $values = array($is_read ? 1 : 0, $ids, $is_read ? 0 : 1); - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); if ($stm && $stm->execute($values)) { return $stm->rowCount(); } else { @@ -454,7 +362,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { Minz_Log::debug('Calling markReadEntries(0) is deprecated!'); } - $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id ' + $sql = 'UPDATE `_entry` e INNER JOIN `_feed` f ON e.id_feed=f.id ' . 'SET e.is_read=? ' . 'WHERE e.is_read <> ? AND e.id <= ?'; if ($onlyFavorites) { @@ -466,7 +374,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { list($searchValues, $search) = $this->sqlListEntriesWhere('e.', $filters, $state); - $stm = $this->bd->prepare($sql . $search); + $stm = $this->pdo->prepare($sql . $search); if (!($stm && $stm->execute(array_merge($values, $searchValues)))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error markReadEntries: ' . $info[2]); @@ -497,14 +405,14 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { Minz_Log::debug('Calling markReadCat(0) is deprecated!'); } - $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id ' + $sql = 'UPDATE `_entry` e INNER JOIN `_feed` f ON e.id_feed=f.id ' . 'SET e.is_read=? ' . 'WHERE f.category=? AND e.is_read <> ? AND e.id <= ?'; $values = array($is_read ? 1 : 0, $id, $is_read ? 1 : 0, $idMax); list($searchValues, $search) = $this->sqlListEntriesWhere('e.', $filters, $state); - $stm = $this->bd->prepare($sql . $search); + $stm = $this->pdo->prepare($sql . $search); if (!($stm && $stm->execute(array_merge($values, $searchValues)))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error markReadCat: ' . $info[2]); @@ -534,39 +442,39 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $idMax = time() . '000000'; Minz_Log::debug('Calling markReadFeed(0) is deprecated!'); } - $this->bd->beginTransaction(); + $this->pdo->beginTransaction(); - $sql = 'UPDATE `' . $this->prefix . 'entry` ' + $sql = 'UPDATE `_entry` ' . 'SET is_read=? ' . 'WHERE id_feed=? AND is_read <> ? AND id <= ?'; $values = array($is_read ? 1 : 0, $id_feed, $is_read ? 1 : 0, $idMax); list($searchValues, $search) = $this->sqlListEntriesWhere('', $filters, $state); - $stm = $this->bd->prepare($sql . $search); + $stm = $this->pdo->prepare($sql . $search); if (!($stm && $stm->execute(array_merge($values, $searchValues)))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error markReadFeed: ' . $info[2] . ' with SQL: ' . $sql . $search); - $this->bd->rollBack(); + $this->pdo->rollBack(); return false; } $affected = $stm->rowCount(); if ($affected > 0) { - $sql = 'UPDATE `' . $this->prefix . 'feed` ' + $sql = 'UPDATE `_feed` ' . 'SET `cache_nbUnreads`=`cache_nbUnreads`-' . $affected - . ' WHERE id=?'; - $values = array($id_feed); - $stm = $this->bd->prepare($sql); + . ' WHERE id=:id'; + $stm = $this->pdo->prepare($sql); + $stm->bindParam(':id', $id_feed, PDO::PARAM_INT); if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error markReadFeed cache: ' . $info[2]); - $this->bd->rollBack(); + $this->pdo->rollBack(); return false; } } - $this->bd->commit(); + $this->pdo->commit(); return $affected; } @@ -583,7 +491,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { Minz_Log::debug('Calling markReadTag(0) is deprecated!'); } - $sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'entrytag` et ON et.id_entry = e.id ' + $sql = 'UPDATE `_entry` e INNER JOIN `_entrytag` et ON et.id_entry = e.id ' . 'SET e.is_read = ? ' . 'WHERE ' . ($id == '' ? '' : 'et.id_tag = ? AND ') @@ -597,7 +505,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { list($searchValues, $search) = $this->sqlListEntriesWhere('e.', $filters, $state); - $stm = $this->bd->prepare($sql . $search); + $stm = $this->pdo->prepare($sql . $search); if (!($stm && $stm->execute(array_merge($values, $searchValues)))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error markReadTag: ' . $info[2]); @@ -611,18 +519,20 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function cleanOldEntries($id_feed, $date_min, $keep = 15) { //Remember to call updateCachedValue($id_feed) or updateCachedValues() just after - $sql = 'DELETE FROM `' . $this->prefix . 'entry` ' - . 'WHERE id_feed=:id_feed AND id<=:id_max ' + $sql = 'DELETE FROM `_entry` ' + . 'WHERE id_feed=:id_feed1 AND id<=:id_max ' . 'AND is_favorite=0 ' //Do not remove favourites - . 'AND `lastSeen` < (SELECT maxLastSeen FROM (SELECT (MAX(e3.`lastSeen`)-99) AS maxLastSeen FROM `' . $this->prefix . 'entry` e3 WHERE e3.id_feed=:id_feed) recent) ' //Do not remove the most newly seen articles, plus a few seconds of tolerance - . 'AND id NOT IN (SELECT id_entry FROM `' . $this->prefix . 'entrytag`) ' //Do not purge tagged entries - . 'AND id NOT IN (SELECT id FROM (SELECT e2.id FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=:id_feed ORDER BY id DESC LIMIT :keep) keep)'; //Double select: MySQL doesn't support 'LIMIT & IN/ALL/ANY/SOME subquery' - $stm = $this->bd->prepare($sql); + . 'AND `lastSeen` < (SELECT maxLastSeen FROM (SELECT (MAX(e3.`lastSeen`)-99) AS maxLastSeen FROM `_entry` e3 WHERE e3.id_feed=:id_feed2) recent) ' //Do not remove the most newly seen articles, plus a few seconds of tolerance + . 'AND id NOT IN (SELECT id_entry FROM `_entrytag`) ' //Do not purge tagged entries + . 'AND id NOT IN (SELECT id FROM (SELECT e2.id FROM `_entry` e2 WHERE e2.id_feed=:id_feed3 ORDER BY id DESC LIMIT :keep) keep)'; //Double select: MySQL doesn't support 'LIMIT & IN/ALL/ANY/SOME subquery' + $stm = $this->pdo->prepare($sql); if ($stm) { $id_max = intval($date_min) . '000000'; - $stm->bindParam(':id_feed', $id_feed, PDO::PARAM_INT); $stm->bindParam(':id_max', $id_max, PDO::PARAM_STR); + $stm->bindParam(':id_feed1', $id_feed, PDO::PARAM_INT); + $stm->bindParam(':id_feed2', $id_feed, PDO::PARAM_INT); + $stm->bindParam(':id_feed3', $id_feed, PDO::PARAM_INT); $stm->bindParam(':keep', $keep, PDO::PARAM_INT); } @@ -642,9 +552,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'SELECT id, guid, title, author, ' . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content') . ', link, date, `lastSeen`, ' . $this->sqlHexEncode('hash') . ' AS hash, is_read, is_favorite, id_feed, tags ' - . 'FROM `' . $this->prefix . 'entry`'; - $stm = $this->bd->prepare($sql); - $stm->execute(); + . 'FROM `_entry`'; + $stm = $this->pdo->query($sql); while ($row = $stm->fetch(PDO::FETCH_ASSOC)) { yield $row; } @@ -655,15 +564,11 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'SELECT id, guid, title, author, ' . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content') . ', link, date, is_read, is_favorite, id_feed, tags ' - . 'FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid=?'; - $stm = $this->bd->prepare($sql); - - $values = array( - $id_feed, - $guid, - ); - - $stm->execute($values); + . 'FROM `_entry` WHERE id_feed=:id_feed AND guid=:guid'; + $stm = $this->pdo->prepare($sql); + $stm->bindParam(':id_feed', $id_feed, PDO::PARAM_INT); + $stm->bindParam(':guid', $guid); + $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_ASSOC); $entries = self::daoToEntries($res); return isset($entries[0]) ? $entries[0] : null; @@ -673,22 +578,21 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'SELECT id, guid, title, author, ' . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content') . ', link, date, is_read, is_favorite, id_feed, tags ' - . 'FROM `' . $this->prefix . 'entry` WHERE id=?'; - $stm = $this->bd->prepare($sql); - - $values = array($id); - - $stm->execute($values); + . 'FROM `_entry` WHERE id=:id'; + $stm = $this->pdo->prepare($sql); + $stm->bindParam(':id', $id, PDO::PARAM_INT); + $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_ASSOC); $entries = self::daoToEntries($res); return isset($entries[0]) ? $entries[0] : null; } public function searchIdByGuid($id_feed, $guid) { - $sql = 'SELECT id FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid=?'; - $stm = $this->bd->prepare($sql); - $values = array($id_feed, $guid); - $stm->execute($values); + $sql = 'SELECT id FROM `_entry` WHERE id_feed=:id_feed AND guid=:guid'; + $stm = $this->pdo->prepare($sql); + $stm->bindParam(':id_feed', $id_feed, PDO::PARAM_INT); + $stm->bindParam(':guid', $guid); + $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); return isset($res[0]) ? $res[0] : null; } @@ -872,7 +776,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $where .= '1=1 '; break; case 'ST': //Starred or tagged - $where .= 'e.is_favorite=1 OR EXISTS (SELECT et2.id_tag FROM `' . $this->prefix . 'entrytag` et2 WHERE et2.id_entry = e.id) '; + $where .= 'e.is_favorite=1 OR EXISTS (SELECT et2.id_tag FROM `_entrytag` et2 WHERE et2.id_entry = e.id) '; break; default: throw new FreshRSS_EntriesGetter_Exception('Bad type in Entry->listByType: [' . $type . ']!'); @@ -883,9 +787,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return array(array_merge($values, $searchValues), 'SELECT ' . ($type === 'T' ? 'DISTINCT ' : '') - . 'e.id FROM `' . $this->prefix . 'entry` e ' - . 'INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id ' - . ($type === 't' || $type === 'T' ? 'INNER JOIN `' . $this->prefix . 'entrytag` et ON et.id_entry = e.id ' : '') + . 'e.id FROM `_entry` e ' + . 'INNER JOIN `_feed` f ON e.id_feed = f.id ' + . ($type === 't' || $type === 'T' ? 'INNER JOIN `_entrytag` et ON et.id_entry = e.id ' : '') . 'WHERE ' . $where . $search . 'ORDER BY e.id ' . $order @@ -898,13 +802,13 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'SELECT e0.id, e0.guid, e0.title, e0.author, ' . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content') . ', e0.link, e0.date, e0.is_read, e0.is_favorite, e0.id_feed, e0.tags ' - . 'FROM `' . $this->prefix . 'entry` e0 ' + . 'FROM `_entry` e0 ' . 'INNER JOIN (' . $sql . ') e2 ON e2.id=e0.id ' . 'ORDER BY e0.id ' . $order; - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); if ($stm && $stm->execute($values)) { return $stm; } else { @@ -931,11 +835,11 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $sql = 'SELECT id, guid, title, author, ' . ($this->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content') . ', link, date, is_read, is_favorite, id_feed, tags ' - . 'FROM `' . $this->prefix . 'entry` ' + . 'FROM `_entry` ' . 'WHERE id IN (' . str_repeat('?,', count($ids) - 1). '?) ' . 'ORDER BY id ' . $order; - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); $stm->execute($ids); return self::daoToEntries($stm->fetchAll(PDO::FETCH_ASSOC)); } @@ -943,7 +847,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function listIdsWhere($type = 'a', $id = '', $state = FreshRSS_Entry::STATE_ALL, $order = 'DESC', $limit = 1, $firstId = '', $filters = null) { //For API list($values, $sql) = $this->sqlListWhere($type, $id, $state, $order, $limit, $firstId, $filters); - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); $stm->execute($values); return $stm->fetchAll(PDO::FETCH_COLUMN, 0); @@ -954,8 +858,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return array(); } $guids = array_unique($guids); - $sql = 'SELECT guid, ' . $this->sqlHexEncode('hash') . ' AS hex_hash FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; - $stm = $this->bd->prepare($sql); + $sql = 'SELECT guid, ' . $this->sqlHexEncode('hash') . ' AS hex_hash FROM `_entry` WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; + $stm = $this->pdo->prepare($sql); $values = array($id_feed); $values = array_merge($values, $guids); if ($stm && $stm->execute($values)) { @@ -980,8 +884,8 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if (count($guids) < 1) { return 0; } - $sql = 'UPDATE `' . $this->prefix . 'entry` SET `lastSeen`=? WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; - $stm = $this->bd->prepare($sql); + $sql = 'UPDATE `_entry` SET `lastSeen`=? WHERE id_feed=? AND guid IN (' . str_repeat('?,', count($guids) - 1). '?)'; + $stm = $this->pdo->prepare($sql); if ($mtime <= 0) { $mtime = time(); } @@ -1001,68 +905,67 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function countUnreadRead() { - $sql = 'SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id WHERE f.priority > 0' - . ' UNION SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id WHERE f.priority > 0 AND e.is_read=0'; - $stm = $this->bd->prepare($sql); - if ($stm == false) { + $sql = 'SELECT COUNT(e.id) AS count FROM `_entry` e INNER JOIN `_feed` f ON e.id_feed=f.id WHERE f.priority > 0' + . ' UNION SELECT COUNT(e.id) AS count FROM `_entry` e INNER JOIN `_feed` f ON e.id_feed=f.id WHERE f.priority > 0 AND e.is_read=0'; + $stm = $this->pdo->query($sql); + if ($stm === false) { return false; } - $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); rsort($res); $all = empty($res[0]) ? 0 : $res[0]; $unread = empty($res[1]) ? 0 : $res[1]; return array('all' => $all, 'unread' => $unread, 'read' => $all - $unread); } + public function count($minPriority = null) { - $sql = 'SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e'; + $sql = 'SELECT COUNT(e.id) AS count FROM `_entry` e'; if ($minPriority !== null) { - $sql .= ' INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id'; + $sql .= ' INNER JOIN `_feed` f ON e.id_feed=f.id'; $sql .= ' WHERE f.priority > ' . intval($minPriority); } - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query($sql); $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); return isset($res[0]) ? $res[0] : 0; } + public function countNotRead($minPriority = null) { - $sql = 'SELECT COUNT(e.id) AS count FROM `' . $this->prefix . 'entry` e'; + $sql = 'SELECT COUNT(e.id) AS count FROM `_entry` e'; if ($minPriority !== null) { - $sql .= ' INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed=f.id'; + $sql .= ' INNER JOIN `_feed` f ON e.id_feed=f.id'; } $sql .= ' WHERE e.is_read=0'; if ($minPriority !== null) { $sql .= ' AND f.priority > ' . intval($minPriority); } - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query($sql); $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); return $res[0]; } public function countUnreadReadFavorites() { - $sql = <<prefix}entry` AS e1 - JOIN `{$this->prefix}feed` AS f1 ON e1.id_feed = f1.id - WHERE e1.is_favorite = 1 - AND f1.priority >= :priority_normal - UNION - SELECT COUNT(e2.id) AS c - , 2 AS o - FROM `{$this->prefix}entry` AS e2 - JOIN `{$this->prefix}feed` AS f2 ON e2.id_feed = f2.id - WHERE e2.is_favorite = 1 - AND e2.is_read = 0 - AND f2.priority >= :priority_normal - ) u + $sql = <<<'SQL' +SELECT c FROM ( + SELECT COUNT(e1.id) AS c, 1 AS o + FROM `_entry` AS e1 + JOIN `_feed` AS f1 ON e1.id_feed = f1.id + WHERE e1.is_favorite = 1 + AND f1.priority >= :priority_normal1 + UNION + SELECT COUNT(e2.id) AS c, 2 AS o + FROM `_entry` AS e2 + JOIN `_feed` AS f2 ON e2.id_feed = f2.id + WHERE e2.is_favorite = 1 + AND e2.is_read = 0 + AND f2.priority >= :priority_normal2 + ) u ORDER BY o SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(array(':priority_normal' => FreshRSS_Feed::PRIORITY_NORMAL)); + $stm = $this->pdo->prepare($sql); + //Binding a value more than once is not standard and does not work with native prepared statements (e.g. MySQL) https://bugs.php.net/bug.php?id=40417 + $stm->bindValue(':priority_normal1', FreshRSS_Feed::PRIORITY_NORMAL, PDO::PARAM_INT); + $stm->bindValue(':priority_normal2', FreshRSS_Feed::PRIORITY_NORMAL, PDO::PARAM_INT); + $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); rsort($res); $all = empty($res[0]) ? 0 : $res[0]; diff --git a/app/Models/EntryDAOPGSQL.php b/app/Models/EntryDAOPGSQL.php index e90aa8332..9afea279f 100644 --- a/app/Models/EntryDAOPGSQL.php +++ b/app/Models/EntryDAOPGSQL.php @@ -35,25 +35,27 @@ class FreshRSS_EntryDAOPGSQL extends FreshRSS_EntryDAOSQLite { public function commitNewEntries() { $sql = 'DO $$ DECLARE -maxrank bigint := (SELECT MAX(id) FROM `' . $this->prefix . 'entrytmp`); -rank bigint := (SELECT maxrank - COUNT(*) FROM `' . $this->prefix . 'entrytmp`); +maxrank bigint := (SELECT MAX(id) FROM `_entrytmp`); +rank bigint := (SELECT maxrank - COUNT(*) FROM `_entrytmp`); BEGIN - INSERT INTO `' . $this->prefix . 'entry` (id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags) - (SELECT rank + row_number() OVER(ORDER BY date) AS id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags - FROM `' . $this->prefix . 'entrytmp` AS etmp + INSERT INTO `_entry` + (id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags) + (SELECT rank + row_number() OVER(ORDER BY date) AS id, guid, title, author, content, + link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags + FROM `_entrytmp` AS etmp WHERE NOT EXISTS ( - SELECT 1 FROM `' . $this->prefix . 'entry` AS ereal + SELECT 1 FROM `_entry` AS ereal WHERE (etmp.id = ereal.id) OR (etmp.id_feed = ereal.id_feed AND etmp.guid = ereal.guid)) ORDER BY date); - DELETE FROM `' . $this->prefix . 'entrytmp` WHERE id <= maxrank; + DELETE FROM `_entrytmp` WHERE id <= maxrank; END $$;'; - $hadTransaction = $this->bd->inTransaction(); + $hadTransaction = $this->pdo->inTransaction(); if (!$hadTransaction) { - $this->bd->beginTransaction(); + $this->pdo->beginTransaction(); } - $result = $this->bd->exec($sql) !== false; + $result = $this->pdo->exec($sql) !== false; if (!$hadTransaction) { - $this->bd->commit(); + $this->pdo->commit(); } return $result; } diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php index f53685e35..d9abefc4b 100644 --- a/app/Models/EntryDAOSQLite.php +++ b/app/Models/EntryDAOSQLite.php @@ -15,27 +15,19 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { } protected function autoUpdateDb($errorInfo) { - if ($tableInfo = $this->bd->query("SELECT sql FROM sqlite_master where name='tag'")) { + if ($tableInfo = $this->pdo->query("SELECT sql FROM sqlite_master where name='tag'")) { $showCreate = $tableInfo->fetchColumn(); if (stripos($showCreate, 'tag') === false) { $tagDAO = FreshRSS_Factory::createTagDao(); return $tagDAO->createTagTable(); //v1.12.0 } } - if ($tableInfo = $this->bd->query("SELECT sql FROM sqlite_master where name='entrytmp'")) { + if ($tableInfo = $this->pdo->query("SELECT sql FROM sqlite_master where name='entrytmp'")) { $showCreate = $tableInfo->fetchColumn(); if (stripos($showCreate, 'entrytmp') === false) { return $this->createEntryTempTable(); //v1.7.0 } } - if ($tableInfo = $this->bd->query("SELECT sql FROM sqlite_master where name='entry'")) { - $showCreate = $tableInfo->fetchColumn(); - foreach (array('lastSeen', 'hash') as $column) { - if (stripos($showCreate, $column) === false) { - return $this->addColumn($column); - } - } - } return false; } @@ -44,27 +36,27 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { DROP TABLE IF EXISTS `tmp`; CREATE TEMP TABLE `tmp` AS SELECT id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags - FROM `' . $this->prefix . 'entrytmp` + FROM `_entrytmp` ORDER BY date; -INSERT OR IGNORE INTO `' . $this->prefix . 'entry` +INSERT OR IGNORE INTO `_entry` (id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags) SELECT rowid + (SELECT MAX(id) - COUNT(*) FROM `tmp`) AS id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags FROM `tmp` ORDER BY date; -DELETE FROM `' . $this->prefix . 'entrytmp` WHERE id <= (SELECT MAX(id) FROM `tmp`); +DELETE FROM `_entrytmp` WHERE id <= (SELECT MAX(id) FROM `tmp`); DROP TABLE IF EXISTS `tmp`; '; - $hadTransaction = $this->bd->inTransaction(); + $hadTransaction = $this->pdo->inTransaction(); if (!$hadTransaction) { - $this->bd->beginTransaction(); + $this->pdo->beginTransaction(); } - $result = $this->bd->exec($sql) !== false; + $result = $this->pdo->exec($sql) !== false; if (!$result) { - Minz_Log::error('SQL error commitNewEntries: ' . json_encode($this->bd->errorInfo())); + Minz_Log::error('SQL error commitNewEntries: ' . json_encode($this->pdo->errorInfo())); } if (!$hadTransaction) { - $this->bd->commit(); + $this->pdo->commit(); } return $result; } @@ -74,10 +66,10 @@ DROP TABLE IF EXISTS `tmp`; } protected function updateCacheUnreads($catId = false, $feedId = false) { - $sql = 'UPDATE `' . $this->prefix . 'feed` ' + $sql = 'UPDATE `_feed` ' . 'SET `cache_nbUnreads`=(' - . 'SELECT COUNT(*) AS nbUnreads FROM `' . $this->prefix . 'entry` e ' - . 'WHERE e.id_feed=`' . $this->prefix . 'feed`.id AND e.is_read=0)'; + . 'SELECT COUNT(*) AS nbUnreads FROM `_entry` e ' + . 'WHERE e.id_feed=`_feed`.id AND e.is_read=0)'; $hasWhere = false; $values = array(); if ($feedId !== false) { @@ -92,7 +84,7 @@ DROP TABLE IF EXISTS `tmp`; $sql .= ' category=?'; $values[] = $catId; } - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); if ($stm && $stm->execute($values)) { return true; } else { @@ -126,30 +118,30 @@ DROP TABLE IF EXISTS `tmp`; return $affected; } } else { - $this->bd->beginTransaction(); - $sql = 'UPDATE `' . $this->prefix . 'entry` SET is_read=? WHERE id=? AND is_read=?'; + $this->pdo->beginTransaction(); + $sql = 'UPDATE `_entry` SET is_read=? WHERE id=? AND is_read=?'; $values = array($is_read ? 1 : 0, $ids, $is_read ? 0 : 1); - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error markRead 1: ' . $info[2]); - $this->bd->rollBack(); + $this->pdo->rollBack(); return false; } $affected = $stm->rowCount(); if ($affected > 0) { - $sql = 'UPDATE `' . $this->prefix . 'feed` SET `cache_nbUnreads`=`cache_nbUnreads`' . ($is_read ? '-' : '+') . '1 ' - . 'WHERE id=(SELECT e.id_feed FROM `' . $this->prefix . 'entry` e WHERE e.id=?)'; + $sql = 'UPDATE `_feed` SET `cache_nbUnreads`=`cache_nbUnreads`' . ($is_read ? '-' : '+') . '1 ' + . 'WHERE id=(SELECT e.id_feed FROM `_entry` e WHERE e.id=?)'; $values = array($ids); - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error markRead 2: ' . $info[2]); - $this->bd->rollBack(); + $this->pdo->rollBack(); return false; } } - $this->bd->commit(); + $this->pdo->commit(); return $affected; } } @@ -182,17 +174,17 @@ DROP TABLE IF EXISTS `tmp`; Minz_Log::debug('Calling markReadEntries(0) is deprecated!'); } - $sql = 'UPDATE `' . $this->prefix . 'entry` SET is_read = ? WHERE is_read <> ? AND id <= ?'; + $sql = 'UPDATE `_entry` SET is_read = ? WHERE is_read <> ? AND id <= ?'; if ($onlyFavorites) { $sql .= ' AND is_favorite=1'; } elseif ($priorityMin >= 0) { - $sql .= ' AND id_feed IN (SELECT f.id FROM `' . $this->prefix . 'feed` f WHERE f.priority > ' . intval($priorityMin) . ')'; + $sql .= ' AND id_feed IN (SELECT f.id FROM `_feed` f WHERE f.priority > ' . intval($priorityMin) . ')'; } $values = array($is_read ? 1 : 0, $is_read ? 1 : 0, $idMax); list($searchValues, $search) = $this->sqlListEntriesWhere('', $filters, $state); - $stm = $this->bd->prepare($sql . $search); + $stm = $this->pdo->prepare($sql . $search); if (!($stm && $stm->execute(array_merge($values, $searchValues)))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error markReadEntries: ' . $info[2]); @@ -223,15 +215,15 @@ DROP TABLE IF EXISTS `tmp`; Minz_Log::debug('Calling markReadCat(0) is deprecated!'); } - $sql = 'UPDATE `' . $this->prefix . 'entry` ' + $sql = 'UPDATE `_entry` ' . 'SET is_read = ? ' . 'WHERE is_read <> ? AND id <= ? AND ' - . 'id_feed IN (SELECT f.id FROM `' . $this->prefix . 'feed` f WHERE f.category=?)'; + . 'id_feed IN (SELECT f.id FROM `_feed` f WHERE f.category=?)'; $values = array($is_read ? 1 : 0, $is_read ? 1 : 0, $idMax, $id); list($searchValues, $search) = $this->sqlListEntriesWhere('', $filters, $state); - $stm = $this->bd->prepare($sql . $search); + $stm = $this->pdo->prepare($sql . $search); if (!($stm && $stm->execute(array_merge($values, $searchValues)))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error markReadCat: ' . $info[2]); @@ -257,10 +249,10 @@ DROP TABLE IF EXISTS `tmp`; Minz_Log::debug('Calling markReadTag(0) is deprecated!'); } - $sql = 'UPDATE `' . $this->prefix . 'entry` e ' + $sql = 'UPDATE `_entry` e ' . 'SET e.is_read = ? ' . 'WHERE e.is_read <> ? AND e.id <= ? AND ' - . 'e.id IN (SELECT et.id_entry FROM `' . $this->prefix . 'entrytag` et ' + . 'e.id IN (SELECT et.id_entry FROM `_entrytag` et ' . ($id == '' ? '' : 'WHERE et.id = ?') . ')'; $values = array($is_read ? 1 : 0, $is_read ? 1 : 0, $idMax); @@ -270,7 +262,7 @@ DROP TABLE IF EXISTS `tmp`; list($searchValues, $search) = $this->sqlListEntriesWhere('e.', $filters, $state); - $stm = $this->bd->prepare($sql . $search); + $stm = $this->pdo->prepare($sql . $search); if (!($stm && $stm->execute(array_merge($values, $searchValues)))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error markReadTag: ' . $info[2]); diff --git a/app/Models/Feed.php b/app/Models/Feed.php index 89989236c..8aee9d62f 100644 --- a/app/Models/Feed.php +++ b/app/Models/Feed.php @@ -153,18 +153,17 @@ class FreshRSS_Feed extends Minz_Model { return $this->nbNotRead; } public function faviconPrepare() { - global $favicons_dir; require_once(LIB_PATH . '/favicons.php'); $url = $this->website; if ($url == '') { $url = $this->url; } - $txt = $favicons_dir . $this->hash() . '.txt'; + $txt = FAVICONS_DIR . $this->hash() . '.txt'; if (!file_exists($txt)) { file_put_contents($txt, $url); } if (FreshRSS_Context::$isCli) { - $ico = $favicons_dir . $this->hash() . '.ico'; + $ico = FAVICONS_DIR . $this->hash() . '.ico'; $ico_mtime = @filemtime($ico); $txt_mtime = @filemtime($txt); if ($txt_mtime != false && @@ -701,7 +700,7 @@ class FreshRSS_Feed extends Minz_Model { file_put_contents($hubFilename, json_encode($hubJson)); } $ch = curl_init(); - curl_setopt_array($ch, array( + curl_setopt_array($ch, [ CURLOPT_URL => $hubJson['hub'], CURLOPT_RETURNTRANSFER => true, CURLOPT_POSTFIELDS => http_build_query(array( @@ -712,13 +711,9 @@ class FreshRSS_Feed extends Minz_Model { )), 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 + ]); $response = curl_exec($ch); $info = curl_getinfo($ch); diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index 1dad4a834..fc914eefc 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -3,14 +3,13 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { protected function addColumn($name) { - Minz_Log::warning('FreshRSS_FeedDAO::addColumn: ' . $name); + Minz_Log::warning(__method__ . ': ' . $name); try { if ($name === 'attributes') { //v1.11.0 - $stm = $this->bd->prepare('ALTER TABLE `' . $this->prefix . 'feed` ADD COLUMN attributes TEXT'); - return $stm && $stm->execute(); + return $this->pdo->exec('ALTER TABLE `_feed` ADD COLUMN attributes TEXT') !== false; } } catch (Exception $e) { - Minz_Log::error('FreshRSS_FeedDAO::addColumn error: ' . $e->getMessage()); + Minz_Log::error(__method__ . ' error: ' . $e->getMessage()); } return false; } @@ -30,7 +29,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function addFeed($valuesTmp) { $sql = ' - INSERT INTO `' . $this->prefix . 'feed` + INSERT INTO `_feed` ( url, category, @@ -48,7 +47,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'; - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); $valuesTmp['url'] = safe_ascii($valuesTmp['url']); $valuesTmp['website'] = safe_ascii($valuesTmp['website']); @@ -73,7 +72,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { ); if ($stm && $stm->execute($values)) { - return $this->bd->lastInsertId('"' . $this->prefix . 'feed_id_seq"'); + return $this->pdo->lastInsertId('`_feed_id_seq`'); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); if ($this->autoUpdateDb($info)) { @@ -141,8 +140,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } $set = substr($set, 0, -2); - $sql = 'UPDATE `' . $this->prefix . 'feed` SET ' . $set . ' WHERE id=?'; - $stm = $this->bd->prepare($sql); + $sql = 'UPDATE `_feed` SET ' . $set . ' WHERE id=?'; + $stm = $this->pdo->prepare($sql); foreach ($valuesTmp as $v) { $values[] = $v; @@ -173,7 +172,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function updateLastUpdate($id, $inError = false, $mtime = 0) { //See also updateCachedValue() - $sql = 'UPDATE `' . $this->prefix . 'feed` ' + $sql = 'UPDATE `_feed` ' . 'SET `lastUpdate`=?, error=? ' . 'WHERE id=?'; $values = array( @@ -181,7 +180,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $inError ? 1 : 0, $id, ); - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); if ($stm && $stm->execute($values)) { return $stm->rowCount(); @@ -199,8 +198,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $newCat = $catDAO->getDefault(); } - $sql = 'UPDATE `' . $this->prefix . 'feed` SET category=? WHERE category=?'; - $stm = $this->bd->prepare($sql); + $sql = 'UPDATE `_feed` SET category=? WHERE category=?'; + $stm = $this->pdo->prepare($sql); $values = array( $newCat->id(), @@ -217,8 +216,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function deleteFeed($id) { - $sql = 'DELETE FROM `' . $this->prefix . 'feed` WHERE id=?'; - $stm = $this->bd->prepare($sql); + $sql = 'DELETE FROM `_feed` WHERE id=?'; + $stm = $this->pdo->prepare($sql); $values = array($id); @@ -231,8 +230,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } } public function deleteFeedByCategory($id) { - $sql = 'DELETE FROM `' . $this->prefix . 'feed` WHERE category=?'; - $stm = $this->bd->prepare($sql); + $sql = 'DELETE FROM `_feed` WHERE category=?'; + $stm = $this->pdo->prepare($sql); $values = array($id); @@ -248,17 +247,16 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function selectAll() { $sql = 'SELECT id, url, category, name, website, description, `lastUpdate`, priority, ' . '`pathEntries`, `httpAuth`, error, keep_history, ttl, attributes ' - . 'FROM `' . $this->prefix . 'feed`'; - $stm = $this->bd->prepare($sql); - $stm->execute(); + . 'FROM `_feed`'; + $stm = $this->pdo->query($sql); while ($row = $stm->fetch(PDO::FETCH_ASSOC)) { yield $row; } } public function searchById($id) { - $sql = 'SELECT * FROM `' . $this->prefix . 'feed` WHERE id=?'; - $stm = $this->bd->prepare($sql); + $sql = 'SELECT * FROM `_feed` WHERE id=?'; + $stm = $this->pdo->prepare($sql); $values = array($id); @@ -273,8 +271,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } } public function searchByUrl($url) { - $sql = 'SELECT * FROM `' . $this->prefix . 'feed` WHERE url=?'; - $stm = $this->bd->prepare($sql); + $sql = 'SELECT * FROM `_feed` WHERE url=?'; + $stm = $this->pdo->prepare($sql); $values = array($url); @@ -290,25 +288,21 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function listFeedsIds() { - $sql = 'SELECT id FROM `' . $this->prefix . 'feed`'; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $sql = 'SELECT id FROM `_feed`'; + $stm = $this->pdo->query($sql); return $stm->fetchAll(PDO::FETCH_COLUMN, 0); } public function listFeeds() { - $sql = 'SELECT * FROM `' . $this->prefix . 'feed` ORDER BY name'; - $stm = $this->bd->prepare($sql); - $stm->execute(); - + $sql = 'SELECT * FROM `_feed` ORDER BY name'; + $stm = $this->pdo->query($sql); return self::daoToFeed($stm->fetchAll(PDO::FETCH_ASSOC)); } public function arrayFeedCategoryNames() { //For API - $sql = 'SELECT f.id, f.name, c.name as c_name FROM `' . $this->prefix . 'feed` f ' - . 'INNER JOIN `' . $this->prefix . 'category` c ON c.id = f.category'; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $sql = 'SELECT f.id, f.name, c.name as c_name FROM `_feed` f ' + . 'INNER JOIN `_category` c ON c.id = f.category'; + $stm = $this->pdo->query($sql); $res = $stm->fetchAll(PDO::FETCH_ASSOC); $feedCategoryNames = array(); foreach ($res as $line) { @@ -326,13 +320,14 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function listFeedsOrderUpdate($defaultCacheDuration = 3600, $limit = 0) { $this->updateTTL(); $sql = 'SELECT id, url, name, website, `lastUpdate`, `pathEntries`, `httpAuth`, keep_history, ttl, attributes ' - . 'FROM `' . $this->prefix . 'feed` ' + . 'FROM `_feed` ' . ($defaultCacheDuration < 0 ? '' : 'WHERE ttl >= ' . FreshRSS_Feed::TTL_DEFAULT - . ' AND `lastUpdate` < (' . (time() + 60) . '-(CASE WHEN ttl=' . FreshRSS_Feed::TTL_DEFAULT . ' THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ') + . ' AND `lastUpdate` < (' . (time() + 60) + . '-(CASE WHEN ttl=' . FreshRSS_Feed::TTL_DEFAULT . ' THEN ' . intval($defaultCacheDuration) . ' ELSE ttl END)) ') . 'ORDER BY `lastUpdate` ' . ($limit < 1 ? '' : 'LIMIT ' . intval($limit)); - $stm = $this->bd->prepare($sql); - if ($stm && $stm->execute()) { + $stm = $this->pdo->query($sql); + if ($stm !== false) { return self::daoToFeed($stm->fetchAll(PDO::FETCH_ASSOC)); } else { $info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo(); @@ -345,8 +340,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function listByCategory($cat) { - $sql = 'SELECT * FROM `' . $this->prefix . 'feed` WHERE category=? ORDER BY name'; - $stm = $this->bd->prepare($sql); + $sql = 'SELECT * FROM `_feed` WHERE category=? ORDER BY name'; + $stm = $this->pdo->prepare($sql); $values = array($cat); @@ -356,8 +351,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function countEntries($id) { - $sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'entry` WHERE id_feed=?'; - $stm = $this->bd->prepare($sql); + $sql = 'SELECT COUNT(*) AS count FROM `_entry` WHERE id_feed=?'; + $stm = $this->pdo->prepare($sql); $values = array($id); $stm->execute($values); $res = $stm->fetchAll(PDO::FETCH_ASSOC); @@ -366,8 +361,8 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function countNotRead($id) { - $sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND is_read=0'; - $stm = $this->bd->prepare($sql); + $sql = 'SELECT COUNT(*) AS count FROM `_entry` WHERE id_feed=? AND is_read=0'; + $stm = $this->pdo->prepare($sql); $values = array($id); $stm->execute($values); $res = $stm->fetchAll(PDO::FETCH_ASSOC); @@ -375,62 +370,51 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { return $res[0]['count']; } - public function updateCachedValue($id) { //For multiple feeds, call updateCachedValues() - $sql = 'UPDATE `' . $this->prefix . 'feed` ' //2 sub-requests with FOREIGN KEY(e.id_feed), INDEX(e.is_read) faster than 1 request with GROUP BY or CASE - . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' - . '`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0) ' - . 'WHERE id=?'; - $values = array($id); - $stm = $this->bd->prepare($sql); - - if ($stm && $stm->execute($values)) { - return $stm->rowCount(); - } else { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::error('SQL error updateCachedValue: ' . $info[2]); - return false; + public function updateCachedValues($id = null) { + //2 sub-requests with FOREIGN KEY(e.id_feed), INDEX(e.is_read) faster than 1 request with GROUP BY or CASE + $sql = 'UPDATE `_feed` ' + . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `_entry` e1 WHERE e1.id_feed=`_feed`.id),' + . '`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `_entry` e2 WHERE e2.id_feed=`_feed`.id AND e2.is_read=0)' + . ($id != null ? ' WHERE id=:id' : ''); + $stm = $this->pdo->prepare($sql); + if ($id != null) { + $stm->bindParam(':id', $id, PDO::PARAM_INT); } - } - public function updateCachedValues() { //For one single feed, call updateCachedValue($id) - $sql = 'UPDATE `' . $this->prefix . 'feed` ' - . 'SET `cache_nbEntries`=(SELECT COUNT(e1.id) FROM `' . $this->prefix . 'entry` e1 WHERE e1.id_feed=`' . $this->prefix . 'feed`.id),' - . '`cache_nbUnreads`=(SELECT COUNT(e2.id) FROM `' . $this->prefix . 'entry` e2 WHERE e2.id_feed=`' . $this->prefix . 'feed`.id AND e2.is_read=0)'; - $stm = $this->bd->prepare($sql); if ($stm && $stm->execute()) { return $stm->rowCount(); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::error('SQL error updateCachedValues: ' . $info[2]); + Minz_Log::error('SQL error updateCachedValue: ' . $info[2]); return false; } } public function truncate($id) { - $sql = 'DELETE FROM `' . $this->prefix . 'entry` WHERE id_feed=?'; - $stm = $this->bd->prepare($sql); - $values = array($id); - $this->bd->beginTransaction(); - if (!($stm && $stm->execute($values))) { + $sql = 'DELETE FROM `_entry` WHERE id_feed=:id'; + $stm = $this->pdo->prepare($sql); + $stm->bindParam(':id', $id, PDO::PARAM_INT); + $this->pdo->beginTransaction(); + if (!($stm && $stm->execute())) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error truncate: ' . $info[2]); - $this->bd->rollBack(); + $this->pdo->rollBack(); return false; } $affected = $stm->rowCount(); - $sql = 'UPDATE `' . $this->prefix . 'feed` ' - . 'SET `cache_nbEntries`=0, `cache_nbUnreads`=0 WHERE id=?'; - $values = array($id); - $stm = $this->bd->prepare($sql); + $sql = 'UPDATE `_feed` ' + . 'SET `cache_nbEntries`=0, `cache_nbUnreads`=0 WHERE id=:id'; + $stm = $this->pdo->prepare($sql); + $stm->bindParam(':id', $id, PDO::PARAM_INT); if (!($stm && $stm->execute($values))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error truncate: ' . $info[2]); - $this->bd->rollBack(); + $this->pdo->rollBack(); return false; } - $this->bd->commit(); + $this->pdo->commit(); return $affected; } @@ -479,19 +463,15 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function updateTTL() { - $sql = <<prefix}feed` - SET ttl = :new_value - WHERE ttl = :old_value -SQL; - $stm = $this->bd->prepare($sql); + $sql = 'UPDATE `_feed` SET ttl=:new_value WHERE ttl=:old_value'; + $stm = $this->pdo->prepare($sql); if (!($stm && $stm->execute(array(':new_value' => FreshRSS_Feed::TTL_DEFAULT, ':old_value' => -2)))) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL warning updateTTL 1: ' . $info[2] . ' ' . $sql); - $sql2 = 'ALTER TABLE `' . $this->prefix . 'feed` ADD COLUMN ttl INT NOT NULL DEFAULT ' . FreshRSS_Feed::TTL_DEFAULT; //v0.7.3 - $stm = $this->bd->prepare($sql2); - if (!($stm && $stm->execute())) { + $sql2 = 'ALTER TABLE `_feed` ADD COLUMN ttl INT NOT NULL DEFAULT ' . FreshRSS_Feed::TTL_DEFAULT; //v0.7.3 + $stm = $this->pdo->query($sql2); + if ($stm === false) { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error updateTTL 2: ' . $info[2] . ' ' . $sql2); } diff --git a/app/Models/FeedDAOSQLite.php b/app/Models/FeedDAOSQLite.php index 3c203b378..c56447df6 100644 --- a/app/Models/FeedDAOSQLite.php +++ b/app/Models/FeedDAOSQLite.php @@ -3,7 +3,7 @@ class FreshRSS_FeedDAOSQLite extends FreshRSS_FeedDAO { protected function autoUpdateDb($errorInfo) { - if ($tableInfo = $this->bd->query("PRAGMA table_info('feed')")) { + if ($tableInfo = $this->pdo->query("PRAGMA table_info('feed')")) { $columns = $tableInfo->fetchAll(PDO::FETCH_COLUMN, 1); foreach (array('attributes') as $column) { if (!in_array($column, $columns)) { diff --git a/app/Models/StatsDAO.php b/app/Models/StatsDAO.php index 67ada73f7..cbfa79c61 100644 --- a/app/Models/StatsDAO.php +++ b/app/Models/StatsDAO.php @@ -45,13 +45,11 @@ SELECT COUNT(1) AS total, COUNT(1) - SUM(e.is_read) AS count_unreads, SUM(e.is_read) AS count_reads, SUM(e.is_favorite) AS count_favorites -FROM `{$this->prefix}entry` AS e -, `{$this->prefix}feed` AS f +FROM `_entry` AS e, `_feed` AS f WHERE e.id_feed = f.id {$filter} SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query($sql); $res = $stm->fetchAll(PDO::FETCH_ASSOC); return $res[0]; @@ -73,13 +71,12 @@ SQL; $sql = <<prefix}entry` +FROM `_entry` WHERE date >= {$oldest} AND date < {$midnight} GROUP BY day ORDER BY day ASC SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query($sql); $res = $stm->fetchAll(PDO::FETCH_ASSOC); foreach ($res as $value) { @@ -143,14 +140,13 @@ SQL; $sql = <<prefix}entry` AS e +FROM `_entry` AS e {$restrict} GROUP BY period ORDER BY period ASC SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query($sql); $res = $stm->fetchAll(PDO::FETCH_NAMED); $repartition = array(); @@ -207,11 +203,10 @@ SQL; SELECT COUNT(1) AS count , MIN(date) AS date_min , MAX(date) AS date_max -FROM `{$this->prefix}entry` AS e +FROM `_entry` AS e {$restrict} SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query($sql); $res = $stm->fetch(PDO::FETCH_NAMED); $date_min = new \DateTime(); $date_min->setTimestamp($res['date_min']); @@ -251,14 +246,12 @@ SQL; $sql = <<prefix}category` AS c, -`{$this->prefix}feed` AS f +FROM `_category` AS c, `_feed` AS f WHERE c.id = f.category GROUP BY label ORDER BY data DESC SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query($sql); $res = $stm->fetchAll(PDO::FETCH_ASSOC); return $res; @@ -274,16 +267,13 @@ SQL; $sql = <<prefix}category` AS c, -`{$this->prefix}feed` AS f, -`{$this->prefix}entry` AS e +FROM `_category` AS c, `_feed` AS f, `_entry` AS e WHERE c.id = f.category AND f.id = e.id_feed GROUP BY label ORDER BY data DESC SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query($sql); $res = $stm->fetchAll(PDO::FETCH_ASSOC); return $res; @@ -300,17 +290,14 @@ SELECT f.id AS id , MAX(f.name) AS name , MAX(c.name) AS category , COUNT(e.id) AS count -FROM `{$this->prefix}category` AS c, -`{$this->prefix}feed` AS f, -`{$this->prefix}entry` AS e +FROM `_category` AS c, `_feed` AS f, `_entry` AS e WHERE c.id = f.category AND f.id = e.id_feed GROUP BY f.id ORDER BY count DESC LIMIT 10 SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query($sql); return $stm->fetchAll(PDO::FETCH_ASSOC); } @@ -325,14 +312,12 @@ SELECT MAX(f.id) as id , MAX(f.name) AS name , MAX(date) AS last_date , COUNT(*) AS nb_articles -FROM `{$this->prefix}feed` AS f, -`{$this->prefix}entry` AS e +FROM `_feed` AS f, `_entry` AS e WHERE f.id = e.id_feed GROUP BY f.id ORDER BY name SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query($sql); return $stm->fetchAll(PDO::FETCH_ASSOC); } diff --git a/app/Models/StatsDAOPGSQL.php b/app/Models/StatsDAOPGSQL.php index 1effbb64b..4a66068cb 100644 --- a/app/Models/StatsDAOPGSQL.php +++ b/app/Models/StatsDAOPGSQL.php @@ -47,14 +47,13 @@ class FreshRSS_StatsDAOPGSQL extends FreshRSS_StatsDAO { $sql = <<prefix}entry" AS e +FROM `_entry` AS e {$restrict} GROUP BY period ORDER BY period ASC SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query($sql); $res = $stm->fetchAll(PDO::FETCH_NAMED); foreach ($res as $value) { diff --git a/app/Models/StatsDAOSQLite.php b/app/Models/StatsDAOSQLite.php index 6cfc20463..f96f8f479 100644 --- a/app/Models/StatsDAOSQLite.php +++ b/app/Models/StatsDAOSQLite.php @@ -15,14 +15,13 @@ class FreshRSS_StatsDAOSQLite extends FreshRSS_StatsDAO { $sql = <<prefix}entry` AS e +FROM `_entry` AS e {$restrict} GROUP BY period ORDER BY period ASC SQL; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $stm = $this->pdo->query($sql); $res = $stm->fetchAll(PDO::FETCH_NAMED); $repartition = array(); diff --git a/app/Models/TagDAO.php b/app/Models/TagDAO.php index 11807fc32..8af3d5e34 100644 --- a/app/Models/TagDAO.php +++ b/app/Models/TagDAO.php @@ -8,36 +8,24 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function createTagTable() { $ok = false; - $hadTransaction = $this->bd->inTransaction(); + $hadTransaction = $this->pdo->inTransaction(); if ($hadTransaction) { - $this->bd->commit(); + $this->pdo->commit(); } try { - require_once(APP_PATH . '/SQL/install.sql.' . $this->bd->dbType() . '.php'); + require_once(APP_PATH . '/SQL/install.sql.' . $this->pdo->dbType() . '.php'); Minz_Log::warning('SQL ALTER GUID case sensitivity...'); $databaseDAO = FreshRSS_Factory::createDatabaseDAO(); $databaseDAO->ensureCaseInsensitiveGuids(); Minz_Log::warning('SQL CREATE TABLE tag...'); - if (defined('SQL_CREATE_TABLE_TAGS')) { - $sql = sprintf(SQL_CREATE_TABLE_TAGS, $this->prefix); - $stm = $this->bd->prepare($sql); - $ok = $stm && $stm->execute(); - } else { - global $SQL_CREATE_TABLE_TAGS; - $ok = !empty($SQL_CREATE_TABLE_TAGS); - foreach ($SQL_CREATE_TABLE_TAGS as $instruction) { - $sql = sprintf($instruction, $this->prefix); - $stm = $this->bd->prepare($sql); - $ok &= $stm && $stm->execute(); - } - } + $ok = $this->pdo->exec(SQL_CREATE_TABLE_TAGS) !== false; } catch (Exception $e) { Minz_Log::error('FreshRSS_EntryDAO::createTagTable error: ' . $e->getMessage()); } if ($hadTransaction) { - $this->bd->beginTransaction(); + $this->pdo->beginTransaction(); } return $ok; } @@ -54,10 +42,10 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function addTag($valuesTmp) { - $sql = 'INSERT INTO `' . $this->prefix . 'tag`(name, attributes) ' - . 'SELECT * FROM (SELECT TRIM(?), TRIM(?)) t2 ' //TRIM() to provide a type hint as text for PostgreSQL - . 'WHERE NOT EXISTS (SELECT 1 FROM `' . $this->prefix . 'category` WHERE name = TRIM(?))'; //No category of the same name - $stm = $this->bd->prepare($sql); + $sql = 'INSERT INTO `_tag`(name, attributes) ' + . 'SELECT * FROM (SELECT TRIM(?) as name, TRIM(?) as attributes) t2 ' //TRIM() gives a text type hint to PostgreSQL + . 'WHERE NOT EXISTS (SELECT 1 FROM `_category` WHERE name = TRIM(?))'; //No category of the same name + $stm = $this->pdo->prepare($sql); $valuesTmp['name'] = mb_strcut(trim($valuesTmp['name']), 0, 63, 'UTF-8'); $values = array( @@ -67,7 +55,7 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { ); if ($stm && $stm->execute($values)) { - return $this->bd->lastInsertId('"' . $this->prefix . 'tag_id_seq"'); + return $this->pdo->lastInsertId('`_tag_id_seq`'); } else { $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); Minz_Log::error('SQL error addTag: ' . $info[2]); @@ -88,9 +76,9 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function updateTag($id, $valuesTmp) { - $sql = 'UPDATE `' . $this->prefix . 'tag` SET name=?, attributes=? WHERE id=? ' - . 'AND NOT EXISTS (SELECT 1 FROM `' . $this->prefix . 'category` WHERE name = ?)'; //No category of the same name - $stm = $this->bd->prepare($sql); + $sql = 'UPDATE `_tag` SET name=?, attributes=? WHERE id=? ' + . 'AND NOT EXISTS (SELECT 1 FROM `_category` WHERE name = ?)'; //No category of the same name + $stm = $this->pdo->prepare($sql); $valuesTmp['name'] = mb_strcut(trim($valuesTmp['name']), 0, 63, 'UTF-8'); $values = array( @@ -124,8 +112,8 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { if ($id <= 0) { return false; } - $sql = 'DELETE FROM `' . $this->prefix . 'tag` WHERE id=?'; - $stm = $this->bd->prepare($sql); + $sql = 'DELETE FROM `_tag` WHERE id=?'; + $stm = $this->pdo->prepare($sql); $values = array($id); @@ -139,26 +127,24 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function selectAll() { - $sql = 'SELECT id, name, attributes FROM `' . $this->prefix . 'tag`'; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $sql = 'SELECT id, name, attributes FROM `_tag`'; + $stm = $this->pdo->query($sql); while ($row = $stm->fetch(PDO::FETCH_ASSOC)) { yield $row; } } public function selectEntryTag() { - $sql = 'SELECT id_tag, id_entry FROM `' . $this->prefix . 'entrytag`'; - $stm = $this->bd->prepare($sql); - $stm->execute(); + $sql = 'SELECT id_tag, id_entry FROM `_entrytag`'; + $stm = $this->pdo->query($sql); while ($row = $stm->fetch(PDO::FETCH_ASSOC)) { yield $row; } } public function searchById($id) { - $sql = 'SELECT * FROM `' . $this->prefix . 'tag` WHERE id=?'; - $stm = $this->bd->prepare($sql); + $sql = 'SELECT * FROM `_tag` WHERE id=?'; + $stm = $this->pdo->prepare($sql); $values = array($id); $stm->execute($values); $res = $stm->fetchAll(PDO::FETCH_ASSOC); @@ -167,8 +153,8 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function searchByName($name) { - $sql = 'SELECT * FROM `' . $this->prefix . 'tag` WHERE name=?'; - $stm = $this->bd->prepare($sql); + $sql = 'SELECT * FROM `_tag` WHERE name=?'; + $stm = $this->pdo->prepare($sql); $values = array($name); $stm->execute($values); $res = $stm->fetchAll(PDO::FETCH_ASSOC); @@ -179,17 +165,17 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function listTags($precounts = false) { if ($precounts) { $sql = 'SELECT t.id, t.name, count(e.id) AS unreads ' - . 'FROM `' . $this->prefix . 'tag` t ' - . 'LEFT OUTER JOIN `' . $this->prefix . 'entrytag` et ON et.id_tag = t.id ' - . 'LEFT OUTER JOIN `' . $this->prefix . 'entry` e ON et.id_entry = e.id AND e.is_read = 0 ' + . 'FROM `_tag` t ' + . 'LEFT OUTER JOIN `_entrytag` et ON et.id_tag = t.id ' + . 'LEFT OUTER JOIN `_entry` e ON et.id_entry = e.id AND e.is_read = 0 ' . 'GROUP BY t.id ' . 'ORDER BY t.name'; } else { - $sql = 'SELECT * FROM `' . $this->prefix . 'tag` ORDER BY name'; + $sql = 'SELECT * FROM `_tag` ORDER BY name'; } - $stm = $this->bd->prepare($sql); - if ($stm && $stm->execute()) { + $stm = $this->pdo->query($sql); + if ($stm !== false) { return self::daoToTag($stm->fetchAll(PDO::FETCH_ASSOC)); } else { $info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo(); @@ -202,9 +188,9 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function count() { - $sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'tag`'; - $stm = $this->bd->prepare($sql); - if ($stm && $stm->execute()) { + $sql = 'SELECT COUNT(*) AS count FROM `_tag`'; + $stm = $this->pdo->query($sql); + if ($stm !== false) { $res = $stm->fetchAll(PDO::FETCH_ASSOC); return $res[0]['count']; } else { @@ -218,8 +204,8 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function countEntries($id) { - $sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'entrytag` WHERE id_tag=?'; - $stm = $this->bd->prepare($sql); + $sql = 'SELECT COUNT(*) AS count FROM `_entrytag` WHERE id_tag=?'; + $stm = $this->pdo->prepare($sql); $values = array($id); $stm->execute($values); $res = $stm->fetchAll(PDO::FETCH_ASSOC); @@ -227,10 +213,10 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } public function countNotRead($id) { - $sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'entrytag` et ' - . 'INNER JOIN `' . $this->prefix . 'entry` e ON et.id_entry=e.id ' + $sql = 'SELECT COUNT(*) AS count FROM `_entrytag` et ' + . 'INNER JOIN `_entry` e ON et.id_entry=e.id ' . 'WHERE et.id_tag=? AND e.is_read=0'; - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); $values = array($id); $stm->execute($values); $res = $stm->fetchAll(PDO::FETCH_ASSOC); @@ -239,11 +225,11 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function tagEntry($id_tag, $id_entry, $checked = true) { if ($checked) { - $sql = 'INSERT ' . $this->sqlIgnore() . ' INTO `' . $this->prefix . 'entrytag`(id_tag, id_entry) VALUES(?, ?)'; + $sql = 'INSERT ' . $this->sqlIgnore() . ' INTO `_entrytag`(id_tag, id_entry) VALUES(?, ?)'; } else { - $sql = 'DELETE FROM `' . $this->prefix . 'entrytag` WHERE id_tag=? AND id_entry=?'; + $sql = 'DELETE FROM `_entrytag` WHERE id_tag=? AND id_entry=?'; } - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); $values = array($id_tag, $id_entry); if ($stm && $stm->execute($values)) { @@ -257,11 +243,11 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function getTagsForEntry($id_entry) { $sql = 'SELECT t.id, t.name, et.id_entry IS NOT NULL as checked ' - . 'FROM `' . $this->prefix . 'tag` t ' - . 'LEFT OUTER JOIN `' . $this->prefix . 'entrytag` et ON et.id_tag = t.id AND et.id_entry=? ' + . 'FROM `_tag` t ' + . 'LEFT OUTER JOIN `_entrytag` et ON et.id_tag = t.id AND et.id_entry=? ' . 'ORDER BY t.name'; - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); $values = array($id_entry); if ($stm && $stm->execute($values)) { @@ -283,8 +269,8 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function getTagsForEntries($entries) { $sql = 'SELECT et.id_entry, et.id_tag, t.name ' - . 'FROM `' . $this->prefix . 'tag` t ' - . 'INNER JOIN `' . $this->prefix . 'entrytag` et ON et.id_tag = t.id'; + . 'FROM `_tag` t ' + . 'INNER JOIN `_entrytag` et ON et.id_tag = t.id'; $values = array(); if (is_array($entries) && count($entries) > 0) { @@ -303,7 +289,7 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } } } - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); if ($stm && $stm->execute($values)) { return $stm->fetchAll(PDO::FETCH_ASSOC); diff --git a/app/Models/TagDAOSQLite.php b/app/Models/TagDAOSQLite.php index b1deb6c65..ca0fce7ca 100644 --- a/app/Models/TagDAOSQLite.php +++ b/app/Models/TagDAOSQLite.php @@ -7,7 +7,7 @@ class FreshRSS_TagDAOSQLite extends FreshRSS_TagDAO { } protected function autoUpdateDb($errorInfo) { - if ($tableInfo = $this->bd->query("SELECT sql FROM sqlite_master where name='tag'")) { + if ($tableInfo = $this->pdo->query("SELECT sql FROM sqlite_master where name='tag'")) { $showCreate = $tableInfo->fetchColumn(); if (stripos($showCreate, 'tag') === false) { return $this->createTagTable(); //v1.12.0 diff --git a/app/Models/UserDAO.php b/app/Models/UserDAO.php index 6292cc09f..7580de06e 100644 --- a/app/Models/UserDAO.php +++ b/app/Models/UserDAO.php @@ -1,43 +1,22 @@ bd->dbType() . '.php'); - - $currentLanguage = Minz_Translate::language(); + public function createUser($insertDefaultFeeds = false) { + require_once(APP_PATH . '/SQL/install.sql.' . $this->pdo->dbType() . '.php'); try { - if ($new_user_language != null) { - Minz_Translate::reset($new_user_language); - } - $ok = false; - if (defined('SQL_CREATE_TABLES')) { //E.g. MySQL - $sql = sprintf(SQL_CREATE_TABLES . SQL_CREATE_TABLE_ENTRYTMP . SQL_CREATE_TABLE_TAGS, $this->prefix, _t('gen.short.default_category')); - $stm = $this->bd->prepare($sql); - $ok = $stm && $stm->execute(); - } else { //E.g. SQLite - global $SQL_CREATE_TABLES, $SQL_CREATE_TABLE_ENTRYTMP, $SQL_CREATE_TABLE_TAGS; - if (is_array($SQL_CREATE_TABLES)) { - $instructions = array_merge($SQL_CREATE_TABLES, $SQL_CREATE_TABLE_ENTRYTMP, $SQL_CREATE_TABLE_TAGS); - $ok = !empty($instructions); - foreach ($instructions as $instruction) { - $sql = sprintf($instruction, $this->prefix, _t('gen.short.default_category')); - $stm = $this->bd->prepare($sql); - $ok &= ($stm && $stm->execute()); - } - } - } + $sql = SQL_CREATE_TABLES . SQL_CREATE_TABLE_ENTRYTMP . SQL_CREATE_TABLE_TAGS; + $ok = $this->pdo->exec($sql) !== false; //Note: Only exec() can take multiple statements safely. if ($ok && $insertDefaultFeeds) { $default_feeds = FreshRSS_Context::$system_conf->default_feeds; + $stm = $this->pdo->prepare(SQL_INSERT_FEED); foreach ($default_feeds as $feed) { - $sql = sprintf(SQL_INSERT_FEED, $this->prefix); - $stm = $this->bd->prepare($sql); - $parameters = array( + $parameters = [ ':url' => $feed['url'], ':name' => $feed['name'], ':website' => $feed['website'], ':description' => $feed['description'], - ); + ]; $ok &= ($stm && $stm->execute($parameters)); } } @@ -45,8 +24,6 @@ class FreshRSS_UserDAO extends Minz_ModelPdo { Minz_Log::error('Error while creating database for user: ' . $e->getMessage()); } - Minz_Translate::reset($currentLanguage); - if ($ok) { return true; } else { @@ -61,25 +38,9 @@ class FreshRSS_UserDAO extends Minz_ModelPdo { fwrite(STDERR, 'Deleting SQL data for user “' . $this->current_user . "”…\n"); } - require_once(APP_PATH . '/SQL/install.sql.' . $this->bd->dbType() . '.php'); + require_once(APP_PATH . '/SQL/install.sql.' . $this->pdo->dbType() . '.php'); - $ok = false; - if (defined('SQL_DROP_TABLES')) { //E.g. MySQL - $sql = sprintf(SQL_DROP_TABLES, $this->prefix); - $stm = $this->bd->prepare($sql); - $ok = $stm && $stm->execute(); - } else { //E.g. SQLite - global $SQL_DROP_TABLES; - if (is_array($SQL_DROP_TABLES)) { - $instructions = $SQL_DROP_TABLES; - $ok = !empty($instructions); - foreach ($instructions as $instruction) { - $sql = sprintf($instruction, $this->prefix); - $stm = $this->bd->prepare($sql); - $ok &= ($stm && $stm->execute()); - } - } - } + $ok = $this->pdo->exec(SQL_DROP_TABLES) !== false; if ($ok) { return true; diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index 0f396f701..328e8acc0 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -1,20 +1,22 @@ ' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . '; -ALTER TABLE `%1$scategory` MODIFY `name` VARCHAR(' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; -OPTIMIZE TABLE `%1$scategory`; - -ALTER TABLE `%1$sfeed` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -UPDATE `%1$sfeed` SET name=SUBSTRING(name,1,' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') WHERE LENGTH(name) > ' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . '; -ALTER TABLE `%1$sfeed` MODIFY `name` VARCHAR(' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; -ALTER TABLE `%1$sfeed` MODIFY `description` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -OPTIMIZE TABLE `%1$sfeed`; - -ALTER TABLE `%1$sentry` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -ALTER TABLE `%1$sentry` MODIFY `title` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; -ALTER TABLE `%1$sentry` MODIFY `author` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -ALTER TABLE `%1$sentry` MODIFY `tags` VARCHAR(1023) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -OPTIMIZE TABLE `%1$sentry`; -'); - -define('SQL_UPDATE_GUID_LATIN1_BIN', ' -- v1.12 -ALTER TABLE `%1$sentrytmp` MODIFY `guid` VARCHAR(760) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL; -ALTER TABLE `%1$sentry` MODIFY `guid` VARCHAR(760) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL; -'); +const SQL_UPDATE_GUID_LATIN1_BIN = <<<'SQL' +ALTER TABLE `_entrytmp` MODIFY `guid` VARCHAR(760) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL; -- v1.12 +ALTER TABLE `_entry` MODIFY `guid` VARCHAR(760) 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 ef08ca940..dfacc38e7 100644 --- a/app/SQL/install.sql.pgsql.php +++ b/app/SQL/install.sql.pgsql.php @@ -1,14 +1,15 @@ title; - $_SESSION['old_entries'] = param('old_entries', $user_default_config->old_entries); - $_SESSION['auth_type'] = param('auth_type', 'form'); - if (FreshRSS_user_Controller::checkUsername(param('default_user', ''))) { - $_SESSION['default_user'] = param('default_user', ''); - } - - $password_plain = param('passwordPlain', false); - if ($password_plain !== false && cryptAvailable()) { - $_SESSION['passwordHash'] = FreshRSS_user_Controller::hashPassword($password_plain); - } - - if (empty($_SESSION['old_entries']) || - empty($_SESSION['auth_type']) || - empty($_SESSION['default_user'])) { - return false; - } - - if ($_SESSION['auth_type'] === 'form' && empty($_SESSION['passwordHash'])) { - return false; - } - - $_SESSION['salt'] = generateSalt(); - if ((!ctype_digit($_SESSION['old_entries'])) ||($_SESSION['old_entries'] < 1)) { - $_SESSION['old_entries'] = $user_default_config->old_entries; - } - - $token = ''; - - $config_array = array( - 'language' => $_SESSION['language'], - 'theme' => $user_default_config->theme, - 'old_entries' => $_SESSION['old_entries'], - 'passwordHash' => $_SESSION['passwordHash'], - 'token' => $token, - ); - - // Create default user files but first, we delete previous data to - // avoid access right problems. - $user_dir = join_path(USERS_PATH, $_SESSION['default_user']); - $user_config_path = join_path($user_dir, 'config.php'); - - recursive_unlink($user_dir); - mkdir($user_dir); - file_put_contents($user_config_path, " $_SESSION['salt'], + $config_array = [ + 'salt' => generateSalt(), 'base_url' => $base_url, - 'title' => $_SESSION['title'], - 'default_user' => $_SESSION['default_user'], - 'auth_type' => $_SESSION['auth_type'], - 'db' => array( + 'default_user' => 'admin', + 'db' => [ 'type' => $_SESSION['bd_type'], 'host' => $_SESSION['bd_host'], 'user' => $_SESSION['bd_user'], 'password' => $_SESSION['bd_password'], 'base' => $_SESSION['bd_base'], 'prefix' => $_SESSION['bd_prefix'], - 'pdo_options' => array(), - ), + 'pdo_options' => [], + ], 'pubsubhubbub_enabled' => server_is_public($base_url), - ); + ]; + if (!empty($_SESSION['title'])) { + $config_array['title'] = $_SESSION['title']; + } + if (!empty($_SESSION['auth_type'])) { + $config_array['auth_type'] = $_SESSION['auth_type']; + } + + @unlink(DATA_PATH . '/config.php'); //To avoid access-rights problems + file_put_contents(DATA_PATH . '/config.php', "title; + $_SESSION['old_entries'] = param('old_entries', $user_default_config->old_entries); + $_SESSION['auth_type'] = param('auth_type', 'form'); + if (FreshRSS_user_Controller::checkUsername(param('default_user', ''))) { + $_SESSION['default_user'] = param('default_user', ''); + } + + if (empty($_SESSION['old_entries']) || + empty($_SESSION['auth_type']) || + empty($_SESSION['default_user'])) { + return false; + } + + $password_plain = param('passwordPlain', false); + if ($_SESSION['auth_type'] === 'form' && $password_plain == '') { + return false; + } + + Minz_Configuration::register('system', DATA_PATH . '/config.php', FRESHRSS_PATH . '/config.default.php'); + FreshRSS_Context::$system_conf = Minz_Configuration::get('system'); + Minz_Translate::init($_SESSION['language']); + + FreshRSS_Context::$system_conf->default_user = $_SESSION['default_user']; + FreshRSS_Context::$system_conf->save(); + + if ((!ctype_digit($_SESSION['old_entries'])) ||($_SESSION['old_entries'] < 1)) { + $_SESSION['old_entries'] = $user_default_config->old_entries; + } + + // Create default user files but first, we delete previous data to + // avoid access right problems. + recursive_unlink(USERS_PATH . '/' . $_SESSION['default_user']); + + $ok = false; + try { + $ok = FreshRSS_user_Controller::createUser( + $_SESSION['default_user'], + '', //TODO: Add e-mail + $password_plain, + '', + [ + 'language' => $_SESSION['language'], + 'old_entries' => $_SESSION['old_entries'], + ] + ); + } catch (Exception $e) { + $_SESSION['bd_error'] = $e->getMessage(); + $ok = false; + } + if (!$ok) { + return false; + } + + header('Location: index.php?step=4'); + } +} /*** VÉRIFICATIONS ***/ function checkStep() { @@ -297,29 +291,6 @@ function freshrss_already_installed() { } function checkStep2() { - $conf = !empty($_SESSION['old_entries']) && - !empty($_SESSION['default_user']); - - $form = ( - isset($_SESSION['auth_type']) && - ($_SESSION['auth_type'] != 'form' || !empty($_SESSION['passwordHash'])) - ); - - $defaultUser = empty($_POST['default_user']) ? null : $_POST['default_user']; - if ($defaultUser === null) { - $defaultUser = empty($_SESSION['default_user']) ? '' : $_SESSION['default_user']; - } - $data = is_writable(join_path(USERS_PATH, $defaultUser, 'config.php')); - - return array( - 'conf' => $conf ? 'ok' : 'ko', - 'form' => $form ? 'ok' : 'ko', - 'data' => $data ? 'ok' : 'ko', - 'all' => $conf && $form && $data ? 'ok' : 'ko' - ); -} - -function checkStep3() { $conf = is_writable(join_path(DATA_PATH, 'config.php')); $bd = isset($_SESSION['bd_type']) && @@ -331,61 +302,35 @@ function checkStep3() { isset($_SESSION['bd_error']); $conn = empty($_SESSION['bd_error']); - return array( + return [ 'bd' => $bd ? 'ok' : 'ko', 'conn' => $conn ? 'ok' : 'ko', 'conf' => $conf ? 'ok' : 'ko', - 'all' => $bd && $conn && $conf ? 'ok' : 'ko' - ); + 'all' => $bd && $conn && $conf ? 'ok' : 'ko', + ]; } -function checkDbUser(&$dbOptions) { - $ok = false; - $str = $dbOptions['dsn']; - $driver_options = $dbOptions['options']; - try { - $c = new PDO($str, $dbOptions['user'], $dbOptions['password'], $driver_options); - if (defined('SQL_CREATE_TABLES')) { - $sql = sprintf(SQL_CREATE_TABLES . SQL_CREATE_TABLE_ENTRYTMP . SQL_CREATE_TABLE_TAGS, - $dbOptions['prefix_user'], _t('gen.short.default_category')); - $stm = $c->prepare($sql); - $ok = $stm && $stm->execute(); - } else { - global $SQL_CREATE_TABLES, $SQL_CREATE_TABLE_ENTRYTMP, $SQL_CREATE_TABLE_TAGS; - $instructions = array_merge($SQL_CREATE_TABLES, $SQL_CREATE_TABLE_ENTRYTMP, $SQL_CREATE_TABLE_TAGS); - $ok = !empty($instructions); - foreach ($instructions as $instruction) { - $sql = sprintf($instruction, $dbOptions['prefix_user'], _t('gen.short.default_category')); - $stm = $c->prepare($sql); - $ok &= $stm && $stm->execute(); - } - } +function checkStep3() { + $conf = !empty($_SESSION['old_entries']) && + !empty($_SESSION['default_user']); - Minz_Configuration::register( - 'system', - join_path(DATA_PATH, 'config.php'), - join_path(FRESHRSS_PATH, 'config.default.php') - ); - $system_conf = Minz_Configuration::get('system'); - $default_feeds = $system_conf->default_feeds; - foreach ($default_feeds as $feed) { - $sql = sprintf(SQL_INSERT_FEED, $dbOptions['prefix_user']); - $stm = $c->prepare($sql); - $parameters = array( - ':url' => $feed['url'], - ':name' => $feed['name'], - ':website' => $feed['website'], - ':description' => $feed['description'], - ); - $ok &= ($stm && $stm->execute($parameters)); - } - } catch (PDOException $e) { - $ok = false; - $dbOptions['error'] = $e->getMessage(); + $form = isset($_SESSION['auth_type']); + + $defaultUser = empty($_POST['default_user']) ? null : $_POST['default_user']; + if ($defaultUser === null) { + $defaultUser = empty($_SESSION['default_user']) ? '' : $_SESSION['default_user']; } - return $ok; + $data = is_writable(join_path(USERS_PATH, $defaultUser, 'config.php')); + + return [ + 'conf' => $conf ? 'ok' : 'ko', + 'form' => $form ? 'ok' : 'ko', + 'data' => $data ? 'ok' : 'ko', + 'all' => $conf && $form && $data ? 'ok' : 'ko', + ]; } + /*** AFFICHAGE ***/ function printStep0() { $actual = Minz_Translate::language(); @@ -544,83 +489,15 @@ function printStep1() { } function printStep2() { - $user_default_config = Minz_Configuration::get('default_user'); -?> - -

- -

- - -
- - -
- -
- -
-
- -
- -
- -
-
- -
- -
- -
-
- -
- -
-
- tabindex="5" /> - -
- - -
-
- -
-
- - - - - -
-
-
- - +

- +

-
+
@@ -685,6 +562,74 @@ function printStep3() {
+
+
+ + + + + +
+
+
+ + +

+ +

+ + +
+ + +
+ +
+ +
+
+ +
+ +
+ +
+
+ +
+ +
+ +
+
+ +
+ +
+
+ tabindex="5" /> + +
+ + +
+
+
@@ -763,8 +708,8 @@ case 5:
    • -
    • -
    • +
    • +
    • diff --git a/app/views/helpers/category/update.phtml b/app/views/helpers/category/update.phtml index a2ee3e2ef..b64bd786a 100644 --- a/app/views/helpers/category/update.phtml +++ b/app/views/helpers/category/update.phtml @@ -11,7 +11,10 @@
    - + category->id() == FreshRSS_CategoryDAO::DEFAULTCATEGORYID ? 'disabled="disabled"' : ''; + ?> />
    diff --git a/cli/do-install.php b/cli/do-install.php index fd5aa4a3c..dea5d235e 100755 --- a/cli/do-install.php +++ b/cli/do-install.php @@ -3,7 +3,7 @@ require(__DIR__ . '/_cli.php'); if (!file_exists(DATA_PATH . '/do-install.txt')) { - fail('FreshRSS looks to be already installed! Please use `./cli/reconfigure.php` instead.'); + fail('FreshRSS seems to be already installed! Please use `./cli/reconfigure.php` instead.'); } $params = array( @@ -82,10 +82,9 @@ if (file_put_contents(join_path(DATA_PATH, 'config.php'), fail('FreshRSS could not write configuration file!: ' . join_path(DATA_PATH, 'config.php')); } -$config['db']['default_user'] = $config['default_user']; -if (!checkDb($config['db'])) { +if (!checkDb()) { @unlink(join_path(DATA_PATH, 'config.php')); - fail('FreshRSS database error: ' . (empty($config['db']['error']) ? 'Unknown error' : $config['db']['error'])); + fail('FreshRSS database error: ' . (empty($_SESSION['bd_error']) ? 'Unknown error' : $_SESSION['bd_error'])); } echo '• Remember to create the default user: ', $config['default_user'] , "\n", 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 @@ 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]; } } } diff --git a/p/api/fever.php b/p/api/fever.php index b81646928..30b85dafd 100644 --- a/p/api/fever.php +++ b/p/api/fever.php @@ -95,7 +95,7 @@ class FeverDAO extends Minz_ModelPdo $sql = 'SELECT id, guid, title, author, ' . ($entryDAO->isCompressed() ? 'UNCOMPRESS(content_bin) AS content' : 'content') . ', link, date, is_read, is_favorite, id_feed ' - . 'FROM `' . $this->prefix . 'entry` WHERE'; + . 'FROM `_entry` WHERE'; if (!empty($entry_ids)) { $bindEntryIds = $this->bindParamArray('id', $entry_ids, $values); @@ -120,7 +120,7 @@ class FeverDAO extends Minz_ModelPdo $sql .= $order; $sql .= ' LIMIT 50'; - $stm = $this->bd->prepare($sql); + $stm = $this->pdo->prepare($sql); $stm->execute($values); $result = $stm->fetchAll(PDO::FETCH_ASSOC); diff --git a/p/api/greader.php b/p/api/greader.php index b6777796a..77e498524 100644 --- a/p/api/greader.php +++ b/p/api/greader.php @@ -76,12 +76,6 @@ function multiplePosts($name) { //https://bugs.php.net/bug.php?id=51633 return $result; } -class MyPDO extends Minz_ModelPdo { - function prepare($sql) { - return $this->bd->prepare(str_replace('%_', $this->prefix, $sql)); - } -} - function debugInfo() { if (function_exists('getallheaders')) { $ALL_HEADERS = getallheaders(); @@ -239,9 +233,8 @@ function userInfo() { //https://github.com/theoldreader/api#user-info function tagList() { header('Content-Type: application/json; charset=UTF-8'); - $pdo = new MyPDO(); - $stm = $pdo->prepare('SELECT c.name FROM `%_category` c'); - $stm->execute(); + $model = new Minz_ModelPdo(); + $stm = $model->pdo->query('SELECT c.name FROM `_category` c'); $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); $tags = array( @@ -277,10 +270,11 @@ function tagList() { function subscriptionList() { header('Content-Type: application/json; charset=UTF-8'); - $pdo = new MyPDO(); - $stm = $pdo->prepare('SELECT f.id, f.name, f.url, f.website, c.id as c_id, c.name as c_name FROM `%_feed` f - INNER JOIN `%_category` c ON c.id = f.category AND f.priority >= :priority_normal'); - $stm->execute(array(':priority_normal' => FreshRSS_Feed::PRIORITY_NORMAL)); + $model = new Minz_ModelPdo(); + $stm = $model->pdo->prepare('SELECT f.id, f.name, f.url, f.website, c.id as c_id, c.name as c_name FROM `_feed` f + INNER JOIN `_category` c ON c.id = f.category AND f.priority >= :priority_normal'); + $stm->bindValue(':priority_normal', FreshRSS_Feed::PRIORITY_NORMAL, PDO::PARAM_INT); + $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_ASSOC); $salt = FreshRSS_Context::$system_conf->salt; diff --git a/p/f.php b/p/f.php index b68109cd5..9947539c3 100644 --- a/p/f.php +++ b/p/f.php @@ -5,13 +5,11 @@ require(LIB_PATH . '/favicons.php'); require(LIB_PATH . '/http-conditional.php'); function show_default_favicon($cacheSeconds = 3600) { - global $default_favicon; - header('Content-Disposition: inline; filename="default_favicon.ico"'); - $default_mtime = @filemtime($default_favicon); + $default_mtime = @filemtime(DEFAULT_FAVICON); if (!httpConditional($default_mtime, $cacheSeconds, 2)) { - readfile($default_favicon); + readfile(DEFAULT_FAVICON); } } @@ -20,8 +18,8 @@ if (!ctype_xdigit($id)) { $id = '0'; } -$txt = $favicons_dir . $id . '.txt'; -$ico = $favicons_dir . $id . '.ico'; +$txt = FAVICONS_DIR . $id . '.txt'; +$ico = FAVICONS_DIR . $id . '.ico'; $ico_mtime = @filemtime($ico); $txt_mtime = @filemtime($txt); -- cgit v1.2.3