serial.unit

↑ serial schema

Soft Deletes: This table uses a deleted boolean flag rather than physical row deletion. Ad-hoc queries must include WHERE deleted = false to exclude logically-deleted rows. The application layer enforces this automatically; direct SQL does not.

Data-Modifying Triggers: This table has BEFORE ROW trigger(s) that modify row data before write. Values you INSERT or UPDATE may differ from what is actually stored. See the Triggers section below.

Deferrable Constraints: The following FK constraints are deferrable — they are checked at transaction end, not statement end: serial_unit_call_number_fkey, serial_unit_creator_fkey, serial_unit_editor_fkey.

Trigger Side Effects: Writing to this table automatically triggers writes to other tables:

Columns

Column Type Nullable Default Notes

id PK

bigint

No

nextval('asset.copy_id_seq'::regclass)

circ_lib

integer

No

creator FK

bigint

No

→ actor.usr(id)

call_number FK

bigint

No

→ asset.call_number(id)

editor FK

bigint

No

→ actor.usr(id)

create_date

timestamp with time zone

Yes

now()

edit_date

timestamp with time zone

Yes

now()

copy_number

integer

Yes

status

integer

No

0

location

integer

No

1

loan_duration

integer

No

fine_level

integer

No

age_protect

integer

Yes

circulate

boolean

No

true

deposit

boolean

No

false

ref

boolean

No

false

holdable

boolean

No

true

deposit_amount

numeric(6,2)

No

0.00

price

numeric(8,2)

Yes

barcode

text

No

circ_modifier

text

Yes

circ_as_type

text

Yes

dummy_title

text

Yes

dummy_author

text

Yes

alert_message

text

Yes

opac_visible

boolean

No

true

deleted SOFT-DEL

boolean

No

false

floating

integer

Yes

dummy_isbn

text

Yes

status_changed_time

timestamp with time zone

Yes

active_date

timestamp with time zone

Yes

mint_condition

boolean

No

true

cost

numeric(8,2)

Yes

sort_key

text

Yes

detailed_contents

text

No

summary_contents

text

No

Primary Key

(id)

Foreign Keys

Column(s) References On Delete On Update Deferrable Constraint

call_number

asset.call_number(id)

NO ACTION

NO ACTION

DEFERRED

serial_unit_call_number_fkey

creator

actor.usr(id)

SET NULL

NO ACTION

DEFERRED

serial_unit_creator_fkey

editor

actor.usr(id)

SET NULL

NO ACTION

DEFERRED

serial_unit_editor_fkey

Check Constraints

  • copy_fine_level_check: CHECK fine_level = ANY (ARRAY[1, 2, 3])

  • copy_loan_duration_check: CHECK loan_duration = ANY (ARRAY[1, 2, 3])

Indexes

Index Method Definition

unit_pkey PK

btree

CREATE UNIQUE INDEX unit_pkey ON serial.unit USING btree (id)

unit_barcode_key UNIQUE

btree

