2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
18 DROP SCHEMA metabib CASCADE;
21 CREATE SCHEMA metabib;
23 CREATE TABLE metabib.metarecord (
24 id BIGSERIAL PRIMARY KEY,
25 fingerprint TEXT NOT NULL,
29 CREATE INDEX metabib_metarecord_master_record_idx ON metabib.metarecord (master_record);
30 CREATE INDEX metabib_metarecord_fingerprint_idx ON metabib.metarecord (fingerprint);
32 CREATE TABLE metabib.title_field_entry (
33 id BIGSERIAL PRIMARY KEY,
34 source BIGINT NOT NULL,
37 index_vector tsvector NOT NULL
39 CREATE TRIGGER metabib_title_field_entry_fti_trigger
40 BEFORE UPDATE OR INSERT ON metabib.title_field_entry
41 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('title');
43 CREATE INDEX metabib_title_field_entry_index_vector_idx ON metabib.title_field_entry USING GIST (index_vector);
46 CREATE TABLE metabib.author_field_entry (
47 id BIGSERIAL PRIMARY KEY,
48 source BIGINT NOT NULL,
51 index_vector tsvector NOT NULL
53 CREATE TRIGGER metabib_author_field_entry_fti_trigger
54 BEFORE UPDATE OR INSERT ON metabib.author_field_entry
55 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('author');
57 CREATE INDEX metabib_author_field_entry_index_vector_idx ON metabib.author_field_entry USING GIST (index_vector);
60 CREATE TABLE metabib.subject_field_entry (
61 id BIGSERIAL PRIMARY KEY,
62 source BIGINT NOT NULL,
65 index_vector tsvector NOT NULL
67 CREATE TRIGGER metabib_subject_field_entry_fti_trigger
68 BEFORE UPDATE OR INSERT ON metabib.subject_field_entry
69 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('subject');
71 CREATE INDEX metabib_subject_field_entry_index_vector_idx ON metabib.subject_field_entry USING GIST (index_vector);
72 CREATE INDEX metabib_subject_field_entry_source_idx ON metabib.subject_field_entry (source);
75 CREATE TABLE metabib.keyword_field_entry (
76 id BIGSERIAL PRIMARY KEY,
77 source BIGINT NOT NULL,
80 index_vector tsvector NOT NULL
82 CREATE TRIGGER metabib_keyword_field_entry_fti_trigger
83 BEFORE UPDATE OR INSERT ON metabib.keyword_field_entry
84 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
86 CREATE INDEX metabib_keyword_field_entry_index_vector_idx ON metabib.keyword_field_entry USING GIST (index_vector);
89 CREATE TABLE metabib.series_field_entry (
90 id BIGSERIAL PRIMARY KEY,
91 source BIGINT NOT NULL,
94 index_vector tsvector NOT NULL
96 CREATE TRIGGER metabib_series_field_entry_fti_trigger
97 BEFORE UPDATE OR INSERT ON metabib.series_field_entry
98 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('series');
100 CREATE INDEX metabib_series_field_entry_index_vector_idx ON metabib.series_field_entry USING GIST (index_vector);
103 CREATE TABLE metabib.rec_descriptor (
104 id BIGSERIAL PRIMARY KEY,
122 CREATE INDEX metabib_rec_descriptor_record_idx ON metabib.rec_descriptor (record);
123 /* We may not need these...
125 CREATE INDEX metabib_rec_descriptor_item_type_idx ON metabib.rec_descriptor (item_type);
126 CREATE INDEX metabib_rec_descriptor_item_form_idx ON metabib.rec_descriptor (item_form);
127 CREATE INDEX metabib_rec_descriptor_bib_level_idx ON metabib.rec_descriptor (bib_level);
128 CREATE INDEX metabib_rec_descriptor_control_type_idx ON metabib.rec_descriptor (control_type);
129 CREATE INDEX metabib_rec_descriptor_char_encoding_idx ON metabib.rec_descriptor (char_encoding);
130 CREATE INDEX metabib_rec_descriptor_enc_level_idx ON metabib.rec_descriptor (enc_level);
131 CREATE INDEX metabib_rec_descriptor_audience_idx ON metabib.rec_descriptor (audience);
132 CREATE INDEX metabib_rec_descriptor_lit_form_idx ON metabib.rec_descriptor (lit_form);
133 CREATE INDEX metabib_rec_descriptor_cat_form_idx ON metabib.rec_descriptor (cat_form);
134 CREATE INDEX metabib_rec_descriptor_pub_status_idx ON metabib.rec_descriptor (pub_status);
135 CREATE INDEX metabib_rec_descriptor_item_lang_idx ON metabib.rec_descriptor (item_lang);
136 CREATE INDEX metabib_rec_descriptor_vr_format_idx ON metabib.rec_descriptor (vr_format);
140 -- Use a sequence that matches previous version, for easier upgrading.
141 CREATE SEQUENCE metabib.full_rec_id_seq;
143 CREATE TABLE metabib.real_full_rec (
144 id BIGINT NOT NULL DEFAULT NEXTVAL('metabib.full_rec_id_seq'::REGCLASS),
145 record BIGINT NOT NULL,
146 tag CHAR(3) NOT NULL,
151 index_vector tsvector NOT NULL
153 ALTER TABLE metabib.real_full_rec ADD PRIMARY KEY (id);
155 CREATE INDEX metabib_full_rec_tag_subfield_idx ON metabib.real_full_rec (tag,subfield);
156 CREATE INDEX metabib_full_rec_value_idx ON metabib.real_full_rec (substring(value,1,1024));
157 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
158 CREATE INDEX metabib_full_rec_value_tpo_index ON metabib.real_full_rec (substring(value,1,1024) text_pattern_ops);
159 CREATE INDEX metabib_full_rec_record_idx ON metabib.real_full_rec (record);
160 CREATE INDEX metabib_full_rec_index_vector_idx ON metabib.real_full_rec USING GIST (index_vector);
162 CREATE TRIGGER metabib_full_rec_fti_trigger
163 BEFORE UPDATE OR INSERT ON metabib.real_full_rec
164 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
166 CREATE OR REPLACE VIEW metabib.full_rec AS
173 SUBSTRING(value,1,1024) AS value,
175 FROM metabib.real_full_rec;
177 CREATE OR REPLACE RULE metabib_full_rec_insert_rule
178 AS ON INSERT TO metabib.full_rec
180 INSERT INTO metabib.real_full_rec VALUES (
181 COALESCE(NEW.id, NEXTVAL('metabib.full_rec_id_seq'::REGCLASS)),
191 CREATE OR REPLACE RULE metabib_full_rec_update_rule
192 AS ON UPDATE TO metabib.full_rec
194 UPDATE metabib.real_full_rec SET
200 subfield = NEW.subfield,
202 index_vector = NEW.index_vector
205 CREATE OR REPLACE RULE metabib_full_rec_delete_rule
206 AS ON DELETE TO metabib.full_rec
208 DELETE FROM metabib.real_full_rec WHERE id = OLD.id;
210 CREATE TABLE metabib.metarecord_source_map (
211 id BIGSERIAL PRIMARY KEY,
212 metarecord BIGINT NOT NULL,
213 source BIGINT NOT NULL
215 CREATE INDEX metabib_metarecord_source_map_metarecord_idx ON metabib.metarecord_source_map (metarecord);
216 CREATE INDEX metabib_metarecord_source_map_source_record_idx ON metabib.metarecord_source_map (source);
218 CREATE FUNCTION version_specific_xpath () RETURNS TEXT AS $wrapper_function$
223 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT < 8.3 THEN
224 out_text := 'Creating XPath functions that work like the native XPATH function in 8.3+';
226 EXECUTE $create_82_funcs$
228 CREATE OR REPLACE FUNCTION oils_xpath ( xpath TEXT, xml TEXT, ns ANYARRAY ) RETURNS TEXT[] AS $func$
235 munged_xpath := xpath;
237 IF ns IS NOT NULL THEN
238 FOR namespace IN 1 .. array_upper(ns, 1) LOOP
239 munged_xpath := REGEXP_REPLACE(
241 E'(' || ns[namespace][1] || E'):(\\w+)',
242 E'*[local-name() = "\\2" and namespace-uri() = "' || ns[namespace][2] || E'"]',
247 munged_xpath := REGEXP_REPLACE( munged_xpath, E'\\]\\[(\\D)',E' and \\1', 'g');
250 node_text := xpath_nodeset(xml, munged_xpath, 'XXX_OILS_NODESET');
251 node_text := REGEXP_REPLACE(node_text,'^<XXX_OILS_NODESET>', '');
252 node_text := REGEXP_REPLACE(node_text,'</XXX_OILS_NODESET>$', '');
254 RETURN STRING_TO_ARRAY(node_text, '</XXX_OILS_NODESET><XXX_OILS_NODESET>');
256 $func$ LANGUAGE PLPGSQL;
258 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT ) RETURNS TEXT[] AS 'SELECT oils_xpath( $1, $2, NULL::TEXT[] );' LANGUAGE SQL;
262 out_text := 'Creating XPath wrapper functions around the native XPATH function in 8.3+';
264 EXECUTE $create_83_funcs$
266 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT, ANYARRAY ) RETURNS TEXT[] AS 'SELECT XPATH( $1, $2::XML, $3 )::TEXT[];' LANGUAGE SQL;
267 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT ) RETURNS TEXT[] AS 'SELECT XPATH( $1, $2::XML )::TEXT[];' LANGUAGE SQL;
275 $wrapper_function$ LANGUAGE PLPGSQL;
277 SELECT version_specific_xpath();
278 DROP FUNCTION version_specific_xpath();
280 CREATE TYPE metabib.field_entry_template AS (
287 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( rid BIGINT, default_joiner TEXT ) RETURNS SETOF metabib.field_entry_template AS $func$
289 bib biblio.record_entry%ROWTYPE;
290 idx config.metabib_field%ROWTYPE;
291 xfrm config.xml_transform%ROWTYPE;
293 transformed_xml TEXT;
295 xml_node_list TEXT[];
297 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
298 output_row metabib.field_entry_template%ROWTYPE;
302 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
304 -- Loop over the indexing entries
305 FOR idx IN SELECT * FROM config.metabib_field ORDER BY format LOOP
307 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
309 -- See if we can skip the XSLT ... it's expensive
310 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
311 -- Can't skip the transform
312 IF xfrm.xslt <> '---' THEN
313 transformed_xml := xslt_process(bib.marc,xfrm.xslt);
315 transformed_xml := bib.marc;
318 prev_xfrm := xfrm.name;
321 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
324 FOR xml_node IN SELECT x FROM explode_array(xml_node_list) AS x LOOP
325 IF raw_text IS NOT NULL THEN
326 raw_text := raw_text || joiner;
328 raw_text := COALESCE(raw_text,'') || ARRAY_TO_STRING(oils_xpath( '//text()', xml_node ), ' ');
331 CONTINUE WHEN raw_text IS NULL;
333 output_row.field_class = idx.field_class;
334 output_row.field = idx.id;
335 output_row.source = rid;
336 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'gs'));
338 RETURN NEXT output_row;
343 $func$ LANGUAGE PLPGSQL;
345 -- default to a space joiner
346 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( BIGINT ) RETURNS SETOF metabib.field_entry_template AS $func$
347 SELECT * FROM biblio.extract_metabib_field_entry($1, ' ');