2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2008 Equinox Software, Inc.
4 * Copyright (C) 2010 Laurentian University
5 * Mike Rylander <miker@esilibrary.com>
6 * Dan Scott <dscott@laurentian.ca>
8 * This program is free software; you can redistribute it and/or
9 * modify it under the terms of the GNU General Public License
10 * as published by the Free Software Foundation; either version 2
11 * of the License, or (at your option) any later version.
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
20 DROP SCHEMA IF EXISTS authority CASCADE;
23 CREATE SCHEMA authority;
25 CREATE TABLE authority.control_set (
26 id SERIAL PRIMARY KEY,
27 name TEXT NOT NULL UNIQUE, -- i18n
28 description TEXT -- i18n
31 CREATE TABLE authority.control_set_authority_field (
32 id SERIAL PRIMARY KEY,
33 main_entry INT REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
34 control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
36 nfi CHAR(1), -- non-filing indicator
37 sf_list TEXT NOT NULL,
38 name TEXT NOT NULL, -- i18n
39 description TEXT -- i18n
42 CREATE TABLE authority.control_set_bib_field (
43 id SERIAL PRIMARY KEY,
44 authority_field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
48 CREATE TABLE authority.thesaurus (
49 code TEXT PRIMARY KEY, -- MARC21 thesaurus code
50 control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
51 name TEXT NOT NULL UNIQUE, -- i18n
52 description TEXT -- i18n
55 CREATE TABLE authority.browse_axis (
56 code TEXT PRIMARY KEY,
57 name TEXT UNIQUE NOT NULL, -- i18n
58 sorter TEXT REFERENCES config.record_attr_definition (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
62 CREATE TABLE authority.browse_axis_authority_field_map (
63 id SERIAL PRIMARY KEY,
64 axis TEXT NOT NULL REFERENCES authority.browse_axis (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
65 field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
68 CREATE TABLE authority.record_entry (
69 id BIGSERIAL PRIMARY KEY,
70 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
71 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
72 creator INT NOT NULL DEFAULT 1,
73 editor INT NOT NULL DEFAULT 1,
74 active BOOL NOT NULL DEFAULT TRUE,
75 deleted BOOL NOT NULL DEFAULT FALSE,
77 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
79 last_xact_id TEXT NOT NULL,
82 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
83 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
84 CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
85 CREATE TRIGGER a_marcxml_is_well_formed BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.check_marcxml_well_formed();
86 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
87 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
89 CREATE TABLE authority.bib_linking (
90 id BIGSERIAL PRIMARY KEY,
91 bib BIGINT NOT NULL REFERENCES biblio.record_entry (id),
92 authority BIGINT NOT NULL REFERENCES authority.record_entry (id)
94 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
95 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
97 CREATE TABLE authority.record_note (
98 id BIGSERIAL PRIMARY KEY,
99 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
101 creator INT NOT NULL DEFAULT 1,
102 editor INT NOT NULL DEFAULT 1,
103 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
104 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
106 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
107 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
108 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
110 CREATE TABLE authority.rec_descriptor (
111 id BIGSERIAL PRIMARY KEY,
117 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
119 CREATE TABLE authority.full_rec (
120 id BIGSERIAL PRIMARY KEY,
121 record BIGINT NOT NULL,
122 tag CHAR(3) NOT NULL,
127 index_vector tsvector NOT NULL
129 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
130 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
131 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
132 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
133 CREATE TRIGGER authority_full_rec_fti_trigger
134 BEFORE UPDATE OR INSERT ON authority.full_rec
135 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
137 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
138 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
139 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
140 /* But we still need this (boooo) for paging using >, <, etc */
141 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
143 CREATE RULE protect_authority_rec_delete AS ON DELETE TO authority.record_entry DO INSTEAD (UPDATE authority.record_entry SET deleted = TRUE WHERE OLD.id = authority.record_entry.id; DELETE FROM authority.full_rec WHERE record = OLD.id);
145 -- Intended to be used in a unique index on authority.record_entry like so:
146 -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
147 -- ON authority.record_entry (authority.normalize_heading(marc))
148 -- WHERE deleted IS FALSE or deleted = FALSE;
149 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
151 acsaf authority.control_set_authority_field%ROWTYPE;
160 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT;
162 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
165 SELECT control_set INTO cset
166 FROM authority.control_set_authority_field
167 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
171 IF thes_code = 'z' THEN
172 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
176 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
177 tag_used := acsaf.tag;
178 nfi_used := acsaf.nfi;
180 FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
181 tmp_text := oils_xpath_string('//*[@tag="'||tag_used||'"]/*[@code="'||sf||'"]', marcxml);
183 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
185 tmp_text := SUBSTRING(
190 oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml),
205 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
206 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
209 EXIT WHEN heading_text <> '';
212 IF heading_text <> '' THEN
213 IF no_thesaurus IS TRUE THEN
214 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
216 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
219 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
224 $func$ LANGUAGE PLPGSQL IMMUTABLE;
226 CREATE TABLE authority.simple_heading (
227 id BIGSERIAL PRIMARY KEY,
228 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
229 atag INT NOT NULL REFERENCES authority.control_set_authority_field (id),
231 sort_value TEXT NOT NULL,
232 index_vector tsvector NOT NULL
234 CREATE TRIGGER authority_simple_heading_fti_trigger
235 BEFORE UPDATE OR INSERT ON authority.simple_heading
236 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
238 CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector);
239 CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value);
240 CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value);
242 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
244 res authority.simple_heading%ROWTYPE;
245 acsaf authority.control_set_authority_field%ROWTYPE;
255 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT;
258 res.record := auth_id;
260 SELECT control_set INTO cset
261 FROM authority.control_set_authority_field
262 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]) )
265 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
267 res.atag := acsaf.id;
268 tag_used := acsaf.tag;
269 nfi_used := acsaf.nfi;
271 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
274 FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
275 heading_text := heading_text || COALESCE( ' ' || oils_xpath_string('//*[@code="'||sf||'"]',tmp_xml::TEXT), '');
278 heading_text := public.naco_normalize(heading_text);
280 IF nfi_used IS NOT NULL THEN
282 sort_text := SUBSTRING(
287 oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml),
299 sort_text := heading_text;
302 IF heading_text IS NOT NULL AND heading_text <> '' THEN
303 res.value := heading_text;
304 res.sort_value := sort_text;
314 $func$ LANGUAGE PLPGSQL IMMUTABLE;
316 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
317 SELECT authority.normalize_heading($1, TRUE);
318 $func$ LANGUAGE SQL IMMUTABLE;
320 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
321 SELECT authority.normalize_heading($1, FALSE);
322 $func$ LANGUAGE SQL IMMUTABLE;
324 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
325 Extract the authority heading, thesaurus, and NACO-normalized values
326 from an authority record. The primary purpose is to build a unique
327 index to defend against duplicated authority records from the same
331 -- Adding indexes using oils_xpath_string() for the main entry tags described in
332 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
333 -- the existing index on authority.normalize_heading() helps already with a record in hand
334 CREATE OR REPLACE VIEW authority.tracing_links AS
335 SELECT main.record AS record,
337 main.tag AS main_tag,
338 oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
339 substr(link.value,1,1) AS relationship,
340 substr(link.value,2,1) AS use_restriction,
341 substr(link.value,3,1) AS deprecation,
342 substr(link.value,4,1) AS display_restriction,
344 link.tag AS link_tag,
345 oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
346 authority.normalize_heading(are.marc) AS normalized_main_value
347 FROM authority.full_rec main
348 JOIN authority.record_entry are ON (main.record = are.id)
349 JOIN authority.control_set_authority_field main_entry
350 ON (main_entry.tag = main.tag
351 AND main_entry.main_entry IS NULL
352 AND main.subfield = 'a' )
353 JOIN authority.control_set_authority_field sub_entry
354 ON (main_entry.id = sub_entry.main_entry)
355 JOIN authority.full_rec link
356 ON (link.record = main.record
357 AND link.tag = sub_entry.tag
358 AND link.subfield = 'w' );
360 -- Function to generate an ephemeral overlay template from an authority record
361 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
364 main_entry authority.control_set_authority_field%ROWTYPE;
365 bib_field authority.control_set_bib_field%ROWTYPE;
366 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
367 replace_data XML[] DEFAULT '{}'::XML[];
368 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
371 IF auth_id IS NULL THEN
375 -- Default to the LoC controll set
376 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
378 -- if none, make a best guess
380 SELECT control_set INTO cset
381 FROM authority.control_set_authority_field
383 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
384 FROM authority.record_entry
390 -- if STILL none, no-op change
394 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
395 XMLELEMENT( name leader, '00881nam a2200193 4500'),
398 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
401 XMLATTRIBUTES('d' AS code),
408 FOR main_entry IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
409 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
410 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
411 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
412 replace_data := replace_data || XMLELEMENT( name datafield, XMLATTRIBUTES(bib_field.tag AS tag), XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]);
413 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
421 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
422 XMLELEMENT( name leader, '00881nam a2200193 4500'),
426 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
429 XMLATTRIBUTES('r' AS code),
430 ARRAY_TO_STRING(replace_rules,',')
435 $f$ STABLE LANGUAGE PLPGSQL;
437 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
438 SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
441 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
443 moved_objects INT := 0;
445 bib_rec biblio.record_entry%ROWTYPE;
446 auth_link authority.bib_linking%ROWTYPE;
450 -- Defining our terms:
451 -- "target record" = the record that will survive the merge
452 -- "source record" = the record that is sacrifing its existence and being
453 -- replaced by the target record
455 -- 1. Update all bib records with the ID from target_record in their $0
458 FROM biblio.record_entry bre
459 JOIN authority.bib_linking abl ON abl.bib = bre.id
460 WHERE abl.authority = source_record
463 UPDATE biblio.record_entry
464 SET marc = REGEXP_REPLACE(
466 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
467 E'\\1' || target_record || '<',
470 WHERE id = bib_rec.id;
472 moved_objects := moved_objects + 1;
475 -- 2. Grab the current value of reingest on same MARC flag
476 SELECT enabled INTO ingest_same
477 FROM config.internal_flag
478 WHERE name = 'ingest.reingest.force_on_same_marc'
481 -- 3. Temporarily set reingest on same to TRUE
482 UPDATE config.internal_flag
484 WHERE name = 'ingest.reingest.force_on_same_marc'
487 -- 4. Make a harmless update to target_record to trigger auto-update
488 -- in linked bibliographic records
489 UPDATE authority.record_entry
491 WHERE id = target_record;
493 -- 5. "Delete" source_record
494 DELETE FROM authority.record_entry WHERE id = source_record;
496 -- 6. Set "reingest on same MARC" flag back to initial value
497 UPDATE config.internal_flag
498 SET enabled = ingest_same
499 WHERE name = 'ingest.reingest.force_on_same_marc'
502 RETURN moved_objects;
504 $func$ LANGUAGE plpgsql;
507 -- Support function used to find the pivot for alpha-heading-browse style searching
508 CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$
510 sort_value_row RECORD;
515 t_term := public.naco_normalize(q);
517 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
518 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
521 FROM authority.simple_heading ash
522 WHERE ash.atag = ANY (a)
523 AND ash.sort_value >= t_term
524 ORDER BY rank DESC, ash.sort_value
527 SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END
528 + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank,
531 FROM authority.simple_heading ash
532 WHERE ash.atag = ANY (a)
533 AND ash.value >= t_term
534 ORDER BY rank DESC, ash.sort_value
537 IF value_row.rank > sort_value_row.rank THEN
538 RETURN value_row.sort_value;
540 RETURN sort_value_row.sort_value;
545 CREATE OR REPLACE FUNCTION authority.simple_heading_browse_center( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
547 pivot_sort_value TEXT;
548 boffset INT DEFAULT 0;
549 aoffset INT DEFAULT 0;
550 blimit INT DEFAULT 0;
551 alimit INT DEFAULT 0;
554 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
557 blimit := pagesize / 2;
560 IF pagesize % 2 <> 0 THEN
561 alimit := alimit + 1;
567 boffset := pagesize / 2;
570 IF pagesize % 2 <> 0 THEN
571 boffset := boffset + 1;
577 -- "bottom" half of the browse results
581 FROM authority.simple_heading ash
582 WHERE ash.atag = ANY (atag_list)
583 AND ash.sort_value < pivot_sort_value
584 ORDER BY ash.sort_value DESC
586 OFFSET ABS(page) * pagesize - boffset
587 ) x ORDER BY row_number DESC;
592 -- "bottom" half of the browse results
594 FROM authority.simple_heading ash
595 WHERE ash.atag = ANY (atag_list)
596 AND ash.sort_value >= pivot_sort_value
597 ORDER BY ash.sort_value
599 OFFSET ABS(page) * pagesize - aoffset;
602 $$ LANGUAGE PLPGSQL ROWS 10;
604 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
605 SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
608 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
611 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
613 FROM authority.browse_axis_authority_field_map a
619 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
620 SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
623 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
625 ARRAY[a.authority_field],
626 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
628 FROM authority.control_set_bib_field a
634 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
635 SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1
638 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
641 (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
643 FROM authority.control_set_authority_field a
649 CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
650 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4)
651 $$ LANGUAGE SQL ROWS 10;
653 CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
654 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4)
655 $$ LANGUAGE SQL ROWS 10;
657 CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
658 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4)
659 $$ LANGUAGE SQL ROWS 10;
661 CREATE OR REPLACE FUNCTION authority.axis_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
662 SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4)
663 $$ LANGUAGE SQL ROWS 10;
665 CREATE OR REPLACE FUNCTION authority.btag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
666 SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4)
667 $$ LANGUAGE SQL ROWS 10;
669 CREATE OR REPLACE FUNCTION authority.atag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$
670 SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4)
671 $$ LANGUAGE SQL ROWS 10;
674 CREATE OR REPLACE FUNCTION authority.simple_heading_browse_top( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
676 pivot_sort_value TEXT;
679 pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q);
683 -- "bottom" half of the browse results
687 FROM authority.simple_heading ash
688 WHERE ash.atag = ANY (atag_list)
689 AND ash.sort_value < pivot_sort_value
690 ORDER BY ash.sort_value DESC
692 OFFSET (ABS(page) - 1) * pagesize
693 ) x ORDER BY row_number DESC;
698 -- "bottom" half of the browse results
700 FROM authority.simple_heading ash
701 WHERE ash.atag = ANY (atag_list)
702 AND ash.sort_value >= pivot_sort_value
703 ORDER BY ash.sort_value
705 OFFSET ABS(page) * pagesize ;
708 $$ LANGUAGE PLPGSQL ROWS 10;
710 CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
711 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4)
712 $$ LANGUAGE SQL ROWS 10;
714 CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
715 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4)
716 $$ LANGUAGE SQL ROWS 10;
718 CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
719 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4)
720 $$ LANGUAGE SQL ROWS 10;
722 CREATE OR REPLACE FUNCTION authority.axis_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
723 SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4)
724 $$ LANGUAGE SQL ROWS 10;
726 CREATE OR REPLACE FUNCTION authority.btag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
727 SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4)
728 $$ LANGUAGE SQL ROWS 10;
730 CREATE OR REPLACE FUNCTION authority.atag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
731 SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4)
732 $$ LANGUAGE SQL ROWS 10;
735 CREATE OR REPLACE FUNCTION authority.simple_heading_search_rank( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
737 FROM authority.simple_heading ash,
738 public.naco_normalize($2) t(term),
739 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
740 WHERE ash.atag = ANY ($1)
741 AND ash.index_vector @@ ptsq.term
742 ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric
743 + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END
744 + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC
747 $$ LANGUAGE SQL ROWS 10;
749 CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
750 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4)
751 $$ LANGUAGE SQL ROWS 10;
753 CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
754 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4)
755 $$ LANGUAGE SQL ROWS 10;
757 CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
758 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4)
759 $$ LANGUAGE SQL ROWS 10;
761 CREATE OR REPLACE FUNCTION authority.axis_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
762 SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4)
763 $$ LANGUAGE SQL ROWS 10;
765 CREATE OR REPLACE FUNCTION authority.btag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
766 SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4)
767 $$ LANGUAGE SQL ROWS 10;
769 CREATE OR REPLACE FUNCTION authority.atag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
770 SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4)
771 $$ LANGUAGE SQL ROWS 10;
774 CREATE OR REPLACE FUNCTION authority.simple_heading_search_heading( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
776 FROM authority.simple_heading ash,
777 public.naco_normalize($2) t(term),
778 plainto_tsquery('keyword'::regconfig,$2) ptsq(term)
779 WHERE ash.atag = ANY ($1)
780 AND ash.index_vector @@ ptsq.term
781 ORDER BY ash.sort_value
784 $$ LANGUAGE SQL ROWS 10;
786 CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
787 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4)
788 $$ LANGUAGE SQL ROWS 10;
790 CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
791 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4)
792 $$ LANGUAGE SQL ROWS 10;
794 CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
795 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4)
796 $$ LANGUAGE SQL ROWS 10;
798 CREATE OR REPLACE FUNCTION authority.axis_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
799 SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4)
800 $$ LANGUAGE SQL ROWS 10;
802 CREATE OR REPLACE FUNCTION authority.btag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
803 SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4)
804 $$ LANGUAGE SQL ROWS 10;
806 CREATE OR REPLACE FUNCTION authority.atag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$
807 SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4)
808 $$ LANGUAGE SQL ROWS 10;