#!/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 '\\.'