update schema

6 files changed, 28 insertions, 155 deletions

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/");