1UP_SQL = """
2CREATE UNIQUE INDEX host_queue_entries_job_id_and_host_id
3ON host_queue_entries (job_id, host_id);
4
5DROP INDEX host_queue_entries_job_id ON host_queue_entries;
6"""
7
8
9DOWN_SQL = """
10CREATE INDEX host_queue_entries_job_id ON host_queue_entries (job_id);
11
12DROP INDEX host_queue_entries_job_id_and_host_id ON host_queue_entries;
13"""
14
15
16def null_out_duplicate_hqes(manager, hqe_ids):
17    if not hqe_ids:
18        return
19    ids_to_null_string = ','.join(str(hqe_id) for hqe_id in hqe_ids)
20
21    # check if any of the HQEs we're going to null out are active. if so, it's
22    # too dangerous to proceed.
23    rows = manager.execute('SELECT id FROM host_queue_entries '
24                           'WHERE active AND id IN (%s)' % ids_to_null_string)
25    if rows:
26        raise Exception('Active duplicate HQEs exist, cannot proceed.  Please '
27                        'manually abort these HQE IDs: %s' % ids_to_null_string)
28
29    # go ahead and null them out
30    print 'Nulling out duplicate HQE IDs: %s' % ids_to_null_string
31    manager.execute('UPDATE host_queue_entries '
32                    'SET host_id = NULL, active = FALSE, complete = TRUE, '
33                    'aborted = TRUE, status = "Aborted" '
34                    'WHERE id IN (%s)' % ids_to_null_string)
35
36
37def migrate_up(manager):
38    # cleanup duplicate host_queue_entries. rather than deleting them (and
39    # dealing with foreign key references), we'll just null out their host_ids
40    # and set them to aborted.
41    rows = manager.execute('SELECT GROUP_CONCAT(id), COUNT(1) AS count '
42                           'FROM host_queue_entries '
43                           'WHERE host_id IS NOT NULL '
44                           'GROUP BY job_id, host_id HAVING count > 1')
45    # gather all the HQE IDs we want to null out
46    ids_to_null = []
47    for ids_string, _ in rows:
48        id_list = ids_string.split(',')
49        # null out all but the first one.  this isn't terribly important, but
50        # the first one is the most likely to have actually executed, so might
51        # as well keep that one.
52        ids_to_null.extend(id_list[1:])
53
54    null_out_duplicate_hqes(manager, ids_to_null)
55
56    manager.execute_script(UP_SQL)
57