9 sql = partial(sql, ask_confirm=False) |
9 sql = partial(sql, ask_confirm=False) |
10 |
10 |
11 source = repo.system_source |
11 source = repo.system_source |
12 helper = source.dbhelper |
12 helper = source.dbhelper |
13 |
13 |
|
14 # drop all relations primary keys |
|
15 for table, cstr in sql(''' |
|
16 SELECT DISTINCT tc.table_name, tc.constraint_name |
|
17 FROM information_schema.table_constraints tc, |
|
18 information_schema.key_column_usage kc |
|
19 WHERE tc.constraint_type IN 'PRIMARY KEY' |
|
20 AND kc.table_name = tc.table_name |
|
21 AND kc.table_name LIKE '%\_relation' |
|
22 AND kc.table_schema = tc.table_schema |
|
23 AND kc.constraint_name = tc.constraint_name; |
|
24 '''): |
|
25 sql('ALTER TABLE %s DROP CONSTRAINT' % (table, cstr)) |
|
26 |
|
27 for table, cstr in sql(""" |
|
28 SELECT DISTINCT table_name, constraint_name FROM information_schema.constraint_column_usage |
|
29 WHERE table_name LIKE 'cw\_%' AND constraint_name LIKE '%\_key'"""): |
|
30 sql("ALTER TABLE %(table)s DROP CONSTRAINT %(cstr)s" % locals()) |
|
31 |
14 for rschema in schema.relations(): |
32 for rschema in schema.relations(): |
15 if rschema.rule or rschema in PURE_VIRTUAL_RTYPES: |
33 if rschema.rule or rschema in PURE_VIRTUAL_RTYPES: |
16 continue |
34 continue |
17 if rschema.final or rschema.inlined: |
35 if rschema.final or rschema.inlined: |
18 for rdef in rschema.rdefs.values(): |
36 for rdef in rschema.rdefs.values(): |
19 table = 'cw_{0}'.format(rdef.subject) |
37 table = 'cw_{0}'.format(rdef.subject) |
20 column = 'cw_{0}'.format(rdef.rtype) |
38 column = 'cw_{0}'.format(rdef.rtype) |
21 if any(isinstance(cstr, UniqueConstraint) for cstr in rdef.constraints): |
39 if any(isinstance(cstr, UniqueConstraint) for cstr in rdef.constraints): |
22 old_name = '%s_%s_key' % (table.lower(), column.lower()) |
40 old_name = '%s_%s_key' % (table.lower(), column.lower()) |
23 sql('ALTER TABLE %s DROP CONSTRAINT IF EXISTS %s' % (table, old_name)) |
41 sql('ALTER TABLE %s DROP CONSTRAINT %s' % (table, old_name)) |
24 source.create_index(cnx, table, column, unique=True) |
42 source.create_index(cnx, table, column, unique=True) |
25 if rschema.inlined or rdef.indexed: |
43 if rschema.inlined or rdef.indexed: |
26 old_name = '%s_%s_idx' % (table.lower(), column.lower()) |
44 old_name = '%s_%s_idx' % (table.lower(), column.lower()) |
27 sql('DROP INDEX IF EXISTS %s' % old_name) |
45 sql('DROP INDEX IF EXISTS %s' % old_name) |
28 source.create_index(cnx, table, column) |
46 source.create_index(cnx, table, column) |
29 else: |
47 else: |
30 table = '{0}_relation'.format(rschema) |
48 table = '{0}_relation'.format(rschema) |
31 sql('ALTER TABLE %s DROP CONSTRAINT IF EXISTS %s_p_key' % (table, table)) |
|
32 sql('ALTER TABLE %s ADD CONSTRAINT %s PRIMARY KEY(eid_from, eid_to)' |
49 sql('ALTER TABLE %s ADD CONSTRAINT %s PRIMARY KEY(eid_from, eid_to)' |
33 % (table, build_index_name(table, ['eid_from', 'eid_to'], 'key_'))) |
50 % (table, build_index_name(table, ['eid_from', 'eid_to'], 'key_'))) |
34 for column in ('from', 'to'): |
51 for column in ('from', 'to'): |
35 sql('DROP INDEX IF EXISTS %s_%s_idx' % (table, column)) |
52 sql('DROP INDEX IF EXISTS %s_%s_idx' % (table, column)) |
36 sql('CREATE INDEX %s ON %s(eid_%s);' |
53 sql('CREATE INDEX %s ON %s(eid_%s);' |
37 % (build_index_name(table, ['eid_' + column], 'idx_'), table, column)) |
54 % (build_index_name(table, ['eid_' + column], 'idx_'), table, column)) |
38 |
55 |
39 |
|
40 # we changed constraint serialization, which also changes their name |
56 # we changed constraint serialization, which also changes their name |
41 |
57 |
42 for table, cstr in sql(""" |
58 for table, cstr in sql(""" |
43 SELECT table_name, constraint_name FROM information_schema.constraint_column_usage |
59 SELECT DISTINCT table_name, constraint_name FROM information_schema.constraint_column_usage |
44 WHERE constraint_name LIKE 'cstr%'"""): |
60 WHERE constraint_name LIKE 'cstr%'"""): |
45 sql("ALTER TABLE %(table)s DROP CONSTRAINT IF EXISTS %(cstr)s" % locals()) |
61 sql("ALTER TABLE %(table)s DROP CONSTRAINT %(cstr)s" % locals()) |
46 |
62 |
47 for cwconstraint in rql('Any C WHERE R constrained_by C').entities(): |
63 for cwconstraint in rql('Any C WHERE R constrained_by C').entities(): |
48 cwrdef = cwconstraint.reverse_constrained_by[0] |
64 cwrdef = cwconstraint.reverse_constrained_by[0] |
49 rdef = cwrdef.yams_schema() |
65 rdef = cwrdef.yams_schema() |
50 cstr = rdef.constraint_by_eid(cwconstraint.eid) |
66 cstr = rdef.constraint_by_eid(cwconstraint.eid) |