Designing of a GUI to store information on a database

Posted on by

We know how to make a web that records info into a database, but how can I do it with a GUI? And more importantly, how can I make a cheap and dirty GUI?

Step one: Prepare the database for access from outside the database host. I assume you have root access to a MariaDB on a host. First we need to find and change the bind-address on mysql configuration. In Ubuntu 22.04 the configuration is on /etc/mysql/mariadb.conf.d/50-server.cnf. We nee to leave it like this:

# bind-address = 127.0.0.1 # original
bind-address = 0.0.0.0 # give access to all machines

Once changed, we restart the maria database: systemctl restart mariadb.service.

Step two: Create the database and the table. I do it with phpmyadmin. My database will be called resultsdatabase, and the table resultstable. I assume you know what to store there, and I’m going to skip the table design here. Any other method to create are fine, of course 🙂

Step three: Create an user and password to access the database. As root, we do this:

~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor. 
Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.6.16-MariaDB-0ubuntu0.22.04.1 
Ubuntu 22.04

Copyright (c) 2000, 2018, Oracle, 
MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. 
Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE USER 'errorreporter'@'%' 
IDENTIFIED BY 'ERRORREPORTER2024';
Query OK, 0 rows affected (0.118 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON 
resultsdatabase.* TO 'errorreporter'@'%';
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.010 sec)

MariaDB [(none)]> SELECT User, Host FROM mysql.user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| %             | %         |
| errorreporter | %         |
| mariadb.sys   | localhost |
| mysql         | localhost |
| phpmyadmin    | localhost |
| root          | localhost |
+---------------+-----------+
6 rows in set (0.003 sec)

MariaDB [(none)]> exit
Bye

Step four: Write a script to access the database. We do that with zenity – post about coming soon. In brief:

#!/bin/bash

# Database credentials
DB_USER="errorreporter"
DB_PASSWORD="ERRORREPORTER2024"
DB_NAME="resultsdatabase"
TABLE_NAME="resultstable"
HOSTNAME="remotedatabase.domain.org"

# Use Zenity to get data to insert
data=$(zenity --width=300 --height=300 --forms \
--title="Database Input" \
--text="Enter information about run report" \
--width=500 --add-entry="Program" \
--add-entry="Command" \
--add-entry="User" \
--add-entry="Result")

# Check if user pressed cancel
if [ "$?" != "0" ]; then
  zenity --error --text="No input provided. Exiting."
  exit 1
fi

# Split the form result into variables
IFS='|' read -r s_program s_command 
  s_user s_result <<< "$data"

#Automatically fill up the date
s_date=$(date +%Y-%m-%d) 
s_hostname=`hostname`

# MySQL command
mysql -h $HOSTNAME -u $DB_USER -p$DB_PASSWORD -D $DB_NAME \
-e "INSERT INTO $TABLE_NAME (date, program, hostname, \
command, user, result) VALUES ('$s_date', '$s_hostname', \
'$s_program', '$s_command', '$s_user', '$s_result');"

# Check if command was successful
if [ "$?" = "0" ]; then
  zenity --info --text="Data inserted successfully."
else
  zenity --error --text="An error occurred. 
    Data not inserted."
fi

Maybe zenity is missing. Any other error may be because of the dirtiness of my code. Please add the sanity checks as comments if any 😉

Leave a comment