#!/usr/bin/python """ Migrasi dari MS SQL Server ke PostgreSQL (c)2008 RAB Linux Indonesia """ import pymssql import sys dbname = sys.argv[1] if sys.argv[2:]: onlytable = sys.argv[2] else: onlytable = '' types = { 'char': 'varchar(n)', 'decimal': 'float', 'image': 'bytea', 'int': 'int', 'nchar': 'varchar(n)', 'ntext': 'text', 'nvarchar': 'varchar(n)', 'smalldatetime': 'timestamp', 'smallint': 'smallint', 'sysname': 'varchar(n)', 'text': 'text', 'tinyint': 'smallint', 'varchar': 'varchar(n)' } nullables = { 0: ' NOT NULL', 1: '' } def gettype(fieldtype, fieldlength): return types[fieldtype].replace('(n)', '(%s)' % fieldlength) def create_table( tablename ): s = '' for fieldname, fieldtype, fieldlength, nullable in tables[tablename]: s += '\n %s %s%s,' % (fieldname.lower(), gettype(fieldtype, fieldlength), nullables[nullable]) return """ CREATE TABLE %s ( %s );""" % (tablename.lower(), s.strip('\n').strip(',')) def fieldnames( desc ): r = [] for field in desc: r += [field[0].lower()] return r def repair_table( tablename ): for fieldname, fieldtype, fieldlength, nullable in tables[tablename]: if fieldtype == 'ntext': return '__' + tablename return tablename def py2sql( value ): if value is None: return '\N' s = '' if type(value) != type(''): return value for ch in value: ascii = ord(ch) if ascii > 31 and ascii < 127: s += ch else: s += ' ' return s con = pymssql.connect(host='127.0.0.1:1433',user='sa',password='',database=dbname) cur = con.cursor() if onlytable: sql_onlytable = "AND lower(sysobjects.name) = '%s'" % onlytable else: sql_onlytable = '' sql = """ SELECT table_name=sysobjects.name, column_name=syscolumns.name, datatype=systypes.name, length=syscolumns.length, syscolumns.isnullable FROM sysobjects JOIN syscolumns ON sysobjects.id = syscolumns.id JOIN systypes ON syscolumns.xtype=systypes.xtype WHERE sysobjects.xtype='U' %s ORDER BY sysobjects.name,syscolumns.colid """ % sql_onlytable cur.execute(sql) tables = {} fields = [] while True: for rec in cur.fetchall(): tablename = rec[0].lower() if tablename.find('__') == 0: continue fieldname = rec[1].lower() s = '%s.%s' % (tablename, fieldname) if s in fields: continue fields += [s] if tables.has_key(tablename): tables[tablename] += [rec[1:]] else: tables[tablename] = [rec[1:]] if not cur.nextset(): break for tablename in tables.keys(): print create_table( tablename ) datatable = repair_table( tablename ) sql = "SELECT * FROM %s.%s" % (dbname, datatable) cur.execute(sql) print print 'COPY', tablename, 'FROM stdin;' while True: for rec in cur.fetchall(): s = '' for d in rec: s += '\t%s' % py2sql(d) print s.strip() if not cur.nextset(): break print '\\.'