From 49e1a2c579757994ae4ed5994339bd58a09238db Mon Sep 17 00:00:00 2001 From: Marien Fressinaud Date: Fri, 2 Aug 2019 11:40:57 +0200 Subject: Allow to change the view layout (#2467) The `_useLayout` function is marked as deprecated, replaced by a more powerful `_layout` function. --- app/Controllers/importExportController.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'app/Controllers/importExportController.php') diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index 1d7176929..51fec75eb 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -773,7 +773,7 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { if (!Minz_Request::isPost()) { Minz_Request::forward(array('c' => 'importExport', 'a' => 'index'), true); } - $this->view->_useLayout(false); + $this->view->_layout(false); $nb_files = 0; try { -- cgit v1.2.3 From c76a318193cda63064625b2d92c719b7150d7d64 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Sun, 15 Sep 2019 21:36:53 +0200 Subject: CLI to export/import any database to/from SQLite (#2496) * CLI to export/import any database to/from SQLite Require PHP 5.5+ https://github.com/FreshRSS/FreshRSS/pull/2495 * Travis * Execution rights * Fix wrong static fields * Fix MySQL bad default buffering https://stackoverflow.com/questions/6895098/pdo-mysql-memory-consumption-with-large-result-set/6935271#6935271 https://php.net/manual/ref.pdo-mysql * Fix count on progression * Avoid static DB information To ease working with two DBs at the same time * Less static, simplify Needs some testing * Small corrections * Special case for SQLite to SQLite * Modify special case for SQLite * Remove special case for SQLite More uniform logic for the 3 databases. Fix wrong DROP TABLE for SQLite. * Drop indexes * Revert "Drop indexes" This reverts commit f28d2bae0935745c1c74ea38f2ee083f3fd4bf9d. * Fix deletion * Fix classic export * Update cli/README.md Co-Authored-By: Marien Fressinaud * Addressing part of review * Remove goto :cry: * Travis * Comment for SQLite case * Fix missing fields when inserting --- app/Controllers/importExportController.php | 2 - app/Controllers/userController.php | 11 +- app/Models/CategoryDAO.php | 14 ++- app/Models/DatabaseDAO.php | 170 ++++++++++++++++++++++++++++- app/Models/EntryDAO.php | 32 ++++-- app/Models/EntryDAOPGSQL.php | 4 + app/Models/EntryDAOSQLite.php | 8 ++ app/Models/Factory.php | 4 + app/Models/FeedDAO.php | 22 +++- app/Models/TagDAO.php | 21 +++- app/Models/UserDAO.php | 71 +++++++----- app/SQL/install.sql.sqlite.php | 12 +- cli/README.md | 32 ++++-- cli/export-sqlite-for-user.php | 28 +++++ cli/import-sqlite-for-user.php | 34 ++++++ lib/Minz/ModelPdo.php | 39 ++++--- 16 files changed, 421 insertions(+), 83 deletions(-) create mode 100755 cli/export-sqlite-for-user.php create mode 100755 cli/import-sqlite-for-user.php (limited to 'app/Controllers/importExportController.php') diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index 51fec75eb..93897dde5 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -709,8 +709,6 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { $this->entryDAO = FreshRSS_Factory::createEntryDao($username); $this->feedDAO = FreshRSS_Factory::createFeedDao($username); - $this->entryDAO->disableBuffering(); - if ($export_feeds === true) { //All feeds $export_feeds = $this->feedDAO->listFeedsIds(); diff --git a/app/Controllers/userController.php b/app/Controllers/userController.php index a1d649c0a..96e4fec8c 100644 --- a/app/Controllers/userController.php +++ b/app/Controllers/userController.php @@ -237,8 +237,8 @@ class FreshRSS_user_Controller extends Minz_ActionController { $ok &= (file_put_contents($configPath, "createUser($new_user_name, $userConfig['language'], $insertDefaultFeeds); + $newUserDAO = FreshRSS_Factory::createUserDao($new_user_name); + $ok &= $newUserDAO->createUser($userConfig['language'], $insertDefaultFeeds); $ok &= self::updateUser($new_user_name, $email, $passwordPlain, $apiPasswordPlain); } return $ok; @@ -316,9 +316,6 @@ class FreshRSS_user_Controller extends Minz_ActionController { } public static function deleteUser($username) { - $db = FreshRSS_Context::$system_conf->db; - require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php'); - $ok = self::checkUsername($username); if ($ok) { $default_user = FreshRSS_Context::$system_conf->default_user; @@ -328,8 +325,8 @@ class FreshRSS_user_Controller extends Minz_ActionController { $ok &= is_dir($user_data); if ($ok) { self::deleteFeverKey($username); - $userDAO = new FreshRSS_UserDAO(); - $ok &= $userDAO->deleteUser($username); + $oldUserDAO = FreshRSS_Factory::createUserDao($username); + $ok &= $oldUserDAO->deleteUser(); $ok &= recursive_unlink($user_data); array_map('unlink', glob(PSHB_PATH . '/feeds/*/' . $username . '.txt')); } diff --git a/app/Models/CategoryDAO.php b/app/Models/CategoryDAO.php index 6535adae7..b0fcb5033 100644 --- a/app/Models/CategoryDAO.php +++ b/app/Models/CategoryDAO.php @@ -77,6 +77,15 @@ 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(); + 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); @@ -96,6 +105,9 @@ 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); + if ($stm == false) { + return false; + } $values = array($name); @@ -156,7 +168,7 @@ class FreshRSS_CategoryDAO extends Minz_ModelPdo implements FreshRSS_Searchable $cat->_id(self::DEFAULTCATEGORYID); $sql = 'INSERT INTO `' . $this->prefix . 'category`(id, name) VALUES(?, ?)'; - if (parent::$sharedDbType === 'pgsql') { + if ($this->bd->dbType() === 'pgsql') { //Force call to nextval() $sql .= ' RETURNING nextval(\'"' . $this->prefix . 'category_id_seq"\');'; } diff --git a/app/Models/DatabaseDAO.php b/app/Models/DatabaseDAO.php index b331eccc3..ec84da664 100644 --- a/app/Models/DatabaseDAO.php +++ b/app/Models/DatabaseDAO.php @@ -144,8 +144,7 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { public function ensureCaseInsensitiveGuids() { $ok = true; - $db = FreshRSS_Context::$system_conf->db; - if ($db['type'] === 'mysql') { + if ($this->bd->dbType() === 'mysql') { include_once(APP_PATH . '/SQL/install.sql.mysql.php'); if (defined('SQL_UPDATE_GUID_LATIN1_BIN')) { //FreshRSS 1.12 try { @@ -154,7 +153,7 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { $ok = $stm->execute(); } catch (Exception $e) { $ok = false; - Minz_Log::error('FreshRSS_DatabaseDAO::ensureCaseInsensitiveGuids error: ' . $e->getMessage()); + Minz_Log::error(__METHOD__ . ' error: ' . $e->getMessage()); } } } @@ -164,4 +163,169 @@ class FreshRSS_DatabaseDAO extends Minz_ModelPdo { public function minorDbMaintenance() { $this->ensureCaseInsensitiveGuids(); } + + private static function stdError($error) { + if (defined('STDERR')) { + fwrite(STDERR, $error . "\n"); + } + Minz_Log::error($error); + return false; + } + + const SQLITE_EXPORT = 1; + const SQLITE_IMPORT = 2; + + public function dbCopy($filename, $mode, $clearFirst = false) { + $error = ''; + + $userDAO = FreshRSS_Factory::createUserDao(); + $catDAO = FreshRSS_Factory::createCategoryDao(); + $feedDAO = FreshRSS_Factory::createFeedDao(); + $entryDAO = FreshRSS_Factory::createEntryDao(); + $tagDAO = FreshRSS_Factory::createTagDao(); + + switch ($mode) { + case self::SQLITE_EXPORT: + if (@filesize($filename) > 0) { + $error = 'Error: SQLite export file already exists: ' . $filename; + } + break; + case self::SQLITE_IMPORT: + if (!is_readable($filename)) { + $error = 'Error: SQLite import file is not readable: ' . $filename; + } elseif ($clearFirst) { + $userDAO->deleteUser(); + if ($this->bd->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(); + } + } else { + $nbEntries = $entryDAO->countUnreadRead(); + if (!empty($nbEntries['all'])) { + $error = 'Error: Destination database already contains some entries!'; + } + } + break; + default: + $error = 'Invalid copy mode!'; + break; + } + if ($error != '') { + return self::stdError($error); + } + + $sqlite = null; + + 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); + + switch ($mode) { + case self::SQLITE_EXPORT: + $userFrom = $userDAO; $userTo = $userDAOSQLite; + $catFrom = $catDAO; $catTo = $categoryDAOSQLite; + $feedFrom = $feedDAO; $feedTo = $feedDAOSQLite; + $entryFrom = $entryDAO; $entryTo = $entryDAOSQLite; + $tagFrom = $tagDAO; $tagTo = $tagDAOSQLite; + break; + case self::SQLITE_IMPORT: + $userFrom = $userDAOSQLite; $userTo = $userDAO; + $catFrom = $categoryDAOSQLite; $catTo = $catDAO; + $feedFrom = $feedDAOSQLite; $feedTo = $feedDAO; + $entryFrom = $entryDAOSQLite; $entryTo = $entryDAO; + $tagFrom = $tagDAOSQLite; $tagTo = $tagDAO; + break; + } + + $idMaps = []; + + if (defined('STDERR')) { + fwrite(STDERR, "Start SQL copy…\n"); + } + + $userTo->createUser(); + + $catTo->beginTransaction(); + foreach ($catFrom->selectAll() as $category) { + $cat = $catTo->searchByName($category['name']); //Useful for the default category + if ($cat != null) { + $catId = $cat->id(); + } else { + $catId = $catTo->addCategory($category); + if ($catId == false) { + $error = 'Error during SQLite copy of categories!'; + return self::stdError($error); + } + } + $idMaps['c' . $category['id']] = $catId; + } + foreach ($feedFrom->selectAll() as $feed) { + $feed['category'] = empty($idMaps['c' . $feed['category']]) ? FreshRSS_CategoryDAO::DEFAULTCATEGORYID : $idMaps['c' . $feed['category']]; + $feedId = $feedTo->addFeed($feed); + if ($feedId == false) { + $error = 'Error during SQLite copy of feeds!'; + return self::stdError($error); + } + $idMaps['f' . $feed['id']] = $feedId; + } + $catTo->commit(); + + $nbEntries = $entryFrom->count(); + $n = 0; + $entryTo->beginTransaction(); + foreach ($entryFrom->selectAll() as $entry) { + $n++; + if (!empty($idMaps['f' . $entry['id_feed']])) { + $entry['id_feed'] = $idMaps['f' . $entry['id_feed']]; + if (!$entryTo->addEntry($entry, false)) { + $error = 'Error during SQLite copy of entries!'; + return self::stdError($error); + } + } + if ($n % 100 === 1 && defined('STDERR')) { //Display progression + fwrite(STDERR, "\033[0G" . $n . '/' . $nbEntries); + } + } + if (defined('STDERR')) { + fwrite(STDERR, "\033[0G" . $n . '/' . $nbEntries . "\n"); + } + $entryTo->commit(); + $feedTo->updateCachedValues(); + + $idMaps = []; + + $tagTo->beginTransaction(); + foreach ($tagFrom->selectAll() as $tag) { + $tagId = $tagTo->addTag($tag); + if ($tagId == false) { + $error = 'Error during SQLite copy of tags!'; + return self::stdError($error); + } + $idMaps['t' . $tag['id']] = $tagId; + } + foreach ($tagFrom->selectEntryTag() as $entryTag) { + if (!empty($idMaps['t' . $entryTag['id_tag']])) { + $entryTag['id_tag'] = $idMaps['t' . $entryTag['id_tag']]; + if (!$tagTo->tagEntry($entryTag['id_tag'], $entryTag['id_entry'])) { + $error = 'Error during SQLite copy of entry-tags!'; + return self::stdError($error); + } + } + } + $tagTo->commit(); + + return true; + } } diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php index 1b2786a6a..b13c83d67 100644 --- a/app/Models/EntryDAO.php +++ b/app/Models/EntryDAO.php @@ -3,11 +3,11 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { public function isCompressed() { - return parent::$sharedDbType === 'mysql'; + return true; } public function hasNativeHex() { - return parent::$sharedDbType !== 'sqlite'; + return true; } public function sqlHexDecode($x) { @@ -64,7 +64,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } $this->triedUpdateToUtf8mb4 = true; $db = FreshRSS_Context::$system_conf->db; - if ($db['type'] === 'mysql') { + 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 @@ -98,8 +98,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->bd->commit(); } try { - $db = FreshRSS_Context::$system_conf->db; - require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php'); + require_once(APP_PATH . '/SQL/install.sql.' . $this->bd->dbType() . '.php'); Minz_Log::warning('SQL CREATE TABLE entrytmp...'); if (defined('SQL_CREATE_TABLE_ENTRYTMP')) { $sql = sprintf(SQL_CREATE_TABLE_ENTRYTMP, $this->prefix); @@ -152,9 +151,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { private $addEntryPrepared = null; - public function addEntry($valuesTmp) { + public function addEntry($valuesTmp, $useTmpTable = true) { if ($this->addEntryPrepared == null) { - $sql = 'INSERT INTO `' . $this->prefix . 'entrytmp` (id, guid, title, author, ' + $sql = 'INSERT INTO `' . $this->prefix . ($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, ' @@ -178,7 +177,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $valuesTmp['link'] = safe_ascii($valuesTmp['link']); $this->addEntryPrepared->bindParam(':link', $valuesTmp['link']); $this->addEntryPrepared->bindParam(':date', $valuesTmp['date'], PDO::PARAM_INT); - $valuesTmp['lastSeen'] = time(); + if (empty($valuesTmp['lastSeen'])) { + $valuesTmp['lastSeen'] = time(); + } $this->addEntryPrepared->bindParam(':last_seen', $valuesTmp['lastSeen'], PDO::PARAM_INT); $valuesTmp['is_read'] = $valuesTmp['is_read'] ? 1 : 0; $this->addEntryPrepared->bindParam(':is_read', $valuesTmp['is_read'], PDO::PARAM_INT); @@ -637,6 +638,18 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { } } + public function selectAll() { + $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(); + while ($row = $stm->fetch(PDO::FETCH_ASSOC)) { + yield $row; + } + } + public function searchByGuid($id_feed, $guid) { // un guid est unique pour un flux donné $sql = 'SELECT id, guid, title, author, ' @@ -991,6 +1004,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $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) { + return false; + } $stm->execute(); $res = $stm->fetchAll(PDO::FETCH_COLUMN, 0); rsort($res); diff --git a/app/Models/EntryDAOPGSQL.php b/app/Models/EntryDAOPGSQL.php index e571e457f..e90aa8332 100644 --- a/app/Models/EntryDAOPGSQL.php +++ b/app/Models/EntryDAOPGSQL.php @@ -2,6 +2,10 @@ class FreshRSS_EntryDAOPGSQL extends FreshRSS_EntryDAOSQLite { + public function hasNativeHex() { + return true; + } + public function sqlHexDecode($x) { return 'decode(' . $x . ", 'hex')"; } diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php index f8cd14fe6..f53685e35 100644 --- a/app/Models/EntryDAOSQLite.php +++ b/app/Models/EntryDAOSQLite.php @@ -2,6 +2,14 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO { + public function isCompressed() { + return false; + } + + public function hasNativeHex() { + return false; + } + public function sqlHexDecode($x) { return $x; } diff --git a/app/Models/Factory.php b/app/Models/Factory.php index 1accb491c..6f2ca2217 100644 --- a/app/Models/Factory.php +++ b/app/Models/Factory.php @@ -2,6 +2,10 @@ class FreshRSS_Factory { + public static function createUserDao($username = null) { + return new FreshRSS_UserDAO($username); + } + public static function createCategoryDao($username = null) { return new FreshRSS_CategoryDAO($username); } diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php index c9c9f6301..1dad4a834 100644 --- a/app/Models/FeedDAO.php +++ b/app/Models/FeedDAO.php @@ -39,6 +39,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { description, `lastUpdate`, priority, + `pathEntries`, `httpAuth`, error, keep_history, @@ -46,11 +47,14 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { attributes ) VALUES - (?, ?, ?, ?, ?, ?, 10, ?, 0, ?, ?, ?)'; + (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'; $stm = $this->bd->prepare($sql); $valuesTmp['url'] = safe_ascii($valuesTmp['url']); $valuesTmp['website'] = safe_ascii($valuesTmp['website']); + if (!isset($valuesTmp['pathEntries'])) { + $valuesTmp['pathEntries'] = ''; + } $values = array( substr($valuesTmp['url'], 0, 511), @@ -59,8 +63,11 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo implements FreshRSS_Searchable { substr($valuesTmp['website'], 0, 255), mb_strcut($valuesTmp['description'], 0, 1023, 'UTF-8'), $valuesTmp['lastUpdate'], + isset($valuesTmp['priority']) ? intval($valuesTmp['priority']) : FreshRSS_Feed::PRIORITY_MAIN_STREAM, + mb_strcut($valuesTmp['pathEntries'], 0, 511, 'UTF-8'), base64_encode($valuesTmp['httpAuth']), - FreshRSS_Feed::KEEP_HISTORY_DEFAULT, + isset($valuesTmp['error']) ? intval($valuesTmp['error']) : 0, + isset($valuesTmp['keep_history']) ? intval($valuesTmp['keep_history']) : FreshRSS_Feed::KEEP_HISTORY_DEFAULT, isset($valuesTmp['ttl']) ? intval($valuesTmp['ttl']) : FreshRSS_Feed::TTL_DEFAULT, isset($valuesTmp['attributes']) ? json_encode($valuesTmp['attributes']) : '', ); @@ -238,6 +245,17 @@ 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(); + 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); diff --git a/app/Models/TagDAO.php b/app/Models/TagDAO.php index 297d24c96..11807fc32 100644 --- a/app/Models/TagDAO.php +++ b/app/Models/TagDAO.php @@ -13,8 +13,7 @@ class FreshRSS_TagDAO extends Minz_ModelPdo implements FreshRSS_Searchable { $this->bd->commit(); } try { - $db = FreshRSS_Context::$system_conf->db; - require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php'); + require_once(APP_PATH . '/SQL/install.sql.' . $this->bd->dbType() . '.php'); Minz_Log::warning('SQL ALTER GUID case sensitivity...'); $databaseDAO = FreshRSS_Factory::createDatabaseDAO(); @@ -139,6 +138,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(); + 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(); + 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); diff --git a/app/Models/UserDAO.php b/app/Models/UserDAO.php index 0cf163bae..6292cc09f 100644 --- a/app/Models/UserDAO.php +++ b/app/Models/UserDAO.php @@ -1,21 +1,19 @@ db; - require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php'); - - $userPDO = new Minz_ModelPdo($username); + public function createUser($new_user_language = null, $insertDefaultFeeds = false) { + require_once(APP_PATH . '/SQL/install.sql.' . $this->bd->dbType() . '.php'); $currentLanguage = Minz_Translate::language(); try { - Minz_Translate::reset($new_user_language); + if ($new_user_language != null) { + Minz_Translate::reset($new_user_language); + } $ok = false; - $bd_prefix_user = $db['prefix'] . $username . '_'; if (defined('SQL_CREATE_TABLES')) { //E.g. MySQL - $sql = sprintf(SQL_CREATE_TABLES . SQL_CREATE_TABLE_ENTRYTMP . SQL_CREATE_TABLE_TAGS, $bd_prefix_user, _t('gen.short.default_category')); - $stm = $userPDO->bd->prepare($sql); + $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; @@ -23,8 +21,8 @@ class FreshRSS_UserDAO extends Minz_ModelPdo { $instructions = array_merge($SQL_CREATE_TABLES, $SQL_CREATE_TABLE_ENTRYTMP, $SQL_CREATE_TABLE_TAGS); $ok = !empty($instructions); foreach ($instructions as $instruction) { - $sql = sprintf($instruction, $bd_prefix_user, _t('gen.short.default_category')); - $stm = $userPDO->bd->prepare($sql); + $sql = sprintf($instruction, $this->prefix, _t('gen.short.default_category')); + $stm = $this->bd->prepare($sql); $ok &= ($stm && $stm->execute()); } } @@ -32,8 +30,8 @@ class FreshRSS_UserDAO extends Minz_ModelPdo { if ($ok && $insertDefaultFeeds) { $default_feeds = FreshRSS_Context::$system_conf->default_feeds; foreach ($default_feeds as $feed) { - $sql = sprintf(SQL_INSERT_FEED, $bd_prefix_user); - $stm = $userPDO->bd->prepare($sql); + $sql = sprintf(SQL_INSERT_FEED, $this->prefix); + $stm = $this->bd->prepare($sql); $parameters = array( ':url' => $feed['url'], ':name' => $feed['name'], @@ -44,7 +42,7 @@ class FreshRSS_UserDAO extends Minz_ModelPdo { } } } catch (Exception $e) { - Minz_Log::error('Error while creating user: ' . $e->getMessage()); + Minz_Log::error('Error while creating database for user: ' . $e->getMessage()); } Minz_Translate::reset($currentLanguage); @@ -53,30 +51,43 @@ class FreshRSS_UserDAO extends Minz_ModelPdo { return true; } else { $info = empty($stm) ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::error('SQL error: ' . $info[2]); + Minz_Log::error(__METHOD__ . ' error: ' . $info[2]); return false; } } - public function deleteUser($username) { - $db = FreshRSS_Context::$system_conf->db; - require_once(APP_PATH . '/SQL/install.sql.' . $db['type'] . '.php'); + public function deleteUser() { + if (defined('STDERR')) { + fwrite(STDERR, 'Deleting SQL data for user “' . $this->current_user . "”…\n"); + } - if ($db['type'] === 'sqlite') { - return unlink(USERS_PATH . '/' . $username . '/db.sqlite'); - } else { - $userPDO = new Minz_ModelPdo($username); + require_once(APP_PATH . '/SQL/install.sql.' . $this->bd->dbType() . '.php'); - $sql = sprintf(SQL_DROP_TABLES, $db['prefix'] . $username . '_'); - $stm = $userPDO->bd->prepare($sql); - if ($stm && $stm->execute()) { - return true; - } else { - $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); - Minz_Log::error('SQL error : ' . $info[2]); - return false; + $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()); + } } } + + if ($ok) { + return true; + } else { + $info = $stm == null ? array(2 => 'syntax error') : $stm->errorInfo(); + Minz_Log::error(__METHOD__ . ' error: ' . $info[2]); + return false; + } } public static function exists($username) { diff --git a/app/SQL/install.sql.sqlite.php b/app/SQL/install.sql.sqlite.php index d1dbfc192..88de84358 100644 --- a/app/SQL/install.sql.sqlite.php +++ b/app/SQL/install.sql.sqlite.php @@ -91,7 +91,7 @@ $SQL_CREATE_TABLE_TAGS = array( );', 'CREATE TABLE IF NOT EXISTS `entrytag` ( `id_tag` SMALLINT, - `id_entry` 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 @@ -105,4 +105,12 @@ define( VALUES(:url, 1, :name, :website, :description, 86400);' ); -define('SQL_DROP_TABLES', 'DROP TABLE IF EXISTS `entrytag`, `tag`, `entrytmp`, `entry`, `feed`, `category`'); +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`', +]; diff --git a/cli/README.md b/cli/README.md index e9e336439..35c9bad9b 100644 --- a/cli/README.md +++ b/cli/README.md @@ -35,7 +35,7 @@ cd /usr/share/FreshRSS ./cli/prepare.php # Ensure the needed directories in ./data/ -./cli/do-install.php --default_user admin ( --auth_type form --environment production --base_url https://rss.example.net --language en --title FreshRSS --allow_anonymous --api_enabled --db-type mysql --db-host localhost:3306 --db-user freshrss --db-password dbPassword123 --db-base freshrss --db-prefix freshrss ) +./cli/do-install.php --default_user admin [ --auth_type form --environment production --base_url https://rss.example.net --language en --title FreshRSS --allow_anonymous --api_enabled --db-type mysql --db-host localhost:3306 --db-user freshrss --db-password dbPassword123 --db-base freshrss --db-prefix freshrss ] # --auth_type can be: 'form' (default), 'http_auth' (using the Web server access control), 'none' (dangerous) # --db-type can be: 'sqlite' (default), 'mysql' (MySQL or MariaDB), 'pgsql' (PostgreSQL) # --base_url should be a public (routable) URL if possible, and is used for push (WebSub), for some API functions (e.g. favicons), and external URLs in FreshRSS. @@ -47,26 +47,20 @@ cd /usr/share/FreshRSS ./cli/reconfigure.php # Same parameters as for do-install.php. Used to update an existing installation. -./cli/create-user.php --user username ( --password 'password' --api_password 'api_password' --language en --email user@example.net --token 'longRandomString' --no_default_feeds --purge_after_months 3 --feed_min_articles_default 50 --feed_ttl_default 3600 --since_hours_posts_per_rss 168 --min_posts_per_rss 2 --max_posts_per_rss 400 ) +./cli/create-user.php --user username [ --password 'password' --api_password 'api_password' --language en --email user@example.net --token 'longRandomString' --no_default_feeds --purge_after_months 3 --feed_min_articles_default 50 --feed_ttl_default 3600 --since_hours_posts_per_rss 168 --min_posts_per_rss 2 --max_posts_per_rss 400 ] # --language can be: 'en' (default), 'fr', or one of the [supported languages](../app/i18n/) -./cli/update-user.php --user username ( ... ) +./cli/update-user.php --user username [ ... ] # Same options as create-user.php, except --no_default_feeds which is only available for create-user.php +./cli/actualize-user.php --user username +# Fetch feeds for the specified user + ./cli/delete-user.php --user username ./cli/list-users.php # Return a list of users, with the default/admin user first -./cli/actualize-user.php --user username - -./cli/import-for-user.php --user username --filename /path/to/file.ext -# The extension of the file { .json, .opml, .xml, .zip } is used to detect the type of import - -./cli/export-opml-for-user.php --user username > /path/to/file.opml.xml - -./cli/export-zip-for-user.php --user username ( --max-feed-entries 100 ) > /path/to/file.zip - ./cli/user-info.php -h --user username # -h is to use a human-readable format # --user can be a username, or '*' to loop on all users @@ -74,6 +68,20 @@ cd /usr/share/FreshRSS # 3) the date/time of last user action, 4) the size occupied, # and the number of: 5) categories, 6) feeds, 7) read articles, 8) unread articles, 9) favourites, and 10) tags +./cli/import-for-user.php --user username --filename /path/to/file.ext +# The extension of the file { .json, .opml, .xml, .zip } is used to detect the type of import + +./cli/export-sqlite-for-user.php --user username --filename /path/to/db.sqlite +# Export the user’s database to a new SQLite file. + +./cli/import-sqlite-for-user.php --user username [ --force-overwrite ] --filename /path/to/db.sqlite +# Import the user’s database from an SQLite file. +# --force-overwrite will clear the target user database before import (import only works on an empty user database) + +./cli/export-opml-for-user.php --user username > /path/to/file.opml.xml + +./cli/export-zip-for-user.php --user username [ --max-feed-entries 100 ] > /path/to/file.zip + ./cli/db-optimize.php --user username # Optimize database (reduces the size) for a given user (perform `OPTIMIZE TABLE` in MySQL, `VACUUM` in SQLite) ``` diff --git a/cli/export-sqlite-for-user.php b/cli/export-sqlite-for-user.php new file mode 100755 index 000000000..027d13f38 --- /dev/null +++ b/cli/export-sqlite-for-user.php @@ -0,0 +1,28 @@ +#!/usr/bin/php +dbCopy($filename, FreshRSS_DatabaseDAO::SQLITE_EXPORT); + +done($ok); diff --git a/cli/import-sqlite-for-user.php b/cli/import-sqlite-for-user.php new file mode 100755 index 000000000..f0e54e2fa --- /dev/null +++ b/cli/import-sqlite-for-user.php @@ -0,0 +1,34 @@ +#!/usr/bin/php +dbCopy($filename, FreshRSS_DatabaseDAO::SQLITE_IMPORT, $clearFirst); +if (!$ok) { + echo 'If you would like to clear the user database first, use the option --force-overwrite', "\n"; +} +invalidateHttpCache($username); + +done($ok); diff --git a/lib/Minz/ModelPdo.php b/lib/Minz/ModelPdo.php index 14510c983..4d5e47da9 100644 --- a/lib/Minz/ModelPdo.php +++ b/lib/Minz/ModelPdo.php @@ -17,7 +17,6 @@ class Minz_ModelPdo { private static $sharedBd = null; private static $sharedPrefix; private static $sharedCurrentUser; - protected static $sharedDbType; /** * $bd variable représentant la base de données @@ -27,18 +26,21 @@ class Minz_ModelPdo { protected $current_user; protected $prefix; - public function dbType() { - return self::$sharedDbType; - } - /** * 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) { + public function __construct($currentUser = null, $currentPrefix = null, $currentDb = null) { if ($currentUser === null) { $currentUser = Minz_Session::param('currentUser'); } + if ($currentPrefix !== null) { + $this->prefix = $currentPrefix; + } + if ($currentDb != null) { + $this->bd = $currentDb; + return; + } if (self::$useSharedBd && self::$sharedBd != null && ($currentUser == null || $currentUser === self::$sharedCurrentUser)) { $this->bd = self::$sharedBd; @@ -65,6 +67,7 @@ class Minz_ModelPdo { $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); break; case 'sqlite': $string = 'sqlite:' . join_path(DATA_PATH, 'users', $currentUser, 'db.sqlite'); @@ -89,7 +92,6 @@ class Minz_ModelPdo { break; } self::$sharedBd = $this->bd; - self::$sharedDbType = $db['type']; self::$sharedPrefix = $this->prefix; } catch (Exception $e) { throw new Minz_PDOConnectionException( @@ -114,17 +116,12 @@ class Minz_ModelPdo { public static function clean() { self::$sharedBd = null; + self::$sharedCurrentUser = ''; self::$sharedPrefix = ''; } - - public function disableBuffering() { - if ((self::$sharedDbType === 'mysql') && defined('PDO::MYSQL_ATTR_USE_BUFFERED_QUERY')) { - $this->bd->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); - } - } } -class MinzPDO extends PDO { +abstract class MinzPDO extends PDO { private static function check($statement) { if (preg_match('/^(?:UPDATE|INSERT|DELETE)/i', $statement)) { invalidateHttpCache(); @@ -135,6 +132,8 @@ class MinzPDO extends PDO { return $statement; } + abstract public function dbType(); + public function prepare($statement, $driver_options = array()) { MinzPDO::check($statement); $statement = $this->compatibility($statement); @@ -155,18 +154,30 @@ class MinzPDO extends PDO { } class MinzPDOMySql extends MinzPDO { + public function dbType() { + return 'mysql'; + } + public function lastInsertId($name = null) { return parent::lastInsertId(); //We discard the name, only used by PostgreSQL } } class MinzPDOSQLite extends MinzPDO { + public function dbType() { + return 'sqlite'; + } + public function lastInsertId($name = null) { return parent::lastInsertId(); //We discard the name, only used by PostgreSQL } } class MinzPDOPGSQL extends MinzPDO { + public function dbType() { + return 'pgsql'; + } + protected function compatibility($statement) { return str_replace(array('`', ' LIKE '), array('"', ' ILIKE '), $statement); } -- cgit v1.2.3 From 077e3cff458e4c36b364c065397d524025ee3de1 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 8 Oct 2019 20:52:03 +0200 Subject: Increase import size (#2563) * Increase import size This is merely a temporary workaround to allow at least some medium size imports https://framateam.org/freshrss/pl/7wbt4tcyetrfmris9xdcbq7uuw The import module should be rewritten to process files one by one and as data streams instead of loading multiple copies of the whole dataset in memory as is the case now :-( https://github.com/FreshRSS/FreshRSS/issues/1890 Note that the new SQLite export/import is distinct from this case. * Use parameter --- Docker/entrypoint.sh | 2 ++ app/Controllers/importExportController.php | 18 ++++++++++++++++++ 2 files changed, 20 insertions(+) (limited to 'app/Controllers/importExportController.php') diff --git a/Docker/entrypoint.sh b/Docker/entrypoint.sh index bb0e1bde0..dcbd7e231 100755 --- a/Docker/entrypoint.sh +++ b/Docker/entrypoint.sh @@ -6,6 +6,8 @@ chown -R :www-data . chmod -R g+r . && chmod -R g+w ./data/ find /etc/php*/ -name php.ini -exec sed -r -i "\\#^;?date.timezone#s#^.*#date.timezone = $TZ#" {} \; +find /etc/php*/ -name php.ini -exec sed -r -i "\\#^;?post_max_size#s#^.*#post_max_size = 32M#" {} \; +find /etc/php*/ -name php.ini -exec sed -r -i "\\#^;?upload_max_filesize#s#^.*#upload_max_filesize = 32M#" {} \; if [ -n "$CRON_MIN" ]; then ( diff --git a/app/Controllers/importExportController.php b/app/Controllers/importExportController.php index 93897dde5..f2ae8238e 100644 --- a/app/Controllers/importExportController.php +++ b/app/Controllers/importExportController.php @@ -29,7 +29,25 @@ class FreshRSS_importExport_Controller extends Minz_ActionController { Minz_View::prependTitle(_t('sub.import_export.title') . ' · '); } + private static function megabytes($size_str) { + switch (substr($size_str, -1)) { + case 'M': case 'm': return (int)$size_str; + case 'K': case 'k': return (int)$size_str / 1024; + case 'G': case 'g': return (int)$size_str * 1024; + } + return $size_str; + } + + private static function minimumMemory($mb) { + $mb = (int)$mb; + $ini = self::megabytes(ini_get('memory_limit')); + if ($ini < $mb) { + ini_set('memory_limit', $mb . 'M'); + } + } + public function importFile($name, $path, $username = null) { + self::minimumMemory(256); require_once(LIB_PATH . '/lib_opml.php'); $this->catDAO = new FreshRSS_CategoryDAO($username); -- cgit v1.2.3