vandelay.queued_authority_record

↑ vandelay schema

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.

Cascading Deletes: Deleting rows from this table will cascade to: vandelay.authority_queue.

Deferrable Constraints: The following FK constraints are deferrable — they are checked at transaction end, not statement end: queued_authority_record_import_error_fkey, queued_authority_record_imported_as_fkey, queued_authority_record_queue_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('vandelay.queued_record_id_seq'::regclass)

create_time

timestamp with time zone

No

now()

import_time

timestamp with time zone

Yes

purpose

text

No

'import'::text

marc

text

No

quality

integer

No

0

queue FK

integer

No

→ vandelay.authority_queue(id)

imported_as FK

integer

Yes

→ authority.record_entry(id)

import_error FK

text

Yes

→ vandelay.import_error(code)

error_detail

text

Yes

Primary Key

(id)

Foreign Keys

Column(s) References On Delete On Update Deferrable Constraint

import_error

vandelay.import_error(code)

SET NULL

CASCADE

DEFERRED

queued_authority_record_import_error_fkey

imported_as

authority.record_entry(id)

NO ACTION

NO ACTION

DEFERRED

queued_authority_record_imported_as_fkey

queue

vandelay.authority_queue(id)

CASCADE

NO ACTION

DEFERRED

queued_authority_record_queue_fkey

Check Constraints

  • queued_record_purpose_check: CHECK purpose = ANY (ARRAY['import'::text, 'overlay'::text])

Indexes

Index Method Definition

queued_authority_record_pkey PK

btree

CREATE UNIQUE INDEX queued_authority_record_pkey ON vandelay.queued_authority_record USING btree (id)

queued_authority_record_queue_idx

btree

CREATE INDEX queued_authority_record_queue_idx ON vandelay.queued_authority_record USING btree (queue)

Triggers

Trigger Timing Event Level Function

cleanup_authority_trigger

BEFORE

DELETE OR UPDATE

ROW

vandelay.cleanup_authority_marc()

ingest_authority_trigger

AFTER

INSERT OR UPDATE

ROW

vandelay.ingest_authority_marc()

zz_match_auths_trigger

BEFORE

INSERT OR UPDATE

ROW

vandelay.match_authority_record()

Trigger Bodies

cleanup_authority_trigger

Function: vandelay.cleanup_authority_marc()
Timing: BEFORE DELETE OR UPDATE ROW

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

 
BEGIN
    IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
        RETURN NEW;
    END IF;

    DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
    IF TG_OP = 'UPDATE' THEN
        RETURN NEW;
    END IF;
    RETURN OLD;
END;

ingest_authority_trigger

Function: vandelay.ingest_authority_marc()
Timing: AFTER INSERT OR UPDATE ROW

DECLARE
    value   TEXT;
    atype   TEXT;
    adef    RECORD;
BEGIN
    IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
        RETURN NEW;
    END IF;

    FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP

        SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
        IF (value IS NOT NULL AND value <> '') THEN
            INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
        END IF;

    END LOOP;

    RETURN NULL;
END;

zz_match_auths_trigger

Function: vandelay.match_authority_record()
Timing: BEFORE INSERT OR UPDATE ROW

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

 
DECLARE
    incoming_existing_id    TEXT;
    test_result             vandelay.match_set_test_result%ROWTYPE;
    tmp_rec                 BIGINT;
    match_set               INT;
BEGIN
    IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
        RETURN NEW;
    END IF;

    DELETE FROM vandelay.authority_match WHERE queued_record = NEW.id;

    SELECT q.match_set INTO match_set FROM vandelay.authority_queue q WHERE q.id = NEW.queue;

    IF match_set IS NOT NULL THEN
        NEW.quality := vandelay.measure_auth_record_quality( NEW.marc, match_set );
    END IF;

    -- Perfect matches on 901$c exit early with a match with high quality.
    incoming_existing_id :=
        oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]', NEW.marc);

    IF incoming_existing_id IS NOT NULL AND incoming_existing_id != '' THEN
        SELECT id INTO tmp_rec FROM authority.record_entry WHERE id = incoming_existing_id::bigint;
        IF tmp_rec IS NOT NULL THEN
            INSERT INTO vandelay.authority_match (queued_record, eg_record, match_score, quality)
                SELECT
                    NEW.id,
                    b.id,
                    9999,
                    -- note: no match_set means quality==0
                    vandelay.measure_auth_record_quality( b.marc, match_set )
                FROM authority.record_entry b
                WHERE id = incoming_existing_id::bigint;
        END IF;
    END IF;

    IF match_set IS NULL THEN
        RETURN NEW;
    END IF;

    FOR test_result IN SELECT * FROM
        vandelay.match_set_test_authxml(match_set, NEW.marc) LOOP

        INSERT INTO vandelay.authority_match ( queued_record, eg_record, match_score, quality )
            SELECT
                NEW.id,
                test_result.record,
                test_result.quality,
                vandelay.measure_auth_record_quality( b.marc, match_set )
	        FROM  authority.record_entry b
	        WHERE id = test_result.record;

    END LOOP;

    RETURN NEW;
END;

Referenced By

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

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

vandelay.authority_match

queued_record

id

authority_match_queued_record_fkey

vandelay.queued_authority_record_attr

record

id

queued_authority_record_attr_record_fkey
Previous
Next