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                 INT,
  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 );
 105
 106 CREATE TABLE acq.fund (
 107     id              SERIAL  PRIMARY KEY,
 108     org             INT     NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 109     name            TEXT    NOT NULL,
 110     year            INT     NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
 111     currency_type   TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
 112     code            TEXT,
 113     CONSTRAINT name_once_per_org_year UNIQUE (org,name,year),
 114     CONSTRAINT code_once_per_org_year UNIQUE (org, code, year)
 115 );
 116
 117 CREATE TABLE acq.fund_debit (
 118         id                      SERIAL  PRIMARY KEY,
 119         fund                    INT     NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 120         origin_amount           NUMERIC NOT NULL,  -- pre-exchange-rate amount
 121         origin_currency_type    TEXT    NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
 122         amount                  NUMERIC NOT NULL,
 123         encumbrance             BOOL    NOT NULL DEFAULT TRUE,
 124         debit_type              TEXT    NOT NULL,
 125         xfer_destination        INT     REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
 126         create_time     TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
 127 );
 128
 129 CREATE TABLE acq.fund_allocation (
 130     id          SERIAL  PRIMARY KEY,
 131     funding_source        INT     NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 132     fund        INT     NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 133     amount      NUMERIC,
 134     percent     NUMERIC CHECK (percent IS NULL OR percent BETWEEN 0.0 AND 100.0),
 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     CONSTRAINT allocation_amount_or_percent CHECK ((percent IS NULL AND amount IS NOT NULL) OR (percent IS NOT NULL AND amount IS NULL))
 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.fiscal_calendar (
 418         id              SERIAL         PRIMARY KEY,
 419         name            TEXT           NOT NULL
 420 );
 421
 422 CREATE TABLE acq.fiscal_year (
 423         id              SERIAL         PRIMARY KEY,
 424         calendar        INT            NOT NULL
 425                                        REFERENCES acq.fiscal_calendar
 426                                        ON DELETE CASCADE
 427                                        DEFERRABLE INITIALLY DEFERRED,
 428         year            INT            NOT NULL,
 429         year_begin      TIMESTAMPTZ    NOT NULL,
 430         year_end        TIMESTAMPTZ    NOT NULL,
 431         CONSTRAINT acq_fy_logical_key  UNIQUE ( calendar, year ),
 432     CONSTRAINT acq_fy_physical_key UNIQUE ( calendar, year_begin )
 433 );
 434
 435 -- Functions
 436
 437 CREATE TYPE acq.flat_lineitem_holding_subfield AS (lineitem int, holding int, subfield text, data text);
 438 CREATE OR REPLACE FUNCTION acq.extract_holding_attr_table (lineitem int, tag text) RETURNS SETOF acq.flat_lineitem_holding_subfield AS $$
 439 DECLARE
 440     counter INT;
 441     lida    acq.flat_lineitem_holding_subfield%ROWTYPE;
 442 BEGIN
 443
 444     SELECT  COUNT(*) INTO counter
 445       FROM  xpath_table(
 446                 'id',
 447                 'marc',
 448                 'acq.lineitem',
 449                 '//*[@tag="' || tag || '"]',
 450                 'id=' || lineitem
 451             ) as t(i int,c text);
 452
 453     FOR i IN 1 .. counter LOOP
 454         FOR lida IN
 455             SELECT  *
 456               FROM  (   SELECT  id,i,t,v
 457                           FROM  xpath_table(
 458                                     'id',
 459                                     'marc',
 460                                     'acq.lineitem',
 461                                     '//*[@tag="' || tag || '"][position()=' || i || ']/*/@code|' ||
 462                                         '//*[@tag="' || tag || '"][position()=' || i || ']/*[@code]',
 463                                     'id=' || lineitem
 464                                 ) as t(id int,t text,v text)
 465                     )x
 466         LOOP
 467             RETURN NEXT lida;
 468         END LOOP;
 469     END LOOP;
 470
 471     RETURN;
 472 END;
 473 $$ LANGUAGE PLPGSQL;
 474
 475 CREATE TYPE acq.flat_lineitem_detail AS (lineitem int, holding int, attr text, data text);
 476 CREATE OR REPLACE FUNCTION acq.extract_provider_holding_data ( lineitem_i int ) RETURNS SETOF acq.flat_lineitem_detail AS $$
 477 DECLARE
 478     prov_i  INT;
 479     tag_t   TEXT;
 480     lida    acq.flat_lineitem_detail%ROWTYPE;
 481 BEGIN
 482     SELECT provider INTO prov_i FROM acq.lineitem WHERE id = lineitem_i;
 483     IF NOT FOUND THEN RETURN; END IF;
 484
 485     SELECT holding_tag INTO tag_t FROM acq.provider WHERE id = prov_i;
 486     IF NOT FOUND OR tag_t IS NULL THEN RETURN; END IF;
 487
 488     FOR lida IN
 489         SELECT  lineitem_i,
 490                 h.holding,
 491                 a.name,
 492                 h.data
 493           FROM  acq.extract_holding_attr_table( lineitem_i, tag_t ) h
 494                 JOIN acq.provider_holding_subfield_map a USING (subfield)
 495           WHERE a.provider = prov_i
 496     LOOP
 497         RETURN NEXT lida;
 498     END LOOP;
 499
 500     RETURN;
 501 END;
 502 $$ LANGUAGE PLPGSQL;
 503
 504 -- select * from acq.extract_provider_holding_data(699);
 505
 506 CREATE OR REPLACE FUNCTION public.extract_acq_marc_field ( BIGINT, TEXT, TEXT) RETURNS TEXT AS $$
 507         SELECT public.extract_marc_field('acq.lineitem', $1, $2, $3);
 508 $$ LANGUAGE SQL;
 509
 510 /*
 511 CREATE OR REPLACE FUNCTION public.extract_bib_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 512         SELECT public.extract_marc_field('biblio.record_entry', $1, $2);
 513 $$ LANGUAGE SQL;
 514
 515 CREATE OR REPLACE FUNCTION public.extract_authority_marc_field ( BIGINT, TEXT ) RETURNS TEXT AS $$
 516         SELECT public.extract_marc_field('authority.record_entry', $1, $2);
 517 $$ LANGUAGE SQL;
 518 */
 519 -- For example:
 520 -- INSERT INTO acq.lineitem_provider_attr_definition ( provider, code, description, xpath ) VALUES (1,'price','Price','//*[@tag="020" or @tag="022"]/*[@code="a"][1]');
 521
 522 /*
 523 Suggested vendor fields:
 524         vendor_price
 525         vendor_currency
 526         vendor_avail
 527         vendor_po
 528         vendor_identifier
 529 */
 530
 531 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $$
 532 DECLARE
 533         value           TEXT;
 534         atype           TEXT;
 535         prov            INT;
 536         adef            RECORD;
 537         xpath_string    TEXT;
 538 BEGIN
 539         FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
 540
 541                 SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
 542
 543                 IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
 544                         IF (atype = 'lineitem_provider_attr_definition') THEN
 545                                 SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 546                                 CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
 547                         END IF;
 548
 549                         IF (atype = 'lineitem_provider_attr_definition') THEN
 550                                 SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
 551                         ELSIF (atype = 'lineitem_marc_attr_definition') THEN
 552                                 SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
 553                         ELSIF (atype = 'lineitem_generated_attr_definition') THEN
 554                                 SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
 555                         END IF;
 556
 557                         SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
 558
 559                         IF (value IS NOT NULL AND value <> '') THEN
 560                                 INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
 561                                         VALUES (NEW.id, adef.id, atype, adef.code, value);
 562                         END IF;
 563
 564                 END IF;
 565
 566         END LOOP;
 567
 568         RETURN NULL;
 569 END;
 570 $$ LANGUAGE PLPGSQL;
 571
 572 CREATE OR REPLACE FUNCTION public.cleanup_acq_marc ( ) RETURNS TRIGGER AS $$
 573 BEGIN
 574         IF TG_OP = 'UPDATE' THEN
 575                 DELETE FROM acq.lineitem_attr
 576                         WHERE lineitem = OLD.id AND attr_type IN ('lineitem_provider_attr_definition', 'lineitem_marc_attr_definition','lineitem_generated_attr_definition');
 577                 RETURN NEW;
 578         ELSE
 579                 DELETE FROM acq.lineitem_attr WHERE lineitem = OLD.id;
 580                 RETURN OLD;
 581         END IF;
 582 END;
 583 $$ LANGUAGE PLPGSQL;
 584
 585 CREATE TRIGGER cleanup_lineitem_trigger
 586         BEFORE UPDATE OR DELETE ON acq.lineitem
 587         FOR EACH ROW EXECUTE PROCEDURE public.cleanup_acq_marc();
 588
 589 CREATE TRIGGER ingest_lineitem_trigger
 590         AFTER INSERT OR UPDATE ON acq.lineitem
 591         FOR EACH ROW EXECUTE PROCEDURE public.ingest_acq_marc();
 592
 593 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( from_ex TEXT, to_ex TEXT ) RETURNS NUMERIC AS $$
 594 DECLARE
 595     rat NUMERIC;
 596 BEGIN
 597     IF from_ex = to_ex THEN
 598         RETURN 1.0;
 599     END IF;
 600
 601     SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = from_ex AND to_currency = to_ex;
 602
 603     IF FOUND THEN
 604         RETURN rat;
 605     ELSE
 606         SELECT ratio INTO rat FROM acq.exchange_rate WHERE from_currency = to_ex AND to_currency = from_ex;
 607         IF FOUND THEN
 608             RETURN 1.0/rat;
 609         END IF;
 610     END IF;
 611
 612     RETURN NULL;
 613
 614 END;
 615 $$ LANGUAGE PLPGSQL;
 616
 617 CREATE OR REPLACE FUNCTION acq.exchange_ratio ( TEXT, TEXT, NUMERIC ) RETURNS NUMERIC AS $$
 618     SELECT $3 * acq.exchange_ratio($1, $2);
 619 $$ LANGUAGE SQL;
 620
 621 CREATE OR REPLACE FUNCTION acq.find_bad_fy()
 622 /*
 623         Examine the acq.fiscal_year table, comparing successive years.
 624         Report any inconsistencies, i.e. years that overlap, have gaps
 625     between them, or are out of sequence.
 626 */
 627 RETURNS SETOF RECORD AS $$
 628 DECLARE
 629         first_row  BOOLEAN;
 630         curr_year  RECORD;
 631         prev_year  RECORD;
 632         return_rec RECORD;
 633 BEGIN
 634         first_row := true;
 635         FOR curr_year in
 636                 SELECT
 637                         id,
 638                         calendar,
 639                         year,
 640                         year_begin,
 641                         year_end
 642                 FROM
 643                         acq.fiscal_year
 644                 ORDER BY
 645                         calendar,
 646                         year_begin
 647         LOOP
 648                 --
 649                 IF first_row THEN
 650                         first_row := FALSE;
 651                 ELSIF curr_year.calendar    = prev_year.calendar THEN
 652                         IF curr_year.year_begin > prev_year.year_end THEN
 653                                 -- This ugly kludge works around the fact that older
 654                                 -- versions of PostgreSQL don't support RETURN QUERY SELECT
 655                                 FOR return_rec IN SELECT
 656                                         prev_year.id,
 657                                         prev_year.year,
 658                                         'Gap between fiscal years'::TEXT
 659                                 LOOP
 660                                         RETURN NEXT return_rec;
 661                                 END LOOP;
 662                         ELSIF curr_year.year_begin < prev_year.year_end THEN
 663                                 FOR return_rec IN SELECT
 664                                         prev_year.id,
 665                                         prev_year.year,
 666                                         'Overlapping fiscal years'::TEXT
 667                                 LOOP
 668                                         RETURN NEXT return_rec;
 669                                 END LOOP;
 670                         ELSIF curr_year.year < prev_year.year THEN
 671                                 FOR return_rec IN SELECT
 672                                         prev_year.id,
 673                                         prev_year.year,
 674                                         'Fiscal years out of order'::TEXT
 675                                 LOOP
 676                                         RETURN NEXT return_rec;
 677                                 END LOOP;
 678                         END IF;
 679                 END IF;
 680                 --
 681                 prev_year := curr_year;
 682         END LOOP;
 683         --
 684         RETURN;
 685 END;
 686 $$ LANGUAGE plpgsql;
 687
 688 CREATE OR REPLACE VIEW acq.funding_source_credit_total AS
 689     SELECT  funding_source,
 690             SUM(amount) AS amount
 691       FROM  acq.funding_source_credit
 692       GROUP BY 1;
 693
 694 CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS
 695     SELECT  funding_source,
 696             SUM(amount)::NUMERIC(100,2) AS amount
 697       FROM (
 698             SELECT  funding_source,
 699                     SUM(a.amount)::NUMERIC(100,2) AS amount
 700               FROM  acq.fund_allocation a
 701               WHERE a.percent IS NULL
 702               GROUP BY 1
 703                             UNION ALL
 704             SELECT  funding_source,
 705                     SUM( (SELECT SUM(amount) FROM acq.funding_source_credit c WHERE c.funding_source = a.funding_source) * (a.percent/100.0) )::NUMERIC(100,2) AS amount
 706               FROM  acq.fund_allocation a
 707               WHERE a.amount IS NULL
 708               GROUP BY 1
 709         ) x
 710       GROUP BY 1;
 711
 712 CREATE OR REPLACE VIEW acq.funding_source_balance AS
 713     SELECT  COALESCE(c.funding_source, a.funding_source) AS funding_source,
 714             SUM(COALESCE(c.amount,0.0) - COALESCE(a.amount,0.0))::NUMERIC(100,2) AS amount
 715       FROM  acq.funding_source_credit_total c
 716             FULL JOIN acq.funding_source_allocation_total a USING (funding_source)
 717       GROUP BY 1;
 718
 719 CREATE OR REPLACE VIEW acq.fund_allocation_total AS
 720     SELECT  fund,
 721             SUM(amount)::NUMERIC(100,2) AS amount
 722       FROM (
 723             SELECT  fund,
 724                     SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount
 725               FROM  acq.fund_allocation a
 726                     JOIN acq.fund f ON (a.fund = f.id)
 727                     JOIN acq.funding_source s ON (a.funding_source = s.id)
 728               WHERE a.percent IS NULL
 729               GROUP BY 1
 730                             UNION ALL
 731             SELECT  fund,
 732                     SUM( (SELECT SUM(amount) FROM acq.funding_source_credit c WHERE c.funding_source = a.funding_source) * acq.exchange_ratio(s.currency_type, f.currency_type) * (a.percent/100.0) )::NUMERIC(100,2) AS amount
 733               FROM  acq.fund_allocation a
 734                     JOIN acq.fund f ON (a.fund = f.id)
 735                     JOIN acq.funding_source s ON (a.funding_source = s.id)
 736               WHERE a.amount IS NULL
 737               GROUP BY 1
 738         ) x
 739       GROUP BY 1;
 740
 741 CREATE OR REPLACE VIEW acq.fund_debit_total AS
 742     SELECT  id AS fund,
 743             encumbrance,
 744             SUM(amount) AS amount
 745       FROM  acq.fund_debit
 746       GROUP BY 1,2;
 747
 748 CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
 749     SELECT  fund,
 750             SUM(amount) AS amount
 751       FROM  acq.fund_debit_total
 752       WHERE encumbrance IS TRUE
 753       GROUP BY 1;
 754
 755 CREATE OR REPLACE VIEW acq.fund_spent_total AS
 756     SELECT  fund,
 757             SUM(amount) AS amount
 758       FROM  acq.fund_debit_total
 759       WHERE encumbrance IS FALSE
 760       GROUP BY 1;
 761
 762 CREATE OR REPLACE VIEW acq.fund_combined_balance AS
 763     SELECT  c.fund,
 764             c.amount - COALESCE(d.amount,0.0) AS amount
 765       FROM  acq.fund_allocation_total c
 766             LEFT JOIN acq.fund_debit_total d USING (fund);
 767
 768 CREATE OR REPLACE VIEW acq.fund_spent_balance AS
 769     SELECT  c.fund,
 770             c.amount - COALESCE(d.amount,0.0) AS amount
 771       FROM  acq.fund_allocation_total c
 772             LEFT JOIN acq.fund_spent_total d USING (fund);
 773
 774 COMMIT;
 775
 776
 777
 778