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