serial.issuance

↑ serial schema

Cascading Deletes: Deleting rows from this table will cascade to: serial.caption_and_pattern, serial.subscription.

Deferrable Constraints: The following FK constraints are deferrable — they are checked at transaction end, not statement end: issuance_caption_and_pattern_fkey, issuance_creator_fkey, issuance_editor_fkey, issuance_subscription_fkey.

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

Columns

Column Type Nullable Default Notes

id PK

integer

No

nextval('serial.issuance_id_seq'::regclass)

creator FK

integer

No

→ actor.usr(id)

editor FK

integer

No

→ actor.usr(id)

create_date

timestamp with time zone

No

now()

edit_date

timestamp with time zone

No

now()

subscription FK

integer

No

→ serial.subscription(id)

label

text

Yes

date_published

timestamp with time zone

Yes

caption_and_pattern FK

integer

Yes

→ serial.caption_and_pattern(id)

holding_code

text

Yes

holding_type

text

Yes

holding_link_id

integer

Yes

Primary Key

(id)

Foreign Keys

Column(s) References On Delete On Update Deferrable Constraint

caption_and_pattern

serial.caption_and_pattern(id)

CASCADE

NO ACTION

DEFERRED

issuance_caption_and_pattern_fkey

creator

actor.usr(id)

NO ACTION

NO ACTION

DEFERRED

issuance_creator_fkey

editor

actor.usr(id)

NO ACTION

NO ACTION

DEFERRED

issuance_editor_fkey

subscription

serial.subscription(id)

CASCADE

NO ACTION

DEFERRED

issuance_subscription_fkey

Check Constraints

  • issuance_holding_code_check: CHECK

  • issuance_holding_code_check1: CHECK

  • valid_holding_type: CHECK

Indexes

Index Method Definition

issuance_pkey PK

btree

CREATE UNIQUE INDEX issuance_pkey ON serial.issuance USING btree (id)

serial_issuance_caption_and_pattern_idx

btree

CREATE INDEX serial_issuance_caption_and_pattern_idx ON serial.issuance USING btree (caption_and_pattern)

serial_issuance_date_published_idx

btree

CREATE INDEX serial_issuance_date_published_idx ON serial.issuance USING btree (date_published)

serial_issuance_sub_idx

btree

CREATE INDEX serial_issuance_sub_idx ON serial.issuance USING btree (subscription)

Triggers

Trigger Timing Event Level Function

materialize_holding_code

AFTER

INSERT OR UPDATE

ROW

serial.materialize_holding_code()

Trigger Bodies

materialize_holding_code

Function: serial.materialize_holding_code()
Timing: AFTER INSERT OR UPDATE ROW

use strict;

use MARC::Field;
use JSON::XS;

if (not defined $_TD->{new}{holding_code}) {
    elog(WARNING, 'NULL in "holding_code" column of serial.issuance allowed for now, but may not be useful');
    return;
}

# Do nothing if holding_code has not changed...

if ($_TD->{new}{holding_code} eq $_TD->{old}{holding_code}) {
    # ... unless the following internal flag is set.

    my $flag_rv = spi_exec_query(q{
        SELECT * FROM config.internal_flag
        WHERE name = 'serial.rematerialize_on_same_holding_code' AND enabled
    }, 1);
    return unless $flag_rv->{processed};
}


my $holding_code = (new JSON::XS)->decode($_TD->{new}{holding_code});

my $field = new MARC::Field('999', @$holding_code); # tag doesnt matter

my $dstmt = spi_prepare(
    'DELETE FROM serial.materialized_holding_code WHERE issuance = $1',
    'INT'
);
spi_exec_prepared($dstmt, $_TD->{new}{id});

my $istmt = spi_prepare(
    q{
        INSERT INTO serial.materialized_holding_code (
            issuance, subfield, value
        ) VALUES ($1, $2, $3)
    }, qw{INT CHAR TEXT}
);

foreach ($field->subfields) {
    spi_exec_prepared(
        $istmt,
        $_TD->{new}{id},
        $_->[0],
        $_->[1]
    );
}

return;

Referenced By

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

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

serial.item

issuance

id

item_issuance_fkey

serial.materialized_holding_code

issuance

id

materialized_holding_code_issuance_fkey
Previous
Next