diff options
| -rw-r--r-- | dbinit/.gitignore | 3 | ||||
| -rw-r--r-- | dbinit/base.sql | 222 | ||||
| -rw-r--r-- | dbinit/demo.sql | 92 | ||||
| -rw-r--r-- | dbinit/init.sql | 6 |
4 files changed, 97 insertions, 226 deletions
diff --git a/dbinit/.gitignore b/dbinit/.gitignore index 8d4caec..e95719f 100644 --- a/dbinit/.gitignore +++ b/dbinit/.gitignore @@ -1,2 +1,3 @@ +full.sql +base.sql data.sql -dbinit.sql diff --git a/dbinit/base.sql b/dbinit/base.sql deleted file mode 100644 index 491c6de..0000000 --- a/dbinit/base.sql +++ /dev/null @@ -1,222 +0,0 @@ -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`; - -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); - -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); - -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); - -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); - -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); - -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); - -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, - primary key (`ID`), - unique index `ID_UNIQUE` (`ID` asc) visible); - -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`.`calendar` (`ID`) - on update cascade, - constraint `raceTypeID` - foreign key (`raceTypeID`) - references `formula1`.`racetype` (`ID`) - on update cascade); - -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); - -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); - -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); - -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); - -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, - `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`.`calendar` (`ID`) - on update cascade); - -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/demo.sql b/dbinit/demo.sql new file mode 100644 index 0000000..7404e97 --- /dev/null +++ b/dbinit/demo.sql @@ -0,0 +1,92 @@ +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"), + ("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`, `membercol`) values + ("Mario", "", "Mario", 1, "TODO"), + ("Luigi", "", "Mario", 1, "TODO"), + ("Peach", "", "Toadstool", 1, "TODO"), + ("Loek", "Le", "Blansch", 2, "TODO"); + +insert into `formula1`.`racedate` (`calanderID`, `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` (`calanderID`, `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`, `teamsMembercol`) values + (1, 1, "TODO"), + (1, 2, "TODO"), + (2, 3, "TODO"), + (2, 4, "TODO"); + +insert into `formula1`.`race` (`raceDateID`, `raceNumber`, `fastestLapID`) values + (1, 1, 1), + (1, 2, 2), + (2, 3, 3), + (2, 4, 2), + (2, 5, 4), + (3, 6, 1), + (3, 7, 2), + (4, 8, 4), + (4, 9, 2); + +insert into `formula1`.`endpositionrace` (`endPositionID`, `raceID`) values + (1, 2), + (3, 4), + (4, 1), + (3, 2), + (3, 3); + diff --git a/dbinit/init.sql b/dbinit/init.sql index 9638fa4..221cac8 100644 --- a/dbinit/init.sql +++ b/dbinit/init.sql @@ -48,17 +48,17 @@ 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, + `specialPositionID` 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, + index `specialPositionID_idx` (`specialPositionID` asc) visible, constraint `memberIDEndPosition` foreign key (`memberID`) references `formula1`.`member` (`ID`) on update cascade, constraint `specialPositionID` - foreign key (`specialPoistionID`) + foreign key (`specialPositionID`) references `formula1`.`specialposition` (`ID`) on update cascade); |