1 def migrate_varchar_to_nvarchar():

     2     dbdriver  = config.sources()['system']['db-driver']

     3     if dbdriver != "sqlserver2005":

     4         return

     5         

     6     introspection_sql = """\

     7 SELECT table_schema, table_name, column_name, is_nullable, character_maximum_length

     8 FROM information_schema.columns 

     9 WHERE data_type = 'VARCHAR' and table_name <> 'SYSDIAGRAMS'

    10 """

    11     has_index_sql = """\

    12 SELECT i.name AS index_name,

    13        i.type_desc,

    14        i.is_unique,

    15        i.is_unique_constraint

    16 FROM sys.indexes AS i, sys.index_columns as j, sys.columns as k

    17 WHERE is_hypothetical = 0 AND i.index_id <> 0 

    18 AND i.object_id = j.object_id 

    19 AND i.index_id = j.index_id

    20 AND i.object_id = OBJECT_ID('%(table)s')

    21 AND k.name = '%(col)s'

    22 AND k.object_id=i.object_id

    23 AND j.column_id = k.column_id;"""

    24 

    25     generated_statements = []

    26     for schema, table, column, is_nullable, length in sql(introspection_sql, ask_confirm=False):

    27         qualified_table = '[%s].[%s]' % (schema, table)

    28         rset = sql(has_index_sql % {'table': qualified_table, 'col':column}, 

    29                    ask_confirm = False)

    30         drops = []

    31         creates = []

    32         for idx_name, idx_type, idx_unique, is_unique_constraint in rset:

    33             if is_unique_constraint:

    34                 drops.append('ALTER TABLE %s DROP CONSTRAINT %s' % (qualified_table, idx_name))

    35                 creates.append('ALTER TABLE %s ADD CONSTRAINT %s UNIQUE (%s)' % (qualified_table, idx_name, column))

    36             else:

    37                 drops.append('DROP INDEX %s ON %s' % (idx_name, qualified_table))

    38                 if idx_unique:

    39                     unique = 'UNIQUE'

    40                 else:

    41                     unique = ''

    42                 creates.append('CREATE %s %s INDEX %s ON %s(%s)' % (unique, idx_type, idx_name, qualified_table, column))

    43             

    44         if length == -1:

    45             length = 'max'

    46         if is_nullable == 'YES':

    47             not_null = 'NULL'

    48         else:

    49             not_null = 'NOT NULL'

    50         alter_sql = 'ALTER TABLE %s ALTER COLUMN %s NVARCHAR(%s) %s' % (qualified_table, column, length, not_null)

    51         generated_statements+= drops + [alter_sql] + creates

    52         

    53     

    54     for statement in generated_statements:

    55         print statement

    56         sql(statement, ask_confirm=False)

    57     commit()

    58     

    59 migrate_varchar_to_nvarchar()