# HG changeset patch
# User Sylvain Thénault <sylvain.thenault@logilab.fr>
# Date 1467829731 -7200
# Node ID a703f00718c21de899428855d1e2c9f2043628fb
# Parent  3432f0e2540d0ed540a33c42598587258ff7fdf7
[migration] More resilient migration script

Query the information schema to retrieve existing constraint (primary key and
unique) and drop them.

Thus don't use anymore DROP CONSTRAINT IF EXISTS (not implemented by e.g. old
versions of sqlserver). To do so a missing DISTINCT was necessary to avoid
duplicates.

diff -r 3432f0e2540d -r a703f00718c2 cubicweb/misc/migration/3.23.0_Any.py
--- a/cubicweb/misc/migration/3.23.0_Any.py	Wed Jul 06 20:14:55 2016 +0200
+++ b/cubicweb/misc/migration/3.23.0_Any.py	Wed Jul 06 20:28:51 2016 +0200
@@ -11,6 +11,24 @@
 source = repo.system_source
 helper = source.dbhelper
 
+# drop all relations primary keys
+for table, cstr in sql('''
+    SELECT DISTINCT tc.table_name, tc.constraint_name
+    FROM information_schema.table_constraints tc,
+         information_schema.key_column_usage kc
+    WHERE tc.constraint_type IN 'PRIMARY KEY'
+          AND kc.table_name = tc.table_name
+          AND kc.table_name LIKE '%\_relation'
+          AND kc.table_schema = tc.table_schema
+          AND kc.constraint_name = tc.constraint_name;
+'''):
+    sql('ALTER TABLE %s DROP CONSTRAINT' % (table, cstr))
+
+for table, cstr in sql("""
+    SELECT DISTINCT table_name, constraint_name FROM information_schema.constraint_column_usage
+    WHERE table_name LIKE 'cw\_%' AND constraint_name LIKE '%\_key'"""):
+    sql("ALTER TABLE %(table)s DROP CONSTRAINT %(cstr)s" % locals())
+
 for rschema in schema.relations():
     if rschema.rule or rschema in PURE_VIRTUAL_RTYPES:
         continue
@@ -20,7 +38,7 @@
             column = 'cw_{0}'.format(rdef.rtype)
             if any(isinstance(cstr, UniqueConstraint) for cstr in rdef.constraints):
                 old_name = '%s_%s_key' % (table.lower(), column.lower())
-                sql('ALTER TABLE %s DROP CONSTRAINT IF EXISTS %s' % (table, old_name))
+                sql('ALTER TABLE %s DROP CONSTRAINT %s' % (table, old_name))
                 source.create_index(cnx, table, column, unique=True)
             if rschema.inlined or rdef.indexed:
                 old_name = '%s_%s_idx' % (table.lower(), column.lower())
@@ -28,7 +46,6 @@
                 source.create_index(cnx, table, column)
     else:
         table = '{0}_relation'.format(rschema)
-        sql('ALTER TABLE %s DROP CONSTRAINT IF EXISTS %s_p_key' % (table, table))
         sql('ALTER TABLE %s ADD CONSTRAINT %s PRIMARY KEY(eid_from, eid_to)'
             % (table, build_index_name(table, ['eid_from', 'eid_to'], 'key_')))
         for column in ('from', 'to'):
@@ -36,13 +53,12 @@
             sql('CREATE INDEX %s ON %s(eid_%s);'
                 % (build_index_name(table, ['eid_' + column], 'idx_'), table, column))
 
-
 # we changed constraint serialization, which also changes their name
 
 for table, cstr in sql("""
-    SELECT table_name, constraint_name FROM information_schema.constraint_column_usage
+    SELECT DISTINCT table_name, constraint_name FROM information_schema.constraint_column_usage
     WHERE constraint_name LIKE 'cstr%'"""):
-    sql("ALTER TABLE %(table)s DROP CONSTRAINT IF EXISTS %(cstr)s" % locals())
+    sql("ALTER TABLE %(table)s DROP CONSTRAINT %(cstr)s" % locals())
 
 for cwconstraint in rql('Any C WHERE R constrained_by C').entities():
     cwrdef = cwconstraint.reverse_constrained_by[0]