Chapter 4. Schema actor


Home Prev		Next

Holds all tables pertaining to users and libraries (org units).

Below are the tables, views and functions for actor

Tables in actor

address_alert

id serial PRIMARY KEY
owner integer NOT NULL REFERENCES actor.table.org-unit
active boolean NOT NULL DEFAULT true
match_all boolean NOT NULL DEFAULT true
alert_message text NOT NULL
street1 text
street2 text
city text
county text
state text
country text
post_code text
mailing_address boolean NOT NULL DEFAULT false
billing_address boolean NOT NULL DEFAULT false

card

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.

id serial PRIMARY KEY
usr integer NOT NULL REFERENCES actor.table.usr
barcode text UNIQUE NOT NULL
active boolean NOT NULL DEFAULT true

Indexes on card

actor_card_barcode_evergreen_lowercase_idx lowercase(barcode)
actor_card_usr_idx usr

hours_of_operation

When does this org_unit usually open and close? (Variations are expressed in the actor.org_unit_closed table.)

id integer PRIMARY KEY REFERENCES actor.table.org-unit
dow_0_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone When does this org_unit open on Monday?
dow_0_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone When does this org_unit close on Monday?
dow_1_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone When does this org_unit open on Tuesday?
dow_1_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone When does this org_unit close on Tuesday?
dow_2_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone When does this org_unit open on Wednesday?
dow_2_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone When does this org_unit close on Wednesday?
dow_3_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone When does this org_unit open on Thursday?
dow_3_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone When does this org_unit close on Thursday?
dow_4_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone When does this org_unit open on Friday?
dow_4_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone When does this org_unit close on Friday?
dow_5_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone When does this org_unit open on Saturday?
dow_5_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone When does this org_unit close on Saturday?
dow_6_open time without time zone NOT NULL DEFAULT '09:00:00'::time without time zone When does this org_unit open on Sunday?
dow_6_close time without time zone NOT NULL DEFAULT '17:00:00'::time without time zone When does this org_unit close on Sunday?

org_address

id serial PRIMARY KEY
valid boolean NOT NULL DEFAULT true
address_type text NOT NULL DEFAULT 'MAILING'::text
org_unit integer NOT NULL REFERENCES actor.table.org-unit
street1 text NOT NULL
street2 text
city text NOT NULL
county text
state text
country text NOT NULL
post_code text NOT NULL
san text

Indexes on org_address

actor_org_address_org_unit_idx org_unit

Tables referencing via foreign key constraints

actor.org_unit

org_lasso

id serial PRIMARY KEY
name text UNIQUE

Tables referencing via foreign key constraints

actor.org_lasso_map

org_lasso_map

id serial PRIMARY KEY
lasso integer NOT NULL REFERENCES actor.table.org-lasso
org_unit integer NOT NULL REFERENCES actor.table.org-unit

Indexes on org_lasso_map

ou_lasso_org_unit_idx org_unit

org_unit

id serial PRIMARY KEY
parent_ou integer REFERENCES actor.table.org-unit
ou_type integer NOT NULL REFERENCES actor.table.org-unit-type
ill_address integer REFERENCES actor.table.org-address
holds_address integer REFERENCES actor.table.org-address
mailing_address integer REFERENCES actor.table.org-address
billing_address integer REFERENCES actor.table.org-address
shortname text UNIQUE NOT NULL
name text UNIQUE NOT NULL
email text
phone text
opac_visible boolean NOT NULL DEFAULT true
fiscal_calendar integer NOT NULL DEFAULT 1 REFERENCES acq.table.fiscal-calendar

Indexes on org_unit

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

Tables referencing via foreign key constraints

