Evergreen
HomePrev Chapter 26. Database Schema Next

Schema actor

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

Table: 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.

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
usr : integer -- NOT NULL, REFERENCES actor.usr.
barcode : text -- UNIQUE, NOT NULL,
active : boolean -- NOT NULL, DEFAULT true,

Indexes: 

actor_card_barcode_evergreen_lowercase_idx : lowercase(barcode)
actor_card_usr_idx : usr

Table: hours_of_operation

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

Columns: 

field name : datatype -- parameters, contraints and notes
id : integer -- PRIMARY KEY, REFERENCES actor.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?

Table: org_address

Columns: 

field name : datatype -- parameters, contraints and notes
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.org_unit.
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,
san : text --

Indexes: 

actor_org_address_org_unit_idx : org_unit

Tables referencing actor.org_unit via Foreign Key Constraints: 

actor.org_unit 

Table: org_lasso

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
name : text -- UNIQUE,

Tables referencing actor.org_lasso_map via Foreign Key Constraints: 

actor.org_lasso_map 

Table: org_lasso_map

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
lasso : integer -- NOT NULL, REFERENCES actor.org_lasso.
org_unit : integer -- NOT NULL, REFERENCES actor.org_unit.

Indexes: 

ou_lasso_org_unit_idx : org_unit

Table: org_unit

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
parent_ou : integer -- REFERENCES actor.org_unit.
ou_type : integer -- NOT NULL, REFERENCES actor.org_unit_type.
ill_address : integer -- REFERENCES actor.org_address.
holds_address : integer -- REFERENCES actor.org_address.
mailing_address : integer -- REFERENCES actor.org_address.
billing_address : integer -- REFERENCES actor.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.fiscal_calendar.

Indexes: 

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 acq.cancel_reason via Foreign Key Constraints: 

acq.cancel_reasonacq.claim_event_type
acq.claim_policyacq.claim_type
acq.distribution_formulaacq.distribution_formula_entry
acq.fundacq.fund_allocation_percent
acq.fund_tagacq.funding_source
acq.invoiceacq.lineitem_alert_text
acq.lineitem_detailacq.picklist
acq.provideracq.purchase_order
acq.user_requestaction.circulation
action.fieldsetaction.hold_request
action.in_house_useaction.non_cat_in_house_use
action.non_cataloged_circulationaction.survey
action.transit_copyaction_trigger.event_definition
actor.hours_of_operationactor.org_address
actor.org_lasso_mapactor.org_unit
actor.org_unit_closedactor.org_unit_setting
actor.stat_catactor.stat_cat_entry
actor.usractor.usr_org_unit_opt_in
actor.usr_standing_penaltyactor.workstation
asset.call_numberasset.copy
asset.copy_locationasset.copy_location_order
asset.copy_templateasset.stat_cat
asset.stat_cat_entrybiblio.record_entry
booking.reservationbooking.resource
booking.resource_attrbooking.resource_attr_value
booking.resource_typeconfig.billing_type
config.circ_matrix_matchpointconfig.hold_matrix_matchpoint
config.idl_field_docconfig.remote_account
money.collections_trackerpermission.grp_penalty_threshold
permission.usr_work_ou_mapreporter.output_folder
reporter.report_folderreporter.template_folder
serial.distributionserial.record_entry
serial.subscriptionvandelay.import_bib_trash_fields
vandelay.import_item_attr_definitionvandelay.merge_profile

Table: org_unit_closed

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
org_unit : integer -- NOT NULL, REFERENCES actor.org_unit.
close_start : timestamp with time zone -- NOT NULL,
close_end : timestamp with time zone -- NOT NULL,
reason : text --

Table: org_unit_proximity

Columns: 

field name : datatype -- parameters, contraints and notes
id : bigserial -- PRIMARY KEY,
from_org : integer --
to_org : integer --
prox : integer --

Indexes: 

from_prox_idx : from_org

Table: org_unit_setting

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

Columns: 

field name : datatype -- parameters, contraints and notes
id : bigserial -- PRIMARY KEY,
org_unit : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
name : text -- UNIQUE#1, NOT NULL, REFERENCES config.org_unit_setting_type.
value : text -- NOT NULL,

Indexes: 

actor_org_unit_setting_usr_idx : org_unit

Table: org_unit_type

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
name : text -- NOT NULL,
opac_label : text -- NOT NULL,
depth : integer -- NOT NULL,
parent : integer -- REFERENCES actor.org_unit_type.
can_have_vols : boolean -- NOT NULL, DEFAULT true,
can_have_users : boolean -- NOT NULL, DEFAULT true,

Indexes: 

actor_org_unit_type_parent_idx : parent

Tables referencing actor.org_unit via Foreign Key Constraints: 

actor.org_unitactor.org_unit_type
config.hold_matrix_matchpoint 

