aboutsummaryrefslogtreecommitdiff
path: root/dbinit/procedures.sql
blob: 776a7cac5dd122d156b2e89167c7c368c5de34ea (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
drop procedure if exists spUpdateFlags;

delimiter $$
create procedure spUpdateFlags(imgPath varchar(255))
	begin
		update `nationality` as `A`
		set `A`.`flag` = (
			select load_file(concat(imgPath, `country`, ".png")) as `flag`
			from `nationality` as `B`
			where `B`.`ID` = `A`.`ID`);
	end$$
delimiter ;

drop procedure if exists spDeleteFlags;

delimiter $$
create procedure spDeleteFlags()
	begin
		update `nationality`
		set `nationality`.`flag` = 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 ;