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 asset CASCADE;
24 CREATE TABLE asset.copy_location (
25 id SERIAL PRIMARY KEY,
27 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
28 holdable BOOL NOT NULL DEFAULT TRUE,
29 hold_verify BOOL NOT NULL DEFAULT FALSE,
30 opac_visible BOOL NOT NULL DEFAULT TRUE,
31 circulate BOOL NOT NULL DEFAULT TRUE,
32 CONSTRAINT acl_name_once_per_lib UNIQUE (name, owning_lib)
35 CREATE TABLE asset.copy_location_order
37 id SERIAL PRIMARY KEY,
39 REFERENCES asset.copy_location
41 DEFERRABLE INITIALLY DEFERRED,
43 REFERENCES actor.org_unit
45 DEFERRABLE INITIALLY DEFERRED,
46 position INT NOT NULL DEFAULT 0,
47 CONSTRAINT acplo_once_per_org UNIQUE ( location, org )
50 CREATE TABLE asset.copy (
51 id BIGSERIAL PRIMARY KEY,
52 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
53 creator BIGINT NOT NULL,
54 call_number BIGINT NOT NULL,
55 editor BIGINT NOT NULL,
56 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
57 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
59 status INT NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
60 location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
61 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
62 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
64 circulate BOOL NOT NULL DEFAULT TRUE,
65 deposit BOOL NOT NULL DEFAULT FALSE,
66 ref BOOL NOT NULL DEFAULT FALSE,
67 holdable BOOL NOT NULL DEFAULT TRUE,
68 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
70 barcode TEXT NOT NULL,
76 opac_visible BOOL NOT NULL DEFAULT TRUE,
77 deleted BOOL NOT NULL DEFAULT FALSE,
79 status_changed_time TIMESTAMP WITH TIME ZONE,
80 mint_condition BOOL NOT NULL DEFAULT TRUE
82 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted IS FALSE;
83 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
84 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
85 CREATE INDEX cp_creator_idx ON asset.copy ( creator );
86 CREATE INDEX cp_editor_idx ON asset.copy ( editor );
87 CREATE RULE protect_copy_delete AS ON DELETE TO asset.copy DO INSTEAD UPDATE asset.copy SET deleted = TRUE WHERE OLD.id = asset.copy.id;
89 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
92 IF NEW.status <> OLD.status THEN
93 NEW.status_changed_time := now();
99 CREATE TRIGGER acp_status_changed_trig
100 BEFORE UPDATE ON asset.copy
101 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
103 CREATE TABLE asset.copy_transparency (
104 id SERIAL PRIMARY KEY,
105 deposit_amount NUMERIC(6,2),
106 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
107 circ_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
108 loan_duration INT CHECK ( loan_duration IN (1,2,3) ),
109 fine_level INT CHECK ( fine_level IN (1,2,3) ),
118 CONSTRAINT scte_name_once_per_lib UNIQUE (owner,name)
121 CREATE TABLE asset.copy_transparency_map (
122 id BIGSERIAL PRIMARY KEY,
123 transparency INT NOT NULL REFERENCES asset.copy_transparency (id) DEFERRABLE INITIALLY DEFERRED,
124 target_copy INT NOT NULL UNIQUE REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED
126 CREATE INDEX cp_tr_cp_idx ON asset.copy_transparency_map (transparency);
128 CREATE TABLE asset.stat_cat_entry_transparency_map (
129 id BIGSERIAL PRIMARY KEY,
130 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
131 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
132 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
133 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
136 CREATE TABLE asset.stat_cat (
137 id SERIAL PRIMARY KEY,
139 opac_visible BOOL NOT NULL DEFAULT FALSE,
141 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
144 CREATE TABLE asset.stat_cat_entry (
145 id SERIAL PRIMARY KEY,
146 stat_cat INT NOT NULL,
149 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
152 CREATE TABLE asset.stat_cat_entry_copy_map (
153 id BIGSERIAL PRIMARY KEY,
154 stat_cat INT NOT NULL,
155 stat_cat_entry INT NOT NULL,
156 owning_copy BIGINT NOT NULL,
157 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
160 CREATE TABLE asset.copy_note (
161 id BIGSERIAL PRIMARY KEY,
162 owning_copy BIGINT NOT NULL,
163 creator BIGINT NOT NULL,
164 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
165 pub BOOL NOT NULL DEFAULT FALSE,
169 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
171 CREATE TABLE asset.uri (
172 id SERIAL PRIMARY KEY,
175 use_restriction TEXT,
176 active BOOL NOT NULL DEFAULT TRUE
179 CREATE TABLE asset.call_number (
180 id bigserial PRIMARY KEY,
181 creator BIGINT NOT NULL,
182 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
183 editor BIGINT NOT NULL,
184 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
185 record bigint NOT NULL,
186 owning_lib INT NOT NULL,
188 deleted BOOL NOT NULL DEFAULT FALSE
190 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
191 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
192 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
193 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
194 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (upper(label),id,owning_lib);
195 CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted IS FALSE;
196 CREATE RULE protect_cn_delete AS ON DELETE TO asset.call_number DO INSTEAD UPDATE asset.call_number SET deleted = TRUE WHERE OLD.id = asset.call_number.id;
198 CREATE TABLE asset.uri_call_number_map (
199 id BIGSERIAL PRIMARY KEY,
200 uri INT NOT NULL REFERENCES asset.uri (id),
201 call_number INT NOT NULL REFERENCES asset.call_number (id),
202 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
204 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
206 CREATE TABLE asset.call_number_note (
207 id BIGSERIAL PRIMARY KEY,
208 call_number BIGINT NOT NULL,
209 creator BIGINT NOT NULL,
210 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
211 pub BOOL NOT NULL DEFAULT FALSE,
215 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
217 CREATE VIEW stats.fleshed_copy AS
219 CAST(cp.create_date AS DATE) AS create_date_day,
220 CAST(cp.edit_date AS DATE) AS edit_date_day,
221 DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
222 DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
223 cn.label AS call_number_label,
229 JOIN asset.call_number cn ON (cp.call_number = cn.id)
230 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
232 CREATE VIEW stats.fleshed_call_number AS
234 CAST(cn.create_date AS DATE) AS create_date_day,
235 CAST(cn.edit_date AS DATE) AS edit_date_day,
236 DATE_TRUNC('hour', cn.create_date) AS create_date_hour,
237 DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour,
241 FROM asset.call_number cn
242 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);