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 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,
78 floating BOOL NOT NULL DEFAULT FALSE,
80 status_changed_time TIMESTAMP WITH TIME ZONE,
81 mint_condition BOOL NOT NULL DEFAULT TRUE,
84 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
85 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
86 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
87 CREATE INDEX cp_creator_idx ON asset.copy ( creator );
88 CREATE INDEX cp_editor_idx ON asset.copy ( editor );
89 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;
91 CREATE TABLE asset.opac_visible_copies (
92 id BIGINT primary key, -- copy id
96 COMMENT ON TABLE asset.opac_visible_copies IS $$
97 Materialized view of copies that are visible in the OPAC, used by
98 search.query_parser_fts() to speed up OPAC visibility checks on large
99 databases. Contents are maintained by a set of triggers.
101 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
103 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
104 RETURNS TRIGGER AS $$
106 IF NEW.status <> OLD.status THEN
107 NEW.status_changed_time := now();
113 CREATE TRIGGER acp_status_changed_trig
114 BEFORE UPDATE ON asset.copy
115 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
117 CREATE TABLE asset.stat_cat_entry_transparency_map (
118 id BIGSERIAL PRIMARY KEY,
119 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
120 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
121 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
122 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
125 CREATE TABLE asset.stat_cat (
126 id SERIAL PRIMARY KEY,
128 opac_visible BOOL NOT NULL DEFAULT FALSE,
130 required BOOL NOT NULL DEFAULT FALSE,
131 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
134 CREATE TABLE asset.stat_cat_entry (
135 id SERIAL PRIMARY KEY,
136 stat_cat INT NOT NULL,
139 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
142 CREATE TABLE asset.stat_cat_entry_copy_map (
143 id BIGSERIAL PRIMARY KEY,
144 stat_cat INT NOT NULL,
145 stat_cat_entry INT NOT NULL,
146 owning_copy BIGINT NOT NULL,
147 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
149 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
151 CREATE TABLE asset.copy_note (
152 id BIGSERIAL PRIMARY KEY,
153 owning_copy BIGINT NOT NULL,
154 creator BIGINT NOT NULL,
155 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
156 pub BOOL NOT NULL DEFAULT FALSE,
160 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
162 CREATE TABLE asset.uri (
163 id SERIAL PRIMARY KEY,
166 use_restriction TEXT,
167 active BOOL NOT NULL DEFAULT TRUE
170 CREATE TABLE asset.call_number (
171 id bigserial PRIMARY KEY,
172 creator BIGINT NOT NULL,
173 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
174 editor BIGINT NOT NULL,
175 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
176 record bigint NOT NULL,
177 owning_lib INT NOT NULL,
179 deleted BOOL NOT NULL DEFAULT FALSE
181 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
182 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
183 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
184 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
185 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (upper(label),id,owning_lib);
186 CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted = FALSE OR deleted IS FALSE;
187 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;
189 CREATE TABLE asset.uri_call_number_map (
190 id BIGSERIAL PRIMARY KEY,
191 uri INT NOT NULL REFERENCES asset.uri (id),
192 call_number INT NOT NULL REFERENCES asset.call_number (id),
193 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
195 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
197 CREATE TABLE asset.call_number_note (
198 id BIGSERIAL PRIMARY KEY,
199 call_number BIGINT NOT NULL,
200 creator BIGINT NOT NULL,
201 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
202 pub BOOL NOT NULL DEFAULT FALSE,
206 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
208 CREATE TABLE asset.copy_template (
209 id SERIAL PRIMARY KEY,
210 owning_lib INT NOT NULL
211 REFERENCES actor.org_unit (id)
212 DEFERRABLE INITIALLY DEFERRED,
213 creator BIGINT NOT NULL
214 REFERENCES actor.usr (id)
215 DEFERRABLE INITIALLY DEFERRED,
216 editor BIGINT NOT NULL
217 REFERENCES actor.usr (id)
218 DEFERRABLE INITIALLY DEFERRED,
219 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
220 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
222 -- columns above this point are attributes of the template itself
223 -- columns after this point are attributes of the copy this template modifies/creates
224 circ_lib INT REFERENCES actor.org_unit (id)
225 DEFERRABLE INITIALLY DEFERRED,
226 status INT REFERENCES config.copy_status (id)
227 DEFERRABLE INITIALLY DEFERRED,
228 location INT REFERENCES asset.copy_location (id)
229 DEFERRABLE INITIALLY DEFERRED,
230 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
231 loan_duration IS NULL OR loan_duration IN (1,2,3)),
232 fine_level INT CONSTRAINT valid_fine_level CHECK (
233 fine_level IS NULL OR loan_duration IN (1,2,3)),
239 deposit_amount NUMERIC(6,2),
249 CREATE VIEW stats.fleshed_copy AS
251 CAST(cp.create_date AS DATE) AS create_date_day,
252 CAST(cp.edit_date AS DATE) AS edit_date_day,
253 DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
254 DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
255 cn.label AS call_number_label,
261 JOIN asset.call_number cn ON (cp.call_number = cn.id)
262 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
264 CREATE VIEW stats.fleshed_call_number AS
266 CAST(cn.create_date AS DATE) AS create_date_day,
267 CAST(cn.edit_date AS DATE) AS edit_date_day,
268 DATE_TRUNC('hour', cn.create_date) AS create_date_hour,
269 DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour,
273 FROM asset.call_number cn
274 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);