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,
38 pub BOOL NOT NULL DEFAULT FALSE,
39 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
40 CONSTRAINT cb_name_once_per_owner UNIQUE (owner,name,btype)
43 CREATE TABLE container.copy_bucket_note (
44 id SERIAL PRIMARY KEY,
45 bucket INT NOT NULL REFERENCES container.copy_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
49 CREATE TABLE container.copy_bucket_item (
50 id SERIAL PRIMARY KEY,
52 REFERENCES container.copy_bucket (id)
57 target_copy INT NOT NULL
58 REFERENCES asset."copy" (id)
64 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
66 CREATE INDEX copy_bucket_item_bucket_idx ON container.copy_bucket_item (bucket);
68 CREATE TABLE container.copy_bucket_item_note (
69 id SERIAL PRIMARY KEY,
70 item INT NOT NULL REFERENCES container.copy_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
76 CREATE TABLE container.call_number_bucket_type (
77 code TEXT PRIMARY KEY,
78 label TEXT NOT NULL UNIQUE
81 CREATE TABLE container.call_number_bucket (
82 id SERIAL PRIMARY KEY,
84 REFERENCES actor.usr (id)
90 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.call_number_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
91 pub BOOL NOT NULL DEFAULT FALSE,
92 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
93 CONSTRAINT cnb_name_once_per_owner UNIQUE (owner,name,btype)
96 CREATE TABLE container.call_number_bucket_note (
97 id SERIAL PRIMARY KEY,
98 bucket INT NOT NULL REFERENCES container.call_number_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
102 CREATE TABLE container.call_number_bucket_item (
103 id SERIAL PRIMARY KEY,
105 REFERENCES container.call_number_bucket (id)
110 target_call_number INT NOT NULL
111 REFERENCES asset.call_number (id)
117 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
120 CREATE TABLE container.call_number_bucket_item_note (
121 id SERIAL PRIMARY KEY,
122 item INT NOT NULL REFERENCES container.call_number_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
129 CREATE TABLE container.biblio_record_entry_bucket_type (
130 code TEXT PRIMARY KEY,
131 label TEXT NOT NULL UNIQUE
135 CREATE TABLE container.biblio_record_entry_bucket (
136 id SERIAL PRIMARY KEY,
138 REFERENCES actor.usr (id)
144 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.biblio_record_entry_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
145 pub BOOL NOT NULL DEFAULT FALSE,
146 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
147 CONSTRAINT breb_name_once_per_owner UNIQUE (owner,name,btype)
150 CREATE TABLE container.biblio_record_entry_bucket_note (
151 id SERIAL PRIMARY KEY,
152 bucket INT NOT NULL REFERENCES container.biblio_record_entry_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
156 CREATE TABLE container.biblio_record_entry_bucket_item (
157 id SERIAL PRIMARY KEY,
159 REFERENCES container.biblio_record_entry_bucket (id)
164 target_biblio_record_entry BIGINT NOT NULL
165 REFERENCES biblio.record_entry (id)
171 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
174 CREATE TABLE container.biblio_record_entry_bucket_item_note (
175 id SERIAL PRIMARY KEY,
176 item INT NOT NULL REFERENCES container.biblio_record_entry_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
182 CREATE TABLE container.user_bucket_type (
183 code TEXT PRIMARY KEY,
184 label TEXT NOT NULL UNIQUE
187 CREATE TABLE container.user_bucket (
188 id SERIAL PRIMARY KEY,
190 REFERENCES actor.usr (id)
196 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.user_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
197 pub BOOL NOT NULL DEFAULT FALSE,
198 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
199 CONSTRAINT ub_name_once_per_owner UNIQUE (owner,name,btype)
202 CREATE TABLE container.user_bucket_note (
203 id SERIAL PRIMARY KEY,
204 bucket INT NOT NULL REFERENCES container.user_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
208 CREATE TABLE container.user_bucket_item (
209 id SERIAL PRIMARY KEY,
211 REFERENCES container.user_bucket (id)
216 target_user INT NOT NULL
217 REFERENCES actor.usr (id)
223 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
225 CREATE INDEX user_bucket_item_target_user_idx ON container.user_bucket_item ( target_user );
227 CREATE TABLE container.user_bucket_item_note (
228 id SERIAL PRIMARY KEY,
229 item INT NOT NULL REFERENCES container.user_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,