2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
20 DROP SCHEMA stats CASCADE;
21 DROP SCHEMA config CASCADE;
27 COMMENT ON SCHEMA config IS $$
29 * Copyright (C) 2005 Georgia Public Library Service
30 * Mike Rylander <mrylander@gmail.com>
32 * The config schema holds static configuration data for the
33 * Open-ILS installation.
37 * This program is free software; you can redistribute it and/or
38 * modify it under the terms of the GNU General Public License
39 * as published by the Free Software Foundation; either version 2
40 * of the License, or (at your option) any later version.
42 * This program is distributed in the hope that it will be useful,
43 * but WITHOUT ANY WARRANTY; without even the implied warranty of
44 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
45 * GNU General Public License for more details.
49 CREATE TABLE config.upgrade_log (
50 version TEXT PRIMARY KEY,
51 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
54 INSERT INTO config.upgrade_log (version) VALUES ('0028'); -- phasefx
56 CREATE TABLE config.bib_source (
57 id SERIAL PRIMARY KEY,
58 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
59 source TEXT NOT NULL UNIQUE,
60 transcendant BOOL NOT NULL DEFAULT FALSE
62 COMMENT ON TABLE config.bib_source IS $$
64 * Copyright (C) 2005 Georgia Public Library Service
65 * Mike Rylander <mrylander@gmail.com>
67 * Valid sources of MARC records
69 * This is table is used to set up the relative "quality" of each
70 * MARC source, such as OCLC.
74 * This program is free software; you can redistribute it and/or
75 * modify it under the terms of the GNU General Public License
76 * as published by the Free Software Foundation; either version 2
77 * of the License, or (at your option) any later version.
79 * This program is distributed in the hope that it will be useful,
80 * but WITHOUT ANY WARRANTY; without even the implied warranty of
81 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
82 * GNU General Public License for more details.
86 CREATE TABLE config.standing (
87 id SERIAL PRIMARY KEY,
88 value TEXT NOT NULL UNIQUE
90 COMMENT ON TABLE config.standing IS $$
92 * Copyright (C) 2005 Georgia Public Library Service
93 * Mike Rylander <mrylander@gmail.com>
97 * This table contains the values that can be applied to a patron
98 * by a staff member. These values should not be changed, other
99 * than for translation, as the ID column is currently a "magic
100 * number" in the source. :(
104 * This program is free software; you can redistribute it and/or
105 * modify it under the terms of the GNU General Public License
106 * as published by the Free Software Foundation; either version 2
107 * of the License, or (at your option) any later version.
109 * This program is distributed in the hope that it will be useful,
110 * but WITHOUT ANY WARRANTY; without even the implied warranty of
111 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
112 * GNU General Public License for more details.
116 CREATE TABLE config.standing_penalty (
117 id SERIAL PRIMARY KEY,
118 name TEXT NOT NULL UNIQUE,
123 INSERT INTO config.standing_penalty (id,name,label,block_list)
124 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
125 INSERT INTO config.standing_penalty (id,name,label,block_list)
126 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
127 INSERT INTO config.standing_penalty (id,name,label,block_list)
128 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
129 INSERT INTO config.standing_penalty (id,name,label,block_list)
130 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
132 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
133 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
134 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
135 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
136 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
137 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
138 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
139 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
140 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
141 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
143 SELECT SETVAL('config.standing_penalty_id_seq', 100);
145 CREATE TABLE config.xml_transform (
146 name TEXT PRIMARY KEY,
147 namespace_uri TEXT NOT NULL,
148 prefix TEXT NOT NULL,
152 CREATE TABLE config.metabib_field (
153 id SERIAL PRIMARY KEY,
154 field_class TEXT NOT NULL CHECK (lower(field_class) IN ('title','author','subject','keyword','series')),
157 weight INT NOT NULL DEFAULT 1,
158 format TEXT NOT NULL DEFAULT 'mods33',
159 search_field BOOL NOT NULL DEFAULT TRUE,
160 facet_field BOOL NOT NULL DEFAULT FALSE
162 COMMENT ON TABLE config.metabib_field IS $$
164 * Copyright (C) 2005 Georgia Public Library Service
165 * Mike Rylander <mrylander@gmail.com>
167 * XPath used for record indexing ingest
169 * This table contains the XPath used to chop up MODS into its
170 * indexable parts. Each XPath entry is named and assigned to
171 * a "class" of either title, subject, author, keyword or series.
176 * This program is free software; you can redistribute it and/or
177 * modify it under the terms of the GNU General Public License
178 * as published by the Free Software Foundation; either version 2
179 * of the License, or (at your option) any later version.
181 * This program is distributed in the hope that it will be useful,
182 * but WITHOUT ANY WARRANTY; without even the implied warranty of
183 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
184 * GNU General Public License for more details.
188 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
190 CREATE TABLE config.non_cataloged_type (
191 id SERIAL PRIMARY KEY,
192 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
194 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
195 in_house BOOL NOT NULL DEFAULT FALSE,
196 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
198 COMMENT ON TABLE config.non_cataloged_type IS $$
200 * Copyright (C) 2005 Georgia Public Library Service
201 * Mike Rylander <mrylander@gmail.com>
203 * Types of valid non-cataloged items.
208 * This program is free software; you can redistribute it and/or
209 * modify it under the terms of the GNU General Public License
210 * as published by the Free Software Foundation; either version 2
211 * of the License, or (at your option) any later version.
213 * This program is distributed in the hope that it will be useful,
214 * but WITHOUT ANY WARRANTY; without even the implied warranty of
215 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
216 * GNU General Public License for more details.
220 CREATE TABLE config.identification_type (
221 id SERIAL PRIMARY KEY,
222 name TEXT NOT NULL UNIQUE
224 COMMENT ON TABLE config.identification_type IS $$
226 * Copyright (C) 2005 Georgia Public Library Service
227 * Mike Rylander <mrylander@gmail.com>
229 * Types of valid patron identification.
231 * Each patron must display at least one valid form of identification
232 * in order to get a library card. This table lists those forms.
237 * This program is free software; you can redistribute it and/or
238 * modify it under the terms of the GNU General Public License
239 * as published by the Free Software Foundation; either version 2
240 * of the License, or (at your option) any later version.
242 * This program is distributed in the hope that it will be useful,
243 * but WITHOUT ANY WARRANTY; without even the implied warranty of
244 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
245 * GNU General Public License for more details.
249 CREATE TABLE config.rule_circ_duration (
250 id SERIAL PRIMARY KEY,
251 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
252 extended INTERVAL NOT NULL,
253 normal INTERVAL NOT NULL,
254 shrt INTERVAL NOT NULL,
255 max_renewals INT NOT NULL
257 COMMENT ON TABLE config.rule_circ_duration IS $$
259 * Copyright (C) 2005 Georgia Public Library Service
260 * Mike Rylander <mrylander@gmail.com>
262 * Circulation Duration rules
264 * Each circulation is given a duration based on one of these rules.
269 * This program is free software; you can redistribute it and/or
270 * modify it under the terms of the GNU General Public License
271 * as published by the Free Software Foundation; either version 2
272 * of the License, or (at your option) any later version.
274 * This program is distributed in the hope that it will be useful,
275 * but WITHOUT ANY WARRANTY; without even the implied warranty of
276 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
277 * GNU General Public License for more details.
281 CREATE TABLE config.rule_max_fine (
282 id SERIAL PRIMARY KEY,
283 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
284 amount NUMERIC(6,2) NOT NULL,
285 is_percent BOOL NOT NULL DEFAULT FALSE
287 COMMENT ON TABLE config.rule_max_fine IS $$
289 * Copyright (C) 2005 Georgia Public Library Service
290 * Mike Rylander <mrylander@gmail.com>
292 * Circulation Max Fine rules
294 * Each circulation is given a maximum fine based on one of
300 * This program is free software; you can redistribute it and/or
301 * modify it under the terms of the GNU General Public License
302 * as published by the Free Software Foundation; either version 2
303 * of the License, or (at your option) any later version.
305 * This program is distributed in the hope that it will be useful,
306 * but WITHOUT ANY WARRANTY; without even the implied warranty of
307 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
308 * GNU General Public License for more details.
312 CREATE TABLE config.rule_recuring_fine (
313 id SERIAL PRIMARY KEY,
314 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
315 high NUMERIC(6,2) NOT NULL,
316 normal NUMERIC(6,2) NOT NULL,
317 low NUMERIC(6,2) NOT NULL,
318 recurance_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
320 COMMENT ON TABLE config.rule_recuring_fine IS $$
322 * Copyright (C) 2005 Georgia Public Library Service
323 * Mike Rylander <mrylander@gmail.com>
325 * Circulation Recurring Fine rules
327 * Each circulation is given a recurring fine amount based on one of
328 * these rules. The recurance_interval should not be any shorter
329 * than the interval between runs of the fine_processor.pl script
330 * (which is run from CRON), or you could miss fines.
335 * This program is free software; you can redistribute it and/or
336 * modify it under the terms of the GNU General Public License
337 * as published by the Free Software Foundation; either version 2
338 * of the License, or (at your option) any later version.
340 * This program is distributed in the hope that it will be useful,
341 * but WITHOUT ANY WARRANTY; without even the implied warranty of
342 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
343 * GNU General Public License for more details.
348 CREATE TABLE config.rule_age_hold_protect (
349 id SERIAL PRIMARY KEY,
350 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
351 age INTERVAL NOT NULL,
354 COMMENT ON TABLE config.rule_age_hold_protect IS $$
356 * Copyright (C) 2005 Georgia Public Library Service
357 * Mike Rylander <mrylander@gmail.com>
359 * Hold Item Age Protection rules
361 * A hold request can only capture new(ish) items when they are
362 * within a particular proximity of the home_ou of the requesting
363 * user. The proximity ('prox' column) is calculated by counting
364 * the number of tree edges between the user's home_ou and the owning_lib
365 * of the copy that could fulfill the hold.
370 * This program is free software; you can redistribute it and/or
371 * modify it under the terms of the GNU General Public License
372 * as published by the Free Software Foundation; either version 2
373 * of the License, or (at your option) any later version.
375 * This program is distributed in the hope that it will be useful,
376 * but WITHOUT ANY WARRANTY; without even the implied warranty of
377 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
378 * GNU General Public License for more details.
382 CREATE TABLE config.copy_status (
383 id SERIAL PRIMARY KEY,
384 name TEXT NOT NULL UNIQUE,
385 holdable BOOL NOT NULL DEFAULT FALSE,
386 opac_visible BOOL NOT NULL DEFAULT FALSE
388 COMMENT ON TABLE config.copy_status IS $$
390 * Copyright (C) 2005 Georgia Public Library Service
391 * Mike Rylander <mrylander@gmail.com>
395 * The available copy statuses, and whether a copy in that
396 * status is available for hold request capture. 0 (zero) is
397 * the only special number in this set, meaning that the item
398 * is available for immediate checkout, and is counted as available
401 * Statuses with an ID below 100 are not removable, and have special
402 * meaning in the code. Do not change them except to translate the
405 * You may add and remove statuses above 100, and these can be used
406 * to remove items from normal circulation without affecting the rest
407 * of the copy's values or its location.
411 * This program is free software; you can redistribute it and/or
412 * modify it under the terms of the GNU General Public License
413 * as published by the Free Software Foundation; either version 2
414 * of the License, or (at your option) any later version.
416 * This program is distributed in the hope that it will be useful,
417 * but WITHOUT ANY WARRANTY; without even the implied warranty of
418 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
419 * GNU General Public License for more details.
423 CREATE TABLE config.net_access_level (
424 id SERIAL PRIMARY KEY,
425 name TEXT NOT NULL UNIQUE
427 COMMENT ON TABLE config.net_access_level IS $$
429 * Copyright (C) 2005 Georgia Public Library Service
430 * Mike Rylander <mrylander@gmail.com>
432 * Patron Network Access level
434 * This will be used to inform the in-library firewall of how much
435 * internet access the using patron should be allowed.
439 * This program is free software; you can redistribute it and/or
440 * modify it under the terms of the GNU General Public License
441 * as published by the Free Software Foundation; either version 2
442 * of the License, or (at your option) any later version.
444 * This program is distributed in the hope that it will be useful,
445 * but WITHOUT ANY WARRANTY; without even the implied warranty of
446 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
447 * GNU General Public License for more details.
451 CREATE TABLE config.audience_map (
452 code TEXT PRIMARY KEY,
457 CREATE TABLE config.lit_form_map (
458 code TEXT PRIMARY KEY,
463 CREATE TABLE config.language_map (
464 code TEXT PRIMARY KEY,
468 CREATE TABLE config.item_form_map (
469 code TEXT PRIMARY KEY,
473 CREATE TABLE config.item_type_map (
474 code TEXT PRIMARY KEY,
478 CREATE TABLE config.bib_level_map (
479 code TEXT PRIMARY KEY,
483 CREATE TABLE config.z3950_source (
484 name TEXT PRIMARY KEY,
485 label TEXT NOT NULL UNIQUE,
489 record_format TEXT NOT NULL DEFAULT 'FI',
490 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
491 auth BOOL NOT NULL DEFAULT TRUE
494 COMMENT ON TABLE config.z3950_source IS $$
497 Each row in this table represents a database searchable via Z39.50.
500 COMMENT ON COLUMN config.z3950_source.record_format IS $$
504 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
505 Z39.50 preferred record syntax..
509 CREATE TABLE config.z3950_attr (
510 id SERIAL PRIMARY KEY,
511 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
516 truncation INT NOT NULL DEFAULT 0,
517 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
520 CREATE TABLE config.i18n_locale (
521 code TEXT PRIMARY KEY,
522 marc_code TEXT NOT NULL REFERENCES config.language_map (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
523 name TEXT UNIQUE NOT NULL,
527 CREATE TABLE config.i18n_core (
528 id BIGSERIAL PRIMARY KEY,
529 fq_field TEXT NOT NULL,
530 identity_value TEXT NOT NULL,
531 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
535 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
537 CREATE TABLE config.billing_type (
538 id SERIAL PRIMARY KEY,
540 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
541 default_price NUMERIC(6,2),
542 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
545 CREATE TABLE config.org_unit_setting_type (
546 name TEXT PRIMARY KEY,
547 label TEXT UNIQUE NOT NULL,
549 datatype TEXT NOT NULL DEFAULT 'string',
552 -- define valid datatypes
554 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
555 ( 'bool', 'integer', 'float', 'currency', 'interval',
556 'date', 'string', 'object', 'array', 'link' ) ),
558 -- fm_class is meaningful only for 'link' datatype
560 CONSTRAINT coust_no_empty_link CHECK
561 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
562 ( datatype <> 'link' AND fm_class IS NULL ) )