1DOWN_SQL = """
2ALTER TABLE jobs ADD COLUMN synchronizing tinyint(1) default NULL;
3ALTER TABLE autotests ADD COLUMN synch_type smallint(6) NOT NULL;
4UPDATE autotests SET synch_type = 1;
5UPDATE autotests SET synch_type = 2 WHERE sync_count > 1;
6ALTER TABLE jobs ADD COLUMN synch_type int(11) default NULL;
7UPDATE jobs SET synch_type = 1;
8UPDATE jobs SET synch_type = 2 WHERE synch_count > 1;
9ALTER TABLE host_queue_entries DROP COLUMN `execution_subdir`;
10"""
11
12def migrate_up(manager):
13    # add execution_subdir field
14    manager.execute("""ALTER TABLE host_queue_entries ADD COLUMN
15                       `execution_subdir` varchar(255) NOT NULL""")
16
17    # fill in execution_subdir field for running/complete entries
18    rows = manager.execute("""
19        SELECT jobs.id, jobs.synch_type, COUNT(1) FROM jobs
20        INNER JOIN host_queue_entries AS hqe ON jobs.id = hqe.job_id
21        GROUP BY jobs.id""")
22    job_hqe_count = dict((row[0], row[2]) for row in rows)
23    synch_jobs = set(row[0] for row in rows if row[1] == 2)
24    hqes = manager.execute("""
25        SELECT hqe.id, hqe.job_id, hqe.status, hqe.complete, hosts.hostname
26        FROM host_queue_entries AS hqe
27        INNER JOIN hosts ON hqe.host_id = hosts.id
28        WHERE hqe.status IN ('Starting', 'Running') OR complete""")
29    for id, job_id, status, complete, hostname in hqes:
30        if job_id in synch_jobs or job_hqe_count[job_id] == 1:
31            execution_subdir = ''
32        else:
33            execution_subdir = hostname
34        manager.execute(
35            'UPDATE host_queue_entries SET execution_subdir = %s WHERE id = %s',
36            execution_subdir, id)
37
38    # ensure synch_type information doesn't get lost if we need to migrate down
39    manager.execute('UPDATE jobs SET synch_count = 1 WHERE synch_type = 1')
40    manager.execute('UPDATE jobs SET synch_count = 2 '
41                    'WHERE synch_type = 2 AND synch_count = 1')
42    # drop the old synch_type fields
43    manager.execute('ALTER TABLE jobs DROP COLUMN synch_type')
44    manager.execute('ALTER TABLE autotests DROP COLUMN synch_type')
45    # drop deprecated synchronizing field
46    manager.execute('ALTER TABLE jobs DROP COLUMN synchronizing')
47
48
49def migrate_down(manager):
50    manager.execute_script(DOWN_SQL)
51