diff options
| -rw-r--r-- | dbinit/functions.sql | 7 | ||||
| -rw-r--r-- | dbinit/makefile | 2 | ||||
| -rw-r--r-- | dbinit/triggers.sql | 58 | ||||
| -rw-r--r-- | dbinit/views.sql | 10 |
4 files changed, 39 insertions, 38 deletions
diff --git a/dbinit/functions.sql b/dbinit/functions.sql index 4343210..34a64bc 100644 --- a/dbinit/functions.sql +++ b/dbinit/functions.sql @@ -1,3 +1,4 @@ +use `formula1`; drop function if exists udfTotalDistance; delimiter $$ @@ -6,10 +7,8 @@ returns int begin return ( select `circuit`.`length` * `circuit`.`laps` - from `circuit` - inner join `racedatecircuit` on `racedatecircuit`.`circuitID` = `circuit`.`ID` - inner join `racedate` on `racedate`.`ID` = `racedatecircuit`.`raceDateID` - inner join `race` on `race`.`raceDateID` = `racedate`.`ID` + from `race` + inner join `circuit` on `circuit`.`ID` = `race`.`circuitID` where `race`.`ID` = raceID); end$$ diff --git a/dbinit/makefile b/dbinit/makefile index d20ebd2..6964dd0 100644 --- a/dbinit/makefile +++ b/dbinit/makefile @@ -22,7 +22,7 @@ base.sql: reset.sql init.sql cat $^ > $@ data.sql: - ./import.py > $@ + ./import.py 2018 > $@ full.sql: base.sql data.sql views.sql functions.sql procedures.sql triggers.sql update.sql cat $^ > $@ diff --git a/dbinit/triggers.sql b/dbinit/triggers.sql index 4acc242..2a22e4f 100644 --- a/dbinit/triggers.sql +++ b/dbinit/triggers.sql @@ -1,9 +1,9 @@ drop trigger if exists endposition_ai; drop trigger if exists endposition_ad; drop trigger if exists endposition_au; -drop trigger if exists fastestlap_ai; -drop trigger if exists fastestlap_ad; -drop trigger if exists fastestlap_au; +-- drop trigger if exists fastestlap_ai; +-- drop trigger if exists fastestlap_ad; +-- drop trigger if exists fastestlap_au; delimiter $$ create trigger endposition_ai after insert on endposition @@ -51,29 +51,29 @@ end; $$ delimiter ; -delimiter $$ -create trigger fastestlap_ai after insert on fastestlap -for each row begin - insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`) - select 'insert', 'fastestlap.memberID', cast(new.`memberID` as char) - from `fastestlap` as d where d.`ID` = new.`ID`; -end; $$ -delimiter ; - -delimiter $$ -create trigger fastestlap_ad before delete on fastestlap -for each row begin - insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`) - select 'delete', 'fastestlap.memberID', NULL - from `fastestlap` as d where d.`ID` = old.`ID`; -end; $$ -delimiter ; - -delimiter $$ -create trigger fastestlap_au after update on fastestlap -for each row begin - insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`) - select 'update', 'fastestlap.memberID', cast(new.`memberID` as char) - from `fastestlap` as d where d.`ID` = new.`ID` and old.`memberID` != new.`memberID`; -end; $$ -delimiter ; +-- delimiter $$ +-- create trigger fastestlap_ai after insert on fastestlap +-- for each row begin +-- insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`) +-- select 'insert', 'fastestlap.memberID', cast(new.`memberID` as char) +-- from `fastestlap` as d where d.`ID` = new.`ID`; +-- end; $$ +-- delimiter ; +-- +-- delimiter $$ +-- create trigger fastestlap_ad before delete on fastestlap +-- for each row begin +-- insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`) +-- select 'delete', 'fastestlap.memberID', NULL +-- from `fastestlap` as d where d.`ID` = old.`ID`; +-- end; $$ +-- delimiter ; +-- +-- delimiter $$ +-- create trigger fastestlap_au after update on fastestlap +-- for each row begin +-- insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`) +-- select 'update', 'fastestlap.memberID', cast(new.`memberID` as char) +-- from `fastestlap` as d where d.`ID` = new.`ID` and old.`memberID` != new.`memberID`; +-- end; $$ +-- delimiter ; diff --git a/dbinit/views.sql b/dbinit/views.sql index 80918a1..5cdee0f 100644 --- a/dbinit/views.sql +++ b/dbinit/views.sql @@ -1,3 +1,5 @@ +use `formula1`; + create view `formula1`.`vwcalendar` as select `calendar`.`year` as `year`, @@ -5,10 +7,10 @@ select `racedate`.`date` as `date`, `circuit`.`name` as `name` from - `calendar` - join `racedate` on `calendar`.`ID` = `racedate`.`calendarID` - join `racedatecircuit` on `racedate`.`ID` = `racedatecircuit`.`raceDateID` - join `circuit` on `racedatecircuit`.`circuitID` = `circuit`.`ID`; + `racedate` + join `calendar` on `calendar`.`ID` = `racedate`.`calendarID` + join `race` on `race`.`raceDateID` = `racedate`.`ID` + join `circuit` on `race`.`circuitID` = `circuit`.`ID`; create view `formula1`.`vwteamcoureurs` as select |