Sunday, June 17, 2012

Convert MS Sql script to MySql

0 comments
Yesterday I got a work to convert a MS SQL CREATE TABLE script to MySql. There were some other stuffs like DROP PROCEDURE, ALTER TABLE in the script. But my client wanted me to convert only the CREATE TABLE parts. So rather converting this manually, I made a python script to achieve the task.
The script was like as follows:-

exec sp_dboption N'testdb', N'autoclose', N'true'
GO
exec sp_dboption N'testdb', N'bulkcopy', N'false'
GO
/****** Object:  Stored Procedure dbo.testproc    Script Date: 11/01/2003 15:25:29 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testproc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[testproc]
GO
CREATE TABLE [dbo].[table1] (
[OrgCode] [varchar] (5) NOT NULL ,
[OrgName] [varchar] (60) NOT NULL ,
[Logo] [image] NULL ,
[Col1] [bit] NOT NULL ,
[Col2] [varchar] (25) NOT NULL ,
[col3] [timestamp] NULL 
) ON [PRIMARY] 
GO

/****** Object:  Table [dbo].[table2]    Script Date: 11/01/2003 15:27:18 ******/
CREATE TABLE [dbo].[table2] (
[ORG_CODE] [varchar] (5) NOT NULL ,
[col1] [varchar] (10) NOT NULL ,
[col2] [timestamp] NULL 
) ON [PRIMARY]
GO

I found that every statement has been ended with GO command. I selected it as a delimiter for the script. At first, I read the whole file in my buffer. Then I extracted only the CREATE TABLE ... GO parts. I found there were 233 tables. Then I modified the CREATE TABLE parts by adding ` before column and table names, replacing [ and ] and also adding longblob and decimal(19,4) data type instead of the image and money data types.

The convert.py as follows:

#!/usr/bin/python -tt

import sys
import commands
import os
import re


def make_list_create_table(script_text):
 
  list = script_text.split('\nGO')
  list_create_table = []
  for i in range(len(list)):
    match = re.search('CREATE TABLE',list[i])
    if match:
  list_create_table.append(list[i])

  return list_create_table
def convert_create_table_for_mysql(list_create_table):
  for i in range(len(list_create_table)):
    s = list_create_table[i]
 
    m = re.search('create table(.*)\([\r\n a-zA-Z0-9,\t_\)\( \[\]]+\)',s,flags=re.DOTALL|re.IGNORECASE)
    if m:
      s = m.group()
      #replace [] from column name and add `
      s = re.sub(r'''([\(,]+[\r\n\t ]*)\[([ a-zA-Z_0-9]+)\]''', r'''\1`\2`''', s, flags=re.DOTALL|re.IGNORECASE)
      #replace all other []
      s = s.replace('[','')
      s = s.replace(']','')
      #replace dbo. and add ` for table name
      s = re.sub(r'''dbo.([a-zA-Z0-9_]+) ''', r'''`\1`''',s, flags=re.DOTALL|re.IGNORECASE)
      s = s + ' ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\n'
      list_create_table[i] = s
   
    else:
      print "!!!not match!!!"
 
  return   list_create_table


def write_file(file_path,str):
  f = open(file_path,'w')
  f.writelines(str)
  f.close
  return
def data_type_conversion(list_create_table):
  for i in range(len(list_create_table)):
    s = list_create_table[i]
    s = s.replace(' image ',' longblob ');
    s = s.replace(' money ',' decimal (19,4) ');
    #s = s.replace(' numeric ',' decimal ');
    list_create_table[i] = s
  return list_create_table
 
def main():
  file_path = 'E:\mssql_scriptsql'
  f = open(file_path,'r')
  script_text = f.read()
  f.close

  list_create_table = make_list_create_table(script_text)
  list_create_table = convert_create_table_for_mysql(list_create_table)
  list_create_table = data_type_conversion(list_create_table)
  write_file('e:\out.sql',list_create_table)
  return
if __name__ == '__main__':
 main()


Leave a Reply

Labels