yams #35380 type_from_constraints uses hardcoded VARCHAR [resolved]

Comments

• 2010/06/20 11:24

  The following function can be used in migration scripts for cubes running on SQL Server to fix the column type:

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