From 27cc3cf06bb001f19cc8e4d4713e5f46c9f296bf Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 30 Aug 2005 14:52:06 +0000 Subject: [PATCH] adding initial authority schema git-svn-id: svn://svn.open-ils.org/ILS/trunk@1768 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/011.schema.authority.sql | 63 ++++++++++++++++++++ 1 file changed, 63 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/011.schema.authority.sql diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql new file mode 100644 index 0000000000..cc64c08fb5 --- /dev/null +++ b/Open-ILS/src/sql/Pg/011.schema.authority.sql @@ -0,0 +1,63 @@ +DROP SCHEMA authority CASCADE; + +BEGIN; +CREATE SCHEMA authority; + +CREATE TABLE authority.record_entry ( + id BIGSERIAL PRIMARY KEY, + arn_source TEXT NOT NULL DEFAULT 'AUTOGEN', + arn_value TEXT NOT NULL, + creator INT NOT NULL DEFAULT 1, + editor INT NOT NULL DEFAULT 1, + create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), + edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), + active BOOL NOT NULL DEFAULT TRUE, + deleted BOOL NOT NULL DEFAULT FALSE, + source INT, + marc TEXT NOT NULL, + last_xact_id TEXT NOT NULL +); +CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator ); +CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor ); +CREATE UNIQUE INDEX authority_record_unique_tcn ON authority.record_entry (arn_source,arn_value) WHERE deleted IS FALSE; + +CREATE TABLE authority.record_note ( + id BIGSERIAL PRIMARY KEY, + record BIGINT NOT NULL REFERENCES authority.record_entry (id), + value TEXT NOT NULL, + creator INT NOT NULL DEFAULT 1, + editor INT NOT NULL DEFAULT 1, + create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), + edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now() +); +CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record ); +CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator ); +CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor ); + +CREATE TABLE authority.rec_descriptor ( + id BIGSERIAL PRIMARY KEY, + record BIGINT, + record_status "char", + char_encoding "char" +); +CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record); + +CREATE TABLE authority.full_rec ( + id BIGSERIAL PRIMARY KEY, + record BIGINT NOT NULL, + tag CHAR(3) NOT NULL, + ind1 "char", + ind2 "char", + subfield "char", + value TEXT NOT NULL, + index_vector tsvector NOT NULL +); +CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record); +CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2)); +CREATE TRIGGER authority_full_rec_fti_trigger + BEFORE UPDATE OR INSERT ON authority.full_rec + FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); + +CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector); + +COMMIT; -- 2.43.2