api.spaceplanner.app

commit 0cc7e62609eb47f85ec919efe886a5d58a5f6d63
parent 225e0dbac33eb452a9357c99d7b85bb59ce01e2f
Author: Jacob R. Edwards <jacob@jacobedwards.org>
Date:   Fri, 26 Jul 2024 20:46:53 -0700

Add Makefile with database managing targets

Provides targets to create, update, backup, restore, and edit the
database and also adds a target which wraps go build.

Diffstat:
A
cmd/api/Makefile
 | 
47
+++++++++++++++++++++++++++++++++++++++++++++++
A
cmd/api/migration/2024-07-26T13-51-13.sql
 | 
4
++++
A
cmd/api/scripts/dbupdates
 | 
22
++++++++++++++++++++++
A
cmd/api/scripts/updatedb
 | 
23
+++++++++++++++++++++++

4 files changed, 96 insertions(+), 0 deletions(-)
diff --git a/cmd/api/Makefile b/cmd/api/Makefile
@@ -0,0 +1,47 @@
+name = api.spaceplanner.app
+
+# Database
+dbname = ${name}
+dbdir = /var/postgresql/data
+dbuser = _postgresql
+dburl = postgresql://${dbuser}@localhost/${dbname}
+dbsql = migration
+dbbackups = backups
+
+all: ${name}
+
+${name}:
+	go build
+
+db: createdb updatedb
+
+createdb:
+	createdb -O ${dbuser} ${dbname}
+
+# TODO:
+# Have last applied update be written to database specific file
+# (${dbname})
+updatedb: backupdb
+	for f in $$(./scripts/dbupdates ${dbsql}); do \
+		psql ${dburl} < ${dbsql}/$$f || { \
+			echo "FAILED TO APPLY $$f" ; \
+			exit 1 ; \
+		}; \
+		echo "APPLIED $$f" ; \
+		echo ${dbsql}/"$ff" > ${dbsql}/last ; \
+	done
+
+backupdb:
+	mkdir -p ${dbbackups}
+	pg_dump ${dbname} > ${dbbackups}/$$(date +%FT%T).sql
+
+restoredb: backupdb
+	b="${dbbackups}/$$(ls ${dbbackups} | sed -n '$p')"; \
+	make backupdb; \
+	psql ${dburl} < "$$b" && \
+	make backupdb
+
+editdb:
+	psql ${dburl}
+
+.PHONY: all db createdb updatedb backupdb restoredb editdb
diff --git a/cmd/api/migration/2024-07-26T13-51-13.sql b/cmd/api/migration/2024-07-26T13-51-13.sql
@@ -0,0 +1,4 @@
+CREATE TABLE users (
+	name varchar(128) PRIMARY KEY,
+	hash varchar
+);
diff --git a/cmd/api/scripts/dbupdates b/cmd/api/scripts/dbupdates
@@ -0,0 +1,22 @@
+#!/bin/sh
+
+set -e
+
+dir="$1"
+last="$dir"/last
+
+if test -f "$last"; then
+	last="$(<"$last")" 
+else
+	last=""
+fi
+ls "$dir" | awk -vl="$last" 'BEGIN {
+	if(l=="")
+		p=1
+}
+
+p && $0 != "last"
+
+l == $0 {
+	p = 1
+}'
diff --git a/cmd/api/scripts/updatedb b/cmd/api/scripts/updatedb
@@ -0,0 +1,23 @@
+#!/bin/sh
+# List unapplied database updates
+
+set -e
+
+dir="$1"
+last="$dir"/last
+
+if test -f "$last"; then
+	last="$(<"$last")" 
+else
+	last=""
+fi
+ls "$dir" | awk -vl="$last" 'BEGIN {
+	if(l=="")
+		p=1
+}
+
+p && $0 != "last"
+
+l == $0 {
+	p = 1
+}'