Chapter 2. Schema action

Table of Contents

Tables in action
Functions in action

Below are the tables, views and functions for action

Tables in action

aged_circulation

  • 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
  • recurring_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
  • recurring_fine_rule text NOT NULL
  • max_fine_rule text NOT NULL
  • stop_fines text
  • workstation integer
  • parent_circ bigint
  • checkin_workstation integer
  • checkin_scan_time timestamp with time zone
  • grace_period interval NOT NULL

Indexes on aged_circulation

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

all_circulation

  • 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
  • grace_period interval
  • 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
  • recurring_fine numeric(6,2)
  • max_fine numeric(6,2)
  • phone_renewal boolean
  • desk_renewal boolean
  • opac_renewal boolean
  • duration_rule text
  • recurring_fine_rule text
  • max_fine_rule text
  • stop_fines text
  • workstation integer
  • checkin_workstation integer
  • checkin_scan_time timestamp with time zone
  • parent_circ bigint

archive_actor_stat_cat

  • id bigserial PRIMARY KEY
  • xact bigint NOT NULL
  • stat_cat integer NOT NULL
  • value text NOT NULL

archive_asset_stat_cat

  • id bigserial PRIMARY KEY
  • xact bigint NOT NULL
  • stat_cat integer NOT NULL
  • value text NOT NULL

billable_circulations

  • 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
  • recurring_fine numeric(6,2)
  • max_fine numeric(6,2)
  • phone_renewal boolean
  • desk_renewal boolean
  • opac_renewal boolean
  • duration_rule text
  • recurring_fine_rule text
  • max_fine_rule text
  • stop_fines text
  • workstation integer
  • parent_circ bigint
  • checkin_workstation integer
  • checkin_scan_time timestamp with time zone
  • grace_period interval

billable_cirulations

  • 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

circulation

  • id bigint PRIMARY KEY DEFAULT nextval('money.billable_xact_id_seq'::regclass)
  • usr integer NOT NULL REFERENCES actor.table.usr
  • xact_start timestamp with time zone NOT NULL DEFAULT now()
  • xact_finish timestamp with time zone
  • unrecovered boolean
  • target_copy bigint NOT NULL
  • circ_lib integer NOT NULL REFERENCES actor.table.org-unit
  • 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
  • recurring_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
  • recurring_fine_rule text NOT NULL
  • max_fine_rule text NOT NULL
  • stop_fines text
  • workstation integer REFERENCES actor.table.workstation
  • parent_circ bigint REFERENCES action.table.circulation
  • checkin_workstation integer REFERENCES actor.table.workstation
  • checkin_scan_time timestamp with time zone
  • grace_period interval NOT NULL
  • copy_location integer NOT NULL DEFAULT 1 REFERENCES asset.table.copy-location

