aboutsummaryrefslogtreecommitdiff
path: root/dbinit
diff options
context:
space:
mode:
authorlonkaars <loek@pipeframe.xyz>2023-03-24 14:27:55 +0100
committerlonkaars <loek@pipeframe.xyz>2023-03-24 14:27:55 +0100
commit45c8c0fdb135c8eaff3d9bbb7d23426fcd57eebc (patch)
treede936bb69c9a0ad936b58e7d220bd82bc69aea80 /dbinit
parent3199b9ebfa707068fd2c901276e663b660d1a050 (diff)
all procedures implemented
Diffstat (limited to 'dbinit')
-rw-r--r--dbinit/demo.sql4
-rw-r--r--dbinit/procedures.sql16
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 ;