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