acq.cancel_reason
acq.claim_event_type
acq.claim_policy
acq.claim_type
acq.distribution_formula
acq.distribution_formula_entry
acq.fund
acq.fund_allocation_percent
acq.fund_tag
acq.funding_source
acq.invoice
acq.lineitem_alert_text
acq.lineitem_detail
acq.picklist
acq.provider
acq.purchase_order
acq.user_request
action.circulation
action.fieldset
action.fieldset_group
action.hold_request
action.in_house_use
action.non_cat_in_house_use
action.non_cataloged_circulation
action.survey
action.transit_copy
action_trigger.event_definition
actor.address_alert
actor.hours_of_operation
actor.org_address
actor.org_lasso_map
actor.org_unit
actor.org_unit_closed
actor.org_unit_custom_tree_node
actor.org_unit_proximity_adjustment
actor.org_unit_setting
actor.search_filter_group
actor.stat_cat
actor.stat_cat_entry
actor.stat_cat_entry_default
actor.toolbar
actor.usr
actor.usr_message
actor.usr_org_unit_opt_in
actor.usr_standing_penalty
actor.workstation
asset.call_number
asset.call_number_prefix
asset.call_number_suffix
asset.copy
asset.copy_location
asset.copy_location_group
asset.copy_location_order
asset.copy_tag
asset.copy_template
asset.stat_cat
asset.stat_cat_entry
biblio.record_entry
booking.reservation
booking.resource
booking.resource_attr
booking.resource_attr_value
booking.resource_type
config.barcode_completion
config.billing_type
config.circ_limit_set
config.circ_matrix_matchpoint
config.copy_tag_type
config.filter_dialog_filter_set
config.floating_group_member
config.hold_matrix_matchpoint
config.idl_field_doc
config.marc_field
config.marc_subfield
config.org_unit_setting_type_log
config.remote_account
config.weight_assoc
config.z3950_source_credentials
container.biblio_record_entry_bucket
container.call_number_bucket
container.copy_bucket
container.user_bucket
money.collections_tracker
permission.grp_penalty_threshold
permission.usr_work_ou_map
rating.badge
reporter.output_folder
reporter.report_folder
reporter.template_folder
serial.distribution
serial.pattern_template
serial.record_entry
serial.subscription
url_verify.session
vandelay.import_bib_trash_group
vandelay.import_item_attr_definition
vandelay.match_set
vandelay.merge_profile

org_unit_closed

id serial PRIMARY KEY
org_unit integer NOT NULL REFERENCES actor.table.org-unit
close_start timestamp with time zone NOT NULL
close_end timestamp with time zone NOT NULL
full_day boolean NOT NULL DEFAULT false
multi_day boolean NOT NULL DEFAULT false
reason text

org_unit_custom_tree

id serial PRIMARY KEY
active boolean DEFAULT false
purpose actor.org_unit_custom_tree_purpose UNIQUE NOT NULL DEFAULT 'opac'::actor.org_unit_custom_tree_purpose

Tables referencing via foreign key constraints

actor.org_unit_custom_tree_node

org_unit_custom_tree_node

id serial PRIMARY KEY
tree integer UNIQUE #1 REFERENCES actor.table.org-unit-custom-tree
org_unit integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
parent_node integer REFERENCES actor.table.org-unit-custom-tree-node
sibling_order integer NOT NULL

Tables referencing via foreign key constraints

actor.org_unit_custom_tree_node

org_unit_proximity

id bigserial PRIMARY KEY
from_org integer
to_org integer
prox integer

Indexes on org_unit_proximity

from_prox_idx from_org

org_unit_proximity_adjustment

id serial PRIMARY KEY
item_circ_lib integer REFERENCES actor.table.org-unit
item_owning_lib integer REFERENCES actor.table.org-unit
copy_location integer REFERENCES asset.table.copy-location
hold_pickup_lib integer REFERENCES actor.table.org-unit
hold_request_lib integer REFERENCES actor.table.org-unit
pos integer NOT NULL
absolute_adjustment boolean NOT NULL DEFAULT false
prox_adjustment numeric
circ_mod text REFERENCES config.table.circ-modifier

Constraints on org_unit_proximity_adjustment

