Fix database cleanup and independent from creat fix

7 files changed, 79 insertions, 70 deletions

diff --git a/service/Dockerfile b/service/Dockerfile
index 8ec6d1e..7c29aa2 100644
--- a/service/Dockerfile
+++ b/service/Dockerfile
@@ -1,8 +1,6 @@
 FROM ubuntu:20.04
 
-RUN apt-get update
-
-RUN apt-get install -y nginx php-fpm php-sqlite3 sqlite3 cron
+RUN apt update && apt install -y nginx php-fpm php-sqlite3 sqlite3
 COPY nginx.conf /etc/nginx/nginx.conf
 
 COPY entrypoint.sh /root
@@ -12,8 +10,5 @@ ENV TZ=UTC
 RUN ln -sf /usr/share/zoneinfo/$TZ /etc/localtime
 RUN echo $TZ > /etc/timezone
 
-RUN echo "SHELL=/bin/bash" > /etc/cron.d/cleaner
-RUN echo "* * * * * www-data /service/cleaner /service/www" >> /etc/cron.d/cleaner
-
 WORKDIR /service
 ENTRYPOINT /root/entrypoint.sh
diff --git a/service/entrypoint.sh b/service/entrypoint.sh
index 2312653..bd566b8 100644
--- a/service/entrypoint.sh
+++ b/service/entrypoint.sh
@@ -1,58 +1,16 @@
-#!/bin/sh
+#!/bin/bash
 
-sqlite3 db.sqlite << EOF
+touch /service/db.sqlite
+sqlite3 /service/db.sqlite < init.sql
 
-CREATE TABLE IF NOT EXISTS users (
-	uid INTEGER PRIMARY KEY,
-	user STRING NOT NULL UNIQUE,
-	pass STRING NOT NULL,
-	auth STRING NOT NULL,
-	creat INTEGER NOT NULL
-);
+chmod 777 /service/{.,files,reports,db.sqlite}
 
-CREATE TABLE IF NOT EXISTS files (
-	uid INTEGER SECONDARY KEY,
-	file STRING NOT NULL,
-	dir STRING NOT NULL,
-	creat INTEGER NOT NULL,
-	UNIQUE(uid, file) ON CONFLICT ABORT,
-	FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE
-);
-
-CREATE TABLE IF NOT EXISTS reports (
-	uid INTEGER SECONDARY KEY,
-	file STRING NOT NULL,
-	creat INTEGER NOT NULL,
-	FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE
-);
-
-INSERT OR IGNORE INTO users (uid, user, pass, auth, creat)
-	VALUES (1, "catlover1998", "ILOVECATS123",
-		hex(randomblob(16)), strftime('%s', 'now'));
-
-INSERT OR IGNORE INTO files (uid, file, dir, creat)
-	VALUES (1, "cat.jpg", "54b8617eca0e54c7d3c8e6732c6b687a",
-		strftime('%s', 'now'));
-INSERT OR IGNORE INTO files (uid, file, dir, creat)
-	VALUES (1, "cat2.jpg", "4307ab44204de40235bad8c66cce0ae9",
-		strftime('%s', 'now'));
-INSERT OR IGNORE INTO files (uid, file, dir, creat)
-	VALUES (1, "cat3.jpg", "6ed554592bbf418df53acb9317644d58",
-		strftime('%s', 'now'));
-INSERT OR IGNORE INTO files (uid, file, dir, creat)
-	VALUES (1, "cat4.jpg", "bdd921efdb71adfc8c097a7ce0718eb3",
-		strftime('%s', 'now'));
-
-INSERT OR IGNORE INTO reports (uid, file, creat)
-	VALUES (1, "2a4db6742253d94be4cf0e56c971fced", strftime('%s', 'now'));
-
-EOF
-
-chmod 777 . files reports db.sqlite
-
-/etc/init.d/cron start
 /etc/init.d/nginx start
 /etc/init.d/php7.4-fpm start
 
-tail -f /var/log/nginx/error.log
+while true; do
+	/service/cleaner
+	sleep 60
+done &
 
+tail -f /var/log/nginx/error.log
diff --git a/service/www/cleaner b/service/www/cleaner
index 24909c6..9667951 100755
--- a/service/www/cleaner
+++ b/service/www/cleaner
@@ -1,14 +1,21 @@
 #!/bin/bash
 
-cd ${1:-.}
-
 while IFS= read -r row; do
-	rm -rf "/service/uploads/$row"
-done < <(sqlite3 db.sqlite "SELECT dir FROM users WHERE creat < strftime('%s', 'now') - 780")
+	path=$(realpath "/service/files/$row")
+	if [[ "$(dirname "$path")" -ef "/service/files" ]]; then
+		rm -rf "$path"
+	fi
+done < <(sqlite3 /service/db.sqlite \
+	"SELECT dir FROM files WHERE creat < strftime('%s', 'now') - 10")
 
 while IFS= read -r row; do
