From c14784d1205c65efd18984dd32ceb1cc732fc2fc Mon Sep 17 00:00:00 2001 From: miker Date: Thu, 10 Mar 2005 07:25:38 +0000 Subject: [PATCH] added tons more SQL level stuff... I am going to bed now git-svn-id: svn://svn.open-ils.org/ILS/trunk@303 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../OpenILS/Application/Storage/CDBI.pm | 68 +++++++++--- .../OpenILS/Application/Storage/CDBI/actor.pm | 7 +- .../Application/Storage/CDBI/biblio.pm | 12 ++- .../Application/Storage/CDBI/metabib.pm | 30 ++++-- .../OpenILS/Application/Storage/Driver/Pg.pm | 101 ++++++++++-------- .../src/sql/Postgres/005.schema.actors.sql | 30 +++++- .../src/sql/Postgres/010.schema.biblio.sql | 15 ++- .../src/sql/Postgres/030.schema.metabib.sql | 36 +++---- .../src/sql/Postgres/810.fkeys.biblio.sql | 6 +- 9 files changed, 207 insertions(+), 98 deletions(-) diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI.pm index f92730e5eb..37151f8e74 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI.pm @@ -132,6 +132,13 @@ sub create_from_fieldmapper { ($_ => $fm->$_) : () } $fm->real_fields; + + if ($class->find_column( 'last_xact_id' )) { + my $xact_id = $class->current_xact_id; + throw Error unless ($xact_id); + $hash{last_xact_id} = $xact_id; + } + return $class->create( \%hash, @params ); } else { return undef; @@ -158,6 +165,13 @@ sub delete { $self = $self->retrieve($arg); } + if ($class->find_column( 'last_xact_id' )) { + my $xact_id = $self->current_xact_id; + throw Error unless ($xact_id); + $self->last_xact_id( $self->current_xact_id ); + $self->update; + } + $self->SUPER::delete; return $arg; } @@ -178,6 +192,12 @@ sub modify_from_fieldmapper { () } $fm->real_fields; + if ($class->find_column( 'last_xact_id' )) { + my $xact_id = $obj->current_xact_id; + throw Error unless ($xact_id); + $hash{last_xact_id} = $xact_id; + } + my $au = $obj->autoupdate; $obj->autoupdate(0); @@ -192,13 +212,24 @@ sub modify_from_fieldmapper { + +#------------------------------------------------------------------------------- +actor::user->has_a( home_ou => 'actor::org_unit' ); +#actor::org_unit->has_a( address => 'actor::address' ); +#------------------------------------------------------------------------------- +actor::org_unit->has_many( users => 'actor::user' ); +actor::org_unit->has_a( parent_ou => 'actor::org_unit' ); +actor::org_unit->has_a( ou_type => 'actor::org_unit_type' ); +#actor::org_unit->has_a( address => 'actor::address' ); +#------------------------------------------------------------------------------- + #------------------------------------------------------------------------------- asset::copy->has_a( call_number => 'asset::call_number' ); -asset::copy->might_have( metadata => 'asset::copy_metadata' ); +#asset::copy->might_have( metadata => 'asset::copy_metadata' ); #------------------------------------------------------------------------------- -asset::copy_metadata->might_have( copy => 'asset::copy' ); -#asset::copy_metadata->has_a( ciruclating_location => 'actor::org_unit'); -#asset::copy_metadata->has_a( hold_radius => 'actor::org_unit_type'); +#asset::copy_metadata->might_have( copy => 'asset::copy' ); +asset::copy_metadata->has_a( circulating_location => 'actor::org_unit'); +asset::copy_metadata->has_a( hold_radius => 'actor::org_unit_type'); #------------------------------------------------------------------------------- asset::call_number->has_a( record => 'biblio::record_entry' ); asset::call_number->has_many( copies => 'asset::copy' ); @@ -208,6 +239,9 @@ asset::call_number->has_many( copies => 'asset::copy' ); #------------------------------------------------------------------------------- biblio::record_note->has_a( record => 'biblio::record_entry' ); #------------------------------------------------------------------------------- +biblio::record_entry->has_a( creator => 'actor::user' ); +biblio::record_entry->has_a( editor => 'actor::user' ); +biblio::record_entry->might_have( mods_entry => 'biblio::record_mods' => qw/mods/ ); biblio::record_entry->has_many( notes => 'biblio::record_note' ); biblio::record_entry->has_many( nodes => 'biblio::record_node', { order_by => 'intra_doc_id' } ); biblio::record_entry->has_many( call_numbers => 'asset::call_number' ); @@ -226,30 +260,38 @@ biblio::record_node->has_a( owner_doc => 'biblio::record_entry' ); #------------------------------------------------------------------------------- #------------------------------------------------------------------------------- -#metabib::metarecord->has_a( master_record => 'biblio::record_entry' ); +metabib::metarecord->has_a( master_record => 'biblio::record_entry' ); +metabib::metarecord->has_many( source_records => [ 'metabib::metarecord_source_map' => 'source_record'] ); #------------------------------------------------------------------------------- +metabib::title_field_entry->has_many( source_records => [ 'metabib::title_field_entry_source_map' => 'source_record'] ); #metabib::title_field_entry->has_a( field => 'config::metabib_field_map' ); #------------------------------------------------------------------------------- +metabib::author_field_entry->has_many( source_records => [ 'metabib::author_field_entry_source_map' => 'source_record'] ); #metabib::author_field_entry->has_a( field => 'config::metabib_field_map' ); #------------------------------------------------------------------------------- +metabib::subject_field_entry->has_many( source_records => [ 'metabib::title_field_entry_source_map' => 'source_record'] ); #metabib::subject_field_entry->has_a( field => 'config::metabib_field_map' ); #------------------------------------------------------------------------------- +metabib::keyword_field_entry->has_many( source_records => [ 'metabib::keyword_field_entry_source_map' => 'source_record'] ); #metabib::keyword_field_entry->has_a( field => 'config::metabib_field_map' ); #------------------------------------------------------------------------------- +metabib::metarecord_source_map->has_a( metarecord => 'metabib::metarecord' ); +metabib::metarecord_source_map->has_a( source_record => 'biblio::record_entry' ); +#------------------------------------------------------------------------------- # should we have just one field entry per class for each record???? (xslt vs xpath) -#metabib::title_field_entry_source_map->has_a( field_entry => 'metabib::title_field_entry' ); -#metabib::title_field_entry_source_map->has_a( source_record => 'biblio::record_entry' ); +metabib::title_field_entry_source_map->has_a( field_entry => 'metabib::title_field_entry' ); +metabib::title_field_entry_source_map->has_a( source_record => 'biblio::record_entry' ); #------------------------------------------------------------------------------- -#metabib::subject_field_entry_source_map->has_a( field_entry => 'metabib::subject_field_entry' ); -#metabib::subject_field_entry_source_map->has_a( source_record => 'biblio::record_entry' ); +metabib::subject_field_entry_source_map->has_a( field_entry => 'metabib::subject_field_entry' ); +metabib::subject_field_entry_source_map->has_a( source_record => 'biblio::record_entry' ); #------------------------------------------------------------------------------- -#metabib::author_field_entry_source_map->has_a( field_entry => 'metabib::author_field_entry' ); -#metabib::author_field_entry_source_map->has_a( source_record => 'biblio::record_entry' ); +metabib::author_field_entry_source_map->has_a( field_entry => 'metabib::author_field_entry' ); +metabib::author_field_entry_source_map->has_a( source_record => 'biblio::record_entry' ); #------------------------------------------------------------------------------- -#metabib::keyword_field_entry_source_map->has_a( field_entry => 'metabib::keyword_field_entry' ); -#metabib::keyword_field_entry_source_map->has_a( source_record => 'biblio::record_entry' ); +metabib::keyword_field_entry_source_map->has_a( field_entry => 'metabib::keyword_field_entry' ); +metabib::keyword_field_entry_source_map->has_a( source_record => 'biblio::record_entry' ); #------------------------------------------------------------------------------- diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/actor.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/actor.pm index 93c5f106e6..5cfc8afa40 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/actor.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/actor.pm @@ -13,7 +13,7 @@ __PACKAGE__->columns( Primary => qw/id/ ); __PACKAGE__->columns( Others => qw/id usrid usrname email prefix first_given_name second_given_name family_name suffix address home_ou gender dob active master_account - super_user usrgroup passwd/ ); + super_user usrgroup passwd last_xact_id/ ); #------------------------------------------------------------------------------- package actor::org_unit_type; @@ -26,6 +26,11 @@ __PACKAGE__->columns( Others => qw/name depth parent can_have_users/); #------------------------------------------------------------------------------- package actor::org_unit; use base qw/actor/; + +__PACKAGE__->table( 'actor_org_unit' ); +__PACKAGE__->columns( Primary => qw/id/); +__PACKAGE__->columns( Others => qw/parent_ou ou_type address shortname name/); + #------------------------------------------------------------------------------- package actor::user_access_entry; use base qw/actor/; diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/biblio.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/biblio.pm index 033a206b60..1d7fdf6e02 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/biblio.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/biblio.pm @@ -12,7 +12,7 @@ use base qw/biblio/; biblio::record_entry->table( 'biblio_record_entry' ); biblio::record_entry->columns( All => qw/id tcn_source tcn_value creator editor create_date edit_date - source active deleted/ ); + source active deleted last_xact_id/ ); #------------------------------------------------------------------------------- package biblio::record_node::subnode; @@ -32,7 +32,7 @@ use base qw/biblio/; biblio::record_node->table( 'biblio_record_data' ); biblio::record_node->columns( All => qw/id owner_doc intra_doc_id parent_node node_type - namespace_uri name value/ ); + namespace_uri name value last_xact_id/ ); #biblio::record_node->has_a( # parent_node => 'biblio::record_node::subnode', @@ -42,6 +42,14 @@ biblio::record_node->columns( All => qw/id owner_doc intra_doc_id #); +#------------------------------------------------------------------------------- +package biblio::record_mods; +use base qw/biblio/; + +biblio::record_mods->table( 'biblio_record_mods' ); +biblio::record_mods->columns( All => qw/id mods/ ); +biblio::record_note->columns( Stringify => qw/mods/ ); + #------------------------------------------------------------------------------- package biblio::record_note; use base qw/biblio/; diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/metabib.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/metabib.pm index 85e012922b..c2cec05030 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/metabib.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/metabib.pm @@ -10,7 +10,7 @@ use base qw/metabib/; metabib::metarecord->table( 'metabib_metarecord' ); metabib::metarecord->columns( Primary => qw/id/ ); -metabib::metarecord->columns( Others => qw/fingerprint master_record/ ); +metabib::metarecord->columns( Others => qw/fingerprint master_record mods/ ); #------------------------------------------------------------------------------- package metabib::title_field_entry; @@ -52,33 +52,45 @@ package metabib::title_field_entry_source_map; use base qw/metabib/; metabib::title_field_entry_source_map->table( 'metabib_title_field_entry_source_map' ); -metabib::title_field_entry_source_map->columns( Primary => qw/id/ ); -metabib::title_field_entry_source_map->columns( Others => qw/field_entry source_record/ ); +metabib::title_field_entry_source_map->columns( Primary => qw/field_entry source_record/ ); #------------------------------------------------------------------------------- package metabib::author_field_entry_source_map; use base qw/metabib/; metabib::author_field_entry_source_map->table( 'metabib_author_field_entry_source_map' ); -metabib::author_field_entry_source_map->columns( Primary => qw/id/ ); -metabib::author_field_entry_source_map->columns( Others => qw/field_entry source_record/ ); +metabib::author_field_entry_source_map->columns( Primary => qw/field_entry source_record/ ); #------------------------------------------------------------------------------- package metabib::subject_field_entry_source_map; use base qw/metabib/; metabib::subject_field_entry_source_map->table( 'metabib_subject_field_entry_source_map' ); -metabib::subject_field_entry_source_map->columns( Primary => qw/id/ ); -metabib::subject_field_entry_source_map->columns( Others => qw/field_entry source_record/ ); +metabib::subject_field_entry_source_map->columns( Primary => qw/field_entry source_record/ ); #------------------------------------------------------------------------------- package metabib::keyword_field_entry_source_map; use base qw/metabib/; metabib::keyword_field_entry_source_map->table( 'metabib_keyword_field_entry_source_map' ); -metabib::keyword_field_entry_source_map->columns( Primary => qw/id/ ); -metabib::keyword_field_entry_source_map->columns( Others => qw/field_entry source_record/ ); +metabib::keyword_field_entry_source_map->columns( Primary => qw/field_entry source_record/ ); #------------------------------------------------------------------------------- +package metabib::metarecord_source_map; +use base qw/metabib/; + +metabib::metarecord_source_map->table( 'metabib_metarecord_source_map' ); +metabib::metarecord_source_map->columns( Primary => qw/metarecord source_record/ ); + +#------------------------------------------------------------------------------- +package metabib::full_rec; +use base qw/metabib/; + +metabib::full_rec->table( 'metabib_full_rec' ); +metabib::full_rec->columns( Primary => qw/id/ ); +metabib::full_rec->columns( Others => qw/record tag ind1 ind2 subfield value/ ); + +#------------------------------------------------------------------------------- + 1; diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg.pm index c673e4ca94..2b8dadcb40 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg.pm @@ -97,11 +97,13 @@ $master_db = DBI->connect("dbi:Pg:host=$$master{host};dbname=$$master{db}",$$master{user},$$master{pw}, \%attrs); $master_db->do("SET NAMES '$$master{client_encoding}';") if ($$master{client_encoding}); - $log->debug("Connected to MASTER db at $$master{host}", INTERNAL); + $log->debug("Connected to MASTER db '$$master{db} at $$master{host}", INFO); for my $db (@$_db_params) { push @slave_dbs, DBI->connect("dbi:Pg:host=$$db{host};dbname=$$db{db}",$$db{user},$$db{pw}, \%attrs); $slave_dbs[-1]->do("SET NAMES '$$db{client_encoding}';") if ($$master{client_encoding}); + + $log->debug("Connected to MASTER db '$$master{db} at $$master{host}", INFO); } $log->debug("All is well on the western front", INTERNAL); @@ -118,17 +120,17 @@ return __PACKAGE__->db_Main->quote(@_) } - sub tsearch2_trigger { - my $self = shift; - return unless ($self->value); - $self->index_vector( - $self->db_Slaves->selectrow_array( - "SELECT to_tsvector('default',?);", - {}, - $self->value - ) - ); - } +# sub tsearch2_trigger { +# my $self = shift; +# return unless ($self->value); +# $self->index_vector( +# $self->db_Slaves->selectrow_array( +# "SELECT to_tsvector('default',?);", +# {}, +# $self->value +# ) +# ); +# } my $_xact_session; @@ -354,6 +356,12 @@ biblio::record_node->table( 'biblio.record_data' ); biblio::record_node->sequence( 'biblio.record_data_id_seq' ); + #--------------------------------------------------------------------- + package biblio::record_mods; + + biblio::record_mods->table( 'biblio.record_mods' ); + biblio::record_mods->sequence( 'biblio.record_mods_id_seq' ); + #--------------------------------------------------------------------- package biblio::record_note; @@ -371,9 +379,15 @@ actor::org_unit_type->table( 'actor.org_unit_type' ); actor::org_unit_type->sequence( 'actor.org_unit_type_id_seq' ); - + #--------------------------------------------------------------------- + package actor::org_unit; + actor::org_unit_type->table( 'actor.org_unit' ); + actor::org_unit_type->sequence( 'actor.org_unit_id_seq' ); + + #--------------------------------------------------------------------- + #------------------------------------------------------------------------------- package metabib::metarecord; @@ -387,16 +401,13 @@ metabib::title_field_entry->table( 'metabib.title_field_entry' ); metabib::title_field_entry->sequence( 'metabib.title_field_entry_id_seq' ); - metabib::title_field_entry->columns( Primary => qw/id/ ); - metabib::title_field_entry->columns( Essential => qw/id/ ); - metabib::title_field_entry->columns( Others => qw/field value index_vector/ ); - metabib::title_field_entry->add_trigger( - before_create => \&OpenILS::Application::Storage::Driver::Pg::tsearch2_trigger - ); - metabib::title_field_entry->add_trigger( - before_update => \&OpenILS::Application::Storage::Driver::Pg::tsearch2_trigger - ); +# metabib::title_field_entry->add_trigger( +# before_create => \&OpenILS::Application::Storage::Driver::Pg::tsearch2_trigger +# ); +# metabib::title_field_entry->add_trigger( +# before_update => \&OpenILS::Application::Storage::Driver::Pg::tsearch2_trigger +# ); #------------------------------------------------------------------------------- @@ -406,13 +417,6 @@ metabib::author_field_entry->table( 'metabib.author_field_entry' ); metabib::author_field_entry->sequence( 'metabib.author_field_entry_id_seq' ); - metabib::author_field_entry->add_trigger( - before_create => \&OpenILS::Application::Storage::Driver::Pg::tsearch2_trigger - ); - metabib::author_field_entry->add_trigger( - before_update => \&OpenILS::Application::Storage::Driver::Pg::tsearch2_trigger - ); - #------------------------------------------------------------------------------- #------------------------------------------------------------------------------- @@ -421,13 +425,6 @@ metabib::subject_field_entry->table( 'metabib.subject_field_entry' ); metabib::subject_field_entry->sequence( 'metabib.subject_field_entry_id_seq' ); - metabib::subject_field_entry->add_trigger( - before_create => \&OpenILS::Application::Storage::Driver::Pg::tsearch2_trigger - ); - metabib::subject_field_entry->add_trigger( - before_update => \&OpenILS::Application::Storage::Driver::Pg::tsearch2_trigger - ); - #------------------------------------------------------------------------------- #------------------------------------------------------------------------------- @@ -436,20 +433,12 @@ metabib::keyword_field_entry->table( 'metabib.keyword_field_entry' ); metabib::keyword_field_entry->sequence( 'metabib.keyword_field_entry_id_seq' ); - metabib::keyword_field_entry->add_trigger( - before_create => \&OpenILS::Application::Storage::Driver::Pg::tsearch2_trigger - ); - metabib::keyword_field_entry->add_trigger( - before_update => \&OpenILS::Application::Storage::Driver::Pg::tsearch2_trigger - ); - #------------------------------------------------------------------------------- #------------------------------------------------------------------------------- package metabib::title_field_entry_source_map; metabib::title_field_entry_source_map->table( 'metabib.title_field_entry_source_map' ); - metabib::title_field_entry_source_map->table( 'metabib.title_field_entry_source_map_id_seq' ); #------------------------------------------------------------------------------- @@ -457,7 +446,6 @@ package metabib::author_field_entry_source_map; metabib::author_field_entry_source_map->table( 'metabib.author_field_entry_source_map' ); - metabib::author_field_entry_source_map->sequence( 'metabib.author_field_entry_source_map_id_seq' ); #------------------------------------------------------------------------------- @@ -465,7 +453,28 @@ package metabib::subject_field_entry_source_map; metabib::subject_field_entry_source_map->table( 'metabib.subject_field_entry_source_map' ); - metabib::subject_field_entry_source_map->sequence( 'metabib.subject_field_entry_source_map_id_seq' ); + + #------------------------------------------------------------------------------- + + #------------------------------------------------------------------------------- + package metabib::keyword_field_entry_source_map; + + metabib::keyword_field_entry_source_map->table( 'metabib.keyword_field_entry_source_map' ); + + #------------------------------------------------------------------------------- + + #------------------------------------------------------------------------------- + package metabib::metarecord_source_map; + + metabib::metarecord_source_map->table( 'metabib.full_rec' ); + + #------------------------------------------------------------------------------- + + #------------------------------------------------------------------------------- + package metabib::full_rec; + + metabib::full_rec->table( 'metabib.full_rec' ); + metabib::full_rec->sequence( 'metabib.full_rec_id_seq' ); #------------------------------------------------------------------------------- } diff --git a/Open-ILS/src/sql/Postgres/005.schema.actors.sql b/Open-ILS/src/sql/Postgres/005.schema.actors.sql index c1cd7cd392..469241b7a3 100644 --- a/Open-ILS/src/sql/Postgres/005.schema.actors.sql +++ b/Open-ILS/src/sql/Postgres/005.schema.actors.sql @@ -25,6 +25,8 @@ CREATE TABLE actor.usr ( last_xact_id TEXT NOT NULL DEFAULT 'none' ); +CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou); +CREATE INDEX actor_usr_address_idx ON actor.usr (address); CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$ BEGIN @@ -50,6 +52,7 @@ CREATE TRIGGER actor_crypt_pw_insert_trigger BEFORE INSERT ON actor.usr FOR EACH ROW EXECUTE PROCEDURE actor.crypt_pw_insert (); +-- Just so that there is a user... INSERT INTO actor.usr ( usrid, usrname, passwd, first_given_name, family_name, gender, dob, master_account, super_user ) VALUES ( 'admin', 'admin', 'open-ils', 'Administrator', '', 'm', '1979-01-22', TRUE, TRUE ); @@ -61,19 +64,38 @@ CREATE TABLE actor.org_unit_type ( can_have_users BOOL NOT NULL DEFAULT TRUE ); +-- The PINES levels INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users) VALUES ( 'Consortium', 0, NULL, FALSE ); INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users) VALUES ( 'System', 1, 1, FALSE ); INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users) VALUES ( 'Branch', 2, 2, TRUE ); -INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users) VALUES ( 'Sub-lib', 3, 3, TRUE ); +INSERT INTO actor.org_unit_type (name, depth, parent, can_have_users) VALUES ( 'Sub-lib', 5, 3, TRUE ); CREATE TABLE actor.org_unit ( id SERIAL PRIMARY KEY, parent_ou INT, ou_type INT NOT NULL, - address INT NOT NULL, - name1 TEXT NOT NULL, - name2 TEXT + address INT, + shortname TEXT NOT NULL, + name TEXT ); +CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou); +CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type); +CREATE INDEX actor_org_unit_address_idx ON actor.org_unit (address); + +-- Some PINES test libraries +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (NULL, 1, 'PINES', 'Georgia PINES Consortium'); + +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 2, 'ARL', 'Athens Regional Library System'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 3, 'ARL-ATH', 'Athens-Clark County Library'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 3, 'ARL-BOG', 'Bogart Branch Library'); + +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 2, 'MGRL', 'Middle Georgia Regional Library System'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 3, 'MGRL-RC', 'Rocky Creek Branch Library'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 3, 'MGRL-WA', 'Washington Memorial Library'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 4, 'MGRL-MM', 'Bookmobile'); + +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 2, 'HOU', 'Houston County Library System'); +INSERT INTO actor.org_unit (parent_ou, ou_type, shortname, name) VALUES (curval('actor.org_unit_id_seq'::TEXT), 3, 'HOU-WR', 'Nola Brantley Memorial Library'); CREATE TABLE actor.usr_access_entry ( id BIGSERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Postgres/010.schema.biblio.sql b/Open-ILS/src/sql/Postgres/010.schema.biblio.sql index 42be4479c2..158a3a4cc5 100644 --- a/Open-ILS/src/sql/Postgres/010.schema.biblio.sql +++ b/Open-ILS/src/sql/Postgres/010.schema.biblio.sql @@ -19,10 +19,16 @@ CREATE TABLE biblio.record_entry ( active BOOL NOT NULL DEFAULT TRUE, deleted BOOL NOT NULL DEFAULT FALSE, source INT, - mods TEXT, - last_xact_id TEXT NOT NULL DEFAULT 'none', - CONSTRAINT unique_tcn UNIQUE (tcn_source,tcn_value) + last_xact_id TEXT NOT NULL DEFAULT 'none' ); +CREATE INDEX biblio_record_entry_creator_idx ON biblio.record_note ( creator ); +CREATE INDEX biblio_record_entry_editor_idx ON biblio.record_note ( editor ); +CREATE UNIQUE INDEX biblio_record_unique_tcn ON (tcn_source,tcn_value) WHERE deleted IS FALSE; + +CREATE TABLE biblio.record_mods ( + id BIGINT PRIMARY KEY, + mods TEXT NOT NULL +) CREATE TABLE biblio.record_data ( id BIGSERIAL PRIMARY KEY, @@ -46,5 +52,8 @@ CREATE TABLE biblio.record_note ( create_date TIMESTAMP NOT NULL DEFAULT now(), edit_date TIMESTAMP NOT NULL DEFAULT now() ); +CREATE INDEX biblio_record_note_record_idx ON biblio.record_note ( record ); +CREATE INDEX biblio_record_note_creator_idx ON biblio.record_note ( creator ); +CREATE INDEX biblio_record_note_editor_idx ON biblio.record_note ( editor ); COMMIT; diff --git a/Open-ILS/src/sql/Postgres/030.schema.metabib.sql b/Open-ILS/src/sql/Postgres/030.schema.metabib.sql index de752fa8df..ec4415c64b 100644 --- a/Open-ILS/src/sql/Postgres/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Postgres/030.schema.metabib.sql @@ -71,39 +71,39 @@ CREATE TRIGGER metabib_full_rec_fti_trigger FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); CREATE TABLE metabib.title_field_entry_source_map ( - id BIGSERIAL PRIMARY KEY, - metarecord BIGINT NOT NULL, - field_entry BIGINT NOT NULL UNIQUE, + field_entry BIGINT NOT NULL, source_record BIGINT NOT NULL ); -CREATE INDEX metabib_title_field_entry_source_map_metarecord_idx ON metabib.title_field_entry_source_map (metarecord); CREATE INDEX metabib_title_field_entry_source_map_source_record_idx ON metabib.title_field_entry_source_map (source_record); +CREATE INDEX metabib_title_field_entry_source_map_field_entry_idx ON metabib.title_field_entry_source_map (field_entry); CREATE TABLE metabib.author_field_entry_source_map ( - id BIGSERIAL PRIMARY KEY, - metarecord BIGINT NOT NULL, - field_entry BIGINT NOT NULL UNIQUE, + field_entry BIGINT NOT NULL, source_record BIGINT NOT NULL ); -CREATE INDEX metabib_author_field_entry_source_map_metarecord_idx ON metabib.author_field_entry_source_map (metarecord); -CREATE INDEX metabib_author_field_entry_source_map_source_record_idx ON metabib.author_field_entry_source_map (metarecord); +CREATE INDEX metabib_author_field_entry_source_map_source_record_idx ON metabib.author_field_entry_source_map (source_record); +CREATE INDEX metabib_author_field_entry_source_map_field_entry_idx ON metabib.author_field_entry_source_map (field_entry); CREATE TABLE metabib.subject_field_entry_source_map ( - id BIGSERIAL PRIMARY KEY, - metarecord BIGINT NOT NULL, - field_entry BIGINT NOT NULL UNIQUE, + field_entry BIGINT NOT NULL, source_record BIGINT NOT NULL ); -CREATE INDEX metabib_subject_field_entry_source_map_metarecord_idx ON metabib.subject_field_entry_source_map (metarecord); -CREATE INDEX metabib_subject_field_entry_source_map_source_record_idx ON metabib.subject_field_entry_source_map (metarecord); +CREATE INDEX metabib_subject_field_entry_source_map_source_record_idx ON metabib.subject_field_entry_source_map (source_record); +CREATE INDEX metabib_subject_field_entry_source_map_field_entry_idx ON metabib.subject_field_entry_source_map (field_entry); CREATE TABLE metabib.keyword_field_entry_source_map ( - id BIGSERIAL PRIMARY KEY, - metarecord BIGINT NOT NULL, - field_entry BIGINT NOT NULL UNIQUE, + field_entry BIGINT NOT NULL, source_record BIGINT NOT NULL ); -CREATE INDEX metabib_keyword_field_entry_source_map_metarecord_idx ON metabib.keyword_field_entry_source_map (metarecord); CREATE INDEX metabib_keyword_field_entry_source_map_source_record_idx ON metabib.keyword_field_entry_source_map (source_record); +CREATE INDEX metabib_keyword_field_entry_source_map_field_entry_idx ON metabib.keyword_field_entry_source_map (field_entry); + +CREATE TABLE metabib.metarecord_source_map ( + metarecord BIGINT NOT NULL, + source_record BIGINT NOT NULL +); +CREATE INDEX metabib_metarecord_source_map_metarecord_idx ON metabib.metarecord_source_map (metarecord); +CREATE INDEX metabib_metarecord_source_map_source_record_idx ON metabib.metarecord_source_map (source_record); + COMMIT; diff --git a/Open-ILS/src/sql/Postgres/810.fkeys.biblio.sql b/Open-ILS/src/sql/Postgres/810.fkeys.biblio.sql index 5ae2b33ef1..ea9e93b162 100644 --- a/Open-ILS/src/sql/Postgres/810.fkeys.biblio.sql +++ b/Open-ILS/src/sql/Postgres/810.fkeys.biblio.sql @@ -4,10 +4,12 @@ ALTER TABLE biblio.record_entry ADD CONSTRAINT record_entry_creator_fkey FOREIGN ALTER TABLE biblio.record_entry ADD CONSTRAINT record_entry_editor_fkey FOREIGN KEY ( editor ) REFERENCES actor.usr (id) ON DELETE RESTRICT; ALTER TABLE biblio.record_entry ADD CONSTRAINT record_entry_source_fkey FOREIGN KEY ( source ) REFERENCES config.bib_source (id) ON DELETE RESTRICT; -ALTER TABLE biblio.record_data ADD CONSTRAINT record_data_owner_doc_fkey FOREIGN KEY ( owner_doc ) REFERENCES biblio.record_entry (id) ON DELETE RESTRICT; +ALTER TABLE biblio.record_data ADD CONSTRAINT record_data_owner_doc_fkey FOREIGN KEY ( owner_doc ) REFERENCES biblio.record_entry (id) ON DELETE CASCADE; --ALTER TABLE biblio.record_data ADD CONSTRAINT record_data_parent_node_fkey FOREIGN KEY ( owner_doc,parent_node ) -- REFERENCES biblio.record_data (owner_doc, intra_doc_id) ON DELETE CASCADE; -ALTER TABLE biblio.record_note ADD CONSTRAINT record_note_record_fkey FOREIGN KEY ( record ) REFERENCES biblio.record_entry (id) ON DELETE RESTRICT; +ALTER TABLE biblio.record_note ADD CONSTRAINT record_note_record_fkey FOREIGN KEY ( record ) REFERENCES biblio.record_entry (id) ON DELETE CASCADE; + +ALTER TABLE biblio.record_mods ADD CONSTRAINT record_mods_record_fkey FOREIGN KEY ( id ) REFERENCES biblio.record_entry (id) ON DELETE CASCADE; COMMIT; -- 2.43.2