fix gitignore, fix typos and add demo.sql for inserting dummy data into database

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);