I have to generate on-the-fly (and let the user download) an SQLite 3 encrypted database, starting/cloning part of the current running MySQL database.
I need to export only a subset of the tables (i.e. products, categories, users), and I'd like to make the procedure dynamic, meaning that adding a field in MySQL shouldn't require changes in the procedure.
I need to some high-level "tips" to make the procedure as robust as possible.
Questions: how do I map column types in MySQL to SQLite types? How can I fetch metadata to determine the correct INSERT statements order? Should I need to lock MySQL database while dumping it into SQLite?
<?php
$sqlite = new PDO('sqlite::memory:', null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$mysql = new PDO('mysql:host=127.0.0.1;dbname=database', 'root', 'root', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
// Enable foreign keys in SQLite
$sqlite->exec('PRAGMA foreign_keys = 1');
// 1) Getting meta information from MySQL
// 2) CREATE TABLE ... in the SQLite schema, dynamically
// 3) INSERT INTO ... statements of SQLite (in the correct order)
// 4) Save the in-memory database to filesystem and send it to the browser
