Skip to content

Instantly share code, notes, and snippets.

@esperlu
Created May 26, 2011 14:09
Show Gist options
  • Save esperlu/993216 to your computer and use it in GitHub Desktop.
Save esperlu/993216 to your computer and use it in GitHub Desktop.
MySQL to Sqlite converter in Lua
#! /usr/bin/lua
-- Usage: mysql2sqlite.lua [data base name]
-- $ ./mysql2sqlite.lua my_database
-- $ ./mysql2sqlite.lua "my_database my_table"
-- $ ./mysql2sqlite
-- Set here the path to the temp file you want mysqldump to dump the database into
local mysqldump_file = '/tmp/lua.sql'
-- Set here your MySQL default database name and MySQL credentials
local dbName = arg[1] and arg[1] or 'default database name'
local dbUser, dbPw = '****', '***********'
-- Various variable initialisation
local fh, line, tableName, indexName, indexKey, findUs, replaceBy
local previous, keys = nil, {}
-- Extracts database from MySQL, remove <--no-data> if you want to extract all INSERT's
os.execute('mysqldump --compatible=ansi --no-data --skip-extended-insert --compact -u '..dbUser..' -p'..dbPw..' '..dbName..' > '..mysqldump_file)
fh, err = io.open(mysqldump_file, 'r')
if not fh then return print(err) end
for line in fh:lines() do
-- skip comments
if not line:find('^/%*') then
-- Print all INSERT's replacing `5 o\'clock` by the sqlite escape format: `5 o''clock`, then loop.
if line:find('^INSERT', 1) then
if line:find("\\'") then line = line:gsub("\\'", "''") end
print(line)
else
-- Remove the KEY declaration from the create block except for the PRIMARY KEY and store it in table for later use
-- then loop
if line:find('^ KEY') or line:find('^ UNIQUE KEY') or line:find('^ FULLTEXT KEY') and not line:find('PRIMARY') then
indexName = line:match('"([^"]+)')
indexKey = line:match('%("([^"]+)')
table.insert(keys, 'CREATE INDEX "'..tableName..'_'..indexName..'" ON "'..tableName..'"("'..indexKey..'");')
else
-- Stores table name in variable
if line:find('CREATE', 1, true) then
tableName = line:match('"([^"]+)', 1)
end
if line:find(');', 1, true) then
previous = previous:gsub(',$', '',1)
print(previous, '\n);')
previous = nil
else
if previous then print(previous) end
-- Various replacements of MySQL specifics
findUs = {
'AUTO_INCREMENT','auto_increment',
'CHARACTER SET [^ ]+ ','character set [^ ]+ ',
'DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'default current_timestamp on update current_timestamp',
'COLLATE [^ ]+','collate [^]+',
'UNSIGNED','unsigned',
'ENUM[^)]+%)', 'enum[^)]+%)',
'SET%([^)]+%)', 'set%([^)]+%)'
}
replaceBy = { '', '', '', '', '', '', '', '', '', '', 'text', 'text','text', 'text'}
for k, findMe in pairs(findUs) do
line = line:gsub(findMe, replaceBy[k])
end
previous = line
end
end
end
end
end
fh:close()
for k, v in pairs(keys) do
print(v)
end
@esperlu
Copy link
Author

esperlu commented May 27, 2011

Generalities

This small Lua script should help you to convert a mysqldump file into a Sqlite3 compatible file.

Lua is, surprisingly enough, faster than my awk version of this converter. However, for large files - multi million lines - you could also consider using a csv export from MySQL and the import Sqlite command.

As usual: your mileage may vary but if you have any issue with this snippet, ping me and I'll see what I can do.

Usage

Usual stuff:

  • copy paste this in a file, say: mysql2sqlite.lua file
  • make it executable: $ chmod +x mysql2sqlite.lua
  • run it $ /dir/to/mysql2sqlite.lua | sqlite3 newDataBase.sqlite or
    $ /dir/to/mysql2sqlite.lua "databaseName tableName" | sqlite3 newDataBase.sqlite

If you only need to create the table structures without data, just change:

- mysqldump --compatible=ansi --skip-extended-insert --compact -u **** -p******  datbase_name
+ mysqldump --compatible=ansi --no-data --compact -u **** -p******  datbase_name

Remarks

  • In the INSERTs, the single quote must be protected as Sqlite doesn't like the Ten o\'clock mysqldump format. It is changed to Ten o''clock
  • It isn't necessary to convert the varchar() data typa as Sqlite accepts them in version 3. Read more over Data Typa affinity in the Sqlite doc.
  • The AUTO_INCREMENT is not necessary as it is more or less automatically activated for keys declared as PRIMARY. Read more...
  • The COLLATE declaration is removed to let Sqlite collate by its default BINARY rule (Read more) . If BINARY is not what you are after, Sqlite let you define User Defined Collation
  • enum and set data types are not supported in Sqlite. They are therefore converted to text.
  • In a CREATE block, Sqlite only accepts a PRIMARY KEY declaration. Not the single KEY's that have to be removed from the block and added at the end. See example below. They are added after all the INSERT's to speed up the all process.
  • As far as I know, in MySQL, when you create a KEY (INDEX), it is attached in some way to its table. Not in Sqlite. Therefore, all KEY declaration are prefixed by its table name to avoid Sqlite error for duplicate key name.

Example

Typical mysqldump output:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE "clients" (
  "CODCLI" varchar(6) NOT NULL DEFAULT '',
  "NAME" varchar(10) NOT NULL DEFAULT '',
  "STAT" char(2) NOT NULL DEFAULT '',
  "VRP" char(2) NOT NULL DEFAULT '',
  "TEL" varchar(15) NOT NULL DEFAULT '',
  "EMAIL" varchar(100) NOT NULL,
  PRIMARY KEY ("CODCLI"),
  FULLTEXT KEY "NAME" ("NAME")
);
/*!40101 SET character_set_client = @saved_cs_client */;

This will be converted as:

CREATE TABLE "clients" (
  "CODCLI" varchar(6) NOT NULL DEFAULT '',
  "NAME" varchar(10) NOT NULL DEFAULT '',
  "STAT" char(2) NOT NULL DEFAULT '',
  "VRP" char(2) NOT NULL DEFAULT '',
  "TEL" varchar(15) NOT NULL DEFAULT '',
  "EMAIL" varchar(100) NOT NULL,
  PRIMARY KEY ("CODCLI")
);
CREATE INDEX "clients_NAME" ON "clients" ("NAME");

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment