XeFQt3sF

1CREATE TABLE IF NOT EXISTS Instrument (
2	ID int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
3    Name varchar(255)
4);
5CREATE TABLE IF NOT EXISTS Location (
6	ID int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
7    Location varchar(255)
8);
9CREATE TABLE IF NOT EXISTS Publisher (
10	ID int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
11    Name varchar(255)
12);
13CREATE TABLE IF NOT EXISTS Record (
14	ID int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
15    Title varchar(255),
16    Comment varchar(255)
17);
18CREATE TABLE IF NOT EXISTS Comp (
19	ID int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
20    Name varchar(255),
21    Born int,
22    Dead int
23);
24CREATE TABLE IF NOT EXISTS RecordPublishedBy (
25	ID int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
26    RecordID int,
27    PublishID int
28);
29CREATE TABLE IF NOT EXISTS CompHasMadeRecord (
30	ID int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
31    CompID int,
32    RecordID int
33);
34CREATE TABLE IF NOT EXISTS RecordHasInstruments (
35	ID int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
36    RecordID int,
37    InstrumentID int
38);
39CREATE TABLE IF NOT EXISTS RecordIsAtLocation (
40	ID int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
41    RecordID int,
42    LocationID int
43);
44
45DELIMITER $$
46DROP PROCEDURE IF EXISTS migrate_data$$
47CREATE PROCEDURE migrate_data()
48BEGIN
49	DECLARE x INT;
50    DECLARE newCompID INT;
51    DECLARE newRecordID INT;
52	DECLARE newPublisherID INT;
53    DECLARE newLocationID INT;
54	DECLARE newInstrumentID INT;
55
56	DECLARE done INT;
57	DECLARE duplicateChecker INT;
58    
59    DECLARE lastName VARCHAR(255);
60	DECLARE currentName VARCHAR(255);
61	DECLARE compDead INT;
62	DECLARE compBorn INT;
63    
64	DECLARE lastTitle VARCHAR(255);
65	DECLARE currentTitle VARCHAR(255);
66	DECLARE titleComment VARCHAR(255);
67    
68    DECLARE lastPub VARCHAR(255);
69	DECLARE currentPub VARCHAR(255);
70    
71    DECLARE lastLocation VARCHAR(255);
72	DECLARE currentLocation VARCHAR(255);
73    
74    DECLARE lastInstrument VARCHAR(255);
75	DECLARE currentInstrument VARCHAR(255);
76    
77    # Init variables so we can do first compare
78	SET lastName = "";
79	SET currentName = "";
80    SET lastTitle = "";
81	SET currentTitle = "";
82    SET lastPub = "";
83	SET currentPub = "";
84	SET lastLocation = "";
85	SET currentLocation = "";
86    SET lastInstrument = "";
87	SET currentInstrument = "";
88
89	# Calculate number of rows to add
90	SELECT ID INTO x FROM Comp ORDER BY ID DESC LIMIT 1;
91    IF x IS NULL THEN
92		SET x = 0;
93	END IF;
94	SELECT COUNT(*) INTO done FROM archive;
95	SET done = done + x;
96    
97    # Do the migration
98	WHILE x < done DO
99		
100        # Get and set Comp
101		SELECT Comp, SUBSTRING(Life, 1, 4) * 1, SUBSTRING(Life, 6, 4) * 1 INTO currentName, compDead, compBorn FROM archive LIMIT x,1;
102        IF currentName <> lastName THEN
103        		INSERT INTO Comp (Name, Born, Dead) VALUES (currentName, compDead, compBorn);
104                SET newCompID = LAST_INSERT_ID();
105                SET lastName = currentName;
106        END IF;
107		
108        # Get and set Record
109        SELECT Title, Comment INTO currentTitle, titleComment FROM archive LIMIT x,1;
110        IF currentTitle <> lastTitle THEN
111			INSERT INTO Record (Title, Comment) VALUES (currentTitle, titleComment);
112			SET newRecordID = LAST_INSERT_ID();
113            SET lastTitle = currentTitle;
114        END IF;
115        
116        # Get and set Publisher
117        SELECT Pub INTO currentPub FROM archive LIMIT x,1;
118        IF currentPub <> lastPub THEN
119			INSERT INTO Publisher (Name) VALUES (currentPub);
120			SET newPublisherID = LAST_INSERT_ID();
121            SET lastPub = currentPub;
122        END IF;
123        
124        # Get and set Location
125        SELECT New_Loc INTO currentLocation FROM archive LIMIT x,1;
126        IF currentLocation <> lastLocation THEN
127			INSERT INTO Location (Location) VALUES (currentLocation);
128			SET newLocationID = LAST_INSERT_ID();
129            SET lastLocation = currentLocation;
130        END IF;
131        
132        # Get and set Instrument
133        SELECT Inst INTO currentInstrument FROM archive LIMIT x,1;
134        IF currentInstrument <> lastInstrument THEN
135			INSERT INTO Instrument (Name) VALUES (currentInstrument);
136			SET newInstrumentID = LAST_INSERT_ID();
137            SET lastInstrument = currentInstrument;
138        END IF;
139
140        # Insert Relations
141        SELECT COUNT(ID) INTO duplicateChecker FROM CompHasMadeRecord WHERE CompID = newCompID AND RecordID = newRecordID;
142        IF duplicateChecker = 0 THEN 
143			INSERT INTO CompHasMadeRecord (CompID, RecordID) VALUES (newCompID, newRecordID);
144        END IF;
145        
146        SELECT COUNT(ID) INTO duplicateChecker FROM RecordPublishedBy WHERE RecordID = newRecordID AND PublishID = newPublisherID;
147        IF duplicateChecker = 0 THEN 
148			INSERT INTO RecordPublishedBy (RecordID, PublishID) VALUES (newRecordID, newPublisherID);
149        END IF;
150        
151        SELECT COUNT(ID) INTO duplicateChecker FROM RecordIsAtLocation WHERE RecordID = newRecordID AND LocationID = newLocationID;
152        IF duplicateChecker = 0 THEN 
153			INSERT INTO RecordIsAtLocation (RecordID, LocationID) VALUES (newRecordID, newLocationID);
154        END IF;
155        
156        SELECT COUNT(ID) INTO duplicateChecker FROM RecordHasInstruments WHERE RecordID = newRecordID AND InstrumentID = newInstrumentID;
157        IF duplicateChecker = 0 THEN 
158			INSERT INTO RecordHasInstruments (RecordID, InstrumentID) VALUES (newRecordID, newInstrumentID);
159        END IF;
160        
161		SET  x = x + 1; 
162	END WHILE;
163END$$
164DELIMITER ;
165
166CALL migrate_data();