AGH-db-lectures - Lecture materials for databases course at AGH

#!/bin/sh

# Exit on error and when referencing an undefined variable.
set -eu

if ! command -v guix >/dev/null; then
    cat >&2 <<EOF
You need to have the GNU Guix package manager installed to use this script.
EOF
    if command -v apt-get >/dev/null; then
        cat >&2 <<EOF
You should be able to install GNU Guix on this machine using APT with, e.g., \
the command

    sudo sh -c 'apt-get update && apt-get install -y guix'

EOF
    elif uname | grep --ignore-case --quiet linux; then
        cat >&2 <<EOF
Consider checking if GNU Guix is available in the package repositories of your \
operating system distribution.  If it is not, you might want to instead follow \
the generic installation instructions at [1].

[1] https://guix.gnu.org/manual/en/html_node/Installation.html
EOF
    else
        cat >&2 <<EOF
Please be aware that, except for some experimental setups, GNU Guix requires \
an operating system based on the Linux kernel to run.
EOF
    fi

    exit 1
fi

GUEST_SCRIPT="$(awk '{if(on)print}/^#{80}/{on=1}' <"$(command -v "$0")")"

cd "$(dirname "$(command -v "$0")")"

TRY_TO_RUN_BROWSER=yes

if [ $# -ge 1 ] && [ "$1" = "--no-browser" ]; then
    unset TRY_TO_RUN_BROWSER
fi

rm -rf notebook-file-open-fifo
mkfifo notebook-file-open-fifo

print_open_request() {
    printf 'Request from the container to open: %s\n' "$1" >&2
}

if ! command -v xdg-open >/dev/null; then
    cat >&2 <<EOF
The xdg-open program was not found.  Web browser and requested files shall not \
be opened automatically.  You can instead manually act on the URL and \
filenames that will be printed.
EOF
    alias xdg-open=print_open_request
fi

open_files_for_guest() {
    IFS= read TO_OPEN < notebook-file-open-fifo
    if [ -n "$TRY_TO_RUN_BROWSER" ]; then
        xdg-open "$(printf %s "$TO_OPEN" |
                    base64 -d |
                    tr --delete '\n' |
                    sed 's_.*/[.]\(local/.*\)[.]html_\1_;
                         s_.*_.container-data/\0.html_')" &
    fi

    while true; do
	if ! IFS= read TO_OPEN < notebook-file-open-fifo; then
	    continue
	fi
        TO_OPEN="$(printf %s "$TO_OPEN" | base64 -d)"
        if (printf %s "$TO_OPEN" |
            tr --delete '\n' |
            grep --quiet -E '^[^-].*[.](png|jpg)$'); then
            xdg-open "$TO_OPEN" &
        else
            printf "Illegal filename.  Not opening file %s\n" "$TO_OPEN" >&2
        fi
    done
}

open_files_for_guest &
trap "kill $! 2>/dev/null || true; wait $!" EXIT

for DIR in .container-data .container-data/var .container-data/local; do
    test -e "$DIR" || mkdir "$DIR"
done

rm -rf .container-data/.my.cnf
touch .container-data/.my.cnf

GUIX_PACKAGES="\
coreutils
findutils
grep
gzip
jupyter
kawa # for qexo, the XQuery runtime
lighttpd # we run Wordpress under lighttpd
mariadb
nss-certs # for TLS
openssl # needed to have Guix set the SSL_CERT_FILE variable
php
postgresql
python
python-bcrypt
python-django
python-ipython-sql
python-mysqlclient
python-psycopg2
python-valkey
sed
tar
unzip
util-linux # for hexdump
valkey
wget
-fpackages/ferretdb.scm
-fpackages/pymongo.scm"
GUIX_PACKAGES="$(printf %s "$GUIX_PACKAGES" | grep -oE '^[^#]+')"

# Some distros still ship a Guix configured to use the Savannah git
# URL.  Cloning from that one is terriby slow nowadays and the project
# now officially hosts code on Codeberg.
CODEBERG_URL=https://codeberg.org/guix/guix.git
# Some ordinary commit from 2025-12-07.
COMMIT_KNOWN_GOOD=3c81c4b8b8a4f6d28faf540d7fa5c36772be15fc

GUIX="guix time-machine --url=$CODEBERG_URL --commit=$COMMIT_KNOWN_GOOD --"

$GUIX shell -C --network --emulate-fhs \
     --share=.container-data/var=/var \
     --share=.container-data/local="$HOME"/.local \
     --share=.="$(pwd)" \
     $GUIX_PACKAGES \
     -- sh -c "$GUEST_SCRIPT"

exit $?

################################################################################
# Container guest part of the script.

# Exit on error and when referencing an undefined variable.
set -eu

# These programs look up their files relative to argv[0].
INITDB=$(realpath $(command -v initdb))
POSTGRES=$(realpath $(command -v postgres))

mkdir -p /var/lib/postgresql
if ! [ -e /var/lib/postgresql/data ]; then
    $INITDB --pgdata /var/lib/postgresql/data-tmp
    mv /var/lib/postgresql/data-tmp /var/lib/postgresql/data
fi

mkdir -p /var/run/postgresql
mkdir -p /var/log

# What if there are some trolls in our LAN?  We'd better only listen
# on localhost.
$POSTGRES -D /var/lib/postgresql/data -p 25432 -h 127.0.0.1 \
          >/var/log/postgresql.log 2>&1 &
printf %s $! >/var/run/postgresql.pid

PSQL='psql --port 25432 --quiet'

# Wait for Postgres to start accepting connections.
for _ in $(seq 20); do
    if $PSQL --command='' postgres 2>/dev/null; then
        break
    fi
    sleep 1
done

# Let these fail if database or user already exists.
for COMMAND in \
    "CREATE USER demo_user PASSWORD 'demo_pwd'" \
    'CREATE DATABASE agh_it_northwind WITH OWNER demo_user' \
    'CREATE DATABASE ferret'; do
    $PSQL --command="$COMMAND" postgres 2>/dev/null || true
done

cat > ~/.my.cnf <<EOF
[mysqld]
socket=/var/run/mysql/mysql.sock
port=23306
bind-address=127.0.0.1

[client]
socket=/var/run/mysql/mysql.sock
port=23306
EOF

mkdir -p /var/run/mysql
mkdir -p /var/lib/mysql/data

if [ 2 = $(stat --format=%h /var/lib/mysql/data) ]; then
    mariadb-install-db --datadir=/var/lib/mysql/data/
fi

mariadbd-safe --datadir=/var/lib/mysql/data/ &

# Wait for MariaDB to start accepting connections.
for _ in $(seq 20); do
    if printf "CREATE DATABASE IF NOT EXISTS agh_it_northwind" | \
            mariadb 2>/dev/null; then
        break
    fi
    sleep 1
done

printf "SET PASSWORD = PASSWORD('demo_pwd')" | mariadb 2>/dev/null;

ferretdb --postgresql-url postgres://127.0.0.1:25432/ferret \
	 >/var/log/ferretdb.log 2>&1 &
printf %s $! >/var/run/ferretdb.pid

cat > /tmp/valkey.conf <<EOF
bind 127.0.0.1
port 20637
tcp-backlog 511
timeout 0
tcp-keepalive 300
daemonize yes
logfile "/var/log/valkey.log"
EOF

valkey-server /tmp/valkey.conf

mkdir -p /var/www

ALLOWED_SRC="$(printf %s "\
blob:
http://localhost:127.0.0.1:28080
http://127.0.0.1:28080
'unsafe-eval'
'unsafe-inline'
" | tr '\n' ' ')"

cat > /tmp/lighttpd.conf <<EOF
server.document-root = "/var/www"
server.modules = ( "mod_indexfile", "mod_cgi", "mod_accesslog", "mod_setenv" )
server.port = 28080
server.bind = "127.0.0.1"
server.errorlog = "/var/log/lighttpd.log"

index-file.names = ( "index.php", "index.html" )

cgi.assign = ( ".php" => "$(command -v php-cgi)" )

accesslog.filename = "/var/log/lighttpd.log"

setenv.add-response-header = (
    "Content-Security-Policy" => "connect-src 'self'; default-src $ALLOWED_SRC;"
)

EOF

# lighttpd shall daemonize itself by default.
lighttpd -f /tmp/lighttpd.conf

ln -sf "$(realpath notebook-file-open-fifo)" /tmp/notebook-file-open-fifo
mkdir -p /tmp/bin

# This is our dummy variant of xdg-open ;)
cat >/tmp/bin/xdg-open <<EOF
#!/bin/sh

printf '%s\n' "\$(realpath "\${1##file://}")" |
    (base64 --wrap=0 && echo) >/tmp/notebook-file-open-fifo
EOF

chmod +x /tmp/bin/xdg-open

printf 'ca_certificate = %s\n' "$SSL_CERT_FILE" >~/.wgetrc

ln -s "$(realpath "$(pwd)")" /tmp/project-root

export PATH=/tmp/bin:"$PATH"
export BROWSER=/tmp/bin/xdg-open
export PYTHONPATH=/tmp/project-root"${PYTHONPATH+:PYTHONPATH}"
exec jupyter-notebook