Table: stat_cat

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

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
owner : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
name : text -- UNIQUE#1, NOT NULL,
opac_visible : boolean -- NOT NULL, DEFAULT false,
usr_summary : boolean -- NOT NULL, DEFAULT false,

Tables referencing actor.stat_cat_entry via Foreign Key Constraints: 

actor.stat_cat_entryactor.stat_cat_entry_usr_map

Table: 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.

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
stat_cat : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.stat_cat.
owner : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
value : text -- UNIQUE#1, NOT NULL,

Table: stat_cat_entry_usr_map

Statistical Catagory Entry to User map Records the stat_cat entries for each user.

Columns: 

field name : datatype -- parameters, contraints and notes
id : bigserial -- PRIMARY KEY,
stat_cat_entry : text -- NOT NULL,
stat_cat : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.stat_cat.
target_usr : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.usr.

Indexes: 

actor_stat_cat_entry_usr_idx : target_usr

Table: 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.

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
card : integer -- UNIQUE,
profile : integer -- NOT NULL, REFERENCES permission.grp_tree.
usrname : text -- UNIQUE, NOT NULL,
email : text --
passwd : text -- NOT NULL,
standing : integer -- NOT NULL, DEFAULT 1, REFERENCES config.standing.
ident_type : integer -- NOT NULL, REFERENCES config.identification_type.
ident_value : text --
ident_type2 : integer -- REFERENCES config.identification_type.
ident_value2 : text --
net_access_level : integer -- NOT NULL, DEFAULT 1, REFERENCES config.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.usr_address.
billing_address : integer -- REFERENCES actor.usr_address.
home_ou : integer -- NOT NULL, REFERENCES actor.org_unit.
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),
claims_never_checked_out_count : integer -- NOT NULL,

Indexes: 

