summaryrefslogtreecommitdiff
path: root/util/updates/2021-10-28-username-history.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/updates/2021-10-28-username-history.sql')
-rw-r--r--util/updates/2021-10-28-username-history.sql16
1 files changed, 16 insertions, 0 deletions
diff --git a/util/updates/2021-10-28-username-history.sql b/util/updates/2021-10-28-username-history.sql
new file mode 100644
index 00000000..ac703fc8
--- /dev/null
+++ b/util/updates/2021-10-28-username-history.sql
@@ -0,0 +1,16 @@
+CREATE TABLE users_username_hist (
+ id vndbid NOT NULL,
+ date timestamptz NOT NULL DEFAULT NOW(),
+ old text NOT NULL,
+ new text NOT NULL,
+ PRIMARY KEY(id, date)
+);
+ALTER TABLE users_username_hist ADD CONSTRAINT users_username_hist_id_fkey FOREIGN KEY (id) REFERENCES users (id) ON DELETE CASCADE;
+\i sql/perms.sql
+
+INSERT INTO users_username_hist (id, date, old, new)
+ SELECT affected_uid, date
+ , regexp_replace(detail, 'username: "([^"]+)" -> "([^"]+)"', '\1', '') AS old
+ , regexp_replace(detail, 'username: "([^"]+)" -> "([^"]+)"', '\2', '') AS new
+ FROM audit_log
+ WHERE detail ~ 'username: "([^"]+)" -> "([^"]+)"' AND EXISTS(SELECT 1 FROM users WHERE id = affected_uid);