MySQL to Sqlite converter in Lua

mysql2sqlite.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

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");