From 9539e58ae7653ffa5ef25cf7b18c67d705813334 Mon Sep 17 00:00:00 2001
From: Cullum Smith <cullum@sacredheartsc.com>
Date: Thu, 27 Mar 2025 22:30:15 -0400
Subject: more mysql stuff

---
 .../local/etc/mysql/conf.d/server.cnf.mysql_server |  1 +
 .../local/etc/poudriere.d/make.conf.pkg_repository |  1 +
 lib/60-mysql                                       | 25 ++++++++++++++++++++++
 lib/60-postgres                                    |  1 +
 scripts/common/10-vars                             |  1 +
 scripts/hostclass/desktop                          |  8 +++++++
 scripts/hostclass/mysql_server                     |  2 ++
 site                                               |  2 +-
 8 files changed, 40 insertions(+), 1 deletion(-)
 create mode 100644 lib/60-mysql

diff --git a/files/usr/local/etc/mysql/conf.d/server.cnf.mysql_server b/files/usr/local/etc/mysql/conf.d/server.cnf.mysql_server
index 93a6975..2f514ae 100644
--- a/files/usr/local/etc/mysql/conf.d/server.cnf.mysql_server
+++ b/files/usr/local/etc/mysql/conf.d/server.cnf.mysql_server
@@ -19,3 +19,4 @@ ssl_ca                    = ${site_cacert_path}
 ssl_cert                  = ${mysql_tls_cert}
 ssl_key                   = ${mysql_tls_key}
 require_secure_transport
+max_allowed_packet        = ${mysql_max_packet_size}
diff --git a/files/usr/local/etc/poudriere.d/make.conf.pkg_repository b/files/usr/local/etc/poudriere.d/make.conf.pkg_repository
index 5d16ed4..928e276 100644
--- a/files/usr/local/etc/poudriere.d/make.conf.pkg_repository
+++ b/files/usr/local/etc/poudriere.d/make.conf.pkg_repository
@@ -32,6 +32,7 @@ editors_vim_SET=CTAGS_EXUBERANT XTERM_SAVE
 editors_vim_UNSET=CTAGS_BASE
 emulators_wine_SET=CUPS
 finance_gnucash_UNSET=AQBANKING
+graphics_digikam_SET=MYSQL
 graphics_vips_UNSET=MATIO
 irc_znc_SET=CYRUS
 lang_lua53_SET=LIBEDIT_DL
diff --git a/lib/60-mysql b/lib/60-mysql
new file mode 100644
index 0000000..1d2f158
--- /dev/null
+++ b/lib/60-mysql
@@ -0,0 +1,25 @@
+#!/bin/sh
+
+mysql_run(){
+  MYSQL_PWD="$boxconf_password" mysql \
+    --batch \
+    --ssl-verify-server-cert \
+    --user="$boxconf_username" \
+    "$@"
+}
+
+mysql_create_user(){
+  # $1 = mysql_host, $2 = username $3 = via
+  cat <<EOF | mysql_run --host="${1}"
+CREATE USER IF NOT EXISTS '${2}' IDENTIFIED VIA ${3};
+EOF
+}
+
+mysql_create_database(){
+  # $1 = mysql_host, $2 = dbname, $3 = owner $4 = options
+  cat <<EOF | mysql_run --host="${1}"
+CREATE DATABASE IF NOT EXISTS ${2} ${4:-};
+GRANT ALL PRIVILEGES ON ${2}.* TO '${3}';
+FLUSH PRIVILEGES;
+EOF
+}
diff --git a/lib/60-postgres b/lib/60-postgres
index 6f418ea..b50d130 100644
--- a/lib/60-postgres
+++ b/lib/60-postgres
@@ -9,6 +9,7 @@ postgres_run(){
     -v ON_ERROR_STOP=1 \
     "$@"
 }
+
 postgres_create_role(){
   # $1 = postgres_host, $2 = username
   cat <<EOF | postgres_run -h "${1}" -d postgres
diff --git a/scripts/common/10-vars b/scripts/common/10-vars
index a82dd44..b903f8b 100644
--- a/scripts/common/10-vars
+++ b/scripts/common/10-vars
@@ -13,6 +13,7 @@ idm_hostnames=$(echo "$idm_server_list" | awk '{print $1}')
 fqdn="${BOXCONF_HOSTNAME}.${domain}"
 : ${smtp_host:="smtp.${domain}"}
 : ${postgres_host:="postgres.${domain}"}
+: ${mysql_host:="mysql.${domain}"}
 
 realm=$(echo "$domain" | tr '[:lower:]' '[:upper:]')
 
diff --git a/scripts/hostclass/desktop b/scripts/hostclass/desktop
index c85ef28..0195b3d 100644
--- a/scripts/hostclass/desktop
+++ b/scripts/hostclass/desktop
@@ -6,6 +6,8 @@
 : ${cups_host:='cups'}
 : ${ublock_whitelist:=''}
 : ${chrome_flags:=''}
+: ${digikam_db_users:=''}
+: ${digikam_db_host:="$mysql_host"}
 
 sddm_user=sddm
 cups_conf_dir=/usr/local/etc/cups
@@ -181,3 +183,9 @@ install_template -m 0644 /usr/local/etc/mpv/mpv.conf
 
 # Start login manager.
 service sddm status || service sddm start > /dev/null 2>&1 < /dev/null || die 'failed to start sddm'
+
+# Create users for digikam db.
+for user in $digikam_db_users; do
+  mysql_create_user "$digikam_db_host" "$user" gssapi
+  mysql_create_database "$digikam_db_host" digikam "$user"
+done
diff --git a/scripts/hostclass/mysql_server b/scripts/hostclass/mysql_server
index 115b591..8fa0ce7 100644
--- a/scripts/hostclass/mysql_server
+++ b/scripts/hostclass/mysql_server
@@ -1,5 +1,7 @@
 #!/bin/sh
 
+: ${mysql_max_packet_size:='67108864'} # 64M
+
 mysql_user=mysql
 mysql_home=/var/db/mysql
 mysql_tls_cert="${mysql_home}/mysql.crt"
diff --git a/site b/site
index 42f2d44..55a4440 160000
--- a/site
+++ b/site
@@ -1 +1 @@
-Subproject commit 42f2d448b6bb8b7f3a4c1be36b16684247739d3d
+Subproject commit 55a4440d5ab567109b00f34e87d36a2fa4c1ea95
-- 
cgit v1.2.3