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 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
41 CONSTRAINT cb_name_once_per_owner UNIQUE (owner,name,btype)
44 CREATE TABLE container.copy_bucket_note (
45 id SERIAL PRIMARY KEY,
46 bucket INT NOT NULL REFERENCES container.copy_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
50 CREATE TABLE container.copy_bucket_item (
51 id SERIAL PRIMARY KEY,
53 REFERENCES container.copy_bucket (id)
58 target_copy INT NOT NULL
59 REFERENCES asset."copy" (id)
65 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
67 CREATE INDEX copy_bucket_item_bucket_idx ON container.copy_bucket_item (bucket);
69 CREATE TABLE container.copy_bucket_item_note (
70 id SERIAL PRIMARY KEY,
71 item INT NOT NULL REFERENCES container.copy_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
77 CREATE TABLE container.call_number_bucket_type (
78 code TEXT PRIMARY KEY,
79 label TEXT NOT NULL UNIQUE
82 CREATE TABLE container.call_number_bucket (
83 id SERIAL PRIMARY KEY,
85 REFERENCES actor.usr (id)
91 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.call_number_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
93 pub BOOL NOT NULL DEFAULT FALSE,
94 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
95 CONSTRAINT cnb_name_once_per_owner UNIQUE (owner,name,btype)
98 CREATE TABLE container.call_number_bucket_note (
99 id SERIAL PRIMARY KEY,
100 bucket INT NOT NULL REFERENCES container.call_number_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
104 CREATE TABLE container.call_number_bucket_item (
105 id SERIAL PRIMARY KEY,
107 REFERENCES container.call_number_bucket (id)
112 target_call_number INT NOT NULL
113 REFERENCES asset.call_number (id)
119 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
122 CREATE TABLE container.call_number_bucket_item_note (
123 id SERIAL PRIMARY KEY,
124 item INT NOT NULL REFERENCES container.call_number_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
131 CREATE TABLE container.biblio_record_entry_bucket_type (
132 code TEXT PRIMARY KEY,
133 label TEXT NOT NULL UNIQUE
137 CREATE TABLE container.biblio_record_entry_bucket (
138 id SERIAL PRIMARY KEY,
140 REFERENCES actor.usr (id)
146 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.biblio_record_entry_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
148 pub BOOL NOT NULL DEFAULT FALSE,
149 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
150 CONSTRAINT breb_name_once_per_owner UNIQUE (owner,name,btype)
153 CREATE TABLE container.biblio_record_entry_bucket_note (
154 id SERIAL PRIMARY KEY,
155 bucket INT NOT NULL REFERENCES container.biblio_record_entry_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
159 CREATE TABLE container.biblio_record_entry_bucket_item (
160 id SERIAL PRIMARY KEY,
162 REFERENCES container.biblio_record_entry_bucket (id)
167 target_biblio_record_entry BIGINT NOT NULL
168 REFERENCES biblio.record_entry (id)
174 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
177 CREATE TABLE container.biblio_record_entry_bucket_item_note (
178 id SERIAL PRIMARY KEY,
179 item INT NOT NULL REFERENCES container.biblio_record_entry_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
185 CREATE TABLE container.user_bucket_type (
186 code TEXT PRIMARY KEY,
187 label TEXT NOT NULL UNIQUE
190 CREATE TABLE container.user_bucket (
191 id SERIAL PRIMARY KEY,
193 REFERENCES actor.usr (id)
199 btype TEXT NOT NULL DEFAULT 'misc' REFERENCES container.user_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
201 pub BOOL NOT NULL DEFAULT FALSE,
202 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
203 CONSTRAINT ub_name_once_per_owner UNIQUE (owner,name,btype)
206 CREATE TABLE container.user_bucket_note (
207 id SERIAL PRIMARY KEY,
208 bucket INT NOT NULL REFERENCES container.user_bucket (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
212 CREATE TABLE container.user_bucket_item (
213 id SERIAL PRIMARY KEY,
215 REFERENCES container.user_bucket (id)
220 target_user INT NOT NULL
221 REFERENCES actor.usr (id)
227 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
229 CREATE INDEX user_bucket_item_target_user_idx ON container.user_bucket_item ( target_user );
231 CREATE TABLE container.user_bucket_item_note (
232 id SERIAL PRIMARY KEY,
233 item INT NOT NULL REFERENCES container.user_bucket_item (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,