all procedures implemented

2 files changed, 17 insertions, 3 deletions

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 ;