misc/migration/3.8.5_Any.py
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--
[server] deprecate the old multi-source system It is removed in 3.19. Closes #2919299
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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()