actor_usr_billing_address_idx : billing_address
actor_usr_day_phone_idx : lowercase(day_phone)
actor_usr_email_idx : lowercase(email)
actor_usr_evening_phone_idx : lowercase(evening_phone)
actor_usr_family_name_idx : lowercase(family_name)
actor_usr_first_given_name_idx : lowercase(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_second_given_name_idx : lowercase(second_given_name)
actor_usr_usrgroup_idx : usrgroup

Tables referencing acq.claim_event via Foreign Key Constraints: 

acq.claim_eventacq.distribution_formula_application
acq.fund_allocationacq.fund_allocation_percent
acq.fund_transferacq.lineitem
acq.lineitem_noteacq.lineitem_usr_attr_definition
acq.picklistacq.po_note
acq.provider_noteacq.purchase_order
acq.serial_claim_eventacq.user_request
action.circulationaction.fieldset
action.hold_notificationaction.hold_request
action.in_house_useaction.non_cat_in_house_use
action.non_cataloged_circulationactor.card
actor.stat_cat_entry_usr_mapactor.usr_address
actor.usr_noteactor.usr_org_unit_opt_in
actor.usr_password_resetactor.usr_saved_search
actor.usr_settingactor.usr_standing_penalty
asset.call_numberasset.call_number_note
asset.copyasset.copy_note
asset.copy_templatebiblio.record_entry
biblio.record_notebooking.reservation
container.biblio_record_entry_bucketcontainer.call_number_bucket
container.copy_bucketcontainer.user_bucket
container.user_bucket_itemmoney.billable_xact
money.collections_trackerpermission.usr_grp_map
permission.usr_object_perm_mappermission.usr_perm_map
permission.usr_work_ou_mapreporter.output_folder
reporter.reportreporter.report_folder
reporter.schedulereporter.template
reporter.template_folderserial.distribution_note
serial.issuanceserial.item
serial.item_noteserial.routing_list_user
serial.subscription_noteserial.unit
vandelay.queue 

Table: usr_address

Columns: 

field name : datatype -- parameters, contraints and notes
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.usr.
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,
replaces : integer -- REFERENCES actor.usr_address.

Indexes: 

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 actor.usr via Foreign Key Constraints: 

actor.usractor.usr_address

Table: usr_note

Columns: 

field name : datatype -- parameters, contraints and notes
id : bigserial -- PRIMARY KEY,
usr : bigint -- NOT NULL, REFERENCES actor.usr.
creator : bigint -- NOT NULL, REFERENCES actor.usr.
create_date : timestamp with time zone -- DEFAULT now(),
pub : boolean -- NOT NULL, DEFAULT false,
title : text -- NOT NULL,
value : text -- NOT NULL,

Indexes: 

actor_usr_note_creator_idx : creator
actor_usr_note_usr_idx : usr

Table: usr_org_unit_opt_in

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
org_unit : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.org_unit.
usr : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.usr.
staff : integer -- NOT NULL, REFERENCES actor.usr.
opt_in_ts : timestamp with time zone -- NOT NULL, DEFAULT now(),
opt_in_ws : integer -- NOT NULL, REFERENCES actor.workstation.

Indexes: 

usr_org_unit_opt_in_staff_idx : staff

Table: usr_password_reset

Self-serve password reset requests

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
uuid : text -- NOT NULL,
usr : bigint -- NOT NULL, REFERENCES actor.usr.
request_time : timestamp with time zone -- NOT NULL, DEFAULT now(),
has_been_reset : boolean -- NOT NULL, DEFAULT false,

Indexes: 

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

Table: usr_saved_search

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
owner : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.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: 

valid_query_text : CHECK ((query_type = 'URL'::text))
valid_target : CHECK ((target = ANY (ARRAY['record'::text, 'metarecord'::text, 'callnumber'::text])))

Table: usr_setting

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

Columns: 

field name : datatype -- parameters, contraints and notes
id : bigserial -- PRIMARY KEY,
usr : integer -- UNIQUE#1, NOT NULL, REFERENCES actor.usr.
name : text -- UNIQUE#1, NOT NULL, REFERENCES config.usr_setting_type.
value : text -- NOT NULL,

Indexes: 

actor_usr_setting_usr_idx : usr

Table: usr_standing_penalty

User standing penalties

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
org_unit : integer -- NOT NULL, REFERENCES actor.org_unit.
usr : integer -- NOT NULL, REFERENCES actor.usr.
standing_penalty : integer -- NOT NULL, REFERENCES config.standing_penalty.
staff : integer -- REFERENCES actor.usr.
set_date : timestamp with time zone -- DEFAULT now(),
stop_date : timestamp with time zone --
note : text --

Indexes: 

actor_usr_standing_penalty_staff_idx : staff
actor_usr_standing_penalty_usr_idx : usr

Table: workstation

Columns: 

field name : datatype -- parameters, contraints and notes
id : serial -- PRIMARY KEY,
name : text -- UNIQUE, NOT NULL,
owning_lib : integer -- NOT NULL, REFERENCES actor.org_unit.

Tables referencing action.circulation via Foreign Key Constraints: 

action.circulationactor.usr_org_unit_opt_in
money.bnm_desk_payment 

approve_pending_address(pending_id integer)

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.

Function Properties
Language: PLPGSQL
Return Type: bigint

calculate_system_penalties(context_org integer, match_user integer)

Function Properties
Language: PLPGSQL
Return Type: SET OF usr_standing_penalty

crypt_pw_insert()

Function Properties
Language: PLPGSQL
Return Type: trigger

crypt_pw_update()

Function Properties
Language: PLPGSQL
Return Type: trigger

org_unit_ancestor_at_depth(integer, integer)

Function Properties
Language: SQL
Return Type: org_unit

org_unit_ancestor_setting(org_id text, setting_name integer)

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

Function Properties
Language: PLPGSQL
Return Type: SET OF org_unit_setting

org_unit_ancestors(integer)

Function Properties
Language: SQL
Return Type: SET OF org_unit

org_unit_ancestors_distance(distance integer)

Function Properties
Language: SQL
Return Type: SET OF record

org_unit_combined_ancestors(integer, integer)

Function Properties
Language: SQL
Return Type: SET OF org_unit

org_unit_common_ancestors(integer, integer)

Function Properties
Language: SQL
Return Type: SET OF org_unit

org_unit_descendants(integer)

Function Properties
Language: SQL
Return Type: SET OF org_unit

org_unit_descendants(integer, integer)

Function Properties
Language: SQL
Return Type: SET OF org_unit

org_unit_descendants_distance(distance integer)

Function Properties
Language: SQL
Return Type: SET OF record

org_unit_full_path(integer)

Function Properties
Language: SQL
Return Type: SET OF org_unit

org_unit_full_path(integer, integer)

Function Properties
Language: SQL
Return Type: SET OF org_unit

org_unit_proximity(integer, integer)

Function Properties
Language: SQL
Return Type: integer

usr_delete(dest_usr integer, src_usr integer)

Logically deletes a user. Removes personally identifiable information, and purges associated data in other tables.

Function Properties
Language: PLPGSQL
Return Type: void

usr_merge(deactivate_cards integer, del_cards integer, del_addrs boolean, dest_usr boolean, src_usr boolean)

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

Function Properties
Language: PLPGSQL
Return Type: void

usr_merge_rows(dest_usr text, src_usr text, col_name integer, table_name integer)

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

Function Properties
Language: PLPGSQL
Return Type: void

usr_purge_data(specified_dest_usr integer, src_usr integer)

Finds rows dependent on a given row in actor.usr and either deletes them or reassigns them to a different user.

Function Properties
Language: PLPGSQL
Return Type: void

Prev Up Next
Schema action_trigger Home Schema asset