MySql2Sqlite

MySql2Sqlite.php

#!/usr/bin/php
<?php

//https://github.com/oleteacher/mysql2sqlite

showMessage( "MySql2Sqlite v1.0" );
showMessage( "by Daniele Ugoletti" );
showMessage( "-------------------" );

// check the arguments
if ( $argc < 5)
{
	showMessageAndDie( "Usage: ".$argv[ 0 ]." <mysql_host> <mysql_username> <mysql_password> <dbName> [<outSqliteFile] [useUTF8 true|false]" );
}
if ( !class_exists( "PDO" ) )
{
	showMessageAndDie( "ERROR: PDO not supported" );
}

$hostname = $argv[ 1 ];
$username = $argv[ 2 ];
$password = $argv[ 3 ];
$database = $argv[ 4 ];
$outDb = $argc >= 6 ? $argv[ 5 ] : $argv[ 4 ].".db";
$useUTF8 = $argc >= 7 ? $argv[ 6 ] == "true" : false;

// only for OS X users that use MAMP, check if there is a mysql socket to use
$socket = file_exists( "/Applications/MAMP/tmp/mysql/mysql.sock" ) ? "/Applications/MAMP/tmp/mysql/mysql.sock" : ini_get( "mysqli.default_socket" );

// open mySql connection
try
{
	$options = $useUTF8 ? array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8' ) : array();
	$mysqli = new PDO( "mysql:host=".$hostname.";dbname=".$database.";unix_socket=".$socket, $username, $password, $options );
}
catch( PDOException $e )
{
	showMessageAndDie( $e->getMessage() );
}

// open Sqlite connection
try
{
	@unlink( $outDb );
	$sqlite = new PDO( "sqlite:".$outDb );
}
catch( PDOException $e )
{
	showMessageAndDie( $e->getMessage() );
}

showMessage( "Start converting mysql:".$database." to sqlite:".$outDb );
foreach ( $mysqli->query( "SHOW TABLES;" ) as $row )
{
	$tableName = $row[ 0 ];
	showMessage( "Convert table: ".$tableName );
	converTable( $mysqli, $sqlite, $tableName );
}
showMessage( "Done!" );


function converTable( $mysqli, $sqlite, $tableName )
{
	$createFields = array();
	$pkFields = array();
	$indexFields = array();
	$tableFields = array();
	
	foreach ( $mysqli->query( "SHOW COLUMNS FROM ".$tableName ) as $row )
	{
		$tableFields[] = $row[ "Field" ];
		$fieldType = "TEXT";
		if ( stripos( $row[ "Type" ], "int(" ) !== false )
		{
			$fieldType = "INTEGER";
		}
		elseif ( stripos( $row[ "Type" ], "datetime") !== false )
		{
			$fieldType = "DATETIME";
		}
		elseif ( stripos( $row[ "Type" ], "date" ) !== false )
		{
			$fieldType = "DATE";
		}

		if ( $row[ "Key" ] == "PRI" )
		{
			//$fieldType = "INTEGER";	
			$pkFields[] = $row[ "Field" ];
		}
		else if ( $row[ "Key" ] == "MUL" )
		{
			$indexFields[] = "CREATE INDEX ".$row[ "Field" ]."_index ON ".$tableName."(".$row[ "Field" ].")";
		}
		$createFields[] = $row[ "Field" ]." ".$fieldType;
	}
	
	if ( count( $pkFields ) )
	{
		array_push( $createFields, "PRIMARY KEY (".implode( ",", $pkFields ).")" );
	}

	// create the table
	$sqlite->exec( "CREATE TABLE ".$tableName." (".implode(",", $createFields).")" );

	// insert statement
	$insertSqlPart = str_repeat( "?,", count( $tableFields ) );
	$insertSqlPart = substr( $insertSqlPart, 0, -1 );
	$insertSql = "INSERT INTO ".$tableName."(".implode(",", $tableFields).") VALUES ( ".$insertSqlPart." ) ";
	$sth = $sqlite->prepare( $insertSql );
	
	// get the number of records in the table
	$sthCount = $mysqli->query( "SELECT count(*) FROM ".$tableName );
	$row = $sthCount->fetch();
	$numRows = $row[ 0 ];
	$sthCount->closeCursor();

	// read and convert all records
	$pageLength = 100000;
	$currentPage = 0;
	$i = 0;
	while ( true )
	{
		$sqlite->beginTransaction();
		foreach ( $mysqli->query( "SELECT * FROM ".$tableName." LIMIT ".$currentPage.",".$pageLength ) as $row )
		{
			$params = array();
			foreach( $tableFields as $v )
			{
				$params[] = $row[ $v ];
			}
		
			$r = $sth->execute( $params );
			if ( !$r )
			{
				// error
				showMessageAndDie( print_r( $sqlite->errorInfo(), true) );
			}
		
			$i++;
		}
		$sqlite->commit();

		if ( $i < $numRows )
		{
			echo ".";
			$currentPage += $pageLength;
		}
		else
		{
			break;
		}
	}
	
	showMessage( "  imported: ".$i." rows" );
	
	// create index
	if ( count( $indexFields ) )
	{
		showMessage( "  create index: ".implode( ";", $indexFields ) );
		$sqlite->exec( implode( ";", $indexFields ) );
	}
}

function showMessage( $message )
{
	echo $message."\n";
}

function showMessageAndDie( $message )
{
	die( $message."\n\n" );
}
?>