Skip to content

Instantly share code, notes, and snippets.

@loranger
Last active July 6, 2020 14:16
Show Gist options
  • Save loranger/31b66006da47f6d29ab8e018ab6a9869 to your computer and use it in GitHub Desktop.
Save loranger/31b66006da47f6d29ab8e018ab6a9869 to your computer and use it in GitHub Desktop.
Convert SQLite dump file to MySQL dump
<?php
echo 'Dump SQLite database as standard SQL';
$dumper = new \App\SQLiteDumper('path/to/sqlite/dump.sql');
$dumper->toMySQLDump('path/to/mysql/dump.sql');
<?php
namespace App;
class SQLiteDumper extends \SQLite3
{
private $sql;
public function __construct(String $sqlite_path)
{
parent::__construct($sqlite_path);
$this->busyTimeout(5000);
}
private function getCreateInstructionForTable($tablename)
{
$create_code = $this->querySingle("SELECT sql FROM sqlite_master WHERE name = '$tablename'");
$create_code = preg_replace([
'/create table "(\w+)"/im',
'/AUTOINCREMENT/im',
'/boolean/im',
"/boolean DEFAULT 't'/im",
"/boolean DEFAULT 'f'/im",
"/DEFAULT 't'/im",
"/DEFAULT 'f'/im",
"/,'t'/im",
"/,'f'/im",
"/TINYINT\(1\) DEFAULT 0 NOT NULL/im",
"/TINYINT\(1\) DEFAULT 1 NOT NULL/im",
"/integer([ \),])/im",
"/varchar([ \),])/im",
"/ text([ \),])/im",
'/"/im',
], [
'CREATE TABLE IF NOT EXISTS `$1`',
'AUTO_INCREMENT',
'TINYINT(1)',
'TINYINT(1) DEFAULT 1',
'TINYINT(1) DEFAULT 0',
"DEFAULT 1",
"DEFAULT 0",
",1",
",0",
"TINYINT(1) NOT NULL DEFAULT 0",
"TINYINT(1) NOT NULL DEFAULT 1",
"int(11)$1",
"varchar(255)$1",
"varchar(255)$1",
"`",
], $create_code);
return $create_code;
}
private function process()
{
$this->sql = "";
$tables=$this->query("SELECT name FROM sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%';");
while ($table = $tables->fetchArray(SQLITE3_NUM)) {
$this->sql .= $this->getCreateInstructionForTable($table[0]).";\n\n";
$rows = $this->query("SELECT * FROM {$table[0]}");
if ($this->querySingle("SELECT count(*) FROM {$table[0]}")) {
$this->sql .= "INSERT IGNORE INTO {$table[0]} (";
$columns = $this->query("PRAGMA table_info({$table[0]})");
$fieldnames = [];
while ($column=$columns->fetchArray(SQLITE3_ASSOC)) {
$fieldnames[]=$column["name"];
}
$this->sql .= implode(",", $fieldnames).") VALUES";
while ($row = $rows->fetchArray(SQLITE3_ASSOC)) {
foreach ($row as $k => $v) {
$row[$k] = "'".\SQLite3::escapeString($v)."'";
}
$this->sql .= "\n(".implode(",", $row)."),";
}
$this->sql = rtrim($this->sql, ",") . ";\n\n";
}
}
}
public function toMySQLDump(String $file_path)
{
if (!$this->sql) {
$this->process();
}
file_put_contents($file_path, $this->sql);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment