MySQL-compatible WP-CLI export command

[batonac]

Hey! I was experimenting with moving site data in and out of SQLite with this plugin. The import step in was pretty straightforward using the following script:

#!/bin/bash

# Export WordPress database to a SQL file
wp db export wordpress.sql

# Convert MySQL to SQLite compatible SQL using https://github.com/dumblob/mysql2sqlite
mysql2sqlite.sh wordpress.sql > wordpress.sqlite

# Move SQLite database to the desired location
mkdir -p wp-content/database
sqlite3 wp-content/database/.ht.sqlite < wordpress.sqlite

# Install and activate the sqlite-database-integration plugin
wp plugin install sqlite-database-integration --activate

# Remove the exported WordPress database SQL file
rm -f wordpress.sql
rm -f wordpress.sqlite

echo "Done. The WordPress database has been converted to SQLite and the necessary plugins have been installed and activated."

So far my testing has been fairly smooth and highly performant, but I encountered a plugin incompatibility with a site I was building, and wanted to convert my data back. Every existing WordPress backup plugin I tried failed to export the SQLite Database. It seems that existing database backup plugins use mysqldump-php, which doesn't work with SQLite and this plugin.

After much trial and error (and help from ChatGPT), I used the following WP-CLI command to export a database file that could then be imported back into MySQL with wp db import:

require 'vendor/autoload.php';

use WP_CLI;
use WP_CLI_Command;

class SQLite_Integration_CLI extends WP_CLI_Command
{
    /**
     * Export the SQLite database to a MySQL-compatible SQL file.
     *
     * ## OPTIONS
     *
     * [<file>]
     * : The name of the SQL file to export to.
     *
     * ## EXAMPLES
     *
     *     wp sqlite export
     *     wp sqlite export mysql.sql
     *
     * @subcommand export
     */
    public function sqlite_export_command($args, $assoc_args)
    {
        global $wpdb;

        // Use the provided file name if one was given, otherwise use 'mysql.sql'
        $filename = isset($args[0]) ? $args[0] : 'mysql.sql';

        // Open the MySQL dump file
        $mysqlDump = fopen($filename, 'w');

        // Get the list of table names
        $tables = $wpdb->get_results("SELECT name FROM sqlite_master WHERE type='table'", ARRAY_N);

        foreach ($tables as $row) {
            $tableName = $row[0];
            $createTableQuery = $wpdb->get_var("SELECT sql FROM sqlite_master WHERE type='table' AND name='$tableName'");

            // Convert SQLite syntax to MySQL syntax
            $createTableQuery = str_replace('AUTOINCREMENT', 'AUTO_INCREMENT', $createTableQuery);
            $createTableQuery = str_replace('integer', 'int', $createTableQuery);
            $createTableQuery = str_replace('text', 'varchar(255)', $createTableQuery);
            $createTableQuery = str_replace('longvarchar(255)', 'TEXT', $createTableQuery);
            $createTableQuery = str_replace('tinyvarchar', 'varchar', $createTableQuery);
            $createTableQuery = str_replace('COLLATE NOCASE', 'COLLATE utf8mb4_general_ci', $createTableQuery);
            // convert double quotes to backticks, but don't corrupt serialized arrays
            $createTableQuery = preg_replace('/\s+"([^"]+)"\s+/', ' `$1` ', $createTableQuery);

            // Write the CREATE TABLE statement to the MySQL dump file
            fwrite($mysqlDump, $createTableQuery . ";\n");

            $rows = $wpdb->get_results("SELECT * FROM $tableName", ARRAY_N);

            foreach ($rows as $row) {
                $values = $wpdb->get_col_info('name');
                $data = [];
                foreach ($row as $key => $value) {
                    if ($value !== null) {
                        $value = addslashes($value);
                    } else {
                        $value = "";
                    }
                    $data[] = "`" . $values[$key] . "` = '" . $value . "'";
                }
                $insertLine = "INSERT INTO `$tableName` SET " . implode(', ', $data) . ';';
            
                // Write the INSERT INTO statement to the MySQL dump file
                fwrite($mysqlDump, $insertLine . "\n");
            }
        }

        // Close the MySQL dump file
        fclose($mysqlDump);

        WP_CLI::success("SQLite database exported to '$filename' in MySQL-compatible format.");
    }

}

WP_CLI::add_command('sqlite', 'SQLite_Integration_CLI');

This creates a new command wp sqlite export, but my suggestion is that this be adapted towards supporting database import and export using the existing wp db import/export WP-CLI commands when the SQLite Database Integration plugin is active.