diff options
| author | lonkaars <loek@pipeframe.xyz> | 2023-04-11 14:38:00 +0200 |
|---|---|---|
| committer | lonkaars <loek@pipeframe.xyz> | 2023-04-11 14:38:00 +0200 |
| commit | 31745c0620869795b76514e48ccdb9b75ee0ba77 (patch) | |
| tree | 6159513bc52b9357d8979277d583a130844cf670 | |
| parent | 81b3c4897c48a443b8488724a2a0b23239ce2381 (diff) | |
| -rwxr-xr-x | dbinit/import.py | 6 | ||||
| -rw-r--r-- | dbinit/makefile | 2 | ||||
| -rw-r--r-- | dbinit/views.sql | 18 |
3 files changed, 25 insertions, 1 deletions
diff --git a/dbinit/import.py b/dbinit/import.py index c6d0195..d2c7373 100755 --- a/dbinit/import.py +++ b/dbinit/import.py @@ -302,6 +302,12 @@ def crawl(year): 0, len(race.laps) )) + if race.first_practice != None and race.first_practice.timestamp() >= datetime.now().timestamp(): return + if race.second_practice != None and race.second_practice.timestamp() >= datetime.now().timestamp(): return + if race.third_practice != None and race.third_practice.timestamp() >= datetime.now().timestamp(): return + if race.sprint != None and race.sprint.timestamp() >= datetime.now().timestamp(): return + if race.qualifying != None and race.qualifying.timestamp() >= datetime.now().timestamp(): return + if race.date != None and race.date.timestamp() >= datetime.now().timestamp(): return race = e.season(race.season).round(race.round_no).get_result() set_race.append(race) for result in race.results: diff --git a/dbinit/makefile b/dbinit/makefile index 1f07ca9..7f60a31 100644 --- a/dbinit/makefile +++ b/dbinit/makefile @@ -22,7 +22,7 @@ base.sql: reset.sql init.sql cat $^ > $@ data.sql: - ./import.py 2017 2020 > $@ + ./import.py 2021 2024 2 > $@ full.sql: base.sql data.sql views.sql functions.sql procedures.sql triggers.sql update.sql cat $^ > $@ diff --git a/dbinit/views.sql b/dbinit/views.sql index 5cdee0f..1e062a0 100644 --- a/dbinit/views.sql +++ b/dbinit/views.sql @@ -25,3 +25,21 @@ from join `teams` on `calendar`.`ID` = `teams`.`calendarID` join `teamsmember` on `teams`.`ID` = `teamsmember`.`teamsID` join `member` on `teamsmember`.`memberID` = `member`.`ID`; + +create view `formula1`.`vwFastestLapRaceID` as +select raceresult.raceID, min(raceresult.fastestlap) as fastestlap + from raceresult join + endposition on endposition.ID = raceresult.endPositionID + group by raceresult.raceID; + +create view `formula1`.`vwFastestlapMemberID` as +select + vwFastestLapRaceID.raceID, + vwFastestLapRaceID.fastestlap, + endposition.memberID +from vwFastestLapRaceID +join raceresult on raceresult.raceID = vwFastestLapRaceID.raceID and raceresult.fastestlap = vwFastestLapRaceID.fastestlap +join endposition on endposition.ID = raceresult.endPositionID +group by raceresult.raceID; + + |