Constraints on circulation

  • circulation_stop_fines_check CHECK stop_fines = ANY (ARRAY['CHECKIN'::text

Indexes on circulation

  • action_circulation_target_copy_idx target_copy
  • circ_all_usr_idx usr
  • circ_checkin_staff_idx checkin_staff
  • circ_checkin_time checkin_time) WHERE (checkin_time IS NOT NULL
  • circ_circ_lib_idx circ_lib
  • circ_circ_staff_idx circ_staff
  • 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

Tables referencing via foreign key constraints

circulation_limit_group_map

  • circ bigint PRIMARY KEY REFERENCES action.table.circulation
  • limit_group integer PRIMARY KEY REFERENCES config.table.circ-limit-group

fieldset

  • id serial PRIMARY KEY
  • owner integer NOT NULL REFERENCES actor.table.usr
  • owning_lib integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
  • status text NOT NULL
  • creation_time timestamp with time zone NOT NULL DEFAULT now()
  • scheduled_time timestamp with time zone
  • applied_time timestamp with time zone
  • classname text NOT NULL
  • name text UNIQUE #1 NOT NULL
  • stored_query integer REFERENCES query.table.stored-query
  • pkey_value text

Constraints on fieldset

  • fieldset_one_or_the_other CHECK ((((stored_query IS NOT NULL) AND (pkey_value IS NULL)) OR pkey_value IS NOT NULL) AND (stored_query IS NULL))
  • valid_status CHECK status = ANY (ARRAY['PENDING'::text

Indexes on fieldset

  • action_fieldset_sched_time_idx scheduled_time
  • action_owner_idx owner

Tables referencing via foreign key constraints

fieldset_col_val

  • id serial PRIMARY KEY
  • fieldset integer UNIQUE #1 NOT NULL REFERENCES action.table.fieldset
  • col text UNIQUE #1 NOT NULL
  • val text

hold_copy_map

  • id bigserial PRIMARY KEY
  • hold integer UNIQUE #1 NOT NULL REFERENCES action.table.hold-request
  • target_copy bigint UNIQUE #1 NOT NULL
  • proximity numeric

Indexes on hold_copy_map

  • acm_copy_idx target_copy

hold_notification

  • id serial PRIMARY KEY
  • hold integer NOT NULL REFERENCES action.table.hold-request
  • notify_staff integer REFERENCES actor.table.usr
  • notify_time timestamp with time zone NOT NULL DEFAULT now()
  • method text NOT NULL
  • note text

Indexes on hold_notification

  • ahn_hold_idx hold
  • ahn_notify_staff_idx notify_staff

hold_request

  • 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
  • target bigint NOT NULL
  • current_copy bigint
  • fulfillment_staff integer REFERENCES actor.table.usr
  • fulfillment_lib integer REFERENCES actor.table.org-unit
  • request_lib integer NOT NULL REFERENCES actor.table.org-unit
  • requestor integer NOT NULL REFERENCES actor.table.usr
  • usr integer NOT NULL REFERENCES actor.table.usr
  • selection_ou integer NOT NULL
  • selection_depth integer NOT NULL
  • pickup_lib integer NOT NULL REFERENCES actor.table.org-unit
  • hold_type text NOT NULL
  • holdable_formats text
  • phone_notify text
  • email_notify boolean NOT NULL DEFAULT false
  • frozen boolean NOT NULL DEFAULT false
  • thaw_date timestamp with time zone
  • cancel_cause integer
  • cancel_note text
  • shelf_time timestamp with time zone
  • cut_in_line boolean
  • mint_condition boolean NOT NULL DEFAULT true
  • shelf_expire_time timestamp with time zone
  • current_shelf_lib integer REFERENCES actor.table.org-unit
  • sms_notify text
  • sms_carrier integer REFERENCES config.table.sms-carrier

Constraints on hold_request

  • sms_check CHECK

Indexes on hold_request

  • hold_request_current_copy_before_cap_idx current_copy) WHERE ((capture_time IS NULL) AND (cancel_time IS NULL)
  • hold_request_current_copy_idx current_copy
  • hold_request_fulfillment_staff_idx fulfillment_staff
  • hold_request_open_idx id) WHERE ((cancel_time IS NULL) AND (fulfillment_time IS NULL)
  • hold_request_pickup_lib_idx pickup_lib
  • hold_request_prev_check_time_idx prev_check_time
  • hold_request_requestor_idx requestor
  • hold_request_target_idx target
  • hold_request_usr_idx usr

Tables referencing via foreign key constraints

hold_request_cancel_cause

  • id serial PRIMARY KEY
  • label text UNIQUE

hold_request_note

  • id bigserial PRIMARY KEY
  • hold bigint NOT NULL REFERENCES action.table.hold-request
  • title text NOT NULL
  • body text NOT NULL
  • slip boolean NOT NULL DEFAULT false
  • pub boolean NOT NULL DEFAULT false
  • staff boolean NOT NULL DEFAULT false

Indexes on hold_request_note

  • ahrn_hold_idx hold

hold_transit_copy

  • id integer PRIMARY KEY DEFAULT nextval('transit_copy_id_seq'::regclass)
  • source_send_time timestamp with time zone
  • dest_recv_time timestamp with time zone
  • 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
  • prev_dest integer
  • hold integer REFERENCES action.table.hold-request

Indexes on hold_transit_copy

  • active_hold_transit_cp_idx target_copy
  • active_hold_transit_dest_idx dest
  • active_hold_transit_source_idx source
  • hold_transit_copy_hold_idx hold

in_house_use

  • id serial PRIMARY KEY
  • item bigint NOT NULL
  • staff integer NOT NULL REFERENCES actor.table.usr
  • org_unit integer NOT NULL REFERENCES actor.table.org-unit
  • use_time timestamp with time zone NOT NULL DEFAULT now()

Indexes on in_house_use

  • action_in_house_use_staff_idx staff

non_cat_in_house_use

  • id serial PRIMARY KEY
  • item_type bigint NOT NULL REFERENCES config.table.non-cataloged-type
  • staff integer NOT NULL REFERENCES actor.table.usr
  • org_unit integer NOT NULL REFERENCES actor.table.org-unit
  • use_time timestamp with time zone NOT NULL DEFAULT now()

Indexes on non_cat_in_house_use

  • non_cat_in_house_use_staff_idx staff

non_cataloged_circulation

  • id serial PRIMARY KEY
  • patron integer NOT NULL REFERENCES actor.table.usr
  • staff integer NOT NULL REFERENCES actor.table.usr
  • circ_lib integer NOT NULL REFERENCES actor.table.org-unit
  • item_type integer NOT NULL REFERENCES config.table.non-cataloged-type
  • circ_time timestamp with time zone NOT NULL DEFAULT now()

Indexes on non_cataloged_circulation

  • action_non_cat_circ_patron_idx patron
  • action_non_cat_circ_staff_idx staff

open_circulation

  • 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
  • recurring_fine numeric(6,2)
  • max_fine numeric(6,2)
  • phone_renewal boolean
  • desk_renewal boolean
  • opac_renewal boolean
  • duration_rule text
  • recurring_fine_rule text
  • max_fine_rule text
  • stop_fines text
  • workstation integer
  • parent_circ bigint
  • checkin_workstation integer
  • checkin_scan_time timestamp with time zone
  • grace_period interval

reservation_transit_copy

  • id integer PRIMARY KEY DEFAULT nextval('transit_copy_id_seq'::regclass)
  • source_send_time timestamp with time zone
  • dest_recv_time timestamp with time zone
  • target_copy bigint NOT NULL REFERENCES booking.table.resource
  • source integer NOT NULL
  • dest integer NOT NULL
  • prev_hop integer
  • copy_status integer NOT NULL
  • persistant_transfer boolean NOT NULL DEFAULT false
  • prev_dest integer
  • reservation integer REFERENCES booking.table.reservation

Indexes on reservation_transit_copy

  • active_reservation_transit_cp_idx target_copy
  • active_reservation_transit_dest_idx dest
  • active_reservation_transit_source_idx source

survey

  • id serial PRIMARY KEY
  • owner integer NOT NULL REFERENCES actor.table.org-unit
  • 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 via foreign key constraints

survey_answer

  • id serial PRIMARY KEY
  • question integer NOT NULL REFERENCES action.table.survey-question
  • answer text NOT NULL

Tables referencing via foreign key constraints

survey_question

  • id serial PRIMARY KEY
  • survey integer NOT NULL REFERENCES action.table.survey
  • question text NOT NULL

Tables referencing via foreign key constraints

survey_response

  • id bigserial PRIMARY KEY
  • response_group_id integer
  • usr integer
  • survey integer NOT NULL REFERENCES action.table.survey
  • question integer NOT NULL REFERENCES action.table.survey-question
  • answer integer NOT NULL REFERENCES action.table.survey-answer
  • answer_date timestamp with time zone
  • effective_date timestamp with time zone NOT NULL DEFAULT now()

Indexes on survey_response

  • action_survey_response_usr_idx usr

transit_copy

  • id serial PRIMARY KEY
  • source_send_time timestamp with time zone
  • dest_recv_time timestamp with time zone
  • target_copy bigint NOT NULL
  • source integer NOT NULL REFERENCES actor.table.org-unit
  • dest integer NOT NULL REFERENCES actor.table.org-unit
  • prev_hop integer REFERENCES action.table.transit-copy
  • copy_status integer NOT NULL REFERENCES config.table.copy-status
  • persistant_transfer boolean NOT NULL DEFAULT false
  • prev_dest integer REFERENCES actor.table.org-unit

Indexes on transit_copy

  • active_transit_cp_idx target_copy
  • active_transit_dest_idx dest
  • active_transit_source_idx source

Tables referencing via foreign key constraints

unfulfilled_hold_innermost_loop

  • hold integer
  • circ_lib integer
  • count bigint

unfulfilled_hold_list

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

Indexes on unfulfilled_hold_list

  • uhr_hold_idx hold

unfulfilled_hold_loops

  • hold integer
  • circ_lib integer
  • count bigint

unfulfilled_hold_max_loop

  • hold integer
  • max bigint

unfulfilled_hold_min_loop

  • hold integer
  • min bigint