From a48660843cc0191b62865b54f29bd9b230589a5e Mon Sep 17 00:00:00 2001
From: lonkaars <loek@pipeframe.xyz>
Date: Fri, 24 Mar 2023 13:48:52 +0100
Subject: implement `spUpdateFlags`

---
 dbinit/demo.sql       |  1 +
 dbinit/makefile       |  3 ++-
 dbinit/procedures.sql | 12 ++++++++++++
 readme.md             |  8 ++++++++
 4 files changed, 23 insertions(+), 1 deletion(-)

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 ;
diff --git a/readme.md b/readme.md
index 1a20fdd..f5658a2 100644
--- a/readme.md
+++ b/readme.md
@@ -6,3 +6,11 @@
 - [Driver images](https://brightspace.avans.nl/d2l/common/dialogs/quickLink/quickLink.d2l?ou=94181&type=coursefile&fileId=Coureurs.zip)
 - [Country images](https://brightspace.avans.nl/d2l/common/dialogs/quickLink/quickLink.d2l?ou=94181&type=coursefile&fileId=Landen.zip)
 - [Circuit images](https://brightspace.avans.nl/d2l/common/dialogs/quickLink/quickLink.d2l?ou=94181&type=coursefile&fileId=Circuits.zip)
+
+## klaagmuur
+
+- de zip bestanden van brightspace moeten met het volgende commando uitgepakt
+  worden op \*nix systemen om de accenten op de landnamen te behouden:
+  ```bash
+  unzip -O IBM437 <zip>
+  ```
-- 
cgit v1.2.3