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 (
36 id BIGSERIAL PRIMARY KEY,
37 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
38 creator BIGINT NOT NULL,
39 call_number BIGINT NOT NULL,
40 editor BIGINT NOT NULL,
41 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
42 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
44 status INT NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
45 location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
46 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
47 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
49 circulate BOOL NOT NULL DEFAULT TRUE,
50 deposit BOOL NOT NULL DEFAULT FALSE,
51 ref BOOL NOT NULL DEFAULT FALSE,
52 holdable BOOL NOT NULL DEFAULT TRUE,
53 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
55 barcode TEXT NOT NULL,
61 opac_visible BOOL NOT NULL DEFAULT TRUE,
62 deleted BOOL NOT NULL DEFAULT FALSE,
65 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted IS FALSE;
66 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
67 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
68 CREATE INDEX cp_creator_idx ON asset.copy ( creator );
69 CREATE INDEX cp_editor_idx ON asset.copy ( editor );
70 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;
72 CREATE TABLE asset.copy_transparency (
73 id SERIAL PRIMARY KEY,
74 deposit_amount NUMERIC(6,2),
75 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
76 circ_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
77 loan_duration INT CHECK ( loan_duration IN (1,2,3) ),
78 fine_level INT CHECK ( fine_level IN (1,2,3) ),
87 CONSTRAINT scte_name_once_per_lib UNIQUE (owner,name)
90 CREATE TABLE asset.copy_transparency_map (
91 id BIGSERIAL PRIMARY KEY,
92 transparency INT NOT NULL REFERENCES asset.copy_transparency (id) DEFERRABLE INITIALLY DEFERRED,
93 target_copy INT NOT NULL UNIQUE REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED
95 CREATE INDEX cp_tr_cp_idx ON asset.copy_transparency_map (transparency);
97 CREATE TABLE asset.stat_cat_entry_transparency_map (
98 id BIGSERIAL PRIMARY KEY,
99 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
100 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
101 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
102 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
105 CREATE TABLE asset.stat_cat (
106 id SERIAL PRIMARY KEY,
108 opac_visible BOOL NOT NULL DEFAULT FALSE,
110 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
113 CREATE TABLE asset.stat_cat_entry (
114 id SERIAL PRIMARY KEY,
115 stat_cat INT NOT NULL,
118 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
121 CREATE TABLE asset.stat_cat_entry_copy_map (
122 id BIGSERIAL PRIMARY KEY,
123 stat_cat INT NOT NULL,
124 stat_cat_entry INT NOT NULL,
125 owning_copy BIGINT NOT NULL,
126 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
129 CREATE TABLE asset.copy_note (
130 id BIGSERIAL PRIMARY KEY,
131 owning_copy BIGINT NOT NULL,
132 creator BIGINT NOT NULL,
133 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
134 pub BOOL NOT NULL DEFAULT FALSE,
138 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
140 CREATE TABLE asset.uri (
141 id SERIAL PRIMARY KEY,
144 use_restriction TEXT,
145 active BOOL NOT NULL DEFAULT TRUE
148 CREATE TABLE asset.call_number (
149 id bigserial PRIMARY KEY,
150 creator BIGINT NOT NULL,
151 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
152 editor BIGINT NOT NULL,
153 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
154 record bigint NOT NULL,
155 owning_lib INT NOT NULL,
157 deleted BOOL NOT NULL DEFAULT FALSE
159 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
160 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
161 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
162 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
163 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (upper(label),id,owning_lib);
164 CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted IS FALSE;
165 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;
167 CREATE TABLE asset.uri_call_number_map (
168 id BIGSERIAL PRIMARY KEY,
169 uri INT NOT NULL REFERENCES asset.uri (id),
170 call_number INT NOT NULL REFERENCES asset.call_number (id),
171 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
173 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
175 CREATE TABLE asset.call_number_note (
176 id BIGSERIAL PRIMARY KEY,
177 call_number BIGINT NOT NULL,
178 creator BIGINT NOT NULL,
179 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
180 pub BOOL NOT NULL DEFAULT FALSE,
184 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
186 CREATE VIEW stats.fleshed_copy AS
188 CAST(cp.create_date AS DATE) AS create_date_day,
189 CAST(cp.edit_date AS DATE) AS edit_date_day,
190 DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
191 DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
192 cn.label AS call_number_label,
198 JOIN asset.call_number cn ON (cp.call_number = cn.id)
199 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
201 CREATE VIEW stats.fleshed_call_number AS
203 CAST(cn.create_date AS DATE) AS create_date_day,
204 CAST(cn.edit_date AS DATE) AS edit_date_day,
205 DATE_TRUNC('hour', cn.create_date) AS create_date_hour,
206 DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour,
210 FROM asset.call_number cn
211 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);