Dumped on 2009-10-12

Index of database - evergreen


Schema acq


Table: acq.currency_type

acq.currency_type Structure
F-Key Name Type Description
code text PRIMARY KEY
label text

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.distribution_formula

acq.distribution_formula Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
skip_count integer NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.distribution_formula_entry

acq.distribution_formula_entry Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.distribution_formula.id formula integer UNIQUE#1 NOT NULL
position integer UNIQUE#1 NOT NULL
item_count integer NOT NULL
actor.org_unit.id owning_lib integer
asset.copy_location.id location integer

 

acq.distribution_formula_entry Constraints
Name Constraint
acqdfe_must_be_somewhere CHECK (((owning_lib IS NOT NULL) OR (location IS NOT NULL)))

Index - Schema acq


Table: acq.exchange_rate

acq.exchange_rate Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.currency_type.code from_currency text UNIQUE#1 NOT NULL
acq.currency_type.code to_currency text UNIQUE#1 NOT NULL
ratio numeric NOT NULL

Index - Schema acq


Table: acq.fund

acq.fund Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id org integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
year integer UNIQUE#1 NOT NULL DEFAULT date_part('year'::text, now())
acq.currency_type.code currency_type text NOT NULL
code text UNIQUE

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.fund_allocation

acq.fund_allocation Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.funding_source.id funding_source integer NOT NULL
acq.fund.id fund integer NOT NULL
amount numeric
percent numeric
actor.usr.id allocator integer NOT NULL
note text
create_time timestamp with time zone NOT NULL DEFAULT now()

 

acq.fund_allocation Constraints
Name Constraint
allocation_amount_or_percent CHECK ((((percent IS NULL) AND (amount IS NOT NULL)) OR ((percent IS NOT NULL) AND (amount IS NULL))))
fund_allocation_percent_check CHECK (((percent IS NULL) OR ((percent >= 0.0) AND (percent <= 100.0))))

Index - Schema acq


View: acq.fund_allocation_total

acq.fund_allocation_total Structure
F-Key Name Type Description
fund integer
amount numeric(100,2)
SELECT x.fund
, (sum
     (x.amount)
)::numeric
(100
     ,2
) AS amount 
FROM (
SELECT a.fund
     , (sum
           (
                 (a.amount * acq.exchange_ratio
                       (s.currency_type
                             , f.currency_type
                       )
                 )
           )
     )::numeric
     (100
           ,2
     ) AS amount 
  FROM (
           (acq.fund_allocation a 
              JOIN acq.fund f 
                ON (
                       (a.fund = f.id)
                 )
           )
        JOIN acq.funding_source s 
          ON (
                 (a.funding_source = s.id)
           )
     )
 WHERE (a.percent IS NULL)
GROUP BY a.fund 
UNION ALLSELECT a.fund
     , (sum
           (
                 (
                       (
                             (
                              SELECT sum
                                   (c.amount) AS sum 
                                FROM acq.funding_source_credit c 
                               WHERE (c.funding_source = a.funding_source)
                             ) * acq.exchange_ratio
                             (s.currency_type
                                   , f.currency_type
                             )
                       ) * 
                       (a.percent / 100.0)
                 )
           )
     )::numeric
     (100
           ,2
     ) AS amount 
  FROM (
           (acq.fund_allocation a 
              JOIN acq.fund f 
                ON (
                       (a.fund = f.id)
                 )
           )
        JOIN acq.funding_source s 
          ON (
                 (a.funding_source = s.id)
           )
     )
 WHERE (a.amount IS NULL)
GROUP BY a.fund
) x 
GROUP BY x.fund;

Index - Schema acq


View: acq.fund_combined_balance

acq.fund_combined_balance Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT c.fund
, (c.amount - COALESCE
     (d.amount
           , 0.0
     )
) AS amount 
FROM (acq.fund_allocation_total c 
LEFT JOIN acq.fund_debit_total d 
 USING (fund)
);

Index - Schema acq


Table: acq.fund_debit

acq.fund_debit Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.fund.id fund integer NOT NULL
origin_amount numeric NOT NULL
acq.currency_type.code origin_currency_type text NOT NULL
amount numeric NOT NULL
encumbrance boolean NOT NULL DEFAULT true
debit_type text NOT NULL
acq.fund.id xfer_destination integer
create_time timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


View: acq.fund_debit_total

acq.fund_debit_total Structure
F-Key Name Type Description
fund integer
encumbrance boolean
amount numeric
SELECT fund_debit.id AS fund
, fund_debit.encumbrance
, sum
(fund_debit.amount) AS amount 
FROM acq.fund_debit 
GROUP BY fund_debit.id
, fund_debit.encumbrance;

Index - Schema acq


View: acq.fund_encumbrance_total

acq.fund_encumbrance_total Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT fund_debit_total.fund
, sum
(fund_debit_total.amount) AS amount 
FROM acq.fund_debit_total 
WHERE (fund_debit_total.encumbrance IS TRUE)
GROUP BY fund_debit_total.fund;

Index - Schema acq


View: acq.fund_spent_balance

acq.fund_spent_balance Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT c.fund
, (c.amount - COALESCE
     (d.amount
           , 0.0
     )
) AS amount 
FROM (acq.fund_allocation_total c 
LEFT JOIN acq.fund_spent_total d 
 USING (fund)
);

Index - Schema acq


View: acq.fund_spent_total

acq.fund_spent_total Structure
F-Key Name Type Description
fund integer
amount numeric
SELECT fund_debit_total.fund
, sum
(fund_debit_total.amount) AS amount 
FROM acq.fund_debit_total 
WHERE (fund_debit_total.encumbrance IS FALSE)
GROUP BY fund_debit_total.fund;

Index - Schema acq


Table: acq.fund_tag

acq.fund_tag Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.fund_tag_map

acq.fund_tag_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.fund.id fund integer UNIQUE#1 NOT NULL
acq.fund_tag.id tag integer UNIQUE#1

Index - Schema acq


Table: acq.funding_source

acq.funding_source Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE#1 NOT NULL
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
acq.currency_type.code currency_type text NOT NULL
code text UNIQUE

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


View: acq.funding_source_allocation_total

acq.funding_source_allocation_total Structure
F-Key Name Type Description
funding_source integer
amount numeric(100,2)
SELECT x.funding_source
, (sum
     (x.amount)
)::numeric
(100
     ,2
) AS amount 
FROM (
SELECT a.funding_source
     , (sum
           (a.amount)
     )::numeric
     (100
           ,2
     ) AS amount 
  FROM acq.fund_allocation a 
 WHERE (a.percent IS NULL)
GROUP BY a.funding_source 
UNION ALLSELECT a.funding_source
     , (sum
           (
                 (
                       (
                        SELECT sum
                             (c.amount) AS sum 
                          FROM acq.funding_source_credit c 
                         WHERE (c.funding_source = a.funding_source)
                       ) * 
                       (a.percent / 100.0)
                 )
           )
     )::numeric
     (100
           ,2
     ) AS amount 
  FROM acq.fund_allocation a 
 WHERE (a.amount IS NULL)
GROUP BY a.funding_source
) x 
GROUP BY x.funding_source;

Index - Schema acq


View: acq.funding_source_balance

acq.funding_source_balance Structure
F-Key Name Type Description
funding_source integer
amount numeric(100,2)
SELECT COALESCE
(c.funding_source
     , a.funding_source
) AS funding_source
, (sum
     (
           (COALESCE
                 (c.amount
                       , 0.0
                 ) - COALESCE
                 (a.amount
                       , 0.0
                 )
           )
     )
)::numeric
(100
     ,2
) AS amount 
FROM (acq.funding_source_credit_total c FULL 
  JOIN acq.funding_source_allocation_total a 
 USING (funding_source)
)
GROUP BY COALESCE
(c.funding_source
     , a.funding_source
);

Index - Schema acq


Table: acq.funding_source_credit

acq.funding_source_credit Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.funding_source.id funding_source integer NOT NULL
amount numeric NOT NULL
note text

Index - Schema acq


View: acq.funding_source_credit_total

acq.funding_source_credit_total Structure
F-Key Name Type Description
funding_source integer
amount numeric
SELECT funding_source_credit.funding_source
, sum
(funding_source_credit.amount) AS amount 
FROM acq.funding_source_credit 
GROUP BY funding_source_credit.funding_source;

Index - Schema acq


Table: acq.lineitem

acq.lineitem Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id creator integer NOT NULL
actor.usr.id editor integer NOT NULL
actor.usr.id selector integer NOT NULL
acq.provider.id provider integer
acq.purchase_order.id purchase_order integer
acq.picklist.id picklist integer
expected_recv_time timestamp with time zone
create_time timestamp with time zone NOT NULL DEFAULT now()
edit_time timestamp with time zone NOT NULL DEFAULT now()
marc text NOT NULL
biblio.record_entry.id eg_bib_id integer
source_label text
item_count integer NOT NULL
state text NOT NULL DEFAULT 'new'::text

 

acq.lineitem Constraints
Name Constraint
picklist_or_po CHECK (((picklist IS NOT NULL) OR (purchase_order IS NOT NULL)))

Tables referencing this one via Foreign Key Constraints:

li_pl_idx picklist li_po_idx purchase_order

Index - Schema acq


Table: acq.lineitem_attr

acq.lineitem_attr Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
definition bigint NOT NULL
acq.lineitem.id lineitem bigint NOT NULL
attr_type text NOT NULL
attr_name text NOT NULL
attr_value text NOT NULL
li_attr_definition_idx definition li_attr_li_idx lineitem li_attr_value_idx attr_value

Index - Schema acq


Table: acq.lineitem_attr_definition

acq.lineitem_attr_definition Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
code text NOT NULL
description text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false

Index - Schema acq


Table: acq.lineitem_detail

acq.lineitem_detail Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
acq.lineitem.id lineitem integer NOT NULL
acq.fund.id fund integer
acq.fund_debit.id fund_debit integer
asset.copy.id eg_copy_id bigint
barcode text
cn_label text
note text
collection_code text
config.circ_modifier.code circ_modifier text
actor.org_unit.id owning_lib integer
asset.copy_location.id location integer
recv_time timestamp with time zone
li_detail_li_idx lineitem

Index - Schema acq


Table: acq.lineitem_generated_attr_definition

acq.lineitem_generated_attr_definition Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('acq.lineitem_attr_definition_id_seq'::regclass)
code text NOT NULL
description text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false
xpath text NOT NULL

Table acq.lineitem_generated_attr_definition Inherits lineitem_attr_definition,

Index - Schema acq


Table: acq.lineitem_local_attr_definition

acq.lineitem_local_attr_definition Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('acq.lineitem_attr_definition_id_seq'::regclass)
code text NOT NULL
description text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false

Table acq.lineitem_local_attr_definition Inherits lineitem_attr_definition,

Index - Schema acq


Table: acq.lineitem_marc_attr_definition

acq.lineitem_marc_attr_definition Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('acq.lineitem_attr_definition_id_seq'::regclass)
code text NOT NULL
description text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false
xpath text NOT NULL

Table acq.lineitem_marc_attr_definition Inherits lineitem_attr_definition,

Index - Schema acq


Table: acq.lineitem_note

acq.lineitem_note Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.lineitem.id lineitem integer NOT NULL
actor.usr.id creator integer NOT NULL
actor.usr.id editor integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
edit_time timestamp with time zone NOT NULL DEFAULT now()
value text NOT NULL
li_note_li_idx lineitem

Index - Schema acq


Table: acq.lineitem_provider_attr_definition

acq.lineitem_provider_attr_definition Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('acq.lineitem_attr_definition_id_seq'::regclass)
code text NOT NULL
description text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false
xpath text NOT NULL
acq.provider.id provider integer NOT NULL

Table acq.lineitem_provider_attr_definition Inherits lineitem_attr_definition,

Index - Schema acq


Table: acq.lineitem_usr_attr_definition

acq.lineitem_usr_attr_definition Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('acq.lineitem_attr_definition_id_seq'::regclass)
code text NOT NULL
description text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false
actor.usr.id usr integer NOT NULL

Table acq.lineitem_usr_attr_definition Inherits lineitem_attr_definition,

Index - Schema acq


Table: acq.picklist

acq.picklist Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id owner integer UNIQUE#1 NOT NULL
actor.usr.id creator integer NOT NULL
actor.usr.id editor integer NOT NULL
actor.org_unit.id org_unit integer NOT NULL
name text UNIQUE#1 NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
edit_time timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.po_note

acq.po_note Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.purchase_order.id purchase_order integer NOT NULL
actor.usr.id creator integer NOT NULL
actor.usr.id editor integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
edit_time timestamp with time zone NOT NULL DEFAULT now()
value text NOT NULL
po_note_po_idx purchase_order

Index - Schema acq


Table: acq.provider

acq.provider Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE#1 NOT NULL
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
acq.currency_type.code currency_type text NOT NULL
code text UNIQUE
holding_tag text

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.provider_address

acq.provider_address Structure
F-Key Name Type Description
id serial PRIMARY KEY
valid boolean NOT NULL DEFAULT true
address_type text
acq.provider.id provider integer NOT NULL
street1 text NOT NULL
street2 text
city text NOT NULL
county text
state text NOT NULL
country text NOT NULL
post_code text NOT NULL

Index - Schema acq


Table: acq.provider_contact

acq.provider_contact Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.provider.id provider integer NOT NULL
name text
role text
email text
phone text

Tables referencing this one via Foreign Key Constraints:

Index - Schema acq


Table: acq.provider_contact_address

acq.provider_contact_address Structure
F-Key Name Type Description
id serial PRIMARY KEY
valid boolean NOT NULL DEFAULT true
address_type text
acq.provider_contact.id contact integer NOT NULL
street1 text NOT NULL
street2 text
city text NOT NULL
county text
state text NOT NULL
country text NOT NULL
post_code text NOT NULL

Index - Schema acq


Table: acq.provider_holding_subfield_map

acq.provider_holding_subfield_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
acq.provider.id provider integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
subfield text NOT NULL

Index - Schema acq


Table: acq.purchase_order

acq.purchase_order Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id owner integer NOT NULL
actor.usr.id creator integer NOT NULL
actor.usr.id editor integer NOT NULL
actor.org_unit.id ordering_agency integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
edit_time timestamp with time zone NOT NULL DEFAULT now()
acq.provider.id provider integer NOT NULL
state text NOT NULL DEFAULT 'new'::text

Tables referencing this one via Foreign Key Constraints:

po_owner_idx owner po_provider_idx provider po_state_idx state

Index - Schema acq


Function: acq.exchange_ratio(text, text, numeric)

Returns: numeric

Language: SQL

    SELECT $3 * acq.exchange_ratio($1, $2);

Function: acq.exchange_ratio(to_ex text, from_ex text)

Returns: numeric

Language: PLPGSQL

DECLARE
    rat NUMERIC;
BEGIN
    IF from_ex = to_ex THEN
        RETURN 1.0;
    END IF;

    SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;

    IF FOUND THEN
        RETURN rat;
    ELSE
        SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
        IF FOUND THEN
            RETURN 1.0/rat;
        END IF;
    END IF;

    RETURN NULL;

END;

Function: acq.extract_holding_attr_table(tag integer, lineitem text)

Returns: SET OF flat_lineitem_holding_subfield

Language: PLPGSQL

DECLARE
    counter INT;
    lida    acq.flat_lineitem_holding_subfield%ROWTYPE;
BEGIN

    SELECT  COUNT(*) INTO counter
      FROM  xpath_table(
                'id',
                'marc',
                'acq.lineitem',
                '//*[@tag="' || tag || '"]',
                'id=' || lineitem
            ) as t(i int,c text);

    FOR i IN 1 .. counter LOOP
        FOR lida IN
            SELECT  * 
              FROM  (   SELECT  id,i,t,v
                          FROM  xpath_table(
                                    'id',
                                    'marc',
                                    'acq.lineitem',
                                    '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
                                        '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
                                    'id=' || lineitem
                                ) as t(id int,t text,v text)
                    )x
        LOOP
            RETURN NEXT lida;
        END LOOP;
    END LOOP;

    RETURN;
END;

Function: acq.extract_provider_holding_data(lineitem_i integer)

Returns: SET OF flat_lineitem_detail

Language: PLPGSQL

DECLARE
    prov_i  INT;
    tag_t   TEXT;
    lida    acq.flat_lineitem_detail%ROWTYPE;
BEGIN
    SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
    IF NOT FOUND THEN RETURN; END IF;

    SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
    IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;

    FOR lida IN
        SELECT  lineitem_i,
                h.holding,
                a.name,
                h.data
          FROM  acq.extract_holding_attr_table( lineitem_i, tag_t ) h
                JOIN acq.provider_holding_subfield_map a USING (subfield)
          WHERE a.provider = prov_i
    LOOP
        RETURN NEXT lida;
    END LOOP;

    RETURN;
END;

Schema action


Table: action.aged_circulation

action.aged_circulation Structure
F-Key Name Type Description
usr_post_code text
usr_home_ou integer NOT NULL
usr_profile integer NOT NULL
usr_birth_year integer
copy_call_number integer NOT NULL
copy_location integer NOT NULL
copy_owning_lib integer NOT NULL
copy_circ_lib integer NOT NULL
copy_bib_record bigint NOT NULL
id bigint PRIMARY KEY
xact_start timestamp with time zone NOT NULL
xact_finish timestamp with time zone
unrecovered boolean
target_copy bigint NOT NULL
circ_lib integer NOT NULL
circ_staff integer NOT NULL
checkin_staff integer
checkin_lib integer
renewal_remaining integer NOT NULL
due_date timestamp with time zone
stop_fines_time timestamp with time zone
checkin_time timestamp with time zone
create_time timestamp with time zone NOT NULL
duration interval
fine_interval interval NOT NULL
recuring_fine numeric(6,2)
max_fine numeric(6,2)
phone_renewal boolean NOT NULL
desk_renewal boolean NOT NULL
opac_renewal boolean NOT NULL
duration_rule text NOT NULL
recuring_fine_rule text NOT NULL
max_fine_rule text NOT NULL
stop_fines text
aged_circ_circ_lib_idx circ_lib aged_circ_copy_circ_lib_idx copy_circ_lib aged_circ_copy_location_idx copy_location aged_circ_copy_owning_lib_idx copy_owning_lib aged_circ_start_idx xact_start

Index - Schema action


View: action.all_circulation

action.all_circulation Structure
F-Key Name Type Description
id bigint
usr_post_code text
usr_home_ou integer
usr_profile integer
usr_birth_year integer
copy_call_number bigint
copy_location integer
copy_owning_lib integer
copy_circ_lib integer
copy_bib_record bigint
xact_start timestamp with time zone
xact_finish timestamp with time zone
target_copy bigint
circ_lib integer
circ_staff integer
checkin_staff integer
checkin_lib integer
renewal_remaining integer
due_date timestamp with time zone
stop_fines_time timestamp with time zone
checkin_time timestamp with time zone
create_time timestamp with time zone
duration interval
fine_interval interval
recuring_fine numeric(6,2)
max_fine numeric(6,2)
phone_renewal boolean
desk_renewal boolean
opac_renewal boolean
duration_rule text
recuring_fine_rule text
max_fine_rule text
stop_fines text
SELECT aged_circulation.id
, aged_circulation.usr_post_code
, aged_circulation.usr_home_ou
, aged_circulation.usr_profile
, aged_circulation.usr_birth_year
, aged_circulation.copy_call_number
, aged_circulation.copy_location
, aged_circulation.copy_owning_lib
, aged_circulation.copy_circ_lib
, aged_circulation.copy_bib_record
, aged_circulation.xact_start
, aged_circulation.xact_finish
, aged_circulation.target_copy
, aged_circulation.circ_lib
, aged_circulation.circ_staff
, aged_circulation.checkin_staff
, aged_circulation.checkin_lib
, aged_circulation.renewal_remaining
, aged_circulation.due_date
, aged_circulation.stop_fines_time
, aged_circulation.checkin_time
, aged_circulation.create_time
, aged_circulation.duration
, aged_circulation.fine_interval
, aged_circulation.recuring_fine
, aged_circulation.max_fine
, aged_circulation.phone_renewal
, aged_circulation.desk_renewal
, aged_circulation.opac_renewal
, aged_circulation.duration_rule
, aged_circulation.recuring_fine_rule
, aged_circulation.max_fine_rule
, aged_circulation.stop_fines 
FROM action.aged_circulation 
UNION ALL (
SELECT DISTINCT circ.id
     , COALESCE
     (a.post_code
           , b.post_code
     ) AS usr_post_code
     , p.home_ou AS usr_home_ou
     , p.profile AS usr_profile
     , (date_part
           ('year'::text
                 , p.dob
           )
     )::integer AS usr_birth_year
     , cp.call_number AS copy_call_number
     , cp.location AS copy_location
     , cn.owning_lib AS copy_owning_lib
     , cp.circ_lib AS copy_circ_lib
     , cn.record AS copy_bib_record
     , circ.xact_start
     , circ.xact_finish
     , circ.target_copy
     , circ.circ_lib
     , circ.circ_staff
     , circ.checkin_staff
     , circ.checkin_lib
     , circ.renewal_remaining
     , circ.due_date
     , circ.stop_fines_time
     , circ.checkin_time
     , circ.create_time
     , circ.duration
     , circ.fine_interval
     , circ.recuring_fine
     , circ.max_fine
     , circ.phone_renewal
     , circ.desk_renewal
     , circ.opac_renewal
     , circ.duration_rule
     , circ.recuring_fine_rule
     , circ.max_fine_rule
     , circ.stop_fines 
  FROM (
           (
                 (
                       (
                             (action.circulation circ 
                                JOIN asset.copy cp 
                                  ON (
                                         (circ.target_copy = cp.id)
                                   )
                             )
                          JOIN asset.call_number cn 
                            ON (
                                   (cp.call_number = cn.id)
                             )
                       )
                    JOIN actor.usr p 
                      ON (
                             (circ.usr = p.id)
                       )
                 )
         LEFT JOIN actor.usr_address a 
                ON (
                       (p.mailing_address = a.id)
                 )
           )
   LEFT JOIN actor.usr_address b 
          ON (
                 (p.billing_address = a.id)
           )
     )
ORDER BY circ.id
     , COALESCE
     (a.post_code
           , b.post_code
     )
     , p.home_ou
     , p.profile
     , (date_part
           ('year'::text
                 , p.dob
           )
     )::integer
     , cp.call_number
     , cp.location
     , cn.owning_lib
     , cp.circ_lib
     , cn.record
     , circ.xact_start
     , circ.xact_finish
     , circ.target_copy
     , circ.circ_lib
     , circ.circ_staff
     , circ.checkin_staff
     , circ.checkin_lib
     , circ.renewal_remaining
     , circ.due_date
     , circ.stop_fines_time
     , circ.checkin_time
     , circ.create_time
     , circ.duration
     , circ.fine_interval
     , circ.recuring_fine
     , circ.max_fine
     , circ.phone_renewal
     , circ.desk_renewal
     , circ.opac_renewal
     , circ.duration_rule
     , circ.recuring_fine_rule
     , circ.max_fine_rule
     , circ.stop_fines
);

Index - Schema action


View: action.billable_circulations

action.billable_circulations Structure
F-Key Name Type Description
id bigint
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
unrecovered boolean
target_copy bigint
circ_lib integer
circ_staff integer
checkin_staff integer
checkin_lib integer
renewal_remaining integer
due_date timestamp with time zone
stop_fines_time timestamp with time zone
checkin_time timestamp with time zone
create_time timestamp with time zone
duration interval
fine_interval interval
recuring_fine numeric(6,2)
max_fine numeric(6,2)
phone_renewal boolean
desk_renewal boolean
opac_renewal boolean
duration_rule text
recuring_fine_rule text
max_fine_rule text
stop_fines text
SELECT circulation.id
, circulation.usr
, circulation.xact_start
, circulation.xact_finish
, circulation.unrecovered
, circulation.target_copy
, circulation.circ_lib
, circulation.circ_staff
, circulation.checkin_staff
, circulation.checkin_lib
, circulation.renewal_remaining
, circulation.due_date
, circulation.stop_fines_time
, circulation.checkin_time
, circulation.create_time
, circulation.duration
, circulation.fine_interval
, circulation.recuring_fine
, circulation.max_fine
, circulation.phone_renewal
, circulation.desk_renewal
, circulation.opac_renewal
, circulation.duration_rule
, circulation.recuring_fine_rule
, circulation.max_fine_rule
, circulation.stop_fines 
FROM action.circulation 
WHERE (circulation.xact_finish IS NULL);

Index - Schema action


Table: action.circulation

action.circulation Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('money.billable_xact_id_seq'::regclass)
actor.usr.id usr integer NOT NULL
xact_start timestamp with time zone NOT NULL DEFAULT now()
xact_finish timestamp with time zone
unrecovered boolean
asset.copy.id target_copy bigint NOT NULL
actor.org_unit.id circ_lib integer NOT NULL
circ_staff integer NOT NULL
checkin_staff integer
checkin_lib integer
renewal_remaining integer NOT NULL
due_date timestamp with time zone
stop_fines_time timestamp with time zone
checkin_time timestamp with time zone
create_time timestamp with time zone NOT NULL DEFAULT now()
duration interval
fine_interval interval NOT NULL DEFAULT '1 day'::interval
recuring_fine numeric(6,2)
max_fine numeric(6,2)
phone_renewal boolean NOT NULL DEFAULT false
desk_renewal boolean NOT NULL DEFAULT false
opac_renewal boolean NOT NULL DEFAULT false
duration_rule text NOT NULL
recuring_fine_rule text NOT NULL
max_fine_rule text NOT NULL
stop_fines text

Table action.circulation Inherits billable_xact,

 

