diff options
Diffstat (limited to 'util/updates/2021-10-28-username-history.sql')
-rw-r--r-- | util/updates/2021-10-28-username-history.sql | 16 |
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); |