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/Models/UserDAO.php | 71 +++++++++++++++++++++++++++++--------------------- 1 file changed, 41 insertions(+), 30 deletions(-) (limited to 'app/Models/UserDAO.php') 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) { -- cgit v1.2.3