action.circulation Constraints
Name Constraint
circulation_stop_fines_check CHECK ((stop_fines = ANY (ARRAY['CHECKIN'::text, 'CLAIMSRETURNED'::text, 'LOST'::text, 'MAXFINES'::text, 'RENEW'::text, 'LONGOVERDUE'::text])))
circ_checkin_time checkin_time) WHERE (checkin_time IS NOT NULL circ_circ_lib_idx circ_lib circ_open_date_idx xact_start) WHERE (xact_finish IS NULL circ_open_xacts_idx usr) WHERE (xact_finish IS NULL circ_outstanding_idx usr) WHERE (checkin_time IS NULL

Index - Schema action


Table: action.hold_copy_map

action.hold_copy_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
action.hold_request.id hold integer UNIQUE#1 NOT NULL
asset.copy.id target_copy bigint UNIQUE#1 NOT NULL
acm_copy_idx target_copy

Index - Schema action


Table: action.hold_notification

action.hold_notification Structure
F-Key Name Type Description
id serial PRIMARY KEY
action.hold_request.id hold integer NOT NULL
actor.usr.id notify_staff integer
notify_time timestamp with time zone NOT NULL DEFAULT now()
method text NOT NULL
note text
ahn_hold_idx hold

Index - Schema action


Table: action.hold_request

action.hold_request Structure
F-Key Name Type Description
id serial PRIMARY KEY
request_time timestamp with time zone NOT NULL DEFAULT now()
capture_time timestamp with time zone
fulfillment_time timestamp with time zone
checkin_time timestamp with time zone
return_time timestamp with time zone
prev_check_time timestamp with time zone
expire_time timestamp with time zone
cancel_time timestamp with time zone
action.hold_request_cancel_cause.id cancel_cause integer
cancel_note text
target bigint NOT NULL
asset.copy.id current_copy bigint
actor.usr.id fulfillment_staff integer
actor.org_unit.id fulfillment_lib integer
actor.org_unit.id request_lib integer NOT NULL
actor.usr.id requestor integer NOT NULL
actor.usr.id usr integer NOT NULL
selection_ou integer NOT NULL
selection_depth integer NOT NULL
actor.org_unit.id pickup_lib integer NOT NULL
hold_type text NOT NULL
holdable_formats text
phone_notify text
email_notify boolean NOT NULL DEFAULT true
frozen boolean NOT NULL DEFAULT false
thaw_date timestamp with time zone

 

action.hold_request Constraints
Name Constraint
hold_request_hold_type_check CHECK ((hold_type = ANY (ARRAY['M'::text, 'T'::text, 'V'::text, 'C'::text])))

Tables referencing this one via Foreign Key Constraints:

hold_request_current_copy_idx current_copy hold_request_pickup_lib_idx pickup_lib hold_request_prev_check_time_idx prev_check_time hold_request_target_idx target hold_request_usr_idx usr

Index - Schema action


Table: action.hold_request_cancel_cause

action.hold_request_cancel_cause Structure
F-Key Name Type Description
id serial PRIMARY KEY
label text UNIQUE

Tables referencing this one via Foreign Key Constraints:

Index - Schema action


Table: action.hold_transit_copy

action.hold_transit_copy Structure
F-Key Name Type Description
id integer PRIMARY KEY DEFAULT nextval('action.transit_copy_id_seq'::regclass)
source_send_time timestamp with time zone
dest_recv_time timestamp with time zone
asset.copy.id target_copy bigint NOT NULL
source integer NOT NULL
dest integer NOT NULL
prev_hop integer
copy_status integer NOT NULL
persistant_transfer boolean NOT NULL DEFAULT false
action.hold_request.id hold integer

Table action.hold_transit_copy Inherits transit_copy,

active_hold_transit_cp_idx target_copy active_hold_transit_dest_idx dest active_hold_transit_source_idx source

Index - Schema action


Table: action.in_house_use

action.in_house_use Structure
F-Key Name Type Description
id serial PRIMARY KEY
asset.copy.id item bigint NOT NULL
actor.usr.id staff integer NOT NULL
actor.org_unit.id org_unit integer NOT NULL
use_time timestamp with time zone NOT NULL DEFAULT now()

Index - Schema action


Table: action.non_cat_in_house_use

action.non_cat_in_house_use Structure
F-Key Name Type Description
id serial PRIMARY KEY
config.non_cataloged_type.id item_type bigint NOT NULL
actor.usr.id staff integer NOT NULL
actor.org_unit.id org_unit integer NOT NULL
use_time timestamp with time zone NOT NULL DEFAULT now()

Index - Schema action


Table: action.non_cataloged_circulation

action.non_cataloged_circulation Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id patron integer NOT NULL
actor.usr.id staff integer NOT NULL
actor.org_unit.id circ_lib integer NOT NULL
config.non_cataloged_type.id item_type integer NOT NULL
circ_time timestamp with time zone NOT NULL DEFAULT now()

Index - Schema action


View: action.open_circulation

action.open_circulation Structure
F-Key Name Type Description
id bigint
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
unrecovered boolean
target_copy bigint
circ_lib integer
circ_staff integer
checkin_staff integer
checkin_lib integer
renewal_remaining integer
due_date timestamp with time zone
stop_fines_time timestamp with time zone
checkin_time timestamp with time zone
create_time timestamp with time zone
duration interval
fine_interval interval
recuring_fine numeric(6,2)
max_fine numeric(6,2)
phone_renewal boolean
desk_renewal boolean
opac_renewal boolean
duration_rule text
recuring_fine_rule text
max_fine_rule text
stop_fines text
SELECT circulation.id
, circulation.usr
, circulation.xact_start
, circulation.xact_finish
, circulation.unrecovered
, circulation.target_copy
, circulation.circ_lib
, circulation.circ_staff
, circulation.checkin_staff
, circulation.checkin_lib
, circulation.renewal_remaining
, circulation.due_date
, circulation.stop_fines_time
, circulation.checkin_time
, circulation.create_time
, circulation.duration
, circulation.fine_interval
, circulation.recuring_fine
, circulation.max_fine
, circulation.phone_renewal
, circulation.desk_renewal
, circulation.opac_renewal
, circulation.duration_rule
, circulation.recuring_fine_rule
, circulation.max_fine_rule
, circulation.stop_fines 
FROM action.circulation 
WHERE (circulation.checkin_time IS NULL)
ORDER BY circulation.due_date;

Index - Schema action


Table: action.survey

action.survey Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owner integer NOT NULL
start_date timestamp with time zone NOT NULL DEFAULT now()
end_date timestamp with time zone NOT NULL DEFAULT (now() + '10 years'::interval)
usr_summary boolean NOT NULL DEFAULT false
opac boolean NOT NULL DEFAULT false
poll boolean NOT NULL DEFAULT false
required boolean NOT NULL DEFAULT false
name text NOT NULL
description text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema action


Table: action.survey_answer

action.survey_answer Structure
F-Key Name Type Description
id serial PRIMARY KEY
action.survey_question.id question integer NOT NULL
answer text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema action


Table: action.survey_question

action.survey_question Structure
F-Key Name Type Description
id serial PRIMARY KEY
action.survey.id survey integer NOT NULL
question text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema action


Table: action.survey_response

action.survey_response Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
response_group_id integer
usr integer
action.survey.id survey integer NOT NULL
action.survey_question.id question integer NOT NULL
action.survey_answer.id answer integer NOT NULL
answer_date timestamp with time zone
effective_date timestamp with time zone NOT NULL DEFAULT now()

Index - Schema action


Table: action.transit_copy

action.transit_copy Structure
F-Key Name Type Description
id serial PRIMARY KEY
source_send_time timestamp with time zone
dest_recv_time timestamp with time zone
asset.copy.id target_copy bigint NOT NULL
actor.org_unit.id source integer NOT NULL
actor.org_unit.id dest integer NOT NULL
action.transit_copy.id prev_hop integer
config.copy_status.id copy_status integer NOT NULL
persistant_transfer boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

active_transit_cp_idx target_copy active_transit_dest_idx dest active_transit_source_idx source

Index - Schema action


Table: action.unfulfilled_hold_list

action.unfulfilled_hold_list Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
current_copy bigint NOT NULL
hold integer NOT NULL
circ_lib integer NOT NULL
fail_time timestamp with time zone NOT NULL DEFAULT now()

Index - Schema action


Function: action.age_circ_on_delete()

Returns: trigger

Language: PLPGSQL

BEGIN
	INSERT INTO action.aged_circulation
		(id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
		copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
		circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
		stop_fines_time, checkin_time, create_time, duration, fine_interval, recuring_fine,
		max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recuring_fine_rule,
		max_fine_rule, stop_fines)
	  SELECT
		id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
		copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
		circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
		stop_fines_time, checkin_time, create_time, duration, fine_interval, recuring_fine,
		max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recuring_fine_rule,
		max_fine_rule, stop_fines
	    FROM action.all_circulation WHERE id = OLD.id;

	RETURN OLD;
END;

Function: action.circulation_claims_returned()

Returns: trigger

Language: PLPGSQL

BEGIN
	IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN
		IF NEW.stop_fines = 'CLAIMSRETURNED' THEN
			UPDATE actor.usr SET claims_returned_count = claims_returned_count + 1 WHERE id = NEW.usr;
		END IF;
		IF NEW.stop_fines = 'LOST' THEN
			UPDATE asset.copy SET status = 3 WHERE id = NEW.target_copy;
		END IF;
	END IF;
	RETURN NEW;
END;

Function: action.find_circ_matrix_matchpoint(renewal integer, match_user bigint, match_item integer, context_ou boolean)

Returns: circ_matrix_matchpoint

Language: PLPGSQL

DECLARE
    current_group    permission.grp_tree%ROWTYPE;
    user_object    actor.usr%ROWTYPE;
    item_object    asset.copy%ROWTYPE;
    rec_descriptor    metabib.rec_descriptor%ROWTYPE;
    current_mp    config.circ_matrix_matchpoint%ROWTYPE;
    matchpoint    config.circ_matrix_matchpoint%ROWTYPE;
BEGIN
    SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
    SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
    SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r JOIN asset.call_number c USING (record) WHERE c.id = item_object.call_number;
    SELECT INTO current_group * FROM permission.grp_tree WHERE id = user_object.profile;

    LOOP 
        -- for each potential matchpoint for this ou and group ...
        FOR current_mp IN
            SELECT    m.*
              FROM    config.circ_matrix_matchpoint m
                JOIN actor.org_unit_ancestors( context_ou ) d ON (m.org_unit = d.id)
                LEFT JOIN actor.org_unit_proximity p ON (p.from_org = context_ou AND p.to_org = d.id)
              WHERE    m.grp = current_group.id AND m.active
              ORDER BY    CASE WHEN p.prox        IS NULL THEN 999 ELSE p.prox END,
                    CASE WHEN m.is_renewal = renewal        THEN 128 ELSE 0 END +
                    CASE WHEN m.juvenile_flag    IS NOT NULL THEN 64 ELSE 0 END +
                    CASE WHEN m.circ_modifier    IS NOT NULL THEN 32 ELSE 0 END +
                    CASE WHEN m.marc_type        IS NOT NULL THEN 16 ELSE 0 END +
                    CASE WHEN m.marc_form        IS NOT NULL THEN 8 ELSE 0 END +
                    CASE WHEN m.marc_vr_format    IS NOT NULL THEN 4 ELSE 0 END +
                    CASE WHEN m.ref_flag        IS NOT NULL THEN 2 ELSE 0 END +
                    CASE WHEN m.usr_age_lower_bound    IS NOT NULL THEN 0.5 ELSE 0 END +
                    CASE WHEN m.usr_age_upper_bound    IS NOT NULL THEN 0.5 ELSE 0 END DESC LOOP

            IF current_mp.circ_modifier IS NOT NULL THEN
                CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier OR item_object.circ_modifier IS NULL;
            END IF;

            IF current_mp.marc_type IS NOT NULL THEN
                IF item_object.circ_as_type IS NOT NULL THEN
                    CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type;
                ELSE
                    CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type;
                END IF;
            END IF;

            IF current_mp.marc_form IS NOT NULL THEN
                CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form;
            END IF;

            IF current_mp.marc_vr_format IS NOT NULL THEN
                CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format;
            END IF;

            IF current_mp.ref_flag IS NOT NULL THEN
                CONTINUE WHEN current_mp.ref_flag <> item_object.ref;
            END IF;

            IF current_mp.juvenile_flag IS NOT NULL THEN
                CONTINUE WHEN current_mp.juvenile_flag <> user_object.juvenile;
            END IF;

            IF current_mp.usr_age_lower_bound IS NOT NULL THEN
                CONTINUE WHEN user_object.dob IS NULL OR current_mp.usr_age_lower_bound < age(user_object.dob);
            END IF;

            IF current_mp.usr_age_upper_bound IS NOT NULL THEN
                CONTINUE WHEN user_object.dob IS NULL OR current_mp.usr_age_upper_bound > age(user_object.dob);
            END IF;


            -- everything was undefined or matched
            matchpoint = current_mp;

            EXIT WHEN matchpoint.id IS NOT NULL;
        END LOOP;

        EXIT WHEN current_group.parent IS NULL OR matchpoint.id IS NOT NULL;

        SELECT INTO current_group * FROM permission.grp_tree WHERE id = current_group.parent;
    END LOOP;

    RETURN matchpoint;
END;

Function: action.find_hold_matrix_matchpoint(match_requestor integer, match_user integer, match_item bigint, request_ou integer, pickup_ou integer)

Returns: integer

Language: PLPGSQL

DECLARE
    current_requestor_group    permission.grp_tree%ROWTYPE;
    root_ou            actor.org_unit%ROWTYPE;
    requestor_object    actor.usr%ROWTYPE;
    user_object        actor.usr%ROWTYPE;
    item_object        asset.copy%ROWTYPE;
    item_cn_object        asset.call_number%ROWTYPE;
    rec_descriptor        metabib.rec_descriptor%ROWTYPE;
    current_mp_weight    FLOAT;
    matchpoint_weight    FLOAT;
    tmp_weight        FLOAT;
    current_mp        config.hold_matrix_matchpoint%ROWTYPE;
    matchpoint        config.hold_matrix_matchpoint%ROWTYPE;
BEGIN
    SELECT INTO root_ou * FROM actor.org_unit WHERE parent_ou IS NULL;
    SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
    SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor;
    SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
    SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
    SELECT INTO rec_descriptor r.* FROM metabib.rec_descriptor r WHERE r.record = item_cn_object.record;
    SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = requestor_object.profile;

    LOOP 
        -- for each potential matchpoint for this ou and group ...
        FOR current_mp IN
            SELECT    m.*
              FROM    config.hold_matrix_matchpoint m
              WHERE    m.requestor_grp = current_requestor_group.id AND m.active
              ORDER BY    CASE WHEN m.circ_modifier    IS NOT NULL THEN 16 ELSE 0 END +
                    CASE WHEN m.juvenile_flag    IS NOT NULL THEN 16 ELSE 0 END +
                    CASE WHEN m.marc_type        IS NOT NULL THEN 8 ELSE 0 END +
                    CASE WHEN m.marc_form        IS NOT NULL THEN 4 ELSE 0 END +
                    CASE WHEN m.marc_vr_format    IS NOT NULL THEN 2 ELSE 0 END +
                    CASE WHEN m.ref_flag        IS NOT NULL THEN 1 ELSE 0 END DESC LOOP

            current_mp_weight := 5.0;

            IF current_mp.circ_modifier IS NOT NULL THEN
                CONTINUE WHEN current_mp.circ_modifier <> item_object.circ_modifier OR item_object.circ_modifier IS NULL;
            END IF;

            IF current_mp.marc_type IS NOT NULL THEN
                IF item_object.circ_as_type IS NOT NULL THEN
                    CONTINUE WHEN current_mp.marc_type <> item_object.circ_as_type;
                ELSE
                    CONTINUE WHEN current_mp.marc_type <> rec_descriptor.item_type;
                END IF;
            END IF;

            IF current_mp.marc_form IS NOT NULL THEN
                CONTINUE WHEN current_mp.marc_form <> rec_descriptor.item_form;
            END IF;

            IF current_mp.marc_vr_format IS NOT NULL THEN
                CONTINUE WHEN current_mp.marc_vr_format <> rec_descriptor.vr_format;
            END IF;

            IF current_mp.juvenile_flag IS NOT NULL THEN
                CONTINUE WHEN current_mp.juvenile_flag <> user_object.juvenile;
            END IF;

            IF current_mp.ref_flag IS NOT NULL THEN
                CONTINUE WHEN current_mp.ref_flag <> item_object.ref;
            END IF;


            -- caclulate the rule match weight
            IF current_mp.item_owning_ou IS NOT NULL AND current_mp.item_owning_ou <> root_ou.id THEN
                SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_owning_ou, item_cn_object.owning_lib)::FLOAT + 1.0)::FLOAT;
                current_mp_weight := current_mp_weight - tmp_weight;
            END IF; 

            IF current_mp.item_circ_ou IS NOT NULL AND current_mp.item_circ_ou <> root_ou.id THEN
                SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.item_circ_ou, item_object.circ_lib)::FLOAT + 1.0)::FLOAT;
                current_mp_weight := current_mp_weight - tmp_weight;
            END IF; 

            IF current_mp.pickup_ou IS NOT NULL AND current_mp.pickup_ou <> root_ou.id THEN
                SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.pickup_ou, pickup_ou)::FLOAT + 1.0)::FLOAT;
                current_mp_weight := current_mp_weight - tmp_weight;
            END IF; 

            IF current_mp.request_ou IS NOT NULL AND current_mp.request_ou <> root_ou.id THEN
                SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.request_ou, request_ou)::FLOAT + 1.0)::FLOAT;
                current_mp_weight := current_mp_weight - tmp_weight;
            END IF; 

            IF current_mp.user_home_ou IS NOT NULL AND current_mp.user_home_ou <> root_ou.id THEN
                SELECT INTO tmp_weight 1.0 / (actor.org_unit_proximity(current_mp.user_home_ou, user_object.home_ou)::FLOAT + 1.0)::FLOAT;
                current_mp_weight := current_mp_weight - tmp_weight;
            END IF; 

            -- set the matchpoint if we found the best one
            IF matchpoint_weight IS NULL OR matchpoint_weight > current_mp_weight THEN
                matchpoint = current_mp;
                matchpoint_weight = current_mp_weight;
            END IF;

        END LOOP;

        EXIT WHEN current_requestor_group.parent IS NULL OR matchpoint.id IS NOT NULL;

        SELECT INTO current_requestor_group * FROM permission.grp_tree WHERE id = current_requestor_group.parent;
    END LOOP;

    RETURN matchpoint.id;
END;

Function: action.hold_request_permit_test(match_requestor integer, match_user integer, match_item bigint, request_ou integer, pickup_ou integer)

Returns: SET OF matrix_test_result

Language: PLPGSQL

DECLARE
    matchpoint_id        INT;
    user_object        actor.usr%ROWTYPE;
    age_protect_object    config.rule_age_hold_protect%ROWTYPE;
    standing_penalty    config.standing_penalty%ROWTYPE;
    transit_range_ou_type    actor.org_unit_type%ROWTYPE;
    transit_source        actor.org_unit%ROWTYPE;
    item_object        asset.copy%ROWTYPE;
    result            action.matrix_test_result;
    hold_test        config.hold_matrix_matchpoint%ROWTYPE;
    hold_count        INT;
    hold_transit_prox    INT;
    frozen_hold_count    INT;
    context_org_list    INT[];
    done            BOOL := FALSE;
BEGIN
    SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
    SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );

    -- Fail if we couldn't find a user
    IF user_object.id IS NULL THEN
        result.fail_part := 'no_user';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    -- Fail if user is barred
    IF user_object.barred IS TRUE THEN
        result.fail_part := 'actor.usr.barred';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    SELECT INTO item_object * FROM asset.copy WHERE id = match_item;

    -- Fail if we couldn't find a copy
    IF item_object.id IS NULL THEN
        result.fail_part := 'no_item';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);

    -- Fail if we couldn't find any matchpoint (requires a default)
    IF matchpoint_id IS NULL THEN
        result.fail_part := 'no_matchpoint';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;

    result.matchpoint := hold_test.id;
    result.success := TRUE;

    IF hold_test.holdable IS FALSE THEN
        result.fail_part := 'config.hold_matrix_test.holdable';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    IF hold_test.transit_range IS NOT NULL THEN
        SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
        IF hold_test.distance_is_from_owner THEN
            SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number;
        ELSE
            SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
        END IF;

        PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;

        IF NOT FOUND THEN
            result.fail_part := 'transit_range';
            result.success := FALSE;
            done := TRUE;
            RETURN NEXT result;
        END IF;
    END IF;
 
    FOR standing_penalty IN
        SELECT  DISTINCT csp.*
          FROM  actor.usr_standing_penalty usp
                JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
          WHERE usr = match_user
                AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
                AND (usp.stop_date IS NULL or usp.stop_date > NOW())
                AND csp.block_list LIKE '%HOLD%' LOOP

        result.fail_part := standing_penalty.name;
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END LOOP;

    IF hold_test.stop_blocked_user IS TRUE THEN
        FOR standing_penalty IN
            SELECT  DISTINCT csp.*
              FROM  actor.usr_standing_penalty usp
                    JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
              WHERE usr = match_user
                    AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
                    AND (usp.stop_date IS NULL or usp.stop_date > NOW())
                    AND csp.block_list LIKE '%CIRC%' LOOP
    
            result.fail_part := standing_penalty.name;
            result.success := FALSE;
            done := TRUE;
            RETURN NEXT result;
        END LOOP;
    END IF;

    IF hold_test.max_holds IS NOT NULL THEN
        SELECT    INTO hold_count COUNT(*)
          FROM    action.hold_request
          WHERE    usr = match_user
            AND fulfillment_time IS NULL
            AND cancel_time IS NULL
            AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;

        IF hold_count >= hold_test.max_holds THEN
            result.fail_part := 'config.hold_matrix_test.max_holds';
            result.success := FALSE;
            done := TRUE;
            RETURN NEXT result;
        END IF;
    END IF;

    IF item_object.age_protect IS NOT NULL THEN
        SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;

        IF item_object.create_date + age_protect_object.age > NOW() THEN
            IF hold_test.distance_is_from_owner THEN
                SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
            ELSE
                SELECT INTO hold_transit_prox prox FROM actor.org_unit_prox WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
            END IF;

            IF hold_transit_prox > age_protect_object.prox THEN
                result.fail_part := 'config.rule_age_hold_protect.prox';
                result.success := FALSE;
                done := TRUE;
                RETURN NEXT result;
            END IF;
        END IF;
    END IF;

    IF NOT done THEN
        RETURN NEXT result;
    END IF;

    RETURN;
END;

Function: action.item_user_circ_test(integer, bigint, integer)

Returns: SET OF matrix_test_result

Language: SQL

    SELECT * FROM action.item_user_circ_test( $1, $2, $3, FALSE );

Function: action.item_user_circ_test(renewal integer, match_user bigint, match_item integer, circ_ou boolean)

Returns: SET OF matrix_test_result

Language: PLPGSQL

DECLARE
    user_object        actor.usr%ROWTYPE;
    standing_penalty    config.standing_penalty%ROWTYPE;
    item_object        asset.copy%ROWTYPE;
    item_status_object    config.copy_status%ROWTYPE;
    item_location_object    asset.copy_location%ROWTYPE;
    result            action.matrix_test_result;
    circ_test        config.circ_matrix_matchpoint%ROWTYPE;
    out_by_circ_mod        config.circ_matrix_circ_mod_test%ROWTYPE;
    circ_mod_map        config.circ_matrix_circ_mod_test_map%ROWTYPE;
    penalty_type         TEXT;
    tmp_grp         INT;
    items_out        INT;
    context_org_list        INT[];
    done            BOOL := FALSE;
BEGIN
    result.success := TRUE;

    -- Fail if the user is BARRED
    SELECT INTO user_object * FROM actor.usr WHERE id = match_user;

    -- Fail if we couldn't find a set of tests
    IF user_object.id IS NULL THEN
        result.fail_part := 'no_user';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
        RETURN;
    END IF;

    IF user_object.barred IS TRUE THEN
        result.fail_part := 'actor.usr.barred';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    -- Fail if the item can't circulate
    SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
    IF item_object.circulate IS FALSE THEN
        result.fail_part := 'asset.copy.circulate';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    -- Fail if the item isn't in a circulateable status on a non-renewal
    IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN 
        result.fail_part := 'asset.copy.status';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    ELSIF renewal AND item_object.status <> 1 THEN
        result.fail_part := 'asset.copy.status';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    -- Fail if the item can't circulate because of the shelving location
    SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
    IF item_location_object.circulate IS FALSE THEN
        result.fail_part := 'asset.copy_location.circulate';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, match_item, match_user, renewal);
    result.matchpoint := circ_test.id;

    SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_test.org_unit );

    -- Fail if we couldn't find a set of tests
    IF result.matchpoint IS NULL THEN
        result.fail_part := 'no_matchpoint';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    -- Fail if the test is set to hard non-circulating
    IF circ_test.circulate IS FALSE THEN
        result.fail_part := 'config.circ_matrix_test.circulate';
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END IF;

    IF renewal THEN
        penalty_type = '%RENEW%';
    ELSE
        penalty_type = '%CIRC%';
    END IF;

    FOR standing_penalty IN
        SELECT  DISTINCT csp.*
          FROM  actor.usr_standing_penalty usp
                JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
          WHERE usr = match_user
                AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) )
                AND (usp.stop_date IS NULL or usp.stop_date > NOW())
                AND csp.block_list LIKE penalty_type LOOP

        result.fail_part := standing_penalty.name;
        result.success := FALSE;
        done := TRUE;
        RETURN NEXT result;
    END LOOP;

    -- Fail if the user has too many items with specific circ_modifiers checked out
    FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_test.id LOOP
        SELECT  INTO items_out COUNT(*)
          FROM  action.circulation circ
            JOIN asset.copy cp ON (cp.id = circ.target_copy)
          WHERE circ.usr = match_user
               AND circ_lib IN ( SELECT * FROM explode_array(context_org_list) )
            AND circ.checkin_time IS NULL
            AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
            AND cp.circ_modifier IN (SELECT circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = out_by_circ_mod.id);
        IF items_out >= out_by_circ_mod.items_out THEN
            result.fail_part := 'config.circ_matrix_circ_mod_test';
            result.success := FALSE;
            done := TRUE;
            RETURN NEXT result;
        END IF;
    END LOOP;

    -- If we passed everything, return the successful matchpoint id
    IF NOT done THEN
        RETURN NEXT result;
    END IF;

    RETURN;
END;

Function: action.item_user_renew_test(integer, bigint, integer)

Returns: SET OF matrix_test_result

Language: SQL

    SELECT * FROM action.item_user_circ_test( $1, $2, $3, TRUE );

Function: action.survey_response_answer_date_fixup()

Returns: trigger

Language: PLPGSQL

BEGIN
	NEW.answer_date := NOW();
	RETURN NEW;
END;

Schema action_trigger


Table: action_trigger.cleanup

action_trigger.cleanup Structure
F-Key Name Type Description
module text PRIMARY KEY
description text

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Table: action_trigger.collector

action_trigger.collector Structure
F-Key Name Type Description
module text PRIMARY KEY
description text

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Table: action_trigger.environment

action_trigger.environment Structure
F-Key Name Type Description
id serial PRIMARY KEY
action_trigger.event_definition.id event_def integer UNIQUE#1 NOT NULL
path text
action_trigger.collector.module collector text
label text UNIQUE#1

 

action_trigger.environment Constraints
Name Constraint
environment_label_check CHECK ((label <> ALL (ARRAY['result'::text, 'target'::text, 'event'::text])))

Index - Schema action_trigger


Table: action_trigger.event

action_trigger.event Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
target bigint NOT NULL
action_trigger.event_definition.id event_def integer
add_time timestamp with time zone NOT NULL DEFAULT now()
run_time timestamp with time zone NOT NULL
start_time timestamp with time zone
update_time timestamp with time zone
complete_time timestamp with time zone
update_process integer
state text NOT NULL DEFAULT 'pending'::text
action_trigger.event_output.id template_output bigint
action_trigger.event_output.id error_output bigint

 

action_trigger.event Constraints
Name Constraint
event_state_check CHECK ((state = ANY (ARRAY['pending'::text, 'invalid'::text, 'found'::text, 'collecting'::text, 'collected'::text, 'validating'::text, 'valid'::text, 'reacting'::text, 'reacted'::text, 'cleaning'::text, 'complete'::text, 'error'::text])))

Index - Schema action_trigger


Table: action_trigger.event_definition

action_trigger.event_definition Structure
F-Key Name Type Description
id serial PRIMARY KEY
active boolean NOT NULL DEFAULT true
actor.org_unit.id owner integer UNIQUE#2 UNIQUE#1 NOT NULL
name text UNIQUE#2 NOT NULL
action_trigger.hook.key hook text UNIQUE#1 NOT NULL
action_trigger.validator.module validator text UNIQUE#1 NOT NULL
action_trigger.reactor.module reactor text UNIQUE#1 NOT NULL
action_trigger.cleanup.module cleanup_success text
action_trigger.cleanup.module cleanup_failure text
delay interval UNIQUE#1 NOT NULL DEFAULT '00:05:00'::interval
delay_field text UNIQUE#1
group_field text
template text

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Table: action_trigger.event_output

action_trigger.event_output Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
create_time timestamp with time zone NOT NULL DEFAULT now()
is_error boolean NOT NULL DEFAULT false
data text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Table: action_trigger.event_params

action_trigger.event_params Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
action_trigger.event_definition.id event_def integer UNIQUE#1 NOT NULL
param text UNIQUE#1 NOT NULL
value text NOT NULL

Index - Schema action_trigger


Table: action_trigger.hook

action_trigger.hook Structure
F-Key Name Type Description
key text PRIMARY KEY
core_type text NOT NULL
description text
passive boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Table: action_trigger.reactor

action_trigger.reactor Structure
F-Key Name Type Description
module text PRIMARY KEY
description text

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Table: action_trigger.validator

action_trigger.validator Structure
F-Key Name Type Description
module text PRIMARY KEY
description text

Tables referencing this one via Foreign Key Constraints:

Index - Schema action_trigger


Schema actor

/* * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service * Mike Rylander * * Schema: actor * * Holds all tables pertaining to users and libraries (org units). * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */


Table: actor.card

/* * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * Library Cards * * Each User has one or more library cards. The current "main" * card is linked to here from the actor.usr table, and it is up * to the consortium policy whether more than one card can be * active for any one user at a given time. * * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

actor.card Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id usr integer NOT NULL
barcode text UNIQUE NOT NULL
active boolean NOT NULL DEFAULT true
actor_card_usr_idx usr

Index - Schema actor


Table: actor.hours_of_operation

actor.hours_of_operation Structure
F-Key Name Type Description
actor.org_unit.id id integer PRIMARY KEY
dow_0_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone
dow_0_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone
dow_1_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone
dow_1_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone
dow_2_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone
dow_2_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone
dow_3_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone
dow_3_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone
dow_4_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone
dow_4_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone
dow_5_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone
dow_5_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone
dow_6_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone
dow_6_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone

Index - Schema actor


Table: actor.org_address

actor.org_address Structure
F-Key Name Type Description
id serial PRIMARY KEY
valid boolean NOT NULL DEFAULT true
address_type text NOT NULL DEFAULT 'MAILING'::text
actor.org_unit.id org_unit integer NOT NULL
street1 text NOT NULL
street2 text
city text NOT NULL
county text
state text NOT NULL
country text NOT NULL
post_code text NOT NULL

Tables referencing this one via Foreign Key Constraints:

actor_org_address_org_unit_idx org_unit

Index - Schema actor


Table: actor.org_lasso

actor.org_lasso Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE

Tables referencing this one via Foreign Key Constraints:

Index - Schema actor


Table: actor.org_lasso_map

actor.org_lasso_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_lasso.id lasso integer NOT NULL
actor.org_unit.id org_unit integer NOT NULL
ou_lasso_org_unit_idx org_unit

Index - Schema actor


Table: actor.org_unit

actor.org_unit Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id parent_ou integer
actor.org_unit_type.id ou_type integer NOT NULL
actor.org_address.id ill_address integer
actor.org_address.id holds_address integer
actor.org_address.id mailing_address integer
actor.org_address.id billing_address integer
shortname text UNIQUE NOT NULL
name text UNIQUE NOT NULL
email text
phone text
opac_visible boolean NOT NULL DEFAULT true

Tables referencing this one via Foreign Key Constraints:

actor_org_unit_billing_address_idx billing_address actor_org_unit_holds_address_idx holds_address actor_org_unit_ill_address_idx ill_address actor_org_unit_mailing_address_idx mailing_address actor_org_unit_ou_type_idx ou_type actor_org_unit_parent_ou_idx parent_ou

Index - Schema actor


Table: actor.org_unit_closed

actor.org_unit_closed Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id org_unit integer NOT NULL
close_start timestamp with time zone NOT NULL
close_end timestamp with time zone NOT NULL
reason text

Index - Schema actor


Table: actor.org_unit_proximity

actor.org_unit_proximity Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
from_org integer
to_org integer
prox integer
from_prox_idx from_org

Index - Schema actor


Table: actor.org_unit_setting

/* * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * Org Unit settings * * This table contains any arbitrary settings that a client * program would like to save for an org unit. * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

actor.org_unit_setting Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.org_unit.id org_unit integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
value text NOT NULL
actor_org_unit_setting_usr_idx org_unit

Index - Schema actor


Table: actor.org_unit_type

actor.org_unit_type Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text NOT NULL
opac_label text NOT NULL
depth integer NOT NULL
actor.org_unit_type.id parent integer
can_have_vols boolean NOT NULL DEFAULT true
can_have_users boolean NOT NULL DEFAULT true

Tables referencing this one via Foreign Key Constraints:

actor_org_unit_type_parent_idx parent

Index - Schema actor


Table: actor.stat_cat

/* * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * User Statistical Catagories * * Local data collected about Users is placed into a Statistical * Catagory. Here's where those catagories are defined. * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

actor.stat_cat Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
opac_visible boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema actor


Table: actor.stat_cat_entry

/* * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * User Statistical Catagory Entries * * Local data collected about Users is placed into a Statistical * Catagory. Each library can create entries into any of its own * stat_cats, its ancestors' stat_cats, or its descendants' stat_cats. * * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

actor.stat_cat_entry Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.stat_cat.id stat_cat integer UNIQUE#1 NOT NULL
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
value text UNIQUE#1 NOT NULL

Index - Schema actor


Table: actor.stat_cat_entry_usr_map

/* * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * Statistical Catagory Entry to User map * * Records the stat_cat entries for each user. * * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

actor.stat_cat_entry_usr_map Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
stat_cat_entry text NOT NULL
actor.stat_cat.id stat_cat integer UNIQUE#1 NOT NULL
actor.usr.id target_usr integer UNIQUE#1 NOT NULL
actor_stat_cat_entry_usr_idx target_usr

Index - Schema actor


Table: actor.usr

/* * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * User objects * * This table contains the core User objects that describe both * staff members and patrons. The difference between the two * types of users is based on the user's permissions. * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

actor.usr Structure
F-Key Name Type Description
id serial PRIMARY KEY
card integer UNIQUE
permission.grp_tree.id profile integer NOT NULL
usrname text UNIQUE NOT NULL
email text
passwd text NOT NULL
config.standing.id standing integer NOT NULL DEFAULT 1
config.identification_type.id ident_type integer NOT NULL
ident_value text
config.identification_type.id ident_type2 integer
ident_value2 text
config.net_access_level.id net_access_level integer NOT NULL DEFAULT 1
photo_url text
prefix text
first_given_name text NOT NULL
second_given_name text
family_name text NOT NULL
suffix text
alias text
day_phone text
evening_phone text
other_phone text
actor.usr_address.id mailing_address integer
actor.usr_address.id billing_address integer
actor.org_unit.id home_ou integer NOT NULL
dob timestamp with time zone
active boolean NOT NULL DEFAULT true
master_account boolean NOT NULL DEFAULT false
super_user boolean NOT NULL DEFAULT false
barred boolean NOT NULL DEFAULT false
deleted boolean NOT NULL DEFAULT false
juvenile boolean NOT NULL DEFAULT false
usrgroup serial NOT NULL
claims_returned_count integer NOT NULL
credit_forward_balance numeric(6,2) NOT NULL DEFAULT 0.00
last_xact_id text NOT NULL DEFAULT 'none'::text
alert_message text
create_date timestamp with time zone NOT NULL DEFAULT now()
expire_date timestamp with time zone NOT NULL DEFAULT (now() + '3 years'::interval)

Tables referencing this one via Foreign Key Constraints:

actor_usr_billing_address_idx billing_address actor_usr_day_phone_idx lower(day_phone) actor_usr_email_idx lower(email) actor_usr_evening_phone_idx lower(evening_phone) actor_usr_family_name_idx lower(family_name) actor_usr_first_given_name_idx lower(first_given_name) actor_usr_home_ou_idx home_ou actor_usr_ident_value2_idx lower(ident_value2) actor_usr_ident_value_idx lower(ident_value) actor_usr_mailing_address_idx mailing_address actor_usr_other_phone_idx lower(other_phone) actor_usr_second_given_name_idx lower(second_given_name)

Index - Schema actor


Table: actor.usr_address

actor.usr_address Structure
F-Key Name Type Description
id serial PRIMARY KEY
valid boolean NOT NULL DEFAULT true
within_city_limits boolean NOT NULL DEFAULT true
address_type text NOT NULL DEFAULT 'MAILING'::text
actor.usr.id usr integer NOT NULL
street1 text NOT NULL
street2 text
city text NOT NULL
county text
state text NOT NULL
country text NOT NULL
post_code text NOT NULL
pending boolean NOT NULL DEFAULT false
actor.usr_address.id replaces integer

Tables referencing this one via Foreign Key Constraints:

actor_usr_addr_city_idx lower(city) actor_usr_addr_post_code_idx lower(post_code) actor_usr_addr_state_idx lower(state) actor_usr_addr_street1_idx lower(street1) actor_usr_addr_street2_idx lower(street2) actor_usr_addr_usr_idx usr

Index - Schema actor


Table: actor.usr_note

actor.usr_note Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id usr bigint NOT NULL
actor.usr.id creator bigint NOT NULL
create_date timestamp with time zone DEFAULT now()
pub boolean NOT NULL DEFAULT false
title text NOT NULL
value text NOT NULL
actor_usr_note_usr_idx usr

Index - Schema actor


Table: actor.usr_org_unit_opt_in

actor.usr_org_unit_opt_in Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id org_unit integer UNIQUE#1 NOT NULL
actor.usr.id usr integer UNIQUE#1 NOT NULL
actor.usr.id staff integer NOT NULL
opt_in_ts timestamp with time zone NOT NULL DEFAULT now()
actor.workstation.id opt_in_ws integer NOT NULL

Index - Schema actor


Table: actor.usr_setting

/* * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * User settings * * This table contains any arbitrary settings that a client * program would like to save for a user. * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

actor.usr_setting Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id usr integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
value text NOT NULL
actor_usr_setting_usr_idx usr

Index - Schema actor


Table: actor.usr_standing_penalty

/* * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * User standing penalties * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

actor.usr_standing_penalty Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id org_unit integer NOT NULL
actor.usr.id usr integer NOT NULL
config.standing_penalty.id standing_penalty integer NOT NULL
actor.usr.id staff integer
set_date timestamp with time zone DEFAULT now()
stop_date timestamp with time zone
note text
actor_usr_standing_penalty_usr_idx usr

Index - Schema actor


Table: actor.workstation

actor.workstation Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE NOT NULL
actor.org_unit.id owning_lib integer NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema actor


Function: actor.approve_pending_address(pending_id integer)

Returns: bigint

Language: PLPGSQL

/** * Replaces an address with a pending address. This is done by giving the pending * address the ID of the old address. The replaced address is retained with -id. */

DECLARE
    old_id INT;
BEGIN
    SELECT INTO old_id replaces FROM actor.usr_address where id = pending_id;
    IF old_id IS NULL THEN
        UPDATE actor.usr_address SET pending = 'f' WHERE id = pending_id;
        RETURN pending_id;
    END IF;
    -- address replaces an existing address
    DELETE FROM actor.usr_address WHERE id = -old_id;
    UPDATE actor.usr_address SET id = -id WHERE id = old_id;
    UPDATE actor.usr_address SET replaces = NULL, id = old_id, pending = 'f' WHERE id = pending_id;
    RETURN old_id;
END

Function: actor.calculate_system_penalties(context_org integer, match_user integer)

Returns: SET OF usr_standing_penalty

Language: PLPGSQL

DECLARE
    user_object         actor.usr%ROWTYPE;
    new_sp_row          actor.usr_standing_penalty%ROWTYPE;
    existing_sp_row     actor.usr_standing_penalty%ROWTYPE;
    collections_fines   permission.grp_penalty_threshold%ROWTYPE;
    max_fines           permission.grp_penalty_threshold%ROWTYPE;
    max_overdue         permission.grp_penalty_threshold%ROWTYPE;
    max_items_out       permission.grp_penalty_threshold%ROWTYPE;
    tmp_grp             INT;
    items_overdue       INT;
    items_out           INT;
    context_org_list    INT[];
    current_fines        NUMERIC(8,2) := 0.0;
    tmp_fines            NUMERIC(8,2);
    tmp_groc            RECORD;
    tmp_circ            RECORD;
    tmp_org             actor.org_unit%ROWTYPE;
