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