From 2528bcaf1f76e4a6c40c6aa4d6c72a16d37f9177 Mon Sep 17 00:00:00 2001 From: lonkaars Date: Tue, 7 Mar 2023 19:50:24 +0100 Subject: WIP import script --- dbinit/base.sql | 39 +++++++++++++++++++-------------------- 1 file changed, 19 insertions(+), 20 deletions(-) (limited to 'dbinit/base.sql') 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, -- cgit v1.2.3