# HG changeset patch
# User Sylvain Thénault <sylvain.thenault@logilab.fr>
# Date 1481701031 -3600
# Node ID 74e0d1a5560ba8dd28d05dfb1ad376582854442d
# Parent  ec29989fba1352dc2e08e116d3698bbbf5c2304f
Repair database wrt indexes / unique constraints

* recreate indexes dropped by 3.23 migration (but it's still unclear why)
* attempt drop remaining extra indexes (there may be a bunch of these on old instances)
* warn about missing expected indexes

Closes #16666137

diff -r ec29989fba13 -r 74e0d1a5560b cubicweb/misc/migration/3.24.4_Any.py
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/cubicweb/misc/migration/3.24.4_Any.py	Wed Dec 14 08:37:11 2016 +0100
@@ -0,0 +1,38 @@
+
+from yams.constraints import UniqueConstraint
+from cubicweb.schema import PURE_VIRTUAL_RTYPES
+from cubicweb.server.checkintegrity import expected_indexes, database_indexes
+
+source = repo.system_source
+
+for rschema in schema.relations():
+    if rschema.rule or rschema in PURE_VIRTUAL_RTYPES:
+        continue
+    if rschema.final or rschema.inlined:
+        for rdef in rschema.rdefs.values():
+            table = 'cw_{0}'.format(rdef.subject)
+            column = 'cw_{0}'.format(rdef.rtype)
+            if any(isinstance(cstr, UniqueConstraint) for cstr in rdef.constraints):
+                source.create_index(cnx, table, column, unique=True)
+                commit()
+            if rschema.inlined or rdef.indexed:
+                source.create_index(cnx, table, column)
+                commit()
+
+schema_indices = expected_indexes(cnx)
+db_indices = database_indexes(cnx)
+for additional_index in (db_indices - set(schema_indices)):
+    try:
+        sql('DROP INDEX %s' % additional_index)
+        commit()
+    except:
+        # ignore if this is not an index but a constraint
+        pass
+
+if source.dbhelper == 'postgres' and 'appears_words_idx' not in db_indices:
+    sql('CREATE INDEX appears_words_idx ON appears USING gin(words)')
+    db_indices.add('appears_words_idx')
+
+for missing_index in (set(schema_indices) - db_indices):
+    print('WARNING: missing index', missing_index)
+