Chapter 5. Schema asset

Table of Contents

Tables in asset
call_number
call_number_class
call_number_note
call_number_prefix
call_number_suffix
copy
copy_location
copy_location_group
copy_location_group_map
copy_location_order
copy_note
copy_part_map
copy_template
opac_visible_copies
stat_cat
stat_cat_entry
stat_cat_entry_copy_map
stat_cat_entry_transparency_map
stat_cat_sip_fields
uri
uri_call_number_map
Functions in asset

Below are the tables, views and functions for asset

Tables in asset

call_number

  • id bigserial PRIMARY KEY
  • creator bigint NOT NULL REFERENCES actor.table.usr
  • create_date timestamp with time zone DEFAULT now()
  • editor bigint NOT NULL REFERENCES actor.table.usr
  • edit_date timestamp with time zone DEFAULT now()
  • record bigint NOT NULL REFERENCES biblio.table.record-entry
  • owning_lib integer NOT NULL REFERENCES actor.table.org-unit
  • label text NOT NULL
  • deleted boolean NOT NULL DEFAULT false
  • prefix integer NOT NULL DEFAULT (-1) REFERENCES asset.table.call-number-prefix
  • suffix integer NOT NULL DEFAULT (-1) REFERENCES asset.table.call-number-suffix
  • label_class bigint NOT NULL REFERENCES asset.table.call-number-class
  • label_sortkey text

