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

3 files changed, 82 insertions, 12 deletions

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