summaryrefslogtreecommitdiff
path: root/util/updates/2021-10-28-username-history.sql
blob: ac703fc808dce45d4405baff7095451cded6254d (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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);