Indexes on call_number

  • asset_call_number_creator_idx creator
  • asset_call_number_dewey_idx call_number_dewey(label)
  • asset_call_number_editor_idx editor
  • asset_call_number_label_sortkey oils_text_as_bytea(label_sortkey)
  • asset_call_number_label_sortkey_browse oils_text_as_bytea(label_sortkey), oils_text_as_bytea(label), id, owning_lib) WHERE ((deleted IS FALSE) OR (deleted = false)
  • asset_call_number_record_idx record
  • asset_call_number_upper_label_id_owning_lib_idx oils_text_as_bytea(label), id, owning_lib

Tables referencing via foreign key constraints

  • asset.call_number_note
  • asset.copy
  • asset.uri_call_number_map
  • container.call_number_bucket_item
  • serial.distribution
  • serial.unit

call_number_class

Defines the call number normalization database functions in the "normalizer" column and the tag/subfield combinations to use to lookup the call number in the "field" column for a given classification scheme. Tag/subfield combinations are delimited by commas.

  • id bigserial PRIMARY KEY
  • name text NOT NULL
  • normalizer text NOT NULL DEFAULT 'asset.normalize_generic'::text
  • field text NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'::text

Tables referencing via foreign key constraints

  • asset.call_number

call_number_note

  • id bigserial PRIMARY KEY
  • call_number bigint NOT NULL REFERENCES asset.table.call-number
  • 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 call_number_note

  • asset_call_number_note_creator_idx creator

call_number_prefix

  • id serial PRIMARY KEY
  • owning_lib integer NOT NULL REFERENCES actor.table.org-unit
  • label text NOT NULL
  • label_sortkey text

Indexes on call_number_prefix

  • asset_call_number_prefix_sortkey_idx label_sortkey

Tables referencing via foreign key constraints

  • asset.call_number

call_number_suffix

  • id serial PRIMARY KEY
  • owning_lib integer NOT NULL REFERENCES actor.table.org-unit
  • label text NOT NULL
  • label_sortkey text

Indexes on call_number_suffix

  • asset_call_number_suffix_sortkey_idx label_sortkey

Tables referencing via foreign key constraints

  • asset.call_number

copy

  • id bigserial PRIMARY KEY
  • circ_lib integer NOT NULL REFERENCES actor.table.org-unit
  • creator bigint NOT NULL REFERENCES actor.table.usr
  • call_number bigint NOT NULL REFERENCES asset.table.call-number
  • editor bigint NOT NULL REFERENCES actor.table.usr
  • create_date timestamp with time zone DEFAULT now()
  • edit_date timestamp with time zone DEFAULT now()
  • copy_number integer
  • status integer NOT NULL REFERENCES config.table.copy-status
  • location integer NOT NULL DEFAULT 1 REFERENCES asset.table.copy-location
  • 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
  • circ_modifier text REFERENCES config.table.circ-modifier
  • 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
  • floating integer REFERENCES config.table.floating-group
  • dummy_isbn text
  • status_changed_time timestamp with time zone
  • active_date timestamp with time zone
  • mint_condition boolean NOT NULL DEFAULT true
  • cost numeric(8,2)

Constraints on copy

  • copy_fine_level_check CHECK fine_level = ANY (ARRAY[1
  • copy_loan_duration_check CHECK loan_duration = ANY (ARRAY[1

Indexes on copy

  • cp_avail_cn_idx call_number
  • cp_available_by_circ_lib_idx circ_lib) WHERE (status = ANY (ARRAY[0, 7])
  • cp_cn_idx call_number
  • cp_create_date create_date
  • cp_creator_idx creator
  • cp_editor_idx editor

Tables referencing via foreign key constraints

  • action.usr_circ_history
  • asset.copy_note
  • container.copy_bucket_item
  • vandelay.import_item

copy_location

  • id serial PRIMARY KEY
  • name text NOT NULL
  • owning_lib integer NOT NULL REFERENCES actor.table.org-unit
  • 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
  • label_prefix text
  • label_suffix text
  • checkin_alert boolean NOT NULL DEFAULT false
  • deleted boolean NOT NULL DEFAULT false

Tables referencing via foreign key constraints

  • acq.distribution_formula_entry
  • acq.lineitem_detail
  • action.circulation
  • actor.org_unit_proximity_adjustment
  • asset.copy
  • asset.copy_location_group_map
  • asset.copy_location_order
  • asset.copy_template
  • config.circ_limit_set_copy_loc_map
  • config.circ_matrix_matchpoint

copy_location_group

  • id serial PRIMARY KEY
  • name text UNIQUE #1 NOT NULL
  • owner integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
  • pos integer NOT NULL
  • top boolean NOT NULL DEFAULT false
  • opac_visible boolean NOT NULL DEFAULT true

Tables referencing via foreign key constraints

  • asset.copy_location_group_map

copy_location_group_map

  • id serial PRIMARY KEY
  • location integer UNIQUE #1 NOT NULL REFERENCES asset.table.copy-location
  • lgroup integer UNIQUE #1 NOT NULL REFERENCES asset.table.copy-location-group

copy_location_order

  • id serial PRIMARY KEY
  • location integer UNIQUE #1 NOT NULL REFERENCES asset.table.copy-location
  • org integer UNIQUE #1 NOT NULL REFERENCES actor.table.org-unit
  • position integer NOT NULL

copy_note

  • id bigserial PRIMARY KEY
  • owning_copy bigint NOT NULL REFERENCES asset.table.copy
  • 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 copy_note

  • asset_copy_note_creator_idx creator
  • asset_copy_note_owning_copy_idx owning_copy

copy_part_map

  • id serial PRIMARY KEY
  • target_copy bigint NOT NULL
  • part integer NOT NULL REFERENCES biblio.table.monograph-part

copy_template

  • id serial PRIMARY KEY
  • owning_lib integer NOT NULL REFERENCES actor.table.org-unit
  • creator bigint NOT NULL REFERENCES actor.table.usr
  • editor bigint NOT NULL REFERENCES actor.table.usr
  • create_date timestamp with time zone DEFAULT now()
  • edit_date timestamp with time zone DEFAULT now()
  • name text NOT NULL
  • circ_lib integer REFERENCES actor.table.org-unit
  • status integer REFERENCES config.table.copy-status
  • location integer REFERENCES asset.table.copy-location
  • 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)
  • circ_modifier text
  • circ_as_type text
  • alert_message text
  • opac_visible boolean
  • floating integer REFERENCES config.table.floating-group
  • mint_condition boolean

Constraints on copy_template

  • valid_fine_level CHECK
  • valid_loan_duration CHECK

Tables referencing via foreign key constraints

  • serial.distribution

opac_visible_copies

Materialized view of copies that are visible in the OPAC, used by search.query_parser_fts() to speed up OPAC visibility checks on large databases. Contents are maintained by a set of triggers.

  • id bigserial PRIMARY KEY
  • copy_id bigint
  • record bigint
  • circ_lib integer

Indexes on opac_visible_copies

  • opac_visible_copies_copy_id_idx copy_id
  • opac_visible_copies_idx1 record, circ_lib

stat_cat

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

Tables referencing via foreign key constraints

  • asset.stat_cat_entry
  • asset.stat_cat_entry_copy_map

stat_cat_entry

  • id serial PRIMARY KEY
  • stat_cat integer UNIQUE #1 NOT NULL REFERENCES asset.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

  • asset.stat_cat_entry_copy_map

stat_cat_entry_copy_map

  • id bigserial PRIMARY KEY
  • stat_cat integer UNIQUE #1 NOT NULL REFERENCES asset.table.stat-cat
  • stat_cat_entry integer NOT NULL REFERENCES asset.table.stat-cat-entry
  • owning_copy bigint UNIQUE #1 NOT NULL

Indexes on stat_cat_entry_copy_map

  • scecm_owning_copy_idx owning_copy

stat_cat_entry_transparency_map

  • id bigserial PRIMARY KEY
  • stat_cat integer UNIQUE #1 NOT NULL
  • stat_cat_entry integer NOT NULL
  • owning_transparency integer UNIQUE #1 NOT NULL

stat_cat_sip_fields

Asset 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

  • asset.stat_cat

uri

  • id serial PRIMARY KEY
  • href text NOT NULL
  • label text
  • use_restriction text
  • active boolean NOT NULL DEFAULT true

Tables referencing via foreign key constraints

  • asset.uri_call_number_map
  • serial.item

uri_call_number_map

  • id bigserial PRIMARY KEY
  • uri integer UNIQUE #1 NOT NULL REFERENCES asset.table.uri
  • call_number integer UNIQUE #1 NOT NULL REFERENCES asset.table.call-number

Indexes on uri_call_number_map

  • asset_uri_call_number_map_cn_idx call_number