format db init sql file and add scaffold for data import code

4 files changed, 231 insertions, 0 deletions

diff --git a/dbinit/.gitignore b/dbinit/.gitignore
new file mode 100644
index 0000000..8d4caec
--- /dev/null
+++ b/dbinit/.gitignore
@@ -0,0 +1,2 @@
+data.sql
+dbinit.sql
diff --git a/dbinit/base.sql b/dbinit/base.sql
new file mode 100644
index 0000000..79ada29
--- /dev/null
+++ b/dbinit/base.sql
@@ -0,0 +1,223 @@
+drop schema if exists `formula1`;
+create schema if not exists `formula1`;
+use `formula1`;
+
+drop table if exists `formula1`.`calender`;
+create table if not exists `formula1`.`calender` (
+  `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,
+  `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);
+
+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,
+  `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);
+
+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,
+  `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);
+
+drop table if exists `formula1`.`fastestlap`;
+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);
+
+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,
+  `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`.`calender` (`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,
+  `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);
+
+drop table if exists `formula1`.`endpositionrace`;
+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);
+
+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,
+  `flag` mediumblob null default null,
+  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,
+  `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);
+
+drop table if exists `formula1`.`racedatecircuit`;
+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);
+
+drop table if exists `formula1`.`teams`;
+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`.`calender` (`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,
+  `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/import.py b/dbinit/import.py
new file mode 100755
index 0000000..d8dbf66
--- /dev/null
+++ b/dbinit/import.py
@@ -0,0 +1 @@
+#!/bin/python3
diff --git a/dbinit/makefile b/dbinit/makefile
new file mode 100644
index 0000000..d6ab70e
--- /dev/null
+++ b/dbinit/makefile
@@ -0,0 +1,5 @@
+dbinit.sql: base.sql data.sql
+	cat $^ > $@
+
+data.sql:
+	./import.py > $@