1 from __future__ import print_function

     2 

     3 def migrate_varchar_to_nvarchar():

     4     dbdriver  = config.system_source_config['db-driver']

     5     if dbdriver != "sqlserver2005":

     6         return

     7 

     8     introspection_sql = """\

     9 SELECT table_schema, table_name, column_name, is_nullable, character_maximum_length

    10 FROM information_schema.columns

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

    12 """

    13     has_index_sql = """\

    14 SELECT i.name AS index_name,

    15        i.type_desc,

    16        i.is_unique,

    17        i.is_unique_constraint

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

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

    20 AND i.object_id = j.object_id

    21 AND i.index_id = j.index_id

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

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

    24 AND k.object_id=i.object_id

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

    26 

    27     generated_statements = []

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

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

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

    31                    ask_confirm = False)

    32         drops = []

    33         creates = []

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

    35             if is_unique_constraint:

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

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

    38             else:

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

    40                 if idx_unique:

    41                     unique = 'UNIQUE'

    42                 else:

    43                     unique = ''

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

    45 

    46         if length == -1:

    47             length = 'max'

    48         if is_nullable == 'YES':

    49             not_null = 'NULL'

    50         else:

    51             not_null = 'NOT NULL'

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

    53         generated_statements+= drops + [alter_sql] + creates

    54 

    55 

    56     for statement in generated_statements:

    57         print(statement)

    58         sql(statement, ask_confirm=False)

    59     commit()

    60 

    61 migrate_varchar_to_nvarchar()