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