Merge Dan Wells' changes to the serial schema from the
[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 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON serial.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_901();
27 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON serial.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
28
29 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;
30
31 CREATE TABLE serial.subscription (
32         id                     SERIAL       PRIMARY KEY,
33         owning_lib             INT          NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
34         start_date             TIMESTAMP WITH TIME ZONE     NOT NULL,
35         end_date               TIMESTAMP WITH TIME ZONE,    -- interpret NULL as current subscription
36         record_entry           BIGINT       REFERENCES biblio.record_entry (id)
37                                             ON DELETE SET NULL
38                                             DEFERRABLE INITIALLY DEFERRED,
39         expected_date_offset   INTERVAL
40         -- acquisitions/business-side tables link to here
41 );
42
43 CREATE TABLE serial.subscription_note (
44         id           SERIAL PRIMARY KEY,
45         subscription INT    NOT NULL
46                             REFERENCES serial.subscription (id)
47                             ON DELETE CASCADE
48                             DEFERRABLE INITIALLY DEFERRED,
49         creator      INT    NOT NULL
50                             REFERENCES actor.usr (id)
51                             DEFERRABLE INITIALLY DEFERRED,
52         create_date  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
53         pub          BOOL   NOT NULL DEFAULT FALSE,
54         title        TEXT   NOT NULL,
55         value        TEXT   NOT NULL
56 );
57
58 CREATE TABLE serial.caption_and_pattern (
59         id           SERIAL       PRIMARY KEY,
60         subscription INT          NOT NULL
61                                   REFERENCES serial.subscription (id)
62                                   ON DELETE CASCADE
63                                   DEFERRABLE INITIALLY DEFERRED,
64         type         TEXT         NOT NULL
65                                   CONSTRAINT cap_type CHECK ( type in
66                                   ( 'basic', 'supplement', 'index' )),
67         create_date  TIMESTAMPTZ  NOT NULL DEFAULT now(),
68         active       BOOL         NOT NULL DEFAULT FALSE,
69         pattern_code TEXT         NOT NULL,       -- must contain JSON
70         enum_1       TEXT,
71         enum_2       TEXT,
72         enum_3       TEXT,
73         enum_4       TEXT,
74         enum_5       TEXT,
75         enum_6       TEXT,
76         chron_1      TEXT,
77         chron_2      TEXT,
78         chron_3      TEXT,
79         chron_4      TEXT,
80         chron_5      TEXT
81 );
82
83 --at least one distribution per org_unit holding issues
84 CREATE TABLE serial.distribution (
85         id                    SERIAL  PRIMARY KEY,
86         record_entry          BIGINT  REFERENCES serial.record_entry (id)
87                                                                   ON DELETE SET NULL
88                                                                   DEFERRABLE INITIALLY DEFERRED,
89         subscription          INT     NOT NULL
90                                       REFERENCES serial.subscription (id)
91                                                                   ON DELETE CASCADE
92                                                                   DEFERRABLE INITIALLY DEFERRED,
93         holding_lib           INT     NOT NULL
94                                       REFERENCES actor.org_unit (id)
95                                                                   DEFERRABLE INITIALLY DEFERRED,
96         label                 TEXT    NOT NULL,
97         receive_call_number   BIGINT  REFERENCES asset.call_number (id)
98                                       DEFERRABLE INITIALLY DEFERRED,
99         receive_unit_template INT     REFERENCES asset.copy_template (id)
100                                       DEFERRABLE INITIALLY DEFERRED,
101         bind_call_number      BIGINT  REFERENCES asset.call_number (id)
102                                       DEFERRABLE INITIALLY DEFERRED,
103         bind_unit_template    INT     REFERENCES asset.copy_template (id)
104                                       DEFERRABLE INITIALLY DEFERRED,
105         unit_label_prefix     TEXT,
106         unit_label_suffix     TEXT
107 );
108 CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry);
109
110 CREATE TABLE serial.distribution_note (
111         id           SERIAL PRIMARY KEY,
112         distribution INT    NOT NULL
113                             REFERENCES serial.distribution (id)
114                             ON DELETE CASCADE
115                             DEFERRABLE INITIALLY DEFERRED,
116         creator      INT    NOT NULL
117                             REFERENCES actor.usr (id)
118                             DEFERRABLE INITIALLY DEFERRED,
119         create_date  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
120         pub          BOOL   NOT NULL DEFAULT FALSE,
121         title        TEXT   NOT NULL,
122         value        TEXT   NOT NULL
123 );
124
125 CREATE TABLE serial.stream (
126         id              SERIAL  PRIMARY KEY,
127         distribution    INT     NOT NULL
128                                 REFERENCES serial.distribution (id)
129                                 ON DELETE CASCADE
130                                 DEFERRABLE INITIALLY DEFERRED,
131         routing_label   TEXT
132 );
133
134 CREATE UNIQUE INDEX label_once_per_dist
135         ON serial.stream (distribution, routing_label)
136         WHERE routing_label IS NOT NULL;
137
138 CREATE TABLE serial.routing_list_user (
139         id             SERIAL       PRIMARY KEY,
140         stream         INT          NOT NULL
141                                     REFERENCES serial.stream
142                                     ON DELETE CASCADE
143                                     DEFERRABLE INITIALLY DEFERRED,
144         pos            INT          NOT NULL DEFAULT 1,
145         reader         INT          REFERENCES actor.usr
146                                     ON DELETE CASCADE
147                                     DEFERRABLE INITIALLY DEFERRED,
148         department     TEXT,
149         note           TEXT,
150         CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
151         CONSTRAINT reader_or_dept CHECK
152         (
153             -- Recipient is a person or a department, but not both
154                 (reader IS NOT NULL AND department IS NULL) OR
155                 (reader IS NULL AND department IS NOT NULL)
156         )
157 );
158
159 CREATE TABLE serial.issuance (
160         id              SERIAL    PRIMARY KEY,
161         creator         INT       NOT NULL
162                                   REFERENCES actor.usr (id)
163                                                           DEFERRABLE INITIALLY DEFERRED,
164         editor          INT       NOT NULL
165                                   REFERENCES actor.usr (id)
166                                   DEFERRABLE INITIALLY DEFERRED,
167         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
168         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
169         subscription    INT       NOT NULL
170                                   REFERENCES serial.subscription (id)
171                                   ON DELETE CASCADE
172                                   DEFERRABLE INITIALLY DEFERRED,
173         label           TEXT,
174         date_published  TIMESTAMP WITH TIME ZONE,
175         caption_and_pattern INT   REFERENCES serial.caption_and_pattern (id)
176                                   DEFERRABLE INITIALLY DEFERRED,
177         holding_code    TEXT,
178         holding_type    TEXT      CONSTRAINT valid_holding_type CHECK
179                                   (
180                                       holding_type IS NULL
181                                       OR holding_type IN ('basic','supplement','index')
182                                   ),
183         holding_link_id INT -- probably defunct
184         -- TODO: add columns for separate enumeration/chronology values
185 );
186
187 CREATE TABLE serial.unit (
188         sort_key          TEXT,
189         detailed_contents TEXT    NOT NULL,
190         summary_contents  TEXT    NOT NULL
191 ) INHERITS (asset.copy);
192
193 ALTER TABLE serial.unit ADD PRIMARY KEY (id);
194
195 -- must create this rule explicitly; it is not inherited from asset.copy
196 CREATE RULE protect_serial_unit_delete AS ON DELETE TO serial.unit DO INSTEAD UPDATE serial.unit SET deleted = TRUE WHERE OLD.id = serial.unit.id;
197
198 CREATE TABLE serial.item (
199         id              SERIAL  PRIMARY KEY,
200         creator         INT     NOT NULL
201                                 REFERENCES actor.usr (id)
202                                 DEFERRABLE INITIALLY DEFERRED,
203         editor          INT     NOT NULL
204                                 REFERENCES actor.usr (id)
205                                 DEFERRABLE INITIALLY DEFERRED,
206         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
207         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
208         issuance        INT     NOT NULL
209                                 REFERENCES serial.issuance (id)
210                                 ON DELETE CASCADE
211                                 DEFERRABLE INITIALLY DEFERRED,
212         stream          INT     NOT NULL
213                                 REFERENCES serial.stream (id)
214                                 ON DELETE CASCADE
215                                 DEFERRABLE INITIALLY DEFERRED,
216         unit            INT     REFERENCES serial.unit (id)
217                                 ON DELETE SET NULL
218                                 DEFERRABLE INITIALLY DEFERRED,
219         uri             INT     REFERENCES asset.uri (id)
220                                 ON DELETE SET NULL
221                                 DEFERRABLE INITIALLY DEFERRED,
222         date_expected   TIMESTAMP WITH TIME ZONE,
223         date_received   TIMESTAMP WITH TIME ZONE
224         status          TEXT    CONSTRAINT valid_status CHECK
225                                 (
226                                     status IN ('Bindery', 'Bound', 'Claimed', 'Discarded', 'Expected', 'Not Held', 'Not Published', 'Received')
227                                 ) DEFAULT 'Expected',
228         shadowed        BOOL    NOT NULL DEFAULT FALSE -- ignore when generating summaries/labels
229 );
230
231 CREATE TABLE serial.item_note (
232         id          SERIAL  PRIMARY KEY,
233         item        INT     NOT NULL
234                             REFERENCES serial.item (id)
235                             ON DELETE CASCADE
236                             DEFERRABLE INITIALLY DEFERRED,
237         creator     INT     NOT NULL
238                             REFERENCES actor.usr (id)
239                             DEFERRABLE INITIALLY DEFERRED,
240         create_date TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
241         pub         BOOL    NOT NULL    DEFAULT FALSE,
242         title       TEXT    NOT NULL,
243         value       TEXT    NOT NULL
244 );
245
246 CREATE TABLE serial.basic_summary (
247         id                  SERIAL  PRIMARY KEY,
248         distribution        INT     NOT NULL
249                                     REFERENCES serial.distribution (id)
250                                     ON DELETE CASCADE
251                                     DEFERRABLE INITIALLY DEFERRED,
252         generated_coverage  TEXT    NOT NULL,
253         textual_holdings    TEXT
254 );
255
256 CREATE TABLE serial.supplement_summary (
257         id                  SERIAL  PRIMARY KEY,
258         distribution        INT     NOT NULL
259                                     REFERENCES serial.distribution (id)
260                                     ON DELETE CASCADE
261                                     DEFERRABLE INITIALLY DEFERRED,
262         generated_coverage  TEXT    NOT NULL,
263         textual_holdings    TEXT
264 );
265
266 CREATE TABLE serial.index_summary (
267         id                  SERIAL  PRIMARY KEY,
268         distribution        INT     NOT NULL
269                                     REFERENCES serial.distribution (id)
270                                     ON DELETE CASCADE
271                                     DEFERRABLE INITIALLY DEFERRED,
272         generated_coverage  TEXT    NOT NULL,
273         textual_holdings    TEXT
274 );
275
276 COMMIT;
277