From 6759c31b658290b8558356cb9d1e7247797b5e31 Mon Sep 17 00:00:00 2001 From: emkael Date: Tue, 17 Feb 2015 16:58:09 +0100 Subject: * integrity checks import --- import/01_integrity_import.sh | 4 +++ import/01a_integrity_fixes.sql | 10 ++++++++ import/01b_indexes.sql | 57 ++++++++++++++++++++++++++++++++++++++++++ import/01c_constraints.sql | 41 ++++++++++++++++++++++++++++++ 4 files changed, 112 insertions(+) create mode 100755 import/01_integrity_import.sh create mode 100644 import/01a_integrity_fixes.sql create mode 100644 import/01b_indexes.sql create mode 100644 import/01c_constraints.sql diff --git a/import/01_integrity_import.sh b/import/01_integrity_import.sh new file mode 100755 index 0000000..7ea5919 --- /dev/null +++ b/import/01_integrity_import.sh @@ -0,0 +1,4 @@ +#!/bin/bash +pushd $(dirname $0) > /dev/null +cat 01?_*.sql | mysql "$@" +popd > /dev/null diff --git a/import/01a_integrity_fixes.sql b/import/01a_integrity_fixes.sql new file mode 100644 index 0000000..b42f117 --- /dev/null +++ b/import/01a_integrity_fixes.sql @@ -0,0 +1,10 @@ +UPDATE constructorResults SET constructorId = 7 WHERE constructorResultsId = 14128; -- Toyota, Spain 2005 +UPDATE constructorResults SET constructorId = 3 WHERE constructorResultsId = 14845; -- Williams, China 2014 + +UPDATE constructorStandings SET constructorId = 7 WHERE constructorStandingsId = 24518; -- Toyota, Spain 2005 + +UPDATE qualifying SET driverId = 42, constructorId = 17 WHERE qualifyId = 4019; -- Baumgartner (Jordan), Hungary 2003 +UPDATE qualifying SET driverId = 42, constructorId = 17 WHERE qualifyId = 4038; -- Baumgartner (Jordan), Italy 2003 +UPDATE qualifying SET driverId = 48, constructorId = 3 WHERE qualifyId = 4025; -- Gené (Williams), Italy 2003 +UPDATE qualifying SET constructorId = 164 WHERE qualifyId IN (3420, 3421); -- HRT, China 2010 +UPDATE qualifying SET constructorId = 3 WHERE qualifyId IN (4600, 4602); -- Williams, China 2011 diff --git a/import/01b_indexes.sql b/import/01b_indexes.sql new file mode 100644 index 0000000..d3ca687 --- /dev/null +++ b/import/01b_indexes.sql @@ -0,0 +1,57 @@ +-- Indexes for table circuits +ALTER TABLE circuits + ADD INDEX country (country); + +-- Indexes for table constructorResults +ALTER TABLE constructorResults + ADD INDEX status (status); + +-- Indexes for table constructors +ALTER TABLE constructors + ADD INDEX nationality (nationality); + +-- Indexes for table drivers +ALTER TABLE drivers + ADD INDEX number (number), + ADD INDEX code (code), + ADD INDEX dob (dob), + ADD INDEX nationality (nationality); + +-- Indexes for table driverStandings +ALTER TABLE driverStandings + ADD INDEX position (position), + ADD INDEX points (points), + ADD INDEX positionText (positionText); + +-- Indexes for table lapTimes +ALTER TABLE lapTimes + ADD INDEX position (position), + ADD INDEX lap (lap); + +-- Indexes for table pitStops +ALTER TABLE pitStops + ADD INDEX stop (stop), + ADD INDEX lap (lap); + +-- Indexes for table qualifying +ALTER TABLE qualifying + ADD INDEX position (position); + +-- Indexes for table races +ALTER TABLE races + ADD INDEX year (year), + ADD INDEX date (date); + +-- Indexes for table results +ALTER TABLE results + ADD INDEX rank (rank), + ADD INDEX points (points), + ADD INDEX positionOrder (positionOrder), + ADD INDEX positionText (positionText), + ADD INDEX position (position), + ADD INDEX grid (grid), + ADD INDEX number (number); + +-- Indexes for table status +ALTER TABLE status + ADD INDEX status (status); diff --git a/import/01c_constraints.sql b/import/01c_constraints.sql new file mode 100644 index 0000000..08cefc7 --- /dev/null +++ b/import/01c_constraints.sql @@ -0,0 +1,41 @@ +-- Constraints for table constructorResults +ALTER TABLE constructorResults + ADD CONSTRAINT constructorResults_ibfk_1 FOREIGN KEY (raceId) REFERENCES races (raceId) ON UPDATE CASCADE, + ADD CONSTRAINT constructorResults_ibfk_2 FOREIGN KEY (constructorId) REFERENCES constructors (constructorId) ON UPDATE CASCADE; + +-- Constraints for table constructorStandings +ALTER TABLE constructorStandings + ADD CONSTRAINT constructorStandings_ibfk_1 FOREIGN KEY (raceId) REFERENCES races (raceId) ON UPDATE CASCADE, + ADD CONSTRAINT constructorStandings_ibfk_2 FOREIGN KEY (constructorId) REFERENCES constructors (constructorId) ON UPDATE CASCADE; + +-- Constraints for table driverStandings +ALTER TABLE driverStandings + ADD CONSTRAINT driverStandings_ibfk_1 FOREIGN KEY (raceId) REFERENCES races (raceId) ON UPDATE CASCADE, + ADD CONSTRAINT driverStandings_ibfk_2 FOREIGN KEY (driverId) REFERENCES drivers (driverId) ON UPDATE CASCADE; + +-- Constraints for table lapTimes +ALTER TABLE lapTimes + ADD CONSTRAINT lapTimes_ibfk_1 FOREIGN KEY (raceId) REFERENCES races (raceId) ON UPDATE CASCADE, + ADD CONSTRAINT lapTimes_ibfk_2 FOREIGN KEY (driverId) REFERENCES drivers (driverId) ON UPDATE CASCADE; + +-- Constraints for table pitStops +ALTER TABLE pitStops + ADD CONSTRAINT pitStops_ibfk_1 FOREIGN KEY (raceId) REFERENCES races (raceId) ON UPDATE CASCADE, + ADD CONSTRAINT pitStops_ibfk_2 FOREIGN KEY (driverId) REFERENCES drivers (driverId) ON UPDATE CASCADE; + +-- Constraints for table qualifying +ALTER TABLE qualifying + ADD CONSTRAINT qualifying_ibfk_1 FOREIGN KEY (raceId) REFERENCES races (raceId) ON UPDATE CASCADE, + ADD CONSTRAINT qualifying_ibfk_2 FOREIGN KEY (driverId) REFERENCES drivers (driverId) ON UPDATE CASCADE, + ADD CONSTRAINT qualifying_ibfk_3 FOREIGN KEY (constructorId) REFERENCES constructors (constructorId) ON UPDATE CASCADE; + +-- Constraints for table races +ALTER TABLE races + ADD CONSTRAINT races_ibfk_1 FOREIGN KEY (circuitId) REFERENCES circuits (circuitId); + +-- Constraints for table results +ALTER TABLE results + ADD CONSTRAINT results_ibfk_1 FOREIGN KEY (raceId) REFERENCES races (raceId) ON UPDATE CASCADE, + ADD CONSTRAINT results_ibfk_2 FOREIGN KEY (driverId) REFERENCES drivers (driverId) ON UPDATE CASCADE, + ADD CONSTRAINT results_ibfk_3 FOREIGN KEY (constructorId) REFERENCES constructors (constructorId) ON UPDATE CASCADE, + ADD CONSTRAINT results_ibfk_4 FOREIGN KEY (statusId) REFERENCES status (statusId) ON UPDATE CASCADE; -- cgit v1.2.3