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 opac_visible BOOL NOT NULL DEFAULT TRUE,
30 circulate BOOL NOT NULL DEFAULT TRUE
33 CREATE TABLE asset.copy (
34 id BIGSERIAL PRIMARY KEY,
35 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
36 creator BIGINT NOT NULL,
37 call_number BIGINT NOT NULL,
38 editor BIGINT NOT NULL,
39 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
40 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
42 status INT NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
43 location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
44 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
45 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
47 circulate BOOL NOT NULL DEFAULT TRUE,
48 deposit BOOL NOT NULL DEFAULT FALSE,
49 ref BOOL NOT NULL DEFAULT FALSE,
50 holdable BOOL NOT NULL DEFAULT TRUE,
51 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
52 price NUMERIC(8,2) NOT NULL DEFAULT 0.00,
53 barcode TEXT NOT NULL,
59 opac_visible BOOL NOT NULL DEFAULT TRUE,
60 deleted BOOL NOT NULL DEFAULT FALSE
62 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted IS FALSE;
63 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
64 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
65 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;
67 CREATE TABLE asset.copy_transparency (
68 id SERIAL PRIMARY KEY,
69 deposit_amount NUMERIC(6,2),
70 owner INT NOT NULL REFERENCES actor.org_unit (id),
71 circ_lib INT REFERENCES actor.org_unit (id),
72 loan_duration INT CHECK ( loan_duration IN (1,2,3) ),
73 fine_level INT CHECK ( fine_level IN (1,2,3) ),
82 CONSTRAINT scte_name_once_per_lib UNIQUE (owner,name)
85 CREATE TABLE asset.copy_tranparency_map (
86 id BIGSERIAL PRIMARY KEY,
87 tansparency INT NOT NULL REFERENCES asset.copy_transparency (id),
88 target_copy INT NOT NULL UNIQUE REFERENCES asset.copy (id)
90 CREATE INDEX cp_tr_cp_idx ON asset.copy_tranparency_map (tansparency);
92 CREATE TABLE asset.stat_cat_entry_transparency_map (
93 id BIGSERIAL PRIMARY KEY,
94 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
95 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
96 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
97 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
100 CREATE TABLE asset.stat_cat (
101 id SERIAL PRIMARY KEY,
103 opac_visible BOOL NOT NULL DEFAULT FALSE,
105 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
108 CREATE TABLE asset.stat_cat_entry (
109 id SERIAL PRIMARY KEY,
110 stat_cat INT NOT NULL,
113 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
116 CREATE TABLE asset.stat_cat_entry_copy_map (
117 id BIGSERIAL PRIMARY KEY,
118 stat_cat INT NOT NULL,
119 stat_cat_entry INT NOT NULL,
120 owning_copy BIGINT NOT NULL,
121 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
124 CREATE TABLE asset.copy_note (
125 id BIGSERIAL PRIMARY KEY,
126 owning_copy BIGINT NOT NULL,
127 creator BIGINT NOT NULL,
128 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
129 pub BOOL NOT NULL DEFAULT FALSE,
134 CREATE TABLE asset.call_number (
135 id bigserial PRIMARY KEY,
136 creator BIGINT NOT NULL,
137 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
138 editor BIGINT NOT NULL,
139 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
140 record bigint NOT NULL,
141 owning_lib INT NOT NULL,
143 deleted BOOL NOT NULL DEFAULT FALSE
145 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
146 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
147 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
148 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
149 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (upper(label),id,owning_lib);
150 CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted IS FALSE;
151 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;
153 CREATE TABLE asset.call_number_note (
154 id BIGSERIAL PRIMARY KEY,
155 call_number BIGINT NOT NULL,
156 creator BIGINT NOT NULL,
157 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
158 pub BOOL NOT NULL DEFAULT FALSE,
163 CREATE VIEW stats.fleshed_copy AS
165 CAST(cp.create_date AS DATE) AS create_date_day,
166 CAST(cp.edit_date AS DATE) AS edit_date_day,
167 DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
168 DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
169 cn.label AS call_number_label,
175 JOIN asset.call_number cn ON (cp.call_number = cn.id)
176 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
178 CREATE VIEW stats.fleshed_call_number AS
180 CAST(cn.create_date AS DATE) AS create_date_day,
181 CAST(cn.edit_date AS DATE) AS edit_date_day,
182 DATE_TRUNC('hour', cn.create_date) AS create_date_hour,
183 DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour,
187 FROM asset.call_number cn
188 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
190 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
193 source_cn asset.call_number%ROWTYPE;
194 target_cn asset.call_number%ROWTYPE;
196 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
198 SELECT INTO target_cn *
199 FROM asset.call_number
200 WHERE label = source_cn.label
201 AND owning_lib = source_cn.owning_lib
202 AND record = source_cn.record;
206 SET call_number = target_cn.id
207 WHERE call_number = source_cn.id;
209 UPDATE asset.call_number
210 SET record = target_record
211 WHERE id = source_cn.id;
214 moved_cns := moved_cns + 1;
219 $func$ LANGUAGE plpgsql;