WIP import script

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`;