Automatically clean database entries older than 13 minutes

3 files changed, 21 insertions, 8 deletions

diff --git a/service/app.py b/service/app.py
index 7136568..87c30cd 100644
--- a/service/app.py
+++ b/service/app.py
@@ -496,6 +496,7 @@ def create_runner():
 async def main():
     global db
     db = await aiosqlite.connect("data/db.sqlite")
+    await db.execute("PRAGMA foreign_keys = ON")
     runner = create_runner()
     await runner.setup()
     site = web.TCPSite(runner, "0.0.0.0", 1812)
diff --git a/service/init.sql b/service/init.sql
index a7f4fbc..dd31b3a 100644
--- a/service/init.sql
+++ b/service/init.sql
@@ -1,6 +1,6 @@
-CREATE TABLE IF NOT EXISTS
-users(
+CREATE TABLE IF NOT EXISTS users(
 	id INTEGER PRIMARY KEY AUTOINCREMENT,
+	creat INTEGER DEFAULT null,
 	name TEXT UNIQUE,
 	p TEXT,
 	q TEXT,
@@ -9,15 +9,20 @@ users(
 	y TEXT
 );
 
-CREATE TABLE IF NOT EXISTS
-events(
+CREATE TABLE IF NOT EXISTS events(
 	id INTEGER PRIMARY KEY AUTOINCREMENT,
 	userid INTEGER SECONDARY KEY,
 	time TEXT,
 	wish TEXT,
 	x FLOAT,
-	y FLOAT
+	y FLOAT,
+	FOREIGN KEY(userid) REFERENCES users(id) ON DELETE CASCADE
 );
 
-CREATE INDEX IF NOT EXISTS
-users_name ON users(name);
+CREATE INDEX IF NOT EXISTS users_name ON users(name);
+
+CREATE TRIGGER IF NOT EXISTS users_creat
+AFTER INSERT ON users
+BEGIN
+    UPDATE users SET creat = strftime("%s", "now") WHERE creat IS null;
+END
diff --git a/service/run.sh b/service/run.sh
index 2bb6eae..c351996 100755
--- a/service/run.sh
+++ b/service/run.sh
@@ -3,4 +3,11 @@
 touch /service/data/db.sqlite
 sqlite3 /service/data/db.sqlite < init.sql
 
-python3 -W ignore /service/app.py
+while true; do
+	sqlite3 /service/data/db.sqlite \
+		"PRAGMA foreign_keys = ON; \
+		DELETE FROM users WHERE creat < strftime('%s', 'now') - 780"
+	sleep 60
+done &
+
+python3 /service/app.py