BEGIN
    SELECT INTO user_object * FROM actor.usr WHERE id = match_user;

    -- Max fines
    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;

    -- Fail if the user has a high fine balance
    LOOP
        tmp_grp := user_object.profile;
        LOOP
            SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;

            IF max_fines.threshold IS NULL THEN
                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
            ELSE
                EXIT;
            END IF;

            IF tmp_grp IS NULL THEN
                EXIT;
            END IF;
        END LOOP;

        IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
            EXIT;
        END IF;

        SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;

    END LOOP;

    IF max_fines.threshold IS NOT NULL THEN

        FOR existing_sp_row IN
                SELECT  *
                  FROM  actor.usr_standing_penalty
                  WHERE usr = match_user
                        AND org_unit = max_fines.org_unit
                        AND (stop_date IS NULL or stop_date > NOW())
                        AND standing_penalty = 1
                LOOP
            RETURN NEXT existing_sp_row;
        END LOOP;

        SELECT  SUM(f.balance_owed) INTO current_fines
          FROM  money.materialized_billable_xact_summary f
                JOIN (
                    SELECT  g.id
                      FROM  money.grocery g
                            JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
                      WHERE usr = match_user
                            AND xact_finish IS NULL
                                UNION ALL
                    SELECT  circ.id
                      FROM  action.circulation circ
                            JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
                      WHERE usr = match_user
                            AND xact_finish IS NULL ) l USING (id);

        IF current_fines >= max_fines.threshold THEN
            new_sp_row.usr := match_user;
            new_sp_row.org_unit := max_fines.org_unit;
            new_sp_row.standing_penalty := 1;
            RETURN NEXT new_sp_row;
        END IF;
    END IF;

    -- Start over for max overdue
    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;

    -- Fail if the user has too many overdue items
    LOOP
        tmp_grp := user_object.profile;
        LOOP

            SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;

            IF max_overdue.threshold IS NULL THEN
                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
            ELSE
                EXIT;
            END IF;

            IF tmp_grp IS NULL THEN
                EXIT;
            END IF;
        END LOOP;

        IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
            EXIT;
        END IF;

        SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;

    END LOOP;

    IF max_overdue.threshold IS NOT NULL THEN

        FOR existing_sp_row IN
                SELECT  *
                  FROM  actor.usr_standing_penalty
                  WHERE usr = match_user
                        AND org_unit = max_overdue.org_unit
                        AND (stop_date IS NULL or stop_date > NOW())
                        AND standing_penalty = 2
                LOOP
            RETURN NEXT existing_sp_row;
        END LOOP;

        SELECT  INTO items_overdue COUNT(*)
          FROM  action.circulation circ
                JOIN  actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
          WHERE circ.usr = match_user
            AND circ.checkin_time IS NULL
            AND circ.due_date < NOW()
            AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);

        IF items_overdue >= max_overdue.threshold::INT THEN
            new_sp_row.usr := match_user;
            new_sp_row.org_unit := max_overdue.org_unit;
            new_sp_row.standing_penalty := 2;
            RETURN NEXT new_sp_row;
        END IF;
    END IF;

    -- Start over for max out
    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;

    -- Fail if the user has too many checked out items
    LOOP
        tmp_grp := user_object.profile;
        LOOP
            SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;

            IF max_items_out.threshold IS NULL THEN
                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
            ELSE
                EXIT;
            END IF;

            IF tmp_grp IS NULL THEN
                EXIT;
            END IF;
        END LOOP;

        IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
            EXIT;
        END IF;

        SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;

    END LOOP;


    -- Fail if the user has too many items checked out
    IF max_items_out.threshold IS NOT NULL THEN

        FOR existing_sp_row IN
                SELECT  *
                  FROM  actor.usr_standing_penalty
                  WHERE usr = match_user
                        AND org_unit = max_items_out.org_unit
                        AND (stop_date IS NULL or stop_date > NOW())
                        AND standing_penalty = 3
                LOOP
            RETURN NEXT existing_sp_row;
        END LOOP;

        SELECT  INTO items_out COUNT(*)
          FROM  action.circulation circ
                JOIN  actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
          WHERE circ.usr = match_user
                AND circ.checkin_time IS NULL
                AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL);

           IF items_out >= max_items_out.threshold::INT THEN
            new_sp_row.usr := match_user;
            new_sp_row.org_unit := max_items_out.org_unit;
            new_sp_row.standing_penalty := 3;
            RETURN NEXT new_sp_row;
           END IF;
    END IF;

    -- Start over for collections warning
    SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;

    -- Fail if the user has a collections-level fine balance
    LOOP
        tmp_grp := user_object.profile;
        LOOP
            SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;

            IF max_fines.threshold IS NULL THEN
                SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
            ELSE
                EXIT;
            END IF;

            IF tmp_grp IS NULL THEN
                EXIT;
            END IF;
        END LOOP;

        IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
            EXIT;
        END IF;

        SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;

    END LOOP;

    IF max_fines.threshold IS NOT NULL THEN

        FOR existing_sp_row IN
                SELECT  *
                  FROM  actor.usr_standing_penalty
                  WHERE usr = match_user
                        AND org_unit = max_fines.org_unit
                        AND (stop_date IS NULL or stop_date > NOW())
                        AND standing_penalty = 4
                LOOP
            RETURN NEXT existing_sp_row;
        END LOOP;

        SELECT  SUM(f.balance_owed) INTO current_fines
          FROM  money.materialized_billable_xact_summary f
                JOIN (
                    SELECT  g.id
                      FROM  money.grocery g
                            JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (g.billing_location = fp.id)
                      WHERE usr = match_user
                            AND xact_finish IS NULL
                                UNION ALL
                    SELECT  circ.id
                      FROM  action.circulation circ
                            JOIN  actor.org_unit_full_path( max_fines.org_unit ) fp ON (circ.circ_lib = fp.id)
                      WHERE usr = match_user
                            AND xact_finish IS NULL ) l USING (id);

        IF current_fines >= max_fines.threshold THEN
            new_sp_row.usr := match_user;
            new_sp_row.org_unit := max_fines.org_unit;
            new_sp_row.standing_penalty := 4;
            RETURN NEXT new_sp_row;
        END IF;
    END IF;


    RETURN;
END;

Function: actor.crypt_pw_insert()

Returns: trigger

Language: PLPGSQL

	BEGIN
		NEW.passwd = MD5( NEW.passwd );
		RETURN NEW;
	END;

Function: actor.crypt_pw_update()

Returns: trigger

Language: PLPGSQL

	BEGIN
		IF NEW.passwd <> OLD.passwd THEN
			NEW.passwd = MD5( NEW.passwd );
		END IF;
		RETURN NEW;
	END;

Function: actor.org_unit_ancestor_at_depth(integer, integer)

Returns: org_unit

Language: SQL

	SELECT	a.*
	  FROM	actor.org_unit a
	  WHERE	id = ( SELECT FIRST(x.id)
	  		 FROM	actor.org_unit_ancestors($1) x
			   	JOIN actor.org_unit_type y
					ON x.ou_type = y.id AND y.depth = $2);

Function: actor.org_unit_ancestor_setting(org_id text, setting_name integer)

Returns: SET OF org_unit_setting

Language: PLPGSQL

/** * Search "up" the org_unit tree until we find the first occurrence of an * org_unit_setting with the given name. */

DECLARE
    setting RECORD;
    cur_org INT;
BEGIN
    cur_org := org_id;
    LOOP
        SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
        IF FOUND THEN
            RETURN NEXT setting;
        END IF;
        SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
        EXIT WHEN cur_org IS NULL;
    END LOOP;
    RETURN;
END;

Function: actor.org_unit_ancestors(integer)

Returns: SET OF org_unit

Language: SQL

	SELECT	a.*
	  FROM	connectby('actor.org_unit'::text,'parent_ou'::text,'id'::text,'name'::text,$1::text,100,'.'::text)
	  		AS t(keyid text, parent_keyid text, level int, branch text,pos int)
		JOIN actor.org_unit a ON a.id::text = t.keyid::text
        JOIN actor.org_unit_type tp ON tp.id = a.ou_type 
        ORDER BY tp.depth, a.name;

Function: actor.org_unit_combined_ancestors(integer, integer)

Returns: SET OF org_unit

Language: SQL

	SELECT	*
	  FROM	actor.org_unit_ancestors($1)
			UNION
	SELECT	*
	  FROM	actor.org_unit_ancestors($2);

Function: actor.org_unit_common_ancestors(integer, integer)

Returns: SET OF org_unit

Language: SQL

	SELECT	*
	  FROM	actor.org_unit_ancestors($1)
			INTERSECT
	SELECT	*
	  FROM	actor.org_unit_ancestors($2);

Function: actor.org_unit_descendants(integer)

Returns: SET OF org_unit

Language: SQL

	SELECT	a.*
	  FROM	connectby('actor.org_unit'::text,'id'::text,'parent_ou'::text,'name'::text,$1::text,100,'.'::text)
	  		AS t(keyid text, parent_keyid text, level int, branch text,pos int)
		JOIN actor.org_unit a ON a.id::text = t.keyid::text
	  ORDER BY  CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;

Function: actor.org_unit_descendants(integer, integer)

Returns: SET OF org_unit

Language: SQL

	SELECT	a.*
	  FROM	connectby('actor.org_unit'::text,'id'::text,'parent_ou'::text,'name'::text,
	  			(SELECT	x.id
				   FROM	actor.org_unit_ancestors($1) x
				   	JOIN actor.org_unit_type y ON x.ou_type = y.id
				  WHERE	y.depth = $2)::text
		,100,'.'::text)
	  		AS t(keyid text, parent_keyid text, level int, branch text,pos int)
		JOIN actor.org_unit a ON a.id::text = t.keyid::text
	  ORDER BY  CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;

Function: actor.org_unit_full_path(integer)

Returns: SET OF org_unit

Language: SQL

	SELECT	*
	  FROM	actor.org_unit_ancestors($1)
			UNION
	SELECT	*
	  FROM	actor.org_unit_descendants($1);

Function: actor.org_unit_full_path(integer, integer)

Returns: SET OF org_unit

Language: SQL

	SELECT	* FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)

Function: actor.org_unit_proximity(integer, integer)

Returns: integer

Language: SQL

	SELECT COUNT(id)::INT FROM (
		SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
			EXCEPT
		SELECT id FROM actor.org_unit_common_ancestors($1, $2)
	) z;

Function: actor.usr_merge(deactivate_cards integer, del_cards integer, del_addrs boolean, dest_usr boolean, src_usr boolean)

Returns: void

Language: PLPGSQL

/** * Merges all user date from src_usr to dest_usr. When collisions occur, * keep dest_usr's data and delete src_usr's data. */

