database commands | | | Search

This shell script uses awk to transform a MySQL dump file into a format compatible with SQLite, while preserving MySQL KEY clauses as separate commands. The script can be used by piping the MySQL dump file to the script, then piping the output to sqlite3, as shown in the example usage section.

Cell 0

#!/bin/sh

# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
# CREATE block and create them in separate commands _after_ all the INSERTs.

# Awk is choosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
# The mysqldump file is traversed only once.

# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite

# Thanks to and @artemyk and @gkuenning for their nice tweaks.

mysqldump  --compatible=ansi --skip-extended-insert --compact  "$@" | \

awk '

BEGIN {
	FS=",$"
	print "PRAGMA synchronous = OFF;"
	print "PRAGMA journal_mode = MEMORY;"
	print "BEGIN TRANSACTION;"
}

# CREATE TRIGGER statements have funny commenting.  Remember we are in trigger.
/^\/\*.*CREATE.*TRIGGER/ {
	gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
	print
	inTrigger = 1
	next
}

# The end of CREATE TRIGGER has a stray comment terminator
/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }

# The rest of triggers just get passed through
inTrigger != 0 { print; next }

# Skip other comments
/^\/\*/ { next }

# Print all `INSERT` lines. The single quotes are protected by another single quote.
/INSERT/ {
	gsub( /\\\047/, "\047\047" )
	gsub(/\\n/, "\n")
	gsub(/\\r/, "\r")
	gsub(/\\"/, "\"")
	gsub(/\\\\/, "\\")
	gsub(/\\\032/, "\032")
	print
	next
}

# Print the `CREATE` line as is and capture the table name.
/^CREATE/ {
	print
	if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 ) 
}

# Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
/^  [^"]+KEY/ && !/^  PRIMARY KEY/ { gsub( /.+KEY/, "  KEY" ) }

# Get rid of field lengths in KEY lines
/ KEY/ { gsub(/\([0-9]+\)/, "") }

# Print all fields definition lines except the `KEY` lines.
/^  / && !/^(  KEY|\);)/ {
	gsub( /AUTO_INCREMENT|auto_increment/, "" )
	gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
	gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
	gsub( /(COLLATE|collate) [^ ]+ /, "" )
	gsub(/(ENUM|enum)[^)]+\)/, "text ")
	gsub(/(SET|set)\([^)]+\)/, "text ")
	gsub(/UNSIGNED|unsigned/, "")
	if (prev) print prev ","
	prev = $1
}

# `KEY` lines are extracted from the `CREATE` block and stored in array for later print 
# in a separate `CREATE KEY` command. The index name is prefixed by the table name to 
# avoid a sqlite error for duplicate index name.
/^(  KEY|\);)/ {
	if (prev) print prev
	prev=""
	if ($0 == ");"){
		print
	} else {
		if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
		if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
		key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
	}
}

# Print all `KEY` creation lines.
END {
	for (table in key) printf key[table]
	print "END TRANSACTION;"
}
'
exit 0

What the code could have been:

bash
#!/bin/sh

# Converts a mysqldump file into a Sqlite 3 compatible file.
#
# Usage: $./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
#
# Example: $./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite

mysqldump  --compatible=ansi --skip-extended-insert --compact  "$@" | \
awk '
  BEGIN {
    FS=",$"
    print "PRAGMA synchronous = OFF;"
    print "PRAGMA journal_mode = MEMORY;"
    print "BEGIN TRANSACTION;"
  }

  # Process CREATE TRIGGER statements
  /^\/\*.*CREATE.*TRIGGER/ {
    gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
    print
    inTrigger = 1
    next
  }

  # Process end of CREATE TRIGGER
  /END \*\/;;/ {
    gsub( /\*\//, "" )
    print
    inTrigger = 0
    next
  }

  # Process TRIGGER statements
  inTrigger!= 0 { print; next }

  # Skip other comments
  /^\/\*/ { next }

  # Process INSERT statements
  /INSERT/ {
    gsub( /\\\047/, "\047\047" )
    gsub(/\\n/, "\n")
    gsub(/\\r/, "\r")
    gsub(/\\"/, "\"")
    gsub(/\\\\/, "\\")
    gsub(/\\\032/, "\032")
    print
    next
  }

  # Process CREATE statements
  /^CREATE/ {
    print
    if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 )
  }

  # Replace KEY statements
  /^  [^"]+KEY/ &&!/^  PRIMARY KEY/ {
    if ( match( $0, / FULLTEXT / ) ) gsub( / FULLTEXT KEY/, " FULLTEXT" )
    else if ( match( $0, / KEY / ) ) gsub( / KEY/, " KEY" )
  }

  # Get rid of field lengths in KEY lines
  / KEY/ { gsub(/\([0-9]+\)/, "") }

  # Process fields definition lines
  /^  / &&!/^(  KEY|\);)/ {
    gsub( /AUTO_INCREMENT|auto_increment/, "" )
    gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
    gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
    gsub( /(COLLATE|collate) [^ ]+ /, "" )
    gsub(/(ENUM|enum)[^)]+\)/, "text ")
    gsub(/(SET|set)\([^)]+\)/, "text ")
    gsub(/UNSIGNED|unsigned/, "")
    if (prev) prev = prev "," $1
    else prev = $1
  }

  # Process KEY lines
  /^(  KEY|\);)/ {
    if (prev) print prev
    prev=""
    if ($0 == ");") {
      print
    } else {
      if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 )
      if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 )
      key[tableName] = key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
    }
  }

  # Print all KEY creation lines
  END {
    for (table in key) printf key[table]
    print "END TRANSACTION;"
  }
'
exit 0

Breakdown of the Code

Overview

This is a shell script that utilizes awk to transform a MySQL dump file into a format compatible with SQLite. The script also extracts the MySQL KEY clauses and creates them as separate commands.

Script Structure

The script consists of:

  1. A shell script header that specifies the usage and an example command.
  2. A mysqldump command that generates the MySQL dump file, with options to make it compatible with SQLite and to skip extended inserts and compact the output.
  3. An awk script that processes the MySQL dump file.

Awk Script

The awk script consists of several sections:

  1. Initialization: Sets the field separator to a comma and prints some SQLite-specific commands to disable synchronous writes and journal mode.
  2. Create Trigger Handling: Identifies and removes comments from CREATE TRIGGER statements.
  3. Insert Statement Handling: Prints INSERT statements, escaping any necessary characters.
  4. Create Statement Handling: Prints CREATE statements and captures the table name.
  5. Key Handling: Replaces FULLTEXT KEY or other XXXXX KEY clauses with KEY, and removes field lengths in KEY lines.
  6. Field Definition Handling: Prints field definitions, excluding KEY lines and removing some MySQL-specific keywords.

Key Features

Example Usage

To use this script, simply pipe the MySQL dump file to the script, and then pipe the output to sqlite3:

./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite

This will transform the MySQL dump file into a format compatible with SQLite and import it into the specified database.