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,
39 opac_visible BOOL NOT NULL DEFAULT TRUE
41 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
42 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number) WHERE status = 0;
44 CREATE TABLE asset.copy_transparency (
45 id SERIAL PRIMARY KEY,
46 deposit_amount NUMERIC(6,2),
47 owner INT NOT NULL REFERENCES actor.org_unit (id),
48 circ_lib INT REFERENCES actor.org_unit (id),
49 loan_duration INT CHECK ( loan_duration IN (1,2,3) ),
50 fine_level INT CHECK ( fine_level IN (1,2,3) ),
59 CONSTRAINT scte_name_once_per_lib UNIQUE (owner,name)
62 CREATE TABLE asset.copy_tranparency_map (
63 id BIGSERIAL PRIMARY KEY,
64 tansparency INT NOT NULL REFERENCES asset.copy_transparency (id),
65 target_copy INT NOT NULL UNIQUE REFERENCES asset.copy (id)
67 CREATE INDEX cp_tr_cp_idx ON asset.copy_tranparency_map (tansparency);
69 CREATE TABLE asset.stat_cat_entry_transparency_map (
70 id BIGSERIAL PRIMARY KEY,
71 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
72 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
73 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
74 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
77 CREATE TABLE asset.stat_cat (
78 id SERIAL PRIMARY KEY,
80 opac_visible BOOL NOT NULL DEFAULT FALSE,
82 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
85 CREATE TABLE asset.stat_cat_entry (
86 id SERIAL PRIMARY KEY,
87 stat_cat INT NOT NULL,
90 CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
93 CREATE TABLE asset.stat_cat_entry_copy_map (
94 id BIGSERIAL PRIMARY KEY,
95 stat_cat INT NOT NULL,
96 stat_cat_entry INT NOT NULL,
97 owning_copy BIGINT NOT NULL,
98 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
101 CREATE TABLE asset.copy_note (
102 id BIGSERIAL PRIMARY KEY,
103 owning_copy BIGINT NOT NULL,
104 creator BIGINT NOT NULL,
105 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
110 CREATE TABLE asset.call_number (
111 id bigserial PRIMARY KEY,
112 creator BIGINT NOT NULL,
113 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
114 editor BIGINT NOT NULL,
115 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
116 record bigint NOT NULL,
117 owning_lib INT NOT NULL,
119 CONSTRAINT asset_call_number_label_once_per_lib UNIQUE (record, owning_lib, label)
121 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
122 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
123 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
124 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (asset.call_number_dewey(label));
126 CREATE TABLE asset.call_number_note (
127 id BIGSERIAL PRIMARY KEY,
128 call_number BIGINT NOT NULL,
129 creator BIGINT NOT NULL,
130 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),