From c804c2c158ea5b785311d0f453e717b33208b866 Mon Sep 17 00:00:00 2001 From: lonkaars Date: Sun, 9 Apr 2023 13:12:55 +0200 Subject: update schema --- dbinit/demo.sql | 93 ------------------------------------------------------- dbinit/import.py | 23 +++++++------- dbinit/init.sql | 45 +++++---------------------- dbinit/makefile | 16 ++++------ dbinit/reset.sql | 4 +-- dbinit/update.sql | 2 ++ 6 files changed, 28 insertions(+), 155 deletions(-) delete mode 100644 dbinit/demo.sql create mode 100644 dbinit/update.sql (limited to 'dbinit') diff --git a/dbinit/demo.sql b/dbinit/demo.sql deleted file mode 100644 index 70d0116..0000000 --- a/dbinit/demo.sql +++ /dev/null @@ -1,93 +0,0 @@ -insert into `formula1`.`calendar` (`year`) values - ('2010'), - ('2011'), - ('2012'), - ('2013'), - ('2014'); - -insert into `formula1`.`circuit` (`name`, `length`, `laps`) values - ("Dolphin shoals", 2, 3), - ("Maple treeway", 4, 2), - ("Moo moo meadows", 6, 1); - -insert into `formula1`.`function` (`function`) values - ("Driver"), - ("Concierge"); - -insert into `formula1`.`nationality` (`country`) values - ("Netherlands"), - ("Germany"), - ("United states of America"), - ("Japan"), - ("North Korea"), - ("Australië"), - ("Gelderland zuid"); - -insert into `formula1`.`racetype` (`raceType`) values - ("Qualification"), - ("Real deal race"); - -insert into `formula1`.`specialposition` (`type`) values - ("Disqualified"), - ("Did not finish"); - -insert into `formula1`.`member` (`firstName`, `middleName`, `lastName`, `functionID`) values - ("Mario", "", "Mario", 1), - ("Carlos", "Sainz", "jr.", 1), - ("Max", "", "Verstappen", 1), - ("Loek", "Le", "Blansch", 2); - -insert into `formula1`.`racedate` (`calendarID`, `raceTypeID`, `week`, `date`) values - (2, 2, 4, "2011-01-29"), - (1, 2, 12, "2011-04-02"), - (3, 1, 28, "2011-07-25"), - (5, 1, 29, "2011-07-26"); - -insert into `formula1`.`teams` (`calendarID`, `teamNumber`, `teamName`) values - (1, 1, "Team red"), - (2, 2, "Team blue"); - -insert into `formula1`.`endposition` (`memberID`, `position`, `specialPositionID`) values - (1, 3, NULL), - (2, 2, NULL), - (3, 4, NULL), - (4, 1, NULL); - -insert into `formula1`.`fastestlap` (`memberID`) values (1), (2), (3), (4); - -insert into `formula1`.`membernationality` (`memberID`, `nationalityID`) values - (1, 1), - (2, 4), - (3, 5), - (4, 3); - -insert into `formula1`.`racedatecircuit` (`raceDateID`, `circuitID`) values - (1, 3), - (2, 2), - (3, 1), - (4, 1); - -insert into `formula1`.`teamsmember` (`teamsID`, `memberID`) values - (1, 1), - (1, 2), - (2, 3), - (2, 4); - -insert into `formula1`.`race` (`raceDateID`, `raceNumber`, `fastestLapID`) values - (1, 1, 1), - (1, 2, 2), - (2, 1, 3), - (2, 2, 2), - (2, 3, 4), - (3, 1, 1), - (3, 2, 2), - (4, 1, 4), - (4, 2, 2); - -insert into `formula1`.`endpositionrace` (`endPositionID`, `raceID`) values - (1, 2), - (3, 4), - (4, 1), - (3, 2), - (3, 3); - diff --git a/dbinit/import.py b/dbinit/import.py index a9ae898..f6e9c93 100755 --- a/dbinit/import.py +++ b/dbinit/import.py @@ -151,9 +151,6 @@ def export_endposition(): out = "".join(out) return out -def export_fastestlap(): - return "" - def export_racetype(): out = "insert into `formula1`.`racetype` (`raceType`) values " found_ids = set() @@ -183,7 +180,7 @@ def export_racedate(): def export_race(): return "" -def export_endpositionrace(): +def export_raceresult(): return "" def export_nationality(): @@ -262,16 +259,15 @@ def export(): export_member(), export_racedate(), export_teams(), - export_fastestlap(), export_membernationality(), export_endposition(), export_racedatecircuit(), export_teamsmember(), export_race(), - export_endpositionrace() + export_raceresult() ])) -def main(year): +def crawl(year): set_racetype.append("first_practice") set_racetype.append("second_practice") set_racetype.append("third_practice") @@ -287,7 +283,6 @@ def main(year): i = 0 for race in e.season(year).get_races(): set_calendar.append(race.season) - set_race.append(race) set_circuit.append(F1Circuit( race.circuit.circuit_id, race.circuit.circuit_name, @@ -295,6 +290,7 @@ def main(year): len(race.laps) )) race = e.season(race.season).round(race.round_no).get_result() + set_race.append(race) for result in race.results: for status in e.season(race.season).round(race.round_no).get_statuses(): set_specialposition.append(status) @@ -372,11 +368,14 @@ def main(year): i += 1 if i == 3: break - export() - if __name__ == "__main__": - if len(sys.argv) < 2: + if len(sys.argv) < 2: # no year provided eprint("please provide a year to fetch f1 data from") exit(1) - main(sys.argv[1]) + else if len(sys.argv) == 2: # crawl single year + crawl(year) + else: # crawl range + for year in range(*[int(x) for x in sys.argv[1:4]]): + crawl(year) + export() diff --git a/dbinit/init.sql b/dbinit/init.sql index 9985038..b74ff54 100644 --- a/dbinit/init.sql +++ b/dbinit/init.sql @@ -61,17 +61,6 @@ create table if not exists `formula1`.`endposition` ( references `formula1`.`specialposition` (`ID`) on update cascade); -create table if not exists `formula1`.`fastestlap` ( - `ID` int not null auto_increment, - `memberID` int null default null, - primary key (`ID`), - unique index `ID_UNIQUE` (`ID` asc) visible, - index `memberIDfastestlap_idx` (`memberID` asc) visible, - constraint `memberIDfastestlap` - foreign key (`memberID`) - references `formula1`.`member` (`ID`) - on update cascade); - create table if not exists `formula1`.`racetype` ( `ID` int not null auto_increment, `raceType` varchar(45) not null, @@ -99,26 +88,25 @@ create table if not exists `formula1`.`racedate` ( create table if not exists `formula1`.`race` ( `ID` int not null auto_increment, `raceDateID` int null default null, - `raceNumber` int null default null, - `fastestLapID` int null default null, + `circuitID` int null default null, primary key (`ID`), unique index `ID_UNIQUE` (`ID` asc) visible, - index `raceNumber_idx` (`raceNumber` asc) visible, + index `circuitID_idx` (`circuitID` asc) visible, index `raceDateID_idx` (`raceDateID` asc) visible, - index `fastestLapID_idx` (`fastestLapID` asc) visible, - constraint `fastestLapID` - foreign key (`fastestLapID`) - references `formula1`.`fastestlap` (`ID`) + constraint `circuitID` + foreign key (`circuitID`) + references `formula1`.`circuit` (`ID`) on update cascade, - constraint `raceDateIDRace` + constraint `raceDateID` foreign key (`raceDateID`) references `formula1`.`racedate` (`ID`) on update cascade); -create table if not exists `formula1`.`endpositionrace` ( +create table if not exists `formula1`.`raceresult` ( `ID` int not null auto_increment, `endPositionID` int null default null, `raceID` int null default null, + `fastestlap` int null default null, primary key (`ID`), unique index `ID_UNIQUE` (`ID` asc) visible, index `endPositionID_idx` (`endPositionID` asc) visible, @@ -156,23 +144,6 @@ create table if not exists `formula1`.`membernationality` ( references `formula1`.`nationality` (`ID`) on update cascade); -create table if not exists `formula1`.`racedatecircuit` ( - `ID` int not null auto_increment, - `raceDateID` int null default null, - `circuitID` int null default null, - primary key (`ID`), - unique index `ID_UNIQUE` (`ID` asc) visible, - index `circuitID_idx` (`circuitID` asc) visible, - index `raceDateID_idx` (`raceDateID` asc) visible, - constraint `circuitID` - foreign key (`circuitID`) - references `formula1`.`circuit` (`ID`) - on update cascade, - constraint `raceDateID` - foreign key (`raceDateID`) - references `formula1`.`racedate` (`ID`) - on update cascade); - create table if not exists `formula1`.`teams` ( `ID` int not null auto_increment, `calendarID` int null default null, diff --git a/dbinit/makefile b/dbinit/makefile index f2dcd75..d20ebd2 100644 --- a/dbinit/makefile +++ b/dbinit/makefile @@ -2,35 +2,31 @@ SQL = mysql USER = $(shell id -un) HOST = localhost -.PHONY: clean permissions init data procedures full +.PHONY: clean permissions base data full all: full # delete old tables and create new tables -init: base.sql +base: base.sql $(SQL) < $< # fill tables with data data: data.sql $(SQL) < $< -# add procedures/sql functions -procedures: procedures.sql - $(SQL) < $< - -# do all of the above in one go +# do everything full: full.sql $(SQL) < $< base.sql: reset.sql init.sql cat $^ > $@ -full.sql: reset.sql init.sql demo.sql views.sql functions.sql procedures.sql triggers.sql - cat $^ > $@ - data.sql: ./import.py > $@ +full.sql: base.sql data.sql views.sql functions.sql procedures.sql triggers.sql update.sql + cat $^ > $@ + # grant all database permissions (development only) permissions: echo "grant all privileges on formula1.* to '$(USER)'@'$(HOST)';" | sudo $(SQL) diff --git a/dbinit/reset.sql b/dbinit/reset.sql index b11d723..b62b2ea 100644 --- a/dbinit/reset.sql +++ b/dbinit/reset.sql @@ -5,13 +5,11 @@ drop table if exists `formula1`.`function`; drop table if exists `formula1`.`member`; drop table if exists `formula1`.`specialposition`; drop table if exists `formula1`.`endposition`; -drop table if exists `formula1`.`fastestlap`; drop table if exists `formula1`.`racetype`; drop table if exists `formula1`.`racedate`; drop table if exists `formula1`.`race`; -drop table if exists `formula1`.`endpositionrace`; +drop table if exists `formula1`.`raceresult`; drop table if exists `formula1`.`nationality`; drop table if exists `formula1`.`membernationality`; -drop table if exists `formula1`.`racedatecircuit`; drop table if exists `formula1`.`teams`; drop table if exists `formula1`.`teamsmember`; diff --git a/dbinit/update.sql b/dbinit/update.sql new file mode 100644 index 0000000..e42f40e --- /dev/null +++ b/dbinit/update.sql @@ -0,0 +1,2 @@ +call spUpdateFlags("/var/dab2/Landen/"); +call spUpdatePersons("/var/dab2/Coureurs/"); -- cgit v1.2.3