1. Add table: serial.caption_and_pattern
[working/Evergreen.git] / Open-ILS / src / sql / Pg / 210.schema.serials.sql
1
2
3 DROP SCHEMA IF EXISTS serial CASCADE;
4
5 BEGIN;
6
7 CREATE SCHEMA serial;
8
9 CREATE TABLE serial.record_entry (
10         id              BIGSERIAL       PRIMARY KEY,
11         record          BIGINT          REFERENCES biblio.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
12         owning_lib      INT             NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
13         creator         INT             NOT NULL DEFAULT 1,
14         editor          INT             NOT NULL DEFAULT 1,
15         source          INT,
16         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
17         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
18         active          BOOL            NOT NULL DEFAULT TRUE,
19         deleted         BOOL            NOT NULL DEFAULT FALSE,
20         marc            TEXT,
21         last_xact_id    TEXT            NOT NULL
22 );
23 CREATE INDEX serial_record_entry_creator_idx ON serial.record_entry ( creator );
24 CREATE INDEX serial_record_entry_editor_idx ON serial.record_entry ( editor );
25 CREATE INDEX serial_record_entry_owning_lib_idx ON serial.record_entry ( owning_lib, deleted );
26
27 CREATE RULE protect_mfhd_delete AS ON DELETE TO serial.record_entry DO INSTEAD UPDATE serial.record_entry SET deleted = true WHERE old.id = serial.record_entry.id;
28
29 CREATE TABLE serial.caption_and_pattern (
30         id           SERIAL       PRIMARY KEY,
31         record       BIGINT       NOT NULL
32                                   REFERENCES serial.record_entry (id)
33                                   ON DELETE CASCADE
34                                   DEFERRABLE INITIALLY DEFERRED,
35         type         TEXT         NOT NULL
36                                   CONSTRAINT cap_type CHECK ( type in
37                                   ( 'basic', 'supplement', 'index' )),
38         create_time  TIMESTAMPTZ  NOT NULL DEFAULT now(),
39         active       BOOL         NOT NULL DEFAULT FALSE,
40         pattern_code TEXT         NOT NULL,       -- must contain JSON
41         enum_1       TEXT,
42         enum_2       TEXT,
43         enum_3       TEXT,
44         enum_4       TEXT,
45         enum_5       TEXT,
46         enum_6       TEXT,
47         chron_1      TEXT,
48         chron_2      TEXT,
49         chron_3      TEXT,
50         chron_4      TEXT,
51         chron_5      TEXT
52 );
53
54 CREATE TABLE serial.subscription (
55         id                     SERIAL       PRIMARY KEY,
56         start_date             TIMESTAMP WITH TIME ZONE     NOT NULL,
57         end_date               TIMESTAMP WITH TIME ZONE,    -- interpret NULL as current subscription
58         record_entry           BIGINT       REFERENCES serial.record_entry (id)
59                                             ON DELETE SET NULL
60                                             DEFERRABLE INITIALLY DEFERRED,
61         expected_date_offset   INTERVAL
62         -- acquisitions/business-side tables link to here
63 );
64
65 --at least one distribution per org_unit holding issues
66 CREATE TABLE serial.distribution (
67         id                    SERIAL  PRIMARY KEY,
68         subscription          INT     NOT NULL
69                                       REFERENCES serial.subscription (id)
70                                                                   ON DELETE CASCADE
71                                                                   DEFERRABLE INITIALLY DEFERRED,
72         holding_lib           INT     NOT NULL
73                                       REFERENCES actor.org_unit (id)
74                                                                   DEFERRABLE INITIALLY DEFERRED,
75         label                 TEXT    NOT NULL,
76         receive_call_number   BIGINT  REFERENCES asset.call_number (id)
77                                       DEFERRABLE INITIALLY DEFERRED,
78         receive_unit_template INT     REFERENCES asset.copy_template (id)
79                                       DEFERRABLE INITIALLY DEFERRED,
80         bind_call_number      BIGINT  REFERENCES asset.call_number (id)
81                                       DEFERRABLE INITIALLY DEFERRED,
82         bind_unit_template    INT     REFERENCES asset.copy_template (id)
83                                       DEFERRABLE INITIALLY DEFERRED,
84         unit_label_base       TEXT,
85         unit_label_suffix     TEXT
86 );
87
88 CREATE TABLE serial.stream (
89         id              SERIAL  PRIMARY KEY,
90         distribution    INT     NOT NULL
91                                 REFERENCES serial.distribution (id)
92                                 ON DELETE CASCADE
93                                 DEFERRABLE INITIALLY DEFERRED,
94         routing_label   TEXT
95 );
96
97 CREATE UNIQUE INDEX label_once_per_dist
98         ON serial.stream (distribution, routing_label)
99         WHERE routing_label IS NOT NULL;
100
101 CREATE TABLE serial.routing_list_user (
102         id             SERIAL       PRIMARY KEY,
103         stream         INT          NOT NULL
104                                     REFERENCES serial.stream
105                                     ON DELETE CASCADE
106                                     DEFERRABLE INITIALLY DEFERRED,
107         pos            INT          NOT NULL DEFAULT 1,
108         reader         INT          REFERENCES actor.usr
109                                     ON DELETE CASCADE
110                                     DEFERRABLE INITIALLY DEFERRED,
111         department     TEXT,
112         note           TEXT,
113         CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
114         CONSTRAINT reader_or_dept CHECK
115         (
116             -- Recipient is a person or a department, but not both
117                 (reader IS NOT NULL AND department IS NULL) OR
118                 (reader IS NULL AND department IS NOT NULL)
119         )
120 );
121
122 CREATE TABLE serial.issuance (
123         id              SERIAL    PRIMARY KEY,
124         creator         INT       NOT NULL
125                                   REFERENCES actor.usr (id)
126                                                           DEFERRABLE INITIALLY DEFERRED,
127         editor          INT       NOT NULL
128                                   REFERENCES actor.usr (id)
129                                   DEFERRABLE INITIALLY DEFERRED,
130         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
131         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
132         subscription    INT       NOT NULL
133                                   REFERENCES serial.subscription (id)
134                                   ON DELETE CASCADE
135                                   DEFERRABLE INITIALLY DEFERRED,
136         label           TEXT,
137         date_published  TIMESTAMP WITH TIME ZONE,
138         holding_code    TEXT,
139         holding_type    TEXT      CONSTRAINT valid_holding_type CHECK
140                                   (
141                                       holding_type IS NULL
142                                       OR holding_type IN ('basic','supplement','index')
143                                   ),
144         holding_link_id INT
145         -- TODO: add columns for separate enumeration/chronology values
146 );
147
148 CREATE TABLE serial.unit (
149         label           TEXT,
150         label_sort_key  TEXT,
151         contents        TEXT    NOT NULL
152 ) INHERITS (asset.copy);
153
154 ALTER TABLE serial.unit ADD PRIMARY KEY (id);
155
156 CREATE TABLE serial.item (
157         id              SERIAL  PRIMARY KEY,
158         creator         INT     NOT NULL
159                                 REFERENCES actor.usr (id)
160                                 DEFERRABLE INITIALLY DEFERRED,
161         editor          INT     NOT NULL
162                                 REFERENCES actor.usr (id)
163                                 DEFERRABLE INITIALLY DEFERRED,
164         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
165         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
166         issuance        INT     NOT NULL
167                                 REFERENCES serial.issuance (id)
168                                 ON DELETE CASCADE
169                                 DEFERRABLE INITIALLY DEFERRED,
170         stream          INT     NOT NULL
171                                 REFERENCES serial.stream (id)
172                                 ON DELETE CASCADE
173                                 DEFERRABLE INITIALLY DEFERRED,
174         unit            INT     REFERENCES serial.unit (id)
175                                 ON DELETE SET NULL
176                                 DEFERRABLE INITIALLY DEFERRED,
177         uri             INT     REFERENCES asset.uri (id)
178                                 ON DELETE SET NULL
179                                 DEFERRABLE INITIALLY DEFERRED,
180         date_expected   TIMESTAMP WITH TIME ZONE,
181         date_received   TIMESTAMP WITH TIME ZONE
182 );
183
184 CREATE TABLE serial.item_note (
185         id          SERIAL  PRIMARY KEY,
186         item        INT     NOT NULL
187                             REFERENCES serial.item (id)
188                             ON DELETE CASCADE
189                             DEFERRABLE INITIALLY DEFERRED,
190         creator     INT     NOT NULL
191                             REFERENCES actor.usr (id)
192                             DEFERRABLE INITIALLY DEFERRED,
193         create_date TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
194         pub         BOOL    NOT NULL    DEFAULT FALSE,
195         title       TEXT    NOT NULL,
196         value       TEXT    NOT NULL
197 );
198
199 CREATE TABLE serial.bib_summary (
200         id                  SERIAL  PRIMARY KEY,
201         distribution        INT     NOT NULL
202                                     REFERENCES serial.distribution (id)
203                                     ON DELETE CASCADE
204                                     DEFERRABLE INITIALLY DEFERRED,
205         generated_coverage  TEXT    NOT NULL,
206         textual_holdings    TEXT
207 );
208
209 CREATE TABLE serial.sup_summary (
210         id                  SERIAL  PRIMARY KEY,
211         distribution        INT     NOT NULL
212                                     REFERENCES serial.distribution (id)
213                                     ON DELETE CASCADE
214                                     DEFERRABLE INITIALLY DEFERRED,
215         generated_coverage  TEXT    NOT NULL,
216         textual_holdings    TEXT
217 );
218
219 CREATE TABLE serial.index_summary (
220         id                  SERIAL  PRIMARY KEY,
221         distribution        INT     NOT NULL
222                                     REFERENCES serial.distribution (id)
223                                     ON DELETE CASCADE
224                                     DEFERRABLE INITIALLY DEFERRED,
225         generated_coverage  TEXT    NOT NULL,
226         textual_holdings    TEXT
227 );
228
229 COMMIT;