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.xml_transform (
115 name TEXT PRIMARY KEY,
116 namespace_uri TEXT NOT NULL,
117 prefix TEXT NOT NULL,
121 CREATE TABLE config.metabib_field (
122 id SERIAL PRIMARY KEY,
123 field_class TEXT NOT NULL CHECK (lower(field_class) IN ('title','author','subject','keyword','series')),
126 weight INT NOT NULL DEFAULT 1,
127 format TEXT NOT NULL DEFAULT 'mods32',
128 search_field BOOL NOT NULL DEFAULT TRUE,
129 facet_field BOOL NOT NULL DEFAULT FALSE
131 COMMENT ON TABLE config.metabib_field IS $$
133 * Copyright (C) 2005 Georgia Public Library Service
134 * Mike Rylander <mrylander@gmail.com>
136 * XPath used for record indexing ingest
138 * This table contains the XPath used to chop up MODS into its
139 * indexable parts. Each XPath entry is named and assigned to
140 * a "class" of either title, subject, author, keyword or series.
145 * This program is free software; you can redistribute it and/or
146 * modify it under the terms of the GNU General Public License
147 * as published by the Free Software Foundation; either version 2
148 * of the License, or (at your option) any later version.
150 * This program is distributed in the hope that it will be useful,
151 * but WITHOUT ANY WARRANTY; without even the implied warranty of
152 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
153 * GNU General Public License for more details.
157 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
159 CREATE TABLE config.non_cataloged_type (
160 id SERIAL PRIMARY KEY,
161 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
163 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
164 in_house BOOL NOT NULL DEFAULT FALSE,
165 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
167 COMMENT ON TABLE config.non_cataloged_type IS $$
169 * Copyright (C) 2005 Georgia Public Library Service
170 * Mike Rylander <mrylander@gmail.com>
172 * Types of valid non-cataloged items.
177 * This program is free software; you can redistribute it and/or
178 * modify it under the terms of the GNU General Public License
179 * as published by the Free Software Foundation; either version 2
180 * of the License, or (at your option) any later version.
182 * This program is distributed in the hope that it will be useful,
183 * but WITHOUT ANY WARRANTY; without even the implied warranty of
184 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
185 * GNU General Public License for more details.
189 CREATE TABLE config.identification_type (
190 id SERIAL PRIMARY KEY,
191 name TEXT NOT NULL UNIQUE
193 COMMENT ON TABLE config.identification_type IS $$
195 * Copyright (C) 2005 Georgia Public Library Service
196 * Mike Rylander <mrylander@gmail.com>
198 * Types of valid patron identification.
200 * Each patron must display at least one valid form of identification
201 * in order to get a library card. This table lists those forms.
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.rule_circ_duration (
219 id SERIAL PRIMARY KEY,
220 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
221 extended INTERVAL NOT NULL,
222 normal INTERVAL NOT NULL,
223 shrt INTERVAL NOT NULL,
224 max_renewals INT NOT NULL
226 COMMENT ON TABLE config.rule_circ_duration IS $$
228 * Copyright (C) 2005 Georgia Public Library Service
229 * Mike Rylander <mrylander@gmail.com>
231 * Circulation Duration rules
233 * Each circulation is given a duration based on one of these rules.
238 * This program is free software; you can redistribute it and/or
239 * modify it under the terms of the GNU General Public License
240 * as published by the Free Software Foundation; either version 2
241 * of the License, or (at your option) any later version.
243 * This program is distributed in the hope that it will be useful,
244 * but WITHOUT ANY WARRANTY; without even the implied warranty of
245 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
246 * GNU General Public License for more details.
250 CREATE TABLE config.rule_max_fine (
251 id SERIAL PRIMARY KEY,
252 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
253 amount NUMERIC(6,2) NOT NULL,
254 is_percent BOOL NOT NULL DEFAULT FALSE
256 COMMENT ON TABLE config.rule_max_fine IS $$
258 * Copyright (C) 2005 Georgia Public Library Service
259 * Mike Rylander <mrylander@gmail.com>
261 * Circulation Max Fine rules
263 * Each circulation is given a maximum fine based on one of
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_recuring_fine (
282 id SERIAL PRIMARY KEY,
283 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
284 high NUMERIC(6,2) NOT NULL,
285 normal NUMERIC(6,2) NOT NULL,
286 low NUMERIC(6,2) NOT NULL,
287 recurance_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
289 COMMENT ON TABLE config.rule_recuring_fine IS $$
291 * Copyright (C) 2005 Georgia Public Library Service
292 * Mike Rylander <mrylander@gmail.com>
294 * Circulation Recurring Fine rules
296 * Each circulation is given a recurring fine amount based on one of
297 * these rules. The recurance_interval should not be any shorter
298 * than the interval between runs of the fine_processor.pl script
299 * (which is run from CRON), or you could miss fines.
304 * This program is free software; you can redistribute it and/or
305 * modify it under the terms of the GNU General Public License
306 * as published by the Free Software Foundation; either version 2
307 * of the License, or (at your option) any later version.
309 * This program is distributed in the hope that it will be useful,
310 * but WITHOUT ANY WARRANTY; without even the implied warranty of
311 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
312 * GNU General Public License for more details.
317 CREATE TABLE config.rule_age_hold_protect (
318 id SERIAL PRIMARY KEY,
319 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
320 age INTERVAL NOT NULL,
323 COMMENT ON TABLE config.rule_age_hold_protect IS $$
325 * Copyright (C) 2005 Georgia Public Library Service
326 * Mike Rylander <mrylander@gmail.com>
328 * Hold Item Age Protection rules
330 * A hold request can only capture new(ish) items when they are
331 * within a particular proximity of the home_ou of the requesting
332 * user. The proximity ('prox' column) is calculated by counting
333 * the number of tree edges between the user's home_ou and the owning_lib
334 * of the copy that could fulfill the hold.
339 * This program is free software; you can redistribute it and/or
340 * modify it under the terms of the GNU General Public License
341 * as published by the Free Software Foundation; either version 2
342 * of the License, or (at your option) any later version.
344 * This program is distributed in the hope that it will be useful,
345 * but WITHOUT ANY WARRANTY; without even the implied warranty of
346 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
347 * GNU General Public License for more details.
351 CREATE TABLE config.copy_status (
352 id SERIAL PRIMARY KEY,
353 name TEXT NOT NULL UNIQUE,
354 holdable BOOL NOT NULL DEFAULT FALSE,
355 opac_visible BOOL NOT NULL DEFAULT FALSE
357 COMMENT ON TABLE config.copy_status IS $$
359 * Copyright (C) 2005 Georgia Public Library Service
360 * Mike Rylander <mrylander@gmail.com>
364 * The available copy statuses, and whether a copy in that
365 * status is available for hold request capture. 0 (zero) is
366 * the only special number in this set, meaning that the item
367 * is available for immediate checkout, and is counted as available
370 * Statuses with an ID below 100 are not removable, and have special
371 * meaning in the code. Do not change them except to translate the
374 * You may add and remove statuses above 100, and these can be used
375 * to remove items from normal circulation without affecting the rest
376 * of the copy's values or its location.
380 * This program is free software; you can redistribute it and/or
381 * modify it under the terms of the GNU General Public License
382 * as published by the Free Software Foundation; either version 2
383 * of the License, or (at your option) any later version.
385 * This program is distributed in the hope that it will be useful,
386 * but WITHOUT ANY WARRANTY; without even the implied warranty of
387 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
388 * GNU General Public License for more details.
392 CREATE TABLE config.net_access_level (
393 id SERIAL PRIMARY KEY,
394 name TEXT NOT NULL UNIQUE
396 COMMENT ON TABLE config.net_access_level IS $$
398 * Copyright (C) 2005 Georgia Public Library Service
399 * Mike Rylander <mrylander@gmail.com>
401 * Patron Network Access level
403 * This will be used to inform the in-library firewall of how much
404 * internet access the using patron should be allowed.
408 * This program is free software; you can redistribute it and/or
409 * modify it under the terms of the GNU General Public License
410 * as published by the Free Software Foundation; either version 2
411 * of the License, or (at your option) any later version.
413 * This program is distributed in the hope that it will be useful,
414 * but WITHOUT ANY WARRANTY; without even the implied warranty of
415 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
416 * GNU General Public License for more details.
420 CREATE TABLE config.audience_map (
421 code TEXT PRIMARY KEY,
426 CREATE TABLE config.lit_form_map (
427 code TEXT PRIMARY KEY,
432 CREATE TABLE config.language_map (
433 code TEXT PRIMARY KEY,
437 CREATE TABLE config.item_form_map (
438 code TEXT PRIMARY KEY,
442 CREATE TABLE config.item_type_map (
443 code TEXT PRIMARY KEY,
447 CREATE TABLE config.bib_level_map (
448 code TEXT PRIMARY KEY,
452 CREATE TABLE config.z3950_source (
453 name TEXT PRIMARY KEY,
454 label TEXT NOT NULL UNIQUE,
458 record_format TEXT NOT NULL DEFAULT 'FI',
459 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
460 auth BOOL NOT NULL DEFAULT TRUE
463 CREATE TABLE config.z3950_attr (
464 id SERIAL PRIMARY KEY,
465 source TEXT NOT NULL REFERENCES config.z3950_source (name),
470 truncation INT NOT NULL DEFAULT 0,
471 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
474 CREATE TABLE config.i18n_locale (
475 code TEXT PRIMARY KEY,
476 marc_code TEXT NOT NULL REFERENCES config.language_map (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
477 name TEXT UNIQUE NOT NULL,
481 CREATE TABLE config.i18n_core (
482 id BIGSERIAL PRIMARY KEY,
483 fq_field TEXT NOT NULL,
484 identity_value TEXT NOT NULL,
485 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
489 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);