misc/migration/3.21.0_Any.py
author Sylvain Thénault <sylvain.thenault@logilab.fr>
Fri, 12 Feb 2016 10:09:13 +0100
changeset 11108 c14087d08698
parent 10651 9ca33768473c
permissions -rw-r--r--
[migration] don't ask confirm for those sql queries
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
10589
7c23b7de2b8d [py3k] print function
Samuel Trégouët <samuel.tregouet@logilab.fr>
parents: 10556
diff changeset
     1
from __future__ import print_function
7c23b7de2b8d [py3k] print function
Samuel Trégouët <samuel.tregouet@logilab.fr>
parents: 10556
diff changeset
     2
10482
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
     3
from cubicweb.schema import PURE_VIRTUAL_RTYPES
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
     4
from cubicweb.server.schema2sql import rschema_has_table
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
     5
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
     6
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
     7
def add_foreign_keys():
10628
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
     8
    source = repo.system_source
10482
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
     9
    if not source.dbhelper.alter_column_support:
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    10
        return
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    11
    for rschema in schema.relations():
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    12
        if rschema.inlined:
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    13
            add_foreign_keys_inlined(rschema)
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    14
        elif rschema_has_table(rschema, skip_relations=PURE_VIRTUAL_RTYPES):
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    15
            add_foreign_keys_relation(rschema)
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    16
    for eschema in schema.entities():
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    17
        if eschema.final:
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    18
            continue
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    19
        add_foreign_key_etype(eschema)
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    20
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    21
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    22
def add_foreign_keys_relation(rschema):
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    23
    args = {'r': rschema.type}
10490
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    24
    count = sql('SELECT COUNT(*) FROM ('
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    25
                '    SELECT eid_from FROM %(r)s_relation'
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    26
                '  UNION'
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    27
                '    SELECT eid_to FROM %(r)s_relation'
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    28
                '  EXCEPT'
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    29
                '    SELECT eid FROM entities) AS eids' % args,
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    30
                ask_confirm=False)[0][0]
10482
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    31
    if count:
10589
7c23b7de2b8d [py3k] print function
Samuel Trégouët <samuel.tregouet@logilab.fr>
parents: 10556
diff changeset
    32
        print('%s references %d unknown entities, deleting' % (rschema, count))
10482
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    33
        sql('DELETE FROM %(r)s_relation '
10490
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    34
            'WHERE eid_from IN (SELECT eid_from FROM %(r)s_relation EXCEPT SELECT eid FROM entities)' % args)
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    35
        sql('DELETE FROM %(r)s_relation '
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    36
            'WHERE eid_to IN (SELECT eid_to FROM %(r)s_relation EXCEPT SELECT eid FROM entities)' % args)
10482
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    37
10628
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    38
    args['from_fk'] = '%(r)s_relation_eid_from_fkey' % args
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    39
    args['to_fk'] = '%(r)s_relation_eid_to_fkey' % args
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    40
    args['table'] = '%(r)s_relation' % args
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    41
    if repo.system_source.dbdriver == 'postgres':
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    42
        sql('ALTER TABLE %(table)s DROP CONSTRAINT IF EXISTS %(from_fk)s' % args,
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    43
            ask_confirm=False)
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    44
        sql('ALTER TABLE %(table)s DROP CONSTRAINT IF EXISTS %(to_fk)s' % args,
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    45
            ask_confirm=False)
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    46
    elif repo.system_source.dbdriver.startswith('sqlserver'):
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    47
        sql("IF OBJECT_ID('%(from_fk)s', 'F') IS NOT NULL "
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    48
            "ALTER TABLE %(table)s DROP CONSTRAINT %(from_fk)s" % args,
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    49
            ask_confirm=False)
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    50
        sql("IF OBJECT_ID('%(to_fk)s', 'F') IS NOT NULL "
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    51
            "ALTER TABLE %(table)s DROP CONSTRAINT %(to_fk)s" % args,
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    52
            ask_confirm=False)
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    53
    sql('ALTER TABLE %(table)s ADD CONSTRAINT %(from_fk)s '
10556
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
    54
        'FOREIGN KEY (eid_from) REFERENCES entities (eid)' % args,
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
    55
        ask_confirm=False)
