diff options
| author |  Alexandre Alapetite <alexandre@alapetite.fr>
| 2019-09-29 16:22:50 +0200 |
|---|---|---|
| committer |  GitHub <noreply@github.com>
| 2019-09-29 16:22:50 +0200 |
| commit | e3e5954394f4523850c78e80e496f1b916622677 (patch) | |
| tree | 2e20d9091735e1da1de85e273e19635f58111e0f | |
| parent | ec4307c1a64a0f60648fdd7d0a2eb819bbf12965 (diff) | |
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
34 files changed, 885 insertions, 1200 deletions
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 @@ -19,105 +19,21 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } //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 = <<<SQL - SELECT c - FROM ( - SELECT COUNT(e1.id) AS c - , 1 AS o - FROM `{$this->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 = <<<SQL -UPDATE `{$this->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 = <<<SQL SELECT {$sqlDay} AS day, COUNT(*) as count -FROM `{$this->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 = <<<SQL SELECT DATE_FORMAT(FROM_UNIXTIME(e.date), '{$period}') AS period , COUNT(1) AS count -FROM `{$this->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 = <<<SQL SELECT c.name AS label , COUNT(f.id) AS data -FROM `{$this->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 = <<<SQL SELECT c.name AS label , COUNT(e.id) AS data -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 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 = <<<SQL SELECT extract( {$period} from to_timestamp(e.date)) AS period , COUNT(1) AS count -FROM "{$this->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 = <<<SQL SELECT strftime('{$period}', e.date, 'unixepoch') AS period , COUNT(1) AS count -FROM `{$this->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 @@ <?php class FreshRSS_UserDAO extends Minz_ModelPdo { - public function createUser($new_user_language = null, $insertDefaultFeeds = false) { - require_once(APP_PATH . '/SQL/install.sql.' . $this->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 @@ <?php -define('SQL_CREATE_DB', 'CREATE DATABASE IF NOT EXISTS `%1$s` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'); +const SQL_CREATE_DB = <<<'SQL' +CREATE DATABASE IF NOT EXISTS `%1$s` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; +SQL; -define('SQL_CREATE_TABLES', ' -CREATE TABLE IF NOT EXISTS `%1$scategory` ( +const SQL_CREATE_TABLES = <<<'SQL' +CREATE TABLE IF NOT EXISTS `_category` ( `id` SMALLINT NOT NULL AUTO_INCREMENT, -- v0.7 - `name` VARCHAR(' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') NOT NULL, -- Max index length for Unicode is 191 characters (767 bytes) + `name` VARCHAR(191) NOT NULL, -- Max index length for Unicode is 191 characters (767 bytes) FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE PRIMARY KEY (`id`), UNIQUE KEY (`name`) -- v0.7 ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = INNODB; -CREATE TABLE IF NOT EXISTS `%1$sfeed` ( +CREATE TABLE IF NOT EXISTS `_feed` ( `id` SMALLINT NOT NULL AUTO_INCREMENT, -- v0.7 `url` VARCHAR(511) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `category` SMALLINT DEFAULT 0, -- v0.7 - `name` VARCHAR(' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') NOT NULL, + `name` VARCHAR(191) NOT NULL, `website` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_bin, `description` TEXT, `lastUpdate` INT(11) DEFAULT 0, -- Until year 2038 @@ -28,7 +30,7 @@ CREATE TABLE IF NOT EXISTS `%1$sfeed` ( `cache_nbEntries` INT DEFAULT 0, -- v0.7 `cache_nbUnreads` INT DEFAULT 0, -- v0.7 PRIMARY KEY (`id`), - FOREIGN KEY (`category`) REFERENCES `%1$scategory`(`id`) ON DELETE SET NULL ON UPDATE CASCADE, + FOREIGN KEY (`category`) REFERENCES `_category`(`id`) ON DELETE SET NULL ON UPDATE CASCADE, UNIQUE KEY (`url`), -- v0.7 INDEX (`name`), -- v0.7 INDEX (`priority`), -- v0.7 @@ -36,7 +38,7 @@ CREATE TABLE IF NOT EXISTS `%1$sfeed` ( ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = INNODB; -CREATE TABLE IF NOT EXISTS `%1$sentry` ( +CREATE TABLE IF NOT EXISTS `_entry` ( `id` BIGINT NOT NULL, -- v0.7 `guid` VARCHAR(760) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, -- Maximum for UNIQUE is 767B `title` VARCHAR(255) NOT NULL, @@ -51,7 +53,7 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` ( `id_feed` SMALLINT, -- v0.7 `tags` VARCHAR(1023), PRIMARY KEY (`id`), - FOREIGN KEY (`id_feed`) REFERENCES `%1$sfeed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (`id_feed`) REFERENCES `_feed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE KEY (`id_feed`,`guid`), -- v0.7 INDEX (`is_favorite`), -- v0.7 INDEX (`is_read`), -- v0.7 @@ -60,11 +62,11 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` ( ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = INNODB; -INSERT IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s"); -'); +INSERT IGNORE INTO `_category` (id, name) VALUES(1, "Uncategorized"); +SQL; -define('SQL_CREATE_TABLE_ENTRYTMP', ' -CREATE TABLE IF NOT EXISTS `%1$sentrytmp` ( -- v1.7 +const SQL_CREATE_TABLE_ENTRYTMP = <<<'SQL' +CREATE TABLE IF NOT EXISTS `_entrytmp` ( -- v1.7 `id` BIGINT NOT NULL, `guid` VARCHAR(760) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `title` VARCHAR(255) NOT NULL, @@ -79,17 +81,18 @@ CREATE TABLE IF NOT EXISTS `%1$sentrytmp` ( -- v1.7 `id_feed` SMALLINT, `tags` VARCHAR(1023), PRIMARY KEY (`id`), - FOREIGN KEY (`id_feed`) REFERENCES `%1$sfeed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (`id_feed`) REFERENCES `_feed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE KEY (`id_feed`,`guid`), INDEX (`date`) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = INNODB; -CREATE INDEX `entry_feed_read_index` ON `%1$sentry`(`id_feed`,`is_read`); -- v1.7 Located here to be auto-added -'); +-- v1.7 Located here to be auto-added +CREATE INDEX `entry_feed_read_index` ON `_entry`(`id_feed`,`is_read`); +SQL; -define('SQL_CREATE_TABLE_TAGS', ' -CREATE TABLE IF NOT EXISTS `%1$stag` ( -- v1.12 +const SQL_CREATE_TABLE_TAGS = <<<'SQL' +CREATE TABLE IF NOT EXISTS `_tag` ( -- v1.12 `id` SMALLINT NOT NULL AUTO_INCREMENT, `name` VARCHAR(63) NOT NULL, `attributes` TEXT, @@ -98,47 +101,27 @@ CREATE TABLE IF NOT EXISTS `%1$stag` ( -- v1.12 ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = INNODB; -CREATE TABLE IF NOT EXISTS `%1$sentrytag` ( -- v1.12 +CREATE TABLE IF NOT EXISTS `_entrytag` ( -- v1.12 `id_tag` SMALLINT, `id_entry` BIGINT, PRIMARY KEY (`id_tag`,`id_entry`), - FOREIGN KEY (`id_tag`) REFERENCES `%1$stag`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, - FOREIGN KEY (`id_entry`) REFERENCES `%1$sentry`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (`id_tag`) REFERENCES `_tag`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY (`id_entry`) REFERENCES `_entry`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, INDEX (`id_entry`) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = INNODB; -'); +SQL; -define( - 'SQL_INSERT_FEED', - 'INSERT IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) - VALUES(:url, 1, :name, :website, :description, 86400);' -); +const SQL_INSERT_FEED = <<<'SQL' +INSERT IGNORE INTO `_feed` (url, category, name, website, description, ttl) + VALUES(:url, 1, :name, :website, :description, 86400); +SQL; -define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS `%1$sentrytag`, `%1$stag`, `%1$sentrytmp`, `%1$sentry`, `%1$sfeed`, `%1$scategory`'); +const SQL_DROP_TABLES = <<<'SQL' +DROP TABLE IF EXISTS `_entrytag`, `_tag`, `_entrytmp`, `_entry`, `_feed`, `_category`; +SQL; -define('SQL_UPDATE_UTF8MB4', ' -ALTER DATABASE `%2$s` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- v1.5.0 - -ALTER TABLE `%1$scategory` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -UPDATE `%1$scategory` SET name=SUBSTRING(name,1,' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') WHERE LENGTH(name) > ' . 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 @@ <?php -define('SQL_CREATE_DB', 'CREATE DATABASE "%1$s" ENCODING \'UTF8\';'); +const SQL_CREATE_DB = <<<'SQL' +CREATE DATABASE "%1$s" ENCODING 'UTF8'; +SQL; -global $SQL_CREATE_TABLES; -$SQL_CREATE_TABLES = array( -'CREATE TABLE IF NOT EXISTS "%1$scategory" ( +const SQL_CREATE_TABLES = <<<'SQL' +CREATE TABLE IF NOT EXISTS `_category` ( "id" SERIAL PRIMARY KEY, "name" VARCHAR(255) UNIQUE NOT NULL -);', +); -'CREATE TABLE IF NOT EXISTS "%1$sfeed" ( +CREATE TABLE IF NOT EXISTS `_feed` ( "id" SERIAL PRIMARY KEY, "url" VARCHAR(511) UNIQUE NOT NULL, "category" SMALLINT DEFAULT 0, @@ -25,13 +26,13 @@ $SQL_CREATE_TABLES = array( "attributes" TEXT, -- v1.11.0 "cache_nbEntries" INT DEFAULT 0, "cache_nbUnreads" INT DEFAULT 0, - FOREIGN KEY ("category") REFERENCES "%1$scategory" ("id") ON DELETE SET NULL ON UPDATE CASCADE -);', -'CREATE INDEX "%1$sname_index" ON "%1$sfeed" ("name");', -'CREATE INDEX "%1$spriority_index" ON "%1$sfeed" ("priority");', -'CREATE INDEX "%1$skeep_history_index" ON "%1$sfeed" ("keep_history");', + FOREIGN KEY ("category") REFERENCES `_category` ("id") ON DELETE SET NULL ON UPDATE CASCADE +); +CREATE INDEX `_name_index` ON `_feed` ("name"); +CREATE INDEX `_priority_index` ON `_feed` ("priority"); +CREATE INDEX `_keep_history_index` ON `_feed` ("keep_history"); -'CREATE TABLE IF NOT EXISTS "%1$sentry" ( +CREATE TABLE IF NOT EXISTS `_entry` ( "id" BIGINT NOT NULL PRIMARY KEY, "guid" VARCHAR(760) NOT NULL, "title" VARCHAR(255) NOT NULL, @@ -45,22 +46,21 @@ $SQL_CREATE_TABLES = array( "is_favorite" SMALLINT NOT NULL DEFAULT 0, "id_feed" SMALLINT, "tags" VARCHAR(1023), - FOREIGN KEY ("id_feed") REFERENCES "%1$sfeed" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY ("id_feed") REFERENCES `_feed` ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("id_feed","guid") -);', -'CREATE INDEX "%1$sis_favorite_index" ON "%1$sentry" ("is_favorite");', -'CREATE INDEX "%1$sis_read_index" ON "%1$sentry" ("is_read");', -'CREATE INDEX "%1$sentry_lastSeen_index" ON "%1$sentry" ("lastSeen");', - -'INSERT INTO "%1$scategory" (id, name) - SELECT 1, \'%2$s\' - WHERE NOT EXISTS (SELECT id FROM "%1$scategory" WHERE id = 1) - RETURNING nextval(\'"%1$scategory_id_seq"\');', ); +CREATE INDEX `_is_favorite_index` ON `_entry` ("is_favorite"); +CREATE INDEX `_is_read_index` ON `_entry` ("is_read"); +CREATE INDEX `_entry_lastSeen_index` ON `_entry` ("lastSeen"); + +INSERT INTO `_category` (id, name) + SELECT 1, 'Uncategorized' + WHERE NOT EXISTS (SELECT id FROM `_category` WHERE id = 1) + RETURNING nextval('`_category_id_seq`'); +SQL; -global $SQL_CREATE_TABLE_ENTRYTMP; -$SQL_CREATE_TABLE_ENTRYTMP = array( -'CREATE TABLE IF NOT EXISTS "%1$sentrytmp" ( -- v1.7 +const SQL_CREATE_TABLE_ENTRYTMP = <<<'SQL' +CREATE TABLE IF NOT EXISTS `_entrytmp` ( -- v1.7 "id" BIGINT NOT NULL PRIMARY KEY, "guid" VARCHAR(760) NOT NULL, "title" VARCHAR(255) NOT NULL, @@ -74,36 +74,37 @@ $SQL_CREATE_TABLE_ENTRYTMP = array( "is_favorite" SMALLINT NOT NULL DEFAULT 0, "id_feed" SMALLINT, "tags" VARCHAR(1023), - FOREIGN KEY ("id_feed") REFERENCES "%1$sfeed" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY ("id_feed") REFERENCES `_feed` ("id") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("id_feed","guid") -);', -'CREATE INDEX "%1$sentrytmp_date_index" ON "%1$sentrytmp" ("date");', - -'CREATE INDEX "%1$sentry_feed_read_index" ON "%1$sentry" ("id_feed","is_read");', //v1.7 ); +CREATE INDEX `_entrytmp_date_index` ON `_entrytmp` ("date"); + +-- v1.7 +CREATE INDEX `_entry_feed_read_index` ON `_entry` ("id_feed","is_read"); +SQL; -global $SQL_CREATE_TABLE_TAGS; -$SQL_CREATE_TABLE_TAGS = array( -'CREATE TABLE IF NOT EXISTS "%1$stag" ( -- v1.12 +const SQL_CREATE_TABLE_TAGS = <<<'SQL' +CREATE TABLE IF NOT EXISTS `_tag` ( -- v1.12 "id" SERIAL PRIMARY KEY, "name" VARCHAR(63) UNIQUE NOT NULL, "attributes" TEXT -);', -'CREATE TABLE IF NOT EXISTS "%1$sentrytag" ( +); +CREATE TABLE IF NOT EXISTS `_entrytag` ( "id_tag" SMALLINT, "id_entry" BIGINT, PRIMARY KEY ("id_tag","id_entry"), - FOREIGN KEY ("id_tag") REFERENCES "%1$stag" ("id") ON DELETE CASCADE ON UPDATE CASCADE, - FOREIGN KEY ("id_entry") REFERENCES "%1$sentry" ("id") ON DELETE CASCADE ON UPDATE CASCADE -);', -'CREATE INDEX "%1$sentrytag_id_entry_index" ON "%1$sentrytag" ("id_entry");', + FOREIGN KEY ("id_tag") REFERENCES `_tag` ("id") ON DELETE CASCADE ON UPDATE CASCADE, + FOREIGN KEY ("id_entry") REFERENCES `_entry` ("id") ON DELETE CASCADE ON UPDATE CASCADE ); +CREATE INDEX `_entrytag_id_entry_index` ON `_entrytag` ("id_entry"); +SQL; -define( - 'SQL_INSERT_FEED', - 'INSERT INTO "%1$sfeed" (url, category, name, website, description, ttl) - SELECT :url::VARCHAR, 1, :name, :website, :description, 86400 - WHERE NOT EXISTS (SELECT id FROM "%1$sfeed" WHERE url = :url);' -); +const SQL_INSERT_FEED = <<<'SQL' +INSERT INTO `_feed` (url, category, name, website, description, ttl) + SELECT :url::VARCHAR, 1, :name, :website, :description, 86400 + WHERE NOT EXISTS (SELECT id FROM `_feed` WHERE url = :url); +SQL; -define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS "%1$sentrytag", "%1$stag", "%1$sentrytmp", "%1$sentry", "%1$sfeed", "%1$scategory"'); +const SQL_DROP_TABLES = <<<'SQL' +DROP TABLE IF EXISTS `_entrytag`, `_tag`, `_entrytmp`, `_entry`, `_feed`, `_category`; +SQL; diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index 88de84358..81370ce56 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -1,13 +1,16 @@ <?php -global $SQL_CREATE_TABLES; -$SQL_CREATE_TABLES = array( -'CREATE TABLE IF NOT EXISTS `category` ( +const SQL_CREATE_DB = <<<'SQL' +SELECT 1; -- Do nothing for SQLite +SQL; + +const SQL_CREATE_TABLES = <<<'SQL' +CREATE TABLE IF NOT EXISTS `category` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` VARCHAR(255) NOT NULL, UNIQUE (`name`) -);', +); -'CREATE TABLE IF NOT EXISTS `feed` ( +CREATE TABLE IF NOT EXISTS `feed` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `url` VARCHAR(511) NOT NULL, `category` SMALLINT DEFAULT 0, @@ -26,12 +29,12 @@ $SQL_CREATE_TABLES = array( `cache_nbUnreads` INT DEFAULT 0, FOREIGN KEY (`category`) REFERENCES `category`(`id`) ON DELETE SET NULL ON UPDATE CASCADE, UNIQUE (`url`) -);', -'CREATE INDEX IF NOT EXISTS feed_name_index ON `feed`(`name`);', -'CREATE INDEX IF NOT EXISTS feed_priority_index ON `feed`(`priority`);', -'CREATE INDEX IF NOT EXISTS feed_keep_history_index ON `feed`(`keep_history`);', +); +CREATE INDEX IF NOT EXISTS feed_name_index ON `feed`(`name`); +CREATE INDEX IF NOT EXISTS feed_priority_index ON `feed`(`priority`); +CREATE INDEX IF NOT EXISTS feed_keep_history_index ON `feed`(`keep_history`); -'CREATE TABLE IF NOT EXISTS `entry` ( +CREATE TABLE IF NOT EXISTS `entry` ( `id` BIGINT NOT NULL, `guid` VARCHAR(760) NOT NULL, `title` VARCHAR(255) NOT NULL, @@ -48,17 +51,16 @@ $SQL_CREATE_TABLES = array( PRIMARY KEY (`id`), FOREIGN KEY (`id_feed`) REFERENCES `feed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE (`id_feed`,`guid`) -);', -'CREATE INDEX IF NOT EXISTS entry_is_favorite_index ON `entry`(`is_favorite`);', -'CREATE INDEX IF NOT EXISTS entry_is_read_index ON `entry`(`is_read`);', -'CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `entry`(`lastSeen`);', //v1.1.1 - -'INSERT OR IGNORE INTO `category` (id, name) VALUES(1, "%2$s");', ); +CREATE INDEX IF NOT EXISTS entry_is_favorite_index ON `entry`(`is_favorite`); +CREATE INDEX IF NOT EXISTS entry_is_read_index ON `entry`(`is_read`); +CREATE INDEX IF NOT EXISTS entry_lastSeen_index ON `entry`(`lastSeen`); -- //v1.1.1 + +INSERT OR IGNORE INTO `category` (id, name) VALUES(1, "Uncategorized"); +SQL; -global $SQL_CREATE_TABLE_ENTRYTMP; -$SQL_CREATE_TABLE_ENTRYTMP = array( -'CREATE TABLE IF NOT EXISTS `entrytmp` ( -- v1.7 +const SQL_CREATE_TABLE_ENTRYTMP = <<<'SQL' +CREATE TABLE IF NOT EXISTS `entrytmp` ( -- v1.7 `id` BIGINT NOT NULL, `guid` VARCHAR(760) NOT NULL, `title` VARCHAR(255) NOT NULL, @@ -75,42 +77,40 @@ $SQL_CREATE_TABLE_ENTRYTMP = array( PRIMARY KEY (`id`), FOREIGN KEY (`id_feed`) REFERENCES `feed`(`id`) ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE (`id_feed`,`guid`) -);', -'CREATE INDEX IF NOT EXISTS entrytmp_date_index ON `entrytmp`(`date`);', - -'CREATE INDEX IF NOT EXISTS `entry_feed_read_index` ON `entry`(`id_feed`,`is_read`);', //v1.7 ); +CREATE INDEX IF NOT EXISTS entrytmp_date_index ON `entrytmp`(`date`); + +-- v1.7 +CREATE INDEX IF NOT EXISTS `entry_feed_read_index` ON `entry`(`id_feed`,`is_read`); +SQL; -global $SQL_CREATE_TABLE_TAGS; -$SQL_CREATE_TABLE_TAGS = array( -'CREATE TABLE IF NOT EXISTS `tag` ( -- v1.12 +const SQL_CREATE_TABLE_TAGS = <<<'SQL' +CREATE TABLE IF NOT EXISTS `tag` ( -- v1.12 `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` VARCHAR(63) NOT NULL, `attributes` TEXT, UNIQUE (`name`) -);', -'CREATE TABLE IF NOT EXISTS `entrytag` ( +); +CREATE TABLE IF NOT EXISTS `entrytag` ( `id_tag` SMALLINT, `id_entry` BIGINT, PRIMARY KEY (`id_tag`,`id_entry`), FOREIGN KEY (`id_tag`) REFERENCES `tag` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`id_entry`) REFERENCES `entry` (`id`) ON DELETE CASCADE ON UPDATE CASCADE -);', -'CREATE INDEX entrytag_id_entry_index ON `entrytag` (`id_entry`);', ); +CREATE INDEX entrytag_id_entry_index ON `entrytag` (`id_entry`); +SQL; -define( - 'SQL_INSERT_FEED', - 'INSERT OR IGNORE INTO `feed` (url, category, name, website, description, ttl) - VALUES(:url, 1, :name, :website, :description, 86400);' -); +const SQL_INSERT_FEED = <<<'SQL' +INSERT OR IGNORE INTO `feed` (url, category, name, website, description, ttl) + VALUES(:url, 1, :name, :website, :description, 86400); +SQL; -global $SQL_DROP_TABLES; -$SQL_DROP_TABLES = [ - 'DROP TABLE IF EXISTS `entrytag`', - 'DROP TABLE IF EXISTS `tag`', - 'DROP TABLE IF EXISTS `entrytmp`', - 'DROP TABLE IF EXISTS `entry`', - 'DROP TABLE IF EXISTS `feed`', - 'DROP TABLE IF EXISTS `category`', -]; +const SQL_DROP_TABLES = <<<'SQL' +DROP TABLE IF EXISTS `entrytag`; +DROP TABLE IF EXISTS `tag`; +DROP TABLE IF EXISTS `entrytmp`; +DROP TABLE IF EXISTS `entry`; +DROP TABLE IF EXISTS `feed`; +DROP TABLE IF EXISTS `category`; +SQL; diff --git a/app/install.php b/app/install.php index 178ae082a..92e07dd4f 100644 --- a/app/install.php +++ b/app/install.php @@ -17,24 +17,10 @@ if (isset($_GET['step'])) { define('STEP', 0); } -if (STEP === 3 && isset($_POST['type'])) { +if (STEP === 2 && isset($_POST['type'])) { $_SESSION['bd_type'] = $_POST['type']; } -if (isset($_SESSION['bd_type'])) { - switch ($_SESSION['bd_type']) { - case 'mysql': - include_once(APP_PATH . '/SQL/install.sql.mysql.php'); - break; - case 'sqlite': - include_once(APP_PATH . '/SQL/install.sql.sqlite.php'); - break; - case 'pgsql': - include_once(APP_PATH . '/SQL/install.sql.pgsql.php'); - break; - } -} - function param($key, $default = false) { if (isset($_POST[$key])) { return $_POST[$key]; @@ -43,7 +29,6 @@ function param($key, $default = false) { } } - // gestion internationalisation function initTranslate() { Minz_Translate::init(); @@ -119,68 +104,13 @@ function saveStep1() { } function saveStep2() { - $user_default_config = Minz_Configuration::get('default_user'); - if (!empty($_POST)) { - $system_default_config = Minz_Configuration::get('default_system'); - $_SESSION['title'] = $system_default_config->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, "<?php\n return " . var_export($config_array, true) . ";\n"); - - header('Location: index.php?step=3'); - } -} - -function saveStep3() { if (!empty($_POST)) { if ($_SESSION['bd_type'] === 'sqlite') { - $_SESSION['bd_base'] = $_SESSION['default_user']; + $_SESSION['bd_base'] = ''; $_SESSION['bd_host'] = ''; $_SESSION['bd_user'] = ''; $_SESSION['bd_password'] = ''; $_SESSION['bd_prefix'] = ''; - $_SESSION['bd_prefix_user'] = ''; //No prefix for SQLite } else { if (empty($_POST['type']) || empty($_POST['host']) || @@ -193,7 +123,6 @@ function saveStep3() { $_SESSION['bd_user'] = $_POST['user']; $_SESSION['bd_password'] = $_POST['pass']; $_SESSION['bd_prefix'] = substr($_POST['prefix'], 0, 16); - $_SESSION['bd_prefix_user'] = $_SESSION['bd_prefix'] . (empty($_SESSION['default_user']) ? '' : ($_SESSION['default_user'] . '_')); } if ($_SESSION['bd_type'] === 'pgsql') { $_SESSION['bd_base'] = strtolower($_SESSION['bd_base']); @@ -201,44 +130,109 @@ function saveStep3() { // We use dirname to remove the /i part $base_url = dirname(Minz_Request::guessBaseUrl()); - $config_array = array( - 'salt' => $_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', "<?php\n return " . var_export($config_array, true) . ";\n"); - @unlink(join_path(DATA_PATH, 'config.php')); //To avoid access-rights problems - file_put_contents(join_path(DATA_PATH, 'config.php'), "<?php\n return " . var_export($config_array, true) . ";\n"); + Minz_Configuration::register('system', DATA_PATH . '/config.php', FRESHRSS_PATH . '/config.default.php'); + FreshRSS_Context::$system_conf = Minz_Configuration::get('system'); - $config_array['db']['default_user'] = $config_array['default_user']; - $config_array['db']['prefix_user'] = $_SESSION['bd_prefix_user']; - $ok = checkDb($config_array['db']) && checkDbUser($config_array['db']); + $ok = checkDb(); if (!$ok) { @unlink(join_path(DATA_PATH, 'config.php')); } if ($ok) { $_SESSION['bd_error'] = ''; - header('Location: index.php?step=4'); - } else { - $_SESSION['bd_error'] = empty($config_array['db']['error']) ? 'Unknown error!' : $config_array['db']['error']; + header('Location: index.php?step=3'); + } elseif (empty($_SESSION['bd_error'])) { + $_SESSION['bd_error'] = 'Unknown error!'; } } invalidateHttpCache(); } +function saveStep3() { + $user_default_config = Minz_Configuration::get('default_user'); + if (!empty($_POST)) { + $system_default_config = Minz_Configuration::get('default_system'); + $_SESSION['title'] = $system_default_config->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'); -?> - <?php $s2 = checkStep2(); if ($s2['all'] == 'ok') { ?> - <p class="alert alert-success"><span class="alert-head"><?php echo _t('gen.short.ok'); ?></span> <?php echo _t('install.conf.ok'); ?></p> - <?php } elseif (!empty($_POST)) { ?> - <p class="alert alert-error"><?php echo _t('install.fix_errors_before'); ?></p> - <?php } ?> - - <form action="index.php?step=2" method="post"> - <legend><?php echo _t('install.conf'); ?></legend> - - <div class="form-group"> - <label class="group-name" for="old_entries"><?php echo _t('install.delete_articles_after'); ?></label> - <div class="group-controls"> - <input type="number" id="old_entries" name="old_entries" required="required" min="1" max="1200" value="<?php echo isset($_SESSION['old_entries']) ? $_SESSION['old_entries'] : $user_default_config->old_entries; ?>" tabindex="2" /> <?php echo _t('gen.date.month'); ?> - </div> - </div> - - <div class="form-group"> - <label class="group-name" for="default_user"><?php echo _t('install.default_user'); ?></label> - <div class="group-controls"> - <input type="text" id="default_user" name="default_user" autocomplete="username" required="required" size="16" pattern="<?php echo FreshRSS_user_Controller::USERNAME_PATTERN; ?>" value="<?php echo isset($_SESSION['default_user']) ? $_SESSION['default_user'] : ''; ?>" placeholder="<?php echo httpAuthUser() == '' ? 'alice' : httpAuthUser(); ?>" tabindex="3" /> - </div> - </div> - - <div class="form-group"> - <label class="group-name" for="auth_type"><?php echo _t('install.auth.type'); ?></label> - <div class="group-controls"> - <select id="auth_type" name="auth_type" required="required" tabindex="4"> - <?php - function no_auth($auth_type) { - return !in_array($auth_type, array('form', 'http_auth', 'none')); - } - $auth_type = isset($_SESSION['auth_type']) ? $_SESSION['auth_type'] : ''; - ?> - <option value="form"<?php echo $auth_type === 'form' || (no_auth($auth_type) && cryptAvailable()) ? ' selected="selected"' : '', cryptAvailable() ? '' : ' disabled="disabled"'; ?>><?php echo _t('install.auth.form'); ?></option> - <option value="http_auth"<?php echo $auth_type === 'http_auth' ? ' selected="selected"' : '', httpAuthUser() == '' ? ' disabled="disabled"' : ''; ?>><?php echo _t('install.auth.http'); ?>(REMOTE_USER = '<?php echo httpAuthUser(); ?>')</option> - <option value="none"<?php echo $auth_type === 'none' || (no_auth($auth_type) && !cryptAvailable()) ? ' selected="selected"' : ''; ?>><?php echo _t('install.auth.none'); ?></option> - </select> - </div> - </div> - - <div class="form-group"> - <label class="group-name" for="passwordPlain"><?php echo _t('install.auth.password_form'); ?></label> - <div class="group-controls"> - <div class="stick"> - <input type="password" id="passwordPlain" name="passwordPlain" pattern=".{7,}" autocomplete="off" <?php echo $auth_type === 'form' ? ' required="required"' : ''; ?> tabindex="5" /> - <a class="btn toggle-password" data-toggle="passwordPlain"><?php echo FreshRSS_Themes::icon('key'); ?></a> - </div> - <?php echo _i('help'); ?> <?php echo _t('install.auth.password_format'); ?> - <noscript><b><?php echo _t('gen.js.should_be_activated'); ?></b></noscript> - </div> - </div> - - <div class="form-group form-actions"> - <div class="group-controls"> - <button type="submit" class="btn btn-important" tabindex="7" ><?php echo _t('gen.action.submit'); ?></button> - <button type="reset" class="btn" tabindex="8" ><?php echo _t('gen.action.cancel'); ?></button> - <?php if ($s2['all'] == 'ok') { ?> - <a class="btn btn-important next-step" href="?step=3" tabindex="9" ><?php echo _t('install.action.next_step'); ?></a> - <?php } ?> - </div> - </div> - </form> -<?php -} - -function printStep3() { $system_default_config = Minz_Configuration::get('default_system'); ?> - <?php $s3 = checkStep3(); if ($s3['all'] == 'ok') { ?> + <?php $s2 = checkStep2(); if ($s2['all'] == 'ok') { ?> <p class="alert alert-success"><span class="alert-head"><?php echo _t('gen.short.ok'); ?></span> <?php echo _t('install.bdd.conf.ok'); ?></p> - <?php } elseif ($s3['conn'] == 'ko') { ?> + <?php } elseif ($s2['conn'] == 'ko') { ?> <p class="alert alert-error"><span class="alert-head"><?php echo _t('gen.short.damn'); ?></span> <?php echo _t('install.bdd.conf.ko'),(empty($_SESSION['bd_error']) ? '' : ' : ' . $_SESSION['bd_error']); ?></p> <?php } ?> - <form action="index.php?step=3" method="post" autocomplete="off"> + <form action="index.php?step=2" method="post" autocomplete="off"> <legend><?php echo _t('install.bdd.conf'); ?></legend> <div class="form-group"> <label class="group-name" for="type"><?php echo _t('install.bdd.type'); ?></label> @@ -689,6 +566,74 @@ function printStep3() { <div class="group-controls"> <button type="submit" class="btn btn-important" tabindex="7" ><?php echo _t('gen.action.submit'); ?></button> <button type="reset" class="btn" tabindex="8" ><?php echo _t('gen.action.cancel'); ?></button> + <?php if ($s2['all'] == 'ok') { ?> + <a class="btn btn-important next-step" href="?step=3" tabindex="9" ><?php echo _t('install.action.next_step'); ?></a> + <?php } ?> + </div> + </div> + </form> +<?php +} + +function printStep3() { + $user_default_config = Minz_Configuration::get('default_user'); +?> + <?php $s3 = checkStep3(); if ($s3['all'] == 'ok') { ?> + <p class="alert alert-success"><span class="alert-head"><?php echo _t('gen.short.ok'); ?></span> <?php echo _t('install.conf.ok'); ?></p> + <?php } elseif (!empty($_POST)) { ?> + <p class="alert alert-error"><?php echo _t('install.fix_errors_before'); ?></p> + <?php } ?> + + <form action="index.php?step=3" method="post"> + <legend><?php echo _t('install.conf'); ?></legend> + + <div class="form-group"> + <label class="group-name" for="old_entries"><?php echo _t('install.delete_articles_after'); ?></label> + <div class="group-controls"> + <input type="number" id="old_entries" name="old_entries" required="required" min="1" max="1200" value="<?php echo isset($_SESSION['old_entries']) ? $_SESSION['old_entries'] : $user_default_config->old_entries; ?>" tabindex="2" /> <?php echo _t('gen.date.month'); ?> + </div> + </div> + + <div class="form-group"> + <label class="group-name" for="default_user"><?php echo _t('install.default_user'); ?></label> + <div class="group-controls"> + <input type="text" id="default_user" name="default_user" autocomplete="username" required="required" size="16" pattern="<?php echo FreshRSS_user_Controller::USERNAME_PATTERN; ?>" value="<?php echo isset($_SESSION['default_user']) ? $_SESSION['default_user'] : ''; ?>" placeholder="<?php echo httpAuthUser() == '' ? 'alice' : httpAuthUser(); ?>" tabindex="3" /> + </div> + </div> + + <div class="form-group"> + <label class="group-name" for="auth_type"><?php echo _t('install.auth.type'); ?></label> + <div class="group-controls"> + <select id="auth_type" name="auth_type" required="required" tabindex="4"> + <?php + function no_auth($auth_type) { + return !in_array($auth_type, array('form', 'http_auth', 'none')); + } + $auth_type = isset($_SESSION['auth_type']) ? $_SESSION['auth_type'] : ''; + ?> + <option value="form"<?php echo $auth_type === 'form' || (no_auth($auth_type) && cryptAvailable()) ? ' selected="selected"' : '', cryptAvailable() ? '' : ' disabled="disabled"'; ?>><?php echo _t('install.auth.form'); ?></option> + <option value="http_auth"<?php echo $auth_type === 'http_auth' ? ' selected="selected"' : '', httpAuthUser() == '' ? ' disabled="disabled"' : ''; ?>><?php echo _t('install.auth.http'); ?>(REMOTE_USER = '<?php echo httpAuthUser(); ?>')</option> + <option value="none"<?php echo $auth_type === 'none' || (no_auth($auth_type) && !cryptAvailable()) ? ' selected="selected"' : ''; ?>><?php echo _t('install.auth.none'); ?></option> + </select> + </div> + </div> + + <div class="form-group"> + <label class="group-name" for="passwordPlain"><?php echo _t('install.auth.password_form'); ?></label> + <div class="group-controls"> + <div class="stick"> + <input type="password" id="passwordPlain" name="passwordPlain" pattern=".{7,}" autocomplete="off" <?php echo $auth_type === 'form' ? ' required="required"' : ''; ?> tabindex="5" /> + <a class="btn toggle-password" data-toggle="passwordPlain"><?php echo FreshRSS_Themes::icon('key'); ?></a> + </div> + <?php echo _i('help'); ?> <?php echo _t('install.auth.password_format'); ?> + <noscript><b><?php echo _t('gen.js.should_be_activated'); ?></b></noscript> + </div> + </div> + + <div class="form-group form-actions"> + <div class="group-controls"> + <button type="submit" class="btn btn-important" tabindex="7" ><?php echo _t('gen.action.submit'); ?></button> + <button type="reset" class="btn" tabindex="8" ><?php echo _t('gen.action.cancel'); ?></button> <?php if ($s3['all'] == 'ok') { ?> <a class="btn btn-important next-step" href="?step=4" tabindex="9" ><?php echo _t('install.action.next_step'); ?></a> <?php } ?> @@ -763,8 +708,8 @@ case 5: <li class="nav-header"><?php echo _t('install.steps'); ?></li> <li class="item<?php echo STEP == 0 ? ' active' : ''; ?>"><a href="?step=0"><?php echo _t('install.language'); ?></a></li> <li class="item<?php echo STEP == 1 ? ' active' : ''; ?>"><a href="?step=1"><?php echo _t('install.check'); ?></a></li> - <li class="item<?php echo STEP == 2 ? ' active' : ''; ?>"><a href="?step=2"><?php echo _t('install.conf'); ?></a></li> - <li class="item<?php echo STEP == 3 ? ' active' : ''; ?>"><a href="?step=3"><?php echo _t('install.bdd.conf'); ?></a></li> + <li class="item<?php echo STEP == 2 ? ' active' : ''; ?>"><a href="?step=2"><?php echo _t('install.bdd.conf'); ?></a></li> + <li class="item<?php echo STEP == 3 ? ' active' : ''; ?>"><a href="?step=3"><?php echo _t('install.conf'); ?></a></li> <li class="item<?php echo STEP == 4 ? ' active' : ''; ?>"><a href="?step=4"><?php echo _t('install.this_is_the_end'); ?></a></li> </ul> 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 @@ <div class="form-group"> <label class="group-name" for="name"><?php echo _t('sub.category.title'); ?></label> <div class="group-controls"> - <input type="text" name="name" id="name" class="extend" value="<?php echo $this->category->name() ; ?>" /> + <input type="text" name="name" id="name" class="extend" value="<?php echo $this->category->name() ; ?>" <?php + //Disallow changing the name of the default category + echo $this->category->id() == FreshRSS_CategoryDAO::DEFAULTCATEGORYID ? 'disabled="disabled"' : ''; + ?> /> </div> </div> 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 @@ <?php -$favicons_dir = DATA_PATH . '/favicons/'; -$default_favicon = PUBLIC_PATH . '/themes/icons/default_favicon.ico'; +const FAVICONS_DIR = DATA_PATH . '/favicons/'; +const DEFAULT_FAVICON = PUBLIC_PATH . '/themes/icons/default_favicon.ico'; function isImgMime($content) { //Based on https://github.com/ArthurHoaro/favicon/blob/3a4f93da9bb24915b21771eb7873a21bde26f5d1/src/Favicon/Favicon.php#L311-L319 @@ -31,18 +31,14 @@ function downloadHttp(&$url, $curlOptions = array()) { return ''; } $ch = curl_init($url); - curl_setopt_array($ch, array( + curl_setopt_array($ch, [ CURLOPT_RETURNTRANSFER => true, CURLOPT_TIMEOUT => 15, CURLOPT_USERAGENT => FRESHRSS_USERAGENT, CURLOPT_MAXREDIRS => 10, - )); - if (version_compare(PHP_VERSION, '5.6.0') >= 0 || ini_get('open_basedir') == '') { - curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true); //Keep option separated for open_basedir PHP bug 65646 - } - if (defined('CURLOPT_ENCODING')) { - curl_setopt($ch, CURLOPT_ENCODING, ''); //Enable all encodings - } + CURLOPT_FOLLOWLOCATION => true, + CURLOPT_ENCODING => '', //Enable all encodings + ]); curl_setopt_array($ch, $curlOptions); $response = curl_exec($ch); $info = curl_getinfo($ch); @@ -89,7 +85,6 @@ function searchFavicon(&$url) { } function download_favicon($url, $dest) { - global $default_favicon; $url = trim($url); $favicon = searchFavicon($url); if ($favicon == '') { @@ -109,5 +104,5 @@ function download_favicon($url, $dest) { } } return ($favicon != '' && file_put_contents($dest, $favicon)) || - @copy($default_favicon, $dest); + @copy(DEFAULT_FAVICON, $dest); } diff --git a/lib/lib_install.php b/lib/lib_install.php index 17defccf6..ed361eb39 100644 --- a/lib/lib_install.php +++ b/lib/lib_install.php @@ -78,69 +78,24 @@ function generateSalt() { return sha1(uniqid(mt_rand(), true).implode('', stat(__FILE__))); } -function checkDb(&$dbOptions) { - $dsn = ''; - $driver_options = null; - prepareSyslog(); - try { - switch ($dbOptions['type']) { - case 'mysql': - include_once(APP_PATH . '/SQL/install.sql.mysql.php'); - $driver_options = array( - PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4' - ); - try { // on ouvre une connexion juste pour créer la base si elle n'existe pas - $dsn = 'mysql:host=' . $dbOptions['host'] . ';'; - $c = new PDO($dsn, $dbOptions['user'], $dbOptions['password'], $driver_options); - $sql = sprintf(SQL_CREATE_DB, $dbOptions['base']); - $res = $c->query($sql); - } catch (PDOException $e) { - syslog(LOG_DEBUG, 'FreshRSS MySQL warning: ' . $e->getMessage()); - } - // on écrase la précédente connexion en sélectionnant la nouvelle BDD - $dsn = 'mysql:host=' . $dbOptions['host'] . ';dbname=' . $dbOptions['base']; - break; - case 'sqlite': - include_once(APP_PATH . '/SQL/install.sql.sqlite.php'); - $path = join_path(USERS_PATH, $dbOptions['default_user']); - if (!is_dir($path)) { - mkdir($path); - } - $dsn = 'sqlite:' . join_path($path, 'db.sqlite'); - $driver_options = array( - PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, - ); - break; - case 'pgsql': - include_once(APP_PATH . '/SQL/install.sql.pgsql.php'); - $driver_options = array( - PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, - ); - try { // on ouvre une connexion juste pour créer la base si elle n'existe pas - $dsn = 'pgsql:host=' . $dbOptions['host'] . ';dbname=postgres'; - $c = new PDO($dsn, $dbOptions['user'], $dbOptions['password'], $driver_options); - $sql = sprintf(SQL_CREATE_DB, $dbOptions['base']); - $res = $c->query($sql); - } catch (PDOException $e) { - syslog(LOG_DEBUG, 'FreshRSS PostgreSQL warning: ' . $e->getMessage()); - } - // on écrase la précédente connexion en sélectionnant la nouvelle BDD - $dsn = 'pgsql:host=' . $dbOptions['host'] . ';dbname=' . $dbOptions['base']; - break; - default: - return false; - } - - $c = new PDO($dsn, $dbOptions['user'], $dbOptions['password'], $driver_options); - $res = $c->query('SELECT 1'); - } catch (PDOException $e) { - $dsn = ''; - syslog(LOG_DEBUG, 'FreshRSS SQL warning: ' . $e->getMessage()); - $dbOptions['error'] = $e->getMessage(); +function checkDb() { + $conf = FreshRSS_Context::$system_conf; + $db = $conf->db; + if (empty($db['pdo_options'])) { + $db['pdo_options'] = []; } - $dbOptions['dsn'] = $dsn; - $dbOptions['options'] = $driver_options; - return $dsn != ''; + $db['pdo_options'][PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION; + $dbBase = isset($db['base']) ? $db['base'] : ''; + + $db['base'] = ''; //First connection without database name to create the database + Minz_ModelPdo::$usesSharedPdo = false; + $databaseDAO = FreshRSS_Factory::createDatabaseDAO(); + $databaseDAO->create(); + + $db['base'] = $dbBase; //New connection with the database name + $databaseDAO = FreshRSS_Factory::createDatabaseDAO(); + Minz_ModelPdo::$usesSharedPdo = true; + return $databaseDAO->testConnection(); } function deleteInstall() { diff --git a/lib/lib_rss.php b/lib/lib_rss.php index 1bba60c36..854126b54 100644 --- a/lib/lib_rss.php +++ b/lib/lib_rss.php @@ -535,17 +535,16 @@ function _i($icon, $url_only = false) { } -$SHORTCUT_KEYS = array( //No const for < PHP 5.6 compatibility +const SHORTCUT_KEYS = [ '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', 'F1', 'F2', 'F3', 'F4', 'F5', 'F6', 'F7', 'F8', 'F9', 'F10', 'F11', 'F12', 'ArrowDown', 'ArrowLeft', 'ArrowRight', 'ArrowUp', 'Backspace', 'Delete', 'End', 'Enter', 'Escape', 'Home', 'Insert', 'PageDown', 'PageUp', 'Space', 'Tab', - ); + ]; function validateShortcutList($shortcuts) { - global $SHORTCUT_KEYS; $legacy = array( 'down' => 'ArrowDown', 'left' => 'ArrowLeft', 'page_down' => 'PageDown', 'page_up' => 'PageUp', 'right' => 'ArrowRight', 'up' => 'ArrowUp', @@ -554,17 +553,17 @@ function validateShortcutList($shortcuts) { $shortcuts_ok = array(); foreach ($shortcuts as $key => $value) { - if (in_array($value, $SHORTCUT_KEYS)) { + if (in_array($value, SHORTCUT_KEYS)) { $shortcuts_ok[$key] = $value; } elseif (isset($legacy[$value])) { $shortcuts_ok[$key] = $legacy[$value]; } else { //Case-insensitive search if ($upper === null) { - $upper = array_map('strtoupper', $SHORTCUT_KEYS); + $upper = array_map('strtoupper', SHORTCUT_KEYS); } $i = array_search(strtoupper($value), $upper); if ($i !== false) { - $shortcuts_ok[$key] = $SHORTCUT_KEYS[$i]; + $shortcuts_ok[$key] = SHORTCUT_KEYS[$i]; } } } 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; @@ -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); |