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,
120 INSERT INTO config.standing_penalty (id,name,label,block_list)
121 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
122 INSERT INTO config.standing_penalty (id,name,label,block_list)
123 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
124 INSERT INTO config.standing_penalty (id,name,label,block_list)
125 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
126 INSERT INTO config.standing_penalty (id,name,label,block_list)
127 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
128 SELECT SETVAL('config.standing_penalty_id_seq', 100);
130 CREATE TABLE config.xml_transform (
131 name TEXT PRIMARY KEY,
132 namespace_uri TEXT NOT NULL,
133 prefix TEXT NOT NULL,
137 CREATE TABLE config.metabib_field (
138 id SERIAL PRIMARY KEY,
139 field_class TEXT NOT NULL CHECK (lower(field_class) IN ('title','author','subject','keyword','series')),
142 weight INT NOT NULL DEFAULT 1,
143 format TEXT NOT NULL DEFAULT 'mods33',
144 search_field BOOL NOT NULL DEFAULT TRUE,
145 facet_field BOOL NOT NULL DEFAULT FALSE
147 COMMENT ON TABLE config.metabib_field IS $$
149 * Copyright (C) 2005 Georgia Public Library Service
150 * Mike Rylander <mrylander@gmail.com>
152 * XPath used for record indexing ingest
154 * This table contains the XPath used to chop up MODS into its
155 * indexable parts. Each XPath entry is named and assigned to
156 * a "class" of either title, subject, author, keyword or series.
161 * This program is free software; you can redistribute it and/or
162 * modify it under the terms of the GNU General Public License
163 * as published by the Free Software Foundation; either version 2
164 * of the License, or (at your option) any later version.
166 * This program is distributed in the hope that it will be useful,
167 * but WITHOUT ANY WARRANTY; without even the implied warranty of
168 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
169 * GNU General Public License for more details.
173 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
175 CREATE TABLE config.non_cataloged_type (
176 id SERIAL PRIMARY KEY,
177 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
179 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
180 in_house BOOL NOT NULL DEFAULT FALSE,
181 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
183 COMMENT ON TABLE config.non_cataloged_type IS $$
185 * Copyright (C) 2005 Georgia Public Library Service
186 * Mike Rylander <mrylander@gmail.com>
188 * Types of valid non-cataloged items.
193 * This program is free software; you can redistribute it and/or
194 * modify it under the terms of the GNU General Public License
195 * as published by the Free Software Foundation; either version 2
196 * of the License, or (at your option) any later version.
198 * This program is distributed in the hope that it will be useful,
199 * but WITHOUT ANY WARRANTY; without even the implied warranty of
200 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
201 * GNU General Public License for more details.
205 CREATE TABLE config.identification_type (
206 id SERIAL PRIMARY KEY,
207 name TEXT NOT NULL UNIQUE
209 COMMENT ON TABLE config.identification_type IS $$
211 * Copyright (C) 2005 Georgia Public Library Service
212 * Mike Rylander <mrylander@gmail.com>
214 * Types of valid patron identification.
216 * Each patron must display at least one valid form of identification
217 * in order to get a library card. This table lists those forms.
222 * This program is free software; you can redistribute it and/or
223 * modify it under the terms of the GNU General Public License
224 * as published by the Free Software Foundation; either version 2
225 * of the License, or (at your option) any later version.
227 * This program is distributed in the hope that it will be useful,
228 * but WITHOUT ANY WARRANTY; without even the implied warranty of
229 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
230 * GNU General Public License for more details.
234 CREATE TABLE config.rule_circ_duration (
235 id SERIAL PRIMARY KEY,
236 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
237 extended INTERVAL NOT NULL,
238 normal INTERVAL NOT NULL,
239 shrt INTERVAL NOT NULL,
240 max_renewals INT NOT NULL
242 COMMENT ON TABLE config.rule_circ_duration IS $$
244 * Copyright (C) 2005 Georgia Public Library Service
245 * Mike Rylander <mrylander@gmail.com>
247 * Circulation Duration rules
249 * Each circulation is given a duration based on one of these rules.
254 * This program is free software; you can redistribute it and/or
255 * modify it under the terms of the GNU General Public License
256 * as published by the Free Software Foundation; either version 2
257 * of the License, or (at your option) any later version.
259 * This program is distributed in the hope that it will be useful,
260 * but WITHOUT ANY WARRANTY; without even the implied warranty of
261 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
262 * GNU General Public License for more details.
266 CREATE TABLE config.rule_max_fine (
267 id SERIAL PRIMARY KEY,
268 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
269 amount NUMERIC(6,2) NOT NULL,
270 is_percent BOOL NOT NULL DEFAULT FALSE
272 COMMENT ON TABLE config.rule_max_fine IS $$
274 * Copyright (C) 2005 Georgia Public Library Service
275 * Mike Rylander <mrylander@gmail.com>
277 * Circulation Max Fine rules
279 * Each circulation is given a maximum fine based on one of
285 * This program is free software; you can redistribute it and/or
286 * modify it under the terms of the GNU General Public License
287 * as published by the Free Software Foundation; either version 2
288 * of the License, or (at your option) any later version.
290 * This program is distributed in the hope that it will be useful,
291 * but WITHOUT ANY WARRANTY; without even the implied warranty of
292 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
293 * GNU General Public License for more details.
297 CREATE TABLE config.rule_recuring_fine (
298 id SERIAL PRIMARY KEY,
299 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
300 high NUMERIC(6,2) NOT NULL,
301 normal NUMERIC(6,2) NOT NULL,
302 low NUMERIC(6,2) NOT NULL,
303 recurance_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
305 COMMENT ON TABLE config.rule_recuring_fine IS $$
307 * Copyright (C) 2005 Georgia Public Library Service
308 * Mike Rylander <mrylander@gmail.com>
310 * Circulation Recurring Fine rules
312 * Each circulation is given a recurring fine amount based on one of
313 * these rules. The recurance_interval should not be any shorter
314 * than the interval between runs of the fine_processor.pl script
315 * (which is run from CRON), or you could miss fines.
320 * This program is free software; you can redistribute it and/or
321 * modify it under the terms of the GNU General Public License
322 * as published by the Free Software Foundation; either version 2
323 * of the License, or (at your option) any later version.
325 * This program is distributed in the hope that it will be useful,
326 * but WITHOUT ANY WARRANTY; without even the implied warranty of
327 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
328 * GNU General Public License for more details.
333 CREATE TABLE config.rule_age_hold_protect (
334 id SERIAL PRIMARY KEY,
335 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
336 age INTERVAL NOT NULL,
339 COMMENT ON TABLE config.rule_age_hold_protect IS $$
341 * Copyright (C) 2005 Georgia Public Library Service
342 * Mike Rylander <mrylander@gmail.com>
344 * Hold Item Age Protection rules
346 * A hold request can only capture new(ish) items when they are
347 * within a particular proximity of the home_ou of the requesting
348 * user. The proximity ('prox' column) is calculated by counting
349 * the number of tree edges between the user's home_ou and the owning_lib
350 * of the copy that could fulfill the hold.
355 * This program is free software; you can redistribute it and/or
356 * modify it under the terms of the GNU General Public License
357 * as published by the Free Software Foundation; either version 2
358 * of the License, or (at your option) any later version.
360 * This program is distributed in the hope that it will be useful,
361 * but WITHOUT ANY WARRANTY; without even the implied warranty of
362 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
363 * GNU General Public License for more details.
367 CREATE TABLE config.copy_status (
368 id SERIAL PRIMARY KEY,
369 name TEXT NOT NULL UNIQUE,
370 holdable BOOL NOT NULL DEFAULT FALSE,
371 opac_visible BOOL NOT NULL DEFAULT FALSE
373 COMMENT ON TABLE config.copy_status IS $$
375 * Copyright (C) 2005 Georgia Public Library Service
376 * Mike Rylander <mrylander@gmail.com>
380 * The available copy statuses, and whether a copy in that
381 * status is available for hold request capture. 0 (zero) is
382 * the only special number in this set, meaning that the item
383 * is available for immediate checkout, and is counted as available
386 * Statuses with an ID below 100 are not removable, and have special
387 * meaning in the code. Do not change them except to translate the
390 * You may add and remove statuses above 100, and these can be used
391 * to remove items from normal circulation without affecting the rest
392 * of the copy's values or its location.
396 * This program is free software; you can redistribute it and/or
397 * modify it under the terms of the GNU General Public License
398 * as published by the Free Software Foundation; either version 2
399 * of the License, or (at your option) any later version.
401 * This program is distributed in the hope that it will be useful,
402 * but WITHOUT ANY WARRANTY; without even the implied warranty of
403 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
404 * GNU General Public License for more details.
408 CREATE TABLE config.net_access_level (
409 id SERIAL PRIMARY KEY,
410 name TEXT NOT NULL UNIQUE
412 COMMENT ON TABLE config.net_access_level IS $$
414 * Copyright (C) 2005 Georgia Public Library Service
415 * Mike Rylander <mrylander@gmail.com>
417 * Patron Network Access level
419 * This will be used to inform the in-library firewall of how much
420 * internet access the using patron should be allowed.
424 * This program is free software; you can redistribute it and/or
425 * modify it under the terms of the GNU General Public License
426 * as published by the Free Software Foundation; either version 2
427 * of the License, or (at your option) any later version.
429 * This program is distributed in the hope that it will be useful,
430 * but WITHOUT ANY WARRANTY; without even the implied warranty of
431 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
432 * GNU General Public License for more details.
436 CREATE TABLE config.audience_map (
437 code TEXT PRIMARY KEY,
442 CREATE TABLE config.lit_form_map (
443 code TEXT PRIMARY KEY,
448 CREATE TABLE config.language_map (
449 code TEXT PRIMARY KEY,
453 CREATE TABLE config.item_form_map (
454 code TEXT PRIMARY KEY,
458 CREATE TABLE config.item_type_map (
459 code TEXT PRIMARY KEY,
463 CREATE TABLE config.bib_level_map (
464 code TEXT PRIMARY KEY,
468 CREATE TABLE config.z3950_source (
469 name TEXT PRIMARY KEY,
470 label TEXT NOT NULL UNIQUE,
474 record_format TEXT NOT NULL DEFAULT 'FI',
475 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
476 auth BOOL NOT NULL DEFAULT TRUE
479 CREATE TABLE config.z3950_attr (
480 id SERIAL PRIMARY KEY,
481 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
486 truncation INT NOT NULL DEFAULT 0,
487 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
490 CREATE TABLE config.i18n_locale (
491 code TEXT PRIMARY KEY,
492 marc_code TEXT NOT NULL REFERENCES config.language_map (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
493 name TEXT UNIQUE NOT NULL,
497 CREATE TABLE config.i18n_core (
498 id BIGSERIAL PRIMARY KEY,
499 fq_field TEXT NOT NULL,
500 identity_value TEXT NOT NULL,
501 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
505 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
507 CREATE TABLE config.billing_type (
508 id SERIAL PRIMARY KEY,
510 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
511 default_price NUMERIC(6,2),
512 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)