From eb52a7da8e2cafa1892d3f8810a69435b7a9b854 Mon Sep 17 00:00:00 2001 From: Andrew Dolgov Date: Sun, 18 Feb 2024 12:34:17 +0300 Subject: add pgsql support --- .docker/app/Dockerfile | 8 +++++++- .docker/app/startup.sh | 7 +++++++ .docker/web-nginx/Dockerfile | 3 ++- .env-dist | 5 +++++ classes/Config.php | 11 +++++++++++ classes/Db.php | 38 ++++++++++++++++++++++++++++++++------ docker-compose.yml | 16 ++++++++++++++-- include/sessions.php | 2 +- login.php | 4 ++-- sql/pgsql/migrations/1.sql | 1 + sql/pgsql/schema.sql | 23 +++++++++++++++++++++++ sql/sqlite/migrations/1.sql | 1 + sql/sqlite/schema.sql | 10 +++++----- update.php | 10 ++++++---- 14 files changed, 117 insertions(+), 22 deletions(-) create mode 100644 sql/pgsql/migrations/1.sql create mode 100644 sql/pgsql/schema.sql create mode 100644 sql/sqlite/migrations/1.sql 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 @@ 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(); } -- cgit v1.2.3