maintenance/db.py

   1 #!/usr/bin/env -S python3 -Bu
   2 # Someone's Mattermost maintenance scripts.
   3 #   Copyright (c) 2016-2023 by Someone <someone@somenet.org> (aka. Jan Vales <jan@jvales.net>)
   4 #   published under MIT-License
   5 #
   6 # Permanently delete {"deleted",orphaned,old,unused} db-data. Also fix some of MM's db-health degrading bugs/stuff. And enforce our system policy.
   7 #
   8 # Some code is duplicated - could not decide what to stick with - also its for the stats! :)
   9 #
  10
  11 import sys
  12 import time
  13 import psycopg2
  14 import psycopg2.extras
  15
  16 import config
  17 print("Mattermost DB cleanup script: https://git.somenet.org/pub/jan/mattermost-privileged.git")
  18 print("Tested on 9.1\n")
  19
  20 dbconn = psycopg2.connect(config.dbconnstring)
  21 dbconn.set_session(autocommit=False)
  22
  23
  24 TS_START = time.time()
  25
  26 #########################
  27 # enforce system policy #
  28 #########################
  29 print("Enforcing system policy ...")
  30 cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
  31
  32 # 31 days+ not active and not verified users (no recent status, no recent account update, no recent posts)
  33 cur.execute("""DELETE FROM users WHERE users.id IN
  34             (SELECT users.id FROM users LEFT JOIN status ON (users.id = status.userid AND lastactivityat < extract(epoch from (NOW() - INTERVAL '31 day'))*1000)
  35               WHERE emailverified = false AND users.updateat < extract(epoch from (NOW() - INTERVAL '31 day'))*1000 AND users.id NOT IN (SELECT distinct userid FROM posts WHERE createat > extract(epoch from (NOW() - INTERVAL '31 day'))*1000)
  36             ) RETURNING *""")
  37 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 31 days+ not verified account(s).")
  38 _ = [print(row, file=sys.stderr) for row in cur.fetchall()]
  39
  40
  41 ## 91 days+ not active guest accounts (no recent status, no recent account update, no recent posts)
  42 cur.execute("""UPDATE posts SET userid = '"""+config.deleted_user_uid+"""' WHERE userid IN (SELECT id FROM (
  43                 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
  44                 FROM users LEFT JOIN status ON (users.id = status.userid) LEFT JOIN sessions ON (users.id = sessions.userid)
  45                 WHERE users.roles like '%system_guest%' GROUP BY users.id, users.username, users.email, status.status, status.manual, status.lastactivityat, users.updateat ORDER BY lastactivity DESC
  46             ) A where extract(epoch from lastactivity)*1000 < extract(epoch from (NOW() - INTERVAL '91 day'))*1000
  47             ) RETURNING *""")
  48 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} chowned 91 days+ not used guest-account(s) public post(s).")
  49
  50 cur.execute("""DELETE FROM users WHERE id IN (SELECT id FROM (
  51                 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
  52                 FROM users LEFT JOIN status ON (users.id = status.userid) LEFT JOIN sessions ON (users.id = sessions.userid)
  53                 WHERE users.roles like '%system_guest%' GROUP BY users.id, users.username, users.email, status.status, status.manual, status.lastactivityat, users.updateat ORDER BY lastactivity DESC
  54             ) A where extract(epoch from lastactivity)*1000 < extract(epoch from (NOW() - INTERVAL '91 day'))*1000
  55             ) RETURNING *""")
  56 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 91 days+ not used guest-account(s).")
  57 _ = [print(row, file=sys.stderr) for row in cur.fetchall()]
  58
  59
  60 ## 48 months+ not active accounts (no recent status, no recent account update, no recent posts)
  61 cur.execute("""UPDATE posts SET userid = '"""+config.deleted_user_uid+"""' WHERE userid IN (SELECT id FROM (
  62                 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
  63                 FROM users LEFT JOIN status ON (users.id = status.userid) LEFT JOIN sessions ON (users.id = sessions.userid)
  64                 GROUP BY users.id, users.username, users.email, status.status, status.manual, status.lastactivityat, users.updateat ORDER BY lastactivity DESC
  65             ) A where extract(epoch from lastactivity)*1000 < extract(epoch from (NOW() - INTERVAL '48 month'))*1000
  66             ) RETURNING *""")
  67 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} chowned 48 months+ not used account(s) public post(s).")
  68
  69 cur.execute("""DELETE FROM users WHERE id IN (SELECT id FROM (
  70                 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
  71                 FROM users LEFT JOIN status ON (users.id = status.userid) LEFT JOIN sessions ON (users.id = sessions.userid)
  72                 GROUP BY users.id, users.username, users.email, status.status, status.manual, status.lastactivityat, users.updateat ORDER BY lastactivity DESC
  73             ) A where extract(epoch from lastactivity)*1000 < extract(epoch from (NOW() - INTERVAL '48 month'))*1000
  74             ) RETURNING *""")
  75 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 48 months+ not used account(s).")
  76 _ = [print(row, file=sys.stderr) for row in cur.fetchall()]
  77
  78
  79 ## 91 days+ disabled accounts
  80 #cur.execute("""UPDATE posts SET userid = '"""+config.deleted_user_uid+"""' WHERE userid IN (SELECT id FROM users WHERE users.deleteat <> 0 AND deleteat < extract(epoch from (NOW() - INTERVAL '91 day'))*1000) RETURNING *""")
  81 #print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} chown 91 days+ disabled account(s) public post(s).")
  82 #
  83 #cur.execute("""DELETE FROM users WHERE users.deleteat <> 0 AND deleteat < extract(epoch from (NOW() - INTERVAL '91 day'))*1000 RETURNING *""")
  84 #print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 91 days+ disabled account(s).")
  85 #_ = [print(row, file=sys.stderr) for row in cur.fetchall()]
  86
  87
  88 # move channel-creatorship of deleted users to deleted_user
  89 cur.execute("""UPDATE channels SET creatorid = '"""+config.deleted_user_uid+"""' WHERE creatorid NOT IN (SELECT id FROM users) AND creatorid != '' RETURNING *""")
  90 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} chowned channel-creatorship(s) of deleted users to deleted_user.")
  91
  92
  93 # 12 months inactive non-public one-user-channels (mostly DM-channels where the other user was deleted; they are inaccessible for the other user anyway)
  94 cur.execute("""DELETE FROM channels WHERE type IS DISTINCT FROM 'O' AND lastpostat < extract(epoch from (NOW() - INTERVAL '12 month'))*1000 AND updateat < extract(epoch from (NOW() - INTERVAL '12 month'))*1000 AND
  95             id IN (SELECT id FROM (SELECT count(id) as cnt, id FROM channels LEFT JOIN channelmembers ON (id = channelid) GROUP BY id) AS a WHERE cnt < 2)
  96             RETURNING *""")
  97 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 12 months+ inactive non-public one-user-channel(s).")
  98 _ = [print(row, file=sys.stderr) for row in cur.fetchall()]
  99
 100
 101 # bot breaking channels in vowi team
 102 cur.execute("""DELETE FROM channels WHERE teamid = 'sswtb6oqciyyfmkibh6mjz479w' AND type = 'O' AND creatorid NOT IN ('5ugpycz1mfrj3ff4k6hbg6g37o', '') RETURNING *""")
 103 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted breaking channel(s).")
 104 _ = [print(row, file=sys.stderr) for row in cur.fetchall()]
 105
 106
 107 cur.close()
 108 if not hasattr(config, "enforce_system_policy") or hasattr(config, "enforce_system_policy") and not config.enforce_system_policy:
 109     dbconn.rollback()
 110     print("\n*** rollback - not enforcing system policy ***")
 111 print()
 112
 113
 114
 115 ##################################
 116 # Delete old unused/history data #
 117 ##################################
 118 print("Deleting old unused/history data ...")
 119 cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
 120
 121 # old audit entries
 122 cur.execute("""DELETE FROM audits WHERE createat < extract(epoch from (NOW() - INTERVAL '31 day'))*1000 RETURNING *""")
 123 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted old audit entries.")
 124
 125 # old job entries
 126 cur.execute("""DELETE FROM jobs WHERE createat < extract(epoch from (NOW() - INTERVAL '31 day'))*1000 AND status = 'success' RETURNING *""")
 127 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted old job entries.")
 128
 129 # old linkmetadata entries
 130 cur.execute("""DELETE FROM linkmetadata WHERE timestamp < extract(epoch from (NOW() - INTERVAL '31 day'))*1000 RETURNING *""")
 131 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted old linkmetadata entries.")
 132
 133 # channelmemberhistory of left members
 134 cur.execute("""DELETE FROM channelmemberhistory WHERE leavetime < extract(epoch from (NOW() - INTERVAL '31 day'))*1000 RETURNING *""")
 135 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted old channelmemberhistory entries.")
 136
 137
 138 cur.close()
 139 print()
 140
 141
 142
 143 ####################################
 144 # Delete 'deleted'/'archived' data #
 145 ####################################
 146 print("Deleting 'deleted'/'archived' entries ...")
 147 cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
 148
 149 # posts
 150 cur.execute("""DELETE FROM posts WHERE deleteat <> 0 AND deleteat < extract(epoch from (NOW() - INTERVAL '14 day'))*1000 RETURNING *""")
 151 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 'deleted' post(s). (14 day delay for delete propagation/cache eviction)")
 152
 153 # drafts
 154 cur.execute("""DELETE FROM drafts WHERE deleteat <> 0 OR message = '' RETURNING *""")
 155 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 'deleted' draft(s).")
 156
 157 # channels
 158 cur.execute("""DELETE FROM channels WHERE deleteat <> 0 RETURNING *""")
 159 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 'deleted' channel(s).")
 160
 161 # emojis
 162 cur.execute("""DELETE FROM emoji WHERE deleteat <> 0 RETURNING *""")
 163 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 'deleted' emoji(s).")
 164
 165 # reactions - MM 5.33 started soft-deleting them :/
 166 cur.execute("""DELETE FROM reactions WHERE deleteat <> 0 RETURNING *""")
 167 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 'deleted' reaction(s).")
 168
 169 # teammembers
 170 cur.execute("""DELETE FROM teammembers WHERE deleteat <> 0 OR (schemeuser = FALSE AND schemeadmin = FALSE AND schemeguest = FALSE) RETURNING *""")
 171 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 'deleted' teammember(s).")
 172
 173 # usergroups
 174 cur.execute("""DELETE FROM usergroups WHERE deleteat <> 0 RETURNING *""")
 175 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 'deleted' usergroup(s).")
 176
 177 # usergroup members
 178 cur.execute("""DELETE FROM groupmembers WHERE deleteat <> 0 RETURNING *""")
 179 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 'deleted' usergroupmember(s).")
 180
 181 # slash commands
 182 cur.execute("""DELETE FROM commands WHERE deleteat <> 0 RETURNING *""")
 183 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 'deleted' slash command(s).")
 184
 185 # outgoing hooks
 186 cur.execute("""DELETE FROM outgoingwebhooks WHERE deleteat <> 0 RETURNING *""")
 187 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 'deleted' outgoing webhook(s).")
 188
 189 # incoming hooks
 190 cur.execute("""DELETE FROM incomingwebhooks WHERE deleteat <> 0 RETURNING *""")
 191 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted 'deleted' incoming webhook(s).")
 192
 193
 194 cur.close()
 195 print()
 196
 197
 198
 199 #######################
 200 # clean orphaned data #
 201 #######################
 202 print("Deleting orphaned entries ...")
 203 cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
 204
 205 # slash commands by team
 206 cur.execute("""DELETE FROM commands WHERE teamid NOT IN (SELECT id FROM teams) RETURNING *""")
 207 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned slash command(s).")
 208
 209 # channels by team
 210 cur.execute("""DELETE FROM channels WHERE teamid NOT IN (SELECT id FROM teams) and teamid IS DISTINCT FROM '' RETURNING *""")
 211 _ = [print(row, file=sys.stderr) for row in cur.fetchall()]
 212 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned channel(s).")
 213
 214 # empty private channels
 215 cur.execute("""DELETE FROM channels WHERE id NOT IN (SELECT channelid FROM channelmembers) AND type = 'P' RETURNING *""")
 216 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned private channel(s).")
 217
 218 # posts orphaned by channel + user
 219 cur.execute("""DELETE FROM posts WHERE channelid NOT IN (SELECT id FROM channels) OR userid NOT IN (SELECT id FROM users) RETURNING *""")
 220 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned post(s).")
 221
 222 # posts orphaned by root post (fixing my own shit; only happens because of AUTODELETE)
 223 cur.execute("""DELETE FROM posts WHERE rootid IS DISTINCT FROM '' AND rootid NOT IN (SELECT id FROM posts) RETURNING *""")
 224 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned post-comment(s).")
 225
 226 # threads orphaned by post + being emptied (by channel happens automatically because of post orphaning)
 227 cur.execute("""DELETE FROM threads WHERE postid NOT IN (SELECT id FROM posts) OR replycount = 0 RETURNING *""")
 228 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned thread(s).")
 229
 230 # threadmembers by thread + post + user
 231 cur.execute("""DELETE FROM threadmemberships WHERE postid NOT IN (SELECT postid FROM threads) OR postid NOT IN (SELECT id FROM posts) OR userid NOT IN (SELECT id FROM users) RETURNING *""")
 232 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned threadmembership(s).")
 233
 234 # reactions orphaned by post + user
 235 cur.execute("""DELETE FROM reactions WHERE postid NOT IN (SELECT id FROM posts) OR userid NOT IN (SELECT id FROM users) RETURNING *""")
 236 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned reaction(s).")
 237
 238 # postspriorities orphaned by post (by channel happens automatically because of post orphaning)
 239 cur.execute("""DELETE FROM postspriority WHERE postid NOT IN (SELECT id FROM posts) RETURNING *""")
 240 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned postspriorit(y/ies).")
 241
 242 # postacknowledgements orphaned by post + user
 243 cur.execute("""DELETE FROM postacknowledgements WHERE postid NOT IN (SELECT id FROM posts) OR userid NOT IN (SELECT id FROM users) RETURNING *""")
 244 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned postacknowledgement(s). (needed?)")
 245
 246 # channelmembers orphaned by channel + user
 247 cur.execute("""DELETE FROM channelmembers WHERE channelid NOT IN (SELECT id FROM channels) OR userid NOT IN (SELECT id FROM users) RETURNING *""")
 248 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned channelmember(s).")
 249
 250 # channelmemberhisroty orphaned by channel + user
 251 cur.execute("""DELETE FROM channelmemberhistory WHERE channelid NOT IN (SELECT id FROM channels) OR userid NOT IN (SELECT id FROM users) RETURNING *""")
 252 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned channelmemberhistory entr(y/ies).")
 253
 254 # teammembers by user
 255 cur.execute("""DELETE FROM teammembers WHERE userid NOT IN (SELECT id FROM users) RETURNING *""")
 256 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned teammember(s).")
 257
 258 # groupmembers orphanes by group + user
 259 cur.execute("""DELETE FROM groupmembers WHERE groupid NOT IN (SELECT id FROM usergroups) OR userid NOT IN (SELECT id FROM users) RETURNING *""")
 260 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned usergroupmember(s).")
 261
 262 # online-status
 263 cur.execute("""DELETE FROM status WHERE userid NOT IN (SELECT id FROM users) RETURNING *""")
 264 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned user status entr(y/ies).")
 265
 266 # sidebarcategories orphaned by team + user
 267 cur.execute("""DELETE FROM sidebarcategories WHERE teamid NOT IN (SELECT id FROM teams) OR userid NOT IN (SELECT id FROM users) RETURNING *""")
 268 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned sidebarcategor(y/ies).")
 269
 270 # sidebarchannels orphaned by channel + sidebarcategory
 271 cur.execute("""DELETE FROM sidebarchannels WHERE channelid NOT IN (SELECT id FROM channels) OR categoryid NOT IN (SELECT id FROM sidebarcategories) RETURNING *""")
 272 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned sidebarchannel entr(y/ies).")
 273
 274 # sessions by user
 275 cur.execute("""DELETE FROM sessions WHERE userid NOT IN (SELECT id FROM users) RETURNING *""")
 276 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned session(s).")
 277
 278 # filereferences (keep last)
 279 cur.execute("""DELETE FROM fileinfo WHERE id NOT IN
 280             (SELECT unnest(string_to_array(replace(replace(replace(fileids, '"', ''), ']', ''), '[', ''), ',')) FROM posts WHERE fileids IS DISTINCT FROM '[]')
 281             RETURNING *""")
 282 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned file reference(s).")
 283
 284
 285 # space for settings
 286 print()
 287
 288
 289 # orphaned preferences by user
 290 cur.execute("""DELETE FROM preferences WHERE userid NOT IN (select id FROM users) RETURNING *""")
 291 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned preference(s).")
 292
 293 # orphaned preferences: open channel info entries by user
 294 cur.execute("""DELETE FROM preferences WHERE category = 'direct_channel_show' AND name NOT IN (select id FROM users) RETURNING *""")
 295 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned direct_channel_show preference(s).")
 296
 297 # orphaned preferences: deleted viewved channel info by user
 298 cur.execute("""DELETE FROM preferences WHERE category = 'channel_approximate_view_time' AND name NOT IN (select id FROM channels union select '') RETURNING *""")
 299 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned channel_approximate_view_time preference(s).")
 300
 301 # orphaned preferences: deleted fav'ed channel info by user
 302 cur.execute("""DELETE FROM preferences WHERE category = 'favorite_channel' AND name NOT IN (select id FROM channels) RETURNING *""")
 303 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned favorite_channel preference(s).")
 304
 305 # orphaned preferences: deleted flagged posts by user
 306 cur.execute("""DELETE FROM preferences WHERE category = 'flagged_post' AND name NOT IN (select id FROM posts) RETURNING *""")
 307 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted orphaned flagged_post preference(s).")
 308
 309
 310 cur.close()
 311 print()
 312
 313
 314
 315 ################################
 316 # Soft-delete system messages. #
 317 ################################
 318 if hasattr(config, "softdel_systemspam") and config.softdel_systemspam:
 319     print("'Deleting' system messages ...")
 320     cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
 321
 322     cur.execute("""UPDATE posts SET deleteat = extract(epoch FROM(date_trunc('second',NOW())))*1000, updateat = extract(epoch FROM(date_trunc('second',NOW())))*1000
 323             WHERE (deleteat = 0 OR deleteat IS NULL) AND (
 324                 type IN ('system_join_team','system_leave_team','system_add_to_team','system_remove_from_team','system_join_channel','system_leave_channel','system_purpose_change','system_header_change','system_guest_join_channel')
 325                 OR (type IN ('system_add_to_channel','system_add_guest_to_chan') AND props::jsonb @> '{"userId":"5ugpycz1mfrj3ff4k6hbg6g37o"}'::jsonb)
 326             ) RETURNING *""")
 327     print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} 'deleted' system spam message(s).")
 328
 329
 330     cur.close()
 331 else:
 332     print("SKIPPED 'Deleting' system messages ...")
 333
 334 print()
 335
 336
 337
 338 #########################################
 339 # Soft-delete ``AUTODELETE-*`` messages #
 340 #########################################
 341 if hasattr(config, "softdel_autodelete") and config.softdel_autodelete:
 342     print("'Deleting' ``AUTODELETE-*`` messages ...")
 343     cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
 344
 345     # delete autodelete-messages by message content
 346     cur.execute("""UPDATE posts SET deleteat = extract(epoch FROM(date_trunc('second',NOW())))*1000, updateat = extract(epoch FROM(date_trunc('second',NOW())))*1000
 347         WHERE (deleteat = 0 OR deleteat IS NULL) AND ispinned = false AND createat < extract(epoch from (NOW() - INTERVAL '2 day'))*1000 AND message LIKE '``BOT-AUTODELETE-DAY``%' RETURNING *""")
 348     print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} 'deleted' old BOT-AUTODELETE-DAY message(s).")
 349     cur.execute("""UPDATE posts SET deleteat = extract(epoch FROM(date_trunc('second',NOW())))*1000, updateat = extract(epoch FROM(date_trunc('second',NOW())))*1000
 350         WHERE (deleteat = 0 OR deleteat IS NULL) AND ispinned = false AND createat < extract(epoch from (NOW() - INTERVAL '7 day'))*1000 AND message LIKE '``BOT-AUTODELETE-WEEK``%' RETURNING *""")
 351     print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} 'deleted' old BOT-AUTODELETE-WEEK message(s).")
 352     cur.execute("""UPDATE posts SET deleteat = extract(epoch FROM(date_trunc('second',NOW())))*1000, updateat = extract(epoch FROM(date_trunc('second',NOW())))*1000
 353         WHERE (deleteat = 0 OR deleteat IS NULL) AND ispinned = false AND createat < extract(epoch from (NOW() - INTERVAL '31 day'))*1000 AND message LIKE '``AUTODELETE%``%' RETURNING *""")
 354     print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} 'deleted' old BOT-AUTODELETE-MONTH message(s).")
 355
 356     # delete autodelete-messages by message content
 357     cur.execute("""UPDATE posts SET deleteat = extract(epoch FROM(date_trunc('second',NOW())))*1000, updateat = extract(epoch FROM(date_trunc('second',NOW())))*1000
 358         WHERE (deleteat = 0 OR deleteat IS NULL) AND ispinned = false AND createat < extract(epoch from (NOW() - INTERVAL '2 day'))*1000 AND message LIKE '``AUTODELETE-DAY``%' RETURNING *""")
 359     print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} 'deleted' old AUTODELETE-DAY message(s).")
 360     cur.execute("""UPDATE posts SET deleteat = extract(epoch FROM(date_trunc('second',NOW())))*1000, updateat = extract(epoch FROM(date_trunc('second',NOW())))*1000
 361         WHERE (deleteat = 0 OR deleteat IS NULL) AND ispinned = false AND createat < extract(epoch from (NOW() - INTERVAL '7 day'))*1000 AND message LIKE '``AUTODELETE-WEEK``%' RETURNING *""")
 362     print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} 'deleted' old AUTODELETE-WEEK message(s).")
 363     cur.execute("""UPDATE posts SET deleteat = extract(epoch FROM(date_trunc('second',NOW())))*1000, updateat = extract(epoch FROM(date_trunc('second',NOW())))*1000
 364         WHERE (deleteat = 0 OR deleteat IS NULL) AND ispinned = false AND createat < extract(epoch from (NOW() - INTERVAL '31 day'))*1000 AND message LIKE '``AUTODELETE%``%' RETURNING *""")
 365     print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} 'deleted' old AUTODELETE-MONTH message(s).")
 366
 367     # delete autodelete-messages by property. We need an index to make this **really** fast... # Dont!
 368     # CREATE INDEX someone_idx_posts_props_autodelete ON public.posts USING btree ((props::jsonb ->> 'somemaint_auto_delete'::text) COLLATE pg_catalog."default");
 369     cur.execute("""UPDATE posts SET deleteat = extract(epoch FROM(date_trunc('second',NOW())))*1000, updateat = extract(epoch FROM(date_trunc('second',NOW())))*1000
 370         WHERE (deleteat = 0 OR deleteat IS NULL) AND ispinned = false AND props::text LIKE '%somecleaner_autodelete%' AND createat < extract(epoch from (NOW() - INTERVAL '1 day'))*1000 AND props::jsonb ->> 'somecleaner_autodelete' = 'day' RETURNING *""")
 371     print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} 'deleted' old AUTODELETE-PROP-DAY message(s).")
 372     cur.execute("""UPDATE posts SET deleteat = extract(epoch FROM(date_trunc('second',NOW())))*1000, updateat = extract(epoch FROM(date_trunc('second',NOW())))*1000
 373         WHERE (deleteat = 0 OR deleteat IS NULL) AND ispinned = false AND props::text LIKE '%somecleaner_autodelete%' AND createat < extract(epoch from (NOW() - INTERVAL '7 day'))*1000 AND props::jsonb ->> 'somecleaner_autodelete' = 'week' RETURNING *""")
 374     print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} 'deleted' old AUTODELETE-PROP-WEEK message(s).")
 375     cur.execute("""UPDATE posts SET deleteat = extract(epoch FROM(date_trunc('second',NOW())))*1000, updateat = extract(epoch FROM(date_trunc('second',NOW())))*1000
 376         WHERE (deleteat = 0 OR deleteat IS NULL) AND ispinned = false AND props::text LIKE '%somecleaner_autodelete%' AND createat < extract(epoch from (NOW() - INTERVAL '31 day'))*1000 AND props::jsonb ? 'somecleaner_autodelete' RETURNING *""")
 377     print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} 'deleted' old AUTODELETE-PROP-MONTH message(s).")
 378
 379
 380     cur.close()
 381 else:
 382     print("SKIPPED deleting ``AUTODELETE-*`` messages ...")
 383
 384 print()
 385
 386
 387
 388 ####################################
 389 # fix self-caused-db-health issues #
 390 ####################################
 391 print("Fixing self-caused stuff ...")
 392 cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
 393
 394 # mark threads as deleted if their root post is deleted.
 395 cur.execute("""UPDATE threads SET threaddeleteat = a.deleteat FROM (SELECT id AS pid, deleteat FROM posts WHERE deleteat <> 0) AS a WHERE postid = a.pid AND (threaddeleteat = 0 OR threaddeleteat IS NULL) RETURNING *""")
 396 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} corrected thread deletion(s).")
 397
 398 # soft-delete posts that belong to a deleted post/thread.
 399 cur.execute("""WITH deleted_top_posts AS (SELECT * FROM posts WHERE deleteat <> 0) UPDATE posts SET deleteat = deleted_top_posts.deleteat FROM deleted_top_posts
 400             WHERE posts.rootid = deleted_top_posts.id AND (posts.deleteat = 0 OR posts.deleteat IS NULL) RETURNING *""")
 401 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} corrected post deletions of deleted thread(s).")
 402
 403
 404 # recalculate channel totalmsgcount(root) and lastpostat(root) because MM doesnt care about system posts AND edited posts for the counts, but always cares for timestamps... WHYYYY?!
 405 cur.execute("""UPDATE channels SET totalmsgcount = a.cnt, totalmsgcountroot = a.cntr
 406             FROM ( SELECT channels.id, COUNT(posts.id) AS cnt, SUM(CASE WHEN NOT posts.id IS NULL AND (posts.rootid = '' OR posts.rootid IS NULL) THEN 1 ELSE 0 END) AS cntr
 407                 FROM posts RIGHT JOIN channels ON (posts.channelid = channels.id AND posts.type NOT LIKE 'system_%' AND posts.originalid = '') GROUP BY channels.id ) AS a
 408             WHERE channels.id = a.id and (totalmsgcount IS DISTINCT FROM a.cnt OR totalmsgcountroot IS DISTINCT FROM a.cntr)
 409             RETURNING *""")
 410 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} updated channel totalmsgcount(root).")
 411
 412 cur.execute("""UPDATE channels SET lastpostat = a.lpts, lastrootpostat = a.lptsr
 413             FROM ( SELECT channels.id, COALESCE(MAX(posts.createat), 0) AS lpts,
 414                 COALESCE(MAX(CASE WHEN NOT posts.id IS NULL AND (posts.rootid = '' OR posts.rootid IS NULL) THEN posts.createat ELSE 0 END),channels.updateat) AS lptsr
 415                 FROM posts RIGHT JOIN channels ON (posts.channelid = channels.id) GROUP BY channels.id) AS a
 416             WHERE channels.id = a.id and (lastpostat > a.lpts OR lastrootpostat > a.lptsr)
 417             RETURNING *""")
 418 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} updated channel lastpostat(root).")
 419
 420
 421 # fix msgcount(root)/lastviewedat.
 422 cur.execute("""UPDATE channelmembers SET msgcount = totalmsgcount FROM channels WHERE channelid = channels.id AND msgcount > totalmsgcount RETURNING *""")
 423 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} corrected channelmember's msgcount.")
 424 cur.execute("""UPDATE channelmembers SET msgcountroot = totalmsgcountroot FROM channels WHERE channelid = channels.id AND msgcountroot > totalmsgcountroot RETURNING *""")
 425 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} corrected channelmember's msgcountroot.")
 426 cur.execute("""UPDATE channelmembers SET lastviewedat = lastpostat FROM channels WHERE channelid = channels.id AND lastviewedat > lastpostat RETURNING *""")
 427 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} corrected channelmember's lastviewedat.")
 428
 429
 430 cur.close()
 431 print()
 432
 433
 434
 435 ########################
 436 # fix db-health issues #
 437 ########################
 438 print("Fixing stuff ...")
 439 cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
 440
 441 # "refresh" materialized "view": publicchannels
 442 cur.execute("""TRUNCATE publicchannels""")
 443 cur.execute("""INSERT INTO publicchannels (id, deleteat, teamid, displayname, name, header, purpose) SELECT c.id, c.deleteat, c.teamid, c.displayname, c.name, c.header, c.purpose FROM channels c WHERE c.type = 'O'""")
 444 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} inserted public channel(s) into 'materialized view'.")
 445
 446
 447 # FIX: delete public channels from dm/group-channel list
 448 cur.execute("""DELETE FROM preferences WHERE category = 'group_channel_show' AND name IN (SELECT id FROM channels WHERE type NOT IN ('G','D')) RETURNING *""")
 449 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} deleted public channel group_channel_show preference(s). THIS IS A BUGFIX. SHOULD BE 0!")
 450
 451 # FIX: make guests converted to users not join any channel "as guest". github: https://github.com/mattermost/mattermost-server/issues/14821
 452 cur.execute("""UPDATE channelmembers SET schemeuser = True, schemeguest = False FROM users WHERE channelmembers.userid = users.id AND schemeguest = True AND 'system_guest' != users.roles RETURNING *""")
 453 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} corrected joins 'as guest' to channels for now-users. THIS IS A BUGFIX. SHOULD BE 0!")
 454
 455 # FIX: regenerate participants list in threads table - migration issue of old threads. github: https://github.com/mattermost/mattermost-server/issues/16320
 456 # TODO? Unknown use of said table -> wait for next thread release phase?
 457
 458 # FIX: recalculate postcount to exclude deleted posts in threads table - migration issue of old threads. github: https://github.com/mattermost/mattermost-server/issues/16321
 459 cur.execute("""UPDATE threads SET replycount = a.cnt FROM (SELECT rootid, count(*) cnt, max(createat) ts FROM posts GROUP BY 1) AS a WHERE threads.postid = a.rootid AND replycount <> a.cnt RETURNING *""")
 460 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} corrected threads replycount(s).")
 461
 462 # remove "disable_group_highlight" prop from posts.
 463 cur.execute("""UPDATE posts SET props = props::jsonb - 'disable_group_highlight' WHERE props::jsonb ? 'disable_group_highlight' RETURNING *""")
 464 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} removed 'disable_group_highlight' prop from post(s).")
 465
 466 # order channels list alphabetically again
 467 cur.execute("""UPDATE sidebarcategories SET sorting = '' WHERE type ='channels' AND sorting != '' RETURNING *""")
 468 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} type = channel sidebarcategory reverted sorting to alphabetical.")
 469 cur.execute("""DELETE FROM sidebarchannels WHERE sidebarchannels.categoryid IN (SELECT id FROM sidebarcategories WHERE type = 'channels') RETURNING *""")
 470 print(f"* [{(time.time() - TS_START):08.5f}] {cur.rowcount} removed type = channel sidebarchannels sorting information.")
 471
 472
 473 cur.close()
 474 print()
 475
 476
 477
 478 ##################################
 479 # commit changes, print db size. #
 480 ##################################
 481 if hasattr(config, "dry_run") and config.dry_run:
 482     dbconn.rollback()
 483     print("*** rollback - dry_run ***\n")
 484 else:
 485     dbconn.commit()
 486     print("*** committed ***\n")
 487
 488 cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)
 489 cur.execute("""SELECT pg_size_pretty(pg_database_size(current_database())) AS size""")
 490 print("* db-size: "+cur.fetchall()[0]["size"]+"\n")
 491
 492 dbconn.close()