BEGIN

    -- do some initial cleanup 
    UPDATE actor.usr SET card = NULL WHERE id = src_usr;
    UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
    UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;

    -- actor.*
    IF del_cards THEN
        DELETE FROM actor.card where usr = src_usr;
    ELSE
        IF deactivate_cards THEN
            UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
        END IF;
        UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
    END IF;


    IF del_addrs THEN
        DELETE FROM actor.usr_address WHERE usr = src_usr;
    ELSE
        UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
    END IF;

    UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
    -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
    UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
    PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);

    -- permission.*
    PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);


    -- container.*
    PERFORM actor.usr_merge_rows('container.biblio_record_entry_bucket', 'owner', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('container.call_number_bucket', 'owner', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('container.copy_bucket', 'owner', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('container.user_bucket', 'owner', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('container.user_bucket_item', 'target_user', src_usr, dest_usr);

    -- vandelay.*
    PERFORM actor.usr_merge_rows('vandelay.queue', 'owner', src_usr, dest_usr);

    -- money.*
    PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
    PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
    UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
    UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
    UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;

    -- action.*
    UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
    UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
    UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;

    UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
    UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
    UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
    UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;

    UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
    UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
    UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
    UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
    UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;

    -- acq.*
    UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
    PERFORM actor.usr_merge_rows('acq.picklist', 'owner', src_usr, dest_usr);
    UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
    UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
    UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
    UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
    UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
    UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;

    -- asset.*
    UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
    UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
    UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
    UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
    UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
    UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;

    -- serial.*
    UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
    UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;

    -- reporter.*
    -- It's not uncommon to define the reporter schema in a replica 
    -- DB only, so don't assume these tables exist in the write DB.
    BEGIN
        PERFORM actor.usr_merge_rows('reporter.template', 'owner', src_usr, dest_usr);
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
        PERFORM actor.usr_merge_rows('reporter.report', 'owner', src_usr, dest_usr);
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
        PERFORM actor.usr_merge_rows('reporter.schedule', 'runner', src_usr, dest_usr);
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
        PERFORM actor.usr_merge_rows('reporter.template_folder', 'owner', src_usr, dest_usr);
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
        PERFORM actor.usr_merge_rows('reporter.report_folder', 'owner', src_usr, dest_usr);
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;
    BEGIN
        PERFORM actor.usr_merge_rows('reporter.output_folder', 'owner', src_usr, dest_usr);
    EXCEPTION WHEN undefined_table THEN
        -- do nothing
    END;

    -- Finally, delete the source user
    DELETE FROM actor.usr WHERE id = src_usr;

END;

Function: actor.usr_merge_rows(dest_usr text, src_usr text, col_name integer, table_name integer)

Returns: void

Language: PLPGSQL

/** * Attempts to move each row of the specified table from src_user to dest_user. * Where conflicts exist, the conflicting "source" row is deleted. */

DECLARE
    sel TEXT;
    upd TEXT;
    del TEXT;
    cur_row RECORD;
BEGIN
    sel := 'SELECT id::BIGINT FROM ' || table_name || ' WHERE ' || quote_ident(col_name) || ' = ' || quote_literal(src_usr);
    upd := 'UPDATE ' || table_name || ' SET ' || quote_ident(col_name) || ' = ' || quote_literal(dest_usr) || ' WHERE id = ';
    del := 'DELETE FROM ' || table_name || ' WHERE id = ';
    FOR cur_row IN EXECUTE sel LOOP
        BEGIN
            --RAISE NOTICE 'Attempting to merge % %', table_name, cur_row.id;
            EXECUTE upd || cur_row.id;
        EXCEPTION WHEN unique_violation THEN
            --RAISE NOTICE 'Deleting conflicting % %', table_name, cur_row.id;
            EXECUTE del || cur_row.id;
        END;
    END LOOP;
END;

Schema asset


Table: asset.call_number

asset.call_number Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id creator bigint NOT NULL
create_date timestamp with time zone DEFAULT now()
actor.usr.id editor bigint NOT NULL
edit_date timestamp with time zone DEFAULT now()
biblio.record_entry.id record bigint NOT NULL
actor.org_unit.id owning_lib integer NOT NULL
label text NOT NULL
deleted boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

asset_call_number_creator_idx creator asset_call_number_dewey_idx call_number_dewey(label) asset_call_number_editor_idx editor asset_call_number_record_idx record asset_call_number_upper_label_id_owning_lib_idx upper(label), id, owning_lib

Index - Schema asset


Table: asset.call_number_note

asset.call_number_note Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
asset.call_number.id call_number bigint NOT NULL
actor.usr.id creator bigint NOT NULL
create_date timestamp with time zone DEFAULT now()
pub boolean NOT NULL DEFAULT false
title text NOT NULL
value text NOT NULL

Index - Schema asset


Table: asset.copy

asset.copy Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.org_unit.id circ_lib integer NOT NULL
actor.usr.id creator bigint NOT NULL
asset.call_number.id call_number bigint NOT NULL
actor.usr.id editor bigint NOT NULL
create_date timestamp with time zone DEFAULT now()
edit_date timestamp with time zone DEFAULT now()
copy_number integer
config.copy_status.id status integer NOT NULL
asset.copy_location.id location integer NOT NULL DEFAULT 1
loan_duration integer NOT NULL
fine_level integer NOT NULL
age_protect integer
circulate boolean NOT NULL DEFAULT true
deposit boolean NOT NULL DEFAULT false
ref boolean NOT NULL DEFAULT false
holdable boolean NOT NULL DEFAULT true
deposit_amount numeric(6,2) NOT NULL DEFAULT 0.00
price numeric(8,2)
barcode text NOT NULL
config.circ_modifier.code circ_modifier text
circ_as_type text
dummy_title text
dummy_author text
alert_message text
opac_visible boolean NOT NULL DEFAULT true
deleted boolean NOT NULL DEFAULT false

 

asset.copy Constraints
Name Constraint
copy_fine_level_check CHECK ((fine_level = ANY (ARRAY[1, 2, 3])))
copy_loan_duration_check CHECK ((loan_duration = ANY (ARRAY[1, 2, 3])))

Tables referencing this one via Foreign Key Constraints:

cp_avail_cn_idx call_number cp_cn_idx call_number

Index - Schema asset


Table: asset.copy_location

asset.copy_location Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE#1 NOT NULL
actor.org_unit.id owning_lib integer UNIQUE#1 NOT NULL
holdable boolean NOT NULL DEFAULT true
hold_verify boolean NOT NULL DEFAULT false
opac_visible boolean NOT NULL DEFAULT true
circulate boolean NOT NULL DEFAULT true

Tables referencing this one via Foreign Key Constraints:

Index - Schema asset


Table: asset.copy_note

asset.copy_note Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
asset.copy.id owning_copy bigint NOT NULL
actor.usr.id creator bigint NOT NULL
create_date timestamp with time zone DEFAULT now()
pub boolean NOT NULL DEFAULT false
title text NOT NULL
value text NOT NULL

Index - Schema asset


Table: asset.copy_transparency

asset.copy_transparency Structure
F-Key Name Type Description
id serial PRIMARY KEY
deposit_amount numeric(6,2)
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
actor.org_unit.id circ_lib integer
loan_duration integer
fine_level integer
holdable boolean
circulate boolean
deposit boolean
ref boolean
opac_visible boolean
circ_modifier text
circ_as_type text
name text UNIQUE#1 NOT NULL

 

asset.copy_transparency Constraints
Name Constraint
copy_transparency_fine_level_check CHECK ((fine_level = ANY (ARRAY[1, 2, 3])))
copy_transparency_loan_duration_check CHECK ((loan_duration = ANY (ARRAY[1, 2, 3])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema asset


Table: asset.copy_transparency_map

asset.copy_transparency_map Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
asset.copy_transparency.id transparency integer NOT NULL
asset.copy.id target_copy integer UNIQUE NOT NULL
cp_tr_cp_idx transparency

Index - Schema asset


Table: asset.stat_cat

asset.stat_cat Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
opac_visible boolean NOT NULL DEFAULT false
name text UNIQUE#1 NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema asset


Table: asset.stat_cat_entry

asset.stat_cat_entry Structure
F-Key Name Type Description
id serial PRIMARY KEY
asset.stat_cat.id stat_cat integer UNIQUE#1 NOT NULL
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
value text UNIQUE#1 NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema asset


Table: asset.stat_cat_entry_copy_map

asset.stat_cat_entry_copy_map Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
asset.stat_cat.id stat_cat integer UNIQUE#1 NOT NULL
asset.stat_cat_entry.id stat_cat_entry integer NOT NULL
asset.copy.id owning_copy bigint UNIQUE#1 NOT NULL

Index - Schema asset


Table: asset.stat_cat_entry_transparency_map

asset.stat_cat_entry_transparency_map Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
stat_cat integer UNIQUE#1 NOT NULL
stat_cat_entry integer NOT NULL
owning_transparency integer UNIQUE#1 NOT NULL

Index - Schema asset


Table: asset.uri

asset.uri Structure
F-Key Name Type Description
id serial PRIMARY KEY
href text NOT NULL
label text
use_restriction text
active boolean NOT NULL DEFAULT true

Tables referencing this one via Foreign Key Constraints:

Index - Schema asset


Table: asset.uri_call_number_map

asset.uri_call_number_map Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
asset.uri.id uri integer UNIQUE#1 NOT NULL
asset.call_number.id call_number integer UNIQUE#1 NOT NULL
asset_uri_call_number_map_cn_idx call_number

Index - Schema asset


Function: asset.merge_record_assets(source_record bigint, target_record bigint)

Returns: integer

Language: PLPGSQL

DECLARE
	moved_objects INT := 0;
	source_cn     asset.call_number%ROWTYPE;
	target_cn     asset.call_number%ROWTYPE;
	metarec       metabib.metarecord%ROWTYPE;
	hold          action.hold_request%ROWTYPE;
	ser_rec       serial.record_entry%ROWTYPE;
    uri_count     INT := 0;
    counter       INT := 0;
    uri_datafield TEXT;
    uri_text      TEXT := '';
BEGIN

    -- move any 856 entries on records that have at least one MARC-mapped URI entry
    SELECT  INTO uri_count COUNT(*)
      FROM  asset.uri_call_number_map m
            JOIN asset.call_number cn ON (m.call_number = cn.id)
      WHERE cn.record = source_record;

    IF uri_count > 0 THEN
        
        SELECT  COUNT(*) INTO counter
          FROM  xpath_table(
                    'id',
                    'marc',
                    'acq.lineitem',
                    '//*[@tag="856"]',
                    'id=' || lineitem
                ) as t(i int,c text);
    
        FOR i IN 1 .. counter LOOP
            SELECT  '<datafield xmlns="http://www.loc.gov/MARC21/slim" tag="856">' ||
                        array_to_string(
                            array_accum(
                                '<subfield code="' || subfield || '">' ||
                                regexp_replace(
                                    regexp_replace(
                                        regexp_replace(data,'&','&amp;','g'),
                                        '>', '&gt;', 'g'
                                    ),
                                    '<', '&lt;', 'g'
                                ) || '</subfield>'
                            ), ''
                        ) || '</datafield>' INTO uri_datafield
              FROM  xpath_table(
                        'id',
                        'marc',
                        'biblio.record_entry',
                        '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
                        '//*[@tag="856"][position()=' || i || ']/*[@code]',
                        'id=' || source_record
                    ) as t(id int,subfield text,data text);

            uri_text := uri_text || uri_datafield;
        END LOOP;

        IF uri_text <> '' THEN
            UPDATE  biblio.record_entry
              SET   marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
              WHERE id = target_record;
        END IF;

    END IF;

	-- Find and move metarecords to the target record
	SELECT	INTO metarec *
	  FROM	metabib.metarecord
	  WHERE	master_record = source_record;

	IF FOUND THEN
		UPDATE	metabib.metarecord
		  SET	master_record = target_record,
			mods = NULL
		  WHERE	id = metarec.id;

		moved_objects := moved_objects + 1;
	END IF;

	-- Find call numbers attached to the source ...
	FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP

		SELECT	INTO target_cn *
		  FROM	asset.call_number
		  WHERE	label = source_cn.label
			AND owning_lib = source_cn.owning_lib
			AND record = target_record;

		-- ... and if there's a conflicting one on the target ...
		IF FOUND THEN

			-- ... move the copies to that, and ...
			UPDATE	asset.copy
			  SET	call_number = target_cn.id
			  WHERE	call_number = source_cn.id;

			-- ... move V holds to the move-target call number
			FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
		
				UPDATE	action.hold_request
				  SET	target = target_cn.id
				  WHERE	id = hold.id;
		
				moved_objects := moved_objects + 1;
			END LOOP;

		-- ... if not ...
		ELSE
			-- ... just move the call number to the target record
			UPDATE	asset.call_number
			  SET	record = target_record
			  WHERE	id = source_cn.id;
		END IF;

		moved_objects := moved_objects + 1;
	END LOOP;

	-- Find T holds targeting the source record ...
	FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP

		-- ... and move them to the target record
		UPDATE	action.hold_request
		  SET	target = target_record
		  WHERE	id = hold.id;

		moved_objects := moved_objects + 1;
	END LOOP;

	-- Find serial records targeting the source record ...
	FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
		-- ... and move them to the target record
		UPDATE	serial.record_entry
		  SET	record = target_record
		  WHERE	id = ser_rec.id;

		moved_objects := moved_objects + 1;
	END LOOP;

    -- Finally, "delete" the source record
    DELETE FROM biblio.record_entry WHERE id = source_record;

	-- That's all, folks!
	RETURN moved_objects;
END;

Schema auditor


Table: auditor.actor_org_unit_history

auditor.actor_org_unit_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
id integer NOT NULL
parent_ou integer
ou_type integer NOT NULL
ill_address integer
holds_address integer
mailing_address integer
billing_address integer
shortname text NOT NULL
name text NOT NULL
email text
phone text
opac_visible boolean NOT NULL

Index - Schema auditor


View: auditor.actor_org_unit_lifecycle

auditor.actor_org_unit_lifecycle Structure
F-Key Name Type Description
?column? bigint
audit_time timestamp with time zone
audit_action text
id integer
parent_ou integer
ou_type integer
ill_address integer
holds_address integer
mailing_address integer
billing_address integer
shortname text
name text
email text
phone text
opac_visible boolean
SELECT (-1)
, now
() AS audit_time
,'-' AS audit_action
, org_unit.id
, org_unit.parent_ou
, org_unit.ou_type
, org_unit.ill_address
, org_unit.holds_address
, org_unit.mailing_address
, org_unit.billing_address
, org_unit.shortname
, org_unit.name
, org_unit.email
, org_unit.phone
, org_unit.opac_visible 
FROM actor.org_unit 
UNION ALLSELECT actor_org_unit_history.audit_id AS "?column?"
, actor_org_unit_history.audit_time
, actor_org_unit_history.audit_action
, actor_org_unit_history.id
, actor_org_unit_history.parent_ou
, actor_org_unit_history.ou_type
, actor_org_unit_history.ill_address
, actor_org_unit_history.holds_address
, actor_org_unit_history.mailing_address
, actor_org_unit_history.billing_address
, actor_org_unit_history.shortname
, actor_org_unit_history.name
, actor_org_unit_history.email
, actor_org_unit_history.phone
, actor_org_unit_history.opac_visible 
FROM auditor.actor_org_unit_history;

Index - Schema auditor


Table: auditor.actor_usr_address_history

auditor.actor_usr_address_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
id integer NOT NULL
valid boolean NOT NULL
within_city_limits boolean NOT NULL
address_type text NOT NULL
usr integer NOT NULL
street1 text NOT NULL
street2 text
city text NOT NULL
county text
state text NOT NULL
country text NOT NULL
post_code text NOT NULL
pending boolean NOT NULL
replaces integer

Index - Schema auditor


View: auditor.actor_usr_address_lifecycle

auditor.actor_usr_address_lifecycle Structure
F-Key Name Type Description
?column? bigint
audit_time timestamp with time zone
audit_action text
id integer
valid boolean
within_city_limits boolean
address_type text
usr integer
street1 text
street2 text
city text
county text
state text
country text
post_code text
pending boolean
replaces integer
SELECT (-1)
, now
() AS audit_time
,'-' AS audit_action
, usr_address.id
, usr_address.valid
, usr_address.within_city_limits
, usr_address.address_type
, usr_address.usr
, usr_address.street1
, usr_address.street2
, usr_address.city
, usr_address.county
, usr_address.state
, usr_address.country
, usr_address.post_code
, usr_address.pending
, usr_address.replaces 
FROM actor.usr_address 
UNION ALLSELECT actor_usr_address_history.audit_id AS "?column?"
, actor_usr_address_history.audit_time
, actor_usr_address_history.audit_action
, actor_usr_address_history.id
, actor_usr_address_history.valid
, actor_usr_address_history.within_city_limits
, actor_usr_address_history.address_type
, actor_usr_address_history.usr
, actor_usr_address_history.street1
, actor_usr_address_history.street2
, actor_usr_address_history.city
, actor_usr_address_history.county
, actor_usr_address_history.state
, actor_usr_address_history.country
, actor_usr_address_history.post_code
, actor_usr_address_history.pending
, actor_usr_address_history.replaces 
FROM auditor.actor_usr_address_history;

Index - Schema auditor


Table: auditor.actor_usr_history

auditor.actor_usr_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
id integer NOT NULL
card integer
profile integer NOT NULL
usrname text NOT NULL
email text
passwd text NOT NULL
standing integer NOT NULL
ident_type integer NOT NULL
ident_value text
ident_type2 integer
ident_value2 text
net_access_level integer NOT NULL
photo_url text
prefix text
first_given_name text NOT NULL
second_given_name text
family_name text NOT NULL
suffix text
alias text
day_phone text
evening_phone text
other_phone text
mailing_address integer
billing_address integer
home_ou integer NOT NULL
dob timestamp with time zone
active boolean NOT NULL
master_account boolean NOT NULL
super_user boolean NOT NULL
barred boolean NOT NULL
deleted boolean NOT NULL
juvenile boolean NOT NULL
usrgroup integer NOT NULL
claims_returned_count integer NOT NULL
credit_forward_balance numeric(6,2) NOT NULL
last_xact_id text NOT NULL
alert_message text
create_date timestamp with time zone NOT NULL
expire_date timestamp with time zone NOT NULL

Index - Schema auditor


View: auditor.actor_usr_lifecycle

auditor.actor_usr_lifecycle Structure
F-Key Name Type Description
?column? bigint
audit_time timestamp with time zone
audit_action text
id integer
card integer
profile integer
usrname text
email text
passwd text
standing integer
ident_type integer
ident_value text
ident_type2 integer
ident_value2 text
net_access_level integer
photo_url text
prefix text
first_given_name text
second_given_name text
family_name text
suffix text
alias text
day_phone text
evening_phone text
other_phone text
mailing_address integer
billing_address integer
home_ou integer
dob timestamp with time zone
active boolean
master_account boolean
super_user boolean
barred boolean
deleted boolean
juvenile boolean
usrgroup integer
claims_returned_count integer
credit_forward_balance numeric(6,2)
last_xact_id text
alert_message text
create_date timestamp with time zone
expire_date timestamp with time zone
SELECT (-1)
, now
() AS audit_time
,'-' AS audit_action
, usr.id
, usr.card
, usr.profile
, usr.usrname
, usr.email
, usr.passwd
, usr.standing
, usr.ident_type
, usr.ident_value
, usr.ident_type2
, usr.ident_value2
, usr.net_access_level
, usr.photo_url
, usr.prefix
, usr.first_given_name
, usr.second_given_name
, usr.family_name
, usr.suffix
, usr.alias
, usr.day_phone
, usr.evening_phone
, usr.other_phone
, usr.mailing_address
, usr.billing_address
, usr.home_ou
, usr.dob
, usr.active
, usr.master_account
, usr.super_user
, usr.barred
, usr.deleted
, usr.juvenile
, usr.usrgroup
, usr.claims_returned_count
, usr.credit_forward_balance
, usr.last_xact_id
, usr.alert_message
, usr.create_date
, usr.expire_date 
FROM actor.usr 
UNION ALLSELECT actor_usr_history.audit_id AS "?column?"
, actor_usr_history.audit_time
, actor_usr_history.audit_action
, actor_usr_history.id
, actor_usr_history.card
, actor_usr_history.profile
, actor_usr_history.usrname
, actor_usr_history.email
, actor_usr_history.passwd
, actor_usr_history.standing
, actor_usr_history.ident_type
, actor_usr_history.ident_value
, actor_usr_history.ident_type2
, actor_usr_history.ident_value2
, actor_usr_history.net_access_level
, actor_usr_history.photo_url
, actor_usr_history.prefix
, actor_usr_history.first_given_name
, actor_usr_history.second_given_name
, actor_usr_history.family_name
, actor_usr_history.suffix
, actor_usr_history.alias
, actor_usr_history.day_phone
, actor_usr_history.evening_phone
, actor_usr_history.other_phone
, actor_usr_history.mailing_address
, actor_usr_history.billing_address
, actor_usr_history.home_ou
, actor_usr_history.dob
, actor_usr_history.active
, actor_usr_history.master_account
, actor_usr_history.super_user
, actor_usr_history.barred
, actor_usr_history.deleted
, actor_usr_history.juvenile
, actor_usr_history.usrgroup
, actor_usr_history.claims_returned_count
, actor_usr_history.credit_forward_balance
, actor_usr_history.last_xact_id
, actor_usr_history.alert_message
, actor_usr_history.create_date
, actor_usr_history.expire_date 
FROM auditor.actor_usr_history;

Index - Schema auditor


Table: auditor.asset_call_number_history

auditor.asset_call_number_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
id bigint NOT NULL
creator bigint NOT NULL
create_date timestamp with time zone
editor bigint NOT NULL
edit_date timestamp with time zone
record bigint NOT NULL
owning_lib integer NOT NULL
label text NOT NULL
deleted boolean NOT NULL

Index - Schema auditor


View: auditor.asset_call_number_lifecycle

auditor.asset_call_number_lifecycle Structure
F-Key Name Type Description
?column? bigint
audit_time timestamp with time zone
audit_action text
id bigint
creator bigint
create_date timestamp with time zone
editor bigint
edit_date timestamp with time zone
record bigint
owning_lib integer
label text
deleted boolean
SELECT (-1)
, now
() AS audit_time
,'-' AS audit_action
, call_number.id
, call_number.creator
, call_number.create_date
, call_number.editor
, call_number.edit_date
, call_number.record
, call_number.owning_lib
, call_number.label
, call_number.deleted 
FROM asset.call_number 
UNION ALLSELECT asset_call_number_history.audit_id AS "?column?"
, asset_call_number_history.audit_time
, asset_call_number_history.audit_action
, asset_call_number_history.id
, asset_call_number_history.creator
, asset_call_number_history.create_date
, asset_call_number_history.editor
, asset_call_number_history.edit_date
, asset_call_number_history.record
, asset_call_number_history.owning_lib
, asset_call_number_history.label
, asset_call_number_history.deleted 
FROM auditor.asset_call_number_history;

Index - Schema auditor


Table: auditor.asset_copy_history

auditor.asset_copy_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
id bigint NOT NULL
circ_lib integer NOT NULL
creator bigint NOT NULL
call_number bigint NOT NULL
editor bigint NOT NULL
create_date timestamp with time zone
edit_date timestamp with time zone
copy_number integer
status integer NOT NULL
location integer NOT NULL
loan_duration integer NOT NULL
fine_level integer NOT NULL
age_protect integer
circulate boolean NOT NULL
deposit boolean NOT NULL
ref boolean NOT NULL
holdable boolean NOT NULL
deposit_amount numeric(6,2) NOT NULL
price numeric(8,2)
barcode text NOT NULL
circ_modifier text
circ_as_type text
dummy_title text
dummy_author text
alert_message text
opac_visible boolean NOT NULL
deleted boolean NOT NULL

Index - Schema auditor


View: auditor.asset_copy_lifecycle

auditor.asset_copy_lifecycle Structure
F-Key Name Type Description
?column? bigint
audit_time timestamp with time zone
audit_action text
id bigint
circ_lib integer
creator bigint
call_number bigint
editor bigint
create_date timestamp with time zone
edit_date timestamp with time zone
copy_number integer
status integer
location integer
loan_duration integer
fine_level integer
age_protect integer
circulate boolean
deposit boolean
ref boolean
holdable boolean
deposit_amount numeric(6,2)
price numeric(8,2)
barcode text
circ_modifier text
circ_as_type text
dummy_title text
dummy_author text
alert_message text
opac_visible boolean
deleted boolean
SELECT (-1)
, now
() AS audit_time
,'-' AS audit_action
, copy.id
, copy.circ_lib
, copy.creator
, copy.call_number
, copy.editor
, copy.create_date
, copy.edit_date
, copy.copy_number
, copy.status
, copy.location
, copy.loan_duration
, copy.fine_level
, copy.age_protect
, copy.circulate
, copy.deposit
, copy.ref
, copy.holdable
, copy.deposit_amount
, copy.price
, copy.barcode
, copy.circ_modifier
, copy.circ_as_type
, copy.dummy_title
, copy.dummy_author
, copy.alert_message
, copy.opac_visible
, copy.deleted 
FROM asset.copy 
UNION ALLSELECT asset_copy_history.audit_id AS "?column?"
, asset_copy_history.audit_time
, asset_copy_history.audit_action
, asset_copy_history.id
, asset_copy_history.circ_lib
, asset_copy_history.creator
, asset_copy_history.call_number
, asset_copy_history.editor
, asset_copy_history.create_date
, asset_copy_history.edit_date
, asset_copy_history.copy_number
, asset_copy_history.status
, asset_copy_history.location
, asset_copy_history.loan_duration
, asset_copy_history.fine_level
, asset_copy_history.age_protect
, asset_copy_history.circulate
, asset_copy_history.deposit
, asset_copy_history.ref
, asset_copy_history.holdable
, asset_copy_history.deposit_amount
, asset_copy_history.price
, asset_copy_history.barcode
, asset_copy_history.circ_modifier
, asset_copy_history.circ_as_type
, asset_copy_history.dummy_title
, asset_copy_history.dummy_author
, asset_copy_history.alert_message
, asset_copy_history.opac_visible
, asset_copy_history.deleted 
FROM auditor.asset_copy_history;

Index - Schema auditor


Table: auditor.biblio_record_entry_history

auditor.biblio_record_entry_history Structure
F-Key Name Type Description
audit_id bigint PRIMARY KEY
audit_time timestamp with time zone NOT NULL
audit_action text NOT NULL
id bigint NOT NULL
creator integer NOT NULL
editor integer NOT NULL
source integer
quality integer
create_date timestamp with time zone NOT NULL
edit_date timestamp with time zone NOT NULL
active boolean NOT NULL
deleted boolean NOT NULL
fingerprint text
tcn_source text NOT NULL
tcn_value text NOT NULL
marc text NOT NULL
last_xact_id text NOT NULL

Index - Schema auditor


View: auditor.biblio_record_entry_lifecycle

auditor.biblio_record_entry_lifecycle Structure
F-Key Name Type Description
?column? bigint
audit_time timestamp with time zone
audit_action text
id bigint
creator integer
editor integer
source integer
quality integer
create_date timestamp with time zone
edit_date timestamp with time zone
active boolean
deleted boolean
fingerprint text
tcn_source text
tcn_value text
marc text
last_xact_id text
SELECT (-1)
, now
() AS audit_time
,'-' AS audit_action
, record_entry.id
, record_entry.creator
, record_entry.editor
, record_entry.source
, record_entry.quality
, record_entry.create_date
, record_entry.edit_date
, record_entry.active
, record_entry.deleted
, record_entry.fingerprint
, record_entry.tcn_source
, record_entry.tcn_value
, record_entry.marc
, record_entry.last_xact_id 
FROM biblio.record_entry 
UNION ALLSELECT biblio_record_entry_history.audit_id AS "?column?"
, biblio_record_entry_history.audit_time
, biblio_record_entry_history.audit_action
, biblio_record_entry_history.id
, biblio_record_entry_history.creator
, biblio_record_entry_history.editor
, biblio_record_entry_history.source
, biblio_record_entry_history.quality
, biblio_record_entry_history.create_date
, biblio_record_entry_history.edit_date
, biblio_record_entry_history.active
, biblio_record_entry_history.deleted
, biblio_record_entry_history.fingerprint
, biblio_record_entry_history.tcn_source
, biblio_record_entry_history.tcn_value
, biblio_record_entry_history.marc
, biblio_record_entry_history.last_xact_id 
FROM auditor.biblio_record_entry_history;

Index - Schema auditor


Function: auditor.audit_actor_org_unit_func()

Returns: trigger

Language: PLPGSQL

			BEGIN
				INSERT INTO auditor.actor_org_unit_history
					SELECT	nextval('auditor.actor_org_unit_pkey_seq'),
						now(),
						SUBSTR(TG_OP,1,1),
						OLD.*;
				RETURN NULL;
			END;
			

Function: auditor.audit_actor_usr_address_func()

Returns: trigger

Language: PLPGSQL

			BEGIN
				INSERT INTO auditor.actor_usr_address_history
					SELECT	nextval('auditor.actor_usr_address_pkey_seq'),
						now(),
						SUBSTR(TG_OP,1,1),
						OLD.*;
				RETURN NULL;
			END;
			

Function: auditor.audit_actor_usr_func()

Returns: trigger

Language: PLPGSQL

			BEGIN
				INSERT INTO auditor.actor_usr_history
					SELECT	nextval('auditor.actor_usr_pkey_seq'),
						now(),
						SUBSTR(TG_OP,1,1),
						OLD.*;
				RETURN NULL;
			END;
			

Function: auditor.audit_asset_call_number_func()

Returns: trigger

Language: PLPGSQL

			BEGIN
				INSERT INTO auditor.asset_call_number_history
					SELECT	nextval('auditor.asset_call_number_pkey_seq'),
						now(),
						SUBSTR(TG_OP,1,1),
						OLD.*;
				RETURN NULL;
			END;
			

Function: auditor.audit_asset_copy_func()

Returns: trigger

Language: PLPGSQL

			BEGIN
				INSERT INTO auditor.asset_copy_history
					SELECT	nextval('auditor.asset_copy_pkey_seq'),
						now(),
						SUBSTR(TG_OP,1,1),
						OLD.*;
				RETURN NULL;
			END;
			

Function: auditor.audit_biblio_record_entry_func()

Returns: trigger

Language: PLPGSQL

			BEGIN
				INSERT INTO auditor.biblio_record_entry_history
					SELECT	nextval('auditor.biblio_record_entry_pkey_seq'),
						now(),
						SUBSTR(TG_OP,1,1),
						OLD.*;
				RETURN NULL;
			END;
			

Function: auditor.create_auditor(tbl text, sch text)

Returns: boolean

Language: PLPGSQL

BEGIN
	EXECUTE $$
			CREATE SEQUENCE auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq;
	$$;

	EXECUTE $$
			CREATE TABLE auditor.$$ || sch || $$_$$ || tbl || $$_history (
				audit_id	BIGINT				PRIMARY KEY,
				audit_time	TIMESTAMP WITH TIME ZONE	NOT NULL,
				audit_action	TEXT				NOT NULL,
				LIKE $$ || sch || $$.$$ || tbl || $$
			);
	$$;

	EXECUTE $$
			CREATE FUNCTION auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ()
			RETURNS TRIGGER AS $func$
			BEGIN
				INSERT INTO auditor.$$ || sch || $$_$$ || tbl || $$_history
					SELECT	nextval('auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq'),
						now(),
						SUBSTR(TG_OP,1,1),
						OLD.*;
				RETURN NULL;
			END;
			$func$ LANGUAGE 'plpgsql';
	$$;

	EXECUTE $$
			CREATE TRIGGER audit_$$ || sch || $$_$$ || tbl || $$_update_trigger
				AFTER UPDATE OR DELETE ON $$ || sch || $$.$$ || tbl || $$ FOR EACH ROW
				EXECUTE PROCEDURE auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ();
	$$;

	EXECUTE $$
			CREATE VIEW auditor.$$ || sch || $$_$$ || tbl || $$_lifecycle AS
				SELECT	-1, now() as audit_time, '-' as audit_action, *
				  FROM	$$ || sch || $$.$$ || tbl || $$
				  	UNION ALL
				SELECT	*
				  FROM	auditor.$$ || sch || $$_$$ || tbl || $$_history;
	$$;
	RETURN TRUE;
END;

Schema authority


Table: authority.full_rec

authority.full_rec Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
record bigint NOT NULL
tag character(3) NOT NULL
ind1 text
ind2 text
subfield text
value text NOT NULL
index_vector tsvector NOT NULL
authority_full_rec_index_vector_idx index_vector authority_full_rec_record_idx record authority_full_rec_tag_part_idx "substring"((tag)::text, 2) authority_full_rec_tag_subfield_idx tag, subfield authority_full_rec_value_tpo_index value text_pattern_ops

Index - Schema authority


Table: authority.rec_descriptor

authority.rec_descriptor Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
record bigint
record_status text
char_encoding text
authority_rec_descriptor_record_idx record

Index - Schema authority


Table: authority.record_entry

authority.record_entry Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
arn_source text NOT NULL DEFAULT 'AUTOGEN'::text
arn_value text NOT NULL
creator integer NOT NULL DEFAULT 1
editor integer NOT NULL DEFAULT 1
create_date timestamp with time zone NOT NULL DEFAULT now()
edit_date timestamp with time zone NOT NULL DEFAULT now()
active boolean NOT NULL DEFAULT true
deleted boolean NOT NULL DEFAULT false
source integer
marc text NOT NULL
last_xact_id text NOT NULL

Tables referencing this one via Foreign Key Constraints:

authority_record_entry_creator_idx creator authority_record_entry_editor_idx editor

Index - Schema authority


Table: authority.record_note

authority.record_note Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
authority.record_entry.id record bigint NOT NULL
value text NOT NULL
creator integer NOT NULL DEFAULT 1
editor integer NOT NULL DEFAULT 1
create_date timestamp with time zone NOT NULL DEFAULT now()
edit_date timestamp with time zone NOT NULL DEFAULT now()
authority_record_note_creator_idx creator authority_record_note_editor_idx editor authority_record_note_record_idx record

Index - Schema authority


View: authority.tracing_links

authority.tracing_links Structure
F-Key Name Type Description
record bigint
main_id bigint
main_tag character(3)
main_value text
relationship text
use_restriction text
deprecation text
display_restriction text
link_id bigint
link_tag character(3)
link_value text
SELECT main.record
, main.id AS main_id
, main.tag AS main_tag
, main.value AS main_value
, substr
(link.value
     , 1
     , 1
) AS relationship
, substr
(link.value
     , 2
     , 1
) AS use_restriction
, substr
(link.value
     , 3
     , 1
) AS deprecation
, substr
(link.value
     , 4
     , 1
) AS display_restriction
, link_value.id AS link_id
, link_value.tag AS link_tag
, link_value.value AS link_value 
FROM (
     (authority.full_rec main 
        JOIN authority.full_rec link 
          ON (
                 (
                       (
                             (link.record = main.record)
                           AND (
                                   (
                                         (link.tag)::text = 
                                         (
                                               (
                                                     (main.tag)::integer + 400
                                               )
                                         )::text
                                   )
                                  OR (
                                         (link.tag)::text = 
                                         (
                                               (
                                                     (main.tag)::integer + 300
                                               )
                                         )::text
                                   )
                             )
                       )
                     AND (link.subfield = 'w'::text)
                 )
           )
     )
  JOIN authority.full_rec link_value 
    ON (
           (
                 (
                       (link_value.record = main.record)
                     AND (link_value.tag = link.tag)
                 )
               AND (link_value.subfield = 'a'::text)
           )
     )
)
WHERE (
     (main.tag = ANY 
           (ARRAY['100'::bpchar
                 ,'110'::bpchar
                 ,'111'::bpchar
                 ,'130'::bpchar
                 ,'150'::bpchar
                 ,'151'::bpchar
                 ,'155'::bpchar
                 ,'180'::bpchar
                 ,'181'::bpchar
                 ,'182'::bpchar
                 ,'185'::bpchar]
           )
     )
   AND (main.subfield = 'a'::text)
);

Index - Schema authority


Schema biblio


Table: biblio.record_entry

biblio.record_entry Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id creator integer NOT NULL DEFAULT 1
actor.usr.id editor integer NOT NULL DEFAULT 1
source integer
quality integer
create_date timestamp with time zone NOT NULL DEFAULT now()
edit_date timestamp with time zone NOT NULL DEFAULT now()
active boolean NOT NULL DEFAULT true
deleted boolean NOT NULL DEFAULT false
fingerprint text
tcn_source text NOT NULL DEFAULT 'AUTOGEN'::text
tcn_value text NOT NULL DEFAULT biblio.next_autogen_tcn_value()
marc text NOT NULL
last_xact_id text NOT NULL

Tables referencing this one via Foreign Key Constraints:

biblio_record_entry_create_date_idx create_date biblio_record_entry_creator_idx creator biblio_record_entry_edit_date_idx edit_date biblio_record_entry_editor_idx editor biblio_record_entry_fp_idx fingerprint

Index - Schema biblio


Table: biblio.record_note

biblio.record_note Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
biblio.record_entry.id record bigint NOT NULL
value text NOT NULL
actor.usr.id creator integer NOT NULL DEFAULT 1
actor.usr.id editor integer NOT NULL DEFAULT 1
pub boolean NOT NULL DEFAULT false
create_date timestamp with time zone NOT NULL DEFAULT now()
edit_date timestamp with time zone NOT NULL DEFAULT now()
biblio_record_note_creator_idx creator biblio_record_note_editor_idx editor biblio_record_note_record_idx record

Index - Schema biblio


Function: biblio.next_autogen_tcn_value()

Returns: text

Language: PLPGSQL

	BEGIN RETURN 'AUTOGENERATED-' || nextval('biblio.autogen_tcn_value_seq'::TEXT); END;

Schema config

/* * Copyright (C) 2005 Georgia Public Library Service * Mike Rylander * * The config schema holds static configuration data for the * Open-ILS installation. * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */


Table: config.audience_map

config.audience_map Structure
F-Key Name Type Description
code text PRIMARY KEY
value text NOT NULL
description text

Index - Schema config


Table: config.bib_level_map

config.bib_level_map Structure
F-Key Name Type Description
code text PRIMARY KEY
value text NOT NULL

Index - Schema config


Table: config.bib_source

/* * Copyright (C) 2005 Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * Valid sources of MARC records * * This is table is used to set up the relative "quality" of each * MARC source, such as OCLC. * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

config.bib_source Structure
F-Key Name Type Description
id serial PRIMARY KEY
quality integer
source text UNIQUE NOT NULL
transcendant boolean NOT NULL DEFAULT false

 

config.bib_source Constraints
Name Constraint
bib_source_quality_check CHECK (((quality >= 0) AND (quality <= 100)))

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.billing_type

config.billing_type Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE#1 NOT NULL
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
default_price numeric(6,2)

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.circ_matrix_circ_mod_test

config.circ_matrix_circ_mod_test Structure
F-Key Name Type Description
id serial PRIMARY KEY
config.circ_matrix_matchpoint.id matchpoint integer NOT NULL
items_out integer NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.circ_matrix_circ_mod_test_map

config.circ_matrix_circ_mod_test_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
config.circ_matrix_circ_mod_test.id circ_mod_test integer UNIQUE#1 NOT NULL
config.circ_modifier.code circ_mod text UNIQUE#1 NOT NULL

Index - Schema config


Table: config.circ_matrix_matchpoint

config.circ_matrix_matchpoint Structure
F-Key Name Type Description
id serial PRIMARY KEY
active boolean NOT NULL DEFAULT true
actor.org_unit.id org_unit integer UNIQUE#1 NOT NULL
permission.grp_tree.id grp integer UNIQUE#1 NOT NULL
config.circ_modifier.code circ_modifier text UNIQUE#1
config.item_type_map.code marc_type text UNIQUE#1
config.item_form_map.code marc_form text UNIQUE#1
config.videorecording_format_map.code marc_vr_format text UNIQUE#1
ref_flag boolean UNIQUE#1
juvenile_flag boolean UNIQUE#1
is_renewal boolean UNIQUE#1
usr_age_lower_bound interval UNIQUE#1
usr_age_upper_bound interval UNIQUE#1
circulate boolean NOT NULL DEFAULT true
config.rule_circ_duration.id duration_rule integer NOT NULL
config.rule_recuring_fine.id recurring_fine_rule integer NOT NULL
config.rule_max_fine.id max_fine_rule integer NOT NULL
script_test text

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.circ_modifier

config.circ_modifier Structure
F-Key Name Type Description
code text PRIMARY KEY
name text UNIQUE NOT NULL
description text NOT NULL
sip2_media_type text NOT NULL
magnetic_media boolean NOT NULL DEFAULT true

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.copy_status

/* * Copyright (C) 2005 Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * Copy Statuses * * The available copy statuses, and whether a copy in that * status is available for hold request capture. 0 (zero) is * the only special number in this set, meaning that the item * is available for immediate checkout, and is counted as available * in the OPAC. * * Statuses with an ID below 100 are not removable, and have special * meaning in the code. Do not change them except to translate the * textual name. * * You may add and remove statuses above 100, and these can be used * to remove items from normal circulation without affecting the rest * of the copy's values or its location. * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

config.copy_status Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE NOT NULL
holdable boolean NOT NULL DEFAULT false
opac_visible boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.hold_matrix_matchpoint

config.hold_matrix_matchpoint Structure
F-Key Name Type Description
id serial PRIMARY KEY
active boolean NOT NULL DEFAULT true
actor.org_unit.id user_home_ou integer UNIQUE#1
actor.org_unit.id request_ou integer UNIQUE#1
actor.org_unit.id pickup_ou integer UNIQUE#1
actor.org_unit.id item_owning_ou integer UNIQUE#1
actor.org_unit.id item_circ_ou integer UNIQUE#1
permission.grp_tree.id usr_grp integer UNIQUE#1
permission.grp_tree.id requestor_grp integer UNIQUE#1 NOT NULL
config.circ_modifier.code circ_modifier text UNIQUE#1
config.item_type_map.code marc_type text UNIQUE#1
config.item_form_map.code marc_form text UNIQUE#1
config.videorecording_format_map.code marc_vr_format text UNIQUE#1
juvenile_flag boolean UNIQUE#1
ref_flag boolean UNIQUE#1
holdable boolean NOT NULL DEFAULT true
distance_is_from_owner boolean NOT NULL DEFAULT false
actor.org_unit_type.id transit_range integer
max_holds integer
include_frozen_holds boolean NOT NULL DEFAULT true
stop_blocked_user boolean NOT NULL DEFAULT false
config.rule_age_hold_protect.id age_hold_protect_rule integer

Index - Schema config


Table: config.i18n_core

config.i18n_core Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
fq_field text NOT NULL
identity_value text NOT NULL
config.i18n_locale.code translation text NOT NULL
string text NOT NULL

Index - Schema config


Table: config.i18n_locale

config.i18n_locale Structure
F-Key Name Type Description
code text PRIMARY KEY
config.language_map.code marc_code text NOT NULL
name text UNIQUE NOT NULL
description text

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.identification_type

/* * Copyright (C) 2005 Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * Types of valid patron identification. * * Each patron must display at least one valid form of identification * in order to get a library card. This table lists those forms. * * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

config.identification_type Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.idl_field_doc

config.idl_field_doc Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
fm_class text NOT NULL
field text NOT NULL
actor.org_unit.id owner integer NOT NULL
string text NOT NULL

Index - Schema config


Table: config.item_form_map

config.item_form_map Structure
F-Key Name Type Description
code text PRIMARY KEY
value text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.item_type_map

config.item_type_map Structure
F-Key Name Type Description
code text PRIMARY KEY
value text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.language_map

config.language_map Structure
F-Key Name Type Description
code text PRIMARY KEY
value text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.lit_form_map

config.lit_form_map Structure
F-Key Name Type Description
code text PRIMARY KEY
value text NOT NULL
description text

Index - Schema config


Table: config.metabib_field

/* * Copyright (C) 2005 Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * XPath used for record indexing ingest * * This table contains the XPath used to chop up MODS into its * indexable parts. Each XPath entry is named and assigned to * a "class" of either title, subject, author, keyword or series. * * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

config.metabib_field Structure
F-Key Name Type Description
id serial PRIMARY KEY
field_class text NOT NULL
name text NOT NULL
xpath text NOT NULL
weight integer NOT NULL DEFAULT 1
format text NOT NULL DEFAULT 'mods33'::text
search_field boolean NOT NULL DEFAULT true
facet_field boolean NOT NULL DEFAULT false

 

config.metabib_field Constraints
Name Constraint
metabib_field_field_class_check CHECK ((lower(field_class) = ANY (ARRAY['title'::text, 'author'::text, 'subject'::text, 'keyword'::text, 'series'::text])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.net_access_level

/* * Copyright (C) 2005 Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * Patron Network Access level * * This will be used to inform the in-library firewall of how much * internet access the using patron should be allowed. * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

config.net_access_level Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.non_cataloged_type

/* * Copyright (C) 2005 Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * Types of valid non-cataloged items. * * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

config.non_cataloged_type Structure
F-Key Name Type Description
id serial PRIMARY KEY
owning_lib integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
circ_duration interval NOT NULL DEFAULT '14 days'::interval
in_house boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.rule_age_hold_protect

/* * Copyright (C) 2005 Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * Hold Item Age Protection rules * * A hold request can only capture new(ish) items when they are * within a particular proximity of the home_ou of the requesting * user. The proximity ('prox' column) is calculated by counting * the number of tree edges between the user's home_ou and the owning_lib * of the copy that could fulfill the hold. * * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

config.rule_age_hold_protect Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE NOT NULL
age interval NOT NULL
prox integer NOT NULL

 

config.rule_age_hold_protect Constraints
Name Constraint
rule_age_hold_protect_name_check CHECK ((name ~ '^\\w+$'::text))

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.rule_circ_duration

/* * Copyright (C) 2005 Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * Circulation Duration rules * * Each circulation is given a duration based on one of these rules. * * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

config.rule_circ_duration Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE NOT NULL
extended interval NOT NULL
normal interval NOT NULL
shrt interval NOT NULL
max_renewals integer NOT NULL

 

config.rule_circ_duration Constraints
Name Constraint
rule_circ_duration_name_check CHECK ((name ~ '^\\w+$'::text))

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.rule_max_fine

/* * Copyright (C) 2005 Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * Circulation Max Fine rules * * Each circulation is given a maximum fine based on one of * these rules. * * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

config.rule_max_fine Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE NOT NULL
amount numeric(6,2) NOT NULL
is_percent boolean NOT NULL DEFAULT false

 

config.rule_max_fine Constraints
Name Constraint
rule_max_fine_name_check CHECK ((name ~ '^\\w+$'::text))

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.rule_recuring_fine

/* * Copyright (C) 2005 Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * Circulation Recurring Fine rules * * Each circulation is given a recurring fine amount based on one of * these rules. The recurance_interval should not be any shorter * than the interval between runs of the fine_processor.pl script * (which is run from CRON), or you could miss fines. * * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

config.rule_recuring_fine Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE NOT NULL
high numeric(6,2) NOT NULL
normal numeric(6,2) NOT NULL
low numeric(6,2) NOT NULL
recurance_interval interval NOT NULL DEFAULT '1 day'::interval

 

config.rule_recuring_fine Constraints
Name Constraint
rule_recuring_fine_name_check CHECK ((name ~ '^\\w+$'::text))

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.standing

/* * Copyright (C) 2005 Georgia Public Library Service * Mike Rylander <mrylander@gmail.com> * * Patron Standings * * This table contains the values that can be applied to a patron * by a staff member. These values should not be changed, other * than for translation, as the ID column is currently a "magic * number" in the source. :( * * **** * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. */

config.standing Structure
F-Key Name Type Description
id serial PRIMARY KEY
value text UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.standing_penalty

config.standing_penalty Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE NOT NULL
label text NOT NULL
block_list text

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.upgrade_log

config.upgrade_log Structure
F-Key Name Type Description
version text PRIMARY KEY
install_date timestamp with time zone NOT NULL DEFAULT now()

Index - Schema config


Table: config.videorecording_format_map

config.videorecording_format_map Structure
F-Key Name Type Description
code text PRIMARY KEY
value text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Table: config.xml_transform

config.xml_transform Structure
F-Key Name Type Description
name text PRIMARY KEY
namespace_uri text NOT NULL
prefix text NOT NULL
xslt text NOT NULL

Index - Schema config


Table: config.z3950_attr

config.z3950_attr Structure
F-Key Name Type Description
id serial PRIMARY KEY
config.z3950_source.name source text UNIQUE#1 NOT NULL
name text NOT NULL
label text NOT NULL
code integer UNIQUE#1 NOT NULL
format integer UNIQUE#1 NOT NULL
truncation integer NOT NULL

Index - Schema config


Table: config.z3950_source

config.z3950_source Structure
F-Key Name Type Description
name text PRIMARY KEY
label text UNIQUE NOT NULL
host text NOT NULL
port integer NOT NULL
db text NOT NULL
record_format text NOT NULL DEFAULT 'FI'::text
transmission_format text NOT NULL DEFAULT 'usmarc'::text
auth boolean NOT NULL DEFAULT true

Tables referencing this one via Foreign Key Constraints:

Index - Schema config


Schema container


Table: container.biblio_record_entry_bucket

container.biblio_record_entry_bucket Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
container.biblio_record_entry_bucket_type.code btype text UNIQUE#1 NOT NULL DEFAULT 'misc'::text
pub boolean NOT NULL DEFAULT false
create_time timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema container


Table: container.biblio_record_entry_bucket_item

container.biblio_record_entry_bucket_item Structure
F-Key Name Type Description
id serial PRIMARY KEY
container.biblio_record_entry_bucket.id bucket integer NOT NULL
biblio.record_entry.id target_biblio_record_entry integer NOT NULL
pos integer
create_time timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema container


Table: container.biblio_record_entry_bucket_item_note

container.biblio_record_entry_bucket_item_note Structure
F-Key Name Type Description
id serial PRIMARY KEY
container.biblio_record_entry_bucket_item.id item integer NOT NULL
note text NOT NULL

Index - Schema container


Table: container.biblio_record_entry_bucket_note

container.biblio_record_entry_bucket_note Structure
F-Key Name Type Description
id serial PRIMARY KEY
container.biblio_record_entry_bucket.id bucket integer NOT NULL
note text NOT NULL

Index - Schema container


Table: container.biblio_record_entry_bucket_type

container.biblio_record_entry_bucket_type Structure
F-Key Name Type Description
code text PRIMARY KEY
label text UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema container


Table: container.call_number_bucket

container.call_number_bucket Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
container.call_number_bucket_type.code btype text UNIQUE#1 NOT NULL DEFAULT 'misc'::text
pub boolean NOT NULL DEFAULT false
create_time timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema container


Table: container.call_number_bucket_item

container.call_number_bucket_item Structure
F-Key Name Type Description
id serial PRIMARY KEY
container.call_number_bucket.id bucket integer NOT NULL
asset.call_number.id target_call_number integer NOT NULL
pos integer
create_time timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema container


Table: container.call_number_bucket_item_note

container.call_number_bucket_item_note Structure
F-Key Name Type Description
id serial PRIMARY KEY
container.call_number_bucket_item.id item integer NOT NULL
note text NOT NULL

Index - Schema container


Table: container.call_number_bucket_note

container.call_number_bucket_note Structure
F-Key Name Type Description
id serial PRIMARY KEY
container.call_number_bucket.id bucket integer NOT NULL
note text NOT NULL

Index - Schema container


Table: container.call_number_bucket_type

container.call_number_bucket_type Structure
F-Key Name Type Description
code text PRIMARY KEY
label text UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema container


Table: container.copy_bucket

container.copy_bucket Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
container.copy_bucket_type.code btype text UNIQUE#1 NOT NULL DEFAULT 'misc'::text
pub boolean NOT NULL DEFAULT false
create_time timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema container


Table: container.copy_bucket_item

container.copy_bucket_item Structure
F-Key Name Type Description
id serial PRIMARY KEY
container.copy_bucket.id bucket integer NOT NULL
asset.copy.id target_copy integer NOT NULL
pos integer
create_time timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

copy_bucket_item_bucket_idx bucket

Index - Schema container


Table: container.copy_bucket_item_note

container.copy_bucket_item_note Structure
F-Key Name Type Description
id serial PRIMARY KEY
container.copy_bucket_item.id item integer NOT NULL
note text NOT NULL

Index - Schema container


Table: container.copy_bucket_note

container.copy_bucket_note Structure
F-Key Name Type Description
id serial PRIMARY KEY
container.copy_bucket.id bucket integer NOT NULL
note text NOT NULL

Index - Schema container


Table: container.copy_bucket_type

container.copy_bucket_type Structure
F-Key Name Type Description
code text PRIMARY KEY
label text UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema container


Table: container.user_bucket

container.user_bucket Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
container.user_bucket_type.code btype text UNIQUE#1 NOT NULL DEFAULT 'misc'::text
pub boolean NOT NULL DEFAULT false
create_time timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema container


Table: container.user_bucket_item

container.user_bucket_item Structure
F-Key Name Type Description
id serial PRIMARY KEY
container.user_bucket.id bucket integer NOT NULL
actor.usr.id target_user integer NOT NULL
pos integer
create_time timestamp with time zone NOT NULL DEFAULT now()

Tables referencing this one via Foreign Key Constraints:

Index - Schema container


Table: container.user_bucket_item_note

container.user_bucket_item_note Structure
F-Key Name Type Description
id serial PRIMARY KEY
container.user_bucket_item.id item integer NOT NULL
note text NOT NULL

Index - Schema container


Table: container.user_bucket_note

container.user_bucket_note Structure
F-Key Name Type Description
id serial PRIMARY KEY
container.user_bucket.id bucket integer NOT NULL
note text NOT NULL

Index - Schema container


Table: container.user_bucket_type

container.user_bucket_type Structure
F-Key Name Type Description
code text PRIMARY KEY
label text UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema container


Schema extend_reporter


View: extend_reporter.full_circ_count

extend_reporter.full_circ_count Structure
F-Key Name Type Description
id bigint
circ_count bigint
SELECT cp.id
, (COALESCE
     (sum
           (c.circ_count)
           , (0)::bigint
     ) + COALESCE
     (count
           (circ.id)
           , (0)::bigint
     )
) AS circ_count 
FROM (
     (asset.copy cp 
   LEFT JOIN extend_reporter.legacy_circ_count c 
       USING (id)
     )
LEFT JOIN action.all_circulation circ 
    ON (
           (circ.target_copy = cp.id)
     )
)
GROUP BY cp.id;

Index - Schema extend_reporter


View: extend_reporter.global_bibs_by_holding_update

extend_reporter.global_bibs_by_holding_update Structure
F-Key Name Type Description
id bigint
holding_update timestamp with time zone
update_type text
SELECT DISTINCT 
ON (x.id) x.id
, x.holding_update
, x.update_type 
FROM (
SELECT b.id
     , last
     (cp.create_date) AS holding_update
     ,'add' AS update_type 
  FROM (
           (biblio.record_entry b 
              JOIN asset.call_number cn 
                ON (
                       (cn.record = b.id)
                 )
           )
        JOIN asset.copy cp 
          ON (
                 (cp.call_number = cn.id)
           )
     )
 WHERE (
           (NOT cp.deleted)
         AND (b.id > 0)
     )
GROUP BY b.id 
 UNIONSELECT b.id
     , last
     (cp.edit_date) AS holding_update
     ,'delete' AS update_type 
  FROM (
           (biblio.record_entry b 
              JOIN asset.call_number cn 
                ON (
                       (cn.record = b.id)
                 )
           )
        JOIN asset.copy cp 
          ON (
                 (cp.call_number = cn.id)
           )
     )
 WHERE (cp.deleted 
         AND (b.id > 0)
     )
GROUP BY b.id
) x 
ORDER BY x.id
, x.holding_update;

Index - Schema extend_reporter


Table: extend_reporter.legacy_circ_count

extend_reporter.legacy_circ_count Structure
F-Key Name Type Description
asset.copy.id id bigint PRIMARY KEY
circ_count integer NOT NULL

Index - Schema extend_reporter


Schema metabib


Table: metabib.author_field_entry

metabib.author_field_entry Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
biblio.record_entry.id source bigint NOT NULL
config.metabib_field.id field integer NOT NULL
value text NOT NULL
index_vector tsvector NOT NULL
metabib_author_field_entry_index_vector_idx index_vector

Index - Schema metabib


View: metabib.full_rec

metabib.full_rec Structure
F-Key Name Type Description
id bigint
record bigint
tag character(3)
ind1 text
ind2 text
subfield text
value text
index_vector tsvector
SELECT real_full_rec.id
, real_full_rec.record
, real_full_rec.tag
, real_full_rec.ind1
, real_full_rec.ind2
, real_full_rec.subfield
,"substring"
(real_full_rec.value
     , 1
     , 1024
) AS value
, real_full_rec.index_vector 
FROM metabib.real_full_rec;

Index - Schema metabib


Table: metabib.keyword_field_entry

metabib.keyword_field_entry Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
biblio.record_entry.id source bigint NOT NULL
config.metabib_field.id field integer NOT NULL
value text NOT NULL
index_vector tsvector NOT NULL
metabib_keyword_field_entry_index_vector_idx index_vector

Index - Schema metabib


Table: metabib.metarecord

metabib.metarecord Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
fingerprint text NOT NULL
biblio.record_entry.id master_record bigint
mods text

Tables referencing this one via Foreign Key Constraints:

metabib_metarecord_fingerprint_idx fingerprint metabib_metarecord_master_record_idx master_record

Index - Schema metabib


Table: metabib.metarecord_source_map

metabib.metarecord_source_map Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
metabib.metarecord.id metarecord bigint NOT NULL
biblio.record_entry.id source bigint NOT NULL
metabib_metarecord_source_map_metarecord_idx metarecord metabib_metarecord_source_map_source_record_idx source

Index - Schema metabib


Table: metabib.real_full_rec

metabib.real_full_rec Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('metabib.full_rec_id_seq'::regclass)
biblio.record_entry.id record bigint NOT NULL
tag character(3) NOT NULL
ind1 text
ind2 text
subfield text
value text NOT NULL
index_vector tsvector NOT NULL
metabib_full_rec_index_vector_idx index_vector metabib_full_rec_record_idx record metabib_full_rec_tag_subfield_idx tag, subfield metabib_full_rec_value_idx "substring"(value, 1, 1024) metabib_full_rec_value_tpo_index "substring"(value, 1, 1024) text_pattern_ops

Index - Schema metabib


Table: metabib.rec_descriptor

metabib.rec_descriptor Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
biblio.record_entry.id record bigint
item_type text
item_form text
bib_level text
control_type text
char_encoding text
enc_level text
audience text
lit_form text
type_mat text
cat_form text
pub_status text
item_lang text
vr_format text
date1 text
date2 text
metabib_rec_descriptor_record_idx record

Index - Schema metabib


Table: metabib.series_field_entry

metabib.series_field_entry Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
source bigint NOT NULL
field integer NOT NULL
value text NOT NULL
index_vector tsvector NOT NULL
metabib_series_field_entry_index_vector_idx index_vector

Index - Schema metabib


Table: metabib.subject_field_entry

metabib.subject_field_entry Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
biblio.record_entry.id source bigint NOT NULL
config.metabib_field.id field integer NOT NULL
value text NOT NULL
index_vector tsvector NOT NULL
metabib_subject_field_entry_index_vector_idx index_vector

Index - Schema metabib


Table: metabib.title_field_entry

metabib.title_field_entry Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
biblio.record_entry.id source bigint NOT NULL
config.metabib_field.id field integer NOT NULL
value text NOT NULL
index_vector tsvector NOT NULL
metabib_title_field_entry_index_vector_idx index_vector

Index - Schema metabib


Schema money


Table: money.billable_xact

money.billable_xact Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id usr integer NOT NULL
xact_start timestamp with time zone NOT NULL DEFAULT now()
xact_finish timestamp with time zone
unrecovered boolean
m_b_x_open_xacts_idx usr

Index - Schema money


View: money.billable_xact_summary

money.billable_xact_summary Structure
F-Key Name Type Description
id bigint
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
total_paid numeric
last_payment_ts timestamp with time zone
last_payment_note text
last_payment_type name
total_owed numeric
last_billing_ts timestamp with time zone
last_billing_note text
last_billing_type text
balance_owed numeric
xact_type name
SELECT xact.id
, xact.usr
, xact.xact_start
, xact.xact_finish
, credit.amount AS total_paid
, credit.payment_ts AS last_payment_ts
, credit.note AS last_payment_note
, credit.payment_type AS last_payment_type
, debit.amount AS total_owed
, debit.billing_ts AS last_billing_ts
, debit.note AS last_billing_note
, debit.billing_type AS last_billing_type
, (COALESCE
     (debit.amount
           , (0)::numeric
     ) - COALESCE
     (credit.amount
           , (0)::numeric
     )
) AS balance_owed
, p.relname AS xact_type 
FROM (
     (
           (money.billable_xact xact 
              JOIN pg_class p 
                ON (
                       (xact.tableoid = p.oid)
                 )
           )
   LEFT JOIN (
            SELECT billing.xact
                 , sum
                 (billing.amount) AS amount
                 , max
                 (billing.billing_ts) AS billing_ts
                 , last
                 (billing.note) AS note
                 , last
                 (billing.billing_type) AS billing_type 
              FROM money.billing 
             WHERE (billing.voided IS FALSE)
          GROUP BY billing.xact
           ) debit 
          ON (
                 (xact.id = debit.xact)
           )
     )
LEFT JOIN (
      SELECT payment_view.xact
           , sum
           (payment_view.amount) AS amount
           , max
           (payment_view.payment_ts) AS payment_ts
           , last
           (payment_view.note) AS note
           , last
           (payment_view.payment_type) AS payment_type 
        FROM money.payment_view 
       WHERE (payment_view.voided IS FALSE)
    GROUP BY payment_view.xact
     ) credit 
    ON (
           (xact.id = credit.xact)
     )
)
ORDER BY debit.billing_ts
, credit.payment_ts;

Index - Schema money


View: money.billable_xact_with_void_summary

money.billable_xact_with_void_summary Structure
F-Key Name Type Description
id bigint
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
total_paid numeric
last_payment_ts timestamp with time zone
last_payment_note text
last_payment_type name
total_owed numeric
last_billing_ts timestamp with time zone
last_billing_note text
last_billing_type text
balance_owed numeric
xact_type name
SELECT xact.id
, xact.usr
, xact.xact_start
, xact.xact_finish
, sum
(credit.amount) AS total_paid
, max
(credit.payment_ts) AS last_payment_ts
, last
(credit.note) AS last_payment_note
, last
(credit.payment_type) AS last_payment_type
, sum
(debit.amount) AS total_owed
, max
(debit.billing_ts) AS last_billing_ts
, last
(debit.note) AS last_billing_note
, last
(debit.billing_type) AS last_billing_type
, (COALESCE
     (sum
           (debit.amount)
           , (0)::numeric
     ) - COALESCE
     (sum
           (credit.amount)
           , (0)::numeric
     )
) AS balance_owed
, p.relname AS xact_type 
FROM (
     (
           (money.billable_xact xact 
              JOIN pg_class p 
                ON (
                       (xact.tableoid = p.oid)
                 )
           )
   LEFT JOIN money.billing debit 
          ON (
                 (xact.id = debit.xact)
           )
     )
LEFT JOIN money.payment_view credit 
    ON (
           (xact.id = credit.xact)
     )
)
GROUP BY xact.id
, xact.usr
, xact.xact_start
, xact.xact_finish
, p.relname 
ORDER BY max
(debit.billing_ts)
, max
(credit.payment_ts);

Index - Schema money


Table: money.billing

money.billing Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
xact bigint NOT NULL
billing_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
voider integer
void_time timestamp with time zone
amount numeric(6,2) NOT NULL
billing_type text NOT NULL
config.billing_type.id btype integer NOT NULL
note text
m_b_time_idx billing_ts m_b_xact_idx xact

Index - Schema money


Table: money.bnm_desk_payment

money.bnm_desk_payment Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL
actor.workstation.id cash_drawer integer

Table money.bnm_desk_payment Inherits bnm_payment,

Index - Schema money


Table: money.bnm_payment

money.bnm_payment Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL

Table money.bnm_payment Inherits payment,

Index - Schema money


View: money.bnm_payment_view

money.bnm_payment_view Structure
F-Key Name Type Description
id bigint
xact bigint
payment_ts timestamp with time zone
voided boolean
amount numeric(6,2)
note text
amount_collected numeric(6,2)
accepting_usr integer
payment_type name
SELECT p.id
, p.xact
, p.payment_ts
, p.voided
, p.amount
, p.note
, p.amount_collected
, p.accepting_usr
, c.relname AS payment_type 
FROM (money.bnm_payment p 
  JOIN pg_class c 
    ON (
           (p.tableoid = c.oid)
     )
);

Index - Schema money


Table: money.cash_payment

money.cash_payment Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL
cash_drawer integer

Table money.cash_payment Inherits bnm_desk_payment,

money_cash_id_idx id money_cash_payment_accepting_usr_idx accepting_usr money_cash_payment_cash_drawer_idx cash_drawer money_cash_payment_ts_idx payment_ts money_cash_payment_xact_idx xact

Index - Schema money


View: money.cashdrawer_payment_view

money.cashdrawer_payment_view Structure
F-Key Name Type Description
org_unit integer
cashdrawer integer
payment_type name
payment_ts timestamp with time zone
amount numeric(6,2)
voided boolean
note text
SELECT ou.id AS org_unit
, ws.id AS cashdrawer
, t.payment_type
, p.payment_ts
, p.amount
, p.voided
, p.note 
FROM (
     (
           (actor.org_unit ou 
              JOIN actor.workstation ws 
                ON (
                       (ou.id = ws.owning_lib)
                 )
           )
   LEFT JOIN money.bnm_desk_payment p 
          ON (
                 (ws.id = p.cash_drawer)
           )
     )
LEFT JOIN money.payment_view t 
    ON (
           (p.id = t.id)
     )
);

Index - Schema money


Table: money.check_payment

money.check_payment Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL
cash_drawer integer
check_number text NOT NULL

Table money.check_payment Inherits bnm_desk_payment,

money_check_id_idx id money_check_payment_accepting_usr_idx accepting_usr money_check_payment_cash_drawer_idx cash_drawer money_check_payment_ts_idx payment_ts money_check_payment_xact_idx xact

Index - Schema money


Table: money.collections_tracker

money.collections_tracker Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id usr integer NOT NULL
actor.usr.id collector integer NOT NULL
actor.org_unit.id location integer NOT NULL
enter_time timestamp with time zone

Index - Schema money


Table: money.credit_card_payment

money.credit_card_payment Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL
cash_drawer integer
cc_type text
cc_number text
expire_month integer
expire_year integer
approval_code text

Table money.credit_card_payment Inherits bnm_desk_payment,

money_credit_card_id_idx id money_credit_card_payment_accepting_usr_idx accepting_usr money_credit_card_payment_cash_drawer_idx cash_drawer money_credit_card_payment_ts_idx payment_ts money_credit_card_payment_xact_idx xact

Index - Schema money


Table: money.credit_payment

money.credit_payment Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL

Table money.credit_payment Inherits bnm_payment,

money_credit_id_idx id money_credit_payment_accepting_usr_idx accepting_usr money_credit_payment_payment_ts_idx payment_ts money_credit_payment_xact_idx xact

Index - Schema money


View: money.desk_payment_view

money.desk_payment_view Structure
F-Key Name Type Description
id bigint
xact bigint
payment_ts timestamp with time zone
voided boolean
amount numeric(6,2)
note text
amount_collected numeric(6,2)
accepting_usr integer
cash_drawer integer
payment_type name
SELECT p.id
, p.xact
, p.payment_ts
, p.voided
, p.amount
, p.note
, p.amount_collected
, p.accepting_usr
, p.cash_drawer
, c.relname AS payment_type 
FROM (money.bnm_desk_payment p 
  JOIN pg_class c 
    ON (
           (p.tableoid = c.oid)
     )
);

Index - Schema money


Table: money.forgive_payment

money.forgive_payment Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL

Table money.forgive_payment Inherits bnm_payment,

money_forgive_id_idx id money_forgive_payment_accepting_usr_idx accepting_usr money_forgive_payment_payment_ts_idx payment_ts money_forgive_payment_xact_idx xact

Index - Schema money


Table: money.goods_payment

money.goods_payment Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL

Table money.goods_payment Inherits bnm_payment,

money_goods_id_idx id money_goods_payment_accepting_usr_idx accepting_usr money_goods_payment_payment_ts_idx payment_ts money_goods_payment_xact_idx xact

Index - Schema money


Table: money.grocery

money.grocery Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('money.billable_xact_id_seq'::regclass)
usr integer NOT NULL
xact_start timestamp with time zone NOT NULL DEFAULT now()
xact_finish timestamp with time zone
unrecovered boolean
billing_location integer NOT NULL
note text

Table money.grocery Inherits billable_xact,

circ_open_date_idx xact_start) WHERE (xact_finish IS NULL m_g_usr_idx usr

Index - Schema money


Table: money.materialized_billable_xact_summary

money.materialized_billable_xact_summary Structure
F-Key Name Type Description
id bigint
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
total_paid numeric
last_payment_ts timestamp with time zone
last_payment_note text
last_payment_type name
total_owed numeric
last_billing_ts timestamp with time zone
last_billing_note text
last_billing_type text
balance_owed numeric
xact_type name
money_mat_summary_id_idx id money_mat_summary_usr_idx usr money_mat_summary_xact_start_idx xact_start

Index - Schema money


View: money.non_drawer_payment_view

money.non_drawer_payment_view Structure
F-Key Name Type Description
id bigint
xact bigint
payment_ts timestamp with time zone
voided boolean
amount numeric(6,2)
note text
amount_collected numeric(6,2)
accepting_usr integer
payment_type name
SELECT p.id
, p.xact
, p.payment_ts
, p.voided
, p.amount
, p.note
, p.amount_collected
, p.accepting_usr
, c.relname AS payment_type 
FROM (money.bnm_payment p 
  JOIN pg_class c 
    ON (
           (p.tableoid = c.oid)
     )
)
WHERE (c.relname <> ALL 
     (ARRAY['cash_payment'::name
           ,'check_payment'::name
           ,'credit_card_payment'::name]
     )
);

Index - Schema money


View: money.open_billable_xact_summary

money.open_billable_xact_summary Structure
F-Key Name Type Description
id bigint
usr integer
billing_location integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
total_paid numeric
last_payment_ts timestamp with time zone
last_payment_note text
last_payment_type name
total_owed numeric
last_billing_ts timestamp with time zone
last_billing_note text
last_billing_type text
balance_owed numeric
xact_type name
SELECT xact.id
, xact.usr
, COALESCE
(circ.circ_lib
     , groc.billing_location
) AS billing_location
, xact.xact_start
, xact.xact_finish
, sum
(credit.amount) AS total_paid
, max
(credit.payment_ts) AS last_payment_ts
, last
(credit.note) AS last_payment_note
, last
(credit.payment_type) AS last_payment_type
, sum
(debit.amount) AS total_owed
, max
(debit.billing_ts) AS last_billing_ts
, last
(debit.note) AS last_billing_note
, last
(debit.billing_type) AS last_billing_type
, (COALESCE
     (sum
           (debit.amount)
           , (0)::numeric
     ) - COALESCE
     (sum
           (credit.amount)
           , (0)::numeric
     )
) AS balance_owed
, p.relname AS xact_type 
FROM (
     (
           (
                 (
                       (money.billable_xact xact 
                          JOIN pg_class p 
                            ON (
                                   (xact.tableoid = p.oid)
                             )
                       )
               LEFT JOIN action.circulation circ 
                      ON (
                             (circ.id = xact.id)
                       )
                 )
         LEFT JOIN money.grocery groc 
                ON (
                       (groc.id = xact.id)
                 )
           )
   LEFT JOIN (
            SELECT billing.xact
                 , billing.voided
                 , sum
                 (billing.amount) AS amount
                 , max
                 (billing.billing_ts) AS billing_ts
                 , last
                 (billing.note) AS note
                 , last
                 (billing.billing_type) AS billing_type 
              FROM money.billing 
             WHERE (billing.voided IS FALSE)
          GROUP BY billing.xact
                 , billing.voided
           ) debit 
          ON (
                 (
                       (xact.id = debit.xact)
                     AND (debit.voided IS FALSE)
                 )
           )
     )
LEFT JOIN (
      SELECT payment_view.xact
           , payment_view.voided
           , sum
           (payment_view.amount) AS amount
           , max
           (payment_view.payment_ts) AS payment_ts
           , last
           (payment_view.note) AS note
           , last
           (payment_view.payment_type) AS payment_type 
        FROM money.payment_view 
       WHERE (payment_view.voided IS FALSE)
    GROUP BY payment_view.xact
           , payment_view.voided
     ) credit 
    ON (
           (
                 (xact.id = credit.xact)
               AND (credit.voided IS FALSE)
           )
     )
)
WHERE (xact.xact_finish IS NULL)
GROUP BY xact.id
, xact.usr
, COALESCE
(circ.circ_lib
     , groc.billing_location
)
, xact.xact_start
, xact.xact_finish
, p.relname 
ORDER BY max
(debit.billing_ts)
, max
(credit.payment_ts);

Index - Schema money


View: money.open_transaction_billing_summary

money.open_transaction_billing_summary Structure
F-Key Name Type Description
xact bigint
last_billing_type text
last_billing_note text
last_billing_ts timestamp with time zone
total_owed numeric
SELECT billing.xact
, last
(billing.billing_type) AS last_billing_type
, last
(billing.note) AS last_billing_note
, max
(billing.billing_ts) AS last_billing_ts
, sum
(COALESCE
     (billing.amount
           , (0)::numeric
     )
) AS total_owed 
FROM money.billing 
WHERE (billing.voided IS FALSE)
GROUP BY billing.xact 
ORDER BY max
(billing.billing_ts);

Index - Schema money


View: money.open_transaction_billing_type_summary

money.open_transaction_billing_type_summary Structure
F-Key Name Type Description
xact bigint
last_billing_type text
last_billing_note text
last_billing_ts timestamp with time zone
total_owed numeric
SELECT billing.xact
, billing.billing_type AS last_billing_type
, last
(billing.note) AS last_billing_note
, max
(billing.billing_ts) AS last_billing_ts
, sum
(COALESCE
     (billing.amount
           , (0)::numeric
     )
) AS total_owed 
FROM money.billing 
WHERE (billing.voided IS FALSE)
GROUP BY billing.xact
, billing.billing_type 
ORDER BY max
(billing.billing_ts);

Index - Schema money


View: money.open_transaction_payment_summary

money.open_transaction_payment_summary Structure
F-Key Name Type Description
xact bigint
last_payment_type name
last_payment_note text
last_payment_ts timestamp with time zone
total_paid numeric
SELECT payment_view.xact
, last
(payment_view.payment_type) AS last_payment_type
, last
(payment_view.note) AS last_payment_note
, max
(payment_view.payment_ts) AS last_payment_ts
, sum
(COALESCE
     (payment_view.amount
           , (0)::numeric
     )
) AS total_paid 
FROM money.payment_view 
WHERE (payment_view.voided IS FALSE)
GROUP BY payment_view.xact 
ORDER BY max
(payment_view.payment_ts);

Index - Schema money


View: money.open_usr_circulation_summary

money.open_usr_circulation_summary Structure
F-Key Name Type Description
usr integer
total_paid numeric
total_owed numeric
balance_owed numeric
SELECT open_billable_xact_summary.usr
, sum
(open_billable_xact_summary.total_paid) AS total_paid
, sum
(open_billable_xact_summary.total_owed) AS total_owed
, sum
(open_billable_xact_summary.balance_owed) AS balance_owed 
FROM money.open_billable_xact_summary 
WHERE (open_billable_xact_summary.xact_type = 'circulation'::name)
GROUP BY open_billable_xact_summary.usr;

Index - Schema money


View: money.open_usr_summary

money.open_usr_summary Structure
F-Key Name Type Description
usr integer
total_paid numeric
total_owed numeric
balance_owed numeric
SELECT open_billable_xact_summary.usr
, sum
(open_billable_xact_summary.total_paid) AS total_paid
, sum
(open_billable_xact_summary.total_owed) AS total_owed
, sum
(open_billable_xact_summary.balance_owed) AS balance_owed 
FROM money.open_billable_xact_summary 
GROUP BY open_billable_xact_summary.usr;

Index - Schema money


Table: money.payment

money.payment Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
m_p_time_idx payment_ts m_p_xact_idx xact

Index - Schema money


View: money.payment_view

money.payment_view Structure
F-Key Name Type Description
id bigint
xact bigint
payment_ts timestamp with time zone
voided boolean
amount numeric(6,2)
note text
payment_type name
SELECT p.id
, p.xact
, p.payment_ts
, p.voided
, p.amount
, p.note
, c.relname AS payment_type 
FROM (money.payment p 
  JOIN pg_class c 
    ON (
           (p.tableoid = c.oid)
     )
);

Index - Schema money


View: money.transaction_billing_summary

money.transaction_billing_summary Structure
F-Key Name Type Description
xact bigint
last_billing_type text
last_billing_note text
last_billing_ts timestamp with time zone
total_owed numeric
SELECT billing.xact
, last
(billing.billing_type) AS last_billing_type
, last
(billing.note) AS last_billing_note
, max
(billing.billing_ts) AS last_billing_ts
, sum
(COALESCE
     (billing.amount
           , (0)::numeric
     )
) AS total_owed 
FROM money.billing 
WHERE (billing.voided IS FALSE)
GROUP BY billing.xact 
ORDER BY max
(billing.billing_ts);

Index - Schema money


View: money.transaction_billing_type_summary

money.transaction_billing_type_summary Structure
F-Key Name Type Description
xact bigint
last_billing_type text
last_billing_note text
last_billing_ts timestamp with time zone
total_owed numeric
SELECT billing.xact
, billing.billing_type AS last_billing_type
, last
(billing.note) AS last_billing_note
, max
(billing.billing_ts) AS last_billing_ts
, sum
(COALESCE
     (billing.amount
           , (0)::numeric
     )
) AS total_owed 
FROM money.billing 
WHERE (billing.voided IS FALSE)
GROUP BY billing.xact
, billing.billing_type 
ORDER BY max
(billing.billing_ts);

Index - Schema money


View: money.transaction_billing_with_void_summary

money.transaction_billing_with_void_summary Structure
F-Key Name Type Description
xact bigint
last_billing_type text
last_billing_note text
last_billing_ts timestamp with time zone
total_owed numeric
SELECT billing.xact
, last
(billing.billing_type) AS last_billing_type
, last
(billing.note) AS last_billing_note
, max
(billing.billing_ts) AS last_billing_ts
, sum
(CASE WHEN billing.voided THEN 
     (0)::numeric ELSE COALESCE
     (billing.amount
           , (0)::numeric
     ) END
) AS total_owed 
FROM money.billing 
GROUP BY billing.xact 
ORDER BY max
(billing.billing_ts);

Index - Schema money


View: money.transaction_payment_summary

money.transaction_payment_summary Structure
F-Key Name Type Description
xact bigint
last_payment_type name
last_payment_note text
last_payment_ts timestamp with time zone
total_paid numeric
SELECT payment_view.xact
, last
(payment_view.payment_type) AS last_payment_type
, last
(payment_view.note) AS last_payment_note
, max
(payment_view.payment_ts) AS last_payment_ts
, sum
(COALESCE
     (payment_view.amount
           , (0)::numeric
     )
) AS total_paid 
FROM money.payment_view 
WHERE (payment_view.voided IS FALSE)
GROUP BY payment_view.xact 
ORDER BY max
(payment_view.payment_ts);

Index - Schema money


View: money.transaction_payment_with_void_summary

money.transaction_payment_with_void_summary Structure
F-Key Name Type Description
xact bigint
last_payment_type name
last_payment_note text
last_payment_ts timestamp with time zone
total_paid numeric
SELECT payment_view.xact
, last
(payment_view.payment_type) AS last_payment_type
, last
(payment_view.note) AS last_payment_note
, max
(payment_view.payment_ts) AS last_payment_ts
, sum
(CASE WHEN payment_view.voided THEN 
     (0)::numeric ELSE COALESCE
     (payment_view.amount
           , (0)::numeric
     ) END
) AS total_paid 
FROM money.payment_view 
GROUP BY payment_view.xact 
ORDER BY max
(payment_view.payment_ts);

Index - Schema money


View: money.usr_circulation_summary

money.usr_circulation_summary Structure
F-Key Name Type Description
usr integer
total_paid numeric
total_owed numeric
balance_owed numeric
SELECT billable_xact_summary.usr
, sum
(billable_xact_summary.total_paid) AS total_paid
, sum
(billable_xact_summary.total_owed) AS total_owed
, sum
(billable_xact_summary.balance_owed) AS balance_owed 
FROM money.billable_xact_summary 
WHERE (billable_xact_summary.xact_type = 'circulation'::name)
GROUP BY billable_xact_summary.usr;

Index - Schema money


View: money.usr_summary

money.usr_summary Structure
F-Key Name Type Description
usr integer
total_paid numeric
total_owed numeric
balance_owed numeric
SELECT billable_xact_summary.usr
, sum
(billable_xact_summary.total_paid) AS total_paid
, sum
(billable_xact_summary.total_owed) AS total_owed
, sum
(billable_xact_summary.balance_owed) AS balance_owed 
FROM money.billable_xact_summary 
GROUP BY billable_xact_summary.usr;

Index - Schema money


Table: money.work_payment

money.work_payment Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('money.payment_id_seq'::regclass)
xact bigint NOT NULL
payment_ts timestamp with time zone NOT NULL DEFAULT now()
voided boolean NOT NULL DEFAULT false
amount numeric(6,2) NOT NULL
note text
amount_collected numeric(6,2) NOT NULL
accepting_usr integer NOT NULL

Table money.work_payment Inherits bnm_payment,

money_work_id_idx id money_work_payment_accepting_usr_idx accepting_usr money_work_payment_payment_ts_idx payment_ts money_work_payment_xact_idx xact

Index - Schema money


Function: money.mat_summary_create()

Returns: trigger

Language: PLPGSQL

BEGIN
	INSERT INTO money.materialized_billable_xact_summary (id, usr, xact_start, xact_finish, total_paid, total_owed, balance_owed)
		VALUES ( NEW.id, NEW.usr, NEW.xact_start, NEW.xact_finish, 0.0, 0.0, 0.0);
	RETURN NEW;
END;

Function: money.mat_summary_delete()

Returns: trigger

Language: PLPGSQL

BEGIN
	DELETE FROM money.materialized_billable_xact_summary WHERE id = OLD.id;
	RETURN OLD;
END;

Function: money.mat_summary_update()

Returns: trigger

Language: PLPGSQL

BEGIN
	UPDATE	money.materialized_billable_xact_summary
	  SET	usr = NEW.usr,
		xact_start = NEW.xact_start,
		xact_finish = NEW.xact_finish
	  WHERE	id = NEW.id;
	RETURN NEW;
END;

Function: money.materialized_summary_billing_add()

Returns: trigger

Language: PLPGSQL

BEGIN
	IF NOT NEW.voided THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = total_owed + NEW.amount,
			last_billing_ts = NEW.billing_ts,
			last_billing_note = NEW.note,
			last_billing_type = NEW.billing_type,
			balance_owed = balance_owed + NEW.amount
		  WHERE	id = NEW.xact;
	END IF;

	RETURN NEW;
END;

Function: money.materialized_summary_billing_del()

Returns: trigger

Language: PLPGSQL

DECLARE
	prev_billing	money.billing%ROWTYPE;
	old_billing	money.billing%ROWTYPE;
BEGIN
	SELECT * INTO prev_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1 OFFSET 1;
	SELECT * INTO old_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1;

	IF OLD.id = old_billing.id THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	last_billing_ts = prev_billing.billing_ts,
			last_billing_note = prev_billing.note,
			last_billing_type = prev_billing.billing_type
		  WHERE	id = NEW.xact;
	END IF;

	IF NOT OLD.voided THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = total_owed - OLD.amount,
			balance_owed = balance_owed + OLD.amount
		  WHERE	id = OLD.xact;
	END IF;

	RETURN OLD;
END;

Function: money.materialized_summary_billing_update()

Returns: trigger

Language: PLPGSQL

DECLARE
	old_billing	money.billing%ROWTYPE;
	old_voided	money.billing%ROWTYPE;
BEGIN

	SELECT * INTO old_billing FROM money.billing WHERE xact = NEW.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1;
	SELECT * INTO old_voided FROM money.billing WHERE xact = NEW.xact ORDER BY billing_ts DESC LIMIT 1;

	IF NEW.voided AND NOT OLD.voided THEN
		IF OLD.id = old_voided.id THEN
			UPDATE	money.materialized_billable_xact_summary
			  SET	last_billing_ts = old_billing.billing_ts,
				last_billing_note = old_billing.note,
				last_billing_type = old_billing.billing_type
			  WHERE	id = OLD.xact;
		END IF;

		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = total_owed - NEW.amount,
			balance_owed = balance_owed - NEW.amount
		  WHERE	id = NEW.xact;

	ELSIF NOT NEW.voided AND OLD.voided THEN

		IF OLD.id = old_billing.id THEN
			UPDATE	money.materialized_billable_xact_summary
			  SET	last_billing_ts = old_billing.billing_ts,
				last_billing_note = old_billing.note,
				last_billing_type = old_billing.billing_type
			  WHERE	id = OLD.xact;
		END IF;

		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = total_owed + NEW.amount,
			balance_owed = balance_owed + NEW.amount
		  WHERE	id = NEW.xact;

	ELSE
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_owed = total_owed - (OLD.amount - NEW.amount),
			balance_owed = balance_owed - (OLD.amount - NEW.amount)
		  WHERE	id = NEW.xact;
	END IF;

	RETURN NEW;
END;

Function: money.materialized_summary_payment_add()

Returns: trigger

Language: PLPGSQL

BEGIN
	IF NOT NEW.voided THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_paid = total_paid + NEW.amount,
			last_payment_ts = NEW.payment_ts,
			last_payment_note = NEW.note,
			last_payment_type = TG_ARGV[0],
			balance_owed = balance_owed - NEW.amount
		  WHERE	id = NEW.xact;
	END IF;

	RETURN NEW;
END;

Function: money.materialized_summary_payment_del()

Returns: trigger

Language: PLPGSQL

DECLARE
	prev_payment	money.payment_view%ROWTYPE;
	old_payment	money.payment_view%ROWTYPE;
BEGIN
	SELECT * INTO prev_payment FROM money.payment_view WHERE xact = OLD.xact AND NOT voided ORDER BY payment_ts DESC LIMIT 1 OFFSET 1;
	SELECT * INTO old_payment FROM money.payment_view WHERE xact = OLD.xact AND NOT voided ORDER BY payment_ts DESC LIMIT 1;

	IF OLD.id = old_payment.id THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	last_payment_ts = prev_payment.payment_ts,
			last_payment_note = prev_payment.note,
			last_payment_type = prev_payment.payment_type
		  WHERE	id = OLD.xact;
	END IF;

	IF NOT OLD.voided THEN
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_paid = total_paid - OLD.amount,
			balance_owed = balance_owed + OLD.amount
		  WHERE	id = OLD.xact;
	END IF;

	RETURN OLD;
END;

Function: money.materialized_summary_payment_update()

Returns: trigger

Language: PLPGSQL

DECLARE
	old_payment	money.payment_view%ROWTYPE;
	old_voided	money.payment_view%ROWTYPE;
BEGIN

	SELECT * INTO old_payment FROM money.payment_view WHERE xact = NEW.xact AND NOT voided ORDER BY payment_ts DESC LIMIT 1;
	SELECT * INTO old_voided FROM money.payment_view WHERE xact = NEW.xact ORDER BY payment_ts DESC LIMIT 1;

	IF NEW.voided AND NOT OLD.voided THEN
		IF OLD.id = old_voided.id THEN
			UPDATE	money.materialized_billable_xact_summary
			  SET	last_payment_ts = old_payment.payment_ts,
				last_payment_note = old_payment.note,
				last_payment_type = old_payment.payment_type
			  WHERE	id = OLD.xact;
		END IF;

		UPDATE	money.materialized_billable_xact_summary
		  SET	total_paid = total_paid - NEW.amount,
			balance_owed = balance_owed + NEW.amount
		  WHERE	id = NEW.xact;

	ELSIF NOT NEW.voided AND OLD.voided THEN

		IF OLD.id = old_payment.id THEN
			UPDATE	money.materialized_billable_xact_summary
			  SET	last_payment_ts = old_payment.payment_ts,
				last_payment_note = old_payment.note,
				last_payment_type = old_payment.payment_type
			  WHERE	id = OLD.xact;
		END IF;

		UPDATE	money.materialized_billable_xact_summary
		  SET	total_paid = total_paid + NEW.amount,
			balance_owed = balance_owed - NEW.amount
		  WHERE	id = NEW.xact;

	ELSE
		UPDATE	money.materialized_billable_xact_summary
		  SET	total_paid = total_paid - (OLD.amount - NEW.amount),
			balance_owed = balance_owed + (OLD.amount - NEW.amount)
		  WHERE	id = NEW.xact;
	END IF;

	RETURN NEW;
END;

Schema offline


Table: offline.script

offline.script Structure
F-Key Name Type Description
id serial PRIMARY KEY
session text NOT NULL
requestor integer NOT NULL
create_time integer NOT NULL
workstation text NOT NULL
logfile text NOT NULL
time_delta integer NOT NULL
count integer NOT NULL
offline_script_pkey id offline_script_session session offline_script_ws workstation

Index - Schema offline


Table: offline.session

offline.session Structure
F-Key Name Type Description
key text PRIMARY KEY
org integer NOT NULL
description text
creator integer NOT NULL
create_time integer NOT NULL
in_process integer NOT NULL
start_time integer
end_time integer
num_complete integer NOT NULL
offline_session_creation create_time offline_session_org org offline_session_pkey key

Index - Schema offline


Schema permission


Table: permission.grp_penalty_threshold

permission.grp_penalty_threshold Structure
F-Key Name Type Description
id serial PRIMARY KEY
permission.grp_tree.id grp integer UNIQUE#1 NOT NULL
actor.org_unit.id org_unit integer UNIQUE#1 NOT NULL
config.standing_penalty.id penalty integer UNIQUE#1 NOT NULL
threshold numeric(8,2) NOT NULL

Index - Schema permission


Table: permission.grp_perm_map

permission.grp_perm_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
permission.grp_tree.id grp integer UNIQUE#1 NOT NULL
permission.perm_list.id perm integer UNIQUE#1 NOT NULL
depth integer NOT NULL
grantable boolean NOT NULL DEFAULT false

Index - Schema permission


Table: permission.grp_tree

permission.grp_tree Structure
F-Key Name Type Description
id serial PRIMARY KEY
name text UNIQUE NOT NULL
permission.grp_tree.id parent integer
usergroup boolean NOT NULL DEFAULT true
perm_interval interval NOT NULL DEFAULT '3 years'::interval
description text
application_perm text

Tables referencing this one via Foreign Key Constraints:

grp_tree_parent_idx parent

Index - Schema permission


Table: permission.perm_list

permission.perm_list Structure
F-Key Name Type Description
id serial PRIMARY KEY
code text UNIQUE NOT NULL
description text

Tables referencing this one via Foreign Key Constraints:

perm_list_code_idx code

Index - Schema permission


Table: permission.usr_grp_map

permission.usr_grp_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id usr integer UNIQUE#1 NOT NULL
permission.grp_tree.id grp integer UNIQUE#1 NOT NULL

Index - Schema permission


Table: permission.usr_object_perm_map

permission.usr_object_perm_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id usr integer UNIQUE#1 NOT NULL
permission.perm_list.id perm integer UNIQUE#1 NOT NULL
object_type text UNIQUE#1 NOT NULL
object_id text UNIQUE#1 NOT NULL
grantable boolean NOT NULL DEFAULT false
uopm_usr_idx usr

Index - Schema permission


Table: permission.usr_perm_map

permission.usr_perm_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id usr integer UNIQUE#1 NOT NULL
permission.perm_list.id perm integer UNIQUE#1 NOT NULL
depth integer NOT NULL
grantable boolean NOT NULL DEFAULT false

Index - Schema permission


Table: permission.usr_work_ou_map

permission.usr_work_ou_map Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id usr integer UNIQUE#1 NOT NULL
actor.org_unit.id work_ou integer UNIQUE#1 NOT NULL

Index - Schema permission


Function: permission.grp_ancestors(integer)

Returns: SET OF grp_tree

Language: SQL

	SELECT	a.*
	FROM	connectby('permission.grp_tree'::text,'parent'::text,'id'::text,'name'::text,$1::text,100,'.'::text)
			AS t(keyid text, parent_keyid text, level int, branch text,pos int)
		JOIN permission.grp_tree a ON a.id::text = t.keyid::text
	ORDER BY
		CASE WHEN a.parent IS NULL
			THEN 0
			ELSE 1
		END, a.name;

Function: permission.usr_can_grant_perm(target_ou integer, tperm text, iuser integer)

Returns: boolean

Language: PLPGSQL

DECLARE
	r_usr	actor.usr%ROWTYPE;
	r_perm	permission.usr_perm_map%ROWTYPE;
BEGIN

	SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;

	IF r_usr.active = FALSE THEN
		RETURN FALSE;
	END IF;

	IF r_usr.super_user = TRUE THEN
		RETURN TRUE;
	END IF;

	FOR r_perm IN	SELECT	*
			  FROM	permission.usr_perms(iuser) p
				JOIN permission.perm_list l
					ON (l.id = p.perm)
			  WHERE	(l.code = tperm AND p.grantable IS TRUE)
		LOOP

		PERFORM	*
		  FROM	actor.org_unit_descendants(target_ou,r_perm.depth)
		  WHERE	id = r_usr.home_ou;

		IF FOUND THEN
			RETURN TRUE;
		ELSE
			RETURN FALSE;
		END IF;
	END LOOP;

	RETURN FALSE;
END;

Function: permission.usr_has_home_perm(target_ou integer, tperm text, iuser integer)

Returns: boolean

Language: PLPGSQL

DECLARE
	r_usr	actor.usr%ROWTYPE;
	r_perm	permission.usr_perm_map%ROWTYPE;
BEGIN

	SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;

	IF r_usr.active = FALSE THEN
		RETURN FALSE;
	END IF;

	IF r_usr.super_user = TRUE THEN
		RETURN TRUE;
	END IF;

	FOR r_perm IN	SELECT	*
			  FROM	permission.usr_perms(iuser) p
				JOIN permission.perm_list l
					ON (l.id = p.perm)
			  WHERE	l.code = tperm
			  	OR p.perm = -1 LOOP

		PERFORM	*
		  FROM	actor.org_unit_descendants(target_ou,r_perm.depth)
		  WHERE	id = r_usr.home_ou;

		IF FOUND THEN
			RETURN TRUE;
		ELSE
			RETURN FALSE;
		END IF;
	END LOOP;

	RETURN FALSE;
END;

Function: permission.usr_has_object_perm(integer, text, text, text)

Returns: boolean

Language: SQL

    SELECT permission.usr_has_object_perm( $1, $2, $3, $4, -1 );

Function: permission.usr_has_object_perm(target_ou integer, obj_id text, obj_type text, tperm text, iuser integer)

Returns: boolean

Language: PLPGSQL

DECLARE
	r_usr	actor.usr%ROWTYPE;
	res     BOOL;
BEGIN

	SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;

	IF r_usr.active = FALSE THEN
		RETURN FALSE;
	END IF;

	IF r_usr.super_user = TRUE THEN
		RETURN TRUE;
	END IF;

	SELECT TRUE INTO res FROM permission.usr_object_perm_map WHERE usr = r_usr.id AND object_type = obj_type AND object_id = obj_id;

	IF FOUND THEN
		RETURN TRUE;
	END IF;

	IF target_ou > -1 THEN
		RETURN permission.usr_has_perm( iuser, tperm, target_ou);
	END IF;

	RETURN FALSE;

END;

Function: permission.usr_has_perm(integer, text, integer)

Returns: boolean

Language: SQL

	SELECT	CASE
			WHEN permission.usr_has_home_perm( $1, $2, $3 ) THEN TRUE
			WHEN permission.usr_has_work_perm( $1, $2, $3 ) THEN TRUE
			ELSE FALSE
		END;

Function: permission.usr_has_perm_at(perm_code integer, user_id text)

Returns: SET OF integer

Language: SQL

SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( $1, $2 );

Function: permission.usr_has_perm_at_all(perm_code integer, user_id text)

Returns: SET OF integer

Language: SQL

SELECT DISTINCT * FROM permission.usr_has_perm_at_all_nd( $1, $2 );

Function: permission.usr_has_perm_at_all_nd(perm_code integer, user_id text)

Returns: SET OF integer

Language: PLPGSQL

--
-- Return a set of all the org units for which a given user has a given
-- permission, granted either directly or through inheritance from a parent
-- org unit.
--
-- The permissions apply to a minimum depth of the org unit hierarchy, and
-- to the subordinates of those org units, for the org unit(s) to which the
-- user is assigned.
--
-- For purposes of this function, the permission.usr_work_ou_map table
-- assigns users to org units.  I.e. we ignore the home_ou column of actor.usr.
--
-- The result set may contain duplicates, which should be eliminated
-- by a DISTINCT clause.
--
DECLARE
	n_head_ou     INTEGER;
	n_child_ou    INTEGER;
BEGIN
	FOR n_head_ou IN
		SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( user_id, perm_code )
	LOOP
		--
		-- The permission applies only at a depth greater than the work org unit.
		-- Use connectby() to find all dependent org units at the specified depth.
		--
		FOR n_child_ou IN
			SELECT ou::INTEGER
			FROM connectby( 
					'actor.org_unit',   -- table name
					'id',               -- key column
					'parent_ou',        -- recursive foreign key
					n_head_ou::TEXT,    -- id of starting point
					0                   -- no limit on search depth
				)
				AS t(
					ou text,            -- dependent org unit
					parent_ou text,     -- (ignore)
					level int           -- (ignore)
				)
		LOOP
			RETURN NEXT n_child_ou;
		END LOOP;
	END LOOP;
	--
	RETURN;
	--
END;

Function: permission.usr_has_perm_at_nd(perm_code integer, user_id text)

Returns: SET OF integer

Language: PLPGSQL

--
-- Return a set of all the org units for which a given user has a given
-- permission, granted directly (not through inheritance from a parent
-- org unit).
--
-- The permissions apply to a minimum depth of the org unit hierarchy,
-- for the org unit(s) to which the user is assigned.  (They also apply
-- to the subordinates of those org units, but we don't report the
-- subordinates here.)
--
-- For purposes of this function, the permission.usr_work_ou_map table
-- defines which users belong to which org units.  I.e. we ignore the
-- home_ou column of actor.usr.
--
-- The result set may contain duplicates, which should be eliminated
-- by a DISTINCT clause.
--
DECLARE
	b_super       BOOLEAN;
	n_perm        INTEGER;
	n_min_depth   INTEGER; 
	n_work_ou     INTEGER;
	n_curr_ou     INTEGER;
	n_depth       INTEGER;
	n_curr_depth  INTEGER;
BEGIN
	--
	-- Check for superuser
	--
	SELECT INTO b_super
		super_user
	FROM
		actor.usr
	WHERE
		id = user_id;
	--
	IF NOT FOUND THEN
		return;				-- No user?  No permissions.
	ELSIF b_super THEN
		--
		-- Super user has all permissions everywhere
		--
		FOR n_work_ou IN
			SELECT
				id
			FROM
				actor.org_unit
			WHERE
				parent_ou IS NULL
		LOOP
			RETURN NEXT n_work_ou; 
		END LOOP;
		RETURN;
	END IF;
	--
	-- Translate the permission name
	-- to a numeric permission id
	--
	SELECT INTO n_perm
		id
	FROM
		permission.perm_list
	WHERE
		code = perm_code;
	--
	IF NOT FOUND THEN
		RETURN;               -- No such permission
	END IF;
	--
	-- Find the highest-level org unit (i.e. the minimum depth)
	-- to which the permission is applied for this user
	--
	-- This query is modified from the one in permission.usr_perms().
	--
	SELECT INTO n_min_depth
		min( depth )
	FROM	(
		SELECT depth 
		  FROM permission.usr_perm_map upm
		 WHERE upm.usr = user_id 
		   AND upm.perm = n_perm
       				UNION
		SELECT	gpm.depth
		  FROM	permission.grp_perm_map gpm
		  WHERE	gpm.perm = n_perm 
	        AND gpm.grp IN (
	 		   SELECT	(permission.grp_ancestors(
					(SELECT profile FROM actor.usr WHERE id = user_id)
				)).id
			)
       				UNION
		SELECT	p.depth
		  FROM	permission.grp_perm_map p 
		  WHERE p.perm = n_perm
		    AND p.grp IN (
		  		SELECT (permission.grp_ancestors(m.grp)).id 
				FROM   permission.usr_grp_map m
				WHERE  m.usr = user_id
			)
	) AS x;
	--
	IF NOT FOUND THEN
		RETURN;                -- No such permission for this user
	END IF;
	--
	-- Identify the org units to which the user is assigned.  Note that
	-- we pay no attention to the home_ou column in actor.usr.
	--
	FOR n_work_ou IN
		SELECT
			work_ou
		FROM
			permission.usr_work_ou_map
		WHERE
			usr = user_id
	LOOP            -- For each org unit to which the user is assigned
		--
		-- Determine the level of the org unit by a lookup in actor.org_unit_type.
		-- We take it on faith that this depth agrees with the actual hierarchy
		-- defined in actor.org_unit.
		--
		SELECT INTO n_depth
		    type.depth
		FROM
		    actor.org_unit_type type
		        INNER JOIN actor.org_unit ou
		            ON ( ou.ou_type = type.id )
		WHERE
		    ou.id = n_work_ou;
		--
		IF NOT FOUND THEN
			CONTINUE;        -- Maybe raise exception?
		END IF;
		--
		-- Compare the depth of the work org unit to the
		-- minimum depth, and branch accordingly
		--
		IF n_depth = n_min_depth THEN
			--
			-- The org unit is at the right depth, so return it.
			--
			RETURN NEXT n_work_ou;
		ELSIF n_depth > n_min_depth THEN
			--
			-- Traverse the org unit tree toward the root,
			-- until you reach the minimum depth determined above
			--
			n_curr_depth := n_depth;
			n_curr_ou := n_work_ou;
			WHILE n_curr_depth > n_min_depth LOOP
				SELECT INTO n_curr_ou
					parent_ou
				FROM
					actor.org_unit
				WHERE
					id = n_curr_ou;
				--
				IF FOUND THEN
					n_curr_depth := n_curr_depth - 1;
				ELSE
					--
					-- This can happen only if the hierarchy defined in
					-- actor.org_unit is corrupted, or out of sync with
					-- the depths defined in actor.org_unit_type.
					-- Maybe we should raise an exception here, instead
					-- of silently ignoring the problem.
					--
					n_curr_ou = NULL;
					EXIT;
				END IF;
			END LOOP;
			--
			IF n_curr_ou IS NOT NULL THEN
				RETURN NEXT n_curr_ou;
			END IF;
		ELSE
			--
			-- The permission applies only at a depth greater than the work org unit.
			-- Use connectby() to find all dependent org units at the specified depth.
			--
			FOR n_curr_ou IN
				SELECT ou::INTEGER
				FROM connectby( 
						'actor.org_unit',         -- table name
						'id',                     -- key column
						'parent_ou',              -- recursive foreign key
						n_work_ou::TEXT,          -- id of starting point
						(n_min_depth - n_depth)   -- max depth to search, relative
					)                             --   to starting point
					AS t(
						ou text,            -- dependent org unit
						parent_ou text,     -- (ignore)
						level int           -- depth relative to starting point
					)
				WHERE
					level = n_min_depth - n_depth
			LOOP
				RETURN NEXT n_curr_ou;
			END LOOP;
		END IF;
		--
	END LOOP;
	--
	RETURN;
	--
END;

Function: permission.usr_has_work_perm(target_ou integer, tperm text, iuser integer)

Returns: boolean

Language: PLPGSQL

DECLARE
	r_woum	permission.usr_work_ou_map%ROWTYPE;
	r_usr	actor.usr%ROWTYPE;
	r_perm	permission.usr_perm_map%ROWTYPE;
BEGIN

	SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;

	IF r_usr.active = FALSE THEN
		RETURN FALSE;
	END IF;

	IF r_usr.super_user = TRUE THEN
		RETURN TRUE;
	END IF;

	FOR r_perm IN	SELECT	*
			  FROM	permission.usr_perms(iuser) p
				JOIN permission.perm_list l
					ON (l.id = p.perm)
			  WHERE	l.code = tperm
			  	OR p.perm = -1
		LOOP

		FOR r_woum IN	SELECT	*
				  FROM	permission.usr_work_ou_map
				  WHERE	usr = iuser
			LOOP

			PERFORM	*
			  FROM	actor.org_unit_descendants(target_ou,r_perm.depth)
			  WHERE	id = r_woum.work_ou;

			IF FOUND THEN
				RETURN TRUE;
			END IF;

		END LOOP;

	END LOOP;

	RETURN FALSE;
END;

Function: permission.usr_perms(integer)

Returns: SET OF usr_perm_map

Language: SQL

	SELECT	DISTINCT ON (usr,perm) *
	  FROM	(
			(SELECT * FROM permission.usr_perm_map WHERE usr = $1)
        				UNION ALL
			(SELECT	-p.id, $1 AS usr, p.perm, p.depth, p.grantable
			  FROM	permission.grp_perm_map p
			  WHERE	p.grp IN (
			  	SELECT	(permission.grp_ancestors(
						(SELECT profile FROM actor.usr WHERE id = $1)
					)).id
				)
			)
        				UNION ALL
			(SELECT	-p.id, $1 AS usr, p.perm, p.depth, p.grantable
			  FROM	permission.grp_perm_map p 
			  WHERE	p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
		) AS x
	  ORDER BY 2, 3, 1 DESC, 5 DESC ;

Schema public

standard public schema


Function: public._get_parser_from_curcfg()

Returns: text

Language: SQL

select prsname::text from pg_catalog.pg_ts_parser p join pg_ts_config c on cfgparser = p.oid where c.oid = show_curcfg();

Function: public.agg_text(text)

Returns: text

Language: INTERNAL

aggregate_dummy

Function: public.agg_tsvector(tsvector)

Returns: tsvector

Language: INTERNAL

aggregate_dummy

Function: public.array_accum(anyelement)

Returns: anyarray

Language: INTERNAL

aggregate_dummy

Function: public.call_number_dewey(text)

Returns: text

Language: PLPERLU

	my $txt = shift;
	$txt =~ s/^\s+//o;
	$txt =~ s/[\[\]\{\}\(\)`'"#<>\*\?\-\+\$\\]+//og;
	$txt =~ s/\s+$//o;
	if ($txt =~ /(\d{3}(?:\.\d+)?)/o) {
		return $1;
	} else {
		return (split /\s+/, $txt)[0];
	}

Function: public.call_number_dewey(text, integer)

Returns: text

Language: SQL

	SELECT SUBSTRING(call_number_dewey($1) FROM 1 FOR $2);

Function: public.cleanup_acq_marc()

Returns: trigger

Language: PLPGSQL

BEGIN
	IF TG_OP = 'UPDATE' THEN
		DELETE FROM acq.lineitem_attr
	    		WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
		RETURN NEW;
	ELSE
		DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
		RETURN OLD;
	END IF;
END;

Function: public.concat(tsvector, tsvector)

Returns: tsvector

Language: INTERNAL

tsvector_concat

Function: public.connectby(text, text, text, text, integer)

Returns: SET OF record

Language: C

connectby_text

Function: public.connectby(text, text, text, text, integer, text)

Returns: SET OF record

Language: C

connectby_text

Function: public.connectby(text, text, text, text, text, integer)

Returns: SET OF record

Language: C

connectby_text_serial

Function: public.connectby(text, text, text, text, text, integer, text)

Returns: SET OF record

Language: C

connectby_text_serial

Function: public.crosstab(text)

Returns: SET OF record

Language: C

crosstab

Function: public.crosstab(text, integer)

Returns: SET OF record

Language: C

crosstab

Function: public.crosstab(text, text)

Returns: SET OF record

Language: C

crosstab_hash

Function: public.crosstab2(text)

Returns: SET OF tablefunc_crosstab_2

Language: C

crosstab

Function: public.crosstab3(text)

Returns: SET OF tablefunc_crosstab_3

Language: C

crosstab

Function: public.crosstab4(text)

Returns: SET OF tablefunc_crosstab_4

Language: C

crosstab

Function: public.dex_init(internal)

Returns: internal

Language: C

tsa_dex_init

Function: public.dex_lexize(internal, internal, integer)

Returns: internal

Language: C

tsa_dex_lexize

Function: public.entityize(text)

Returns: text

Language: PLPERLU

    use Unicode::Normalize;

    my $x = NFC(shift);
    $x =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
    return $x;


Function: public.explode_array(anyarray)

Returns: SET OF anyelement

Language: SQL

    SELECT ($1)[s] FROM generate_series(1, array_upper($1, 1)) AS s;

Function: public.extract_acq_marc_field(bigint, text, text)

Returns: text

Language: SQL

	SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);

Function: public.extract_marc_field(text, bigint, text)

Returns: text

Language: SQL

	SELECT public.extract_marc_field($1,$2,$3,'');

Function: public.extract_marc_field(text, bigint, text, text)

Returns: text

Language: SQL

    SELECT regexp_replace(array_to_string( array_accum( output ),' ' ),$4,'','g') FROM xpath_table('id', 'marc', $1, $3, 'id='||$2)x(id INT, output TEXT);

Function: public.first(anyelement)

Returns: anyelement

Language: INTERNAL

aggregate_dummy

Function: public.first5(text)

Returns: text

Language: SQL

	SELECT SUBSTRING( $1, 1, 5);

Function: public.first_agg(anyelement, anyelement)

Returns: anyelement

Language: SQL

	SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;

Function: public.first_word(text)

Returns: text

Language: SQL

        SELECT SUBSTRING( $1 FROM $_$^\S+$_$);

Function: public.get_covers(tsvector, tsquery)

Returns: text

Language: C

tsa_get_covers

Function: public.headline(oid, text, tsquery)

Returns: text

Language: INTERNAL

ts_headline_byid

Function: public.headline(oid, text, tsquery, text)

Returns: text

Language: INTERNAL

ts_headline_byid_opt

Function: public.headline(text, text, tsquery)

Returns: text

Language: C

tsa_headline_byname

Function: public.headline(text, text, tsquery, text)

Returns: text

Language: C

tsa_headline_byname

Function: public.headline(text, tsquery)

Returns: text

Language: INTERNAL

ts_headline

Function: public.headline(text, tsquery, text)

Returns: text

Language: INTERNAL

ts_headline_opt

Function: public.ingest_acq_marc()

Returns: trigger

Language: PLPGSQL

DECLARE
	value		TEXT;
	atype		TEXT;
	prov		INT;
	adef		RECORD;
	xpath_string	TEXT;
BEGIN
	FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP

		SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;

		IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
			IF (atype = 'lineitem_provider_attr_definition') THEN
				SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
				CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
			END IF;
			
			IF (atype = 'lineitem_provider_attr_definition') THEN
				SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
			ELSIF (atype = 'lineitem_marc_attr_definition') THEN
				SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
			ELSIF (atype = 'lineitem_generated_attr_definition') THEN
				SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
			END IF;

			SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;

			IF (value IS NOT NULL AND value <> '') THEN
				INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
					VALUES (NEW.id, adef.id, atype, adef.code, value);
			END IF;

		END IF;

	END LOOP;

	RETURN NULL;
END;

Function: public.last(anyelement)

Returns: anyelement

Language: INTERNAL

aggregate_dummy

Function: public.last_agg(anyelement, anyelement)

Returns: anyelement

Language: SQL

	SELECT $2;

Function: public.length(tsvector)

Returns: integer

Language: INTERNAL

tsvector_length

Function: public.lexize(oid, text)

Returns: text[]

Language: INTERNAL

ts_lexize

Function: public.lexize(text)

Returns: text[]

Language: C

tsa_lexize_bycurrent

Function: public.lexize(text, text)

Returns: text[]

Language: C

tsa_lexize_byname

Function: public.lowercase(text)

Returns: text

Language: PLPERLU

    return lc(shift);

Function: public.naco_normalize(text)

Returns: text

Language: SQL

	SELECT public.naco_normalize($1,'');

Function: public.naco_normalize(text, text)

Returns: text

Language: PLPERLU

    use Unicode::Normalize;

	my $txt = lc(shift);
	my $sf = shift;

    $txt = NFD($txt);
	$txt =~ s/\pM+//go;	# Remove diacritics

	$txt =~ s/\xE6/AE/go;	# Convert ae digraph
	$txt =~ s/\x{153}/OE/go;# Convert oe digraph
	$txt =~ s/\xFE/TH/go;	# Convert Icelandic thorn

	$txt =~ tr/\x{2070}\x{2071}\x{2072}\x{2073}\x{2074}\x{2075}\x{2076}\x{2077}\x{2078}\x{2079}\x{207A}\x{207B}/0123456789+-/;# Convert superscript numbers
	$txt =~ tr/\x{2080}\x{2081}\x{2082}\x{2083}\x{2084}\x{2085}\x{2086}\x{2087}\x{2088}\x{2089}\x{208A}\x{208B}/0123456889+-/;# Convert subscript numbers

	$txt =~ tr/\x{0251}\x{03B1}\x{03B2}\x{0262}\x{03B3}/AABGG/;	 	# Convert Latin and Greek
	$txt =~ tr/\x{2113}\xF0\!\"\(\)\-\{\}\<\>\;\:\.\?\xA1\xBF\/\\\@\*\%\=\xB1\+\xAE\xA9\x{2117}\$\xA3\x{FFE1}\xB0\^\_\~\`/LD /;	# Convert Misc
	$txt =~ tr/\'\[\]\|//d;							# Remove Misc

	if ($sf && $sf =~ /^a/o) {
		my $commapos = index($txt,',');
		if ($commapos > -1) {
			if ($commapos != length($txt) - 1) {
				my @list = split /,/, $txt;
				my $first = shift @list;
				$txt = $first . ',' . join(' ', @list);
			} else {
				$txt =~ s/,/ /go;
			}
		}
	} else {
		$txt =~ s/,/ /go;
	}

	$txt =~ s/\s+/ /go;	# Compress multiple spaces
	$txt =~ s/^\s+//o;	# Remove leading space
	$txt =~ s/\s+$//o;	# Remove trailing space

	return $txt;

Function: public.non_filing_normalize(text, "char")

Returns: text

Language: SQL

        SELECT  SUBSTRING(
                        REGEXP_REPLACE(
                                REGEXP_REPLACE(
                                        $1,
                                        E'\W*$',
					''
				),
                                '  ',
                                ' '
                        ),
                        CASE
				WHEN $2::INT NOT BETWEEN 48 AND 57 THEN 1
				ELSE $2::TEXT::INT + 1
			END
		);

Function: public.normal_rand(integer, double precision, double precision)

Returns: SET OF double precision

Language: C

normal_rand

Function: public.normalize_space(text)

Returns: text

Language: SQL

    SELECT regexp_replace(regexp_replace(regexp_replace($1, E'\\n', ' ', 'g'), E'(?:^\\s+)|(\\s+$)', '', 'g'), E'\\s+', ' ', 'g');

Function: public.numnode(tsquery)

Returns: integer

Language: INTERNAL

tsquery_numnode

Function: public.oils_i18n_gettext(integer, text, text, text)

Returns: text

Language: SQL

    SELECT $2;

Function: public.oils_i18n_gettext(text, text, text, text)

Returns: text

Language: SQL

    SELECT $2;

Function: public.oils_i18n_xlate(raw_locale text, keyvalue text, identcol text, keycol text, keyclass text, keytable text)

Returns: text

Language: PLPGSQL

DECLARE
    locale      TEXT := REGEXP_REPLACE( REGEXP_REPLACE( raw_locale, E'[;, ].+$', '' ), E'_', '-', 'g' );
    language    TEXT := REGEXP_REPLACE( locale, E'-.+$', '' );
    result      config.i18n_core%ROWTYPE;
    fallback    TEXT;
    keyfield    TEXT := keyclass || '.' || keycol;
BEGIN

    -- Try the full locale
    SELECT  * INTO result
      FROM  config.i18n_core
      WHERE fq_field = keyfield
            AND identity_value = keyvalue
            AND translation = locale;

    -- Try just the language
    IF NOT FOUND THEN
        SELECT  * INTO result
          FROM  config.i18n_core
          WHERE fq_field = keyfield
                AND identity_value = keyvalue
                AND translation = language;
    END IF;

    -- Fall back to the string we passed in in the first place
    IF NOT FOUND THEN
	EXECUTE
            'SELECT ' ||
                keycol ||
            ' FROM ' || keytable ||
            ' WHERE ' || identcol || ' = ' || quote_literal(keyvalue)
                INTO fallback;
        RETURN fallback;
    END IF;

    RETURN result.string;
END;

Function: public.oils_tsearch2()

Returns: trigger

Language: PLPGSQL

BEGIN
	NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, NEW.value);
	RETURN NEW;
END;

Function: public.parse(oid, text)

Returns: SET OF tokenout

Language: INTERNAL

ts_parse_byid

Function: public.parse(text)

Returns: SET OF tokenout

Language: C

tsa_parse_current

Function: public.parse(text, text)

Returns: SET OF tokenout

Language: INTERNAL

ts_parse_byname

Function: public.plainto_tsquery(oid, text)

Returns: tsquery

Language: INTERNAL

plainto_tsquery_byid

Function: public.plainto_tsquery(text)

Returns: tsquery

Language: INTERNAL

plainto_tsquery

Function: public.plainto_tsquery(text, text)

Returns: tsquery

Language: C

tsa_plainto_tsquery_name

Function: public.prsd_end(internal)

Returns: void

Language: C

tsa_prsd_end

Function: public.prsd_getlexeme(internal, internal, internal)

Returns: integer

Language: C

tsa_prsd_getlexeme

Function: public.prsd_headline(internal, internal, internal)

Returns: internal

Language: C

tsa_prsd_headline

Function: public.prsd_lextype(internal)

Returns: internal

Language: C

tsa_prsd_lextype

Function: public.prsd_start(internal, integer)

Returns: internal

Language: C

tsa_prsd_start

Function: public.querytree(tsquery)

Returns: text

Language: INTERNAL

tsquerytree

Function: public.rank(real[], tsvector, tsquery)

Returns: real

Language: INTERNAL

ts_rank_wtt

Function: public.rank(real[], tsvector, tsquery, integer)

Returns: real

Language: INTERNAL

ts_rank_wttf

Function: public.rank(tsvector, tsquery)

Returns: real

Language: INTERNAL

ts_rank_tt

Function: public.rank(tsvector, tsquery, integer)

Returns: real

Language: INTERNAL

ts_rank_ttf

Function: public.rank_cd(real[], tsvector, tsquery)

Returns: real

Language: INTERNAL

ts_rankcd_wtt

Function: public.rank_cd(real[], tsvector, tsquery, integer)

Returns: real

Language: INTERNAL

ts_rankcd_wttf

Function: public.rank_cd(tsvector, tsquery)

Returns: real

Language: INTERNAL

ts_rankcd_tt

Function: public.rank_cd(tsvector, tsquery, integer)

Returns: real

Language: INTERNAL

ts_rankcd_ttf

Function: public.remove_diacritics(text)

Returns: text

Language: PLPERLU

    use Unicode::Normalize;

    my $x = NFD(shift);
    $x =~ s/\pM+//go;
    return $x;


Function: public.reset_tsearch()

Returns: void

Language: C

tsa_reset_tsearch

Function: public.rewrite(tsquery, text)

Returns: tsquery

Language: INTERNAL

tsquery_rewrite_query

Function: public.rewrite(tsquery, tsquery, tsquery)

Returns: tsquery

Language: INTERNAL

tsquery_rewrite

Function: public.rewrite(tsquery[])

Returns: tsquery

Language: INTERNAL

aggregate_dummy

Function: public.rewrite_accum(tsquery, tsquery[])

Returns: tsquery

Language: C

tsa_rewrite_accum

Function: public.rewrite_finish(tsquery)

Returns: tsquery

Language: C

tsa_rewrite_finish

Function: public.set_curcfg(integer)

Returns: void

Language: C

tsa_set_curcfg

Function: public.set_curcfg(text)

Returns: void

Language: C

tsa_set_curcfg_byname

Function: public.set_curdict(integer)

Returns: void

Language: C

tsa_set_curdict

Function: public.set_curdict(text)

Returns: void

Language: C

tsa_set_curdict_byname

Function: public.set_curprs(integer)

Returns: void

Language: C

tsa_set_curprs

Function: public.set_curprs(text)

Returns: void

Language: C

tsa_set_curprs_byname

Function: public.setweight(tsvector, "char")

Returns: tsvector

Language: INTERNAL

tsvector_setweight

Function: public.show_curcfg()

Returns: oid

Language: INTERNAL

get_current_ts_config

Function: public.snb_en_init(internal)

Returns: internal

Language: C

tsa_snb_en_init

Function: public.snb_lexize(internal, internal, integer)

Returns: internal

Language: C

tsa_snb_lexize

Function: public.snb_ru_init(internal)

Returns: internal

Language: C

tsa_snb_ru_init

Function: public.snb_ru_init_koi8(internal)

Returns: internal

Language: C

tsa_snb_ru_init_koi8

Function: public.snb_ru_init_utf8(internal)

Returns: internal

Language: C

tsa_snb_ru_init_utf8

Function: public.spell_init(internal)

Returns: internal

Language: C

tsa_spell_init

Function: public.spell_lexize(internal, internal, integer)

Returns: internal

Language: C

tsa_spell_lexize

Function: public.stat(text)

Returns: SET OF statinfo

Language: INTERNAL

ts_stat1

Function: public.stat(text, text)

Returns: SET OF statinfo

Language: INTERNAL

ts_stat2

Function: public.strip(tsvector)

Returns: tsvector

Language: INTERNAL

tsvector_strip

Function: public.syn_init(internal)

Returns: internal

Language: C

tsa_syn_init

Function: public.syn_lexize(internal, internal, integer)

Returns: internal

Language: C

tsa_syn_lexize

Function: public.tableoid2name(oid)

Returns: text

Language: PLPGSQL

	BEGIN
		RETURN $1::regclass;
	END;

Function: public.text_concat(text, text)

Returns: text

Language: SQL

SELECT
	CASE	WHEN $1 IS NULL
			THEN $2
		WHEN $2 IS NULL
			THEN $1
		ELSE $1 || ' ' || $2
	END;

Function: public.thesaurus_init(internal)

Returns: internal

Language: C

tsa_thesaurus_init

Function: public.thesaurus_lexize(internal, internal, integer, internal)

Returns: internal

Language: C

tsa_thesaurus_lexize

Function: public.to_tsquery(oid, text)

Returns: tsquery

Language: INTERNAL

to_tsquery_byid

Function: public.to_tsquery(text)

Returns: tsquery

Language: INTERNAL

to_tsquery

Function: public.to_tsquery(text, text)

Returns: tsquery

Language: C

tsa_to_tsquery_name

Function: public.to_tsvector(oid, text)

Returns: tsvector

Language: INTERNAL

to_tsvector_byid

Function: public.to_tsvector(text)

Returns: tsvector

Language: INTERNAL

to_tsvector

Function: public.to_tsvector(text, text)

Returns: tsvector

Language: C

tsa_to_tsvector_name

Function: public.token_type()

Returns: SET OF tokentype

Language: C

tsa_token_type_current

Function: public.token_type(integer)

Returns: SET OF tokentype

Language: INTERNAL

ts_token_type_byid

Function: public.token_type(text)

Returns: SET OF tokentype

Language: INTERNAL

ts_token_type_byname

Function: public.ts_debug(text)

Returns: SET OF tsdebug

Language: SQL

select
        (select c.cfgname::text from pg_catalog.pg_ts_config as c
         where c.oid = show_curcfg()),
        t.alias as tok_type,
        t.descr as description,
        p.token,
        ARRAY ( SELECT m.mapdict::pg_catalog.regdictionary::pg_catalog.text
                FROM pg_catalog.pg_ts_config_map AS m
                WHERE m.mapcfg = show_curcfg() AND m.maptokentype = p.tokid
                ORDER BY m.mapseqno )
        AS dict_name,
        strip(to_tsvector(p.token)) as tsvector
from
        parse( _get_parser_from_curcfg(), $1 ) as p,
        token_type() as t
where
        t.tokid = p.tokid

Function: public.tsearch2()

Returns: trigger

Language: C

tsa_tsearch2

Function: public.tsq_mcontained(tsquery, tsquery)

Returns: boolean

Language: INTERNAL

tsq_mcontained

Function: public.tsq_mcontains(tsquery, tsquery)

Returns: boolean

Language: INTERNAL

tsq_mcontains

Function: public.tsquery_and(tsquery, tsquery)

Returns: tsquery

Language: INTERNAL

tsquery_and

Function: public.tsquery_not(tsquery)

Returns: tsquery

Language: INTERNAL

tsquery_not

Function: public.tsquery_or(tsquery, tsquery)

Returns: tsquery

Language: INTERNAL

tsquery_or

Function: public.tsvector_concat(tsvector, tsvector)

Returns: tsvector

Language: SQL

SELECT
	CASE	WHEN $1 IS NULL
			THEN $2
		WHEN $2 IS NULL
			THEN $1
		ELSE $1 || ' ' || $2
	END;

Function: public.uppercase(text)

Returns: text

Language: PLPERLU

    return uc(shift);

Function: public.xml_encode_special_chars(text)

Returns: text

Language: C

xml_encode_special_chars

Function: public.xml_is_well_formed(text)

Returns: boolean

Language: C

xml_is_well_formed

Function: public.xml_valid(text)

Returns: boolean

Language: C

xml_is_well_formed

Function: public.xpath_bool(text, text)

Returns: boolean

Language: C

xpath_bool

Function: public.xpath_list(text, text)

Returns: text

Language: SQL

SELECT xpath_list($1,$2,',')

Function: public.xpath_list(text, text, text)

Returns: text

Language: C

xpath_list

Function: public.xpath_nodeset(text, text)

Returns: text

Language: SQL

SELECT xpath_nodeset($1,$2,'','')

Function: public.xpath_nodeset(text, text, text)

Returns: text

Language: SQL

SELECT xpath_nodeset($1,$2,'',$3)

Function: public.xpath_nodeset(text, text, text, text)

Returns: text

Language: C

xpath_nodeset

Function: public.xpath_number(text, text)

Returns: real

Language: C

xpath_number

Function: public.xpath_string(text, text)

Returns: text

Language: C

xpath_string

Function: public.xpath_table(text, text, text, text, text)

Returns: SET OF record

Language: C

xpath_table

Function: public.xslt_process(text, text)

Returns: text

Language: C

xslt_process

Function: public.xslt_process(text, text, text)

Returns: text

Language: C

xslt_process

Schema reporter


View: reporter.circ_type

reporter.circ_type Structure
F-Key Name Type Description
id bigint
type text
SELECT circulation.id
, CASE WHEN 
(
     (circulation.opac_renewal 
          OR circulation.phone_renewal
     )
    OR circulation.desk_renewal
) THEN 'RENEWAL'::text ELSE 'CHECKOUT'::text END AS type 
FROM action.circulation;

Index - Schema reporter


View: reporter.currently_running

reporter.currently_running Structure
F-Key Name Type Description
id integer
runner_barcode text
name text
run_time timestamp with time zone
scheduled_wait_time interval
SELECT s.id
, c.barcode AS runner_barcode
, r.name
, s.run_time
, (s.run_time - now
     ()
) AS scheduled_wait_time 
FROM (
     (
           (reporter.schedule s 
              JOIN reporter.report r 
                ON (
                       (r.id = s.report)
                 )
           )
        JOIN actor.usr u 
          ON (
                 (s.runner = u.id)
           )
     )
  JOIN actor.card c 
    ON (
           (c.id = u.card)
     )
)
WHERE (
     (s.start_time IS NOT NULL)
   AND (s.complete_time IS NULL)
);

Index - Schema reporter


View: reporter.demographic

reporter.demographic Structure
F-Key Name Type Description
id integer
dob timestamp with time zone
general_division text
SELECT u.id
, u.dob
, CASE WHEN 
(u.dob IS NULL) THEN 'Adult'::text WHEN 
(age
     (u.dob) > '18 years'::interval
) THEN 'Adult'::text ELSE 'Juvenile'::text END AS general_division 
FROM actor.usr u;

Index - Schema reporter


View: reporter.hold_request_record

reporter.hold_request_record Structure
F-Key Name Type Description
id integer
target bigint
hold_type text
bib_record bigint
SELECT ahr.id
, ahr.target
, ahr.hold_type
, CASE WHEN 
(ahr.hold_type = 'T'::text) THEN ahr.target WHEN 
(ahr.hold_type = 'V'::text) THEN 
(
SELECT cn.record 
  FROM asset.call_number cn 
 WHERE (cn.id = ahr.target)
) WHEN 
(ahr.hold_type = 'C'::text) THEN 
(
SELECT cn.record 
  FROM (asset.call_number cn 
        JOIN asset.copy cp 
          ON (
                 (cn.id = cp.call_number)
           )
     )
 WHERE (cp.id = ahr.target)
) WHEN 
(ahr.hold_type = 'M'::text) THEN 
(
SELECT mr.master_record 
  FROM metabib.metarecord mr 
 WHERE (mr.id = ahr.target)
) ELSE NULL::bigint END AS bib_record 
FROM action.hold_request ahr;

Index - Schema reporter


Table: reporter.materialized_simple_record

reporter.materialized_simple_record Structure
F-Key Name Type Description
id bigint PRIMARY KEY
fingerprint text
quality integer
tcn_source text
tcn_value text
title text
author text
publisher text
pubdate text
isbn text[]
issn text[]

Index - Schema reporter


View: reporter.old_super_simple_record

reporter.old_super_simple_record Structure
F-Key Name Type Description
id bigint
fingerprint text
quality integer
tcn_source text
tcn_value text
title text
author text
publisher text
pubdate text
isbn text[]
issn text[]
SELECT r.id
, r.fingerprint
, r.quality
, r.tcn_source
, r.tcn_value
, title.value AS title
, first
(author.value) AS author
, publisher.value AS publisher
,"substring"
(pubdate.value
     ,'\\d+'::text
) AS pubdate
, array_accum
("substring"
     (isbn.value
           ,'^\\S+'::text
     )
) AS isbn
, array_accum
("substring"
     (issn.value
           ,'^\\S+'::text
     )
) AS issn 
FROM (
     (
           (
                 (
                       (
                             (biblio.record_entry r 
                           LEFT JOIN metabib.full_rec title 
                                  ON (
                                         (
                                               (
                                                     (r.id = title.record)
                                                   AND (title.tag = '245'::bpchar)
                                               )
                                             AND (title.subfield = 'a'::text)
                                         )
                                   )
                             )
                     LEFT JOIN metabib.full_rec author 
                            ON (
                                   (
                                         (
                                               (r.id = author.record)
                                             AND (author.tag = ANY 
                                                     (ARRAY['100'::bpchar
                                                           ,'110'::bpchar
                                                           ,'111'::bpchar]
                                                     )
                                               )
                                         )
                                       AND (author.subfield = 'a'::text)
                                   )
                             )
                       )
               LEFT JOIN metabib.full_rec publisher 
                      ON (
                             (
                                   (
                                         (r.id = publisher.record)
                                       AND (publisher.tag = '260'::bpchar)
                                   )
                                 AND (publisher.subfield = 'b'::text)
                             )
                       )
                 )
         LEFT JOIN metabib.full_rec pubdate 
                ON (
                       (
                             (
                                   (r.id = pubdate.record)
                                 AND (pubdate.tag = '260'::bpchar)
                             )
                           AND (pubdate.subfield = 'c'::text)
                       )
                 )
           )
   LEFT JOIN metabib.full_rec isbn 
          ON (
                 (
                       (
                             (r.id = isbn.record)
                           AND (isbn.tag = ANY 
                                   (ARRAY['024'::bpchar
                                         ,'020'::bpchar]
                                   )
                             )
                       )
                     AND (isbn.subfield = ANY 
                             (ARRAY['a'::text
                                   ,'z'::text]
                             )
                       )
                 )
           )
     )
LEFT JOIN metabib.full_rec issn 
    ON (
           (
                 (
                       (r.id = issn.record)
                     AND (issn.tag = '022'::bpchar)
                 )
               AND (issn.subfield = 'a'::text)
           )
     )
)
GROUP BY r.id
, r.fingerprint
, r.quality
, r.tcn_source
, r.tcn_value
, title.value
, publisher.value
,"substring"
(pubdate.value
     ,'\\d+'::text
);

Index - Schema reporter


Table: reporter.output_folder

reporter.output_folder Structure
F-Key Name Type Description
id serial PRIMARY KEY
reporter.output_folder.id parent integer
actor.usr.id owner integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
name text NOT NULL
shared boolean NOT NULL DEFAULT false
actor.org_unit.id share_with integer

Tables referencing this one via Foreign Key Constraints:

rpt_output_fldr_owner_idx owner

Index - Schema reporter


View: reporter.overdue_circs

reporter.overdue_circs Structure
F-Key Name Type Description
id bigint
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
unrecovered boolean
target_copy bigint
circ_lib integer
circ_staff integer
checkin_staff integer
checkin_lib integer
renewal_remaining integer
due_date timestamp with time zone
stop_fines_time timestamp with time zone
checkin_time timestamp with time zone
create_time timestamp with time zone
duration interval
fine_interval interval
recuring_fine numeric(6,2)
max_fine numeric(6,2)
phone_renewal boolean
desk_renewal boolean
opac_renewal boolean
duration_rule text
recuring_fine_rule text
max_fine_rule text
stop_fines text
SELECT circulation.id
, circulation.usr
, circulation.xact_start
, circulation.xact_finish
, circulation.unrecovered
, circulation.target_copy
, circulation.circ_lib
, circulation.circ_staff
, circulation.checkin_staff
, circulation.checkin_lib
, circulation.renewal_remaining
, circulation.due_date
, circulation.stop_fines_time
, circulation.checkin_time
, circulation.create_time
, circulation.duration
, circulation.fine_interval
, circulation.recuring_fine
, circulation.max_fine
, circulation.phone_renewal
, circulation.desk_renewal
, circulation.opac_renewal
, circulation.duration_rule
, circulation.recuring_fine_rule
, circulation.max_fine_rule
, circulation.stop_fines 
FROM action.circulation 
WHERE (
     (
           (circulation.checkin_time IS NULL)
         AND (
                 (circulation.stop_fines <> ALL 
                       (ARRAY['LOST'::text
                             ,'CLAIMSRETURNED'::text]
                       )
                 )
                OR (circulation.stop_fines IS NULL)
           )
     )
   AND (circulation.due_date < now
           ()
     )
);

Index - Schema reporter


View: reporter.overdue_reports

reporter.overdue_reports Structure
F-Key Name Type Description
id integer
runner_barcode text
name text
run_time timestamp with time zone
scheduled_wait_time interval
SELECT s.id
, c.barcode AS runner_barcode
, r.name
, s.run_time
, (s.run_time - now
     ()
) AS scheduled_wait_time 
FROM (
     (
           (reporter.schedule s 
              JOIN reporter.report r 
                ON (
                       (r.id = s.report)
                 )
           )
        JOIN actor.usr u 
          ON (
                 (s.runner = u.id)
           )
     )
  JOIN actor.card c 
    ON (
           (c.id = u.card)
     )
)
WHERE (
     (s.start_time IS NULL)
   AND (s.run_time < now
           ()
     )
);

Index - Schema reporter


View: reporter.pending_reports

reporter.pending_reports Structure
F-Key Name Type Description
id integer
runner_barcode text
name text
run_time timestamp with time zone
scheduled_wait_time interval
SELECT s.id
, c.barcode AS runner_barcode
, r.name
, s.run_time
, (s.run_time - now
     ()
) AS scheduled_wait_time 
FROM (
     (
           (reporter.schedule s 
              JOIN reporter.report r 
                ON (
                       (r.id = s.report)
                 )
           )
        JOIN actor.usr u 
          ON (
                 (s.runner = u.id)
           )
     )
  JOIN actor.card c 
    ON (
           (c.id = u.card)
     )
)
WHERE (s.start_time IS NULL);

Index - Schema reporter


Table: reporter.report

reporter.report Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id owner integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
name text NOT NULL DEFAULT ''::text
description text NOT NULL DEFAULT ''::text
reporter.template.id template integer NOT NULL
data text NOT NULL
reporter.report_folder.id folder integer NOT NULL
recur boolean NOT NULL DEFAULT false
recurance interval

Tables referencing this one via Foreign Key Constraints:

rpt_rpt_fldr_idx folder rpt_rpt_owner_idx owner

Index - Schema reporter


Table: reporter.report_folder

reporter.report_folder Structure
F-Key Name Type Description
id serial PRIMARY KEY
reporter.report_folder.id parent integer
actor.usr.id owner integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
name text NOT NULL
shared boolean NOT NULL DEFAULT false
actor.org_unit.id share_with integer

Tables referencing this one via Foreign Key Constraints:

rpt_rpt_fldr_owner_idx owner

Index - Schema reporter


Table: reporter.schedule

reporter.schedule Structure
F-Key Name Type Description
id serial PRIMARY KEY
reporter.report.id report integer NOT NULL
reporter.output_folder.id folder integer NOT NULL
actor.usr.id runner integer NOT NULL
run_time timestamp with time zone NOT NULL DEFAULT now()
start_time timestamp with time zone
complete_time timestamp with time zone
email text
excel_format boolean NOT NULL DEFAULT true
html_format boolean NOT NULL DEFAULT true
csv_format boolean NOT NULL DEFAULT true
chart_pie boolean NOT NULL DEFAULT false
chart_bar boolean NOT NULL DEFAULT false
chart_line boolean NOT NULL DEFAULT false
error_code integer
error_text text
rpt_sched_folder_idx folder rpt_sched_runner_idx runner

Index - Schema reporter


View: reporter.simple_record

reporter.simple_record Structure
F-Key Name Type Description
id bigint
metarecord bigint
fingerprint text
quality integer
tcn_source text
tcn_value text
title text
uniform_title text
author text
publisher text
pubdate text
series_title text
series_statement text
summary text
isbn text[]
issn text[]
topic_subject text[]
geographic_subject text[]
genre text[]
name_subject text[]
corporate_subject text[]
external_uri text[]
SELECT r.id
, s.metarecord
, r.fingerprint
, r.quality
, r.tcn_source
, r.tcn_value
, title.value AS title
, uniform_title.value AS uniform_title
, author.value AS author
, publisher.value AS publisher
,"substring"
(pubdate.value
     ,'\\d+'::text
) AS pubdate
, series_title.value AS series_title
, series_statement.value AS series_statement
, summary.value AS summary
, array_accum
("substring"
     (isbn.value
           ,'^\\S+'::text
     )
) AS isbn
, array_accum
("substring"
     (issn.value
           ,'^\\S+'::text
     )
) AS issn
, ARRAY
(
SELECT DISTINCT full_rec.value 
  FROM metabib.full_rec 
 WHERE (
           (
                 (full_rec.tag = '650'::bpchar)
               AND (full_rec.subfield = 'a'::text)
           )
         AND (full_rec.record = r.id)
     )
ORDER BY full_rec.value
) AS topic_subject
, ARRAY
(
SELECT DISTINCT full_rec.value 
  FROM metabib.full_rec 
 WHERE (
           (
                 (full_rec.tag = '651'::bpchar)
               AND (full_rec.subfield = 'a'::text)
           )
         AND (full_rec.record = r.id)
     )
ORDER BY full_rec.value
) AS geographic_subject
, ARRAY
(
SELECT DISTINCT full_rec.value 
  FROM metabib.full_rec 
 WHERE (
           (
                 (full_rec.tag = '655'::bpchar)
               AND (full_rec.subfield = 'a'::text)
           )
         AND (full_rec.record = r.id)
     )
ORDER BY full_rec.value
) AS genre
, ARRAY
(
SELECT DISTINCT full_rec.value 
  FROM metabib.full_rec 
 WHERE (
           (
                 (full_rec.tag = '600'::bpchar)
               AND (full_rec.subfield = 'a'::text)
           )
         AND (full_rec.record = r.id)
     )
ORDER BY full_rec.value
) AS name_subject
, ARRAY
(
SELECT DISTINCT full_rec.value 
  FROM metabib.full_rec 
 WHERE (
           (
                 (full_rec.tag = '610'::bpchar)
               AND (full_rec.subfield = 'a'::text)
           )
         AND (full_rec.record = r.id)
     )
ORDER BY full_rec.value
) AS corporate_subject
, ARRAY
(
SELECT full_rec.value 
  FROM metabib.full_rec 
 WHERE (
           (
                 (full_rec.tag = '856'::bpchar)
               AND (full_rec.subfield = ANY 
                       (ARRAY['3'::text
                             ,'y'::text
                             ,'u'::text]
                       )
                 )
           )
         AND (full_rec.record = r.id)
     )
ORDER BY CASE WHEN 
     (full_rec.subfield = ANY 
           (ARRAY['3'::text
                 ,'y'::text]
           )
     ) THEN 0 ELSE 1 END
) AS external_uri 
FROM (
     (
           (
                 (
                       (
                             (
                                   (
                                         (
                                               (
                                                     (
                                                           (biblio.record_entry r 
                                                              JOIN metabib.metarecord_source_map s 
                                                                ON (
                                                                       (s.source = r.id)
                                                                 )
                                                           )
                                                   LEFT JOIN metabib.full_rec uniform_title 
                                                          ON (
                                                                 (
                                                                       (
                                                                             (r.id = uniform_title.record)
                                                                           AND (uniform_title.tag = '240'::bpchar)
                                                                       )
                                                                     AND (uniform_title.subfield = 'a'::text)
                                                                 )
                                                           )
                                                     )
                                             LEFT JOIN metabib.full_rec title 
                                                    ON (
                                                           (
                                                                 (
                                                                       (r.id = title.record)
                                                                     AND (title.tag = '245'::bpchar)
                                                                 )
                                                               AND (title.subfield = 'a'::text)
                                                           )
                                                     )
                                               )
                                       LEFT JOIN metabib.full_rec author 
                                              ON (
                                                     (
                                                           (
                                                                 (r.id = author.record)
                                                               AND (author.tag = '100'::bpchar)
                                                           )
                                                         AND (author.subfield = 'a'::text)
                                                     )
                                               )
                                         )
                                 LEFT JOIN metabib.full_rec publisher 
                                        ON (
                                               (
                                                     (
                                                           (r.id = publisher.record)
                                                         AND (publisher.tag = '260'::bpchar)
                                                     )
                                                   AND (publisher.subfield = 'b'::text)
                                               )
                                         )
                                   )
                           LEFT JOIN metabib.full_rec pubdate 
                                  ON (
                                         (
                                               (
                                                     (r.id = pubdate.record)
                                                   AND (pubdate.tag = '260'::bpchar)
                                               )
                                             AND (pubdate.subfield = 'c'::text)
                                         )
                                   )
                             )
                     LEFT JOIN metabib.full_rec isbn 
                            ON (
                                   (
                                         (
                                               (r.id = isbn.record)
                                             AND (isbn.tag = ANY 
                                                     (ARRAY['024'::bpchar
                                                           ,'020'::bpchar]
                                                     )
                                               )
                                         )
                                       AND (isbn.subfield = ANY 
                                               (ARRAY['a'::text
                                                     ,'z'::text]
                                               )
                                         )
                                   )
                             )
                       )
               LEFT JOIN metabib.full_rec issn 
                      ON (
                             (
                                   (
                                         (r.id = issn.record)
                                       AND (issn.tag = '022'::bpchar)
                                   )
                                 AND (issn.subfield = 'a'::text)
                             )
                       )
                 )
         LEFT JOIN metabib.full_rec series_title 
                ON (
                       (
                             (
                                   (r.id = series_title.record)
                                 AND (series_title.tag = ANY 
                                         (ARRAY['830'::bpchar
                                               ,'440'::bpchar]
                                         )
                                   )
                             )
                           AND (series_title.subfield = 'a'::text)
                       )
                 )
           )
   LEFT JOIN metabib.full_rec series_statement 
          ON (
                 (
                       (
                             (r.id = series_statement.record)
                           AND (series_statement.tag = '490'::bpchar)
                       )
                     AND (series_statement.subfield = 'a'::text)
                 )
           )
     )
LEFT JOIN metabib.full_rec summary 
    ON (
           (
                 (
                       (r.id = summary.record)
                     AND (summary.tag = '520'::bpchar)
                 )
               AND (summary.subfield = 'a'::text)
           )
     )
)
GROUP BY r.id
, s.metarecord
, r.fingerprint
, r.quality
, r.tcn_source
, r.tcn_value
, title.value
, uniform_title.value
, author.value
, publisher.value
,"substring"
(pubdate.value
     ,'\\d+'::text
)
, series_title.value
, series_statement.value
, summary.value;

Index - Schema reporter


View: reporter.super_simple_record

reporter.super_simple_record Structure
F-Key Name Type Description
id bigint
fingerprint text
quality integer
tcn_source text
tcn_value text
title text
author text
publisher text
pubdate text
isbn text[]
issn text[]
SELECT materialized_simple_record.id
, materialized_simple_record.fingerprint
, materialized_simple_record.quality
, materialized_simple_record.tcn_source
, materialized_simple_record.tcn_value
, materialized_simple_record.title
, materialized_simple_record.author
, materialized_simple_record.publisher
, materialized_simple_record.pubdate
, materialized_simple_record.isbn
, materialized_simple_record.issn 
FROM reporter.materialized_simple_record;

Index - Schema reporter


Table: reporter.template

reporter.template Structure
F-Key Name Type Description
id serial PRIMARY KEY
actor.usr.id owner integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
name text NOT NULL
description text NOT NULL
data text NOT NULL
reporter.template_folder.id folder integer NOT NULL

Tables referencing this one via Foreign Key Constraints:

rpt_tmpl_fldr_idx folder rpt_tmpl_owner_idx owner

Index - Schema reporter


Table: reporter.template_folder

reporter.template_folder Structure
F-Key Name Type Description
id serial PRIMARY KEY
reporter.template_folder.id parent integer
actor.usr.id owner integer NOT NULL
create_time timestamp with time zone NOT NULL DEFAULT now()
name text NOT NULL
shared boolean NOT NULL DEFAULT false
actor.org_unit.id share_with integer

Tables referencing this one via Foreign Key Constraints:

rpt_tmpl_fldr_owner_idx owner

Index - Schema reporter


View: reporter.xact_billing_totals

reporter.xact_billing_totals Structure
F-Key Name Type Description
xact bigint
unvoided numeric
voided numeric
total numeric
SELECT b.xact
, sum
(CASE WHEN b.voided THEN 
     (0)::numeric ELSE b.amount END
) AS unvoided
, sum
(CASE WHEN b.voided THEN b.amount ELSE 
     (0)::numeric END
) AS voided
, sum
(b.amount) AS total 
FROM money.billing b 
GROUP BY b.xact;

Index - Schema reporter


View: reporter.xact_paid_totals

reporter.xact_paid_totals Structure
F-Key Name Type Description
xact bigint
unvoided numeric
voided numeric
total numeric
SELECT b.xact
, sum
(CASE WHEN b.voided THEN 
     (0)::numeric ELSE b.amount END
) AS unvoided
, sum
(CASE WHEN b.voided THEN b.amount ELSE 
     (0)::numeric END
) AS voided
, sum
(b.amount) AS total 
FROM money.payment b 
GROUP BY b.xact;

Index - Schema reporter


Function: reporter.disable_materialized_simple_record_trigger()

Returns: void

Language: SQL

    DROP TRIGGER zzz_update_materialized_simple_record_tgr ON metabib.real_full_rec;

Function: reporter.enable_materialized_simple_record_trigger()

Returns: void

Language: SQL


    TRUNCATE TABLE reporter.materialized_simple_record;

    INSERT INTO reporter.materialized_simple_record
        (id,fingerprint,quality,tcn_source,tcn_value,title,author,publisher,pubdate,isbn,issn)
        SELECT DISTINCT ON (id) * FROM reporter.old_super_simple_record;

    CREATE TRIGGER zzz_update_materialized_simple_record_tgr
        AFTER INSERT OR UPDATE OR DELETE ON metabib.real_full_rec
        FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_sync();


Function: reporter.refresh_materialized_simple_record()

Returns: void

Language: SQL

    SELECT reporter.disable_materialized_simple_record_trigger();
    SELECT reporter.enable_materialized_simple_record_trigger();

Function: reporter.simple_rec_bib_sync()

Returns: trigger

Language: PLPGSQL

BEGIN
    IF NEW.deleted THEN
        DELETE FROM reporter.materialized_simple_record WHERE id = NEW.id;
        RETURN NEW;
    ELSE
        RETURN NEW;
    END IF;
END;

Function: reporter.simple_rec_delete(r_id bigint)

Returns: boolean

Language: SQL

    SELECT reporter.simple_rec_update($1, TRUE);

Function: reporter.simple_rec_sync()

Returns: trigger

Language: PLPGSQL

DECLARE
    r_id        BIGINT;
    deleted     BOOL;
BEGIN
    IF TG_OP IN ('DELETE') THEN
        r_id := OLD.record;
        deleted := TRUE;
    ELSE
        r_id := NEW.record;
        deleted := FALSE;
    END IF;

    PERFORM reporter.simple_rec_update(r_id, deleted);

    IF deleted THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;

END;

Function: reporter.simple_rec_update(deleted bigint, r_id boolean)

Returns: boolean

Language: PLPGSQL

BEGIN

    DELETE FROM reporter.materialized_simple_record WHERE id = r_id;

    IF NOT deleted THEN
        INSERT INTO reporter.materialized_simple_record SELECT DISTINCT ON (id) * FROM reporter.old_super_simple_record WHERE id = r_id;
    END IF;

    RETURN TRUE;

END;

Function: reporter.simple_rec_update(r_id bigint)

Returns: boolean

Language: SQL

    SELECT reporter.simple_rec_update($1, FALSE);

Schema search


Table: search.relevance_adjustment

search.relevance_adjustment Structure
F-Key Name Type Description
id serial PRIMARY KEY
active boolean NOT NULL DEFAULT true
config.metabib_field.id field integer NOT NULL
bump_type text NOT NULL
multiplier numeric NOT NULL DEFAULT 1.0

 

search.relevance_adjustment Constraints
Name Constraint
relevance_adjustment_bump_type_check CHECK ((bump_type = ANY (ARRAY['word_order'::text, 'first_word'::text, 'full_match'::text])))

Index - Schema search


Function: search.explode_array(anyarray)

Returns: SET OF anyelement

Language: SQL

    SELECT ($1)[s] FROM generate_series(1, array_upper($1, 1)) AS s;

Function: search.parse_search_args(text)

Returns: SET OF search_args

Language: PLPERLU

    use JSON::XS;
    my $json = shift;

    my $args = decode_json( $json );

    my $id = 1;

    for my $k ( keys %$args ) {
        (my $alias = $k) =~ s/\|/_/gso;
        my ($class, $field) = split /\|/, $k;
        my $part = $args->{$k};
        for my $p ( keys %$part ) {
            my $data = $part->{$p};
            $data = [$data] if (!ref($data));
            for my $datum ( @$data ) {
                return_next(
                    {   field_class => $class,
                        field_name  => $field,
                        term        => $datum,
                        table_alias => $alias,
                        term_type   => $p,
                        id          => $id,
                    }
                );
                $id++;
            }
        }
    }

    return undef;


Function: search.pick_table(text)

Returns: text

Language: SQL

    SELECT  CASE
                WHEN $1 = 'author'  THEN 'metabib.author_field_entry'
                WHEN $1 = 'title'   THEN 'metabib.title_field_entry'
                WHEN $1 = 'subject' THEN 'metabib.subject_field_entry'
                WHEN $1 = 'keyword' THEN 'metabib.keyword_field_entry'
                WHEN $1 = 'series'  THEN 'metabib.series_field_entry'
            END;

Function: search.staged_fts(param_skip_chk integer, param_chk_limit integer, param_rel_limit text, staff integer[], metarecord integer[], param_sort_desc text[], param_sort text[], param_pref_lang_multiplier text[], param_pref_lang text[], param_between text[], param_during text[], param_after text[], param_before text, param_bib_level text, param_vformats text, param_forms text[], param_types text, param_lit_form real, param_language text, param_audience boolean, param_locations boolean, param_statuses boolean, param_searches integer, param_depth integer, param_search_ou integer)

Returns: SET OF search_result

Language: PLPGSQL

DECLARE

    current_res         search.search_result%ROWTYPE;
    query_part          search.search_args%ROWTYPE;
    phrase_query_part   search.search_args%ROWTYPE;
    rank_adjust_id      INT;
    core_rel_limit      INT;
    core_chk_limit      INT;
    core_skip_chk       INT;
    rank_adjust         search.relevance_adjustment%ROWTYPE;
    query_table         TEXT;
    tmp_text            TEXT;
    tmp_int             INT;
    current_rank        TEXT;
    ranks               TEXT[] := '{}';
    query_table_alias   TEXT;
    from_alias_array    TEXT[] := '{}';
    used_ranks          TEXT[] := '{}';
    mb_field            INT;
    mb_field_list       INT[];
    search_org_list     INT[];
    select_clause       TEXT := 'SELECT';
    from_clause         TEXT := ' FROM  metabib.metarecord_source_map m JOIN metabib.rec_descriptor mrd ON (m.source = mrd.record) ';
    where_clause        TEXT := ' WHERE 1=1 ';
    mrd_used            BOOL := FALSE;
    sort_desc           BOOL := FALSE;

    core_result         RECORD;
    core_cursor         REFCURSOR;
    core_rel_query      TEXT;
    vis_limit_query     TEXT;
    inner_where_clause  TEXT;

    total_count         INT := 0;
    check_count         INT := 0;
    deleted_count       INT := 0;
    visible_count       INT := 0;
    excluded_count      INT := 0;

BEGIN

    core_rel_limit := COALESCE( param_rel_limit, 25000 );
    core_chk_limit := COALESCE( param_chk_limit, 1000 );
    core_skip_chk := COALESCE( param_skip_chk, 1 );

    IF metarecord THEN
        select_clause := select_clause || ' m.metarecord as id, array_accum(distinct m.source) as records,';
    ELSE
        select_clause := select_clause || ' m.source as id, array_accum(distinct m.source) as records,';
    END IF;

    -- first we need to construct the base query
    FOR query_part IN SELECT * FROM search.parse_search_args(param_searches) WHERE term_type = 'fts_query' LOOP

        inner_where_clause := 'index_vector @@ ' || query_part.term;

        IF query_part.field_name IS NOT NULL THEN

           SELECT  id INTO mb_field
             FROM  config.metabib_field
             WHERE field_class = query_part.field_class
                   AND name = query_part.field_name;

            IF FOUND THEN
                inner_where_clause := inner_where_clause ||
                    ' AND ' || 'field = ' || mb_field;
            END IF;

        END IF;

        -- moving on to the rank ...
        SELECT  * INTO query_part
          FROM  search.parse_search_args(param_searches)
          WHERE term_type = 'fts_rank'
                AND table_alias = query_part.table_alias;

        current_rank := query_part.term || ' * ' || query_part.table_alias || '_weight.weight';

        IF query_part.field_name IS NOT NULL THEN

           SELECT  array_accum(distinct id) INTO mb_field_list
             FROM  config.metabib_field
             WHERE field_class = query_part.field_class
                   AND name = query_part.field_name;

        ELSE

           SELECT  array_accum(distinct id) INTO mb_field_list
             FROM  config.metabib_field
             WHERE field_class = query_part.field_class;

        END IF;

        FOR rank_adjust IN SELECT * FROM search.relevance_adjustment WHERE active AND field IN ( SELECT * FROM search.explode_array( mb_field_list ) ) LOOP

            IF NOT rank_adjust.bump_type = ANY (used_ranks) THEN

                IF rank_adjust.bump_type = 'first_word' THEN
                    SELECT  term INTO tmp_text
                      FROM  search.parse_search_args(param_searches)
                      WHERE table_alias = query_part.table_alias AND term_type = 'word'
                      ORDER BY id
                      LIMIT 1;

                    tmp_text := query_part.table_alias || '.value ILIKE ' || quote_literal( tmp_text || '%' );

                ELSIF rank_adjust.bump_type = 'word_order' THEN
                    SELECT  array_to_string( array_accum( term ), '%' ) INTO tmp_text
                      FROM  search.parse_search_args(param_searches)
                      WHERE table_alias = query_part.table_alias AND term_type = 'word';

                    tmp_text := query_part.table_alias || '.value ILIKE ' || quote_literal( '%' || tmp_text || '%' );

                ELSIF rank_adjust.bump_type = 'full_match' THEN
                    SELECT  array_to_string( array_accum( term ), E'\\s+' ) INTO tmp_text
                      FROM  search.parse_search_args(param_searches)
                      WHERE table_alias = query_part.table_alias AND term_type = 'word';

                    tmp_text := query_part.table_alias || '.value  ~ ' || quote_literal( '^' || tmp_text || E'\\W*$' );

                END IF;


                IF tmp_text IS NOT NULL THEN
                    current_rank := current_rank || ' * ( CASE WHEN ' || tmp_text ||
                        ' THEN ' || rank_adjust.multiplier || '::REAL ELSE 1.0 END )';
                END IF;

                used_ranks := array_append( used_ranks, rank_adjust.bump_type );

            END IF;

        END LOOP;

        ranks := array_append( ranks, current_rank );
        used_ranks := '{}';

        FOR phrase_query_part IN
            SELECT  * 
              FROM  search.parse_search_args(param_searches)
              WHERE term_type = 'phrase'
                    AND table_alias = query_part.table_alias LOOP

            tmp_text := replace( phrase_query_part.term, '*', E'\\*' );
            tmp_text := replace( tmp_text, '?', E'\\?' );
            tmp_text := replace( tmp_text, '+', E'\\+' );
            tmp_text := replace( tmp_text, '|', E'\\|' );
            tmp_text := replace( tmp_text, '(', E'\\(' );
            tmp_text := replace( tmp_text, ')', E'\\)' );
            tmp_text := replace( tmp_text, '[', E'\\[' );
            tmp_text := replace( tmp_text, ']', E'\\]' );

            inner_where_clause := inner_where_clause || ' AND ' || 'value  ~* ' || quote_literal( E'(^|\\W+)' || regexp_replace(tmp_text, E'\\s+',E'\\\\s+','g') || E'(\\W+|\$)' );

        END LOOP;

        query_table := search.pick_table(query_part.field_class);

        from_clause := from_clause ||
            ' JOIN ( SELECT * FROM ' || query_table || ' WHERE ' || inner_where_clause ||
                    CASE WHEN core_rel_limit > 0 THEN ' LIMIT ' || core_rel_limit::TEXT ELSE '' END || ' ) AS ' || query_part.table_alias ||
                ' ON ( m.source = ' || query_part.table_alias || '.source )' ||
            ' JOIN config.metabib_field AS ' || query_part.table_alias || '_weight' ||
                ' ON ( ' || query_part.table_alias || '.field = ' || query_part.table_alias || '_weight.id  AND  ' || query_part.table_alias || '_weight.search_field)';

        from_alias_array := array_append(from_alias_array, query_part.table_alias);

    END LOOP;

    IF param_pref_lang IS NOT NULL AND param_pref_lang_multiplier IS NOT NULL THEN
        current_rank := ' CASE WHEN mrd.item_lang = ' || quote_literal( param_pref_lang ) ||
            ' THEN ' || param_pref_lang_multiplier || '::REAL ELSE 1.0 END ';

        -- ranks := array_append( ranks, current_rank );
    END IF;

    current_rank := ' AVG( ( (' || array_to_string( ranks, ') + (' ) || ') ) * ' || current_rank || ' ) ';
    select_clause := select_clause || current_rank || ' AS rel,';

    sort_desc = param_sort_desc;

    IF param_sort = 'pubdate' THEN

        tmp_text := '999999';
        IF param_sort_desc THEN tmp_text := '0'; END IF;

        current_rank := $$ COALESCE( FIRST(NULLIF(REGEXP_REPLACE(mrd.date1, E'\\D+', '9', 'g'),'')), $$ || quote_literal(tmp_text) || $$ )::INT $$;

    ELSIF param_sort = 'title' THEN

        tmp_text := 'zzzzzz';
        IF param_sort_desc THEN tmp_text := '    '; END IF;

        current_rank := $$
            ( COALESCE( FIRST ((
                SELECT  LTRIM(SUBSTR( frt.value, COALESCE(SUBSTRING(frt.ind2 FROM E'\\d+'),'0')::INT + 1 ))
                  FROM  metabib.full_rec frt
                  WHERE frt.record = m.source
                    AND frt.tag = '245'
                    AND frt.subfield = 'a'
                  LIMIT 1
            )),$$ || quote_literal(tmp_text) || $$))
        $$;

    ELSIF param_sort = 'author' THEN

        tmp_text := 'zzzzzz';
        IF param_sort_desc THEN tmp_text := '    '; END IF;

        current_rank := $$
            ( COALESCE( FIRST ((
                SELECT  LTRIM(fra.value)
                  FROM  metabib.full_rec fra
                  WHERE fra.record = m.source
                    AND fra.tag LIKE '1%'
                    AND fra.subfield = 'a'
                  ORDER BY fra.tag::text::int
                  LIMIT 1
            )),$$ || quote_literal(tmp_text) || $$))
        $$;

    ELSIF param_sort = 'create_date' THEN
            current_rank := $$( FIRST (( SELECT create_date FROM biblio.record_entry rbr WHERE rbr.id = m.source)) )$$;
    ELSIF param_sort = 'edit_date' THEN
            current_rank := $$( FIRST (( SELECT edit_date FROM biblio.record_entry rbr WHERE rbr.id = m.source)) )$$;
    ELSE
        sort_desc := NOT COALESCE(param_sort_desc, FALSE);
    END IF;

    select_clause := select_clause || current_rank || ' AS rank';

    -- now add the other qualifiers
    IF param_audience IS NOT NULL AND array_upper(param_audience, 1) > 0 THEN
        where_clause = where_clause || $$ AND mrd.audience IN ('$$ || array_to_string(param_audience, $$','$$) || $$') $$;
    END IF;

    IF param_language IS NOT NULL AND array_upper(param_language, 1) > 0 THEN
        where_clause = where_clause || $$ AND mrd.item_lang IN ('$$ || array_to_string(param_language, $$','$$) || $$') $$;
    END IF;

    IF param_lit_form IS NOT NULL AND array_upper(param_lit_form, 1) > 0 THEN
        where_clause = where_clause || $$ AND mrd.lit_form IN ('$$ || array_to_string(param_lit_form, $$','$$) || $$') $$;
    END IF;

    IF param_types IS NOT NULL AND array_upper(param_types, 1) > 0 THEN
        where_clause = where_clause || $$ AND mrd.item_type IN ('$$ || array_to_string(param_types, $$','$$) || $$') $$;
    END IF;

    IF param_forms IS NOT NULL AND array_upper(param_forms, 1) > 0 THEN
        where_clause = where_clause || $$ AND mrd.item_form IN ('$$ || array_to_string(param_forms, $$','$$) || $$') $$;
    END IF;

    IF param_vformats IS NOT NULL AND array_upper(param_vformats, 1) > 0 THEN
        where_clause = where_clause || $$ AND mrd.vr_format IN ('$$ || array_to_string(param_vformats, $$','$$) || $$') $$;
    END IF;

    IF param_bib_level IS NOT NULL AND array_upper(param_bib_level, 1) > 0 THEN
        where_clause = where_clause || $$ AND mrd.bib_level IN ('$$ || array_to_string(param_bib_level, $$','$$) || $$') $$;
    END IF;

    IF param_before IS NOT NULL AND param_before <> '' THEN
        where_clause = where_clause || $$ AND mrd.date1 <= $$ || quote_literal(param_before) || ' ';
    END IF;

    IF param_after IS NOT NULL AND param_after <> '' THEN
        where_clause = where_clause || $$ AND mrd.date1 >= $$ || quote_literal(param_after) || ' ';
    END IF;

    IF param_during IS NOT NULL AND param_during <> '' THEN
        where_clause = where_clause || $$ AND $$ || quote_literal(param_during) || $$ BETWEEN mrd.date1 AND mrd.date2 $$;
    END IF;

    IF param_between IS NOT NULL AND array_upper(param_between, 1) > 1 THEN
        where_clause = where_clause || $$ AND mrd.date1 BETWEEN '$$ || array_to_string(param_between, $$' AND '$$) || $$' $$;
    END IF;

    core_rel_query := select_clause || from_clause || where_clause ||
                        ' GROUP BY 1 ORDER BY 4' || CASE WHEN sort_desc THEN ' DESC' ELSE ' ASC' END || ';';
    --RAISE NOTICE 'Base Query:  %', core_rel_query;

    IF param_search_ou > 0 THEN
        IF param_depth IS NOT NULL THEN
            SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
        ELSE
            SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
        END IF;
    ELSIF param_search_ou < 0 THEN
        SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
    ELSIF param_search_ou = 0 THEN
        -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
    END IF;

    OPEN core_cursor FOR EXECUTE core_rel_query;

    LOOP

        FETCH core_cursor INTO core_result;
        EXIT WHEN NOT FOUND;


        IF total_count % 1000 = 0 THEN
            -- RAISE NOTICE ' % total, % checked so far ... ', total_count, check_count;
        END IF;

        IF core_chk_limit > 0 AND total_count - core_skip_chk + 1 >= core_chk_limit THEN
            total_count := total_count + 1;
            CONTINUE;
        END IF;

        total_count := total_count + 1;

        CONTINUE WHEN param_skip_chk IS NOT NULL and total_count < param_skip_chk;

        check_count := check_count + 1;

        PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
        IF NOT FOUND THEN
            -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
            deleted_count := deleted_count + 1;
            CONTINUE;
        END IF;

        PERFORM 1
          FROM  biblio.record_entry b
                JOIN config.bib_source s ON (b.source = s.id)
          WHERE s.transcendant
                AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );

        IF FOUND THEN
            -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
            visible_count := visible_count + 1;

            current_res.id = core_result.id;
            current_res.rel = core_result.rel;

            tmp_int := 1;
            IF metarecord THEN
                SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
            END IF;

            IF tmp_int = 1 THEN
                current_res.record = core_result.records[1];
            ELSE
                current_res.record = NULL;
            END IF;

            RETURN NEXT current_res;

            CONTINUE;
        END IF;

        PERFORM 1
          FROM  asset.call_number cn
                JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
                JOIN asset.uri uri ON (map.uri = uri.id)
          WHERE NOT cn.deleted
                AND cn.label = '##URI##'
                AND uri.active
                AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
                AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
                AND cn.owning_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
          LIMIT 1;

        IF FOUND THEN
            -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
            visible_count := visible_count + 1;

            current_res.id = core_result.id;
            current_res.rel = core_result.rel;

            tmp_int := 1;
            IF metarecord THEN
                SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
            END IF;

            IF tmp_int = 1 THEN
                current_res.record = core_result.records[1];
            ELSE
                current_res.record = NULL;
            END IF;

            RETURN NEXT current_res;

            CONTINUE;
        END IF;

        IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN

            PERFORM 1
              FROM  asset.call_number cn
                    JOIN asset.copy cp ON (cp.call_number = cn.id)
              WHERE NOT cn.deleted
                    AND NOT cp.deleted
                    AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) )
                    AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
                    AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
              LIMIT 1;

            IF NOT FOUND THEN
                -- RAISE NOTICE ' % were all status-excluded ... ', core_result.records;
                excluded_count := excluded_count + 1;
                CONTINUE;
            END IF;

        END IF;

        IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN

            PERFORM 1
              FROM  asset.call_number cn
                    JOIN asset.copy cp ON (cp.call_number = cn.id)
              WHERE NOT cn.deleted
                    AND NOT cp.deleted
                    AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) )
                    AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
                    AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
              LIMIT 1;

            IF NOT FOUND THEN
                -- RAISE NOTICE ' % were all copy_location-excluded ... ', core_result.records;
                excluded_count := excluded_count + 1;
                CONTINUE;
            END IF;

        END IF;

        IF staff IS NULL OR NOT staff THEN

            PERFORM 1
              FROM  asset.call_number cn
                    JOIN asset.copy cp ON (cp.call_number = cn.id)
                    JOIN actor.org_unit a ON (cp.circ_lib = a.id)
                    JOIN asset.copy_location cl ON (cp.location = cl.id)
                    JOIN config.copy_status cs ON (cp.status = cs.id)
              WHERE NOT cn.deleted
                    AND NOT cp.deleted
                    AND cs.opac_visible
                    AND cl.opac_visible
                    AND cp.opac_visible
                    AND a.opac_visible
                    AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
                    AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
              LIMIT 1;

            IF NOT FOUND THEN
                -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
                excluded_count := excluded_count + 1;
                CONTINUE;
            END IF;

        ELSE

            PERFORM 1
              FROM  asset.call_number cn
                    JOIN asset.copy cp ON (cp.call_number = cn.id)
                    JOIN actor.org_unit a ON (cp.circ_lib = a.id)
                    JOIN asset.copy_location cl ON (cp.location = cl.id)
              WHERE NOT cn.deleted
                    AND NOT cp.deleted
                    AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
                    AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
              LIMIT 1;

            IF NOT FOUND THEN

                PERFORM 1
                  FROM  asset.call_number cn
                  WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
                  LIMIT 1;

                IF FOUND THEN
                    -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
                    excluded_count := excluded_count + 1;
                    CONTINUE;
                END IF;

            END IF;

        END IF;

        visible_count := visible_count + 1;

        current_res.id = core_result.id;
        current_res.rel = core_result.rel;

        tmp_int := 1;
        IF metarecord THEN
            SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
        END IF;

        IF tmp_int = 1 THEN
            current_res.record = core_result.records[1];
        ELSE
            current_res.record = NULL;
        END IF;

        RETURN NEXT current_res;

        IF visible_count % 1000 = 0 THEN
            -- RAISE NOTICE ' % visible so far ... ', visible_count;
        END IF;

    END LOOP;

    current_res.id = NULL;
    current_res.rel = NULL;
    current_res.record = NULL;
    current_res.total = total_count;
    current_res.checked = check_count;
    current_res.deleted = deleted_count;
    current_res.visible = visible_count;
    current_res.excluded = excluded_count;

    CLOSE core_cursor;

    RETURN NEXT current_res;

END;

Schema serial


Table: serial.bib_summary

serial.bib_summary Structure
F-Key Name Type Description
id serial PRIMARY KEY
serial.subscription.id subscription integer UNIQUE NOT NULL
generated_coverage text NOT NULL
textual_holdings text

Index - Schema serial


Table: serial.binding_unit

serial.binding_unit Structure
F-Key Name Type Description
id serial PRIMARY KEY
serial.subscription.id subscription integer UNIQUE#1 NOT NULL
label text UNIQUE#1 NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema serial


Table: serial.index_summary

serial.index_summary Structure
F-Key Name Type Description
id serial PRIMARY KEY
serial.subscription.id subscription integer UNIQUE NOT NULL
generated_coverage text NOT NULL
textual_holdings text

Index - Schema serial


Table: serial.issuance

serial.issuance Structure
F-Key Name Type Description
id serial PRIMARY KEY
serial.subscription.id subscription integer NOT NULL
asset.copy.id target_copy bigint
asset.copy_location.id location bigint
serial.binding_unit.id binding_unit integer
label text

Index - Schema serial


Table: serial.record_entry

serial.record_entry Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
biblio.record_entry.id record bigint
actor.org_unit.id owning_lib integer NOT NULL DEFAULT 1
creator integer NOT NULL DEFAULT 1
editor integer NOT NULL DEFAULT 1
source integer
create_date timestamp with time zone NOT NULL DEFAULT now()
edit_date timestamp with time zone NOT NULL DEFAULT now()
active boolean NOT NULL DEFAULT true
deleted boolean NOT NULL DEFAULT false
marc text NOT NULL
last_xact_id text NOT NULL
serial_record_entry_creator_idx creator serial_record_entry_editor_idx editor serial_record_entry_owning_lib_idx owning_lib, deleted

Index - Schema serial


Table: serial.subscription

serial.subscription Structure
F-Key Name Type Description
id serial PRIMARY KEY
asset.call_number.id callnumber bigint
asset.uri.id uri integer
start_date date NOT NULL
end_date date

Tables referencing this one via Foreign Key Constraints:

Index - Schema serial


Table: serial.sup_summary

serial.sup_summary Structure
F-Key Name Type Description
id serial PRIMARY KEY
serial.subscription.id subscription integer UNIQUE NOT NULL
generated_coverage text NOT NULL
textual_holdings text

Index - Schema serial


Schema stats


View: stats.fleshed_call_number

stats.fleshed_call_number Structure
F-Key Name Type Description
id bigint
creator bigint
create_date timestamp with time zone
editor bigint
edit_date timestamp with time zone
record bigint
owning_lib integer
label text
deleted boolean
create_date_day date
edit_date_day date
create_date_hour timestamp with time zone
edit_date_hour timestamp with time zone
item_lang text
item_type text
item_form text
SELECT cn.id
, cn.creator
, cn.create_date
, cn.editor
, cn.edit_date
, cn.record
, cn.owning_lib
, cn.label
, cn.deleted
, (cn.create_date)::date AS create_date_day
, (cn.edit_date)::date AS edit_date_day
, date_trunc
('hour'::text
     , cn.create_date
) AS create_date_hour
, date_trunc
('hour'::text
     , cn.edit_date
) AS edit_date_hour
, rd.item_lang
, rd.item_type
, rd.item_form 
FROM (asset.call_number cn 
  JOIN metabib.rec_descriptor rd 
    ON (
           (rd.record = cn.record)
     )
);

Index - Schema stats


View: stats.fleshed_circulation

stats.fleshed_circulation Structure
F-Key Name Type Description
id bigint
usr integer
xact_start timestamp with time zone
xact_finish timestamp with time zone
unrecovered boolean
target_copy bigint
circ_lib integer
circ_staff integer
checkin_staff integer
checkin_lib integer
renewal_remaining integer
due_date timestamp with time zone
stop_fines_time timestamp with time zone
checkin_time timestamp with time zone
create_time timestamp with time zone
duration interval
fine_interval interval
recuring_fine numeric(6,2)
max_fine numeric(6,2)
phone_renewal boolean
desk_renewal boolean
opac_renewal boolean
duration_rule text
recuring_fine_rule text
max_fine_rule text
stop_fines text
start_date_day date
finish_date_day date
start_date_hour timestamp with time zone
finish_date_hour timestamp with time zone
call_number_label text
owning_lib integer
item_lang text
item_type text
item_form text
SELECT c.id
, c.usr
, c.xact_start
, c.xact_finish
, c.unrecovered
, c.target_copy
, c.circ_lib
, c.circ_staff
, c.checkin_staff
, c.checkin_lib
, c.renewal_remaining
, c.due_date
, c.stop_fines_time
, c.checkin_time
, c.create_time
, c.duration
, c.fine_interval
, c.recuring_fine
, c.max_fine
, c.phone_renewal
, c.desk_renewal
, c.opac_renewal
, c.duration_rule
, c.recuring_fine_rule
, c.max_fine_rule
, c.stop_fines
, (c.xact_start)::date AS start_date_day
, (c.xact_finish)::date AS finish_date_day
, date_trunc
('hour'::text
     , c.xact_start
) AS start_date_hour
, date_trunc
('hour'::text
     , c.xact_finish
) AS finish_date_hour
, cp.call_number_label
, cp.owning_lib
, cp.item_lang
, cp.item_type
, cp.item_form 
FROM (action.circulation c 
  JOIN stats.fleshed_copy cp 
    ON (
           (cp.id = c.target_copy)
     )
);

Index - Schema stats


View: stats.fleshed_copy

stats.fleshed_copy Structure
F-Key Name Type Description
id bigint
circ_lib integer
creator bigint
call_number bigint
editor bigint
create_date timestamp with time zone
edit_date timestamp with time zone
copy_number integer
status integer
location integer
loan_duration integer
fine_level integer
age_protect integer
circulate boolean
deposit boolean
ref boolean
holdable boolean
deposit_amount numeric(6,2)
price numeric(8,2)
barcode text
circ_modifier text
circ_as_type text
dummy_title text
dummy_author text
alert_message text
opac_visible boolean
deleted boolean
create_date_day date
edit_date_day date
create_date_hour timestamp with time zone
edit_date_hour timestamp with time zone
call_number_label text
owning_lib integer
item_lang text
item_type text
item_form text
SELECT cp.id
, cp.circ_lib
, cp.creator
, cp.call_number
, cp.editor
, cp.create_date
, cp.edit_date
, cp.copy_number
, cp.status
, cp.location
, cp.loan_duration
, cp.fine_level
, cp.age_protect
, cp.circulate
, cp.deposit
, cp.ref
, cp.holdable
, cp.deposit_amount
, cp.price
, cp.barcode
, cp.circ_modifier
, cp.circ_as_type
, cp.dummy_title
, cp.dummy_author
, cp.alert_message
, cp.opac_visible
, cp.deleted
, (cp.create_date)::date AS create_date_day
, (cp.edit_date)::date AS edit_date_day
, date_trunc
('hour'::text
     , cp.create_date
) AS create_date_hour
, date_trunc
('hour'::text
     , cp.edit_date
) AS edit_date_hour
, cn.label AS call_number_label
, cn.owning_lib
, rd.item_lang
, rd.item_type
, rd.item_form 
FROM (
     (asset.copy cp 
        JOIN asset.call_number cn 
          ON (
                 (cp.call_number = cn.id)
           )
     )
  JOIN metabib.rec_descriptor rd 
    ON (
           (rd.record = cn.record)
     )
);

Index - Schema stats


Schema vandelay


Table: vandelay.authority_attr_definition

vandelay.authority_attr_definition Structure
F-Key Name Type Description
id serial PRIMARY KEY
code text UNIQUE NOT NULL
description text
xpath text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema vandelay


Table: vandelay.authority_match

vandelay.authority_match Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
vandelay.queued_authority_record_attr.id matched_attr integer
vandelay.queued_authority_record.id queued_record bigint
authority.record_entry.id eg_record bigint

Index - Schema vandelay


Table: vandelay.authority_queue

vandelay.authority_queue Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('vandelay.queue_id_seq'::regclass)
owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
complete boolean NOT NULL DEFAULT false
queue_type text UNIQUE#1 NOT NULL DEFAULT 'authority'::text

Table vandelay.authority_queue Inherits queue,

 

vandelay.authority_queue Constraints
Name Constraint
authority_queue_queue_type_check CHECK ((queue_type = 'authority'::text))
queue_queue_type_check CHECK ((queue_type = ANY (ARRAY['bib'::text, 'authority'::text])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema vandelay


Table: vandelay.bib_attr_definition

vandelay.bib_attr_definition Structure
F-Key Name Type Description
id serial PRIMARY KEY
code text UNIQUE NOT NULL
description text
xpath text NOT NULL
remove text NOT NULL DEFAULT ''::text
ident boolean NOT NULL DEFAULT false

Tables referencing this one via Foreign Key Constraints:

Index - Schema vandelay


Table: vandelay.bib_match

vandelay.bib_match Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
field_type text NOT NULL
vandelay.queued_bib_record_attr.id matched_attr integer
vandelay.queued_bib_record.id queued_record bigint
biblio.record_entry.id eg_record bigint

 

vandelay.bib_match Constraints
Name Constraint
bib_match_field_type_check CHECK ((field_type = ANY (ARRAY['isbn'::text, 'tcn_value'::text, 'id'::text])))

Index - Schema vandelay


Table: vandelay.bib_queue

vandelay.bib_queue Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('vandelay.queue_id_seq'::regclass)
owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
complete boolean NOT NULL DEFAULT false
queue_type text UNIQUE#1 NOT NULL DEFAULT 'bib'::text
vandelay.import_item_attr_definition.id item_attr_def bigint

Table vandelay.bib_queue Inherits queue,

 

vandelay.bib_queue Constraints
Name Constraint
bib_queue_queue_type_check CHECK ((queue_type = 'bib'::text))
queue_queue_type_check CHECK ((queue_type = ANY (ARRAY['bib'::text, 'authority'::text])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema vandelay


Table: vandelay.import_bib_trash_fields

vandelay.import_bib_trash_fields Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
field text UNIQUE#1 NOT NULL

Index - Schema vandelay


Table: vandelay.import_item

vandelay.import_item Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
vandelay.queued_bib_record.id record bigint NOT NULL
vandelay.import_item_attr_definition.id definition bigint NOT NULL
owning_lib integer
circ_lib integer
call_number text
copy_number integer
status integer
location integer
circulate boolean
deposit boolean
deposit_amount numeric(8,2)
ref boolean
holdable boolean
price numeric(8,2)
barcode text
circ_modifier text
circ_as_type text
alert_message text
pub_note text
priv_note text
opac_visible boolean

Index - Schema vandelay


Table: vandelay.import_item_attr_definition

vandelay.import_item_attr_definition Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.org_unit.id owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
tag text NOT NULL
keep boolean NOT NULL DEFAULT false
owning_lib text
circ_lib text
call_number text
copy_number text
status text
location text
circulate text
deposit text
deposit_amount text
ref text
holdable text
price text
barcode text
circ_modifier text
circ_as_type text
alert_message text
opac_visible text
pub_note_title text
pub_note text
priv_note_title text
priv_note text

Tables referencing this one via Foreign Key Constraints:

Index - Schema vandelay


Table: vandelay.queue

vandelay.queue Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
actor.usr.id owner integer UNIQUE#1 NOT NULL
name text UNIQUE#1 NOT NULL
complete boolean NOT NULL DEFAULT false
queue_type text UNIQUE#1 NOT NULL DEFAULT 'bib'::text

 

vandelay.queue Constraints
Name Constraint
queue_queue_type_check CHECK ((queue_type = ANY (ARRAY['bib'::text, 'authority'::text])))

Index - Schema vandelay


Table: vandelay.queued_authority_record

vandelay.queued_authority_record Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('vandelay.queued_record_id_seq'::regclass)
create_time timestamp with time zone NOT NULL DEFAULT now()
import_time timestamp with time zone
purpose text NOT NULL DEFAULT 'import'::text
marc text NOT NULL
vandelay.authority_queue.id queue integer NOT NULL
authority.record_entry.id imported_as integer

Table vandelay.queued_authority_record Inherits queued_record,

 

vandelay.queued_authority_record Constraints
Name Constraint
queued_record_purpose_check CHECK ((purpose = ANY (ARRAY['import'::text, 'overlay'::text])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema vandelay


Table: vandelay.queued_authority_record_attr

vandelay.queued_authority_record_attr Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
vandelay.queued_authority_record.id record bigint NOT NULL
vandelay.authority_attr_definition.id field integer NOT NULL
attr_value text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema vandelay


Table: vandelay.queued_bib_record

vandelay.queued_bib_record Structure
F-Key Name Type Description
id bigint PRIMARY KEY DEFAULT nextval('vandelay.queued_record_id_seq'::regclass)
create_time timestamp with time zone NOT NULL DEFAULT now()
import_time timestamp with time zone
purpose text NOT NULL DEFAULT 'import'::text
marc text NOT NULL
vandelay.bib_queue.id queue integer NOT NULL
config.bib_source.id bib_source integer
biblio.record_entry.id imported_as integer

Table vandelay.queued_bib_record Inherits queued_record,

 

vandelay.queued_bib_record Constraints
Name Constraint
queued_record_purpose_check CHECK ((purpose = ANY (ARRAY['import'::text, 'overlay'::text])))

Tables referencing this one via Foreign Key Constraints:

Index - Schema vandelay


Table: vandelay.queued_bib_record_attr

vandelay.queued_bib_record_attr Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
vandelay.queued_bib_record.id record bigint NOT NULL
vandelay.bib_attr_definition.id field integer NOT NULL
attr_value text NOT NULL

Tables referencing this one via Foreign Key Constraints:

Index - Schema vandelay


Table: vandelay.queued_record

vandelay.queued_record Structure
F-Key Name Type Description
id bigserial PRIMARY KEY
create_time timestamp with time zone NOT NULL DEFAULT now()
import_time timestamp with time zone
purpose text NOT NULL DEFAULT 'import'::text
marc text NOT NULL

 

vandelay.queued_record Constraints
Name Constraint
queued_record_purpose_check CHECK ((purpose = ANY (ARRAY['import'::text, 'overlay'::text])))

Index - Schema vandelay


Function: vandelay.cleanup_authority_marc()

Returns: trigger

Language: PLPGSQL

BEGIN
    DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
    IF TG_OP = 'UPDATE' THEN
        RETURN NEW;
    END IF;
    RETURN OLD;
END;

Function: vandelay.cleanup_bib_marc()

Returns: trigger

Language: PLPGSQL

BEGIN
    DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
    DELETE FROM vandelay.import_item WHERE record = OLD.id;

    IF TG_OP = 'UPDATE' THEN
        RETURN NEW;
    END IF;
    RETURN OLD;
END;

Function: vandelay.ingest_authority_marc()

Returns: trigger

Language: PLPGSQL

DECLARE
    value   TEXT;
    atype   TEXT;
    adef    RECORD;
BEGIN
    FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP

        SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
        IF (value IS NOT NULL AND value <> '') THEN
            INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
        END IF;

    END LOOP;

    RETURN NULL;
END;

Function: vandelay.ingest_bib_items()

Returns: trigger

Language: PLPGSQL

DECLARE
    queue_rec   RECORD;
    item_rule   RECORD;
    item_data   vandelay.import_item%ROWTYPE;
BEGIN

    SELECT * INTO queue_rec FROM vandelay.bib_queue WHERE id = NEW.queue;

    FOR item_rule IN SELECT r.* FROM actor.org_unit_ancestors( queue_rec.owner ) o JOIN vandelay.import_item_attr_definition r ON ( r.owner = o.id ) LOOP
        FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, item_rule.id::BIGINT ) LOOP
            INSERT INTO vandelay.import_item (
		record,
                definition,
                owning_lib,
                circ_lib,
                call_number,
                copy_number,
                status,
                location,
                circulate,
                deposit,
                deposit_amount,
                ref,
                holdable,
                price,
                barcode,
                circ_modifier,
                circ_as_type,
                alert_message,
                pub_note,
                priv_note,
                opac_visible
            ) VALUES (
		NEW.id,
                item_data.definition,
                item_data.owning_lib,
                item_data.circ_lib,
                item_data.call_number,
                item_data.copy_number,
                item_data.status,
                item_data.location,
                item_data.circulate,
                item_data.deposit,
                item_data.deposit_amount,
                item_data.ref,
                item_data.holdable,
                item_data.price,
                item_data.barcode,
                item_data.circ_modifier,
                item_data.circ_as_type,
                item_data.alert_message,
                item_data.pub_note,
                item_data.priv_note,
                item_data.opac_visible
            );
        END LOOP;
    END LOOP;

    RETURN NULL;
END;

Function: vandelay.ingest_bib_marc()

Returns: trigger

Language: PLPGSQL

DECLARE
    value   TEXT;
    atype   TEXT;
    adef    RECORD;
BEGIN
    FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP

        SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
        IF (value IS NOT NULL AND value <> '') THEN
            INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
        END IF;

    END LOOP;

    RETURN NULL;
END;

Function: vandelay.ingest_items(attr_def_id bigint, import_id bigint)

Returns: SET OF import_item

Language: PLPGSQL

DECLARE

    owning_lib      TEXT;
    circ_lib        TEXT;
    call_number     TEXT;
    copy_number     TEXT;
    status          TEXT;
    location        TEXT;
    circulate       TEXT;
    deposit         TEXT;
    deposit_amount  TEXT;
    ref             TEXT;
    holdable        TEXT;
    price           TEXT;
    barcode         TEXT;
    circ_modifier   TEXT;
    circ_as_type    TEXT;
    alert_message   TEXT;
    opac_visible    TEXT;
    pub_note        TEXT;
    priv_note       TEXT;

    attr_def        RECORD;
    tmp_attr_set    RECORD;
    attr_set        vandelay.import_item%ROWTYPE;

    xpath           TEXT;

BEGIN

    SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;

    IF FOUND THEN

        attr_set.definition := attr_def.id; 
    
        -- Build the combined XPath
    
        owning_lib :=
            CASE
                WHEN attr_def.owning_lib IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
            END;
    
        circ_lib :=
            CASE
                WHEN attr_def.circ_lib IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
            END;
    
        call_number :=
            CASE
                WHEN attr_def.call_number IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
            END;
    
        copy_number :=
            CASE
                WHEN attr_def.copy_number IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
            END;
    
        status :=
            CASE
                WHEN attr_def.status IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
            END;
    
        location :=
            CASE
                WHEN attr_def.location IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
            END;
    
        circulate :=
            CASE
                WHEN attr_def.circulate IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
            END;
    
        deposit :=
            CASE
                WHEN attr_def.deposit IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
            END;
    
        deposit_amount :=
            CASE
                WHEN attr_def.deposit_amount IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
            END;
    
        ref :=
            CASE
                WHEN attr_def.ref IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
            END;
    
        holdable :=
            CASE
                WHEN attr_def.holdable IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
            END;
    
        price :=
            CASE
                WHEN attr_def.price IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
            END;
    
        barcode :=
            CASE
                WHEN attr_def.barcode IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
            END;
    
        circ_modifier :=
            CASE
                WHEN attr_def.circ_modifier IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
            END;
    
        circ_as_type :=
            CASE
                WHEN attr_def.circ_as_type IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
            END;
    
        alert_message :=
            CASE
                WHEN attr_def.alert_message IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
            END;
    
        opac_visible :=
            CASE
                WHEN attr_def.opac_visible IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
            END;

        pub_note :=
            CASE
                WHEN attr_def.pub_note IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
            END;
        priv_note :=
            CASE
                WHEN attr_def.priv_note IS NULL THEN 'null()'
                WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
                ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
            END;
    
    
        xpath := 
            owning_lib      || '|' || 
            circ_lib        || '|' || 
            call_number     || '|' || 
            copy_number     || '|' || 
            status          || '|' || 
            location        || '|' || 
            circulate       || '|' || 
            deposit         || '|' || 
            deposit_amount  || '|' || 
            ref             || '|' || 
            holdable        || '|' || 
            price           || '|' || 
            barcode         || '|' || 
            circ_modifier   || '|' || 
            circ_as_type    || '|' || 
            alert_message   || '|' || 
            pub_note        || '|' || 
            priv_note       || '|' || 
            opac_visible;

        -- RAISE NOTICE 'XPath: %', xpath;
        
        FOR tmp_attr_set IN
                SELECT  *
                  FROM  xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
                            AS t( id BIGINT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
                                  dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
                                  circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
        LOOP
    
            tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
            tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');

            tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
            tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
    
            SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
            SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
            SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
    
            SELECT  id INTO attr_set.location
              FROM  asset.copy_location
              WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
                    AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
    
            attr_set.circulate      :=
                LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
                OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL

            attr_set.deposit        :=
                LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
                OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL

            attr_set.holdable       :=
                LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
                OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL

            attr_set.opac_visible   :=
                LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
                OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL

            attr_set.ref            :=
                LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
                OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
    
            attr_set.copy_number    := tmp_attr_set.cnum::INT; -- INT,
            attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
            attr_set.price          := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
    
            attr_set.call_number    := tmp_attr_set.cn; -- TEXT
            attr_set.barcode        := tmp_attr_set.bc; -- TEXT,
            attr_set.circ_modifier  := tmp_attr_set.circ_mod; -- TEXT,
            attr_set.circ_as_type   := tmp_attr_set.circ_as; -- TEXT,
            attr_set.alert_message  := tmp_attr_set.amessage; -- TEXT,
            attr_set.pub_note       := tmp_attr_set.note; -- TEXT,
            attr_set.priv_note      := tmp_attr_set.pnote; -- TEXT,
            attr_set.alert_message  := tmp_attr_set.amessage; -- TEXT,
    
            RETURN NEXT attr_set;
    
        END LOOP;
    
    END IF;

END;

Function: vandelay.match_bib_record()

Returns: trigger

Language: PLPGSQL

DECLARE
    attr    RECORD;
    eg_rec  RECORD;
BEGIN
    FOR attr IN SELECT a.* FROM vandelay.queued_bib_record_attr a JOIN vandelay.bib_attr_definition d ON (d.id = a.field) WHERE record = NEW.id AND d.ident IS TRUE LOOP

		-- All numbers? check for an id match
		IF (attr.attr_value ~ $r$^\d+$$r$) THEN
	        FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
		        INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
			END LOOP;
		END IF;

		-- Looks like an ISBN? check for an isbn match
		IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
	        FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE LOWER('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP
				PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
				IF FOUND THEN
			        INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
				END IF;
			END LOOP;

			-- subcheck for isbn-as-tcn
		    FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
			    INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
	        END LOOP;
		END IF;

		-- check for an OCLC tcn_value match
		IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
		    FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
			    INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
	        END LOOP;
		END IF;

		-- check for a direct tcn_value match
        FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
            INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
        END LOOP;

		-- check for a direct item barcode match
        FOR eg_rec IN
                SELECT  DISTINCT b.*
                  FROM  biblio.record_entry b
                        JOIN asset.call_number cn ON (cn.record = b.id)
                        JOIN asset.copy cp ON (cp.call_number = cn.id)
                  WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
        LOOP
            INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
        END LOOP;

    END LOOP;

    RETURN NULL;
END;

Function: vandelay.strip_field(field text, xml text)

Returns: text

Language: PLPERLU


    use MARC::Record;
    use MARC::File::XML;

    my $xml = shift;
    my $field_spec = shift;

    my $r = MARC::Record->new_from_xml( $xml );
    $r->delete_field( $_ ) for ( $r->field( $field_spec ) );

    $xml = $r->as_xml_record;
    $xml =~ s/^<\?.+?\?>$//mo;
    $xml =~ s/\n//sgo;
    $xml =~ s/>\s+</></sgo;

    return $xml;

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict