add pgsql support

14 files changed, 117 insertions, 22 deletions

diff --git a/.docker/app/Dockerfile b/.docker/app/Dockerfile
index dc32cbc..44327a7 100644
--- a/.docker/app/Dockerfile
+++ b/.docker/app/Dockerfile
@@ -1,4 +1,5 @@
-FROM registry.fakecake.org/docker.io/alpine:3.16
+ARG PROXY_REGISTRY
+FROM ${PROXY_REGISTRY}alpine:3.16
 EXPOSE 9000/tcp
 
 ENV SCRIPT_ROOT=/opt/epube
@@ -6,6 +7,7 @@ ENV SRC_DIR=/src/epube
 
 RUN apk add --no-cache php81 php81-fpm \
 	php81-pdo php81-gd php81-mbstring php81-pecl-apcu \
+	php81-pgsql php81-pdo_pgsql postgresql-client \
 	php81-intl php81-xml php81-session php81-opcache \
 	php81-dom php81-fileinfo php81-json \
 	php81-sqlite3 php81-pdo_sqlite sqlite \
@@ -54,6 +56,10 @@ ENV EPUBE_XDEBUG_ENABLED=""
 ENV EPUBE_XDEBUG_HOST=""
 ENV EPUBE_XDEBUG_PORT="9000"
 
+ENV EPUBE_DB_TYPE="sqlite"
+ENV EPUBE_DB_HOST="db"
+ENV EPUBE_DB_PORT="5432"
+
 ENV EPUBE_SCRATCH_DB="db/scratch.db"
 ENV EPUBE_BOOKS_DIR="/books"
 ENV EPUBE_CALIBRE_DB="/books/metadata.db"
diff --git a/.docker/app/startup.sh b/.docker/app/startup.sh
index a307110..f4b66b3 100644
--- a/.docker/app/startup.sh
+++ b/.docker/app/startup.sh
@@ -1,5 +1,12 @@
 #!/bin/sh -e
 
+if [ "$EPUBE_DB_TYPE" == "pgsql" ]; then
+	while ! pg_isready -h $EPUBE_DB_HOST -U $EPUBE_DB_USER; do
+		echo waiting until $EPUBE_DB_HOST is ready...
+		sleep 3
+	done
+fi
+
 if ! id app >/dev/null 2>&1; then
 	# what if i actually need a duplicate GID/UID group?
 
diff --git a/.docker/web-nginx/Dockerfile b/.docker/web-nginx/Dockerfile
index 282e48c..fe56173 100644
--- a/.docker/web-nginx/Dockerfile
+++ b/.docker/web-nginx/Dockerfile
@@ -1,4 +1,5 @@
-FROM registry.fakecake.org/docker.io/nginx:alpine
+ARG PROXY_REGISTRY
+FROM ${PROXY_REGISTRY}nginx:alpine
 
 COPY .docker/web-nginx/nginx.conf /etc/nginx/templates/nginx.conf.template
 
diff --git a/.env-dist b/.env-dist
index 6cd60e3..7c0a8ce 100644
--- a/.env-dist
+++ b/.env-dist
@@ -13,3 +13,8 @@ EPUBE_ADMIN_PASS=password
 # use next HTTP_PORT definition (or remove "127.0.0.1:").
 HTTP_PORT=127.0.0.1:8280
 #HTTP_PORT=8280
+
+# EPUBE_DB_TYPE=pgsql
+# EPUBE_DB_USER=postgres
+# EPUBE_DB_PASS=password
+# EPUBE_DB_NAME=postgres
diff --git a/classes/Config.php b/classes/Config.php
index 3fc8358..5f7b710 100644
--- a/classes/Config.php
+++ b/classes/Config.php
@@ -9,6 +9,12 @@ class Config {
 	// override defaults, defined below in _DEFAULTS[], via environment: DB_TYPE becomes EPUBE_DB_TYPE, etc
 
 	const DB_TYPE = "DB_TYPE";
+	const DB_HOST = "DB_HOST";
+	const DB_USER = "DB_USER";
+	const DB_NAME = "DB_NAME";
+	const DB_PASS = "DB_PASS";
+	const DB_PORT = "DB_PORT";
+
 	const SCRATCH_DB = "SCRATCH_DB";
 	const CALIBRE_DB = "CALIBRE_DB";
 	const BOOKS_DIR = "BOOKS_DIR";
@@ -22,6 +28,11 @@ class Config {
 
 	private const _DEFAULTS = [
 		Config::DB_TYPE => [ "sqlite", Config::T_STRING ],
+		Config::DB_HOST => [ "db", Config::T_STRING ],
+		Config::DB_USER => [ "", Config::T_STRING ],
+		Config::DB_NAME => [ "", Config::T_STRING ],
+		Config::DB_PASS => [ "", Config::T_STRING ],
+		Config::DB_PORT => [ "5432", Config::T_STRING ],
 		Config::SCRATCH_DB => [ "db/scratch.db", Config::T_STRING ],
 		Config::CALIBRE_DB => [ "", Config::T_STRING ],
 		Config::BOOKS_DIR => [ "", Config::T_STRING ],
diff --git a/classes/Db.php b/classes/Db.php
index bf14468..dcaa482 100644
--- a/classes/Db.php
+++ b/classes/Db.php
@@ -1,24 +1,40 @@
 <?php
+
 class Db {
 	private static ?Db $instance = null;
 
 	private PDO $pdo;
 
 	private function __construct() {
+		$db_type = Config::get(Config::DB_TYPE);
+
 		try {
-			$this->pdo = new PDO(self::get_dsn());
+			$this->pdo = new PDO(self::get_dsn(),
+				Config::get(Config::DB_USER),
+				Config::get(Config::DB_PASS));
+
+			if ($db_type == 'sqlite') {
+				$this->pdo->query('PRAGMA journal_mode = wal');
+			}
 		} catch (Exception $e) {
 			user_error($e, E_USER_WARNING);
-			die("Unable to initialize database driver (SQLite).");
+			die("Unable to initialize database driver: " . Config::get(Config::DB_TYPE));
 		}
-		//$this->dbh->busyTimeout(30*1000);
+
 		$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
-		$this->pdo->query('PRAGMA journal_mode = wal');
 
 		try {
 			ORM::configure(self::get_dsn());
 			ORM::configure('return_result_sets', true);
-			ORM::raw_execute('PRAGMA journal_mode = wal');
+
+			if ($db_type == 'pgsql') {
+				ORM::configure('username', Config::get(Config::DB_USER));
+				ORM::configure('password', Config::get(Config::DB_PASS));
+				ORM::configure('return_result_sets', true);
+			} else {
+				ORM::raw_execute('PRAGMA journal_mode = wal');
+			}
+
 		} catch (Exception $e) {
 			user_error($e, E_USER_WARNING);
 			die("Unable to initialize ORM layer.");
@@ -26,7 +42,17 @@ class Db {
 	}
 
 	public static function get_dsn() : string {
-		return Config::get(Config::DB_TYPE) . ':' . Config::get(Config::SCRATCH_DB);
+		$db_type = Config::get(Config::DB_TYPE);
+
+		if ($db_type == 'pgsql') {
+			$db_port = Config::get(Config::DB_PORT) ? ';port=' . Config::get(Config::DB_PORT) : '';
+			$db_host = Config::get(Config::DB_HOST) ? ';host=' . Config::get(Config::DB_HOST) : '';
+
+			return $db_type . ':dbname=' . Config::get(Config::DB_NAME) . $db_host . $db_port;
+
+		} else {
+			return $db_type . ':' . Config::get(Config::SCRATCH_DB);
+		}
 	}
 
 	public static function pdo() : PDO {
diff --git a/docker-compose.yml b/docker-compose.yml
index 5f08922..5c3427d 100644
--- a/docker-compose.yml
+++ b/docker-compose.yml
@@ -5,8 +5,19 @@
 version: '3'
 
 services:
+  db:
+    image: postgres:15-alpine
+    restart: unless-stopped
+    env_file:
+      - .env
+    environment:
+      - POSTGRES_USER=${EPUBE_DB_USER}
+      - POSTGRES_PASSWORD=${EPUBE_DB_PASS}
+      - POSTGRES_DB=${EPUBE_DB_NAME}
+    ports:
+      - 127.0.0.1:15432:5432
+
   app:
-    image: cthulhoo/the-epube-fpm-static:latest
     environment:
       SKIP_RSYNC_ON_STARTUP: true
     build:
@@ -18,9 +29,10 @@ services:
     volumes:
       - .:/var/www/html/books
       - ${BOOKS_DIR}:/books:ro
+    depends_on:
+      - db
 
   web-nginx:
-    image: cthulhoo/the-epube-web-nginx:latest
     build:
       dockerfile: .docker/web-nginx/Dockerfile
       context: .
diff --git a/include/sessions.php b/include/sessions.php
index bf2cec0..8fbab1d 100644
--- a/include/sessions.php
+++ b/include/sessions.php
@@ -30,7 +30,7 @@
 		if (!empty($_SESSION["owner"])) {
 
 			$user = ORM::for_table('epube_users')
-				->where('user', $_SESSION['owner'])
+				->where('username', $_SESSION['owner'])
 				->find_one();
 
 			if ($user && sha1($user->pass) == $_SESSION['pass_hash']) {
diff --git a/login.php b/login.php
index a8df8ac..96375d7 100644
--- a/login.php
+++ b/login.php
@@ -17,7 +17,7 @@
 		$pass_hash = 'SHA256:' . hash('sha256', "$username:" . trim($_REQUEST["password"]));
 
 		$user = ORM::for_table('epube_users')
-			->where('user', $username)
+			->where('username', $username)
 			->where('pass', $pass_hash)
 			->find_one();
 
@@ -62,7 +62,7 @@
 
 			if ($username) {
 				$user = ORM::for_table('epube_users')
-					->where('user', $username)
+					->where('username', $username)
 					->find_one();
 
 				if ($user) {
diff --git a/sql/pgsql/migrations/1.sql b/sql/pgsql/migrations/1.sql
new file mode 100644
index 0000000..e0298b7
--- /dev/null
+++ b/sql/pgsql/migrations/1.sql
@@ -0,0 +1 @@
+select true;
diff --git a/sql/pgsql/schema.sql b/sql/pgsql/schema.sql
new file mode 100644
index 0000000..3616f06
--- /dev/null
+++ b/sql/pgsql/schema.sql
@@ -0,0 +1,23 @@
+create table if not exists epube_users (
+	id serial not null primary key,
+	username varchar(100) not null unique,
+	pass varchar(200) not null);
+
+create table if not exists epube_pagination (
+	id serial not null primary key,
+	bookid bigint not null,
+	total_pages bigint not null,
+	pagination text not null);
+
+create table if not exists epube_books (
+	id serial not null primary key,
+	bookid bigint not null,
+	owner varchar(200) not null not null references epube_users(username) on delete cascade,
+	lastts bigint not null,
+	lastcfi text not null,
+	lastread bigint not null);
+
+create table if not exists epube_favorites(
+	id serial not null primary key,
+	bookid bigint not null,
+	owner varchar(200) not null references epube_users(username) on delete cascade);
diff --git a/sql/sqlite/migrations/1.sql b/sql/sqlite/migrations/1.sql
new file mode 100644
index 0000000..198131a
--- /dev/null
+++ b/sql/sqlite/migrations/1.sql
@@ -0,0 +1 @@
+alter table epube_users RENAME COLUMN user to username;
diff --git a/sql/sqlite/schema.sql b/sql/sqlite/schema.sql
index a0daf61..eeab2f9 100644
--- a/sql/sqlite/schema.sql
+++ b/sql/sqlite/schema.sql
@@ -4,6 +4,11 @@ create table if not exists epube_pagination (
 	total_pages integer not null,
 	pagination text not null);
 
+create table if not exists epube_users (
+	id integer not null primary key autoincrement,
+	username varchar(100) not null,
+	pass varchar(200) not null);
+
 create table if not exists epube_books (
 	id integer not null primary key autoincrement,
 	bookid integer not null,
@@ -12,11 +17,6 @@ create table if not exists epube_books (
 	lastcfi varchar(200) not null,
 	lastread integer not null);
 
-create table if not exists epube_users (
-	id integer not null primary key autoincrement,
-	user varchar(100) not null,
-	pass varchar(200) not null);
-
 create table if not exists epube_favorites(
 	id integer not null primary key autoincrement,
 	bookid integer not null,
diff --git a/update.php b/update.php
index 968e022..fda9ecf 100644
--- a/update.php
+++ b/update.php
@@ -54,7 +54,9 @@
 			}
 
 			$migrations = Config::get_migrations();
-			$migrations->migrate();
+			$rc = $migrations->migrate();
+
+			exit($rc ? 0 : 1);
 
 		} else {
 			Debug::log("Database schema is already at latest version.");
@@ -66,7 +68,7 @@
 		$username = $options["user-del"];
 
 		$user = ORM::for_table('epube_users')
-			->where('user', $username)
+			->where('username', $username)
 			->find_one();
 
 		if ($user) {
@@ -104,7 +106,7 @@
 		$pass_hash = 'SHA256:' . hash('sha256', "$username:" . trim($pass));
 
 		$user = ORM::for_table('epube_users')
-			->where('user', $username)
+			->where('username', $username)
 			->find_one();
 
 		if ($user) {
@@ -114,7 +116,7 @@
 		} else {
 			Debug::log("Adding user $username.");
 			$user = ORM::for_table('epube_users')->create();
-			$user->user = $username;
+			$user->username = $username;
 			$user->pass = $pass_hash;
 			$user->save();
 		}