aboutsummaryrefslogtreecommitdiff
path: root/dbinit
diff options
context:
space:
mode:
Diffstat (limited to 'dbinit')
-rw-r--r--dbinit/.gitignore2
-rw-r--r--dbinit/base.sql223
-rwxr-xr-xdbinit/import.py1
-rw-r--r--dbinit/makefile5
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 > $@