From 9874d0660cf4218172e909ce0933731babb75bda Mon Sep 17 00:00:00 2001 From: root Date: Tue, 25 Aug 2026 01:48:04 +0200 Subject: [PATCH] maintenance/db.py --- maintenance/db.py | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/maintenance/db.py b/maintenance/db.py index bd8aad9..06c2896 100755 --- a/maintenance/db.py +++ b/maintenance/db.py @@ -15,7 +15,7 @@ import psycopg2.extras import config print("Mattermost DB cleanup script: https://git.somenet.org/pub/jan/mattermost-privileged.git") -print("Tested on 10.11\n") +print("Tested on 11.09\n") dbconn = psycopg2.connect(config.dbconnstring) dbconn.set_session(autocommit=False) @@ -58,7 +58,7 @@ _ = [print(row, file=sys.stderr) for row in cur.fetchall()] ## 48 months+ not active accounts (no recent status, no recent account update, no recent posts) -cur.execute("""UPDATE posts SET userid = '"""+config.deleted_user_uid+"""' WHERE userid IN (SELECT id FROM ( +cur.execute("""UPDATE posts SET userid = '"""+config.deleted_user_uid+"""' WHERE userid<>'"""+config.deleted_user_uid+"""' AND userid IN (SELECT id FROM ( SELECT users.id, users.username, users.email, status.status, status.manual, date_trunc('second',TO_TIMESTAMP(GREATEST(status.lastactivityat, MAX(sessions.lastactivityat), users.updateat)/1000)) as lastactivity FROM users LEFT JOIN status ON (users.id = status.userid) LEFT JOIN sessions ON (users.id = sessions.userid) GROUP BY users.id, users.username, users.email, status.status, status.manual, status.lastactivityat, users.updateat ORDER BY lastactivity DESC @@ -66,7 +66,7 @@ cur.execute("""UPDATE posts SET userid = '"""+config.deleted_user_uid+"""' WHERE ) RETURNING *""") print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} chowned 48 months+ not used account(s) public post(s).") -cur.execute("""DELETE FROM users WHERE id IN (SELECT id FROM ( +cur.execute("""DELETE FROM users WHERE id<>'"""+config.deleted_user_uid+"""' AND id IN (SELECT id FROM ( SELECT users.id, users.username, users.email, status.status, status.manual, date_trunc('second',TO_TIMESTAMP(GREATEST(status.lastactivityat, MAX(sessions.lastactivityat), users.updateat)/1000)) as lastactivity FROM users LEFT JOIN status ON (users.id = status.userid) LEFT JOIN sessions ON (users.id = sessions.userid) GROUP BY users.id, users.username, users.email, status.status, status.manual, status.lastactivityat, users.updateat ORDER BY lastactivity DESC -- 2.47.3