#!/usr/bin/python

"""
Repair MS SQL
(c)2008 RAB Linux Indonesia

Proses dump pada tabel yang memiliki field bertipe NTEXT akan menemui kendala,
seperti yang dijelaskan di

  http://pymssql.sourceforge.net/troubleshooting.html#unicode

Script ini bermanfaat untuk "memperbaiki" tipe data tabel tersebut dengan cara
membuat tabel baru berawalan __ (underscore dua kali) diikuti nama tabel
yang bermasalah, contoh: __pegawai. Strukturnya sama dengan tabel aslinya, namun
field yang bertipe NTEXT diganti menjadi TEXT.

Kemudian gunakan mssql-dump.py untuk proses dump. Script ini akan mengambil
data dari tabel __pegawai untuk tabel pegawai. 
"""

import pymssql
import sys


dbname = sys.argv[1]

without_width = ['smalldatetime','smallint','int','tinyint','text']

nullables = {
  0: ' NOT NULL',
  1: ''
}

def gettype(fieldtype, fieldlength):
    if fieldtype == 'ntext':
        fieldtype = 'text'
    if fieldtype in without_width:
        return fieldtype
    return '%s(%s)' % (fieldtype, fieldlength)

def create_table( tablename ):
    s = ''
    fieldnames = []
    for fieldname, fieldtype, fieldlength, nullable in tables[tablename]:
        try:
            fieldnames.index(fieldname)
            continue
        except:
            fieldnames.append(fieldname)
        s += '\n  %s %s%s,' % (fieldname.lower(), gettype(fieldtype, fieldlength), nullables[nullable])
    return """
CREATE TABLE %s.%s (
%s
);""" % (dbname, tablename, s.strip('\n'))

def repair( tablename ):
    for fieldname, fieldtype, fieldlength, nullable in tables[tablename]:
        if fieldtype == 'ntext':
            sql = create_table( tablename )
            sql = sql.replace(
              'CREATE TABLE %s.%s' % (dbname, tablename),
              'CREATE TABLE %s.__%s' % (dbname, tablename))
            return sql

def query( sql ):
    print sql
    cur.execute( sql )


con = pymssql.connect(host='127.0.0.1:1433',user='sa',password='',database=dbname)
cur = con.cursor()

sql = "SELECT id, name FROM sysobjects WHERE xtype='U'"
cur.execute( sql )

cur1 = con.cursor()
tables = {}
while True:
    recs = cur.fetchall()
    for id, tablename in recs:
        tablename = tablename.lower()
        sql = """SELECT syscolumns.name, systypes.name, syscolumns.length, syscolumns.isnullable
FROM syscolumns, systypes
WHERE syscolumns.xtype = systypes.xtype AND syscolumns.id = %s
ORDER BY syscolumns.colid""" % id 
        cur1.execute( sql )
        fields = []
        while True:
            recs1 = cur1.fetchall()
            for rec1 in recs1:
                fields += [rec1]
            if not cur1.nextset():
                break
        tables[tablename] = fields
    if not cur.nextset():
        break

for tablename in tables.keys():
    sql = repair(tablename)
    if not sql:
        continue
    try:
        query( sql )
    except pymssql.DatabaseError, msg:
        print msg

    sql = "DELETE FROM %s.__%s" % (dbname, tablename)
    query( sql )

    sql = "INSERT INTO %s.__%s SELECT * FROM %s.%s" % (
      dbname, tablename, dbname, tablename)
    query( sql )

con.commit()
con.close()