MxMJ2xpd

1-- Start block to undo previous attempts (used for testing query
2DROP TABLE IF EXISTS `lim_sub_staging`.`submission_2_coeus`, `lim_sub_staging`.`new_submission`, `lim_sub_staging`.`new_person`;
3-- end undo block
4
5CREATE TEMPORARY TABLE IF NOT EXISTS temp_submission (id INT auto_increment, PRIMARY KEY(id)) SELECT * FROM submission;
6SELECT * FROM temp_submission;
7
8CREATE TABLE new_person (id INT auto_increment , username VARCHAR(20) UNIQUE, PRIMARY KEY (id)) SELECT * FROM person;
9SELECT * FROM new_person;
10
11-- Create the new Submission Table
12CREATE TABLE new_submission LIKE temp_submission;
13ALTER TABLE new_submission DROP person_id;
14
15INSERT INTO new_submission(id, deadline_id, status_id,  coeus_award, coeus_proposal, submission_date)
16SELECT id, deadline_id, status_id,  coeus_award, coeus_proposal, submission_date
17FROM temp_submission;
18
19ALTER TABLE new_submission
20ADD FOREIGN KEY (deadline_id) REFERENCES deadline(id);
21SELECT * FROM new_submission;
22
23-- Create the new junction table
24CREATE TABLE submission_2_coeus (
25	submission_id INT,
26    person_id INT,
27    is_lead BOOL DEFAULT FALSE NOT NULL,
28    PRIMARY KEY(submission_id, person_id),
29    FOREIGN KEY (submission_id) REFERENCES new_submission(id),
30    FOREIGN KEY (person_id) REFERENCES new_person(id)
31);
32INSERT INTO submission_2_coeus(person_id, submission_id, is_lead)
33	SELECT new_person.id, temp_submission.id, TRUE 
34	FROM new_person, temp_submission
35	WHERE new_person.mnumber = temp_submission.person_id;
36    
37
38SELECT 
39	new_person.mnumber, 
40    new_person.firstname, 
41    new_person.lastname, 
42    opportunity.spin_id, 
43    opportunity.sponsor_name, 
44    opportunity.title, 
45    deadline.internal_deadline
46FROM opportunity, deadline ,new_submission, new_person, submission_2_coeus
47WHERE 
48	new_person.id = submission_2_coeus.person_id 
49    AND submission_2_coeus.submission_id = new_submission.id 
50    AND deadline.id = new_submission.deadline_id 
51    AND opportunity.id = deadline.opp_id;
52
53-- Rename Tables
54RENAME TABLE
55	person TO deprecated_person,
56    submission TO deprecated_submission,
57    new_person TO person,
58    new_submission TO submission;
59
60DROP TABLE temp_submission;