From d0b6c6b1c7c5b59bf33d80aab8ed2f95b45354b7 Mon Sep 17 00:00:00 2001 From: miker Date: Mon, 9 May 2005 15:44:23 +0000 Subject: [PATCH] field name adjustments git-svn-id: svn://svn.open-ils.org/ILS/trunk@680 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../OpenILS/Application/Storage/CDBI.pm | 10 +- .../OpenILS/Application/Storage/CDBI/asset.pm | 2 +- .../Application/Storage/Driver/Pg/dbi.pm | 8 +- .../Application/Storage/Publisher/metabib.pm | 186 ++++++++++-------- .../src/sql/Postgres/005.schema.actors.sql | 29 ++- .../src/sql/Postgres/040.schema.asset.sql | 12 +- .../src/sql/Postgres/090.schema.action.sql | 10 +- Open-ILS/src/sql/Postgres/800.fkeys.sql | 71 ++++--- 8 files changed, 183 insertions(+), 145 deletions(-) diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI.pm index 7f192beb3e..f7243dcc50 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI.pm @@ -246,12 +246,10 @@ sub modify_from_fieldmapper { } } -# my %hash = map { defined $fm->$_ ? -# ($_ => ''.$fm->$_) : -# () -# } $fm->real_fields; - - my %hash = map { ($_ => ''.$fm->$_) } $fm->real_fields; + my %hash = map { defined $fm->$_ ? + ($_ => ''.$fm->$_) : + () + } $fm->real_fields; my $au = $obj->autoupdate; $obj->autoupdate(0); diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/asset.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/asset.pm index 0a9f79be77..435e3386f9 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/asset.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/asset.pm @@ -26,7 +26,7 @@ use base qw/asset/; __PACKAGE__->table( 'asset_call_number_note' ); __PACKAGE__->columns( Primary => qw/id/ ); -__PACKAGE__->columns( Essential => qw/owning_call_number title creator create_date value/ ); +__PACKAGE__->columns( Essential => qw/call_number title creator create_date value/ ); #------------------------------------------------------------------------------- package asset::copy; diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm index 5081c1cae5..2a1c8fecab 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm @@ -98,14 +98,14 @@ #--------------------------------------------------------------------- package asset::call_number_note; - asset::call_number->table( 'asset.call_number_note' ); - asset::call_number->sequence( 'asset.call_number_note_id_seq' ); + asset::call_number_note->table( 'asset.call_number_note' ); + asset::call_number_note->sequence( 'asset.call_number_note_id_seq' ); #--------------------------------------------------------------------- package asset::copy_note; - asset::copy->table( 'asset.copy_note' ); - asset::copy->sequence( 'asset.copy_note_id_seq' ); + asset::copy_note->table( 'asset.copy_note' ); + asset::copy_note->sequence( 'asset.copy_note_id_seq' ); #--------------------------------------------------------------------- package asset::call_number; diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm index cdb64df8a8..3fd62c0341 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm @@ -26,6 +26,9 @@ sub metarecord_copy_count { my $descendants = "actor.org_unit_descendants(u.id)"; my $ancestors = "actor.org_unit_ancestors(?)"; + my $copies_visible = 'AND cp.opac_visible IS TRUE'; + $copies_visible = '' if ($self->api_name =~ /staff/o); + my $sql = <<" SQL"; SELECT t.depth, u.id AS org_unit, @@ -36,7 +39,8 @@ sub metarecord_copy_count { JOIN $cp_table cp ON (cn.id = cp.call_number) JOIN $descendants a ON (cp.circ_lib = a.id) WHERE r.metarecord = ? - AND cp.opac_visible IS TRUE) + $copies_visible + ) ) AS count, sum( (SELECT count(cp.id) @@ -46,7 +50,8 @@ sub metarecord_copy_count { JOIN $descendants a ON (cp.circ_lib = a.id) WHERE r.metarecord = ? AND cp.status = 0 - AND cp.opac_visible IS TRUE) + $copies_visible + ) ) AS available FROM $ancestors u @@ -68,6 +73,13 @@ __PACKAGE__->register_method( stream => 1, cachable => 1, ); +__PACKAGE__->register_method( + api_name => 'open-ils.storage.metabib.metarecord.copy_count.staff', + method => 'metarecord_copy_count', + api_level => 1, + stream => 1, + cachable => 1, +); sub search_full_rec { my $self = shift; @@ -156,8 +168,27 @@ sub search_class_fts { my $rank = join(' + ', @fts_ranks); + my $has_vols = 'AND cn.owning_lib = d.id'; + my $has_copies = 'AND cp.call_number = cn.id'; + my $copies_visible = 'AND cp.opac_visible IS TRUE'; + + my $visible_count = ', count(DISTINCT cp.id)'; + my $visible_count_test = 'HAVING count(DISTINCT cp.id) > 0'; + + if ($self->api_name =~ /staff/o) { + $copies_visible = ''; + $visible_count_test = ''; + $has_copies = '' if ($ou_type == 0); + $has_vols = '' if ($ou_type == 0); + } + + my $rank_calc = ", sum($rank)/count(m.source)"; + my $rank_order = "ORDER BY 2 DESC"; + $rank_calc = ',1' if ($self->api_name =~ /unordered/o); + $rank_order = '' if ($self->api_name =~ /unordered/o); + my $select = <<" SQL"; - SELECT m.metarecord, sum($rank)/count(m.source), count(DISTINCT cp.id) + SELECT m.metarecord $rank_calc $visible_count FROM $search_table f, $metabib_metarecord_source_map_table m, $asset_call_number_table cn, @@ -166,12 +197,12 @@ sub search_class_fts { WHERE $fts_where AND m.source = f.source AND cn.record = m.source - AND cn.owning_lib = d.id - AND cp.call_number = cn.id - AND cp.opac_visible IS TRUE + $has_vols + $has_copies + $copies_visible GROUP BY 1 - HAVING count(DISTINCT cp.id) > 0 - ORDER BY 2 DESC; + $visible_count_test + $rank_order SQL $log->debug("Field Search SQL :: [$select]",DEBUG); @@ -183,38 +214,41 @@ sub search_class_fts { $client->respond($_) for (@$recs); return undef; } -__PACKAGE__->register_method( - api_name => 'open-ils.storage.metabib.title.search_fts.metarecord', - method => 'search_class_fts', - api_level => 1, - stream => 1, - cdbi => 'metabib::title_field_entry', - cachable => 1, -); -__PACKAGE__->register_method( - api_name => 'open-ils.storage.metabib.author.search_fts.metarecord', - method => 'search_class_fts', - api_level => 1, - stream => 1, - cdbi => 'metabib::author_field_entry', - cachable => 1, -); -__PACKAGE__->register_method( - api_name => 'open-ils.storage.metabib.subject.search_fts.metarecord', - method => 'search_class_fts', - api_level => 1, - stream => 1, - cdbi => 'metabib::subject_field_entry', - cachable => 1, -); -__PACKAGE__->register_method( - api_name => 'open-ils.storage.metabib.keyword.search_fts.metarecord', - method => 'search_class_fts', - api_level => 1, - stream => 1, - cdbi => 'metabib::keyword_field_entry', - cachable => 1, -); + +for my $class ( qw/title author subject keyword/ ) { + __PACKAGE__->register_method( + api_name => "open-ils.storage.metabib.$class.search_fts.metarecord", + method => 'search_class_fts', + api_level => 1, + stream => 1, + cdbi => "metabib::${class}_field_entry", + cachable => 1, + ); + __PACKAGE__->register_method( + api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.unordered", + method => 'search_class_fts', + api_level => 1, + stream => 1, + cdbi => "metabib::${class}_field_entry", + cachable => 1, + ); + __PACKAGE__->register_method( + api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff", + method => 'search_class_fts', + api_level => 1, + stream => 1, + cdbi => "metabib::${class}_field_entry", + cachable => 1, + ); + __PACKAGE__->register_method( + api_name => "open-ils.storage.metabib.$class.search_fts.metarecord.staff.unordered", + method => 'search_class_fts', + api_level => 1, + stream => 1, + cdbi => "metabib::${class}_field_entry", + cachable => 1, + ); +} # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants". sub search_class_fts_count { @@ -234,10 +268,6 @@ sub search_class_fts_count { (my $search_class = $self->api_name) =~ s/.*metabib.(\w+).search_fts.*/$1/o; - my $cache_key = md5_hex($search_class.$term.$ou.$ou_type.'_COUNT_'); - - my $cached_recs = OpenSRF::Utils::Cache->new->get_cache( $cache_key ); - return $cached_recs if (defined $cached_recs); my $class = $self->{cdbi}; my $search_table = $class->table; @@ -253,6 +283,15 @@ sub search_class_fts_count { my $fts_where = $fts->sql_where_clause; + my $has_vols = 'AND cn.owning_lib = d.id'; + my $has_copies = 'AND cp.call_number = cn.id'; + my $copies_visible = 'AND cp.opac_visible IS TRUE'; + if ($self->api_name =~ /staff/o) { + $copies_visible = ''; + $has_vols = '' if ($ou_type == 0); + $has_copies = '' if ($ou_type == 0); + } + # XXX test an "EXISTS version of descendant checking... my $select = <<" SQL"; SELECT count(distinct m.metarecord) @@ -264,9 +303,9 @@ sub search_class_fts_count { WHERE $fts_where AND m.source = f.source AND cn.record = m.source - AND cn.owning_lib = d.id - AND cp.call_number = cn.id - AND cp.opac_visible IS TRUE + $has_vols + $has_copies + $copies_visible SQL $log->debug("Field Search Count SQL :: [$select]",DEBUG); @@ -275,42 +314,27 @@ sub search_class_fts_count { $log->debug("Count Search yielded $recs results.",DEBUG); - OpenSRF::Utils::Cache->new->put_cache( $cache_key => $recs ); - return $recs; } -__PACKAGE__->register_method( - api_name => 'open-ils.storage.metabib.title.search_fts.metarecord_count', - method => 'search_class_fts_count', - api_level => 1, - stream => 1, - cdbi => 'metabib::title_field_entry', - cachable => 1, -); -__PACKAGE__->register_method( - api_name => 'open-ils.storage.metabib.author.search_fts.metarecord_count', - method => 'search_class_fts_count', - api_level => 1, - stream => 1, - cdbi => 'metabib::author_field_entry', - cachable => 1, -); -__PACKAGE__->register_method( - api_name => 'open-ils.storage.metabib.subject.search_fts.metarecord_count', - method => 'search_class_fts_count', - api_level => 1, - stream => 1, - cdbi => 'metabib::subject_field_entry', - cachable => 1, -); -__PACKAGE__->register_method( - api_name => 'open-ils.storage.metabib.keyword.search_fts.metarecord_count', - method => 'search_class_fts_count', - api_level => 1, - stream => 1, - cdbi => 'metabib::keyword_field_entry', - cachable => 1, -); +for my $class ( qw/title author subject keyword/ ) { + __PACKAGE__->register_method( + api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count", + method => 'search_class_fts_count', + api_level => 1, + stream => 1, + cdbi => "metabib::${class}_field_entry", + cachable => 1, + ); + __PACKAGE__->register_method( + api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count.staff", + method => 'search_class_fts_count', + api_level => 1, + stream => 1, + cdbi => "metabib::${class}_field_entry", + cachable => 1, + ); +} + 1; diff --git a/Open-ILS/src/sql/Postgres/005.schema.actors.sql b/Open-ILS/src/sql/Postgres/005.schema.actors.sql index 0924186c51..2d69dceea1 100644 --- a/Open-ILS/src/sql/Postgres/005.schema.actors.sql +++ b/Open-ILS/src/sql/Postgres/005.schema.actors.sql @@ -7,9 +7,8 @@ CREATE TABLE actor.usr ( id SERIAL PRIMARY KEY, card INT UNIQUE, -- active card profile INT NOT NULL, -- patron profile - usrid TEXT NOT NULL UNIQUE, usrname TEXT NOT NULL UNIQUE, - email TEXT CHECK (email ~ $re$^[[:alnum:]_\.]+@[[:alnum:]_]+(?:\.[[:alnum:]_])+$$re$), + email TEXT, passwd TEXT NOT NULL, standing INT NOT NULL DEFAULT 1 REFERENCES config.standing (id), ident_type INT NOT NULL REFERENCES config.identification_type (id), @@ -29,7 +28,6 @@ CREATE TABLE actor.usr ( mailing_address INT, billing_address INT, home_ou INT, - gender CHAR(1) NOT NULL CHECK ( LOWER(gender) IN ('m','f') ), dob DATE NOT NULL, active BOOL NOT NULL DEFAULT TRUE, master_account BOOL NOT NULL DEFAULT FALSE, @@ -71,12 +69,12 @@ CREATE TRIGGER actor_crypt_pw_insert_trigger EXECUTE PROCEDURE actor.crypt_pw_insert (); -- Just so that there is a user... -INSERT INTO actor.usr ( profile, card, usrid, usrname, passwd, first_given_name, family_name, gender, dob, master_account, super_user, ident_type, ident_value ) - VALUES ( 3, 1,'admin', 'admin', 'open-ils', 'Administrator', '', 'm', '1979-01-22', TRUE, TRUE, 1, 'identification' ); -INSERT INTO actor.usr ( profile, card, usrid, usrname, passwd, first_given_name, family_name, gender, dob, master_account, super_user, ident_type, ident_value ) - VALUES ( 2, 2,'demo', 'demo', 'demo', 'demo', 'user', 'm', '1979-01-22', FALSE, TRUE, 1, 'identification' ); -INSERT INTO actor.usr ( profile, card, usrid, usrname, passwd, first_given_name, family_name, gender, dob, master_account, super_user, ident_type, ident_value ) - VALUES ( 1, 3,'athens', 'athens', 'athens', 'athens', 'user', 'm', '1979-01-22', FALSE, TRUE, 1, 'identification' ); +INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value ) + VALUES ( 3, 1,'admin', 'open-ils', 'Administrator', '', '1979-01-22', TRUE, TRUE, 1, 'identification' ); +INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value ) + VALUES ( 2, 2,'demo', 'demo', 'demo', 'user', '1979-01-22', FALSE, TRUE, 1, 'identification' ); +INSERT INTO actor.usr ( profile, card, usrname, passwd, first_given_name, family_name, dob, master_account, super_user, ident_type, ident_value ) + VALUES ( 1, 3,'athens', 'athens', 'athens', 'user', '1979-01-22', FALSE, TRUE, 1, 'identification' ); CREATE TABLE actor.profile ( id SERIAL PRIMARY KEY, @@ -88,7 +86,7 @@ INSERT INTO actor.profile (name) VALUES ('STAFF'); CREATE TABLE actor.stat_cat ( id SERIAL PRIMARY KEY, - owner INT NOT NULL, -- actor.org_unit.id + owner INT NOT NULL, name TEXT NOT NULL, opac_visible BOOL NOT NULL DEFAULT FALSE, CONSTRAINT sc_once_per_owner UNIQUE (owner,name) @@ -96,8 +94,8 @@ CREATE TABLE actor.stat_cat ( CREATE TABLE actor.stat_cat_entry ( id SERIAL PRIMARY KEY, - stat_cat INT NOT NULL REFERENCES actor.stat_cat (id) ON DELETE CASCADE, - owner INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE, + stat_cat INT NOT NULL, + owner INT NOT NULL, value TEXT NOT NULL, CONSTRAINT sce_once_per_owner UNIQUE (owner,value) ); @@ -105,8 +103,8 @@ CREATE TABLE actor.stat_cat_entry ( CREATE TABLE actor.stat_cat_entry_usr_map ( id BIGSERIAL PRIMARY KEY, stat_cat_entry TEXT NOT NULL, - stat_cat INT NOT NULL REFERENCES actor.stat_cat (id) ON DELETE CASCADE, - target_usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE, + stat_cat INT NOT NULL, + target_usr INT NOT NULL, CONSTRAINT sce_once_per_copy UNIQUE (target_usr,stat_cat) ); CREATE INDEX actor_stat_cat_entry_usr_idx ON actor.stat_cat_entry_usr_map (target_usr); @@ -138,7 +136,8 @@ CREATE INDEX actor_org_unit_type_parent_idx ON actor.org_unit_type (parent); INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users, can_have_vols) VALUES ( 'Consortium', 0, NULL, FALSE, FALSE ); INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users, can_have_vols) VALUES ( 'System', 1, 1, FALSE, FALSE ); INSERT INTO actor.org_unit_type (name, depth, parent) VALUES ( 'Branch', 2, 2 ); -INSERT INTO actor.org_unit_type (name, depth, parent) VALUES ( 'Sub-lib', 5, 3 ); +INSERT INTO actor.org_unit_type (name, depth, parent) VALUES ( 'Sub-lib', 3, 3 ); +INSERT INTO actor.org_unit_type (name, depth, parent) VALUES ( 'Bookmobile', 3, 3 ); CREATE TABLE actor.org_unit ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Postgres/040.schema.asset.sql b/Open-ILS/src/sql/Postgres/040.schema.asset.sql index b69f08b7db..7e38df8be8 100644 --- a/Open-ILS/src/sql/Postgres/040.schema.asset.sql +++ b/Open-ILS/src/sql/Postgres/040.schema.asset.sql @@ -76,7 +76,7 @@ CREATE TABLE asset.stat_cat_entry_transparency_map ( CREATE TABLE asset.stat_cat ( id SERIAL PRIMARY KEY, - owner INT NOT NULL, -- actor.org_unit.id + owner INT NOT NULL, name TEXT NOT NULL, opac_visible BOOL NOT NULL DEFAULT FALSE, CONSTRAINT sc_once_per_owner UNIQUE (owner,name) @@ -84,17 +84,17 @@ CREATE TABLE asset.stat_cat ( CREATE TABLE asset.stat_cat_entry ( id SERIAL PRIMARY KEY, - stat_cat INT NOT NULL REFERENCES actor.stat_cat (id) ON DELETE CASCADE, - owner INT NOT NULL REFERENCES actor.org_unit.id (id) ON DELETE CASCADE, + stat_cat INT NOT NULL, + owner INT NOT NULL, value TEXT NOT NULL, CONSTRAINT sce_once_per_owner UNIQUE (owner,value) ); CREATE TABLE asset.stat_cat_entry_copy_map ( id BIGSERIAL PRIMARY KEY, - stat_cat INT NOT NULL, -- needs ON DELETE CASCADE - stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE - owning_copy BIGINT NOT NULL, -- needs ON DELETE CASCADE + stat_cat INT NOT NULL, + stat_cat_entry INT NOT NULL, + owning_copy BIGINT NOT NULL, CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat) ); diff --git a/Open-ILS/src/sql/Postgres/090.schema.action.sql b/Open-ILS/src/sql/Postgres/090.schema.action.sql index 8618edc6d4..9687e2a2b9 100644 --- a/Open-ILS/src/sql/Postgres/090.schema.action.sql +++ b/Open-ILS/src/sql/Postgres/090.schema.action.sql @@ -30,18 +30,21 @@ CREATE TABLE action.survey_answer ( answer TEXT NOT NULL ); +CREATE SEQUENCE action.survey_response_group_id_seq; + CREATE TABLE action.survey_response ( id BIGSERIAL PRIMARY KEY, + response_group_id INT, usr INT, -- REFERENCES actor.usr survey INT NOT NULL REFERENCES action.survey, question INT NOT NULL REFERENCES action.survey_question, answer INT NOT NULL REFERENCES action.survey_answer, - answer_date DATE, - effective_date DATE NOT NULL DEFAULT NOW()::DATE + answer_date TIMESTAMP WITH TIME ZONE, + effective_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); CREATE OR REPLACE FUNCTION action.survey_response_answer_date_fixup () RETURNS TRIGGER AS ' BEGIN - NEW.answer_date := NOW()::DATE; + NEW.answer_date := NOW(); RETURN NEW; END; ' LANGUAGE 'plpgsql'; @@ -50,6 +53,7 @@ CREATE TRIGGER action_survey_response_answer_date_fixup_tgr FOR EACH ROW EXECUTE PROCEDURE action.survey_response_answer_date_fixup (); + CREATE TABLE action.circulation ( target_copy BIGINT NOT NULL, -- asset.copy.id circ_lib INT NOT NULL, -- actor.org_unit.id diff --git a/Open-ILS/src/sql/Postgres/800.fkeys.sql b/Open-ILS/src/sql/Postgres/800.fkeys.sql index 4605f012b6..a871aa0f4d 100644 --- a/Open-ILS/src/sql/Postgres/800.fkeys.sql +++ b/Open-ILS/src/sql/Postgres/800.fkeys.sql @@ -5,9 +5,14 @@ ALTER TABLE actor.usr ADD CONSTRAINT actor_usr_mailing_address_fkey FOREIGN KEY ALTER TABLE actor.usr ADD CONSTRAINT actor_usr_billining_address_fkey FOREIGN KEY (billing_address) REFERENCES actor.usr_address (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE actor.usr ADD CONSTRAINT actor_usr_home_ou_fkey FOREIGN KEY (home_ou) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE actor.stat_cat ADD CONSTRAINT actor_stat_cat_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE actor.stat_cat ADD CONSTRAINT actor_stat_cat_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE actor.stat_cat_entry ADD CONSTRAINT actor_stat_cat_entry_stat_cat_fkey FOREIGN KEY (stat_cat) REFERENCES actor.stat_cat (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE actor.stat_cat_entry ADD CONSTRAINT actor_stat_cat_entry_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE actor.stat_cat_entry_user_map ADD CONSTRAINT actor_sc_tu_fkey FOREIGN KEY (target_usr) REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE actor.stat_cat_entry_user_map ADD CONSTRAINT actor_sc_sue_fkey FOREIGN KEY (stat_cat_entry) REFERENCES asset.stat_cat_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE actor.stat_cat_entry ADD CONSTRAINT actor_stat_cat_entry_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE actor.org_unit ADD CONSTRAINT actor_org_unit_mailing_address_fkey FOREIGN KEY (mailing_address) REFERENCES actor.org_address (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE actor.org_unit ADD CONSTRAINT actor_org_unit_billing_address_fkey FOREIGN KEY (billing_address) REFERENCES actor.org_address (id) DEFERRABLE INITIALLY DEFERRED; @@ -23,44 +28,52 @@ ALTER TABLE biblio.record_entry ADD CONSTRAINT biblio_record_entry_editor_fkey F ALTER TABLE metabib.metarecord ADD CONSTRAINT metabib_metarecord_master_record_fkey FOREIGN KEY (master_record) REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE metabib.title_field_entry ADD CONSTRAINT metabib_title_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE metabib.title_field_entry ADD CONSTRAINT metabib_title_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.title_field_entry ADD CONSTRAINT metabib_title_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.title_field_entry ADD CONSTRAINT metabib_title_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE metabib.author_field_entry ADD CONSTRAINT metabib_author_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.author_field_entry ADD CONSTRAINT metabib_author_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE metabib.subject_field_entry ADD CONSTRAINT metabib_subject_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.subject_field_entry ADD CONSTRAINT metabib_subject_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE metabib.keyword_field_entry ADD CONSTRAINT metabib_keyword_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.keyword_field_entry ADD CONSTRAINT metabib_keyword_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE metabib.author_field_entry ADD CONSTRAINT metabib_author_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE metabib.author_field_entry ADD CONSTRAINT metabib_author_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.rec_descriptor ADD CONSTRAINT metabib_rec_descriptor_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE metabib.subject_field_entry ADD CONSTRAINT metabib_subject_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE metabib.subject_field_entry ADD CONSTRAINT metabib_subject_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id); +ALTER TABLE metabib.full_rec ADD CONSTRAINT metabib_full_rec_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE metabib.keyword_field_entry ADD CONSTRAINT metabib_keyword_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id); -ALTER TABLE metabib.keyword_field_entry ADD CONSTRAINT metabib_keyword_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id); +ALTER TABLE metabib.metarecord_source_map ADD CONSTRAINT metabib_metarecord_source_map_source_fkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.metarecord_source_map ADD CONSTRAINT metabib_metarecord_source_map_metarecord_fkey FOREIGN KEY (metarecord) REFERENCES metabib.metarecord (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE metabib.rec_descriptor ADD CONSTRAINT metabib_rec_descriptor_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id); +ALTER TABLE asset.copy ADD CONSTRAINT asset_copy_call_number_fkey FOREIGN KEY (call_number) REFERENCES asset.call_number (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.copy ADD CONSTRAINT asset_copy_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.copy ADD CONSTRAINT asset_copy_editor_fkey FOREIGN KEY (editor) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE metabib.full_rec ADD CONSTRAINT metabib_full_rec_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id); +ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_copy_fkey FOREIGN KEY (owning_copy) REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE metabib.metarecord_source_map ADD CONSTRAINT metabib_metarecord_source_map_source_fkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id); -ALTER TABLE metabib.metarecord_source_map ADD CONSTRAINT metabib_metarecord_source_map_metarecord_fkey FOREIGN KEY (metarecord) REFERENCES metabib.metarecord (id); +ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_owning_lib_fkey FOREIGN KEY (owning_lib) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_editor_fkey FOREIGN KEY (editor) REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE asset.copy ADD CONSTRAINT asset_copy_call_number_fkey FOREIGN KEY (call_number) REFERENCES asset.call_number (id); -ALTER TABLE asset.copy ADD CONSTRAINT asset_copy_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id); -ALTER TABLE asset.copy ADD CONSTRAINT asset_copy_editor_fkey FOREIGN KEY (editor) REFERENCES actor.usr (id); +ALTER TABLE asset.call_number_note ADD CONSTRAINT asset_call_number_note_record_fkey FOREIGN KEY (call_number) REFERENCES asset.call_number (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.call_number_note ADD CONSTRAINT asset_call_number_note_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_copy_fkey FOREIGN KEY (owning_copy) REFERENCES asset.copy (id); -ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id); +ALTER TABLE asset.stat_cat ADD CONSTRAINT a_sc_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_owning_lib_fkey FOREIGN KEY (owning_lib) REFERENCES actor.org_unit (id); -ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id); -ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id); -ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_editor_fkey FOREIGN KEY (editor) REFERENCES actor.usr (id); +ALTER TABLE asset.stat_cat_entry ADD CONSTRAINT a_sce_sc_fkey FOREIGN KEY (stat_cat) REFERENCES asset.stat_cat (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.stat_cat_entry ADD CONSTRAINT a_sce_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE asset.call_number_note ADD CONSTRAINT asset_call_number_note_record_fkey FOREIGN KEY (call_number) REFERENCES asset.call_number (id); -ALTER TABLE asset.call_number_note ADD CONSTRAINT asset_call_number_note_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id); +ALTER TABLE asset.stat_cat_entry_copy_map ADD CONSTRAINT a_sc_oc_fkey FOREIGN KEY (owning_copy) REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE asset.stat_cat_entry_copy_map ADD CONSTRAINT a_sc_sce_fkey FOREIGN KEY (stat_cat_entry) REFERENCES asset.stat_cat_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE money.billable_xact ADD CONSTRAINT money_billable_xact_usr_fkey FOREIGN KEY (usr) REFERENCES actor.usr (id); +ALTER TABLE money.billable_xact ADD CONSTRAINT money_billable_xact_usr_fkey FOREIGN KEY (usr) REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE action.circulation ADD CONSTRAINT action_circulation_usr_fkey FOREIGN KEY (usr) REFERENCES actor.usr (id); -ALTER TABLE action.circulation ADD CONSTRAINT action_circulation_circ_lib_fkey FOREIGN KEY (circ_lib) REFERENCES actor.org_unit (id); -ALTER TABLE action.circulation ADD CONSTRAINT action_circulation_target_copy_fkey FOREIGN KEY (target_copy) REFERENCES asset.copy (id); +ALTER TABLE action.circulation ADD CONSTRAINT action_circulation_usr_fkey FOREIGN KEY (usr) REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE action.circulation ADD CONSTRAINT action_circulation_circ_lib_fkey FOREIGN KEY (circ_lib) REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE action.circulation ADD CONSTRAINT action_circulation_target_copy_fkey FOREIGN KEY (target_copy) REFERENCES asset.copy (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; COMMIT; -- 2.43.2