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