diff options
Diffstat (limited to 'dbinit')
| -rw-r--r-- | dbinit/base.sql | 39 | ||||
| -rwxr-xr-x | dbinit/import.py | 23 | ||||
| -rw-r--r-- | dbinit/init.sql | 205 | ||||
| -rw-r--r-- | dbinit/makefile | 36 | ||||
| -rw-r--r-- | dbinit/readme.md | 24 | ||||
| -rw-r--r-- | dbinit/reset.sql | 17 |
6 files changed, 323 insertions, 21 deletions
diff --git a/dbinit/base.sql b/dbinit/base.sql index 79ada29..491c6de 100644 --- a/dbinit/base.sql +++ b/dbinit/base.sql @@ -1,16 +1,29 @@ drop schema if exists `formula1`; +drop table if exists `formula1`.`calendar`; +drop table if exists `formula1`.`circuit`; +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`.`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`; create schema if not exists `formula1`; -use `formula1`; -drop table if exists `formula1`.`calender`; -create table if not exists `formula1`.`calender` ( +create table if not exists `formula1`.`calendar` ( `ID` int not null auto_increment, `year` year not null, primary key (`ID`), unique index `ID_UNIQUE` (`ID` asc) visible, unique index `year_UNIQUE` (`year` asc) visible); -drop table if exists `formula1`.`circuit`; create table if not exists `formula1`.`circuit` ( `ID` int not null auto_increment, `name` varchar(45) null default null, @@ -20,14 +33,12 @@ create table if not exists `formula1`.`circuit` ( primary key (`ID`), unique index `ID_UNIQUE` (`ID` asc) visible); -drop table if exists `formula1`.`function`; create table if not exists `formula1`.`function` ( `ID` int not null auto_increment, `function` varchar(45) null default null, primary key (`ID`), unique index `ID_UNIQUE` (`ID` asc) visible); -drop table if exists `formula1`.`member`; create table if not exists `formula1`.`member` ( `ID` int not null auto_increment, `firstName` varchar(45) null default null, @@ -44,14 +55,12 @@ create table if not exists `formula1`.`member` ( references `formula1`.`function` (`ID`) on update cascade); -drop table if exists `formula1`.`specialposition`; create table if not exists `formula1`.`specialposition` ( `ID` int not null auto_increment, `type` varchar(45) null default null, primary key (`ID`), unique index `ID_UNIQUE` (`ID` asc) visible); -drop table if exists `formula1`.`endposition`; create table if not exists `formula1`.`endposition` ( `ID` int not null auto_increment, `memberID` int null default null, @@ -70,7 +79,6 @@ create table if not exists `formula1`.`endposition` ( references `formula1`.`specialposition` (`ID`) on update cascade); -drop table if exists `formula1`.`fastestlap`; create table if not exists `formula1`.`fastestlap` ( `ID` int not null auto_increment, `memberID` int null default null, @@ -82,14 +90,12 @@ create table if not exists `formula1`.`fastestlap` ( references `formula1`.`member` (`ID`) on update cascade); -drop table if exists `formula1`.`racetype`; create table if not exists `formula1`.`racetype` ( `ID` int not null auto_increment, `raceType` varchar(45) not null, primary key (`ID`), unique index `ID_UNIQUE` (`ID` asc) visible); -drop table if exists `formula1`.`racedate`; create table if not exists `formula1`.`racedate` ( `ID` int not null auto_increment, `calanderID` int null default null, @@ -101,14 +107,13 @@ create table if not exists `formula1`.`racedate` ( index `raceTypeID_idx` (`raceTypeID` asc) visible, constraint `calanderID` foreign key (`calanderID`) - references `formula1`.`calender` (`ID`) + references `formula1`.`calendar` (`ID`) on update cascade, constraint `raceTypeID` foreign key (`raceTypeID`) references `formula1`.`racetype` (`ID`) on update cascade); -drop table if exists `formula1`.`race`; create table if not exists `formula1`.`race` ( `ID` int not null auto_increment, `raceDateID` int null default null, @@ -128,7 +133,6 @@ create table if not exists `formula1`.`race` ( references `formula1`.`racedate` (`ID`) on update cascade); -drop table if exists `formula1`.`endpositionrace`; create table if not exists `formula1`.`endpositionrace` ( `ID` int not null auto_increment, `endPositionID` int null default null, @@ -146,7 +150,6 @@ create table if not exists `formula1`.`endpositionrace` ( references `formula1`.`race` (`ID`) on update cascade); -drop table if exists `formula1`.`nationality`; create table if not exists `formula1`.`nationality` ( `ID` int not null auto_increment, `country` varchar(45) null default null, @@ -154,7 +157,6 @@ create table if not exists `formula1`.`nationality` ( primary key (`ID`), unique index `ID_UNIQUE` (`ID` asc) visible); -drop table if exists `formula1`.`membernationality`; create table if not exists `formula1`.`membernationality` ( `ID` int not null auto_increment, `memberID` int null default null, @@ -172,7 +174,6 @@ create table if not exists `formula1`.`membernationality` ( references `formula1`.`nationality` (`ID`) on update cascade); -drop table if exists `formula1`.`racedatecircuit`; create table if not exists `formula1`.`racedatecircuit` ( `ID` int not null auto_increment, `raceDateID` int null default null, @@ -190,7 +191,6 @@ create table if not exists `formula1`.`racedatecircuit` ( references `formula1`.`racedate` (`ID`) on update cascade); -drop table if exists `formula1`.`teams`; create table if not exists `formula1`.`teams` ( `ID` int not null auto_increment, `calanderID` int null default null, @@ -201,10 +201,9 @@ create table if not exists `formula1`.`teams` ( index `calanderID_idx` (`calanderID` asc) visible, constraint `calanderID2` foreign key (`calanderID`) - references `formula1`.`calender` (`ID`) + references `formula1`.`calendar` (`ID`) on update cascade); -drop table if exists `formula1`.`teamsmember`; create table if not exists `formula1`.`teamsmember` ( `ID` int not null auto_increment, `teamsID` int null default null, diff --git a/dbinit/import.py b/dbinit/import.py index d8dbf66..bf81bfb 100755 --- a/dbinit/import.py +++ b/dbinit/import.py @@ -1 +1,24 @@ #!/bin/python3 + +import os +import sys +import urllib.request + +global YEAR +USE_LOCAL_CACHE = True +API_BASE_URL = "https://ergast.com/" + +def api_request(endpoint): + conn = urllib.request.urlopen(API_BASE_URL + endpoint) + return conn.read() + +def main(): + print(f"fetching year {YEAR}") + +if __name__ == "__main__": + if len(sys.argv) < 2: + print("please provide a year to fetch f1 data from") + exit(1) + YEAR = int(sys.argv[1]) + main() + diff --git a/dbinit/init.sql b/dbinit/init.sql new file mode 100644 index 0000000..9638fa4 --- /dev/null +++ b/dbinit/init.sql @@ -0,0 +1,205 @@ +create schema if not exists `formula1`; + +create table if not exists `formula1`.`calendar` ( + `ID` int not null auto_increment, + `year` year not null, + primary key (`ID`), + unique index `ID_UNIQUE` (`ID` asc) visible, + unique index `year_UNIQUE` (`year` asc) visible); + +create table if not exists `formula1`.`circuit` ( + `ID` int not null auto_increment, + `name` varchar(45) null default null, + `length` int null default null, + `photo` mediumblob null default null, + `laps` int null default null, + primary key (`ID`), + unique index `ID_UNIQUE` (`ID` asc) visible); + +create table if not exists `formula1`.`function` ( + `ID` int not null auto_increment, + `function` varchar(45) null default null, + primary key (`ID`), + unique index `ID_UNIQUE` (`ID` asc) visible); + +create table if not exists `formula1`.`member` ( + `ID` int not null auto_increment, + `firstName` varchar(45) null default null, + `middleName` varchar(31) null default null, + `lastName` varchar(45) null default null, + `photo` mediumblob null default null, + `functionID` int null default null, + `membercol` varchar(45) null default null, + primary key (`ID`), + unique index `ID_UNIQUE` (`ID` asc) visible, + index `functionID_idx` (`functionID` asc) visible, + constraint `functionID` + foreign key (`functionID`) + references `formula1`.`function` (`ID`) + on update cascade); + +create table if not exists `formula1`.`specialposition` ( + `ID` int not null auto_increment, + `type` varchar(45) null default null, + primary key (`ID`), + unique index `ID_UNIQUE` (`ID` asc) visible); + +create table if not exists `formula1`.`endposition` ( + `ID` int not null auto_increment, + `memberID` int null default null, + `position` int null default null, + `specialPoistionID` int null default null, + primary key (`ID`), + unique index `ID_UNIQUE` (`ID` asc) visible, + index `memberIDEndPosition_idx` (`memberID` asc) visible, + index `specialPositionID_idx` (`specialPoistionID` asc) visible, + constraint `memberIDEndPosition` + foreign key (`memberID`) + references `formula1`.`member` (`ID`) + on update cascade, + constraint `specialPositionID` + foreign key (`specialPoistionID`) + 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, + primary key (`ID`), + unique index `ID_UNIQUE` (`ID` asc) visible); + +create table if not exists `formula1`.`racedate` ( + `ID` int not null auto_increment, + `calanderID` int null default null, + `raceTypeID` int null default null, + `week` int not null, + `date` date not null, + primary key (`ID`), + index `calanderID_idx` (`calanderID` asc) visible, + index `raceTypeID_idx` (`raceTypeID` asc) visible, + constraint `calanderID` + foreign key (`calanderID`) + references `formula1`.`calendar` (`ID`) + on update cascade, + constraint `raceTypeID` + foreign key (`raceTypeID`) + references `formula1`.`racetype` (`ID`) + on update cascade); + +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, + primary key (`ID`), + unique index `ID_UNIQUE` (`ID` asc) visible, + unique index `raceNumber_UNIQUE` (`raceNumber` asc) visible, + index `raceDateID_idx` (`raceDateID` asc) visible, + index `fastestLapID_idx` (`fastestLapID` asc) visible, + constraint `fastestLapID` + foreign key (`fastestLapID`) + references `formula1`.`fastestlap` (`ID`) + on update cascade, + constraint `raceDateIDRace` + foreign key (`raceDateID`) + references `formula1`.`racedate` (`ID`) + on update cascade); + +create table if not exists `formula1`.`endpositionrace` ( + `ID` int not null auto_increment, + `endPositionID` int null default null, + `raceID` int null default null, + primary key (`ID`), + unique index `ID_UNIQUE` (`ID` asc) visible, + index `endPositionID_idx` (`endPositionID` asc) visible, + index `raceID_idx` (`raceID` asc) visible, + constraint `endPositionID` + foreign key (`endPositionID`) + references `formula1`.`endposition` (`ID`) + on update cascade, + constraint `raceID` + foreign key (`raceID`) + references `formula1`.`race` (`ID`) + on update cascade); + +create table if not exists `formula1`.`nationality` ( + `ID` int not null auto_increment, + `country` varchar(45) null default null, + `flag` mediumblob null default null, + primary key (`ID`), + unique index `ID_UNIQUE` (`ID` asc) visible); + +create table if not exists `formula1`.`membernationality` ( + `ID` int not null auto_increment, + `memberID` int null default null, + `nationalityID` int null default null, + primary key (`ID`), + unique index `ID_UNIQUE` (`ID` asc) visible, + index `memberID_idx` (`memberID` asc) visible, + index `nationalityID_idx` (`nationalityID` asc) visible, + constraint `memberIDNationality` + foreign key (`memberID`) + references `formula1`.`member` (`ID`) + on update cascade, + constraint `nationalityID` + foreign key (`nationalityID`) + 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, + `calanderID` int null default null, + `teamNumber` int null default null, + `teamName` varchar(45) null default null, + primary key (`ID`), + unique index `ID_UNIQUE` (`ID` asc) visible, + index `calanderID_idx` (`calanderID` asc) visible, + constraint `calanderID2` + foreign key (`calanderID`) + references `formula1`.`calendar` (`ID`) + on update cascade); + +create table if not exists `formula1`.`teamsmember` ( + `ID` int not null auto_increment, + `teamsID` int null default null, + `memberID` int null default null, + `teamsMembercol` varchar(45) null default null, + primary key (`ID`), + unique index `ID_UNIQUE` (`ID` asc) visible, + index `teamsID_idx` (`teamsID` asc) visible, + index `memberIDteams_idx` (`memberID` asc) visible, + constraint `memberIDteams` + foreign key (`memberID`) + references `formula1`.`member` (`ID`), + constraint `teamsID` + foreign key (`teamsID`) + references `formula1`.`teams` (`ID`)); + diff --git a/dbinit/makefile b/dbinit/makefile index d6ab70e..0e335b7 100644 --- a/dbinit/makefile +++ b/dbinit/makefile @@ -1,5 +1,39 @@ -dbinit.sql: base.sql data.sql +SQL = mysql +USER = $(shell id -un) +HOST = localhost + +.PHONY: clean permissions init data procedures full + +# delete old tables and create new tables +init: 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 +full: full.sql + $(SQL) < $< + +base.sql: reset.sql init.sql + cat $^ > $@ + +full.sql: reset.sql init.sql data.sql procedures.sql cat $^ > $@ data.sql: ./import.py > $@ + +# grant all database permissions (development only) +permissions: + echo "grant all privileges on formula1.* to '$(USER)'@'$(HOST)';" | sudo $(SQL) + +# delete generated sql files +clean: + $(RM) full.sql base.sql data.sql + diff --git a/dbinit/readme.md b/dbinit/readme.md new file mode 100644 index 0000000..8179faf --- /dev/null +++ b/dbinit/readme.md @@ -0,0 +1,24 @@ +# init scripts + +(see makefile for target explanations) + +## data storing order + +|round|table| +|-|-| +|1|calendar| +|1|circuit| +|1|function| +|1|nationality| +|1|racetype| +|1|specialposition| +|2|member| +|2|racedate| +|2|teams| +|3|endposition| +|3|fastestlap| +|3|membernationality| +|3|racedatecircuit| +|3|teamsmember| +|4|race| +|5|endpositionrace| diff --git a/dbinit/reset.sql b/dbinit/reset.sql new file mode 100644 index 0000000..b11d723 --- /dev/null +++ b/dbinit/reset.sql @@ -0,0 +1,17 @@ +drop schema if exists `formula1`; +drop table if exists `formula1`.`calendar`; +drop table if exists `formula1`.`circuit`; +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`.`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`; |