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

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