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() |
|