From 7567984ca753c5a01fd8c5310e5aac5ef39a6fbc Mon Sep 17 00:00:00 2001
From: lonkaars <loek@pipeframe.xyz>
Date: Fri, 24 Mar 2023 11:28:35 +0100
Subject: implement udfTotalDistance

---
 dbinit/functions.sql | 16 ++++++++++++++++
 dbinit/makefile      |  4 +++-
 2 files changed, 19 insertions(+), 1 deletion(-)
 create mode 100644 dbinit/functions.sql

(limited to 'dbinit')

diff --git a/dbinit/functions.sql b/dbinit/functions.sql
new file mode 100644
index 0000000..4343210
--- /dev/null
+++ b/dbinit/functions.sql
@@ -0,0 +1,16 @@
+drop function if exists udfTotalDistance;
+
+delimiter $$
+create function udfTotalDistance(raceID int)
+returns int
+begin
+	return (
+		select `circuit`.`length` * `circuit`.`laps`
+		from `circuit`
+		inner join `racedatecircuit` on `racedatecircuit`.`circuitID` = `circuit`.`ID`
+		inner join `racedate` on `racedate`.`ID` = `racedatecircuit`.`raceDateID`
+		inner join `race` on `race`.`raceDateID` = `racedate`.`ID`
+		where `race`.`ID` = raceID);
+end$$
+
+delimiter ;
diff --git a/dbinit/makefile b/dbinit/makefile
index d412570..9e5d5d4 100644
--- a/dbinit/makefile
+++ b/dbinit/makefile
@@ -4,6 +4,8 @@ HOST = localhost
 
 .PHONY: clean permissions init data procedures full
 
+all: full
+
 # delete old tables and create new tables
 init: base.sql
 	$(SQL) < $<
@@ -23,7 +25,7 @@ full: full.sql
 base.sql: reset.sql init.sql
 	cat $^ > $@
 
-full.sql: reset.sql init.sql demo.sql views.sql procedures.sql
+full.sql: reset.sql init.sql demo.sql views.sql functions.sql procedures.sql
 	cat $^ > $@
 
 data.sql:
-- 
cgit v1.2.3