-	rm -rf "/service/reports/$row"
-done < <(sqlite3 db.sqlite3 "SELECT file FROM reports WHERE creat < strftime('%s', 'now') - 780")
+	path=$(realpath "/service/reports/$row")
+	if [ "$(dirname "$path")" -ef "/service/reports" ]; then
+		rm -rf "$path"
+	fi
+done < <(sqlite3 /service/db.sqlite \
+	"SELECT file FROM reports WHERE creat < strftime('%s', 'now') - 10")
 
-sqlite3 db.sqlite "DELETE FROM users WHERE creat < strftime('%s', 'now') - 780"
+sqlite3 /service/db.sqlite "PRAGMA foreign_keys = ON; \
+	DELETE FROM users WHERE creat < strftime('%s', 'now') - 10"
 
diff --git a/service/www/index.php b/service/www/index.php
index a3af8cf..93ec952 100644
--- a/service/www/index.php
+++ b/service/www/index.php
@@ -19,7 +19,8 @@ $head = <<<EOF
 <html>
 <head>
 	<title>Catchbox</title>
-	<link rel="stylesheet" href="/style.css">
+	<link rel="icon" type="image/x-icon" href="/static/favicon.ico">
+	<link rel="stylesheet" href="/static/style.css">
 </head>
 <body>
 EOF;
@@ -32,11 +33,13 @@ function load() {
 		/* https://phiresky.github.io/blog/2020/sqlite-performance-tuning/ */
 		$db = new SQLite3("db.sqlite");
 		$db->busyTimeout(15000);
-		$db->exec("PRAGMA journal_mode = WAL;");
-		$db->exec("PRAGMA synchronous = normal;");
-		$db->exec("PRAGMA temp_storage = memory;");
-		$db->exec("PRAGMA mmap_size = 30000000000;");
-		$db->exec("PRAGMA page_size = 32768;");
+		$db->exec("PRAGMA journal_mode = WAL");
+		$db->exec("PRAGMA synchronous = normal");
+		$db->exec("PRAGMA temp_storage = memory");
+		$db->exec("PRAGMA mmap_size = 30000000000");
+		$db->exec("PRAGMA page_size = 32768");
+		/* for ON DELETE CASCADE */
+		$db->exec("PRAGMA foreign_keys = ON");
 	}
 	return $db;
 }
diff --git a/service/www/init.sql b/service/www/init.sql
new file mode 100644
index 0000000..5855b4b
--- /dev/null
+++ b/service/www/init.sql
@@ -0,0 +1,46 @@
+CREATE TABLE IF NOT EXISTS users (
+	uid INTEGER PRIMARY KEY,
+	user STRING NOT NULL UNIQUE,
+	pass STRING NOT NULL,
+	auth STRING NOT NULL,
+	creat INTEGER NOT NULL,
+	delet INTEGER DEFAULT NULL
+);
+
+CREATE TABLE IF NOT EXISTS files (
+	uid INTEGER SECONDARY KEY,
+	file STRING NOT NULL,
+	dir STRING NOT NULL,
+	creat INTEGER NOT NULL,
+	UNIQUE(uid, file) ON CONFLICT ABORT,
+	FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE
+);
+
+CREATE TABLE IF NOT EXISTS reports (
+	uid INTEGER SECONDARY KEY,
+	file STRING NOT NULL,
+	creat INTEGER NOT NULL,
+	FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE
+);
+
+CREATE TRIGGER IF NOT EXISTS users_delete_by
+AFTER INSERT ON users
+BEGIN
+    UPDATE users SET delet = strftime("%s", "now") + 780 WHERE delet IS NULL;
+END;
+
+INSERT OR IGNORE INTO users (uid, user, pass, auth, creat)
+	VALUES (1, "catlover1998", "ILOVECATS123",
+		hex(randomblob(16)), 9999999999);
+
+INSERT OR IGNORE INTO files (uid, file, dir, creat)
+	VALUES (1, "cat.jpg", "54b8617eca0e54c7d3c8e6732c6b687a", 9999999999);
+INSERT OR IGNORE INTO files (uid, file, dir, creat)
+	VALUES (1, "cat2.jpg", "4307ab44204de40235bad8c66cce0ae9", 9999999999);
+INSERT OR IGNORE INTO files (uid, file, dir, creat)
+	VALUES (1, "cat3.jpg", "6ed554592bbf418df53acb9317644d58", 9999999999);
+INSERT OR IGNORE INTO files (uid, file, dir, creat)
+	VALUES (1, "cat4.jpg", "bdd921efdb71adfc8c097a7ce0718eb3", 9999999999);
+
+INSERT OR IGNORE INTO reports (uid, file, creat)
+	VALUES (1, "2a4db6742253d94be4cf0e56c971fced", 9999999999);
diff --git a/service/www/favicon.ico b/service/www/static/favicon.ico
index 01b2a83..01b2a83 100644
--- a/service/www/favicon.ico
+++ b/service/www/static/favicon.ico
diff --git a/service/www/style.css b/service/www/static/style.css
index 8032096..8032096 100644
--- a/service/www/style.css
+++ b/service/www/static/style.css