From 45c8c0fdb135c8eaff3d9bbb7d23426fcd57eebc Mon Sep 17 00:00:00 2001
From: lonkaars <loek@pipeframe.xyz>
Date: Fri, 24 Mar 2023 14:27:55 +0100
Subject: all procedures implemented

---
 dbinit/demo.sql       |  4 ++--
 dbinit/procedures.sql | 16 +++++++++++++++-
 2 files changed, 17 insertions(+), 3 deletions(-)

(limited to 'dbinit')

diff --git a/dbinit/demo.sql b/dbinit/demo.sql
index 56abc36..70d0116 100644
--- a/dbinit/demo.sql
+++ b/dbinit/demo.sql
@@ -33,8 +33,8 @@ insert into `formula1`.`specialposition` (`type`) values
 
 insert into `formula1`.`member` (`firstName`, `middleName`, `lastName`, `functionID`) values
 	("Mario", "", "Mario", 1),
-	("Luigi", "", "Mario", 1),
-	("Peach", "", "Toadstool", 1),
+	("Carlos", "Sainz", "jr.", 1),
+	("Max", "", "Verstappen", 1),
 	("Loek", "Le", "Blansch", 2);
 
 insert into `formula1`.`racedate` (`calendarID`, `raceTypeID`, `week`, `date`) values
diff --git a/dbinit/procedures.sql b/dbinit/procedures.sql
index 73627c2..d94ea65 100644
--- a/dbinit/procedures.sql
+++ b/dbinit/procedures.sql
@@ -18,6 +18,20 @@ create procedure spDeleteFlags()
 	begin
 		update `nationality`
 		set `nationality`.`flag` = NULL
-		where `nationality`.`flag` is not NULL
+		where `nationality`.`flag` is not NULL;
+	end$$
+delimiter ;
+
+drop procedure if exists spUpdatePersons;
+
+delimiter $$
+create procedure spUpdatePersons(imgPath varchar(255))
+	begin
+		select concat(imgPath, regexp_replace(concat(`firstName`, " ", `middleName`, " ", `lastName`), '  *', ' '), ".jpg") from `member`;
+		update `member` as `A`
+		set `A`.`photo` = (
+			select load_file(concat(imgPath, regexp_replace(concat(`firstName`, " ", `middleName`, " ", `lastName`), '  *', ' '), ".jpg")) as `photo`
+			from `member` as `B`
+			where `B`.`ID` = `A`.`ID`);
 	end$$
 delimiter ;
-- 
cgit v1.2.3