Open-ILS/src/sql/Pg/200.schema.acq.sql

   1 DROP SCHEMA acq CASCADE;
   2
   3 BEGIN;
   4
   5 CREATE SCHEMA acq;
   6
   7
   8 -- Tables
   9
  10
  11 CREATE TABLE acq.currency_type (
  12         code    TEXT PRIMARY KEY,
  13         label   TEXT
  14 );
  15
  16 -- Use the ISO 4217 abbreviations for currency codes
  17 INSERT INTO acq.currency_type (code, label) VALUES ('USD','US Dollars');
  18 INSERT INTO acq.currency_type (code, label) VALUES ('CAN','Canadian Dollars');
  19 INSERT INTO acq.currency_type (code, label) VALUES ('EUR','Euros');
  20
  21 CREATE TABLE acq.exchange_rate (
  22     id              SERIAL  PRIMARY KEY,
  23     from_currency   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
  24     to_currency     TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
  25     ratio           NUMERIC NOT NULL,
  26     CONSTRAINT exchange_rate_from_to_once UNIQUE (from_currency,to_currency)
  27 );
  28
  29 INSERT INTO acq.exchange_rate (from_currency,to_currency,ratio) VALUES ('USD','CAN',1.2);
  30 INSERT INTO acq.exchange_rate (from_currency,to_currency,ratio) VALUES ('USD','EUR',0.5);
  31
  32 CREATE TABLE acq.provider (
  33     id                  SERIAL  PRIMARY KEY,
  34     name                TEXT    NOT NULL,
  35     owner               INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  36     currency_type       TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
  37     code                TEXT    NOT NULL,
  38     holding_tag         TEXT,
  39     san                 TEXT,
  40     CONSTRAINT provider_name_once_per_owner UNIQUE (name,owner),
  41         CONSTRAINT code_once_per_owner UNIQUE (code, owner)
  42 );
  43
  44 CREATE TABLE acq.provider_holding_subfield_map (
  45     id          SERIAL  PRIMARY KEY,
  46     provider    INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  47     name        TEXT    NOT NULL, -- barcode, price, etc
  48     subfield    TEXT    NOT NULL,
  49     CONSTRAINT name_once_per_provider UNIQUE (provider,name)
  50 );
  51
  52 CREATE TABLE acq.provider_address (
  53         id              SERIAL  PRIMARY KEY,
  54         valid           BOOL    NOT NULL DEFAULT TRUE,
  55         address_type    TEXT,
  56     provider    INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  57         street1         TEXT    NOT NULL,
  58         street2         TEXT,
  59         city            TEXT    NOT NULL,
  60         county          TEXT,
  61         state           TEXT    NOT NULL,
  62         country         TEXT    NOT NULL,
  63         post_code       TEXT    NOT NULL
  64 );
  65
  66 CREATE TABLE acq.provider_contact (
  67         id              SERIAL  PRIMARY KEY,
  68     provider    INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
  69     name    TEXT NULL NULL,
  70     role    TEXT, -- free-form.. e.g. "our sales guy"
  71     email   TEXT,
  72     phone   TEXT
  73 );
  74
  75 CREATE TABLE acq.provider_contact_address (
  76         id                      SERIAL  PRIMARY KEY,
  77         valid                   BOOL    NOT NULL DEFAULT TRUE,
  78         address_type    TEXT,
  79         contact                 INT         NOT NULL REFERENCES acq.provider_contact (id) DEFERRABLE INITIALLY DEFERRED,
  80         street1                 TEXT    NOT NULL,
  81         street2                 TEXT,
  82         city                    TEXT    NOT NULL,
  83         county                  TEXT,
  84         state                   TEXT    NOT NULL,
  85         country                 TEXT    NOT NULL,
  86         post_code               TEXT    NOT NULL
  87 );
  88
  89
  90 CREATE TABLE acq.funding_source (
  91         id              SERIAL  PRIMARY KEY,
  92         name            TEXT    NOT NULL,
  93         owner           INT     NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  94         currency_type   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
  95         code            TEXT    UNIQUE,
  96         CONSTRAINT funding_source_name_once_per_owner UNIQUE (name,owner)
  97 );
  98
  99 CREATE TABLE acq.funding_source_credit (
 100         id      SERIAL     PRIMARY KEY,
 101         funding_source INT      NOT NULL REFERENCES acq.funding_source (id) DEFERRABLE INITIALLY DEFERRED,
 102         amount         NUMERIC  NOT NULL,
 103         note           TEXT,
 104         deadline_date  TIMESTAMPTZ,
 105         effective_date TIMESTAMPTZ NOT NULL default now()
 106 );
 107
 108 CREATE VIEW acq.ordered_funding_source_credit AS
 109     SELECT
 110         CASE WHEN deadline_date IS NULL THEN
 111             2
 112         ELSE
 113             1
 114         END AS sort_priority,
 115         CASE WHEN deadline_date IS NULL THEN
 116             effective_date
 117         ELSE
 118             deadline_date
 119         END AS sort_date,
 120         id,
 121         funding_source,
 122         amount,
 123         note
 124     FROM
 125         acq.funding_source_credit;
 126
 127 COMMENT ON VIEW acq.ordered_funding_source_credit IS $$
 128 /*
 129  * Copyright (C) 2009  Georgia Public Library Service
 130  * Scott McKellar <scott@gmail.com>
 131  *
 132  * The acq.ordered_funding_source_credit view is a prioritized
 133  * ordering of funding source credits.  When ordered by the first
 134  * three columns, this view defines the order in which the various
 135  * credits are to be tapped for spending, subject to the allocations
 136  * in the acq.fund_allocation table.
 137  *
 138  * The first column reflects the principle that we should spend
 139  * money with deadlines before spending money without deadlines.
 140  *
 141  * The second column reflects the principle that we should spend the
 142  * oldest money first.  For money with deadlines, that means that we
 143  * spend first from the credit with the earliest deadline.  For
 144  * money without deadlines, we spend first from the credit with the
 145  * earliest effective date.
 146  *
 147  * The third column is a tie breaker to ensure a consistent
 148  * ordering.
 149  *
 150  * ****
 151  *
 152  * This program is free software; you can redistribute it and/or
 153  * modify it under the terms of the GNU General Public License
 154  * as published by the Free Software Foundation; either version 2
 155  * of the License, or (at your option) any later version.
 156  *
 157  * This program is distributed in the hope that it will be useful,
 158  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 159  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 160  * GNU General Public License for more details.
 161  */
 162 $$;
 163
 164 CREATE TABLE acq.fund (
 165     id              SERIAL  PRIMARY KEY,
 166     org             INT     NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 167     name            TEXT    NOT NULL,
 168     year            INT     NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
 169     currency_type   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
 170     code            TEXT,
 171         rollover        BOOL    NOT NULL DEFAULT FALSE,
 172     CONSTRAINT name_once_per_org_year UNIQUE (org,name,year),
 173     CONSTRAINT code_once_per_org_year UNIQUE (org, code, year)
 174 );
 175
 176 CREATE TABLE acq.fund_debit (
 177         id                      SERIAL  PRIMARY KEY,
 178         fund                    INT     NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 179         origin_amount           NUMERIC NOT NULL,  -- pre-exchange-rate amount
 180         origin_currency_type    TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
 181         amount                  NUMERIC NOT NULL,
 182         encumbrance             BOOL    NOT NULL DEFAULT TRUE,
 183         debit_type              TEXT    NOT NULL,
 184         xfer_destination        INT     REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 185         create_time     TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
 186 );
 187
 188 CREATE TABLE acq.fund_allocation (
 189     id          SERIAL  PRIMARY KEY,
 190     funding_source        INT     NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 191     fund        INT     NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 192     amount      NUMERIC NOT NULL,
 193     allocator   INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 194     note        TEXT,
 195         create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
 196 );
 197 CREATE INDEX fund_alloc_allocator_idx ON acq.fund_allocation ( allocator );
 198
 199 CREATE TABLE acq.picklist (
 200         id              SERIAL                          PRIMARY KEY,
 201         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 202         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 203         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 204         org_unit        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 205         name            TEXT                            NOT NULL,
 206         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 207         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 208         CONSTRAINT name_once_per_owner UNIQUE (name,owner)
 209 );
 210 CREATE INDEX acq_picklist_owner_idx   ON acq.picklist ( owner );
 211 CREATE INDEX acq_picklist_creator_idx ON acq.picklist ( creator );
 212 CREATE INDEX acq_picklist_editor_idx  ON acq.picklist ( editor );
 213
 214 CREATE TABLE acq.purchase_order (
 215         id              SERIAL                          PRIMARY KEY,
 216         owner           INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 217         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 218         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 219         ordering_agency INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 220         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 221         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 222         provider        INT                             NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
 223         state                   TEXT                                    NOT NULL DEFAULT 'new',
 224         order_date              TIMESTAMP WITH TIME ZONE,
 225         name                    TEXT                                    NOT NULL
 226 );
 227 CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
 228 CREATE INDEX po_provider_idx ON acq.purchase_order (provider);
 229 CREATE INDEX po_state_idx ON acq.purchase_order (state);
 230 CREATE INDEX po_creator_idx  ON acq.purchase_order ( creator );
 231 CREATE INDEX po_editor_idx   ON acq.purchase_order ( editor );
 232 CREATE INDEX acq_po_org_name_order_date_idx ON acq.purchase_order( ordering_agency, name, order_date );
 233
 234 -- The name should default to the id, as text.  We can't reference a column
 235 -- in a DEFAULT clause, so we use a trigger:
 236
 237 CREATE OR REPLACE FUNCTION acq.purchase_order_name_default () RETURNS TRIGGER
 238 AS $$
 239 BEGIN
 240         IF NEW.name IS NULL THEN
 241                 NEW.name := NEW.id::TEXT;
 242         END IF;
 243
 244         RETURN NEW;
 245 END;
 246 $$ LANGUAGE PLPGSQL;
 247
 248 CREATE TRIGGER po_name_default_trg
 249   BEFORE INSERT OR UPDATE ON acq.purchase_order
 250   FOR EACH ROW EXECUTE PROCEDURE acq.purchase_order_name_default ();
 251
 252 -- The order name should be unique for a given ordering agency on a given order date
 253 -- (truncated to midnight), but only where the order_date is not NULL.  Conceptually
 254 -- this rule requires a check constraint with a subquery.  However you can't have a
 255 -- subquery in a CHECK constraint, so we fake it with a trigger.
 256
 257 CREATE OR REPLACE FUNCTION acq.po_org_name_date_unique () RETURNS TRIGGER
 258 AS $$
 259 DECLARE
 260         collision INT;
 261 BEGIN
 262         --
 263         -- If order_date is not null, then make sure we don't have a collision
 264         -- on order_date (truncated to day), org, and name
 265         --
 266         IF NEW.order_date IS NULL THEN
 267                 RETURN NEW;
 268         END IF;
 269         --
 270         -- In the WHERE clause, we compare the order_dates without regard to time of day.
 271         -- We use a pair of inequalities instead of comparing truncated dates so that the
 272         -- query can do an indexed range scan.
 273         --
 274         SELECT 1 INTO collision
 275         FROM acq.purchase_order
 276         WHERE
 277                 ordering_agency = NEW.ordering_agency
 278                 AND name = NEW.name
 279                 AND order_date >= date_trunc( 'day', NEW.order_date )
 280                 AND order_date <  date_trunc( 'day', NEW.order_date ) + '1 day'::INTERVAL
 281                 AND id <> NEW.id;
 282         --
 283         IF collision IS NULL THEN
 284                 -- okay, no collision
 285                 RETURN NEW;
 286         ELSE
 287                 -- collision; nip it in the bud
 288                 RAISE EXCEPTION 'Colliding purchase orders: ordering_agency %, date %, name ''%''',
 289                         NEW.ordering_agency, NEW.order_date, NEW.name;
 290         END IF;
 291 END;
 292 $$ LANGUAGE PLPGSQL;
 293
 294 CREATE TRIGGER po_org_name_date_unique_trg
 295   BEFORE INSERT OR UPDATE ON acq.purchase_order
 296   FOR EACH ROW EXECUTE PROCEDURE acq.po_org_name_date_unique ();
 297
 298 CREATE TABLE acq.po_note (
 299         id              SERIAL                          PRIMARY KEY,
 300         purchase_order  INT                             NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
 301         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 302         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 303         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 304         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 305         value           TEXT                            NOT NULL
 306 );
 307 CREATE INDEX po_note_po_idx ON acq.po_note (purchase_order);
 308 CREATE INDEX acq_po_note_creator_idx  ON acq.po_note ( creator );
 309 CREATE INDEX acq_po_note_editor_idx   ON acq.po_note ( editor );
 310
 311 CREATE TABLE acq.lineitem (
 312         id                  BIGSERIAL                   PRIMARY KEY,
 313         creator             INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 314         editor              INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 315         selector            INT                         NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 316         provider            INT                         REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
 317         purchase_order      INT                         REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
 318         picklist            INT                         REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
 319         expected_recv_time  TIMESTAMP WITH TIME ZONE,
 320         create_time         TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 321         edit_time           TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
 322         marc                TEXT                        NOT NULL,
 323         eg_bib_id           INT                         REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
 324         source_label        TEXT,
 325         item_count          INT                         NOT NULL DEFAULT 0,
 326         state               TEXT                        NOT NULL DEFAULT 'new',
 327     CONSTRAINT picklist_or_po CHECK (picklist IS NOT NULL OR purchase_order IS NOT NULL)
 328 );
 329 CREATE INDEX li_po_idx ON acq.lineitem (purchase_order);
 330 CREATE INDEX li_pl_idx ON acq.lineitem (picklist);
 331 CREATE INDEX li_creator_idx   ON acq.lineitem ( creator );
 332 CREATE INDEX li_editor_idx    ON acq.lineitem ( editor );
 333 CREATE INDEX li_selector_idx  ON acq.lineitem ( selector );
 334
 335 CREATE TABLE acq.lineitem_note (
 336         id              SERIAL                          PRIMARY KEY,
 337         lineitem        INT                             NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 338         creator         INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 339         editor          INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
 340         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 341         edit_time       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 342         value           TEXT                            NOT NULL
 343 );
 344 CREATE INDEX li_note_li_idx ON acq.lineitem_note (lineitem);
 345 CREATE INDEX li_note_creator_idx  ON acq.lineitem_note ( creator );
 346 CREATE INDEX li_note_editor_idx   ON acq.lineitem_note ( editor );
 347
 348 CREATE TABLE acq.lineitem_detail (
 349     id          BIGSERIAL       PRIMARY KEY,
 350     lineitem    INT         NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 351     fund        INT         REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 352     fund_debit  INT         REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
 353     eg_copy_id  BIGINT      REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 354     barcode     TEXT,
 355     cn_label    TEXT,
 356     note        TEXT,
 357     collection_code TEXT,
 358     circ_modifier   TEXT    REFERENCES config.circ_modifier (code) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 359     owning_lib  INT         REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 360     location    INT         REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 361     recv_time   TIMESTAMP WITH TIME ZONE
 362 );
 363
 364 CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem);
 365
 366 CREATE TABLE acq.lineitem_attr_definition (
 367         id              BIGSERIAL       PRIMARY KEY,
 368         code            TEXT            NOT NULL,
 369         description     TEXT            NOT NULL,
 370         remove          TEXT            NOT NULL DEFAULT '',
 371         ident           BOOL            NOT NULL DEFAULT FALSE
 372 );
 373
 374 CREATE TABLE acq.lineitem_marc_attr_definition (
 375         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 376         xpath           TEXT            NOT NULL
 377 ) INHERITS (acq.lineitem_attr_definition);
 378
 379 CREATE TABLE acq.lineitem_provider_attr_definition (
 380         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 381         xpath           TEXT            NOT NULL,
 382         provider        INT     NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
 383 ) INHERITS (acq.lineitem_attr_definition);
 384
 385 CREATE TABLE acq.lineitem_generated_attr_definition (
 386         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 387         xpath           TEXT            NOT NULL
 388 ) INHERITS (acq.lineitem_attr_definition);
 389
 390 CREATE TABLE acq.lineitem_usr_attr_definition (
 391         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
 392         usr             INT     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
 393 ) INHERITS (acq.lineitem_attr_definition);
 394 CREATE INDEX li_usr_attr_def_usr_idx  ON acq.lineitem_usr_attr_definition ( usr );
 395
 396 CREATE TABLE acq.lineitem_local_attr_definition (
 397         id              BIGINT  PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq')
 398 ) INHERITS (acq.lineitem_attr_definition);
 399
 400 CREATE TABLE acq.lineitem_attr (
 401         id              BIGSERIAL       PRIMARY KEY,
 402         definition      BIGINT          NOT NULL,
 403         lineitem        BIGINT          NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
 404         attr_type       TEXT            NOT NULL,
 405         attr_name       TEXT            NOT NULL,
 406         attr_value      TEXT            NOT NULL
 407 );
 408
 409 CREATE INDEX li_attr_li_idx ON acq.lineitem_attr (lineitem);
 410 CREATE INDEX li_attr_value_idx ON acq.lineitem_attr (attr_value);
 411 CREATE INDEX li_attr_definition_idx ON acq.lineitem_attr (definition);
 412
 413
 414 -- Seed data
 415
 416
 417 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('title','Title of work','//*[@tag="245"]/*[contains("abcmnopr",@code)]');
 418 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('author','Author of work','//*[@tag="100" or @tag="110" or @tag="113"]/*[contains("ad",@code)]');
 419 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('language','Language of work','//*[@tag="240"]/*[@code="l"][1]');
 420 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pagination','Pagination','//*[@tag="300"]/*[@code="a"][1]');
 421 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('isbn','ISBN','//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
 422 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath, remove ) VALUES ('issn','ISSN','//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$);
 423 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('price','Price','//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
 424 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('identifier','Identifier','//*[@tag="001"]');
 425 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('publisher','Publisher','//*[@tag="260"]/*[@code="b"][1]');
 426 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('pubdate','Publication Date','//*[@tag="260"]/*[@code="c"][1]');
 427 INSERT INTO acq.lineitem_marc_attr_definition ( code, description, xpath ) VALUES ('edition','Edition','//*[@tag="250"]/*[@code="a"][1]');
 428
 429 INSERT INTO acq.lineitem_local_attr_definition ( code, description ) VALUES ('estimated_price', 'Estimated Price');
 430
 431
 432 CREATE TABLE acq.distribution_formula (
 433         id              SERIAL PRIMARY KEY,
 434         owner   INT NOT NULL
 435                         REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
 436         name    TEXT NOT NULL,
 437         skip_count      INT NOT NULL DEFAULT 0,
 438         CONSTRAINT acqdf_name_once_per_owner UNIQUE (name, owner)
 439 );
 440
 441 CREATE TABLE acq.distribution_formula_entry (
 442         id                      SERIAL PRIMARY KEY,
 443         formula         INTEGER NOT NULL REFERENCES acq.distribution_formula(id)
 444                                 ON DELETE CASCADE
 445                                 DEFERRABLE INITIALLY DEFERRED,
 446         position        INTEGER NOT NULL,
 447         item_count      INTEGER NOT NULL,
 448         owning_lib      INTEGER REFERENCES actor.org_unit(id)
 449                                 DEFERRABLE INITIALLY DEFERRED,
 450         location        INTEGER REFERENCES asset.copy_location(id),
 451         CONSTRAINT acqdfe_lib_once_per_formula UNIQUE( formula, position ),
 452         CONSTRAINT acqdfe_must_be_somewhere
 453                                 CHECK( owning_lib IS NOT NULL OR location IS NOT NULL )
 454 );
 455
 456 CREATE TABLE acq.fund_tag (
 457         id              SERIAL PRIMARY KEY,
 458         owner   INT NOT NULL
 459                         REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
 460         name    TEXT NOT NULL,
 461         CONSTRAINT acqft_tag_once_per_owner UNIQUE (name, owner)
 462 );
 463
 464 CREATE TABLE acq.fund_tag_map (
 465         id                      SERIAL PRIMARY KEY,
 466         fund            INTEGER NOT NULL REFERENCES acq.fund(id)
 467                                 DEFERRABLE INITIALLY DEFERRED,
 468         tag         INTEGER REFERENCES acq.fund_tag(id)
 469                                 ON DELETE CASCADE
 470                                 DEFERRABLE INITIALLY DEFERRED,
 471         CONSTRAINT acqftm_fund_once_per_tag UNIQUE( fund, tag )
 472 );
 473
 474 CREATE TABLE acq.fund_transfer (
 475     id               SERIAL         PRIMARY KEY,
 476     src_fund         INT            NOT NULL REFERENCES acq.fund( id )
 477                                     DEFERRABLE INITIALLY DEFERRED,
 478     src_amount       NUMERIC        NOT NULL,
 479     dest_fund        INT            NOT NULL REFERENCES acq.fund( id )
 480                                     DEFERRABLE INITIALLY DEFERRED,
 481     dest_amount      NUMERIC        NOT NULL,
 482     transfer_time    TIMESTAMPTZ    NOT NULL DEFAULT now(),
 483     transfer_user    INT            NOT NULL REFERENCES actor.usr( id )
 484                                     DEFERRABLE INITIALLY DEFERRED,
 485     note             TEXT,
 486         funding_source_credit INT       NOT NULL REFERENCES acq.funding_source_credit( id )
 487                                     DEFERRABLE INITIALLY DEFERRED
 488 );
 489
 490 CREATE INDEX acqftr_usr_idx
 491 ON acq.fund_transfer( transfer_user );
 492
 493 COMMENT ON TABLE acq.fund_transfer IS $$
 494 /*
 495  * Copyright (C) 2009  Georgia Public Library Service
 496  * Scott McKellar <scott@esilibrary.com>
 497  *
 498  * Fund Transfer
 499  *
 500  * Each row represents the transfer of money from a source fund
 501  * to a destination fund.  There should be corresponding entries
 502  * in acq.fund_allocation.  The purpose of acq.fund_transfer is
 503  * to record how much money moved from which fund to which other
 504  * fund.
 505  *
 506  * The presence of two amount fields, rather than one, reflects
 507  * the possibility that the two funds are denominated in different
 508  * currencies.  If they use the same currency type, the two
 509  * amounts should be the same.
 510  *
 511  * ****
 512  *
 513  * This program is free software; you can redistribute it and/or
 514  * modify it under the terms of the GNU General Public License
 515  * as published by the Free Software Foundation; either version 2
 516  * of the License, or (at your option) any later version.
 517  *
 518  * This program is distributed in the hope that it will be useful,
 519  * but WITHOUT ANY WARRANTY; without even the implied warranty of
 520  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 521  * GNU General Public License for more details.
 522  */
 523 $$;
 524
 525 CREATE TABLE acq.fiscal_calendar (
 526         id              SERIAL         PRIMARY KEY,
 527         name            TEXT           NOT NULL
 528 );
 529
 530 -- Create a default calendar (though we don't specify its contents).
 531 -- Create a foreign key in actor.org_unit, initially pointing to
 532 -- the default calendar.
 533
 534 INSERT INTO acq.fiscal_calendar (
 535     name
 536 ) VALUES (
 537
 538     'Default'
 539 );
 540
 541 ALTER TABLE actor.org_unit
 542 ADD COLUMN fiscal_calendar INT NOT NULL
 543     REFERENCES acq.fiscal_calendar( id )
 544     DEFERRABLE INITIALLY DEFERRED
 545     DEFAULT 1;
 546
 547 CREATE TABLE acq.fiscal_year (
 548         id              SERIAL         PRIMARY KEY,
 549         calendar        INT            NOT NULL
 550                                        REFERENCES acq.fiscal_calendar
 551                                        ON DELETE CASCADE
 552                                        DEFERRABLE INITIALLY DEFERRED,
 553         year            INT            NOT NULL,
 554         year_begin      TIMESTAMPTZ    NOT NULL,
 555         year_end        TIMESTAMPTZ    NOT NULL,
 556         CONSTRAINT acq_fy_logical_key  UNIQUE ( calendar, year ),
 557     CONSTRAINT acq_fy_physical_key UNIQUE ( calendar, year_begin )
 558 );
 559
 560 -- Functions
 561
 562 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
 563 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
 564 DECLARE
 565     counter INT;
 566     lida    acq.flat_lineitem_holding_subfield%ROWTYPE;
 567 BEGIN
 568
 569     SELECT  COUNT(*) INTO counter
 570       FROM  xpath_table(
 571                 'id',
 572                 'marc',
 573                 'acq.lineitem',
 574                 '//*[@tag="' || tag || '"]',
 575                 'id=' || lineitem
 576             ) as t(i int,c text);
 577
 578     FOR i IN 1 .. counter LOOP
 579         FOR lida IN
 580             SELECT  *
 581               FROM  (   SELECT  id,i,t,v
 582                           FROM  xpath_table(
 583                                     'id',
 584                                     'marc',
 585                                     'acq.lineitem',
 586                                     '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
 587                                         '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
 588                                     'id=' || lineitem
 589                                 ) as t(id int,t text,v text)
 590                     )x
 591         LOOP
 592             RETURN NEXT lida;
 593         END LOOP;
 594     END LOOP;
 595
 596     RETURN;
 597 END;
 598 $$ LANGUAGE PLPGSQL;
 599
 600 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
 601 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
 602 DECLARE
 603     prov_i  INT;
 604     tag_t   TEXT;
 605     lida    acq.flat_lineitem_detail%ROWTYPE;
 606 BEGIN
 607     SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
 608     IF NOT FOUND THEN RETURN; END IF;
 609
 610     SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
 611     IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
 612
 613     FOR lida IN
 614         SELECT  lineitem_i,
 615                 h.holding,
 616                 a.name,
 617                 h.data
 618           FROM  acq.extract_holding_attr_table( lineitem_i, tag_t ) h
 619                 JOIN acq.provider_holding_subfield_map a USING (subfield)
 620           WHERE a.provider = prov_i
 621     LOOP
 622         RETURN NEXT lida;
 623     END LOOP;
 624
 625     RETURN;
 626 END;
 627 $$ LANGUAGE PLPGSQL;
 628
 629 -- select * from acq.extract_provider_holding_data(699);
 630
 631 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
 632         SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
 633 $$ LANGUAGE SQL;
 634
 635 /*
 636 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 637         SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
 638 $$ LANGUAGE SQL;
 639
 640 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 641         SELECT public.extract_marc_field('authority.record_entry', $1, $2);
 642 $$ LANGUAGE SQL;
 643 */
 644 -- For example:
 645 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
 646
 647 /*
 648 Suggested vendor fields:
 649         vendor_price
 650         vendor_currency
 651         vendor_avail
 652         vendor_po
 653         vendor_identifier
 654 */
 655
 656 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
 657 DECLARE
 658         value           TEXT;
 659         atype           TEXT;
 660         prov            INT;
 661         adef            RECORD;
 662         xpath_string    TEXT;
 663 BEGIN
 664         FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
 665
 666                 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
 667
 668                 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
 669                         IF (atype = 'lineitem_provider_attr_definition') THEN
 670                                 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 671                                 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
 672                         END IF;
 673
 674                         IF (atype = 'lineitem_provider_attr_definition') THEN
 675                                 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 676                         ELSIF (atype = 'lineitem_marc_attr_definition') THEN
 677                                 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
 678                         ELSIF (atype = 'lineitem_generated_attr_definition') THEN
 679                                 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
 680                         END IF;
 681
 682                         SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
 683
 684                         IF (value IS NOT NULL AND value <> '') THEN
 685                                 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
 686                                         VALUES (NEW.id, adef.id, atype, adef.code, value);
 687                         END IF;
 688
 689                 END IF;
 690
 691         END LOOP;
 692
 693         RETURN NULL;
 694 END;
 695 $$ LANGUAGE PLPGSQL;
 696
 697 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
 698 BEGIN
 699         IF TG_OP = 'UPDATE' THEN
 700                 DELETE FROM acq.lineitem_attr
 701                         WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
 702                 RETURN NEW;
 703         ELSE
 704                 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
 705                 RETURN OLD;
 706         END IF;
 707 END;
 708 $$ LANGUAGE PLPGSQL;
 709
 710 CREATE TRIGGER cleanup_lineitem_trigger
 711         BEFORE UPDATE OR DELETE ON acq.lineitem
 712         FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
 713
 714 CREATE TRIGGER ingest_lineitem_trigger
 715         AFTER INSERT OR UPDATE ON acq.lineitem
 716         FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
 717
 718 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
 719 DECLARE
 720     rat NUMERIC;
 721 BEGIN
 722     IF from_ex = to_ex THEN
 723         RETURN 1.0;
 724     END IF;
 725
 726     SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
 727
 728     IF FOUND THEN
 729         RETURN rat;
 730     ELSE
 731         SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
 732         IF FOUND THEN
 733             RETURN 1.0/rat;
 734         END IF;
 735     END IF;
 736
 737     RETURN NULL;
 738
 739 END;
 740 $$ LANGUAGE PLPGSQL;
 741
 742 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
 743     SELECT $3 * acq.exchange_ratio($1, $2);
 744 $$ LANGUAGE SQL;
 745
 746 CREATE OR REPLACE FUNCTION acq.find_bad_fy()
 747 /*
 748         Examine the acq.fiscal_year table, comparing successive years.
 749         Report any inconsistencies, i.e. years that overlap, have gaps
 750     between them, or are out of sequence.
 751 */
 752 RETURNS SETOF RECORD AS $$
 753 DECLARE
 754         first_row  BOOLEAN;
 755         curr_year  RECORD;
 756         prev_year  RECORD;
 757         return_rec RECORD;
 758 BEGIN
 759         first_row := true;
 760         FOR curr_year in
 761                 SELECT
 762                         id,
 763                         calendar,
 764                         year,
 765                         year_begin,
 766                         year_end
 767                 FROM
 768                         acq.fiscal_year
 769                 ORDER BY
 770                         calendar,
 771                         year_begin
 772         LOOP
 773                 --
 774                 IF first_row THEN
 775                         first_row := FALSE;
 776                 ELSIF curr_year.calendar    = prev_year.calendar THEN
 777                         IF curr_year.year_begin > prev_year.year_end THEN
 778                                 -- This ugly kludge works around the fact that older
 779                                 -- versions of PostgreSQL don't support RETURN QUERY SELECT
 780                                 FOR return_rec IN SELECT
 781                                         prev_year.id,
 782                                         prev_year.year,
 783                                         'Gap between fiscal years'::TEXT
 784                                 LOOP
 785                                         RETURN NEXT return_rec;
 786                                 END LOOP;
 787                         ELSIF curr_year.year_begin < prev_year.year_end THEN
 788                                 FOR return_rec IN SELECT
 789                                         prev_year.id,
 790                                         prev_year.year,
 791                                         'Overlapping fiscal years'::TEXT
 792                                 LOOP
 793                                         RETURN NEXT return_rec;
 794                                 END LOOP;
 795                         ELSIF curr_year.year < prev_year.year THEN
 796                                 FOR return_rec IN SELECT
 797                                         prev_year.id,
 798                                         prev_year.year,
 799                                         'Fiscal years out of order'::TEXT
 800                                 LOOP
 801                                         RETURN NEXT return_rec;
 802                                 END LOOP;
 803                         END IF;
 804                 END IF;
 805                 --
 806                 prev_year := curr_year;
 807         END LOOP;
 808         --
 809         RETURN;
 810 END;
 811 $$ LANGUAGE plpgsql;
 812
 813 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
 814     SELECT  funding_source,
 815             SUM(amount) AS amount
 816       FROM  acq.funding_source_credit
 817       GROUP BY 1;
 818
 819 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
 820     SELECT  funding_source,
 821             SUM(a.amount)::NUMERIC(100,2) AS amount
 822     FROM  acq.fund_allocation a
 823     GROUP BY 1;
 824
 825 CREATE OR REPLACE VIEW acq.funding_source_balance AS
 826     SELECT  COALESCE(c.funding_source, a.funding_source) AS funding_source,
 827             SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
 828       FROM  acq.funding_source_credit_total c
 829             FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
 830       GROUP BY 1;
 831
 832 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
 833     SELECT  fund,
 834             SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
 835     FROM acq.fund_allocation a
 836          JOIN acq.fund f ON (a.fund = f.id)
 837          JOIN acq.funding_source s ON (a.funding_source = s.id)
 838     GROUP BY 1;
 839
 840 CREATE OR REPLACE VIEW acq.fund_debit_total AS
 841     SELECT  id AS fund,
 842             encumbrance,
 843             SUM(amount) AS amount
 844       FROM  acq.fund_debit
 845       GROUP BY 1,2;
 846
 847 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
 848     SELECT  fund,
 849             SUM(amount) AS amount
 850       FROM  acq.fund_debit_total
 851       WHERE encumbrance IS TRUE
 852       GROUP BY 1;
 853
 854 CREATE OR REPLACE VIEW acq.fund_spent_total AS
 855     SELECT  fund,
 856             SUM(amount) AS amount
 857       FROM  acq.fund_debit_total
 858       WHERE encumbrance IS FALSE
 859       GROUP BY 1;
 860
 861 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
 862     SELECT  c.fund,
 863             c.amount - COALESCE(d.amount,0.0) AS amount
 864       FROM  acq.fund_allocation_total c
 865             LEFT JOIN acq.fund_debit_total d USING (fund);
 866
 867 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
 868     SELECT  c.fund,
 869             c.amount - COALESCE(d.amount,0.0) AS amount
 870       FROM  acq.fund_allocation_total c
 871             LEFT JOIN acq.fund_spent_total d USING (fund);
 872
 873 COMMIT;
 874
 875
 876
 877