1 DROP SCHEMA IF EXISTS staging CASCADE;
7 CREATE TABLE staging.user_stage (
8 row_id BIGSERIAL PRIMARY KEY,
9 row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
10 usrname TEXT NOT NULL,
14 ident_type INT DEFAULT 3,
15 first_given_name TEXT,
16 second_given_name TEXT,
18 pref_first_given_name TEXT,
19 pref_second_given_name TEXT,
20 pref_family_name TEXT,
23 home_ou INT DEFAULT 2,
25 complete BOOL DEFAULT FALSE,
26 requesting_usr INT REFERENCES actor.usr(id) ON DELETE SET NULL
29 CREATE TABLE staging.card_stage ( -- for new library barcodes
30 row_id BIGSERIAL PRIMARY KEY,
31 row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
32 usrname TEXT NOT NULL,
33 barcode TEXT NOT NULL,
34 complete BOOL DEFAULT FALSE
37 CREATE TABLE staging.mailing_address_stage (
38 row_id BIGSERIAL PRIMARY KEY,
39 row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
40 usrname TEXT NOT NULL, -- user's SIS barcode, for linking
43 city TEXT NOT NULL DEFAULT '',
46 country TEXT NOT NULL DEFAULT 'US',
47 post_code TEXT NOT NULL,
48 complete BOOL DEFAULT FALSE
51 CREATE TABLE staging.billing_address_stage (
52 LIKE staging.mailing_address_stage INCLUDING DEFAULTS
55 ALTER TABLE staging.billing_address_stage ADD PRIMARY KEY (row_id);
57 CREATE TABLE staging.statcat_stage (
58 row_id BIGSERIAL PRIMARY KEY,
59 row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
60 usrname TEXT NOT NULL,
61 statcat TEXT NOT NULL, -- for things like 'Year of study'
62 value TEXT NOT NULL, -- and the value, such as 'Freshman'
63 complete BOOL DEFAULT FALSE
66 CREATE TABLE staging.setting_stage (
67 row_id BIGSERIAL PRIMARY KEY,
68 row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
69 usrname TEXT NOT NULL,
70 setting TEXT NOT NULL,
72 complete BOOL DEFAULT FALSE
75 -- stored procedure for deleting expired pending patrons
76 CREATE OR REPLACE FUNCTION staging.purge_pending_users() RETURNS VOID AS $$
81 FOR org_id IN SELECT DISTINCT(home_ou) FROM staging.user_stage LOOP
83 SELECT INTO intvl value FROM
84 actor.org_unit_ancestor_setting(
85 'opac.pending_user_expire_interval', org_id);
87 CONTINUE WHEN intvl IS NULL OR intvl ILIKE 'null';
89 -- de-JSON-ify the string
90 SELECT INTO intvl TRIM(BOTH '"' FROM intvl);
92 DELETE FROM staging.user_stage
93 WHERE home_ou = org_id AND row_date + intvl::INTERVAL < NOW();