CREATE UNIQUE INDEX unit_barcode_key ON serial.unit USING btree (barcode) WHERE deleted = false) OR (deleted IS FALSE

unit_avail_cn_idx

btree

CREATE INDEX unit_avail_cn_idx ON serial.unit USING btree (call_number)

unit_cn_idx

btree

CREATE INDEX unit_cn_idx ON serial.unit USING btree (call_number)

unit_creator_idx

btree

CREATE INDEX unit_creator_idx ON serial.unit USING btree (creator)

unit_editor_idx

btree

CREATE INDEX unit_editor_idx ON serial.unit USING btree (editor)

unit_extant_by_circ_lib_idx

btree

CREATE INDEX unit_extant_by_circ_lib_idx ON serial.unit USING btree (circ_lib) WHERE deleted = false) OR (deleted IS FALSE

Triggers

Trigger Timing Event Level Function

audit_serial_unit_update_trigger

AFTER

DELETE OR UPDATE

ROW

auditor.audit_serial_unit_func()

autogenerate_placeholder_barcode

BEFORE

INSERT OR UPDATE

ROW

asset.autogenerate_placeholder_barcode()

sunit_created_trig

BEFORE

INSERT

ROW

asset.acp_created()

sunit_status_changed_trig

BEFORE

UPDATE

ROW

asset.acp_status_changed()

z_opac_vis_mat_view_del_tgr

BEFORE

DELETE

ROW

asset.cache_copy_visibility()

z_opac_vis_mat_view_tgr

AFTER

INSERT OR UPDATE

ROW

asset.cache_copy_visibility()

Trigger Bodies

audit_serial_unit_update_trigger

Function: auditor.audit_serial_unit_func()
Timing: AFTER DELETE OR UPDATE ROW

        BEGIN
            INSERT INTO auditor.serial_unit_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, id, circ_lib, creator, call_number, editor, create_date, edit_date, copy_number, status, location, loan_duration, fine_level, age_protect, circulate, deposit, ref, holdable, deposit_amount, price, barcode, circ_modifier, circ_as_type, dummy_title, dummy_author, alert_message, opac_visible, deleted, floating, dummy_isbn, status_changed_time, active_date, mint_condition, cost, sort_key, detailed_contents, summary_contents )
                SELECT  nextval('auditor.serial_unit_pkey_seq'),
                    now(),
                    SUBSTR(TG_OP,1,1),
                    eg_user,
                    eg_ws,
                    OLD.id, OLD.circ_lib, OLD.creator, OLD.call_number, OLD.editor, OLD.create_date, OLD.edit_date, OLD.copy_number, OLD.status, OLD.location, OLD.loan_duration, OLD.fine_level, OLD.age_protect, OLD.circulate, OLD.deposit, OLD.ref, OLD.holdable, OLD.deposit_amount, OLD.price, OLD.barcode, OLD.circ_modifier, OLD.circ_as_type, OLD.dummy_title, OLD.dummy_author, OLD.alert_message, OLD.opac_visible, OLD.deleted, OLD.floating, OLD.dummy_isbn, OLD.status_changed_time, OLD.active_date, OLD.mint_condition, OLD.cost, OLD.sort_key, OLD.detailed_contents, OLD.summary_contents
                FROM auditor.get_audit_info();
            RETURN NULL;
        END;

autogenerate_placeholder_barcode

Function: asset.autogenerate_placeholder_barcode()
Timing: BEFORE INSERT OR UPDATE ROW

This trigger modifies the row before it is written (returns a modified NEW).

 
BEGIN
	IF NEW.barcode LIKE '@@%' THEN
		NEW.barcode := '@@' || NEW.id;
	END IF;
	RETURN NEW;
END;

sunit_created_trig

Function: asset.acp_created()
Timing: BEFORE INSERT ROW

This trigger modifies the row before it is written (returns a modified NEW).

 
BEGIN
    IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
        NEW.active_date := now();
    END IF;
    IF NEW.status_changed_time IS NULL THEN
        NEW.status_changed_time := now();
    END IF;
    RETURN NEW;
END;

sunit_status_changed_trig

Function: asset.acp_status_changed()
Timing: BEFORE UPDATE ROW

This trigger modifies the row before it is written (returns a modified NEW).

 
BEGIN
	IF NEW.status <> OLD.status AND NOT (NEW.status = 0 AND OLD.status = 7) THEN
        NEW.status_changed_time := now();
        IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
            NEW.active_date := now();
        END IF;
    END IF;
    RETURN NEW;
END;

z_opac_vis_mat_view_del_tgr

Function: asset.cache_copy_visibility()
Timing: BEFORE DELETE ROW

This trigger modifies the row before it is written (returns a modified NEW).

 
DECLARE
    ocn     asset.call_number%ROWTYPE;
    ncn     asset.call_number%ROWTYPE;
    cid     BIGINT;
    dobib   BOOL;
