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