1UP_SQL = """ 2ALTER TABLE hosts 3ADD CONSTRAINT hosts_locked_by_fk FOREIGN KEY 4(locked_by_id) REFERENCES users(id) 5ON DELETE NO ACTION; 6 7ALTER TABLE host_queue_entries 8ADD CONSTRAINT host_queue_entries_job_id_fk FOREIGN KEY 9(job_id) REFERENCES jobs(id) 10ON DELETE NO ACTION; 11 12INSERT INTO hosts (hostname, invalid, protection, dirty) 13VALUES ('__missing_host__', 1, 0, 1); 14 15UPDATE host_queue_entries AS hqe 16 LEFT OUTER JOIN hosts ON (hqe.host_id = hosts.id) 17SET hqe.host_id = (SELECT id FROM hosts WHERE hostname = '__missing_host__') 18WHERE hqe.host_id IS NOT NULL AND hosts.id IS NULL; 19 20ALTER TABLE host_queue_entries 21ADD CONSTRAINT host_queue_entries_host_id_fk FOREIGN KEY 22(host_id) REFERENCES hosts(id) 23ON DELETE NO ACTION; 24 25ALTER TABLE host_queue_entries 26ADD CONSTRAINT host_queue_entries_meta_host_fk FOREIGN KEY 27(meta_host) REFERENCES labels(id) 28ON DELETE NO ACTION; 29 30ALTER TABLE aborted_host_queue_entries 31ADD CONSTRAINT aborted_host_queue_entries_queue_entry_id_fk FOREIGN KEY 32(queue_entry_id) REFERENCES host_queue_entries(id) 33ON DELETE NO ACTION; 34 35ALTER TABLE aborted_host_queue_entries 36ADD CONSTRAINT aborted_host_queue_entries_aborted_by_id_fk FOREIGN KEY 37(aborted_by_id) REFERENCES users(id) 38ON DELETE NO ACTION; 39 40ALTER TABLE recurring_run 41ADD CONSTRAINT recurring_run_job_id_fk FOREIGN KEY 42(job_id) REFERENCES jobs(id) 43ON DELETE NO ACTION; 44 45ALTER TABLE recurring_run 46ADD CONSTRAINT recurring_run_owner_id_fk FOREIGN KEY 47(owner_id) REFERENCES users(id) 48ON DELETE NO ACTION; 49""" 50 51DOWN_SQL = """ 52ALTER TABLE hosts 53DROP FOREIGN KEY hosts_locked_by_fk; 54 55ALTER TABLE host_queue_entries 56DROP FOREIGN KEY host_queue_entries_job_id_fk; 57 58ALTER TABLE host_queue_entries 59DROP FOREIGN KEY host_queue_entries_host_id_fk; 60 61ALTER TABLE host_queue_entries 62DROP FOREIGN KEY host_queue_entries_meta_host_fk; 63 64ALTER TABLE aborted_host_queue_entries 65DROP FOREIGN KEY aborted_host_queue_entries_queue_entry_id_fk; 66 67ALTER TABLE aborted_host_queue_entries 68DROP FOREIGN KEY aborted_host_queue_entries_aborted_by_id_fk; 69 70ALTER TABLE recurring_run 71DROP FOREIGN KEY recurring_run_job_id_fk; 72 73ALTER TABLE recurring_run 74DROP FOREIGN KEY recurring_run_owner_id_fk; 75""" 76