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 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
22 editor BIGINT NOT NULL,
23 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
24 barcode TEXT UNIQUE NOT NULL,
25 call_number BIGINT NOT NULL,
27 holdable BOOL NOT NULL DEFAULT TRUE,
28 status INT NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
29 location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
30 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
31 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
32 circulate BOOL NOT NULL DEFAULT TRUE,
33 deposit BOOL NOT NULL DEFAULT FALSE,
34 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
35 price NUMERIC(8,2) NOT NULL DEFAULT 0.00,
36 ref BOOL NOT NULL DEFAULT FALSE,
42 opac_visible BOOL NOT NULL DEFAULT TRUE,
43 deleted BOOL NOT NULL DEFAULT FALSE
45 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
46 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number) WHERE status = 0;
47 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;
49 CREATE TABLE asset.copy_transparency (
50 id SERIAL PRIMARY KEY,
51 deposit_amount NUMERIC(6,2),
52 owner INT NOT NULL REFERENCES actor.org_unit (id),
53 circ_lib INT REFERENCES actor.org_unit (id),
54 loan_duration INT CHECK ( loan_duration IN (1,2,3) ),
55 fine_level INT CHECK ( fine_level IN (1,2,3) ),
64 CONSTRAINT scte_name_once_per_lib UNIQUE (owner,name)
67 CREATE TABLE asset.copy_tranparency_map (
68 id BIGSERIAL PRIMARY KEY,
69 tansparency INT NOT NULL REFERENCES asset.copy_transparency (id),
70 target_copy INT NOT NULL UNIQUE REFERENCES asset.copy (id)
72 CREATE INDEX cp_tr_cp_idx ON asset.copy_tranparency_map (tansparency);
74 CREATE TABLE asset.stat_cat_entry_transparency_map (
75 id BIGSERIAL PRIMARY KEY,
76 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
77 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
78 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
79 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
82 CREATE TABLE asset.stat_cat (
83 id SERIAL PRIMARY KEY,
85 opac_visible BOOL NOT NULL DEFAULT FALSE,
87 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
90 CREATE TABLE asset.stat_cat_entry (
91 id SERIAL PRIMARY KEY,
92 stat_cat INT NOT NULL,
95 CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
98 CREATE TABLE asset.stat_cat_entry_copy_map (
99 id BIGSERIAL PRIMARY KEY,
100 stat_cat INT NOT NULL,
101 stat_cat_entry INT NOT NULL,
102 owning_copy BIGINT NOT NULL,
103 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
106 CREATE TABLE asset.copy_note (
107 id BIGSERIAL PRIMARY KEY,
108 owning_copy BIGINT NOT NULL,
109 creator BIGINT NOT NULL,
110 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
111 pub BOOL NOT NULL DEFAULT FALSE,
116 CREATE TABLE asset.call_number (
117 id bigserial PRIMARY KEY,
118 creator BIGINT NOT NULL,
119 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
120 editor BIGINT NOT NULL,
121 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
122 record bigint NOT NULL,
123 owning_lib INT NOT NULL,
125 deleted BOOL NOT NULL DEFAULT FALSE
127 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
128 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
129 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
130 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
131 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (upper(label),id,owning_lib);
132 CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted IS FALSE;
133 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;
135 INSERT INTO asset.call_number VALUES (-1,1,NOW(),1,NOW(),-1,1,'UNCATALOGED');
137 CREATE TABLE asset.call_number_note (
138 id BIGSERIAL PRIMARY KEY,
139 call_number BIGINT NOT NULL,
140 creator BIGINT NOT NULL,
141 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
142 pub BOOL NOT NULL DEFAULT FALSE,
148 CREATE VIEW stats.fleshed_copy AS
150 CAST(cp.create_date AS DATE) AS create_date_day,
151 CAST(cp.edit_date AS DATE) AS edit_date_day,
152 DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
153 DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
154 cn.label AS call_number_label,
160 JOIN asset.call_number cn ON (cp.call_number = cn.id)
161 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
163 CREATE VIEW stats.fleshed_call_number AS
165 CAST(cn.create_date AS DATE) AS create_date_day,
166 CAST(cn.edit_date AS DATE) AS edit_date_day,
167 DATE_TRUNC('hour', cn.create_date) AS create_date_hour,
168 DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour,
172 FROM asset.call_number cn
173 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);