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
60 REFERENCES asset."copy" (id)
66 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
68 CREATE INDEX copy_bucket_item_bucket_idx ON container.copy_bucket_item (bucket);
70 CREATE TABLE container.copy_bucket_item_note (
71 id SERIAL PRIMARY KEY,
72 item INT NOT NULL REFERENCES container.copy_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
78 CREATE TABLE container.call_number_bucket_type (
79 code TEXT PRIMARY KEY,
80 label TEXT NOT NULL UNIQUE
83 CREATE TABLE container.call_number_bucket (
84 id SERIAL PRIMARY KEY,
86 REFERENCES actor.usr (id)
92 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.call_number_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
94 pub BOOL NOT NULL DEFAULT FALSE,
95 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
96 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
97 CONSTRAINT cnb_name_once_per_owner UNIQUE (owner,name,btype)
100 CREATE TABLE container.call_number_bucket_note (
101 id SERIAL PRIMARY KEY,
102 bucket INT NOT NULL REFERENCES container.call_number_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
106 CREATE TABLE container.call_number_bucket_item (
107 id SERIAL PRIMARY KEY,
109 REFERENCES container.call_number_bucket (id)
114 target_call_number INT NOT NULL
115 REFERENCES asset.call_number (id)
121 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
124 CREATE TABLE container.call_number_bucket_item_note (
125 id SERIAL PRIMARY KEY,
126 item INT NOT NULL REFERENCES container.call_number_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
133 CREATE TABLE container.biblio_record_entry_bucket_type (
134 code TEXT PRIMARY KEY,
135 label TEXT NOT NULL UNIQUE
139 CREATE TABLE container.biblio_record_entry_bucket (
140 id SERIAL PRIMARY KEY,
142 REFERENCES actor.usr (id)
148 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.biblio_record_entry_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
150 pub BOOL NOT NULL DEFAULT FALSE,
151 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
152 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
153 CONSTRAINT breb_name_once_per_owner UNIQUE (owner,name,btype)
156 CREATE TABLE container.biblio_record_entry_bucket_note (
157 id SERIAL PRIMARY KEY,
158 bucket INT NOT NULL REFERENCES container.biblio_record_entry_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
162 CREATE TABLE container.biblio_record_entry_bucket_item (
163 id SERIAL PRIMARY KEY,
165 REFERENCES container.biblio_record_entry_bucket (id)
170 target_biblio_record_entry BIGINT NOT NULL
171 REFERENCES biblio.record_entry (id)
177 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
180 CREATE TABLE container.biblio_record_entry_bucket_item_note (
181 id SERIAL PRIMARY KEY,
182 item INT NOT NULL REFERENCES container.biblio_record_entry_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
188 CREATE TABLE container.user_bucket_type (
189 code TEXT PRIMARY KEY,
190 label TEXT NOT NULL UNIQUE
193 CREATE TABLE container.user_bucket (
194 id SERIAL PRIMARY KEY,
196 REFERENCES actor.usr (id)
202 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.user_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
204 pub BOOL NOT NULL DEFAULT FALSE,
205 owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
206 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
207 CONSTRAINT ub_name_once_per_owner UNIQUE (owner,name,btype)
210 CREATE TABLE container.user_bucket_note (
211 id SERIAL PRIMARY KEY,
212 bucket INT NOT NULL REFERENCES container.user_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
216 CREATE TABLE container.user_bucket_item (
217 id SERIAL PRIMARY KEY,
219 REFERENCES container.user_bucket (id)
224 target_user INT NOT NULL
225 REFERENCES actor.usr (id)
231 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
233 CREATE INDEX user_bucket_item_target_user_idx ON container.user_bucket_item ( target_user );
235 CREATE TABLE container.user_bucket_item_note (
236 id SERIAL PRIMARY KEY,
237 item INT NOT NULL REFERENCES container.user_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,