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 <dev@marienfressinaud.fr>

* Addressing part of review

* Remove goto :cry:

* Travis

* Comment for SQLite case

* Fix missing fields when inserting

1 files changed, 41 insertions, 30 deletions

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) {