10628
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    56
    sql('ALTER TABLE %(table)s ADD CONSTRAINT %(to_fk)s '
10556
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
    57
        'FOREIGN KEY (eid_to) REFERENCES entities (eid)' % args,
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
    58
        ask_confirm=False)
10482
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    59
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    60
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    61
def add_foreign_keys_inlined(rschema):
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    62
    for eschema in rschema.subjects():
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    63
        args = {'e': eschema.type, 'r': rschema.type}
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    64
        args['c'] = 'cw_%(e)s_cw_%(r)s_fkey' % args
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    65
10490
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    66
        if eschema.rdef(rschema).cardinality[0] == '1':
10556
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
    67
            broken_eids = sql('SELECT cw_eid FROM cw_%(e)s WHERE cw_%(r)s IS NULL' % args,
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
    68
                              ask_confirm=False)
10490
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    69
            if broken_eids:
10589
7c23b7de2b8d [py3k] print function
Samuel Trégouët <samuel.tregouet@logilab.fr>
parents: 10556
diff changeset
    70
                print('Required relation %(e)s.%(r)s missing' % args)
10538
a2d2383612f3 [migration/3.21] fix stupid error in migration script
Julien Cristau <julien.cristau@logilab.fr>
parents: 10490
diff changeset
    71
                args['eids'] = ', '.join(str(eid) for eid, in broken_eids)
10490
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    72
                rql('DELETE %(e)s X WHERE X eid IN (%(eids)s)' % args)
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    73
            broken_eids = sql('SELECT cw_eid FROM cw_%(e)s WHERE cw_%(r)s IN (SELECT cw_%(r)s FROM cw_%(e)s '
10556
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
    74
                              'EXCEPT SELECT eid FROM entities)' % args,
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
    75
                              ask_confirm=False)
10490
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    76
            if broken_eids:
10589
7c23b7de2b8d [py3k] print function
Samuel Trégouët <samuel.tregouet@logilab.fr>
parents: 10556
diff changeset
    77
                print('Required relation %(e)s.%(r)s references unknown objects, deleting subject entities' % args)
10538
a2d2383612f3 [migration/3.21] fix stupid error in migration script
Julien Cristau <julien.cristau@logilab.fr>
parents: 10490
diff changeset
    78
                args['eids'] = ', '.join(str(eid) for eid, in broken_eids)
10490
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    79
                rql('DELETE %(e)s X WHERE X eid IN (%(eids)s)' % args)
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    80
        else:
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    81
            if sql('SELECT COUNT(*) FROM ('
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    82
                   '    SELECT cw_%(r)s FROM cw_%(e)s WHERE cw_%(r)s IS NOT NULL'
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    83
                   '  EXCEPT'
10556
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
    84
                   '    SELECT eid FROM entities) AS eids' % args,
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
    85
                   ask_confirm=False)[0][0]:
10589
7c23b7de2b8d [py3k] print function
Samuel Trégouët <samuel.tregouet@logilab.fr>
parents: 10556
diff changeset
    86
                print('%(e)s.%(r)s references unknown entities, deleting relation' % args)
10490
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    87
                sql('UPDATE cw_%(e)s SET cw_%(r)s = NULL WHERE cw_%(r)s IS NOT NULL AND cw_%(r)s IN '
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
    88
                    '(SELECT cw_%(r)s FROM cw_%(e)s EXCEPT SELECT eid FROM entities)' % args)
10628
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    89
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    90
        if repo.system_source.dbdriver == 'postgres':
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    91
            sql('ALTER TABLE cw_%(e)s DROP CONSTRAINT IF EXISTS %(c)s' % args,
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    92
                ask_confirm=False)
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    93
        elif repo.system_source.dbdriver.startswith('sqlserver'):
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    94
            sql("IF OBJECT_ID('%(c)s', 'F') IS NOT NULL "
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    95
                "ALTER TABLE cw_%(e)s DROP CONSTRAINT %(c)s" % args,
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
    96
                ask_confirm=False)
10482
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
    97
        sql('ALTER TABLE cw_%(e)s ADD CONSTRAINT %(c)s '
10556
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
    98
            'FOREIGN KEY (cw_%(r)s) references entities(eid)' % args,
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
    99
            ask_confirm=False)
10482
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
   100
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
   101
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
   102
