author | Sylvain Thénault <sylvain.thenault@logilab.fr> |
Fri, 21 Apr 2017 10:29:07 +0200 | |
changeset 12177 | 8cc3af4d9c3a |
parent 11938 | fc19dda111dc |
permissions | -rw-r--r-- |
11895
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
1 |
|
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
2 |
from yams.constraints import UniqueConstraint |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
3 |
from cubicweb.schema import PURE_VIRTUAL_RTYPES |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
4 |
from cubicweb.server.checkintegrity import expected_indexes, database_indexes |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
5 |
|
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
6 |
source = repo.system_source |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
7 |
|
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
8 |
for rschema in schema.relations(): |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
9 |
if rschema.rule or rschema in PURE_VIRTUAL_RTYPES: |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
10 |
continue |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
11 |
if rschema.final or rschema.inlined: |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
12 |
for rdef in rschema.rdefs.values(): |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
13 |
table = 'cw_{0}'.format(rdef.subject) |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
14 |
column = 'cw_{0}'.format(rdef.rtype) |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
15 |
if any(isinstance(cstr, UniqueConstraint) for cstr in rdef.constraints): |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
16 |
source.create_index(cnx, table, column, unique=True) |
11938
fc19dda111dc
[migration] Stop asking confirm to commit
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
11895
diff
changeset
|
17 |
commit(ask_confirm=False) |
11895
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
18 |
if rschema.inlined or rdef.indexed: |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
19 |
source.create_index(cnx, table, column) |
11938
fc19dda111dc
[migration] Stop asking confirm to commit
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
11895
diff
changeset
|
20 |
commit(ask_confirm=False) |
11895
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
21 |
|
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
22 |
schema_indices = expected_indexes(cnx) |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
23 |
db_indices = database_indexes(cnx) |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
24 |
for additional_index in (db_indices - set(schema_indices)): |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
25 |
try: |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
26 |
sql('DROP INDEX %s' % additional_index) |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
27 |
commit() |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
28 |
except: |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
29 |
# ignore if this is not an index but a constraint |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
30 |
pass |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
31 |
|
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
32 |
if source.dbhelper == 'postgres' and 'appears_words_idx' not in db_indices: |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
33 |
sql('CREATE INDEX appears_words_idx ON appears USING gin(words)') |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
34 |
db_indices.add('appears_words_idx') |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
35 |
|
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
36 |
for missing_index in (set(schema_indices) - db_indices): |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
37 |
print('WARNING: missing index', missing_index) |
74e0d1a5560b
Repair database wrt indexes / unique constraints
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
diff
changeset
|
38 |