1 from cubicweb.schema import PURE_VIRTUAL_RTYPES |
1 from cubicweb.schema import PURE_VIRTUAL_RTYPES |
2 from cubicweb.server.schema2sql import rschema_has_table |
2 from cubicweb.server.schema2sql import rschema_has_table |
3 |
3 |
4 |
4 |
5 def add_foreign_keys(): |
5 def add_foreign_keys(): |
6 source = repo.sources_by_uri['system'] |
6 source = repo.system_source |
7 if not source.dbhelper.alter_column_support: |
7 if not source.dbhelper.alter_column_support: |
8 return |
8 return |
9 for rschema in schema.relations(): |
9 for rschema in schema.relations(): |
10 if rschema.inlined: |
10 if rschema.inlined: |
11 add_foreign_keys_inlined(rschema) |
11 add_foreign_keys_inlined(rschema) |
31 sql('DELETE FROM %(r)s_relation ' |
31 sql('DELETE FROM %(r)s_relation ' |
32 'WHERE eid_from IN (SELECT eid_from FROM %(r)s_relation EXCEPT SELECT eid FROM entities)' % args) |
32 'WHERE eid_from IN (SELECT eid_from FROM %(r)s_relation EXCEPT SELECT eid FROM entities)' % args) |
33 sql('DELETE FROM %(r)s_relation ' |
33 sql('DELETE FROM %(r)s_relation ' |
34 'WHERE eid_to IN (SELECT eid_to FROM %(r)s_relation EXCEPT SELECT eid FROM entities)' % args) |
34 'WHERE eid_to IN (SELECT eid_to FROM %(r)s_relation EXCEPT SELECT eid FROM entities)' % args) |
35 |
35 |
36 sql('ALTER TABLE %(r)s_relation DROP CONSTRAINT IF EXISTS %(r)s_relation_eid_from_fkey' % args, |
36 args['from_fk'] = '%(r)s_relation_eid_from_fkey' % args |
37 ask_confirm=False) |
37 args['to_fk'] = '%(r)s_relation_eid_to_fkey' % args |
38 sql('ALTER TABLE %(r)s_relation DROP CONSTRAINT IF EXISTS %(r)s_relation_eid_to_fkey' % args, |
38 args['table'] = '%(r)s_relation' % args |
39 ask_confirm=False) |
39 if repo.system_source.dbdriver == 'postgres': |
40 sql('ALTER TABLE %(r)s_relation ADD CONSTRAINT %(r)s_relation_eid_from_fkey ' |
40 sql('ALTER TABLE %(table)s DROP CONSTRAINT IF EXISTS %(from_fk)s' % args, |
|
41 ask_confirm=False) |
|
42 sql('ALTER TABLE %(table)s DROP CONSTRAINT IF EXISTS %(to_fk)s' % args, |
|
43 ask_confirm=False) |
|
44 elif repo.system_source.dbdriver.startswith('sqlserver'): |
|
45 sql("IF OBJECT_ID('%(from_fk)s', 'F') IS NOT NULL " |
|
46 "ALTER TABLE %(table)s DROP CONSTRAINT %(from_fk)s" % args, |
|
47 ask_confirm=False) |
|
48 sql("IF OBJECT_ID('%(to_fk)s', 'F') IS NOT NULL " |
|
49 "ALTER TABLE %(table)s DROP CONSTRAINT %(to_fk)s" % args, |
|
50 ask_confirm=False) |
|
51 sql('ALTER TABLE %(table)s ADD CONSTRAINT %(from_fk)s ' |
41 'FOREIGN KEY (eid_from) REFERENCES entities (eid)' % args, |
52 'FOREIGN KEY (eid_from) REFERENCES entities (eid)' % args, |
42 ask_confirm=False) |
53 ask_confirm=False) |
43 sql('ALTER TABLE %(r)s_relation ADD CONSTRAINT %(r)s_relation_eid_to_fkey ' |
54 sql('ALTER TABLE %(table)s ADD CONSTRAINT %(to_fk)s ' |
44 'FOREIGN KEY (eid_to) REFERENCES entities (eid)' % args, |
55 'FOREIGN KEY (eid_to) REFERENCES entities (eid)' % args, |
45 ask_confirm=False) |
56 ask_confirm=False) |
46 |
57 |
47 |
58 |
48 def add_foreign_keys_inlined(rschema): |
59 def add_foreign_keys_inlined(rschema): |
71 ' SELECT eid FROM entities) AS eids' % args, |
82 ' SELECT eid FROM entities) AS eids' % args, |
72 ask_confirm=False)[0][0]: |
83 ask_confirm=False)[0][0]: |
73 print '%(e)s.%(r)s references unknown entities, deleting relation' % args |
84 print '%(e)s.%(r)s references unknown entities, deleting relation' % args |
74 sql('UPDATE cw_%(e)s SET cw_%(r)s = NULL WHERE cw_%(r)s IS NOT NULL AND cw_%(r)s IN ' |
85 sql('UPDATE cw_%(e)s SET cw_%(r)s = NULL WHERE cw_%(r)s IS NOT NULL AND cw_%(r)s IN ' |
75 '(SELECT cw_%(r)s FROM cw_%(e)s EXCEPT SELECT eid FROM entities)' % args) |
86 '(SELECT cw_%(r)s FROM cw_%(e)s EXCEPT SELECT eid FROM entities)' % args) |
76 sql('ALTER TABLE cw_%(e)s DROP CONSTRAINT IF EXISTS %(c)s' % args, |
87 |
77 ask_confirm=False) |
88 if repo.system_source.dbdriver == 'postgres': |
|
89 sql('ALTER TABLE cw_%(e)s DROP CONSTRAINT IF EXISTS %(c)s' % args, |
|
90 ask_confirm=False) |
|
91 elif repo.system_source.dbdriver.startswith('sqlserver'): |
|
92 sql("IF OBJECT_ID('%(c)s', 'F') IS NOT NULL " |
|
93 "ALTER TABLE cw_%(e)s DROP CONSTRAINT %(c)s" % args, |
|
94 ask_confirm=False) |
78 sql('ALTER TABLE cw_%(e)s ADD CONSTRAINT %(c)s ' |
95 sql('ALTER TABLE cw_%(e)s ADD CONSTRAINT %(c)s ' |
79 'FOREIGN KEY (cw_%(r)s) references entities(eid)' % args, |
96 'FOREIGN KEY (cw_%(r)s) references entities(eid)' % args, |
80 ask_confirm=False) |
97 ask_confirm=False) |
81 |
98 |
82 |
99 |
88 ' SELECT eid FROM entities) AS eids' % args, |
105 ' SELECT eid FROM entities) AS eids' % args, |
89 ask_confirm=False)[0][0]: |
106 ask_confirm=False)[0][0]: |
90 print '%(e)s has nonexistent entities, deleting' % args |
107 print '%(e)s has nonexistent entities, deleting' % args |
91 sql('DELETE FROM cw_%(e)s WHERE cw_eid IN ' |
108 sql('DELETE FROM cw_%(e)s WHERE cw_eid IN ' |
92 '(SELECT cw_eid FROM cw_%(e)s EXCEPT SELECT eid FROM entities)' % args) |
109 '(SELECT cw_eid FROM cw_%(e)s EXCEPT SELECT eid FROM entities)' % args) |
93 sql('ALTER TABLE cw_%(e)s DROP CONSTRAINT IF EXISTS cw_%(e)s_cw_eid_fkey' % args, |
110 args['c'] = 'cw_%(e)s_cw_eid_fkey' % args |
94 ask_confirm=False) |
111 if repo.system_source.dbdriver == 'postgres': |
95 sql('ALTER TABLE cw_%(e)s ADD CONSTRAINT cw_%(e)s_cw_eid_fkey ' |
112 sql('ALTER TABLE cw_%(e)s DROP CONSTRAINT IF EXISTS %(c)s' % args, |
|
113 ask_confirm=False) |
|
114 elif repo.system_source.dbdriver.startswith('sqlserver'): |
|
115 sql("IF OBJECT_ID('%(c)s', 'F') IS NOT NULL " |
|
116 "ALTER TABLE cw_%(e)s DROP CONSTRAINT %(c)s" % args, |
|
117 ask_confirm=False) |
|
118 sql('ALTER TABLE cw_%(e)s ADD CONSTRAINT %(c)s ' |
96 'FOREIGN KEY (cw_eid) REFERENCES entities (eid)' % args, |
119 'FOREIGN KEY (cw_eid) REFERENCES entities (eid)' % args, |
97 ask_confirm=False) |
120 ask_confirm=False) |
98 |
121 |
99 |
122 |
100 add_foreign_keys() |
123 add_foreign_keys() |
116 ) |
139 ) |
117 ''') |
140 ''') |
118 |
141 |
119 moved_entities = sql('SELECT -eid, extid FROM entities WHERE eid < 0', |
142 moved_entities = sql('SELECT -eid, extid FROM entities WHERE eid < 0', |
120 ask_confirm=False) |
143 ask_confirm=False) |
121 cu.executemany('INSERT INTO moved_entities (eid, extid) VALUES (%s, %s)', |
144 if moved_entities: |
122 moved_entities) |
145 cu.executemany('INSERT INTO moved_entities (eid, extid) VALUES (%s, %s)', |
123 sql('DELETE FROM entities WHERE eid < 0') |
146 moved_entities) |
|
147 sql('DELETE FROM entities WHERE eid < 0') |
124 |
148 |
125 commit() |
149 commit() |
126 |
150 |
127 sync_schema_props_perms('CWEType') |
151 sync_schema_props_perms('CWEType') |
128 |
152 |
136 cstr = rdef.constraint_by_eid(cwconstraint.eid) |
160 cstr = rdef.constraint_by_eid(cwconstraint.eid) |
137 if cstr.type() not in ('BoundaryConstraint', 'IntervalBoundConstraint', 'StaticVocabularyConstraint'): |
161 if cstr.type() not in ('BoundaryConstraint', 'IntervalBoundConstraint', 'StaticVocabularyConstraint'): |
138 continue |
162 continue |
139 cstrname, check = check_constraint(rdef.subject, rdef.object, rdef.rtype.type, |
163 cstrname, check = check_constraint(rdef.subject, rdef.object, rdef.rtype.type, |
140 cstr, helper, prefix='cw_') |
164 cstr, helper, prefix='cw_') |
141 sql('ALTER TABLE %s%s DROP CONSTRAINT IF EXISTS %s' % ('cw_', rdef.subject.type, cstrname)) |
165 args = {'e': rdef.subject.type, 'c': cstrname, 'v': check} |
142 sql('ALTER TABLE %s%s ADD CONSTRAINT %s CHECK(%s)' % ('cw_', rdef.subject.type, cstrname, check)) |
166 if repo.system_source.dbdriver == 'postgres': |
|
167 sql('ALTER TABLE cw_%(e)s DROP CONSTRAINT IF EXISTS %(c)s' % args) |
|
168 elif repo.system_source.dbdriver.startswith('sqlserver'): |
|
169 sql("IF OBJECT_ID('%(c)s', 'C') IS NOT NULL " |
|
170 "ALTER TABLE cw_%(e)s DROP CONSTRAINT %(c)s" % args) |
|
171 sql('ALTER TABLE cw_%(e)s ADD CONSTRAINT %(c)s CHECK(%(v)s)' % args) |
143 commit() |
172 commit() |