BEGIN

    SELECT enabled = FALSE INTO dobib FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc';

    IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
        IF TG_OP = 'INSERT' THEN
            INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
                NEW.peer_record,
                NEW.target_copy,
                asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
            );

            RETURN NEW;
        ELSIF TG_OP = 'DELETE' THEN
            DELETE FROM asset.copy_vis_attr_cache
              WHERE record = OLD.peer_record AND target_copy = OLD.target_copy;

            RETURN OLD;
        END IF;
    END IF;

    IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
        IF TG_TABLE_NAME IN ('copy', 'unit') THEN
            SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
            INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
                ncn.record,
                NEW.id,
                asset.calculate_copy_visibility_attribute_set(NEW.id)
            );
        ELSIF TG_TABLE_NAME = 'record_entry' THEN
            NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE);
        ELSIF TG_TABLE_NAME = 'call_number' AND NEW.label = '##URI##' AND dobib THEN -- New located URI
            UPDATE  biblio.record_entry
              SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
              WHERE id = NEW.record;

        END IF;

        RETURN NEW;
    END IF;

    -- handle items first, since with circulation activity
    -- their statuses change frequently
    IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above

        IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
            DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
            RETURN OLD;
        END IF;

        SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;

        IF OLD.deleted <> NEW.deleted THEN
            IF NEW.deleted THEN
                DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
            ELSE
                INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
                    ncn.record,
                    NEW.id,
                    asset.calculate_copy_visibility_attribute_set(NEW.id)
                );
            END IF;

            RETURN NEW;
        ELSIF OLD.location   <> NEW.location OR
            OLD.status       <> NEW.status OR
            OLD.opac_visible <> NEW.opac_visible OR
            OLD.circ_lib     <> NEW.circ_lib OR
            OLD.call_number  <> NEW.call_number
        THEN
            IF OLD.call_number  <> NEW.call_number THEN -- Special check since it's more expensive than the next branch
                SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;

                IF ncn.record <> ocn.record THEN
                    -- We have to use a record-specific WHERE clause
                    -- to avoid modifying the entries for peer-bib copies.
                    UPDATE  asset.copy_vis_attr_cache
                      SET   target_copy = NEW.id,
                            record = ncn.record
                      WHERE target_copy = OLD.id
                            AND record = ocn.record;

                END IF;
            ELSE
                -- Any of these could change visibility, but
                -- we'll save some queries and not try to calculate
                -- the change directly.  We want to update peer-bib
                -- entries in this case, unlike above.
                UPDATE  asset.copy_vis_attr_cache
                  SET   target_copy = NEW.id,
                        vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
                  WHERE target_copy = OLD.id;
            END IF;
        END IF;

    ELSIF TG_TABLE_NAME = 'call_number' THEN

        IF TG_OP = 'DELETE' AND OLD.label = '##URI##' AND dobib THEN -- really deleted located URI, if the delete protection rule is disabled...
            UPDATE  biblio.record_entry
              SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
              WHERE id = OLD.record;
            RETURN OLD;
        END IF;

        IF OLD.label = '##URI##' AND dobib THEN -- Located URI
            IF OLD.deleted <> NEW.deleted OR OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN
                UPDATE  biblio.record_entry
                  SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
                  WHERE id = NEW.record;

                IF OLD.record <> NEW.record THEN -- maybe on merge?
                    UPDATE  biblio.record_entry
                      SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
                      WHERE id = OLD.record;
                END IF;
            END IF;

        ELSIF OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN
            UPDATE  asset.copy_vis_attr_cache
              SET   record = NEW.record,
                    vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
              WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
                    AND record = OLD.record;

        END IF;

    ELSIF TG_TABLE_NAME = 'record_entry' AND OLD.source IS DISTINCT FROM NEW.source THEN -- Only handles ON UPDATE, INSERT above
        NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE);
    END IF;

    RETURN NEW;
END;

z_opac_vis_mat_view_tgr

Function: asset.cache_copy_visibility()
Timing: AFTER INSERT OR UPDATE ROW

DECLARE
    ocn     asset.call_number%ROWTYPE;
    ncn     asset.call_number%ROWTYPE;
    cid     BIGINT;
    dobib   BOOL;
