Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/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" );
- }
- ?>
Add Comment
Please, Sign In to add comment