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 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()
67 CREATE TABLE container.copy_bucket_item_note (
68 id SERIAL PRIMARY KEY,
69 item INT NOT NULL REFERENCES container.copy_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
75 CREATE TABLE container.call_number_bucket_type (
76 code TEXT PRIMARY KEY,
77 label TEXT NOT NULL UNIQUE
80 CREATE TABLE container.call_number_bucket (
81 id SERIAL PRIMARY KEY,
83 REFERENCES actor.usr (id)
89 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.call_number_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
90 pub BOOL NOT NULL DEFAULT FALSE,
91 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
92 CONSTRAINT cnb_name_once_per_owner UNIQUE (owner,name,btype)
95 CREATE TABLE container.call_number_bucket_note (
96 id SERIAL PRIMARY KEY,
97 bucket INT NOT NULL REFERENCES container.call_number_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
101 CREATE TABLE container.call_number_bucket_item (
102 id SERIAL PRIMARY KEY,
104 REFERENCES container.call_number_bucket (id)
109 target_call_number INT NOT NULL
110 REFERENCES asset.call_number (id)
116 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
119 CREATE TABLE container.call_number_bucket_item_note (
120 id SERIAL PRIMARY KEY,
121 item INT NOT NULL REFERENCES container.call_number_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
128 CREATE TABLE container.biblio_record_entry_bucket_type (
129 code TEXT PRIMARY KEY,
130 label TEXT NOT NULL UNIQUE
134 CREATE TABLE container.biblio_record_entry_bucket (
135 id SERIAL PRIMARY KEY,
137 REFERENCES actor.usr (id)
143 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.biblio_record_entry_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
144 pub BOOL NOT NULL DEFAULT FALSE,
145 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
146 CONSTRAINT breb_name_once_per_owner UNIQUE (owner,name,btype)
149 CREATE TABLE container.biblio_record_entry_bucket_note (
150 id SERIAL PRIMARY KEY,
151 bucket INT NOT NULL REFERENCES container.biblio_record_entry_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
155 CREATE TABLE container.biblio_record_entry_bucket_item (
156 id SERIAL PRIMARY KEY,
158 REFERENCES container.biblio_record_entry_bucket (id)
163 target_biblio_record_entry INT NOT NULL
164 REFERENCES biblio.record_entry (id)
170 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
173 CREATE TABLE container.biblio_record_entry_bucket_item_note (
174 id SERIAL PRIMARY KEY,
175 item INT NOT NULL REFERENCES container.biblio_record_entry_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
181 CREATE TABLE container.user_bucket_type (
182 code TEXT PRIMARY KEY,
183 label TEXT NOT NULL UNIQUE
186 CREATE TABLE container.user_bucket (
187 id SERIAL PRIMARY KEY,
189 REFERENCES actor.usr (id)
195 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.user_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
196 pub BOOL NOT NULL DEFAULT FALSE,
197 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
198 CONSTRAINT ub_name_once_per_owner UNIQUE (owner,name,btype)
201 CREATE TABLE container.user_bucket_note (
202 id SERIAL PRIMARY KEY,
203 bucket INT NOT NULL REFERENCES container.user_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
207 CREATE TABLE container.user_bucket_item (
208 id SERIAL PRIMARY KEY,
210 REFERENCES container.user_bucket (id)
215 target_user INT NOT NULL
216 REFERENCES actor.usr (id)
222 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
225 CREATE TABLE container.user_bucket_item_note (
226 id SERIAL PRIMARY KEY,
227 item INT NOT NULL REFERENCES container.user_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,