author | Samuel Trégouët <samuel.tregouet@logilab.fr> |
Fri, 11 Sep 2015 14:52:09 +0200 | |
changeset 10589 | 7c23b7de2b8d |
parent 10556 | 8fbff2247816 |
child 10651 | 9ca33768473c |
permissions | -rw-r--r-- |
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(): |
88119421a09c
[migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents:
10448
diff
changeset
|
8 |
source = repo.sources_by_uri['system'] |
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 |
|
10556
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
38 |
sql('ALTER TABLE %(r)s_relation DROP CONSTRAINT IF EXISTS %(r)s_relation_eid_from_fkey' % args, |
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
39 |
ask_confirm=False) |
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
40 |
sql('ALTER TABLE %(r)s_relation DROP CONSTRAINT IF EXISTS %(r)s_relation_eid_to_fkey' % args, |
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
41 |
ask_confirm=False) |
10482
88119421a09c
[migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents:
10448
diff
changeset
|
42 |
sql('ALTER TABLE %(r)s_relation ADD CONSTRAINT %(r)s_relation_eid_from_fkey ' |
10556
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
43 |
'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
|
44 |
ask_confirm=False) |
10482
88119421a09c
[migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents:
10448
diff
changeset
|
45 |
sql('ALTER TABLE %(r)s_relation ADD CONSTRAINT %(r)s_relation_eid_to_fkey ' |
10556
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
46 |
'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
|
47 |
ask_confirm=False) |
10482
88119421a09c
[migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents:
10448
diff
changeset
|
48 |
|
88119421a09c
[migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents:
10448
diff
changeset
|
49 |
|
88119421a09c
[migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents:
10448
diff
changeset
|
50 |
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
|
51 |
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
|
52 |
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
|
53 |
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
|
54 |
|
10490
76ab3c71aff2
[migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents:
10484
diff
changeset
|
55 |
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
|
56 |
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
|
57 |
ask_confirm=False) |
10490
76ab3c71aff2
[migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents:
10484
diff
changeset
|
58 |
if broken_eids: |
10589
7c23b7de2b8d
[py3k] print function
Samuel Trégouët <samuel.tregouet@logilab.fr>
parents:
10556
diff
changeset
|
59 |
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
|
60 |
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
|
61 |
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
|
62 |
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
|
63 |
'EXCEPT SELECT eid FROM entities)' % args, |
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
64 |
ask_confirm=False) |
10490
76ab3c71aff2
[migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents:
10484
diff
changeset
|
65 |
if broken_eids: |
10589
7c23b7de2b8d
[py3k] print function
Samuel Trégouët <samuel.tregouet@logilab.fr>
parents:
10556
diff
changeset
|
66 |
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
|
67 |
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
|
68 |
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
|
69 |
else: |
76ab3c71aff2
[migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents:
10484
diff
changeset
|
70 |
if sql('SELECT COUNT(*) FROM (' |
76ab3c71aff2
[migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents:
10484
diff
changeset
|
71 |
' 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
|
72 |
' EXCEPT' |
10556
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
73 |
' SELECT eid FROM entities) AS eids' % args, |
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
74 |
ask_confirm=False)[0][0]: |
10589
7c23b7de2b8d
[py3k] print function
Samuel Trégouët <samuel.tregouet@logilab.fr>
parents:
10556
diff
changeset
|
75 |
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
|
76 |
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
|
77 |
'(SELECT cw_%(r)s FROM cw_%(e)s EXCEPT SELECT eid FROM entities)' % args) |
10556
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
78 |
sql('ALTER TABLE cw_%(e)s DROP CONSTRAINT IF EXISTS %(c)s' % args, |
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
79 |
ask_confirm=False) |
10482
88119421a09c
[migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents:
10448
diff
changeset
|
80 |
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
|
81 |
'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
|
82 |
ask_confirm=False) |
10482
88119421a09c
[migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents:
10448
diff
changeset
|
83 |
|
88119421a09c
[migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents:
10448
diff
changeset
|
84 |
|
88119421a09c
[migration/3.21] Add missing foreign keys when upgrading
Julien Cristau <julien.cristau@logilab.fr>
parents:
10448
diff
changeset
|
85 |
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
|
86 |
args = {'e': eschema.type} |
10490
76ab3c71aff2
[migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents:
10484
diff
changeset
|
87 |
if sql('SELECT COUNT(*) FROM (' |
76ab3c71aff2
[migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents:
10484
diff
changeset
|
88 |
' 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
|
89 |
' EXCEPT' |
76ab3c71aff2
[migration/3.21.0] performance and reliability fixes
Julien Cristau <julien.cristau@logilab.fr>
parents:
10484
diff
changeset
|
90 |
' 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
|
91 |
ask_confirm=False)[0][0]: |
10589
7c23b7de2b8d
[py3k] print function
Samuel Trégouët <samuel.tregouet@logilab.fr>
parents:
10556
diff
changeset
|
92 |
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
|
93 |
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
|
94 |
'(SELECT cw_eid FROM cw_%(e)s EXCEPT SELECT eid FROM entities)' % args) |
10556
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
95 |
sql('ALTER TABLE cw_%(e)s DROP CONSTRAINT IF EXISTS cw_%(e)s_cw_eid_fkey' % args, |
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
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 cw_%(e)s_cw_eid_fkey ' |
10556
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
98 |
'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
|
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 |
add_foreign_keys() |
10208
249126034c0e
Add unique index on entities.extid
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
103 |
|
10483
c8dbb845b465
[migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents:
10482
diff
changeset
|
104 |
cu = session.cnxset.cu |
10208
249126034c0e
Add unique index on entities.extid
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
105 |
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
|
106 |
|
c8dbb845b465
[migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents:
10482
diff
changeset
|
107 |
helper.drop_index(cu, 'entities', 'extid', False) |
c8dbb845b465
[migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents:
10482
diff
changeset
|
108 |
helper.create_index(cu, 'entities', 'extid', True) |
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
|
109 |
|
10483
c8dbb845b465
[migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents:
10482
diff
changeset
|
110 |
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
|
111 |
sql(''' |
c8dbb845b465
[migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents:
10482
diff
changeset
|
112 |
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
|
113 |
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
|
114 |
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
|
115 |
) |
c8dbb845b465
[migration/3.21] Make index and table creation idempotent
Rémi Cardona <remi.cardona@logilab.fr>
parents:
10482
diff
changeset
|
116 |
''') |
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
|
117 |
|
10556
8fbff2247816
[migration/3.21] ask fewer questions
Julien Cristau <julien.cristau@logilab.fr>
parents:
10538
diff
changeset
|
118 |
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
|
119 |
ask_confirm=False) |
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
|
120 |
cu.executemany('INSERT INTO moved_entities (eid, extid) VALUES (%s, %s)', |
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
|
121 |
moved_entities) |
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
|
122 |
sql('DELETE FROM entities WHERE eid < 0') |
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
|
123 |
|
10208
249126034c0e
Add unique index on entities.extid
Julien Cristau <julien.cristau@logilab.fr>
parents:
diff
changeset
|
124 |
commit() |
10316
4ce8b8437838
[schemas] make CWEType.final default to False (closes #5049201)
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents:
10209
diff
changeset
|
125 |
|
4ce8b8437838
[schemas] make CWEType.final default to False (closes #5049201)
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents:
10209
diff
changeset
|
126 |
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
|
127 |
|
88577b10b31e
[schema] add a unique index on cwuri
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
10316
diff
changeset
|
128 |
sync_schema_props_perms('cwuri') |
10448
de5be53e2ea8
[migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents:
10371
diff
changeset
|
129 |
|
de5be53e2ea8
[migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents:
10371
diff
changeset
|
130 |
from cubicweb.server.schema2sql import check_constraint |
de5be53e2ea8
[migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents:
10371
diff
changeset
|
131 |
|
de5be53e2ea8
[migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents:
10371
diff
changeset
|
132 |
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
|
133 |
cwrdef = cwconstraint.reverse_constrained_by[0] |
de5be53e2ea8
[migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents:
10371
diff
changeset
|
134 |
rdef = cwrdef.yams_schema() |
de5be53e2ea8
[migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents:
10371
diff
changeset
|
135 |
cstr = rdef.constraint_by_eid(cwconstraint.eid) |
de5be53e2ea8
[migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents:
10371
diff
changeset
|
136 |
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
|
137 |
continue |
de5be53e2ea8
[migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents:
10371
diff
changeset
|
138 |
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
|
139 |
cstr, helper, prefix='cw_') |
10484
7f15552b7463
[migration/3.21] idempotency fixes
Rémi Cardona <remi.cardona@logilab.fr>
parents:
10483
diff
changeset
|
140 |
sql('ALTER TABLE %s%s DROP CONSTRAINT IF EXISTS %s' % ('cw_', rdef.subject.type, cstrname)) |
10448
de5be53e2ea8
[migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents:
10371
diff
changeset
|
141 |
sql('ALTER TABLE %s%s ADD CONSTRAINT %s CHECK(%s)' % ('cw_', rdef.subject.type, cstrname, check)) |
de5be53e2ea8
[migration] add sql constraints on upgrade
Julien Cristau <julien.cristau@logilab.fr>
parents:
10371
diff
changeset
|
142 |
commit() |