Chapter 5. Schema asset

Table of Contents

Tables in asset
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

call_number_class

  • 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

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

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

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 boolean NOT NULL DEFAULT false
  • 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_cn_idx call_number
  • cp_create_date create_date
  • cp_creator_idx creator
  • cp_editor_idx editor

Tables referencing via foreign key constraints

copy_location

  • id serial PRIMARY KEY
  • name text UNIQUE #1 NOT NULL
  • owning_lib integer UNIQUE #1 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

Tables referencing via foreign key constraints

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

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

Constraints on copy_template

  • valid_fine_level CHECK
  • valid_loan_duration CHECK

Tables referencing via foreign key constraints

opac_visible_copies

  • 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

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

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

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

Tables referencing via foreign key constraints

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

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