BEGIN

    SELECT enabled = FALSE INTO dobib FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc';

    IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN -- Only needs ON INSERT OR DELETE, so handle separately
        IF TG_OP = 'INSERT' THEN
            INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
                NEW.peer_record,
                NEW.target_copy,
                asset.calculate_copy_visibility_attribute_set(NEW.target_copy)
            );

            RETURN NEW;
        ELSIF TG_OP = 'DELETE' THEN
            DELETE FROM asset.copy_vis_attr_cache
              WHERE record = OLD.peer_record AND target_copy = OLD.target_copy;

            RETURN OLD;
        END IF;
    END IF;

    IF TG_OP = 'INSERT' THEN -- Handles ON INSERT. ON UPDATE is below.
        IF TG_TABLE_NAME IN ('copy', 'unit') THEN
            SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;
            INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
                ncn.record,
                NEW.id,
                asset.calculate_copy_visibility_attribute_set(NEW.id)
            );
        ELSIF TG_TABLE_NAME = 'record_entry' THEN
            NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE);
        ELSIF TG_TABLE_NAME = 'call_number' AND NEW.label = '##URI##' AND dobib THEN -- New located URI
            UPDATE  biblio.record_entry
              SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
              WHERE id = NEW.record;

        END IF;

        RETURN NEW;
    END IF;

    -- handle items first, since with circulation activity
    -- their statuses change frequently
    IF TG_TABLE_NAME IN ('copy', 'unit') THEN -- This handles ON UPDATE OR DELETE. ON INSERT above

        IF TG_OP = 'DELETE' THEN -- Shouldn't get here, normally
            DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
            RETURN OLD;
        END IF;

        SELECT * INTO ncn FROM asset.call_number cn WHERE id = NEW.call_number;

        IF OLD.deleted <> NEW.deleted THEN
            IF NEW.deleted THEN
                DELETE FROM asset.copy_vis_attr_cache WHERE target_copy = OLD.id;
            ELSE
                INSERT INTO asset.copy_vis_attr_cache (record, target_copy, vis_attr_vector) VALUES (
                    ncn.record,
                    NEW.id,
                    asset.calculate_copy_visibility_attribute_set(NEW.id)
                );
            END IF;

            RETURN NEW;
        ELSIF OLD.location   <> NEW.location OR
            OLD.status       <> NEW.status OR
            OLD.opac_visible <> NEW.opac_visible OR
            OLD.circ_lib     <> NEW.circ_lib OR
            OLD.call_number  <> NEW.call_number
        THEN
            IF OLD.call_number  <> NEW.call_number THEN -- Special check since it's more expensive than the next branch
                SELECT * INTO ocn FROM asset.call_number cn WHERE id = OLD.call_number;

                IF ncn.record <> ocn.record THEN
                    -- We have to use a record-specific WHERE clause
                    -- to avoid modifying the entries for peer-bib copies.
                    UPDATE  asset.copy_vis_attr_cache
                      SET   target_copy = NEW.id,
                            record = ncn.record
                      WHERE target_copy = OLD.id
                            AND record = ocn.record;

                END IF;
            ELSE
                -- Any of these could change visibility, but
                -- we'll save some queries and not try to calculate
                -- the change directly.  We want to update peer-bib
                -- entries in this case, unlike above.
                UPDATE  asset.copy_vis_attr_cache
                  SET   target_copy = NEW.id,
                        vis_attr_vector = asset.calculate_copy_visibility_attribute_set(NEW.id)
                  WHERE target_copy = OLD.id;
            END IF;
        END IF;

    ELSIF TG_TABLE_NAME = 'call_number' THEN

        IF TG_OP = 'DELETE' AND OLD.label = '##URI##' AND dobib THEN -- really deleted located URI, if the delete protection rule is disabled...
            UPDATE  biblio.record_entry
              SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
              WHERE id = OLD.record;
            RETURN OLD;
        END IF;

        IF OLD.label = '##URI##' AND dobib THEN -- Located URI
            IF OLD.deleted <> NEW.deleted OR OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN
                UPDATE  biblio.record_entry
                  SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(NEW.record)
                  WHERE id = NEW.record;

                IF OLD.record <> NEW.record THEN -- maybe on merge?
                    UPDATE  biblio.record_entry
                      SET   vis_attr_vector = biblio.calculate_bib_visibility_attribute_set(OLD.record)
                      WHERE id = OLD.record;
                END IF;
            END IF;

        ELSIF OLD.record <> NEW.record OR OLD.owning_lib <> NEW.owning_lib THEN
            UPDATE  asset.copy_vis_attr_cache
              SET   record = NEW.record,
                    vis_attr_vector = asset.calculate_copy_visibility_attribute_set(target_copy)
              WHERE target_copy IN (SELECT id FROM asset.copy WHERE call_number = NEW.id)
                    AND record = OLD.record;

        END IF;

    ELSIF TG_TABLE_NAME = 'record_entry' AND OLD.source IS DISTINCT FROM NEW.source THEN -- Only handles ON UPDATE, INSERT above
        NEW.vis_attr_vector := biblio.calculate_bib_visibility_attribute_set(NEW.id, NEW.source, TRUE);
    END IF;

    RETURN NEW;
END;

Referenced By

The following tables have foreign keys pointing to serial.unit (1 referencing table(s)):

Table Referencing Column(s) Referenced Column(s) Constraint

serial.item

unit

id

item_unit_fkey
Previous
Next