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