1 DROP SCHEMA asset CASCADE;
7 CREATE TABLE asset.copy (
8 id BIGSERIAL PRIMARY KEY,
9 circ_lib INT NOT NULL REFERENCES actor.org_unit (id),
10 creator BIGINT NOT NULL,
11 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
12 editor BIGINT NOT NULL,
13 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
14 barcode TEXT UNIQUE NOT NULL,
15 call_number BIGINT NOT NULL,
17 holdable BOOL NOT NULL DEFAULT TRUE,
18 available BOOL NOT NULL DEFAULT TRUE, -- was STATUS
19 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
20 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
21 circulate BOOL NOT NULL DEFAULT TRUE,
22 deposit BOOL NOT NULL DEFAULT FALSE,
23 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
24 price NUMERIC(8,2) NOT NULL DEFAULT 0.00,
25 ref BOOL NOT NULL DEFAULT FALSE,
28 opac_visible BOOL NOT NULL DEFAULT TRUE
30 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
32 CREATE TABLE asset.copy_transparency (
33 id SERIAL PRIMARY KEY,
35 owner INT NOT NULL REFERENCES actor.org_unit (id),
36 circ_lib INT REFERENCES actor.org_unit (id),
38 loan_duration INT CHECK ( loan_duration IN (1,2,3) ),
39 fine_level INT CHECK ( fine_level IN (1,2,3) ),
42 deposit_amount NUMERIC(6,2),
47 CONSTRAINT scte_name_once_per_lib UNIQUE (owner,name)
50 CREATE TABLE asset.copy_tranparency_map (
51 id BIGSERIAL PRIMARY KEY,
52 tansparency INT NOT NULL REFERENCES asset.copy_transparency (id),
53 target_copy INT NOT NULL UNIQUE REFERENCES asset.copy (id)
55 CREATE INDEX cp_tr_cp_idx ON asset.copy_tranparency_map (tansparency);
57 CREATE TABLE asset.stat_cat_entry_transparency_map (
58 id BIGSERIAL PRIMARY KEY,
59 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
60 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
61 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
62 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
65 CREATE TABLE asset.stat_cat (
66 id SERIAL PRIMARY KEY,
67 owner INT NOT NULL, -- actor.org_unit.id
69 opac_visible BOOL NOT NULL DEFAULT FALSE,
70 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
73 CREATE TABLE asset.stat_cat_entry (
74 id SERIAL PRIMARY KEY,
75 owner INT NOT NULL, -- actor.org_unit.id
77 CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
80 CREATE TABLE asset.stat_cat_entry_copy_map (
81 id BIGSERIAL PRIMARY KEY,
82 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
83 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
84 owning_copy BIGINT NOT NULL, -- needs ON DELETE CASCADE
85 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
88 CREATE TABLE asset.copy_note (
89 id BIGSERIAL PRIMARY KEY,
90 owning_copy BIGINT NOT NULL,
91 creator BIGINT NOT NULL,
92 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
97 CREATE TABLE asset.call_number (
98 id bigserial PRIMARY KEY,
99 creator BIGINT NOT NULL,
100 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
101 editor BIGINT NOT NULL,
102 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
103 record bigint NOT NULL,
105 owning_lib INT NOT NULL,
106 CONSTRAINT asset_call_number_label_once_per_lib UNIQUE (record, owning_lib, label)
108 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
109 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
110 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
112 CREATE TABLE asset.call_number_note (
113 id BIGSERIAL PRIMARY KEY,
114 call_number BIGINT NOT NULL,
115 creator BIGINT NOT NULL,
116 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),