CREATE TABLE acq.exchange_rate (
id SERIAL PRIMARY KEY,
- from_currency TEXT NOT NULL REFERENCES acq.currency_type (code),
- to_currency TEXT NOT NULL REFERENCES acq.currency_type (code),
+ from_currency TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
+ to_currency TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
ratio NUMERIC NOT NULL,
CONSTRAINT exchange_rate_from_to_once UNIQUE (from_currency,to_currency)
);
CREATE TABLE acq.provider (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
- owner INT NOT NULL REFERENCES actor.org_unit (id),
- currency_type TEXT NOT NULL REFERENCES acq.currency_type (code),
+ owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
+ currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
code TEXT UNIQUE,
CONSTRAINT provider_name_once_per_owner UNIQUE (name,owner)
);
CREATE TABLE acq.funding_source (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
- owner INT NOT NULL REFERENCES actor.org_unit (id),
- currency_type TEXT NOT NULL REFERENCES acq.currency_type (code),
+ owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
+ currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
code TEXT UNIQUE,
CONSTRAINT funding_source_name_once_per_owner UNIQUE (name,owner)
);
CREATE TABLE acq.funding_source_credit (
id SERIAL PRIMARY KEY,
- funding_source INT NOT NULL REFERENCES acq.funding_source (id),
+ funding_source INT NOT NULL REFERENCES acq.funding_source (id) DEFERRABLE INITIALLY DEFERRED,
amount NUMERIC NOT NULL,
note TEXT
);
CREATE TABLE acq.fund (
id SERIAL PRIMARY KEY,
- org INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE,
+ org INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
name TEXT NOT NULL,
year INT NOT NULL DEFAULT EXTRACT( YEAR FROM NOW() ),
- currency_type TEXT NOT NULL REFERENCES acq.currency_type (code),
+ currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
code TEXT UNIQUE,
CONSTRAINT name_once_per_org_year UNIQUE (org,name,year)
);
CREATE TABLE acq.fund_debit (
id SERIAL PRIMARY KEY,
- fund INT NOT NULL REFERENCES acq.fund (id),
+ fund INT NOT NULL REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
origin_amount NUMERIC NOT NULL, -- pre-exchange-rate amount
- origin_currency_type TEXT NOT NULL REFERENCES acq.currency_type (code),
+ origin_currency_type TEXT NOT NULL REFERENCES acq.currency_type (code) DEFERRABLE INITIALLY DEFERRED,
amount NUMERIC NOT NULL,
encumbrance BOOL NOT NULL DEFAULT TRUE,
debit_type TEXT NOT NULL,
- xfer_destination INT REFERENCES acq.fund (id)
+ xfer_destination INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED
);
CREATE TABLE acq.fund_allocation (
id SERIAL PRIMARY KEY,
- funding_source INT NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE,
- fund INT NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE,
+ funding_source INT NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ fund INT NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
amount NUMERIC,
percent NUMERIC CHECK (percent IS NULL OR percent BETWEEN 0.0 AND 100.0),
- allocator INT NOT NULL REFERENCES actor.usr (id),
+ allocator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
note TEXT,
CONSTRAINT allocation_amount_or_percent CHECK ((percent IS NULL AND amount IS NOT NULL) OR (percent IS NOT NULL AND amount IS NULL))
);
CREATE TABLE acq.picklist (
id SERIAL PRIMARY KEY,
- owner INT NOT NULL REFERENCES actor.usr (id),
- org_unit INT NOT NULL REFERENCES actor.org_unit (id),
+ owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
+ org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
name TEXT NOT NULL,
create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
CREATE TABLE acq.purchase_order (
id SERIAL PRIMARY KEY,
- owner INT NOT NULL REFERENCES actor.usr (id),
- ordering_agency INT NOT NULL REFERENCES actor.org_unit (id),
+ owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
+ ordering_agency INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
- provider INT NOT NULL REFERENCES acq.provider (id),
+ provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
state TEXT NOT NULL DEFAULT 'new'
);
CREATE INDEX po_owner_idx ON acq.purchase_order (owner);
CREATE TABLE acq.po_note (
id SERIAL PRIMARY KEY,
- purchase_order INT NOT NULL REFERENCES acq.purchase_order (id),
- creator INT NOT NULL REFERENCES actor.usr (id),
- editor INT NOT NULL REFERENCES actor.usr (id),
+ purchase_order INT NOT NULL REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
+ creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
+ editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
value TEXT NOT NULL
CREATE TABLE acq.lineitem (
id BIGSERIAL PRIMARY KEY,
- selector INT NOT NULL REFERENCES actor.org_unit (id),
- provider INT REFERENCES acq.provider (id),
- purchase_order INT REFERENCES acq.purchase_order (id),
- picklist INT REFERENCES acq.picklist (id),
+ selector INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
+ provider INT REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED,
+ purchase_order INT REFERENCES acq.purchase_order (id) DEFERRABLE INITIALLY DEFERRED,
+ picklist INT REFERENCES acq.picklist (id) DEFERRABLE INITIALLY DEFERRED,
expected_recv_time TIMESTAMP WITH TIME ZONE,
create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
marc TEXT NOT NULL,
- eg_bib_id INT REFERENCES biblio.record_entry (id),
+ eg_bib_id INT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
source_label TEXT,
item_count INT NOT NULL DEFAULT 0,
state TEXT NOT NULL DEFAULT 'new',
CREATE TABLE acq.lineitem_note (
id SERIAL PRIMARY KEY,
- lineitem INT NOT NULL REFERENCES acq.lineitem (id),
- creator INT NOT NULL REFERENCES actor.usr (id),
- editor INT NOT NULL REFERENCES actor.usr (id),
+ lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
+ creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
+ editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
value TEXT NOT NULL
CREATE TABLE acq.lineitem_detail (
id BIGSERIAL PRIMARY KEY,
- lineitem INT NOT NULL REFERENCES acq.lineitem (id),
- fund INT REFERENCES acq.fund (id),
- fund_debit INT REFERENCES acq.fund_debit (id),
- eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL,
+ lineitem INT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
+ fund INT REFERENCES acq.fund (id) DEFERRABLE INITIALLY DEFERRED,
+ fund_debit INT REFERENCES acq.fund_debit (id) DEFERRABLE INITIALLY DEFERRED,
+ eg_copy_id BIGINT REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
barcode TEXT,
cn_label TEXT,
- owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL,
- location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL,
+ owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
+ location INT REFERENCES asset.copy_location (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
recv_time TIMESTAMP WITH TIME ZONE
);
CREATE TABLE acq.lineitem_provider_attr_definition (
id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
xpath TEXT NOT NULL,
- provider INT NOT NULL REFERENCES acq.provider (id)
+ provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED
) INHERITS (acq.lineitem_attr_definition);
CREATE TABLE acq.lineitem_generated_attr_definition (
CREATE TABLE acq.lineitem_usr_attr_definition (
id BIGINT PRIMARY KEY DEFAULT NEXTVAL('acq.lineitem_attr_definition_id_seq'),
- usr INT NOT NULL REFERENCES actor.usr (id)
+ usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED
) INHERITS (acq.lineitem_attr_definition);
CREATE TABLE acq.lineitem_local_attr_definition (
CREATE TABLE acq.lineitem_attr (
id BIGSERIAL PRIMARY KEY,
definition BIGINT NOT NULL,
- lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id),
+ lineitem BIGINT NOT NULL REFERENCES acq.lineitem (id) DEFERRABLE INITIALLY DEFERRED,
attr_type TEXT NOT NULL,
attr_name TEXT NOT NULL,
attr_value TEXT NOT NULL
CREATE TABLE action_trigger.event_definition (
id SERIAL PRIMARY KEY,
active BOOL NOT NULL DEFAULT TRUE,
- owner INT NOT NULL REFERENCES actor.org_unit (id),
- hook TEXT NOT NULL REFERENCES action_trigger.hook (key),
- validator TEXT NOT NULL REFERENCES action_trigger.validator (module),
- reactor TEXT NOT NULL REFERENCES action_trigger.reactor (module),
- cleanup_success TEXT REFERENCES action_trigger.cleanup (module),
- cleanup_failure TEXT REFERENCES action_trigger.cleanup (module),
+ owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
+ hook TEXT NOT NULL REFERENCES action_trigger.hook (key) DEFERRABLE INITIALLY DEFERRED,
+ validator TEXT NOT NULL REFERENCES action_trigger.validator (module) DEFERRABLE INITIALLY DEFERRED,
+ reactor TEXT NOT NULL REFERENCES action_trigger.reactor (module) DEFERRABLE INITIALLY DEFERRED,
+ cleanup_success TEXT REFERENCES action_trigger.cleanup (module) DEFERRABLE INITIALLY DEFERRED,
+ cleanup_failure TEXT REFERENCES action_trigger.cleanup (module) DEFERRABLE INITIALLY DEFERRED,
delay INTERVAL NOT NULL DEFAULT '5 minutes',
delay_field TEXT, -- for instance, xact_start on a circ hook ... look for fields on hook.core_type where datatype=timestamp? If not set, delay from now()
group_field TEXT, -- field from this.hook.core_type to batch event targets together on, fed into reactor a group at a time.
CREATE TABLE action_trigger.environment (
id SERIAL PRIMARY KEY,
- event_def INT NOT NULL REFERENCES action_trigger.event_definition (id),
+ event_def INT NOT NULL REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
path TEXT, -- fields to flesh. given a hook with a core_type of circ, imagine circ_lib.parent_ou expanding to
-- {flesh: 2, flesh_fields: {circ: ['circ_lib'], aou: ['parent_ou']}} ... default is to flesh all
-- at flesh depth 1
- collector TEXT REFERENCES action_trigger.collector (module), -- if set, given the object at 'path', return some data
+ collector TEXT REFERENCES action_trigger.collector (module) DEFERRABLE INITIALLY DEFERRED, -- if set, given the object at 'path', return some data
-- to be stashed at environment.<label>
label TEXT CHECK (label NOT IN ('result','target','event')),
CONSTRAINT env_event_label_once UNIQUE (event_def,label)
CREATE TABLE action_trigger.event (
id BIGSERIAL PRIMARY KEY,
target BIGINT NOT NULL, -- points at the id from class defined by event_def.hook.core_type
- event_def INT REFERENCES action_trigger.event_definition (id),
+ event_def INT REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
add_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
run_time TIMESTAMPTZ NOT NULL,
start_time TIMESTAMPTZ,
CREATE TABLE action_trigger.event_params (
id BIGSERIAL PRIMARY KEY,
- event_def INT NOT NULL REFERENCES action_trigger.event_definition (id),
+ event_def INT NOT NULL REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
param TEXT NOT NULL, -- the key under environment.event.params to store the output of ...
- value TEXT NOT NULL, -- ... the eval() output of this. Has access to environmen (and, well, all of perl)
+ value TEXT NOT NULL, -- ... the eval() output of this. Has access to environment (and, well, all of perl)
CONSTRAINT event_params_event_def_param_once UNIQUE (event_def,param)
);
projects / working / Evergreen.git / commitdiff