From ee88077eb90bc7f7f800fa04e78bf055fc552cd9 Mon Sep 17 00:00:00 2001 From: lonkaars Date: Tue, 7 Mar 2023 16:57:14 +0100 Subject: format db init sql file and add scaffold for data import code --- dbinit/.gitignore | 2 + dbinit/base.sql | 223 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ dbinit/import.py | 1 + dbinit/makefile | 5 ++ 4 files changed, 231 insertions(+) create mode 100644 dbinit/.gitignore create mode 100644 dbinit/base.sql create mode 100755 dbinit/import.py create mode 100644 dbinit/makefile (limited to 'dbinit') 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 > $@ -- cgit v1.2.3