metabib.subject_field_entry

↑ metabib 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: config.metabib_field, biblio.record_entry.

Deferrable Constraints: The following FK constraints are deferrable — they are checked at transaction end, not statement end: metabib_subject_field_entry_field_pkey, metabib_subject_field_entry_source_pkey.

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

Columns

Column Type Nullable Default Notes

id PK

bigint

No

nextval('metabib.subject_field_entry_id_seq'::regclass)

source FK

bigint

No

→ biblio.record_entry(id)

field FK

integer

No

→ config.metabib_field(id)

value

text

No

index_vector

tsvector

No

Primary Key

(id)

Foreign Keys

Column(s) References On Delete On Update Deferrable Constraint

field

config.metabib_field(id)

CASCADE

NO ACTION

DEFERRED

metabib_subject_field_entry_field_pkey

source

biblio.record_entry(id)

CASCADE

NO ACTION

DEFERRED

metabib_subject_field_entry_source_pkey

Indexes

Index Method Definition

subject_field_entry_pkey PK

btree

CREATE UNIQUE INDEX subject_field_entry_pkey ON metabib.subject_field_entry USING btree (id)

metabib_subject_field_entry_index_vector_idx

GIN

CREATE INDEX metabib_subject_field_entry_index_vector_idx ON metabib.subject_field_entry USING gin (index_vector)

metabib_subject_field_entry_source_idx

btree

CREATE INDEX metabib_subject_field_entry_source_idx ON metabib.subject_field_entry USING btree (source)

metabib_subject_field_entry_value_idx

btree

CREATE INDEX metabib_subject_field_entry_value_idx ON metabib.subject_field_entry USING btree ("substring"(value, 1, 1024)) WHERE (index_vector = ''::tsvector)

Triggers

Trigger Timing Event Level Function

maintain_symspell_entries_tgr

AFTER

INSERT OR DELETE OR UPDATE

ROW

search.symspell_maintain_entries()

metabib_subject_field_entry_fti_trigger

BEFORE

INSERT OR UPDATE

ROW

public.oils_tsearch2()

Trigger Bodies

maintain_symspell_entries_tgr

Function: search.symspell_maintain_entries()
Timing: AFTER INSERT OR DELETE OR UPDATE ROW

DECLARE
    search_class    TEXT;
    new_value       TEXT := NULL;
    old_value       TEXT := NULL;
    _atag           INTEGER;
BEGIN

    IF TG_TABLE_SCHEMA = 'authority' THEN
        IF TG_OP IN ('INSERT', 'UPDATE') THEN
            _atag = NEW.atag;
        ELSE
            _atag = OLD.atag;
        END IF;

        SELECT  m.field_class INTO search_class
          FROM  authority.control_set_auth_field_metabib_field_map_refs a
                JOIN config.metabib_field m ON (a.metabib_field=m.id)
          WHERE a.authority_field = _atag;

        IF NOT FOUND THEN
            RETURN NULL;
        END IF;
    ELSE
        search_class := COALESCE(TG_ARGV[0], SPLIT_PART(TG_TABLE_NAME,'_',1));
    END IF;

    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        new_value := NEW.value;
    END IF;

    IF TG_OP IN ('DELETE', 'UPDATE') THEN
        old_value := OLD.value;
    END IF;

    IF new_value = old_value THEN
        -- same, move along
    ELSE
        INSERT INTO search.symspell_dictionary_updates
            SELECT  txid_current(), *
              FROM  search.symspell_build_entries(
                        new_value,
                        search_class,
                        old_value
                    );
    END IF;

    -- PERFORM * FROM search.symspell_build_and_merge_entries(new_value, search_class, old_value);

    RETURN NULL; -- always fired AFTER
END;

metabib_subject_field_entry_fti_trigger

Function: public.oils_tsearch2()
Timing: BEFORE INSERT OR UPDATE ROW

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

 
DECLARE
    normalizer      RECORD;
    value           TEXT := '';
    temp_vector     TEXT := '';
    ts_rec          RECORD;
    cur_weight      "char";
BEGIN

    value := NEW.value;
    NEW.index_vector = ''::tsvector;

    IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
        FOR normalizer IN
            SELECT  n.func AS func,
                    n.param_count AS param_count,
                    m.params AS params
              FROM  config.index_normalizer n
                    JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
              WHERE field = NEW.field AND m.pos < 0
              ORDER BY m.pos LOOP
                EXECUTE 'SELECT ' || normalizer.func || '(' ||
                    quote_literal( value ) ||
                    CASE
                        WHEN normalizer.param_count > 0
                            THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
                            ELSE ''
                        END ||
                    ')' INTO value;

        END LOOP;

        NEW.value = value;

        FOR normalizer IN
            SELECT  n.func AS func,
                    n.param_count AS param_count,
                    m.params AS params
              FROM  config.index_normalizer n
                    JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
              WHERE field = NEW.field AND m.pos >= 0
              ORDER BY m.pos LOOP
                EXECUTE 'SELECT ' || normalizer.func || '(' ||
                    quote_literal( value ) ||
                    CASE
                        WHEN normalizer.param_count > 0
                            THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
                            ELSE ''
                        END ||
                    ')' INTO value;

        END LOOP;
   END IF;

    IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN

        value :=  ARRAY_TO_STRING(
            evergreen.regexp_split_to_array(value, E'\\W+'), ' '
        );
        value := public.search_normalize(value);
        NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);

    ELSIF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
        FOR ts_rec IN

            SELECT DISTINCT m.ts_config, m.index_weight
            FROM config.metabib_class_ts_map m
                 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
                 LEFT JOIN config.coded_value_map ccvm ON (
                    ccvm.ctype IN ('item_lang', 'language') AND
                    ccvm.code = m.index_lang AND
                    r.vlist @> intset(ccvm.id)
                )
            WHERE m.field_class = TG_ARGV[0]
                AND m.active
                AND (m.always OR NOT EXISTS (SELECT 1 FROM config.metabib_field_ts_map WHERE metabib_field = NEW.field))
                AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
                        UNION
            SELECT DISTINCT m.ts_config, m.index_weight
            FROM config.metabib_field_ts_map m
                 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
                 LEFT JOIN config.coded_value_map ccvm ON (
                    ccvm.ctype IN ('item_lang', 'language') AND
                    ccvm.code = m.index_lang AND
                    r.vlist @> intset(ccvm.id)
                )
            WHERE m.metabib_field = NEW.field
                AND m.active
                AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
            ORDER BY index_weight ASC

        LOOP

            IF cur_weight IS NOT NULL AND cur_weight != ts_rec.index_weight THEN
                NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
                temp_vector = '';
            END IF;

            cur_weight = ts_rec.index_weight;
            SELECT INTO temp_vector temp_vector || ' ' || to_tsvector(ts_rec.ts_config::regconfig, value)::TEXT;

        END LOOP;
        NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
    ELSE
        NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
    END IF;

    RETURN NEW;
END;
Previous
Next