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 IF EXISTS container CASCADE;
21 CREATE SCHEMA container;
23 CREATE TABLE container.copy_bucket_type (
24 code TEXT PRIMARY KEY,
25 label TEXT NOT NULL UNIQUE
28 CREATE TABLE container.copy_bucket (
29 id SERIAL PRIMARY KEY,
31 REFERENCES actor.usr (id)
37 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.copy_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
39 pub BOOL NOT NULL DEFAULT FALSE,
40 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
41 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
42 CONSTRAINT cb_name_once_per_owner UNIQUE (owner,name,btype)
45 CREATE TABLE container.copy_bucket_note (
46 id SERIAL PRIMARY KEY,
47 bucket INT NOT NULL REFERENCES container.copy_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
51 CREATE TABLE container.copy_bucket_item (
52 id SERIAL PRIMARY KEY,
54 REFERENCES container.copy_bucket (id)
59 target_copy INT NOT NULL,
61 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
63 CREATE INDEX copy_bucket_item_bucket_idx ON container.copy_bucket_item (bucket);
65 CREATE OR REPLACE FUNCTION evergreen.container_copy_bucket_item_target_copy_inh_fkey() RETURNS TRIGGER AS $f$
67 PERFORM 1 FROM asset.copy WHERE id = NEW.target_copy;
69 RAISE foreign_key_violation USING MESSAGE = FORMAT(
70 $$Referenced asset.copy id not found, target_copy:%s$$, NEW.target_copy
75 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
77 CREATE CONSTRAINT TRIGGER inherit_copy_bucket_item_target_copy_fkey
78 AFTER UPDATE OR INSERT ON container.copy_bucket_item
79 DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.container_copy_bucket_item_target_copy_inh_fkey();
82 CREATE TABLE container.copy_bucket_item_note (
83 id SERIAL PRIMARY KEY,
84 item INT NOT NULL REFERENCES container.copy_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
90 CREATE TABLE container.call_number_bucket_type (
91 code TEXT PRIMARY KEY,
92 label TEXT NOT NULL UNIQUE
95 CREATE TABLE container.call_number_bucket (
96 id SERIAL PRIMARY KEY,
98 REFERENCES actor.usr (id)
104 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.call_number_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
106 pub BOOL NOT NULL DEFAULT FALSE,
107 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
108 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
109 CONSTRAINT cnb_name_once_per_owner UNIQUE (owner,name,btype)
112 CREATE TABLE container.call_number_bucket_note (
113 id SERIAL PRIMARY KEY,
114 bucket INT NOT NULL REFERENCES container.call_number_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
118 CREATE TABLE container.call_number_bucket_item (
119 id SERIAL PRIMARY KEY,
121 REFERENCES container.call_number_bucket (id)
126 target_call_number INT NOT NULL
127 REFERENCES asset.call_number (id)
133 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
136 CREATE TABLE container.call_number_bucket_item_note (
137 id SERIAL PRIMARY KEY,
138 item INT NOT NULL REFERENCES container.call_number_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
145 CREATE TABLE container.biblio_record_entry_bucket_type (
146 code TEXT PRIMARY KEY,
147 label TEXT NOT NULL UNIQUE
151 CREATE TABLE container.biblio_record_entry_bucket (
152 id SERIAL PRIMARY KEY,
154 REFERENCES actor.usr (id)
160 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.biblio_record_entry_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
162 pub BOOL NOT NULL DEFAULT FALSE,
163 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
164 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
165 CONSTRAINT breb_name_once_per_owner UNIQUE (owner,name,btype)
168 CREATE TABLE container.biblio_record_entry_bucket_note (
169 id SERIAL PRIMARY KEY,
170 bucket INT NOT NULL REFERENCES container.biblio_record_entry_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
174 CREATE TABLE container.biblio_record_entry_bucket_item (
175 id SERIAL PRIMARY KEY,
177 REFERENCES container.biblio_record_entry_bucket (id)
182 target_biblio_record_entry BIGINT NOT NULL
183 REFERENCES biblio.record_entry (id)
189 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
192 CREATE TABLE container.biblio_record_entry_bucket_item_note (
193 id SERIAL PRIMARY KEY,
194 item INT NOT NULL REFERENCES container.biblio_record_entry_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
200 CREATE TABLE container.user_bucket_type (
201 code TEXT PRIMARY KEY,
202 label TEXT NOT NULL UNIQUE
205 CREATE TABLE container.user_bucket (
206 id SERIAL PRIMARY KEY,
208 REFERENCES actor.usr (id)
214 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.user_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
216 pub BOOL NOT NULL DEFAULT FALSE,
217 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
218 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
219 CONSTRAINT ub_name_once_per_owner UNIQUE (owner,name,btype)
222 CREATE TABLE container.user_bucket_note (
223 id SERIAL PRIMARY KEY,
224 bucket INT NOT NULL REFERENCES container.user_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
228 CREATE TABLE container.user_bucket_item (
229 id SERIAL PRIMARY KEY,
231 REFERENCES container.user_bucket (id)
236 target_user INT NOT NULL
237 REFERENCES actor.usr (id)
243 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
245 CREATE INDEX user_bucket_item_target_user_idx ON container.user_bucket_item ( target_user );
247 CREATE TABLE container.user_bucket_item_note (
248 id SERIAL PRIMARY KEY,
249 item INT NOT NULL REFERENCES container.user_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,