Skip to content

Instantly share code, notes, and snippets.

@ugoletti

ugoletti/MySql2Sqlite.php

Created January 30, 2011 23:01
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ugoletti/803375 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ugoletti/803375 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ugoletti created this gist Jan 30, 2011.
    171 changes: 171 additions & 0 deletions MySql2Sqlite.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,171 @@
    #!/usr/bin/php
    <?php
    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" );
    }
    ?>