implement `spUpdateFlags`

3 files changed, 15 insertions, 1 deletions

diff --git a/dbinit/demo.sql b/dbinit/demo.sql
index 2672489..56abc36 100644
--- a/dbinit/demo.sql
+++ b/dbinit/demo.sql
@@ -20,6 +20,7 @@ insert into `formula1`.`nationality` (`country`) values
 	("United states of America"),
 	("Japan"),
 	("North Korea"),
+	("Australië"),
 	("Gelderland zuid");
 
 insert into `formula1`.`racetype` (`raceType`) values
diff --git a/dbinit/makefile b/dbinit/makefile
index 9e5d5d4..76f867f 100644
--- a/dbinit/makefile
+++ b/dbinit/makefile
@@ -33,7 +33,8 @@ data.sql:
 
 # grant all database permissions (development only)
 permissions:
-	echo "grant all privileges on formula1.* to '$(USER)'@'$(HOST)';"
+	echo "grant all privileges on formula1.* to '$(USER)'@'$(HOST)';" | sudo $(SQL)
+	echo "grant file on *.* to '$(USER)'@'$(HOST)';" | sudo $(SQL)
 
 # delete generated sql files
 clean:
diff --git a/dbinit/procedures.sql b/dbinit/procedures.sql
index e69de29..b6b0042 100644
--- a/dbinit/procedures.sql
+++ b/dbinit/procedures.sql
@@ -0,0 +1,12 @@
+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 ;