mysql2sqlite.sh Auto_Increment

Question

original MySQl Tbl_driver

delimiter $$

CREATE TABLE `tbl_driver` (
  `_id` int(11) NOT NULL AUTO_INCREMENT,
  `Driver_Code` varchar(45) NOT NULL,
  `Driver_Name` varchar(45) NOT NULL,
  `AddBy_ID` int(11) NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1$$

mysql2sqlite.sh

#!/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

when execute this script, my sqlite database become like this

Sqlite Tbl_Driver

CREATE TABLE "tbl_driver" (
  "_id" int(11) NOT NULL ,
  "Driver_Code" varchar(45) NOT NULL,
  "Driver_Name" varchar(45) NOT NULL,
  "AddBy_ID" int(11) NOT NULL,
  PRIMARY KEY ("_id")
)

i want to change "_id" int(11) NOT NULL ,
become like this "_id" int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
or
become like this "_id" int(11) NOT NULL AUTO_INCREMENT,
with out primary key also can

any idea to modify this script?

Answer 1

The AUTO_INCREMENT keyword is specific to MySQL.

SQLite has a keyword AUTOINCREMENT (without the underscore) which means the column auto-generates monotonically increasing values that have never been used before in the table.

If you leave out the AUTOINCREMENT keyword (as the script you show does currently), SQLite assigns the ROWID to a new row, which means it will be a value 1 greater than the current greatest ROWID in the table. This could re-use values if you delete rows from the high end of the table and then insert new rows.

See http://www.sqlite.org/autoinc.html for more details.

If you want to modify this script to add the AUTOINCREMENT keyword, it looks like you could modify this line:

gsub( /AUTO_INCREMENT|auto_increment/, "" )

To this:

gsub( /AUTO_INCREMENT|auto_increment/, "AUTOINCREMENT" )

---

Re your comments:

Okay I tried it on a dummy table using sqlite3.

sqlite> create table foo ( 
  i int autoincrement, 
  primary key (i)
);
Error: near "autoincrement": syntax error

Apparently SQLite requires that autoincrement follow a column-level primary key constraint. It's not happy with the MySQL convention of putting the pk constraint at the end, as a table-level constraint. That's supported by the syntax diagrams in the SQLite documentation for CREATE TABLE.

Let's try putting primary key before autoincrement.

sqlite> create table foo ( 
  i int primary key autoincrement
);
Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

And apparently SQLite doesn't like "INT", it prefers "INTEGER":

sqlite> create table foo (
  i integer primary key autoincrement
);
sqlite>

Success!

So your awk script is not able to translate MySQL table DDL into SQLite as easily as you thought it would.

---

Re your comments:

You're trying to duplicate the work of a Perl module called SQL::Translator, which is a lot of work. I'm not going to write a full working script for you.

To really solve this, and make a script that can automate all syntax changes to make the DDL compatible with SQLite, you would need to implement a full parser for SQL DDL. This is not practical to do in awk.

I recommend that you use your script for some of the cases of keyword substitution, and then if further changes are necessary, fix them by hand in a text editor.

Also consider making compromises. If it's too difficult to reformat the DDL to use the AUTOINCREMENT feature in SQLite, consider if the default ROWID functionality is close enough. Read the link I posted above to understand the differences.