# HG changeset patch
# User Alexandre Fayolle <alexandre.fayolle@logilab.fr>
# Date 1277374201 -7200
# Node ID 561014b6d70a8999bd6c7d9c8dceb985e69cab3b
# Parent  23ea56123670d02fc5939700ca4c699f9ee1f49c
Had thought this could be done in cube, but it really needs to be done earlier in the migration process

diff -r 23ea56123670 -r 561014b6d70a misc/migration/3.8.5_Any.py
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/misc/migration/3.8.5_Any.py	Thu Jun 24 12:10:01 2010 +0200
@@ -0,0 +1,59 @@
+def migrate_varchar_to_nvarchar():
+    dbdriver  = config.sources()['system']['db-driver']
+    if dbdriver != "sqlserver2005":
+        return
+        
+    introspection_sql = """\
+SELECT table_schema, table_name, column_name, is_nullable, character_maximum_length
+FROM information_schema.columns 
+WHERE data_type = 'VARCHAR' and table_name <> 'SYSDIAGRAMS'
+"""
+    has_index_sql = """\
+SELECT i.name AS index_name,
+       i.type_desc,
+       i.is_unique,
+       i.is_unique_constraint
+FROM sys.indexes AS i, sys.index_columns as j, sys.columns as k
+WHERE is_hypothetical = 0 AND i.index_id <> 0 
+AND i.object_id = j.object_id 
+AND i.index_id = j.index_id
+AND i.object_id = OBJECT_ID('%(table)s')
+AND k.name = '%(col)s'
+AND k.object_id=i.object_id
+AND j.column_id = k.column_id;"""
+
+    generated_statements = []
+    for schema, table, column, is_nullable, length in sql(introspection_sql, ask_confirm=False):
+        qualified_table = '[%s].[%s]' % (schema, table)
+        rset = sql(has_index_sql % {'table': qualified_table, 'col':column}, 
+                   ask_confirm = False)
+        drops = []
+        creates = []
+        for idx_name, idx_type, idx_unique, is_unique_constraint in rset:
+            if is_unique_constraint:
+                drops.append('ALTER TABLE %s DROP CONSTRAINT %s' % (qualified_table, idx_name))
+                creates.append('ALTER TABLE %s ADD CONSTRAINT %s UNIQUE (%s)' % (qualified_table, idx_name, column))
+            else:
+                drops.append('DROP INDEX %s ON %s' % (idx_name, qualified_table))
+                if idx_unique:
+                    unique = 'UNIQUE'
+                else:
+                    unique = ''
+                creates.append('CREATE %s %s INDEX %s ON %s(%s)' % (unique, idx_type, idx_name, qualified_table, column))
+            
+        if length == -1:
+            length = 'max'
+        if is_nullable == 'YES':
+            not_null = 'NULL'
+        else:
+            not_null = 'NOT NULL'
+        alter_sql = 'ALTER TABLE %s ALTER COLUMN %s NVARCHAR(%s) %s' % (qualified_table, column, length, not_null)
+        generated_statements+= drops + [alter_sql] + creates
+        
+    
+    for statement in generated_statements:
+        print statement
+        sql(statement, ask_confirm=False)
+    commit()
+    
+migrate_varchar_to_nvarchar()