Evergreen
HomePrev   Next

Chapter 4. Schema actor

Table of Contents

Tables in actor
Functions in actor

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

  • 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_barcode_lower_idx lower(barcode)
  • actor_card_usr_idx usr

hours_of_operation

  • 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
  • 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

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 NOT NULL
  • 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

org_lasso

  • id serial PRIMARY KEY
  • name text UNIQUE

Tables referencing via foreign key constraints

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 NOT NULL
  • name text 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

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
  • 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

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

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

  • 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

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

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

stat_cat

  • 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

stat_cat_entry

  • 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

stat_cat_entry_default

  • 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

  • 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

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

Tables referencing via foreign key constraints

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

  • 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
  • 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 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
  • 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)
  • alias text
  • juvenile boolean NOT NULL DEFAULT false
  • 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_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_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_usrgroup_idx usrgroup

Tables referencing via foreign key constraints

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 NOT NULL
  • country text NOT NULL
  • post_code text NOT NULL
  • pending boolean NOT NULL DEFAULT false
  • replaces integer

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

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

  • 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

  • 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

  • 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

Prev   Next
Chapter 3. Schema action_trigger Home Functions in actor