6 files changed, 416 insertions, 0 deletions

diff --git a/build/db-init/Dockerfile b/build/db-init/Dockerfile
new file mode 100644
index 0000000..c5a4b59
--- /dev/null
+++ b/build/db-init/Dockerfile
@@ -0,0 +1,41 @@
+### CRIMSON --- A simple PHP framework.
+### Copyright © 2024 Freya Murphy <contact@freyacat.org>
+###
+### This file is part of CRIMSON.
+###
+### CRIMSON is free software; you can redistribute it and/or modify it
+### under the terms of the GNU General Public License as published by
+### the Free Software Foundation; either version 3 of the License, or (at
+### your option) any later version.
+###
+### CRIMSON is distributed in the hope that it will be useful, but
+### WITHOUT ANY WARRANTY; without even the implied warranty of
+### MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+### GNU General Public License for more details.
+###
+### You should have received a copy of the GNU General Public License
+### along with CRIMSON. If not, see <http://www.gnu.org/licenses/>.
+FROM alpine:latest
+
+# install packages
+RUN apk add --no-cache postgresql16-client tini shadow sed
+RUN rm -fr /var/cache/apk/*
+
+# setup main user
+RUN adduser -D db-init
+RUN groupmod --gid 1000 db-init
+RUN usermod --uid 1000 db-init
+
+# copy scripts
+COPY ./db-init /usr/local/bin/db-init
+COPY ./rev.sql /var/lib/rev.sql
+COPY ./ext.sql /var/lib/ext.sql
+COPY ./base.sql /var/lib/base.sql
+
+# remove build packages
+RUN apk del shadow
+
+# do the
+USER db-init
+ENTRYPOINT ["/sbin/tini", "--"]
+CMD ["/usr/local/bin/db-init"]
diff --git a/build/db-init/README.md b/build/db-init/README.md
new file mode 100644
index 0000000..942090a
--- /dev/null
+++ b/build/db-init/README.md
@@ -0,0 +1,52 @@
+## db-init
+
+This script setups of the databse with the requrired baseline, runs migrations,
+and loads the api schema (if enabled).
+
+#### Migration script
+
+All migrations scrips MUST do ALL of the following:
+
+    - Placed in src/db/migrations
+    - Named with its migration number (0 indexed), and have four
+      numbers of padding. i.e. `0000.sql`, `0030.sql`, or `9999.sql`
+    - In numerical order with all other migrations (cannot go from migration
+      0 to 2).
+    - A postgres transaction. `BEGIN TRANSACTION ... COMMIT TRANSACTION`.
+    - End with the following before COMMIT, where <rev> is the NEXT
+      revision number. (i.e. in `0000.sql` <rev> MUST be 1).
+
+```
+UPDATE sys.database_info SET curr_revision = <rev> WHERE name = current_database();
+```
+
+Example `0000.sql`:
+```sql
+BEGIN TRANSACTION;
+
+CREATE SCHEMA website;
+
+UPDATE sys.database_info SET curr_revision = 1 WHERE name = current_database();
+
+COMMIT TRANSACTION;
+```
+
+Migrations will ONLY EVER be ONCE, and will ALLWAYS be run in order. This means
+that you can assume all previous migrations have run successfully in any current
+migration, and there is NO other possible state in the database.
+
+### API
+
+Once all migrations have been completed, the api will be initalized (if enabled.
+
+If you opt to use postgrest which is builtin into crimson, you must create a
+sql file loads the api schema that MUST do ALL of the following:
+
+    - Placed in src/db/rest and named `rest.sql`
+    - A postgres transaction. `BEGIN TRANSACTION ... COMMIT TRANSACTION`.
+
+Within that transaction you can setup postgres with the api schema you want.
+See https://docs.postgrest.org/en/v12/. (crimson currently uses postgres 12).
+
+NOTE: If you want to load any sql file though an absolute path, src/db will be
+mounted as READ ONLY to /db. (i.e. src/db/rest/rest.sql => /db/rest/rest.sql).
diff --git a/build/db-init/base.sql b/build/db-init/base.sql
new file mode 100644
index 0000000..784eaaa
--- /dev/null
+++ b/build/db-init/base.sql
@@ -0,0 +1,86 @@
+--- CRIMSON --- A simple PHP framework.
+--- Copyright © 2024 Freya Murphy <contact@freyacat.org>
+---
+--- This file is part of CRIMSON.
+---
+--- CRIMSON is free software; you can redistribute it and/or modify it
+--- under the terms of the GNU General Public License as published by
+--- the Free Software Foundation; either version 3 of the License, or (at
+--- your option) any later version.
+---
+--- CRIMSON is distributed in the hope that it will be useful, but
+--- WITHOUT ANY WARRANTY; without even the implied warranty of
+--- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+--- GNU General Public License for more details.
+---
+--- You should have received a copy of the GNU General Public License
+--- along with CRIMSON. If not, see <http://www.gnu.org/licenses/>.
+
+-- Set's up crimsons baseline database. Can be run multiple times.
+
+BEGIN TRANSACTION;
+SET search_path = public;
+SET client_min_messages TO WARNING;
+
+-- Migration Start
+
+CREATE SCHEMA IF NOT EXISTS sys;
+
+-- sys
+
+ALTER SCHEMA sys OWNER TO POSTGRES_USER;
+
+-- sys.*/*
+
+DROP DOMAIN IF EXISTS sys."*/*" CASCADE;
+CREATE DOMAIN sys."*/*" AS BYTEA;
+
+-- sys.database_info
+
+CREATE TABLE IF NOT EXISTS sys.database_info (
+	name TEXT DEFAULT ''::text NOT NULL,
+	jwt_secret TEXT DEFAULT ''::text NOT NULL,
+	curr_revision INTEGER DEFAULT 0 NOT NULL
+);
+
+ALTER TABLE sys.database_info
+	DROP CONSTRAINT IF EXISTS database_info_pkey;
+
+ALTER TABLE sys.database_info
+	ADD CONSTRAINT database_info_pkey PRIMARY KEY (name);
+
+ALTER TABLE sys.database_info OWNER TO POSTGRES_USER;
+
+INSERT INTO sys.database_info
+	(name, curr_revision) VALUES (current_database(), 0)
+	ON CONFLICT DO NOTHING;
+
+-- sys.JWT
+
+DROP TYPE IF EXISTS sys.JWT CASCADE;
+CREATE TYPE sys.JWT AS (
+	token TEXT
+);
+
+-- authenticator
+DO
+$$
+BEGIN
+	IF NOT EXISTS (SELECT * FROM pg_user WHERE usename = 'authenticator')
+	THEN
+		CREATE ROLE authenticator
+			LOGIN NOINHERIT NOCREATEDB NOCREATEROLE NOSUPERUSER;
+	END IF;
+END
+$$;
+
+ALTER ROLE authenticator WITH PASSWORD 'postgrest';
+
+-- anonymous
+
+DROP ROLE IF EXISTS anonymous;
+CREATE ROLE anonymous NOLOGIN;
+
+-- Migration End;
+
+COMMIT TRANSACTION;
diff --git a/build/db-init/db-init b/build/db-init/db-init
new file mode 100755
index 0000000..5b62927
--- /dev/null
+++ b/build/db-init/db-init
@@ -0,0 +1,168 @@
+#!/bin/sh
+
+errors=$(mktemp)
+
+step() {
+	printf '\x1b[34;1m>> %s\x1b[0m\n' "$*"
+}
+
+error() {
+	{
+		printf '\x1b[31;1merror: \x1b[0m%s\n' "$*";
+		grep -v 'current transaction is aborted' < "$errors";
+		printf "\x1b[31m;1error: \x1b[0mAborting migrations, fix file(s) then restart process.";
+	} 1>&2;
+}
+
+try() {
+	"$@" 2> "$errors";
+	count=$(grep -c 'ERROR' < "$errors")
+	if [ "$count" -eq 0 ]; then
+		return 0;
+	else
+		return 1;
+	fi
+}
+
+export PGPASSWORD="$POSTGRES_PASSWORD"
+
+psql() {
+	/usr/bin/psql \
+		-h postgres \
+		-p 5432 \
+		-d "$POSTGRES_DB" \
+		-U "$POSTGRES_USER" \
+		"$@"
+}
+
+pg_isready() {
+	/usr/bin/pg_isready \
+		-h postgres \
+		-p 5432 \
+		-d "$POSTGRES_DB" \
+		-U "$POSTGRES_USER"
+}
+
+curr_revision() {
+	sed "s/POSTGRES_USER/$POSTGRES_USER/g" /var/lib/rev.sql > /tmp/rev.sql
+	psql -qtAX -f /tmp/rev.sql;
+}
+
+wait_until_ready() {
+	step 'Checking if the database is ready...';
+	while true; do
+		pg_isready;
+		code=$?;
+		if [ $code -eq 0 ]; then
+			break;
+		fi
+		sleep 3;
+	done
+}
+
+run_hook() {
+	hook="$1"
+	if [ ! -f "$hook" ]; then
+		printf '\x1b[31;1merror: \x1b[0m%s\n' "required hook not found: '$hook'";
+		return 1;
+	fi
+	if ! try psql -f "$hook"; then
+		error "An error occoured during a hook ($hook)"
+		return 1;
+	fi
+}
+
+run_baseline() {
+	sed "s/POSTGRES_USER/$POSTGRES_USER/g" /var/lib/base.sql > /tmp/base.sql
+	run_hook /tmp/base.sql
+}
+
+run_migrations() {
+	i="$1"
+	while true; do
+		name=$(printf "%04d" "$i");
+		file="/db/migrations/$name.sql"
+		if [ -f "$file" ]; then
+			if try psql -f "$file"; then
+				i=$((i+1));
+				continue;
+			else
+				error "An error occoured during a migration (rev $name)"
+				return 1;
+			fi
+		else
+			return 0;
+		fi
+	done
+}
+
+update_jwt() {
+	if try psql -c "UPDATE sys.database_info SET jwt_secret = '$API_SECRET' WHERE name = current_database();"; then
+		return 0;
+	else
+		error "Could not update JWT"
+		return 1;
+	fi
+}
+
+init_api () {
+	step 'Initalizing the api';
+	# reinit the api schema for
+	# postgrest
+	if ! run_hook "/db/rest/rest.sql"; then
+		return 1;
+	fi
+
+	step 'Updating JWT secret';
+	# make sure postgres has the corrent
+	# jwt secret
+	if ! update_jwt; then
+		return 1;
+	fi
+}
+
+init_db () {
+	# reomve ready status
+	# so php ignores requests
+	rm -f /var/run/crimson/db_ready
+
+	step 'Waiting for database';
+	# make sure the database is running
+	# before we run any requests
+	wait_until_ready;
+	step 'Database ready';
+
+	step 'Loading extensions';
+	# Make sure extensions are loaded
+	if ! run_hook "/var/lib/ext.sql"; then
+		return 1;
+	fi
+
+	step 'Checking baseline'
+	# make sure baseline sys schema exists
+	if ! run_baseline; then
+		return 1;
+	fi
+
+	step 'Peforming migrations';
+	# get the current revision
+	REV=$(curr_revision);
+	step "Database at revision: $REV"
+	# run each migration that is
+	# higher than our current revision
+	if ! run_migrations "$REV"; then
+		return 1;
+	fi
+
+	if [ "$API_ENABLED" = "true" ]; then
+		if ! init_api; then
+			return 1;
+		fi
+	fi
+
+	step 'Database is initialized'
+	# database is ready
+	touch /var/run/crimson/db_ready
+}
+
+init_db
diff --git a/build/db-init/ext.sql b/build/db-init/ext.sql
new file mode 100644
index 0000000..5ec4fea
--- /dev/null
+++ b/build/db-init/ext.sql
@@ -0,0 +1,28 @@
+--- CRIMSON --- A simple PHP framework.
+--- Copyright © 2024 Freya Murphy <contact@freyacat.org>
+---
+--- This file is part of CRIMSON.
+---
+--- CRIMSON is free software; you can redistribute it and/or modify it
+--- under the terms of the GNU General Public License as published by
+--- the Free Software Foundation; either version 3 of the License, or (at
+--- your option) any later version.
+---
+--- CRIMSON is distributed in the hope that it will be useful, but
+--- WITHOUT ANY WARRANTY; without even the implied warranty of
+--- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+--- GNU General Public License for more details.
+---
+--- You should have received a copy of the GNU General Public License
+--- along with CRIMSON. If not, see <http://www.gnu.org/licenses/>.
+
+-- Loads required postgres extensions.
+
+BEGIN TRANSACTION;
+SET search_path = public;
+SET client_min_messages TO WARNING;
+
+CREATE EXTENSION IF NOT EXISTS pgcrypto;
+CREATE EXTENSION IF NOT EXISTS pgjwt;
+
+COMMIT TRANSACTION;
diff --git a/build/db-init/rev.sql b/build/db-init/rev.sql
new file mode 100644
index 0000000..d8443c3
--- /dev/null
+++ b/build/db-init/rev.sql
@@ -0,0 +1,41 @@
+--- CRIMSON --- A simple PHP framework.
+--- Copyright © 2024 Freya Murphy <contact@freyacat.org>
+---
+--- This file is part of CRIMSON.
+---
+--- CRIMSON is free software; you can redistribute it and/or modify it
+--- under the terms of the GNU General Public License as published by
+--- the Free Software Foundation; either version 3 of the License, or (at
+--- your option) any later version.
+---
+--- CRIMSON is distributed in the hope that it will be useful, but
+--- WITHOUT ANY WARRANTY; without even the implied warranty of
+--- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+--- GNU General Public License for more details.
+---
+--- You should have received a copy of the GNU General Public License
+--- along with CRIMSON. If not, see <http://www.gnu.org/licenses/>.
+
+-- Gets the current databse revision.
+
+CREATE OR REPLACE FUNCTION curr_revision()
+RETURNS INTEGER
+LANGUAGE plpgsql VOLATILE
+AS $BODY$
+DECLARE
+	_revision INTEGER;
+BEGIN
+	BEGIN
+		SELECT curr_revision INTO _revision
+			FROM sys.database_info
+			WHERE name = current_database();
+		RETURN _revision;
+	EXCEPTION WHEN OTHERS THEN
+		RETURN 0;
+	END;
+END
+$BODY$;
+
+GRANT EXECUTE ON FUNCTION curr_revision() TO POSTGRES_USER;
+
+SELECT curr_revision();