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 --- cli/README.md | 32 ++++++++++++++++++++------------ cli/export-sqlite-for-user.php | 28 ++++++++++++++++++++++++++++ cli/import-sqlite-for-user.php | 34 ++++++++++++++++++++++++++++++++++ 3 files changed, 82 insertions(+), 12 deletions(-) create mode 100755 cli/export-sqlite-for-user.php create mode 100755 cli/import-sqlite-for-user.php (limited to 'cli') 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); -- cgit v1.2.3