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 CREATE TABLE config.bib_source (
55 id SERIAL PRIMARY KEY,
56 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
57 source TEXT NOT NULL UNIQUE,
58 transcendant BOOL NOT NULL DEFAULT FALSE
60 COMMENT ON TABLE config.bib_source IS $$
62 * Copyright (C) 2005 Georgia Public Library Service
63 * Mike Rylander <mrylander@gmail.com>
65 * Valid sources of MARC records
67 * This is table is used to set up the relative "quality" of each
68 * MARC source, such as OCLC.
72 * This program is free software; you can redistribute it and/or
73 * modify it under the terms of the GNU General Public License
74 * as published by the Free Software Foundation; either version 2
75 * of the License, or (at your option) any later version.
77 * This program is distributed in the hope that it will be useful,
78 * but WITHOUT ANY WARRANTY; without even the implied warranty of
79 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
80 * GNU General Public License for more details.
84 CREATE TABLE config.standing (
85 id SERIAL PRIMARY KEY,
86 value TEXT NOT NULL UNIQUE
88 COMMENT ON TABLE config.standing IS $$
90 * Copyright (C) 2005 Georgia Public Library Service
91 * Mike Rylander <mrylander@gmail.com>
95 * This table contains the values that can be applied to a patron
96 * by a staff member. These values should not be changed, other
97 * than for translation, as the ID column is currently a "magic
98 * number" in the source. :(
102 * This program is free software; you can redistribute it and/or
103 * modify it under the terms of the GNU General Public License
104 * as published by the Free Software Foundation; either version 2
105 * of the License, or (at your option) any later version.
107 * This program is distributed in the hope that it will be useful,
108 * but WITHOUT ANY WARRANTY; without even the implied warranty of
109 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
110 * GNU General Public License for more details.
114 CREATE TABLE config.standing_penalty (
115 id SERIAL PRIMARY KEY,
116 name TEXT NOT NULL UNIQUE,
121 INSERT INTO config.standing_penalty (id,name,label,block_list)
122 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
123 INSERT INTO config.standing_penalty (id,name,label,block_list)
124 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
125 INSERT INTO config.standing_penalty (id,name,label,block_list)
126 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
127 INSERT INTO config.standing_penalty (id,name,label,block_list)
128 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
130 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
131 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
132 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
133 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
134 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
135 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
136 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
137 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
138 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
139 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
141 SELECT SETVAL('config.standing_penalty_id_seq', 100);
143 CREATE TABLE config.xml_transform (
144 name TEXT PRIMARY KEY,
145 namespace_uri TEXT NOT NULL,
146 prefix TEXT NOT NULL,
150 CREATE TABLE config.metabib_field (
151 id SERIAL PRIMARY KEY,
152 field_class TEXT NOT NULL CHECK (lower(field_class) IN ('title','author','subject','keyword','series')),
155 weight INT NOT NULL DEFAULT 1,
156 format TEXT NOT NULL DEFAULT 'mods33',
157 search_field BOOL NOT NULL DEFAULT TRUE,
158 facet_field BOOL NOT NULL DEFAULT FALSE
160 COMMENT ON TABLE config.metabib_field IS $$
162 * Copyright (C) 2005 Georgia Public Library Service
163 * Mike Rylander <mrylander@gmail.com>
165 * XPath used for record indexing ingest
167 * This table contains the XPath used to chop up MODS into its
168 * indexable parts. Each XPath entry is named and assigned to
169 * a "class" of either title, subject, author, keyword or series.
174 * This program is free software; you can redistribute it and/or
175 * modify it under the terms of the GNU General Public License
176 * as published by the Free Software Foundation; either version 2
177 * of the License, or (at your option) any later version.
179 * This program is distributed in the hope that it will be useful,
180 * but WITHOUT ANY WARRANTY; without even the implied warranty of
181 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
182 * GNU General Public License for more details.
186 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
188 CREATE TABLE config.non_cataloged_type (
189 id SERIAL PRIMARY KEY,
190 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
192 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
193 in_house BOOL NOT NULL DEFAULT FALSE,
194 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
196 COMMENT ON TABLE config.non_cataloged_type IS $$
198 * Copyright (C) 2005 Georgia Public Library Service
199 * Mike Rylander <mrylander@gmail.com>
201 * Types of valid non-cataloged items.
206 * This program is free software; you can redistribute it and/or
207 * modify it under the terms of the GNU General Public License
208 * as published by the Free Software Foundation; either version 2
209 * of the License, or (at your option) any later version.
211 * This program is distributed in the hope that it will be useful,
212 * but WITHOUT ANY WARRANTY; without even the implied warranty of
213 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
214 * GNU General Public License for more details.
218 CREATE TABLE config.identification_type (
219 id SERIAL PRIMARY KEY,
220 name TEXT NOT NULL UNIQUE
222 COMMENT ON TABLE config.identification_type IS $$
224 * Copyright (C) 2005 Georgia Public Library Service
225 * Mike Rylander <mrylander@gmail.com>
227 * Types of valid patron identification.
229 * Each patron must display at least one valid form of identification
230 * in order to get a library card. This table lists those forms.
235 * This program is free software; you can redistribute it and/or
236 * modify it under the terms of the GNU General Public License
237 * as published by the Free Software Foundation; either version 2
238 * of the License, or (at your option) any later version.
240 * This program is distributed in the hope that it will be useful,
241 * but WITHOUT ANY WARRANTY; without even the implied warranty of
242 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
243 * GNU General Public License for more details.
247 CREATE TABLE config.rule_circ_duration (
248 id SERIAL PRIMARY KEY,
249 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
250 extended INTERVAL NOT NULL,
251 normal INTERVAL NOT NULL,
252 shrt INTERVAL NOT NULL,
253 max_renewals INT NOT NULL
255 COMMENT ON TABLE config.rule_circ_duration IS $$
257 * Copyright (C) 2005 Georgia Public Library Service
258 * Mike Rylander <mrylander@gmail.com>
260 * Circulation Duration rules
262 * Each circulation is given a duration based on one of these rules.
267 * This program is free software; you can redistribute it and/or
268 * modify it under the terms of the GNU General Public License
269 * as published by the Free Software Foundation; either version 2
270 * of the License, or (at your option) any later version.
272 * This program is distributed in the hope that it will be useful,
273 * but WITHOUT ANY WARRANTY; without even the implied warranty of
274 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
275 * GNU General Public License for more details.
279 CREATE TABLE config.rule_max_fine (
280 id SERIAL PRIMARY KEY,
281 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
282 amount NUMERIC(6,2) NOT NULL,
283 is_percent BOOL NOT NULL DEFAULT FALSE
285 COMMENT ON TABLE config.rule_max_fine IS $$
287 * Copyright (C) 2005 Georgia Public Library Service
288 * Mike Rylander <mrylander@gmail.com>
290 * Circulation Max Fine rules
292 * Each circulation is given a maximum fine based on one of
298 * This program is free software; you can redistribute it and/or
299 * modify it under the terms of the GNU General Public License
300 * as published by the Free Software Foundation; either version 2
301 * of the License, or (at your option) any later version.
303 * This program is distributed in the hope that it will be useful,
304 * but WITHOUT ANY WARRANTY; without even the implied warranty of
305 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
306 * GNU General Public License for more details.
310 CREATE TABLE config.rule_recuring_fine (
311 id SERIAL PRIMARY KEY,
312 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
313 high NUMERIC(6,2) NOT NULL,
314 normal NUMERIC(6,2) NOT NULL,
315 low NUMERIC(6,2) NOT NULL,
316 recurance_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
318 COMMENT ON TABLE config.rule_recuring_fine IS $$
320 * Copyright (C) 2005 Georgia Public Library Service
321 * Mike Rylander <mrylander@gmail.com>
323 * Circulation Recurring Fine rules
325 * Each circulation is given a recurring fine amount based on one of
326 * these rules. The recurance_interval should not be any shorter
327 * than the interval between runs of the fine_processor.pl script
328 * (which is run from CRON), or you could miss fines.
333 * This program is free software; you can redistribute it and/or
334 * modify it under the terms of the GNU General Public License
335 * as published by the Free Software Foundation; either version 2
336 * of the License, or (at your option) any later version.
338 * This program is distributed in the hope that it will be useful,
339 * but WITHOUT ANY WARRANTY; without even the implied warranty of
340 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
341 * GNU General Public License for more details.
346 CREATE TABLE config.rule_age_hold_protect (
347 id SERIAL PRIMARY KEY,
348 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
349 age INTERVAL NOT NULL,
352 COMMENT ON TABLE config.rule_age_hold_protect IS $$
354 * Copyright (C) 2005 Georgia Public Library Service
355 * Mike Rylander <mrylander@gmail.com>
357 * Hold Item Age Protection rules
359 * A hold request can only capture new(ish) items when they are
360 * within a particular proximity of the home_ou of the requesting
361 * user. The proximity ('prox' column) is calculated by counting
362 * the number of tree edges between the user's home_ou and the owning_lib
363 * of the copy that could fulfill the hold.
368 * This program is free software; you can redistribute it and/or
369 * modify it under the terms of the GNU General Public License
370 * as published by the Free Software Foundation; either version 2
371 * of the License, or (at your option) any later version.
373 * This program is distributed in the hope that it will be useful,
374 * but WITHOUT ANY WARRANTY; without even the implied warranty of
375 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
376 * GNU General Public License for more details.
380 CREATE TABLE config.copy_status (
381 id SERIAL PRIMARY KEY,
382 name TEXT NOT NULL UNIQUE,
383 holdable BOOL NOT NULL DEFAULT FALSE,
384 opac_visible BOOL NOT NULL DEFAULT FALSE
386 COMMENT ON TABLE config.copy_status IS $$
388 * Copyright (C) 2005 Georgia Public Library Service
389 * Mike Rylander <mrylander@gmail.com>
393 * The available copy statuses, and whether a copy in that
394 * status is available for hold request capture. 0 (zero) is
395 * the only special number in this set, meaning that the item
396 * is available for immediate checkout, and is counted as available
399 * Statuses with an ID below 100 are not removable, and have special
400 * meaning in the code. Do not change them except to translate the
403 * You may add and remove statuses above 100, and these can be used
404 * to remove items from normal circulation without affecting the rest
405 * of the copy's values or its location.
409 * This program is free software; you can redistribute it and/or
410 * modify it under the terms of the GNU General Public License
411 * as published by the Free Software Foundation; either version 2
412 * of the License, or (at your option) any later version.
414 * This program is distributed in the hope that it will be useful,
415 * but WITHOUT ANY WARRANTY; without even the implied warranty of
416 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
417 * GNU General Public License for more details.
421 CREATE TABLE config.net_access_level (
422 id SERIAL PRIMARY KEY,
423 name TEXT NOT NULL UNIQUE
425 COMMENT ON TABLE config.net_access_level IS $$
427 * Copyright (C) 2005 Georgia Public Library Service
428 * Mike Rylander <mrylander@gmail.com>
430 * Patron Network Access level
432 * This will be used to inform the in-library firewall of how much
433 * internet access the using patron should be allowed.
437 * This program is free software; you can redistribute it and/or
438 * modify it under the terms of the GNU General Public License
439 * as published by the Free Software Foundation; either version 2
440 * of the License, or (at your option) any later version.
442 * This program is distributed in the hope that it will be useful,
443 * but WITHOUT ANY WARRANTY; without even the implied warranty of
444 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
445 * GNU General Public License for more details.
449 CREATE TABLE config.audience_map (
450 code TEXT PRIMARY KEY,
455 CREATE TABLE config.lit_form_map (
456 code TEXT PRIMARY KEY,
461 CREATE TABLE config.language_map (
462 code TEXT PRIMARY KEY,
466 CREATE TABLE config.item_form_map (
467 code TEXT PRIMARY KEY,
471 CREATE TABLE config.item_type_map (
472 code TEXT PRIMARY KEY,
476 CREATE TABLE config.bib_level_map (
477 code TEXT PRIMARY KEY,
481 CREATE TABLE config.z3950_source (
482 name TEXT PRIMARY KEY,
483 label TEXT NOT NULL UNIQUE,
487 record_format TEXT NOT NULL DEFAULT 'FI',
488 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
489 auth BOOL NOT NULL DEFAULT TRUE
492 CREATE TABLE config.z3950_attr (
493 id SERIAL PRIMARY KEY,
494 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
499 truncation INT NOT NULL DEFAULT 0,
500 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
503 CREATE TABLE config.i18n_locale (
504 code TEXT PRIMARY KEY,
505 marc_code TEXT NOT NULL REFERENCES config.language_map (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
506 name TEXT UNIQUE NOT NULL,
510 CREATE TABLE config.i18n_core (
511 id BIGSERIAL PRIMARY KEY,
512 fq_field TEXT NOT NULL,
513 identity_value TEXT NOT NULL,
514 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
518 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
520 CREATE TABLE config.billing_type (
521 id SERIAL PRIMARY KEY,
523 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
524 default_price NUMERIC(6,2),
525 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
528 CREATE TABLE config.org_unit_setting_type (
529 name TEXT PRIMARY KEY,
530 label TEXT UNIQUE NOT NULL,
532 datatype TEXT NOT NULL DEFAULT 'string',
535 -- define valid datatypes
537 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
538 ( 'bool', 'integer', 'float', 'currency', 'interval',
539 'date', 'string', 'object', 'array', 'link' ) ),
541 -- fm_class is meaningful only for 'link' datatype
543 CONSTRAINT coust_no_empty_link CHECK
544 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
545 ( datatype <> 'link' AND fm_class IS NULL ) )