prox_adj_criterium CHECK COALESCE((item_circ_lib)::text

Indexes on org_unit_proximity_adjustment

prox_adj_circ_lib_idx item_circ_lib
prox_adj_circ_mod_idx circ_mod
prox_adj_copy_location_idx copy_location
prox_adj_owning_lib_idx item_owning_lib
prox_adj_pickup_lib_idx hold_pickup_lib
prox_adj_request_lib_idx hold_request_lib

org_unit_setting

Org Unit settings

This table contains any arbitrary settings that a client program would like to save for an org unit.

id bigserial PRIMARY KEY
org_unit integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
name text UNIQUE #1 NOT NULL REFERENCES config.table.org-unit-setting-type
value text NOT NULL

Constraints on org_unit_setting

aous_must_be_json CHECK (is_json(value))

Indexes on org_unit_setting

actor_org_unit_setting_usr_idx org_unit

org_unit_type

id serial PRIMARY KEY
name text NOT NULL
opac_label text NOT NULL
depth integer NOT NULL
parent integer REFERENCES actor.table.org-unit-type
can_have_vols boolean NOT NULL DEFAULT true
can_have_users boolean NOT NULL DEFAULT true

Indexes on org_unit_type

actor_org_unit_type_parent_idx parent

Tables referencing via foreign key constraints

actor.org_unit
actor.org_unit_type
config.hold_matrix_matchpoint

passwd

id serial PRIMARY KEY
usr integer UNIQUE #1 NOT NULL REFERENCES actor.table.usr
salt text
passwd text NOT NULL
passwd_type text UNIQUE #1 NOT NULL REFERENCES actor.table.passwd-type
create_date timestamp with time zone NOT NULL DEFAULT now()
edit_date timestamp with time zone NOT NULL DEFAULT now()

passwd_type

code text PRIMARY KEY
name text UNIQUE NOT NULL
login boolean NOT NULL DEFAULT false
regex text
crypt_algo text
iter_count integer

Constraints on passwd_type

passwd_type_iter_count_check CHECK

Tables referencing via foreign key constraints

actor.passwd

search_filter_group

id serial PRIMARY KEY
owner integer UNIQUE #2 UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
code text UNIQUE #2 NOT NULL
label text UNIQUE #1 NOT NULL
create_date timestamp with time zone NOT NULL DEFAULT now()

Tables referencing via foreign key constraints

actor.search_filter_group_entry

search_filter_group_entry

id serial PRIMARY KEY
grp integer UNIQUE #1 NOT NULL REFERENCES actor.table.search-filter-group
pos integer NOT NULL
query integer UNIQUE #1 NOT NULL REFERENCES actor.table.search-query

search_query

id serial PRIMARY KEY
label text NOT NULL
query_text text NOT NULL

Tables referencing via foreign key constraints

actor.search_filter_group_entry

stat_cat

User Statistical Catagories

Local data collected about Users is placed into a Statistical Catagory. Here's where those catagories are defined.

id serial PRIMARY KEY
owner integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
name text UNIQUE #1 NOT NULL
opac_visible boolean NOT NULL DEFAULT false
usr_summary boolean NOT NULL DEFAULT false
sip_field character(2) REFERENCES actor.table.stat-cat-sip-fields
sip_format text
checkout_archive boolean NOT NULL DEFAULT false
required boolean NOT NULL DEFAULT false
allow_freetext boolean NOT NULL DEFAULT true

Tables referencing via foreign key constraints

actor.stat_cat_entry
actor.stat_cat_entry_default
actor.stat_cat_entry_usr_map

stat_cat_entry

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.

id serial PRIMARY KEY
stat_cat integer UNIQUE #1 NOT NULL REFERENCES actor.table.stat-cat
owner integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
value text UNIQUE #1 NOT NULL

Tables referencing via foreign key constraints

actor.stat_cat_entry_default

stat_cat_entry_default

User Statistical Category Default Entry

A library may choose one of the stat_cat entries to be the default entry.

id serial PRIMARY KEY
stat_cat_entry integer NOT NULL REFERENCES actor.table.stat-cat-entry
stat_cat integer UNIQUE #1 NOT NULL REFERENCES actor.table.stat-cat
owner integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit

stat_cat_entry_usr_map

Statistical Catagory Entry to User map

Records the stat_cat entries for each user.

id bigserial PRIMARY KEY
stat_cat_entry text NOT NULL
stat_cat integer UNIQUE #1 NOT NULL REFERENCES actor.table.stat-cat
target_usr integer UNIQUE #1 NOT NULL REFERENCES actor.table.usr

Indexes on stat_cat_entry_usr_map

actor_stat_cat_entry_usr_idx target_usr

stat_cat_sip_fields

Actor Statistical Category SIP Fields

Contains the list of valid SIP Field identifiers for Statistical Categories.

field character(2) PRIMARY KEY
name text NOT NULL
one_only boolean NOT NULL DEFAULT false

Tables referencing via foreign key constraints

actor.stat_cat

toolbar

id bigserial PRIMARY KEY
ws integer REFERENCES actor.table.workstation
org integer REFERENCES actor.table.org-unit
usr integer REFERENCES actor.table.usr
label text NOT NULL
layout text NOT NULL

Constraints on toolbar

layout_must_be_json CHECK (is_json(layout))
only_one_type CHECK ((((ws IS NOT NULL) AND (COALESCE(org, usr) IS NULL)) OR org IS NOT NULL) AND (COALESCE(ws OR usr IS NOT NULL) AND (COALESCE(org

usr

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.

id serial PRIMARY KEY
card integer UNIQUE
profile integer NOT NULL REFERENCES permission.table.grp-tree
usrname text UNIQUE NOT NULL
email text
passwd text NOT NULL
standing integer NOT NULL DEFAULT 1 REFERENCES config.table.standing
ident_type integer NOT NULL REFERENCES config.table.identification-type
ident_value text
ident_type2 integer REFERENCES config.table.identification-type
ident_value2 text
net_access_level integer NOT NULL DEFAULT 1 REFERENCES config.table.net-access-level
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 REFERENCES actor.table.usr-address
billing_address integer REFERENCES actor.table.usr-address
home_ou integer NOT NULL REFERENCES actor.table.org-unit
dob date
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)
claims_never_checked_out_count integer NOT NULL
last_update_time timestamp with time zone

Indexes on usr

actor_usr_billing_address_idx billing_address
actor_usr_day_phone_idx lowercase(day_phone)
actor_usr_day_phone_idx_numeric lowercase(regexp_replace(day_phone, '[^0-9]'::text, ''::text, 'g'::text))
actor_usr_email_idx lowercase(email)
actor_usr_evening_phone_idx lowercase(evening_phone)
actor_usr_evening_phone_idx_numeric lowercase(regexp_replace(evening_phone, '[^0-9]'::text, ''::text, 'g'::text))
actor_usr_family_name_idx lowercase(family_name)
actor_usr_family_name_unaccent_idx unaccent_and_squash(family_name)
actor_usr_first_given_name_idx lowercase(first_given_name)
actor_usr_first_given_name_unaccent_idx unaccent_and_squash(first_given_name)
actor_usr_home_ou_idx home_ou
actor_usr_ident_value2_idx lowercase(ident_value2)
actor_usr_ident_value_idx lowercase(ident_value)
actor_usr_mailing_address_idx mailing_address
actor_usr_other_phone_idx lowercase(other_phone)
actor_usr_other_phone_idx_numeric lowercase(regexp_replace(other_phone, '[^0-9]'::text, ''::text, 'g'::text))
actor_usr_second_given_name_idx lowercase(second_given_name)
actor_usr_second_given_name_unaccent_idx unaccent_and_squash(second_given_name)
actor_usr_usrgroup_idx usrgroup
actor_usr_usrname_idx lowercase(usrname)
actor_usr_usrname_unaccent_idx unaccent_and_squash(usrname)

Tables referencing via foreign key constraints

acq.claim_event
acq.distribution_formula_application
acq.fund_allocation
acq.fund_allocation_percent
acq.fund_transfer
acq.lineitem
acq.lineitem_detail
acq.lineitem_note
acq.lineitem_usr_attr_definition
acq.picklist
acq.po_note
acq.provider_note
acq.purchase_order
acq.serial_claim_event
acq.user_request
action.circulation
action.fieldset
action.fieldset_group
action.hold_notification
action.hold_request
action.in_house_use
action.non_cat_in_house_use
action.non_cataloged_circulation
action.usr_circ_history
actor.card
actor.passwd
actor.stat_cat_entry_usr_map
actor.toolbar
actor.usr_activity
actor.usr_address
actor.usr_message
actor.usr_note
actor.usr_org_unit_opt_in
actor.usr_password_reset
actor.usr_saved_search
actor.usr_setting
actor.usr_standing_penalty
asset.call_number
asset.call_number_note
asset.copy
asset.copy_note
asset.copy_template
biblio.record_entry
biblio.record_note
booking.reservation
config.filter_dialog_filter_set
container.biblio_record_entry_bucket
container.call_number_bucket
container.copy_bucket
container.user_bucket
container.user_bucket_item
money.billable_xact
money.collections_tracker
permission.usr_grp_map
permission.usr_object_perm_map
permission.usr_perm_map
permission.usr_work_ou_map
reporter.output_folder
reporter.report
reporter.report_folder
reporter.schedule
reporter.template
reporter.template_folder
serial.distribution_note
serial.issuance
serial.item
serial.item_note
serial.routing_list_user
serial.subscription_note
serial.unit
staging.user_stage
url_verify.session
url_verify.verification_attempt
vandelay.queue

usr_activity

id bigserial PRIMARY KEY
usr integer REFERENCES actor.table.usr
etype integer NOT NULL REFERENCES config.table.usr-activity-type
event_time timestamp with time zone NOT NULL DEFAULT now()

Indexes on usr_activity

usr_activity_usr_idx usr

usr_address

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
usr integer NOT NULL REFERENCES actor.table.usr
street1 text NOT NULL
street2 text
city text NOT NULL
county text
state text
country text NOT NULL
post_code text NOT NULL
pending boolean NOT NULL DEFAULT false
replaces integer REFERENCES actor.table.usr-address

Indexes on usr_address

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

Tables referencing via foreign key constraints

actor.usr
actor.usr_address

usr_message

id serial PRIMARY KEY
usr integer NOT NULL REFERENCES actor.table.usr
title text
message text NOT NULL
create_date timestamp with time zone NOT NULL DEFAULT now()
deleted boolean NOT NULL DEFAULT false
read_date timestamp with time zone
sending_lib integer NOT NULL REFERENCES actor.table.org-unit

Indexes on usr_message

aum_usr usr

usr_message_limited

id integer
usr integer
title text
message text
create_date timestamp with time zone
deleted boolean
read_date timestamp with time zone
sending_lib integer

usr_note

id bigserial PRIMARY KEY
usr bigint NOT NULL REFERENCES actor.table.usr
creator bigint NOT NULL REFERENCES actor.table.usr
create_date timestamp with time zone DEFAULT now()
pub boolean NOT NULL DEFAULT false
title text NOT NULL
value text NOT NULL

Indexes on usr_note

actor_usr_note_creator_idx creator
actor_usr_note_usr_idx usr

usr_org_unit_opt_in

id serial PRIMARY KEY
org_unit integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
usr integer UNIQUE #1 NOT NULL REFERENCES actor.table.usr
staff integer NOT NULL REFERENCES actor.table.usr
opt_in_ts timestamp with time zone NOT NULL DEFAULT now()
opt_in_ws integer NOT NULL REFERENCES actor.table.workstation

Indexes on usr_org_unit_opt_in

usr_org_unit_opt_in_staff_idx staff

usr_password_reset

Self-serve password reset requests

id serial PRIMARY KEY
uuid text NOT NULL
usr bigint NOT NULL REFERENCES actor.table.usr
request_time timestamp with time zone NOT NULL DEFAULT now()
has_been_reset boolean NOT NULL DEFAULT false

Indexes on usr_password_reset

actor_usr_password_reset_has_been_reset_idx has_been_reset
actor_usr_password_reset_request_time_idx request_time
actor_usr_password_reset_usr_idx usr

usr_saved_search

id serial PRIMARY KEY
owner integer UNIQUE #1 NOT NULL REFERENCES actor.table.usr
name text UNIQUE #1 NOT NULL
create_date timestamp with time zone NOT NULL DEFAULT now()
query_text text NOT NULL
query_type text NOT NULL DEFAULT 'URL'::text
target text NOT NULL

Constraints on usr_saved_search

valid_query_text CHECK query_type = 'URL'::text
valid_target CHECK target = ANY (ARRAY['record'::text

usr_setting

User settings

This table contains any arbitrary settings that a client program would like to save for a user.

id bigserial PRIMARY KEY
usr integer UNIQUE #1 NOT NULL REFERENCES actor.table.usr
name text UNIQUE #1 NOT NULL REFERENCES config.table.usr-setting-type
value text NOT NULL

Indexes on usr_setting

actor_usr_setting_usr_idx usr

usr_standing_penalty

User standing penalties

id serial PRIMARY KEY
org_unit integer NOT NULL REFERENCES actor.table.org-unit
usr integer NOT NULL REFERENCES actor.table.usr
standing_penalty integer NOT NULL REFERENCES config.table.standing-penalty
staff integer REFERENCES actor.table.usr
set_date timestamp with time zone DEFAULT now()
stop_date timestamp with time zone
note text

Indexes on usr_standing_penalty

actor_usr_standing_penalty_staff_idx staff
actor_usr_standing_penalty_usr_idx usr

workstation

id serial PRIMARY KEY
name text UNIQUE NOT NULL
owning_lib integer NOT NULL REFERENCES actor.table.org-unit

Tables referencing via foreign key constraints

action.circulation
actor.toolbar
actor.usr_org_unit_opt_in
money.bnm_desk_payment