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,
20 home_ou INT DEFAULT 2,
22 complete BOOL DEFAULT FALSE,
23 requesting_usr INT REFERENCES actor.usr(id) ON DELETE SET NULL
26 CREATE TABLE staging.card_stage ( -- for new library barcodes
27 row_id BIGSERIAL PRIMARY KEY,
28 row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
29 usrname TEXT NOT NULL,
30 barcode TEXT NOT NULL,
31 complete BOOL DEFAULT FALSE
34 CREATE TABLE staging.mailing_address_stage (
35 row_id BIGSERIAL PRIMARY KEY,
36 row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
37 usrname TEXT NOT NULL, -- user's SIS barcode, for linking
40 city TEXT NOT NULL DEFAULT '',
43 country TEXT NOT NULL DEFAULT 'US',
44 post_code TEXT NOT NULL,
45 complete BOOL DEFAULT FALSE
48 CREATE TABLE staging.billing_address_stage (
49 LIKE staging.mailing_address_stage INCLUDING DEFAULTS
52 ALTER TABLE staging.billing_address_stage ADD PRIMARY KEY (row_id);
54 CREATE TABLE staging.statcat_stage (
55 row_id BIGSERIAL PRIMARY KEY,
56 row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
57 usrname TEXT NOT NULL,
58 statcat TEXT NOT NULL, -- for things like 'Year of study'
59 value TEXT NOT NULL, -- and the value, such as 'Freshman'
60 complete BOOL DEFAULT FALSE
63 CREATE TABLE staging.setting_stage (
64 row_id BIGSERIAL PRIMARY KEY,
65 row_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
66 usrname TEXT NOT NULL,
67 setting TEXT NOT NULL,
69 complete BOOL DEFAULT FALSE
72 -- stored procedure for deleting expired pending patrons
73 CREATE OR REPLACE FUNCTION staging.purge_pending_users() RETURNS VOID AS $$
78 FOR org_id IN SELECT DISTINCT(home_ou) FROM staging.user_stage LOOP
80 SELECT INTO intvl value FROM
81 actor.org_unit_ancestor_setting(
82 'opac.pending_user_expire_interval', org_id);
84 CONTINUE WHEN intvl IS NULL OR intvl ILIKE 'null';
86 -- de-JSON-ify the string
87 SELECT INTO intvl TRIM(BOTH '"' FROM intvl);
89 DELETE FROM staging.user_stage
90 WHERE home_ou = org_id AND row_date + intvl::INTERVAL < NOW();