-
Notifications
You must be signed in to change notification settings - Fork 19
/
0050.sql
43 lines (38 loc) · 1.24 KB
/
0050.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- This script enables the migration of ag.ag_login into the new schema for
-- accounts.
-- Build update triggers for setting timestamps
CREATE OR REPLACE FUNCTION update_trigger()
RETURNS TRIGGER AS $$
BEGIN
NEW.update_time = current_timestamp;
RETURN NEW;
END
$$ language 'plpgsql';
ALTER TABLE ag.ag_login RENAME TO ag_login_backup;
-- create table for individual accounts. Migrated accounts will use
-- the ag_login_id as their id.
CREATE TABLE ag.account (
id uuid PRIMARY KEY NOT NULL,
email varchar,
account_type varchar NOT NULL,
auth_issuer varchar,
auth_sub varchar,
first_name varchar,
last_name varchar,
street varchar,
city varchar,
state varchar,
post_code varchar,
country_code varchar,
latitude double precision,
longitude double precision,
cannot_geocode character(1),
elevation double precision,
creation_time timestamptz default current_timestamp,
update_time timestamptz default current_timestamp
);
CREATE UNIQUE INDEX idx_account_email ON ag.account ( email );
CREATE UNIQUE INDEX idx_account_issuer_sub ON ag.account
( auth_issuer, auth_sub );
CREATE TRIGGER update_account_trigger BEFORE UPDATE ON account FOR EACH ROW
EXECUTE PROCEDURE update_trigger();