1 DROP SCHEMA asset CASCADE;
7 CREATE TABLE asset.copy_location (
10 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
11 holdable BOOL NOT NULL DEFAULT TRUE,
12 opac_visible BOOL NOT NULL DEFAULT TRUE,
13 circulate BOOL NOT NULL DEFAULT TRUE
15 INSERT INTO asset.copy_location (name,owning_lib) VALUES ('Stacks',1);
17 CREATE TABLE asset.copy (
18 id BIGSERIAL PRIMARY KEY,
19 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
20 creator BIGINT NOT NULL,
21 call_number BIGINT NOT NULL,
22 editor BIGINT NOT NULL,
23 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
24 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
26 status INT NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
27 location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
28 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
29 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
31 circulate BOOL NOT NULL DEFAULT TRUE,
32 deposit BOOL NOT NULL DEFAULT FALSE,
33 ref BOOL NOT NULL DEFAULT FALSE,
34 holdable BOOL NOT NULL DEFAULT TRUE,
35 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
36 price NUMERIC(8,2) NOT NULL DEFAULT 0.00,
37 barcode TEXT NOT NULL,
43 opac_visible BOOL NOT NULL DEFAULT TRUE,
44 deleted BOOL NOT NULL DEFAULT FALSE
46 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted IS FALSE;
47 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
48 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
49 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;
51 CREATE TABLE asset.copy_transparency (
52 id SERIAL PRIMARY KEY,
53 deposit_amount NUMERIC(6,2),
54 owner INT NOT NULL REFERENCES actor.org_unit (id),
55 circ_lib INT REFERENCES actor.org_unit (id),
56 loan_duration INT CHECK ( loan_duration IN (1,2,3) ),
57 fine_level INT CHECK ( fine_level IN (1,2,3) ),
66 CONSTRAINT scte_name_once_per_lib UNIQUE (owner,name)
69 CREATE TABLE asset.copy_tranparency_map (
70 id BIGSERIAL PRIMARY KEY,
71 tansparency INT NOT NULL REFERENCES asset.copy_transparency (id),
72 target_copy INT NOT NULL UNIQUE REFERENCES asset.copy (id)
74 CREATE INDEX cp_tr_cp_idx ON asset.copy_tranparency_map (tansparency);
76 CREATE TABLE asset.stat_cat_entry_transparency_map (
77 id BIGSERIAL PRIMARY KEY,
78 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
79 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
80 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
81 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
84 CREATE TABLE asset.stat_cat (
85 id SERIAL PRIMARY KEY,
87 opac_visible BOOL NOT NULL DEFAULT FALSE,
89 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
92 CREATE TABLE asset.stat_cat_entry (
93 id SERIAL PRIMARY KEY,
94 stat_cat INT NOT NULL,
97 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
100 CREATE TABLE asset.stat_cat_entry_copy_map (
101 id BIGSERIAL PRIMARY KEY,
102 stat_cat INT NOT NULL,
103 stat_cat_entry INT NOT NULL,
104 owning_copy BIGINT NOT NULL,
105 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
108 CREATE TABLE asset.copy_note (
109 id BIGSERIAL PRIMARY KEY,
110 owning_copy BIGINT NOT NULL,
111 creator BIGINT NOT NULL,
112 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
113 pub BOOL NOT NULL DEFAULT FALSE,
118 CREATE TABLE asset.call_number (
119 id bigserial PRIMARY KEY,
120 creator BIGINT NOT NULL,
121 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
122 editor BIGINT NOT NULL,
123 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
124 record bigint NOT NULL,
125 owning_lib INT NOT NULL,
127 deleted BOOL NOT NULL DEFAULT FALSE
129 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
130 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
131 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
132 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
133 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (upper(label),id,owning_lib);
134 CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted IS FALSE;
135 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;
137 INSERT INTO asset.call_number VALUES (-1,1,NOW(),1,NOW(),-1,1,'UNCATALOGED');
139 CREATE TABLE asset.call_number_note (
140 id BIGSERIAL PRIMARY KEY,
141 call_number BIGINT NOT NULL,
142 creator BIGINT NOT NULL,
143 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
144 pub BOOL NOT NULL DEFAULT FALSE,
150 CREATE VIEW stats.fleshed_copy AS
152 CAST(cp.create_date AS DATE) AS create_date_day,
153 CAST(cp.edit_date AS DATE) AS edit_date_day,
154 DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
155 DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
156 cn.label AS call_number_label,
162 JOIN asset.call_number cn ON (cp.call_number = cn.id)
163 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
165 CREATE VIEW stats.fleshed_call_number AS
167 CAST(cn.create_date AS DATE) AS create_date_day,
168 CAST(cn.edit_date AS DATE) AS edit_date_day,
169 DATE_TRUNC('hour', cn.create_date) AS create_date_hour,
170 DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour,
174 FROM asset.call_number cn
175 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
177 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
180 source_cn asset.call_number%ROWTYPE;
181 target_cn asset.call_number%ROWTYPE;
183 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
185 SELECT INTO target_cn *
186 FROM asset.call_number
187 WHERE label = source_cn.label
188 AND owning_lib = source_cn.owning_lib
189 AND record = source_cn.record;
193 SET call_number = target_cn.id
194 WHERE call_number = source_cn.id;
196 UPDATE asset.call_number
197 SET record = target_record
198 WHERE id = source_cn.id;
201 moved_cns := moved_cns + 1;
206 $func$ LANGUAGE plpgsql;