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);
74 CREATE TABLE metabib.keyword_field_entry (
75 id BIGSERIAL PRIMARY KEY,
76 source BIGINT NOT NULL,
79 index_vector tsvector NOT NULL
81 CREATE TRIGGER metabib_keyword_field_entry_fti_trigger
82 BEFORE UPDATE OR INSERT ON metabib.keyword_field_entry
83 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword');
85 CREATE INDEX metabib_keyword_field_entry_index_vector_idx ON metabib.keyword_field_entry USING GIST (index_vector);
88 CREATE TABLE metabib.series_field_entry (
89 id BIGSERIAL PRIMARY KEY,
90 source BIGINT NOT NULL,
93 index_vector tsvector NOT NULL
95 CREATE TRIGGER metabib_series_field_entry_fti_trigger
96 BEFORE UPDATE OR INSERT ON metabib.series_field_entry
97 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('series');
99 CREATE INDEX metabib_series_field_entry_index_vector_idx ON metabib.series_field_entry USING GIST (index_vector);
102 CREATE TABLE metabib.rec_descriptor (
103 id BIGSERIAL PRIMARY KEY,
119 CREATE INDEX metabib_rec_descriptor_record_idx ON metabib.rec_descriptor (record);
120 /* We may not need these...
122 CREATE INDEX metabib_rec_descriptor_item_type_idx ON metabib.rec_descriptor (item_type);
123 CREATE INDEX metabib_rec_descriptor_item_form_idx ON metabib.rec_descriptor (item_form);
124 CREATE INDEX metabib_rec_descriptor_bib_level_idx ON metabib.rec_descriptor (bib_level);
125 CREATE INDEX metabib_rec_descriptor_control_type_idx ON metabib.rec_descriptor (control_type);
126 CREATE INDEX metabib_rec_descriptor_char_encoding_idx ON metabib.rec_descriptor (char_encoding);
127 CREATE INDEX metabib_rec_descriptor_enc_level_idx ON metabib.rec_descriptor (enc_level);
128 CREATE INDEX metabib_rec_descriptor_audience_idx ON metabib.rec_descriptor (audience);
129 CREATE INDEX metabib_rec_descriptor_lit_form_idx ON metabib.rec_descriptor (lit_form);
130 CREATE INDEX metabib_rec_descriptor_cat_form_idx ON metabib.rec_descriptor (cat_form);
131 CREATE INDEX metabib_rec_descriptor_pub_status_idx ON metabib.rec_descriptor (pub_status);
132 CREATE INDEX metabib_rec_descriptor_item_lang_idx ON metabib.rec_descriptor (item_lang);
133 CREATE INDEX metabib_rec_descriptor_vr_format_idx ON metabib.rec_descriptor (vr_format);
138 CREATE TABLE metabib.full_rec (
139 id BIGSERIAL PRIMARY KEY,
140 record BIGINT NOT NULL,
141 tag CHAR(3) NOT NULL,
146 index_vector tsvector NOT NULL
148 CREATE INDEX metabib_full_rec_tag_subfield_idx ON metabib.full_rec (tag,subfield);
149 CREATE INDEX metabib_full_rec_value_idx ON metabib.full_rec (value);
150 CREATE INDEX metabib_full_rec_record_idx ON metabib.full_rec (record);
151 CREATE TRIGGER metabib_full_rec_fti_trigger
152 BEFORE UPDATE OR INSERT ON metabib.full_rec
153 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
155 CREATE INDEX metabib_full_rec_index_vector_idx ON metabib.full_rec USING GIST (index_vector);
158 CREATE TABLE metabib.metarecord_source_map (
159 id BIGSERIAL PRIMARY KEY,
160 metarecord BIGINT NOT NULL,
161 source BIGINT NOT NULL
163 CREATE INDEX metabib_metarecord_source_map_metarecord_idx ON metabib.metarecord_source_map (metarecord);
164 CREATE INDEX metabib_metarecord_source_map_source_record_idx ON metabib.metarecord_source_map (source);