author | Aurelien Campeas <aurelien.campeas@logilab.fr> |
Fri, 18 Oct 2013 16:58:48 +0200 | |
changeset 9302 | af36f459d2f0 |
parent 5832 | 16a4235e031f |
child 9460 | a2a0bc984863 |
permissions | -rw-r--r-- |
5831
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
1 |
def migrate_varchar_to_nvarchar(): |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
2 |
dbdriver = config.sources()['system']['db-driver'] |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
3 |
if dbdriver != "sqlserver2005": |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
4 |
return |
5832
16a4235e031f
delete trailing whitespace
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
5831
diff
changeset
|
5 |
|
5831
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
6 |
introspection_sql = """\ |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
7 |
SELECT table_schema, table_name, column_name, is_nullable, character_maximum_length |
5832
16a4235e031f
delete trailing whitespace
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
5831
diff
changeset
|
8 |
FROM information_schema.columns |
5831
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
9 |
WHERE data_type = 'VARCHAR' and table_name <> 'SYSDIAGRAMS' |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
10 |
""" |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
11 |
has_index_sql = """\ |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
12 |
SELECT i.name AS index_name, |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
13 |
i.type_desc, |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
14 |
i.is_unique, |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
15 |
i.is_unique_constraint |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
16 |
FROM sys.indexes AS i, sys.index_columns as j, sys.columns as k |
5832
16a4235e031f
delete trailing whitespace
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
5831
diff
changeset
|
17 |
WHERE is_hypothetical = 0 AND i.index_id <> 0 |
16a4235e031f
delete trailing whitespace
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
5831
diff
changeset
|
18 |
AND i.object_id = j.object_id |
5831
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
19 |
AND i.index_id = j.index_id |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
20 |
AND i.object_id = OBJECT_ID('%(table)s') |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
21 |
AND k.name = '%(col)s' |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
22 |
AND k.object_id=i.object_id |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
23 |
AND j.column_id = k.column_id;""" |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
24 |
|
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
25 |
generated_statements = [] |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
26 |
for schema, table, column, is_nullable, length in sql(introspection_sql, ask_confirm=False): |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
27 |
qualified_table = '[%s].[%s]' % (schema, table) |
5832
16a4235e031f
delete trailing whitespace
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
5831
diff
changeset
|
28 |
rset = sql(has_index_sql % {'table': qualified_table, 'col':column}, |
5831
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
29 |
ask_confirm = False) |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
30 |
drops = [] |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
31 |
creates = [] |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
32 |
for idx_name, idx_type, idx_unique, is_unique_constraint in rset: |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
33 |
if is_unique_constraint: |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
34 |
drops.append('ALTER TABLE %s DROP CONSTRAINT %s' % (qualified_table, idx_name)) |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
35 |
creates.append('ALTER TABLE %s ADD CONSTRAINT %s UNIQUE (%s)' % (qualified_table, idx_name, column)) |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
36 |
else: |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
37 |
drops.append('DROP INDEX %s ON %s' % (idx_name, qualified_table)) |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
38 |
if idx_unique: |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
39 |
unique = 'UNIQUE' |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
40 |
else: |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
41 |
unique = '' |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
42 |
creates.append('CREATE %s %s INDEX %s ON %s(%s)' % (unique, idx_type, idx_name, qualified_table, column)) |
5832
16a4235e031f
delete trailing whitespace
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
5831
diff
changeset
|
43 |
|
5831
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
44 |
if length == -1: |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
45 |
length = 'max' |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
46 |
if is_nullable == 'YES': |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
47 |
not_null = 'NULL' |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
48 |
else: |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
49 |
not_null = 'NOT NULL' |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
50 |
alter_sql = 'ALTER TABLE %s ALTER COLUMN %s NVARCHAR(%s) %s' % (qualified_table, column, length, not_null) |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
51 |
generated_statements+= drops + [alter_sql] + creates |
5832
16a4235e031f
delete trailing whitespace
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
5831
diff
changeset
|
52 |
|
16a4235e031f
delete trailing whitespace
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
5831
diff
changeset
|
53 |
|
5831
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
54 |
for statement in generated_statements: |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
55 |
print statement |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
56 |
sql(statement, ask_confirm=False) |
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
57 |
commit() |
5832
16a4235e031f
delete trailing whitespace
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
5831
diff
changeset
|
58 |
|
5831
561014b6d70a
Had thought this could be done in cube, but it really needs to be done earlier in the migration process
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
diff
changeset
|
59 |
migrate_varchar_to_nvarchar() |