Add commands to export and import SQLite databases

[danielbachhuber]

For increased compatibility with SQLite, it would be nice to be able to:

• Import a SQLite database into a MySQL database.
• Import a MySQL database into a SQLite database.
• Export a MySQL database to a SQLite database.
• Export a SQLite database to a MySQL database.

I think these features could live inside of wp db export and wp db import.

[wojtekn]

We could consider to have SQLite-MySQL and MySQL-SQLite implementation a part of https://github.com/wordPress/sqlite-database-integration/.

[wojtekn]

I talked with @schlessera and came up with the proposed plan:

1. Create a separate repository that holds the PHP library that supports:

• Exporting MySQL database to MySQL dump (using mysqldump like WP-CLI does now)
• Exporting MySQL database to SQLite dump
• Exporting SQLite database to SQLite dump
• Exporting SQLite database to MySQL dump
• And importing for all of the above cases

2. Use the library as a composer dependency of db-command
3. Refactor DB_Command::import() and DB_Command::export() to use the new library
4. Automatically guess the imported database format
5. Add support for --dialect option for wp db export to let the user choose MySQL or SQLite dialect

Then, other WP tools, like Sandbox Site powered by Playground or the sqlite-database-integration plugin, could reuse the import/export library.

Any thoughts? What feedback do you have?

CC @danielbachhuber @adamziel

[danielbachhuber]

This seems reasonable, although I would prefer to implement as a WP-CLI command first and then figure out the abstraction. I think the abstraction could quickly rabbit hole into zero progress.

[schlessera]

@danielbachhuber I get the concern, but I think the entire point here is to build something reusable. The way WP-CLI commands are usually built just results in spaghetti code that can hardly be salvaged - unless you have a strict conceptual separation from the start, at which point a distribution via a separate package is just a minor detail.
With a separate package, we can reuse that package on 2 or more fronts at the same time to make sure we exchange data.
I'll help with the architectural design to ensure we do this iteratively and don't get stuck along the way.

[danielbachhuber]

@schlessera Ok. We need a version of this in the short term, though. @wojtekn Maybe our first draft can be an internal implementation, and then we can produce a final version as a public library?

[adamziel]

+1 on separating this from a start. Starting as an internal implementation sounds good if that's later turned into a reusable library.

Exporting MySQL database to MySQL dump
Exporting MySQL database to SQLite dump
Exporting SQLite database to SQLite dump
Exporting SQLite database to MySQL dump

All these combinations are covered by a single, existing exporter.

The existing Sandbox Site plugin produces a MySQL-compliant dump from either MySQL or SQLite. That dump can then be imported into MySQL verbatim, or into SQLite using the SQLite database integration plugin.

Exporting MySQL database to MySQL dump (using mysqldump like WP-CLI does now)

mysqldump is great as an option when it's available. When it isn't installed or accessible (proc_open may be disabled) the PHP exporter fallback would still be useful. That's the only way we could eventually make it a part of WordPress core.

Looping in @brandonpayton.

[wojtekn]

We work on that under wp-cli/db-command#259

[vadimkantorov]

Some additional considerations:

• might be good to have an option to also include attachments/uploads as blobs in the exported sqlite db (for it to be a complete backup of site contents)
• sqlite sql dialect option (as mysql and sqlite DDLs are quite different, and even syntax for blob literals in INSERT statements)
• very useful would be to have an option to directly produce a binary sqlite .db file - in many circumstances we need this, and not the sql dump file (even if in sqlite dialect)

This feature idea is especially pertinent given that Sqlite integration is rapidly developing:

• Draft/POC for SQLite support in Core WordPress/wordpress-develop#3220
• Exhaustive MySQL Parser  WordPress/sqlite-database-integration#157

But even outside this context, having a single-file binary-file backup is nice to have and work with.