1 from __future__ import print_function |
|
2 |
|
3 from cubicweb.schema import PURE_VIRTUAL_RTYPES |
|
4 from cubicweb.server.schema2sql import rschema_has_table |
|
5 |
|
6 |
|
7 def add_foreign_keys(): |
|
8 source = repo.system_source |
|
9 if not source.dbhelper.alter_column_support: |
|
10 return |
|
11 for rschema in schema.relations(): |
|
12 if rschema.inlined: |
|
13 add_foreign_keys_inlined(rschema) |
|
14 elif rschema_has_table(rschema, skip_relations=PURE_VIRTUAL_RTYPES): |
|
15 add_foreign_keys_relation(rschema) |
|
16 for eschema in schema.entities(): |
|
17 if eschema.final: |
|
18 continue |
|
19 add_foreign_key_etype(eschema) |
|
20 |
|
21 |
|
22 def add_foreign_keys_relation(rschema): |
|
23 args = {'r': rschema.type} |
|
24 count = sql('SELECT COUNT(*) FROM (' |
|
25 ' SELECT eid_from FROM %(r)s_relation' |
|
26 ' UNION' |
|
27 ' SELECT eid_to FROM %(r)s_relation' |
|
28 ' EXCEPT' |
|
29 ' SELECT eid FROM entities) AS eids' % args, |
|
30 ask_confirm=False)[0][0] |
|
31 if count: |
|
32 print('%s references %d unknown entities, deleting' % (rschema, count)) |
|
33 sql('DELETE FROM %(r)s_relation ' |
|
34 'WHERE eid_from IN (SELECT eid_from FROM %(r)s_relation EXCEPT SELECT eid FROM entities)' % args) |
|
35 sql('DELETE FROM %(r)s_relation ' |
|
36 'WHERE eid_to IN (SELECT eid_to FROM %(r)s_relation EXCEPT SELECT eid FROM entities)' % args) |
|
37 |
|
38 args['from_fk'] = '%(r)s_relation_eid_from_fkey' % args |
|
39 args['to_fk'] = '%(r)s_relation_eid_to_fkey' % args |
|
40 args['table'] = '%(r)s_relation' % args |
|
41 if repo.system_source.dbdriver == 'postgres': |
|
42 sql('ALTER TABLE %(table)s DROP CONSTRAINT IF EXISTS %(from_fk)s' % args, |
|
43 ask_confirm=False) |
|
44 sql('ALTER TABLE %(table)s DROP CONSTRAINT IF EXISTS %(to_fk)s' % args, |
|
45 ask_confirm=False) |
|
46 elif repo.system_source.dbdriver.startswith('sqlserver'): |
|
47 sql("IF OBJECT_ID('%(from_fk)s', 'F') IS NOT NULL " |
|
48 "ALTER TABLE %(table)s DROP CONSTRAINT %(from_fk)s" % args, |
|
49 ask_confirm=False) |
|
50 sql("IF OBJECT_ID('%(to_fk)s', 'F') IS NOT NULL " |
|
51 "ALTER TABLE %(table)s DROP CONSTRAINT %(to_fk)s" % args, |
|
52 ask_confirm=False) |
|
53 sql('ALTER TABLE %(table)s ADD CONSTRAINT %(from_fk)s ' |
|
54 'FOREIGN KEY (eid_from) REFERENCES entities (eid)' % args, |
|
55 ask_confirm=False) |
|
56 sql('ALTER TABLE %(table)s ADD CONSTRAINT %(to_fk)s ' |
|
57 'FOREIGN KEY (eid_to) REFERENCES entities (eid)' % args, |
|
58 ask_confirm=False) |
|
59 |
|
60 |
|
61 def add_foreign_keys_inlined(rschema): |
|
62 for eschema in rschema.subjects(): |
|
63 args = {'e': eschema.type, 'r': rschema.type} |
|
64 args['c'] = 'cw_%(e)s_cw_%(r)s_fkey' % args |
|
65 |
|
66 if eschema.rdef(rschema).cardinality[0] == '1': |
|
67 broken_eids = sql('SELECT cw_eid FROM cw_%(e)s WHERE cw_%(r)s IS NULL' % args, |
|
68 ask_confirm=False) |
|
69 if broken_eids: |
|
70 print('Required relation %(e)s.%(r)s missing' % args) |
|
71 args['eids'] = ', '.join(str(eid) for eid, in broken_eids) |
|
72 rql('DELETE %(e)s X WHERE X eid IN (%(eids)s)' % args) |
|
73 broken_eids = sql('SELECT cw_eid FROM cw_%(e)s WHERE cw_%(r)s IN (SELECT cw_%(r)s FROM cw_%(e)s ' |
|
74 'EXCEPT SELECT eid FROM entities)' % args, |
|
75 ask_confirm=False) |
|
76 if broken_eids: |
|
77 print('Required relation %(e)s.%(r)s references unknown objects, deleting subject entities' % args) |
|
78 args['eids'] = ', '.join(str(eid) for eid, in broken_eids) |
|
79 rql('DELETE %(e)s X WHERE X eid IN (%(eids)s)' % args) |
|
80 else: |
|
81 if sql('SELECT COUNT(*) FROM (' |
|
82 ' SELECT cw_%(r)s FROM cw_%(e)s WHERE cw_%(r)s IS NOT NULL' |
|
83 ' EXCEPT' |
|
84 ' SELECT eid FROM entities) AS eids' % args, |
|
85 ask_confirm=False)[0][0]: |
|
86 print('%(e)s.%(r)s references unknown entities, deleting relation' % args) |
|
87 sql('UPDATE cw_%(e)s SET cw_%(r)s = NULL WHERE cw_%(r)s IS NOT NULL AND cw_%(r)s IN ' |
|
88 '(SELECT cw_%(r)s FROM cw_%(e)s EXCEPT SELECT eid FROM entities)' % args) |
|
89 |
|
90 if repo.system_source.dbdriver == 'postgres': |
|
91 sql('ALTER TABLE cw_%(e)s DROP CONSTRAINT IF EXISTS %(c)s' % args, |
|
92 ask_confirm=False) |
|
93 elif repo.system_source.dbdriver.startswith('sqlserver'): |
|
94 sql("IF OBJECT_ID('%(c)s', 'F') IS NOT NULL " |
|
95 "ALTER TABLE cw_%(e)s DROP CONSTRAINT %(c)s" % args, |
|
96 ask_confirm=False) |
|
97 sql('ALTER TABLE cw_%(e)s ADD CONSTRAINT %(c)s ' |
|
98 'FOREIGN KEY (cw_%(r)s) references entities(eid)' % args, |
|
99 ask_confirm=False) |
|
100 |
|
101 |
|
102 def add_foreign_key_etype(eschema): |
|
103 args = {'e': eschema.type} |
|
104 if sql('SELECT COUNT(*) FROM (' |
|
105 ' SELECT cw_eid FROM cw_%(e)s' |
|
106 ' EXCEPT' |
|
107 ' SELECT eid FROM entities) AS eids' % args, |
|
108 ask_confirm=False)[0][0]: |
|
109 print('%(e)s has nonexistent entities, deleting' % args) |
|
110 sql('DELETE FROM cw_%(e)s WHERE cw_eid IN ' |
|
111 '(SELECT cw_eid FROM cw_%(e)s EXCEPT SELECT eid FROM entities)' % args) |
|
112 args['c'] = 'cw_%(e)s_cw_eid_fkey' % args |
|
113 if repo.system_source.dbdriver == 'postgres': |
|
114 sql('ALTER TABLE cw_%(e)s DROP CONSTRAINT IF EXISTS %(c)s' % args, |
|
115 ask_confirm=False) |
|
116 elif repo.system_source.dbdriver.startswith('sqlserver'): |
|
117 sql("IF OBJECT_ID('%(c)s', 'F') IS NOT NULL " |
|
118 "ALTER TABLE cw_%(e)s DROP CONSTRAINT %(c)s" % args, |
|
119 ask_confirm=False) |
|
120 sql('ALTER TABLE cw_%(e)s ADD CONSTRAINT %(c)s ' |
|
121 'FOREIGN KEY (cw_eid) REFERENCES entities (eid)' % args, |
|
122 ask_confirm=False) |
|
123 |
|
124 |
|
125 add_foreign_keys() |
|
126 |
|
127 cu = session.cnxset.cu |
|
128 helper = repo.system_source.dbhelper |
|
129 |
|
130 helper.drop_index(cu, 'entities', 'extid', False) |
|
131 # don't use create_index because it doesn't work for columns that may be NULL |
|
132 # on sqlserver |
|
133 for query in helper.sqls_create_multicol_unique_index('entities', ['extid']): |
|
134 cu.execute(query) |
|
135 |
|
136 if 'moved_entities' not in helper.list_tables(cu): |
|
137 sql(''' |
|
138 CREATE TABLE moved_entities ( |
|
139 eid INTEGER PRIMARY KEY NOT NULL, |
|
140 extid VARCHAR(256) UNIQUE |
|
141 ) |
|
142 ''') |
|
143 |
|
144 moved_entities = sql('SELECT -eid, extid FROM entities WHERE eid < 0', |
|
145 ask_confirm=False) |
|
146 if moved_entities: |
|
147 cu.executemany('INSERT INTO moved_entities (eid, extid) VALUES (%s, %s)', |
|
148 moved_entities) |
|
149 sql('DELETE FROM entities WHERE eid < 0') |
|
150 |
|
151 commit() |
|
152 |
|
153 sync_schema_props_perms('CWEType') |
|
154 |
|
155 sync_schema_props_perms('cwuri') |
|
156 |
|
157 from cubicweb.server.schema2sql import check_constraint |
|
158 |
|
159 for cwconstraint in rql('Any C WHERE R constrained_by C').entities(): |
|
160 cwrdef = cwconstraint.reverse_constrained_by[0] |
|
161 rdef = cwrdef.yams_schema() |
|
162 cstr = rdef.constraint_by_eid(cwconstraint.eid) |
|
163 if cstr.type() not in ('BoundaryConstraint', 'IntervalBoundConstraint', 'StaticVocabularyConstraint'): |
|
164 continue |
|
165 cstrname, check = check_constraint(rdef.subject, rdef.object, rdef.rtype.type, |
|
166 cstr, helper, prefix='cw_') |
|
167 args = {'e': rdef.subject.type, 'c': cstrname, 'v': check} |
|
168 if repo.system_source.dbdriver == 'postgres': |
|
169 sql('ALTER TABLE cw_%(e)s DROP CONSTRAINT IF EXISTS %(c)s' % args) |
|
170 elif repo.system_source.dbdriver.startswith('sqlserver'): |
|
171 sql("IF OBJECT_ID('%(c)s', 'C') IS NOT NULL " |
|
172 "ALTER TABLE cw_%(e)s DROP CONSTRAINT %(c)s" % args) |
|
173 sql('ALTER TABLE cw_%(e)s ADD CONSTRAINT %(c)s CHECK(%(v)s)' % args) |
|
174 commit() |
|