def add_foreign_key_etype(eschema):
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
   103
    args = {'e': eschema.type}
10490
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
   104
    if sql('SELECT COUNT(*) FROM ('
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
   105
           '    SELECT cw_eid FROM cw_%(e)s'
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
   106
           '  EXCEPT'
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
   107
           '    SELECT eid FROM entities) AS eids' % args,
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
   108
           ask_confirm=False)[0][0]:
10589
7c23b7de2b8d [py3k] print function
Samuel Trégouët <samuel.tregouet@logilab.fr>
parents: 10556
diff changeset
   109
        print('%(e)s has nonexistent entities, deleting' % args)
10490
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
   110
        sql('DELETE FROM cw_%(e)s WHERE cw_eid IN '
76ab3c71aff2 [migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents: 10484
diff changeset
   111
            '(SELECT cw_eid FROM cw_%(e)s EXCEPT SELECT eid FROM entities)' % args)
10628
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   112
    args['c'] = 'cw_%(e)s_cw_eid_fkey' % args
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   113
    if repo.system_source.dbdriver == 'postgres':
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   114
        sql('ALTER TABLE cw_%(e)s DROP CONSTRAINT IF EXISTS %(c)s' % args,
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   115
            ask_confirm=False)
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   116
    elif repo.system_source.dbdriver.startswith('sqlserver'):
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   117
        sql("IF OBJECT_ID('%(c)s', 'F') IS NOT NULL "
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   118
            "ALTER TABLE cw_%(e)s DROP CONSTRAINT %(c)s" % args,
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   119
            ask_confirm=False)
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   120
    sql('ALTER TABLE cw_%(e)s ADD CONSTRAINT %(c)s '
10556
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
   121
        'FOREIGN KEY (cw_eid) REFERENCES entities (eid)' % args,
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
   122
        ask_confirm=False)
10482
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
   123
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
   124
88119421a09c [migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents: 10448
diff changeset
   125
add_foreign_keys()
10208
249126034c0e Add unique index on entities.extid
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   126
10483
c8dbb845b465 [migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents: 10482
diff changeset
   127
cu = session.cnxset.cu
10208
249126034c0e Add unique index on entities.extid
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   128
helper = repo.system_source.dbhelper
10483
c8dbb845b465 [migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents: 10482
diff changeset
   129
c8dbb845b465 [migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents: 10482
diff changeset
   130
helper.drop_index(cu, 'entities', 'extid', False)
10626
93f8d239c708 [server] use sqls_create_multicol_unique_index to index entities.extid
Julien Cristau <julien.cristau@logilab.fr>
parents: 10556
diff changeset
   131
# don't use create_index because it doesn't work for columns that may be NULL
93f8d239c708 [server] use sqls_create_multicol_unique_index to index entities.extid
Julien Cristau <julien.cristau@logilab.fr>
parents: 10556
diff changeset
   132
# on sqlserver
93f8d239c708 [server] use sqls_create_multicol_unique_index to index entities.extid
Julien Cristau <julien.cristau@logilab.fr>
parents: 10556
diff changeset
   133
for query in helper.sqls_create_multicol_unique_index('entities', ['extid']):
93f8d239c708 [server] use sqls_create_multicol_unique_index to index entities.extid
Julien Cristau <julien.cristau@logilab.fr>
parents: 10556
diff changeset
   134
    cu.execute(query)
10209
4c64a41c0a1d Use a moved_entities table to record external entities moved to the system source
Julien Cristau <julien.cristau@logilab.fr>
parents: 10208
diff changeset
   135
10483
c8dbb845b465 [migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents: 10482
diff changeset
   136
if 'moved_entities' not in helper.list_tables(cu):
c8dbb845b465 [migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents: 10482
diff changeset
   137
    sql('''
c8dbb845b465 [migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents: 10482
diff changeset
   138
    CREATE TABLE moved_entities (
c8dbb845b465 [migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents: 10482
diff changeset
   139
      eid INTEGER PRIMARY KEY NOT NULL,
c8dbb845b465 [migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents: 10482
diff changeset
   140
      extid VARCHAR(256) UNIQUE
c8dbb845b465 [migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents: 10482
diff changeset
   141
    )
c8dbb845b465 [migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents: 10482
diff changeset
   142
    ''')
10209
4c64a41c0a1d Use a moved_entities table to record external entities moved to the system source
Julien Cristau <julien.cristau@logilab.fr>
parents: 10208
diff changeset
   143
10556
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
   144
moved_entities = sql('SELECT -eid, extid FROM entities WHERE eid < 0',
8fbff2247816 [migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents: 10538
diff changeset
   145
                     ask_confirm=False)
10628
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   146
if moved_entities:
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   147
    cu.executemany('INSERT INTO moved_entities (eid, extid) VALUES (%s, %s)',
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   148
                   moved_entities)
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   149
    sql('DELETE FROM entities WHERE eid < 0')
10209
4c64a41c0a1d Use a moved_entities table to record external entities moved to the system source
Julien Cristau <julien.cristau@logilab.fr>
parents: 10208
diff changeset
   150
10208
249126034c0e Add unique index on entities.extid
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff changeset
   151
commit()
10316
4ce8b8437838 [schemas] make CWEType.final default to False (closes #5049201)
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 10209
diff changeset
   152
4ce8b8437838 [schemas] make CWEType.final default to False (closes #5049201)
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 10209
diff changeset
   153
sync_schema_props_perms('CWEType')
10371
88577b10b31e [schema] add a unique index on cwuri
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10316
diff changeset
   154
88577b10b31e [schema] add a unique index on cwuri
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10316
diff changeset
   155
sync_schema_props_perms('cwuri')
10448
de5be53e2ea8 [migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents: 10371
diff changeset
   156
de5be53e2ea8 [migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents: 10371
diff changeset
   157
from cubicweb.server.schema2sql import check_constraint
de5be53e2ea8 [migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents: 10371
diff changeset
   158
de5be53e2ea8 [migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents: 10371
diff changeset
   159
for cwconstraint in rql('Any C WHERE R constrained_by C').entities():
de5be53e2ea8 [migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents: 10371
diff changeset
   160
    cwrdef = cwconstraint.reverse_constrained_by[0]
de5be53e2ea8 [migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents: 10371
diff changeset
   161
    rdef = cwrdef.yams_schema()
de5be53e2ea8 [migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents: 10371
diff changeset
   162
    cstr = rdef.constraint_by_eid(cwconstraint.eid)
de5be53e2ea8 [migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents: 10371
diff changeset
   163
    if cstr.type() not in ('BoundaryConstraint', 'IntervalBoundConstraint', 'StaticVocabularyConstraint'):
de5be53e2ea8 [migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents: 10371
diff changeset
   164
        continue
de5be53e2ea8 [migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents: 10371
diff changeset
   165
    cstrname, check = check_constraint(rdef.subject, rdef.object, rdef.rtype.type,
de5be53e2ea8 [migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents: 10371
diff changeset
   166
            cstr, helper, prefix='cw_')
10628
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   167
    args = {'e': rdef.subject.type, 'c': cstrname, 'v': check}
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   168
    if repo.system_source.dbdriver == 'postgres':
11108
c14087d08698 [migration] don't ask confirm for those sql queries
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10651
diff changeset
   169
        sql('ALTER TABLE cw_%(e)s DROP CONSTRAINT IF EXISTS %(c)s' % args, ask_confirm=False)
10628
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   170
    elif repo.system_source.dbdriver.startswith('sqlserver'):
8f32cdc3f4ec [migration/3.21] add support for sqlserver
Julien Cristau <julien.cristau@logilab.fr>
parents: 10626
diff changeset
   171
        sql("IF OBJECT_ID('%(c)s', 'C') IS NOT NULL "
11108
c14087d08698 [migration] don't ask confirm for those sql queries
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10651
diff changeset
   172
            "ALTER TABLE cw_%(e)s DROP CONSTRAINT %(c)s" % args, ask_confirm=False)
c14087d08698 [migration] don't ask confirm for those sql queries
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 10651
diff changeset
   173
    sql('ALTER TABLE cw_%(e)s ADD CONSTRAINT %(c)s CHECK(%(v)s)' % args, ask_confirm=False)
10448
de5be53e2ea8 [migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents: 10371
diff changeset
   174
commit()