1 DROP SCHEMA asset CASCADE;
7 CREATE TABLE asset.copy (
8 id BIGSERIAL PRIMARY KEY,
9 creator BIGINT NOT NULL,
10 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
11 editor BIGINT NOT NULL,
12 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
13 barcode TEXT UNIQUE NOT NULL,
14 call_number BIGINT NOT NULL,
16 holdable BOOL NOT NULL DEFAULT TRUE,
17 available BOOL NOT NULL DEFAULT TRUE, -- was STATUS
18 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
19 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
20 circulate BOOL NOT NULL DEFAULT TRUE,
21 deposit BOOL NOT NULL DEFAULT FALSE,
22 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
23 price NUMERIC(8,2) NOT NULL DEFAULT 0.00,
24 ref BOOL NOT NULL DEFAULT FALSE,
27 opac_visible BOOL NOT NULL DEFAULT TRUE
29 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
31 CREATE TABLE asset.stat_cat (
32 id SERIAL PRIMARY KEY,
33 owner INT NOT NULL, -- actor.org_unit.id
35 opac_visible BOOL NOT NULL DEFAULT FALSE,
36 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
39 CREATE TABLE asset.stat_cat_entry (
40 id SERIAL PRIMARY KEY,
41 owner INT NOT NULL, -- actor.org_unit.id
43 CONSTRAINT sce_once_per_owner UNIQUE (owner,value)
46 CREATE TABLE asset.stat_cat_entry_copy_map (
47 id BIGSERIAL PRIMARY KEY,
48 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
49 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
50 owning_copy BIGINT NOT NULL, -- needs ON DELETE CASCADE
51 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
54 CREATE TABLE asset.copy_note (
55 id BIGSERIAL PRIMARY KEY,
56 owning_copy BIGINT NOT NULL,
57 creator BIGINT NOT NULL,
58 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
63 CREATE TABLE asset.call_number (
64 id bigserial PRIMARY KEY,
65 creator BIGINT NOT NULL,
66 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
67 editor BIGINT NOT NULL,
68 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
69 record bigint NOT NULL,
71 owning_lib INT NOT NULL,
72 CONSTRAINT asset_call_number_label_once_per_lib UNIQUE (owning_lib, label)
74 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
75 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
76 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
78 CREATE TABLE asset.call_number_note (
79 id BIGSERIAL PRIMARY KEY,
80 call_number BIGINT NOT NULL,
81 creator BIGINT NOT NULL,
82 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),