1 DROP SCHEMA authority CASCADE;
4 CREATE SCHEMA authority;
6 CREATE TABLE authority.record_entry (
7 id BIGSERIAL PRIMARY KEY,
8 arn_source TEXT NOT NULL DEFAULT 'AUTOGEN',
9 arn_value TEXT NOT NULL,
10 creator INT NOT NULL DEFAULT 1,
11 editor INT NOT NULL DEFAULT 1,
12 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
13 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
14 active BOOL NOT NULL DEFAULT TRUE,
15 deleted BOOL NOT NULL DEFAULT FALSE,
18 last_xact_id TEXT NOT NULL
20 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
21 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
22 CREATE UNIQUE INDEX authority_record_unique_tcn ON authority.record_entry (arn_source,arn_value) WHERE deleted IS FALSE;
24 CREATE TABLE authority.record_note (
25 id BIGSERIAL PRIMARY KEY,
26 record BIGINT NOT NULL REFERENCES authority.record_entry (id),
28 creator INT NOT NULL DEFAULT 1,
29 editor INT NOT NULL DEFAULT 1,
30 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
31 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
33 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
34 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
35 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
37 CREATE TABLE authority.rec_descriptor (
38 id BIGSERIAL PRIMARY KEY,
43 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
45 CREATE TABLE authority.full_rec (
46 id BIGSERIAL PRIMARY KEY,
47 record BIGINT NOT NULL,
53 index_vector tsvector NOT NULL
55 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
56 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
57 CREATE TRIGGER authority_full_rec_fti_trigger
58 BEFORE UPDATE OR INSERT ON authority.full_rec
59 FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
61 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);