diff options
| -rw-r--r-- | app/Controllers/importExportController.php | 2 | ||||
| -rw-r--r-- | app/Controllers/userController.php | 11 | ||||
| -rw-r--r-- | app/Models/CategoryDAO.php | 14 | ||||
| -rw-r--r-- | app/Models/DatabaseDAO.php | 170 | ||||
| -rw-r--r-- | app/Models/EntryDAO.php | 32 | ||||
| -rw-r--r-- | app/Models/EntryDAOPGSQL.php | 4 | ||||
| -rw-r--r-- | app/Models/EntryDAOSQLite.php | 8 | ||||
| -rw-r--r-- | app/Models/Factory.php | 4 | ||||
| -rw-r--r-- | app/Models/FeedDAO.php | 22 | ||||
| -rw-r--r-- | app/Models/TagDAO.php | 21 | ||||
| -rw-r--r-- | app/Models/UserDAO.php | 71 | ||||
| -rw-r--r-- | app/SQL/install.sql.sqlite.php | 12 | ||||
| -rw-r--r-- | cli/README.md | 32 | ||||
| -rwxr-xr-x | cli/export-sqlite-for-user.php | 28 | ||||
| -rwxr-xr-x | cli/import-sqlite-for-user.php | 34 | ||||
| -rw-r--r-- | lib/Minz/ModelPdo.php | 39 |
16 files changed, 421 insertions, 83 deletions
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, "<?php\n return " . var_export($userConfig, true) . ';') !== false); } if ($ok) { - $userDAO = new FreshRSS_UserDAO(); - $ok &= $userDAO->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 @@ <?php class FreshRSS_UserDAO extends Minz_ModelPdo { - public function createUser($username, $new_user_language, $insertDefaultFeeds = true) { - $db = FreshRSS_Context::$system_conf->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 +<?php +require(__DIR__ . '/_cli.php'); + +$params = [ + 'user:', + 'filename:', +]; + +$options = getopt('', $params); + +if (!validateOptions($argv, $params) || empty($options['user']) || empty($options['filename'])) { + fail('Usage: ' . basename(__FILE__) . ' --user username --filename /path/to/db.sqlite'); +} + +$username = cliInitUser($options['user']); +$filename = $options['filename']; + +if (pathinfo($filename, PATHINFO_EXTENSION) !== 'sqlite') { + fail('Only *.sqlite files are supported!'); +} + +echo 'FreshRSS exporting database to SQLite for user “', $username, "”…\n"; + +$databaseDAO = FreshRSS_Factory::createDatabaseDAO($username); +$ok = $databaseDAO->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 +<?php +require(__DIR__ . '/_cli.php'); + +$params = [ + 'user:', + 'filename:', + 'force-overwrite', +]; + +$options = getopt('', $params); + +if (!validateOptions($argv, $params) || empty($options['user']) || empty($options['filename'])) { + fail('Usage: ' . basename(__FILE__) . ' --user username --force-overwrite --filename /path/to/db.sqlite'); +} + +$username = cliInitUser($options['user']); +$filename = $options['filename']; + +if (pathinfo($filename, PATHINFO_EXTENSION) !== 'sqlite') { + fail('Only *.sqlite files are supported!'); +} + +echo 'FreshRSS importing database from SQLite for user “', $username, "”…\n"; + +$databaseDAO = FreshRSS_Factory::createDatabaseDAO($username); +$clearFirst = array_key_exists('force-overwrite', $options); +$ok = $databaseDAO->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); } |