Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg.pm

   1 { # Based on the change to Class::DBI in OpenILS::Application::Storage.  This will
   2   # allow us to use TSearch2 via a simple cdbi "search" interface.
   3         #-------------------------------------------------------------------------------
   4         use Class::DBI;
   5         package Class::DBI;
   6
   7         sub search_fti {
   8                 my $self = shift;
   9                 my @args = @_;
  10                 if (ref($args[-1]) eq 'HASH') {
  11                         $args[-1]->{_placeholder} = "to_tsquery('default',?)";
  12                 } else {
  13                         push @args, {_placeholder => "to_tsquery('default',?)"};
  14                 }
  15                 $self->_do_search("@@"  => @args);
  16         }
  17 }
  18
  19 { # Every driver needs to provide a 'compile()' method to OpenILS::Application::Storage::FTS.
  20   # If that driver wants to support FTI, that is...
  21         #-------------------------------------------------------------------------------
  22         package OpenILS::Application::Storage::FTS;
  23         use OpenSRF::Utils::Logger qw/:level/;
  24         my $log = 'OpenSRF::Utils::Logger';
  25
  26         sub compile {
  27                 my $self = shift;
  28                 my $term = shift;
  29
  30                 $self = ref($self) || $self;
  31                 $self = bless {} => $self;
  32
  33                 $self->decompose($term);
  34
  35                 my $newterm = join('&', $self->words);
  36
  37                 if (@{$self->nots}) {
  38                         $newterm = '('.$newterm.')&('. join('|', $self->nots) . ')';
  39                 }
  40
  41                 $log->debug("Compiled term is [$newterm]", DEBUG);
  42                 $newterm = OpenILS::Application::Storage::Driver::Pg->quote($newterm);
  43                 $log->debug("Quoted term is [$newterm]", DEBUG);
  44
  45                 $self->{fts_query} = ["to_tsquery('default',$newterm)"];
  46                 $self->{fts_query_nots} = [];
  47                 $self->{fts_op} = '@@';
  48                 $self->{text_col} = shift;
  49                 $self->{fts_col} = shift;
  50
  51                 return $self;
  52         }
  53
  54         sub sql_where_clause {
  55                 my $self = shift;
  56                 my $column = $self->fts_col;
  57                 my @output;
  58
  59                 my @ranks;
  60                 for my $fts ( $self->fts_query ) {
  61                         push @output, join(' ', $self->fts_col, $self->{fts_op}, $fts);
  62                         push @ranks, "rank($column, $fts)";
  63                 }
  64                 $self->{fts_rank} = \@ranks;
  65
  66                 my $phrase_match = $self->sql_exact_phrase_match();
  67                 return join(' AND ', @output) . $phrase_match;
  68         }
  69
  70 }
  71
  72
  73 { # The driver package itself just needs a db_Main method (or db_Slaves if
  74   #Class::DBI::Replication is in use) for Class::DBI to call.
  75   #
  76   # Any other fixups can go in here too... Also, the drivers should subclass the
  77   # DBI driver that they are wrapping, or provide a 'quote()' method that calls
  78   # the DBD::xxx::quote() method on FTI's behalf.
  79   #
  80   # The dirver MUST be a subclass of Class::DBI(::Replication) and
  81   # OpenILS::Application::Storage.
  82   #-------------------------------------------------------------------------------
  83         package OpenILS::Application::Storage::Driver::Pg;
  84         use Class::DBI;
  85         use base qw/Class::DBI OpenILS::Application::Storage/;
  86         use DBI;
  87         use OpenSRF::EX qw/:try/;
  88         use OpenSRF::DomainObject::oilsResponse;
  89         use OpenSRF::Utils::Logger qw/:level/;
  90         my $log = 'OpenSRF::Utils::Logger';
  91
  92         __PACKAGE__->set_sql( retrieve_limited => 'SELECT * FROM __TABLE__ ORDER BY id LIMIT ?' );
  93         __PACKAGE__->set_sql( copy_start => 'COPY %s (%s) FROM STDIN;' );
  94         __PACKAGE__->set_sql( copy_end => '\.' );
  95
  96         my $master_db;
  97         my @slave_dbs;
  98         my $_db_params;
  99         sub child_init {
 100                 my $self = shift;
 101                 $_db_params = shift;
 102
 103                 $log->debug("Running child_init inside ".__PACKAGE__, INTERNAL);
 104
 105                 $_db_params = [ $_db_params ] unless (ref($_db_params) eq 'ARRAY');
 106
 107                 my %attrs = (   %{$self->_default_attributes},
 108                                 RootClass => 'DBIx::ContextualFetch',
 109                                 ShowErrorStatement => 1,
 110                                 RaiseError => 1,
 111                                 AutoCommit => 1,
 112                                 PrintError => 1,
 113                                 Taint => 1,
 114                                 pg_enable_utf8 => 1,
 115                                 FetchHashKeyName => 'NAME_lc',
 116                                 ChopBlanks => 1,
 117                 );
 118
 119                 my $master = shift @$_db_params;
 120                 $master_db = DBI->connect("dbi:Pg:host=$$master{host};dbname=$$master{db}",$$master{user},$$master{pw}, \%attrs);
 121                 $master_db->do("SET NAMES '$$master{client_encoding}';") if ($$master{client_encoding});
 122
 123                 $log->debug("Connected to MASTER db '$$master{db} at $$master{host}", INFO);
 124
 125                 for my $db (@$_db_params) {
 126                         push @slave_dbs, DBI->connect("dbi:Pg:host=$$db{host};dbname=$$db{db}",$$db{user},$$db{pw}, \%attrs);
 127                         $slave_dbs[-1]->do("SET NAMES '$$db{client_encoding}';") if ($$master{client_encoding});
 128
 129                         $log->debug("Connected to MASTER db '$$master{db} at $$master{host}", INFO);
 130                 }
 131
 132                 $log->debug("All is well on the western front", INTERNAL);
 133         }
 134
 135         sub db_Main {
 136                 my $self = shift;
 137                 return $master_db if ($self->current_xact_session);
 138                 return $master_db unless (@slave_dbs);
 139                 return ($master_db, @slave_dbs)[rand(scalar(@slave_dbs))];
 140         }
 141
 142         sub quote {
 143                 my $self = shift;
 144                 return $self->db_Main->quote(@_)
 145         }
 146
 147 #       sub tsearch2_trigger {
 148 #               my $self = shift;
 149 #               return unless ($self->value);
 150 #               $self->index_vector(
 151 #                       $self->db_Slaves->selectrow_array(
 152 #                               "SELECT to_tsvector('default',?);",
 153 #                               {},
 154 #                               $self->value
 155 #                       )
 156 #               );
 157 #       }
 158
 159         my $_xact_session;
 160
 161         sub current_xact_session {
 162                 my $self = shift;
 163                 if (defined($_xact_session)) {
 164                         return $_xact_session;
 165                 }
 166                 return undef;
 167         }
 168
 169         sub current_xact_is_auto {
 170                 my $self = shift;
 171                 my $auto = shift;
 172                 if (defined($_xact_session) and ref($_xact_session)) {
 173                         if (defined $auto) {
 174                                 $_xact_session->session_data(autocommit => $auto);
 175                         }
 176                         return $_xact_session->session_data('autocommit');
 177                 }
 178         }
 179
 180         sub current_xact_id {
 181                 my $self = shift;
 182                 if (defined($_xact_session) and ref($_xact_session)) {
 183                         return $_xact_session->session_id;
 184                 }
 185                 return undef;
 186         }
 187
 188         sub set_xact_session {
 189                 my $self = shift;
 190                 my $ses = shift;
 191                 if (!defined($ses)) {
 192                         return undef;
 193                 }
 194                 $_xact_session = $ses;
 195                 return $_xact_session;
 196         }
 197
 198         sub unset_xact_session {
 199                 my $self = shift;
 200                 my $ses = $_xact_session;
 201                 undef $_xact_session;
 202                 return $ses;
 203         }
 204
 205 }
 206
 207
 208 {
 209         package OpenILS::Application::Storage;
 210         use OpenSRF::Utils::Logger;
 211         my $log = 'OpenSRF::Utils::Logger';
 212
 213         my $pg = 'OpenILS::Application::Storage::Driver::Pg';
 214
 215         sub pg_begin_xaction {
 216                 my $self = shift;
 217                 my $client = shift;
 218
 219                 if (my $old_xact = $pg->current_xact_session) {
 220                         if ($pg->current_xact_is_auto) {
 221                                 $log->debug("Commiting old autocommit transaction with Open-ILS XACT-ID [$old_xact]", INFO);
 222                                 $self->pg_commit_xaction($client);
 223                         } else {
 224                                 $log->debug("Rolling back old NON-autocommit transaction with Open-ILS XACT-ID [$old_xact]", INFO);
 225                                 $self->pg_rollback_xaction($client);
 226                                 return new OpenSRF::DomainObject::oilsException (
 227                                                 statusCode => 500,
 228                                                 status => "Previous transaction rolled back!",
 229                                 );
 230                         }
 231                 }
 232
 233                 $pg->set_xact_session( $client->session );
 234                 my $xact_id = $pg->current_xact_id;
 235
 236                 $log->debug("Beginning a new trasaction with Open-ILS XACT-ID [$xact_id]", INFO);
 237
 238                 my $dbh = OpenILS::Application::Storage::CDBI->db_Main;
 239
 240                 try {
 241                         $dbh->begin_work;
 242
 243                 } catch Error with {
 244                         my $e = shift;
 245                         $log->debug("Failed to begin a new trasaction with Open-ILS XACT-ID [$xact_id]: ".$e, INFO);
 246                         return $e;
 247                 };
 248
 249
 250                 my $death_cb = $client->session->register_callback(
 251                         death => sub {
 252                                 __PACKAGE__->pg_rollback_xaction;
 253                         }
 254                 );
 255
 256                 $log->debug("Registered 'death' callback [$death_cb] for new trasaction with Open-ILS XACT-ID [$xact_id]", DEBUG);
 257
 258                 $client->session->session_data( death_cb => $death_cb );
 259
 260                 if ($self->api_name =~ /autocommit$/o) {
 261                         $pg->current_xact_is_auto(1);
 262                         my $dc_cb = $client->session->register_callback(
 263                                 disconnect => sub {
 264                                         my $ses = shift;
 265                                         $ses->unregister_callback(death => $death_cb);
 266                                         __PACKAGE__->pg_commit_xaction;
 267                                 }
 268                         );
 269                         $log->debug("Registered 'disconnect' callback [$dc_cb] for new trasaction with Open-ILS XACT-ID [$xact_id]", DEBUG);
 270                         if ($client and $client->session) {
 271                                 $client->session->session_data( disconnect_cb => $dc_cb );
 272                         }
 273                 }
 274
 275                 return 1;
 276
 277         }
 278         __PACKAGE__->register_method(
 279                 method          => 'pg_begin_xaction',
 280                 api_name        => 'open-ils.storage.transaction.begin',
 281                 api_level       => 1,
 282                 argc            => 0,
 283         );
 284         __PACKAGE__->register_method(
 285                 method          => 'pg_begin_xaction',
 286                 api_name        => 'open-ils.storage.transaction.begin.autocommit',
 287                 api_level       => 1,
 288                 argc            => 0,
 289         );
 290
 291         sub pg_commit_xaction {
 292                 my $self = shift;
 293
 294                 my $xact_id = $pg->current_xact_id;
 295
 296                 try {
 297                         $log->debug("Committing trasaction with Open-ILS XACT-ID [$xact_id]", INFO);
 298                         my $dbh = OpenILS::Application::Storage::CDBI->db_Main;
 299                         $dbh->commit;
 300
 301                 } catch Error with {
 302                         my $e = shift;
 303                         $log->debug("Failed to commit trasaction with Open-ILS XACT-ID [$xact_id]: ".$e, INFO);
 304                         return 0;
 305                 };
 306
 307                 $pg->current_xact_session->unregister_callback( death =>
 308                         $pg->current_xact_session->session_data( 'death_cb' )
 309                 ) if ($pg->current_xact_session);
 310
 311                 if ($pg->current_xact_is_auto) {
 312                         $pg->current_xact_session->unregister_callback( disconnect =>
 313                                 $pg->current_xact_session->session_data( 'disconnect_cb' )
 314                         );
 315                 }
 316
 317                 $pg->unset_xact_session;
 318
 319                 return 1;
 320
 321         }
 322         __PACKAGE__->register_method(
 323                 method          => 'pg_commit_xaction',
 324                 api_name        => 'open-ils.storage.transaction.commit',
 325                 api_level       => 1,
 326                 argc            => 0,
 327         );
 328
 329         sub pg_rollback_xaction {
 330                 my $self = shift;
 331
 332                 my $xact_id = $pg->current_xact_id;
 333                 try {
 334                         my $dbh = OpenILS::Application::Storage::CDBI->db_Main;
 335                         $log->debug("Rolling back a trasaction with Open-ILS XACT-ID [$xact_id]", INFO);
 336                         $dbh->rollback;
 337
 338                 } catch Error with {
 339                         my $e = shift;
 340                         $log->debug("Failed to roll back trasaction with Open-ILS XACT-ID [$xact_id]: ".$e, INFO);
 341                         return 0;
 342                 };
 343
 344                 $pg->current_xact_session->unregister_callback( death =>
 345                         $pg->current_xact_session->session_data( 'death_cb' )
 346                 ) if ($pg->current_xact_session);
 347
 348                 if ($pg->current_xact_is_auto) {
 349                         $pg->current_xact_session->unregister_callback( disconnect =>
 350                                 $pg->current_xact_session->session_data( 'disconnect_cb' )
 351                         );
 352                 }
 353
 354                 $pg->unset_xact_session;
 355
 356                 return 1;
 357         }
 358         __PACKAGE__->register_method(
 359                 method          => 'pg_rollback_xaction',
 360                 api_name        => 'open-ils.storage.transaction.rollback',
 361                 api_level       => 1,
 362                 argc            => 0,
 363         );
 364
 365         sub copy_create {
 366                 my $self = shift;
 367                 my $client = shift;
 368                 my @fm_nodes = @_;
 369
 370                 warn 'Inside copy_create...';
 371
 372                 return undef unless ($pg->current_xact_session);
 373
 374                 my $cdbi = $self->{cdbi};
 375
 376                 my $pri = $cdbi->columns('Primary');
 377
 378                 my @cols = grep {$_ ne $pri} $cdbi->columns('All');
 379
 380                 my $col_list = join ',', @cols;
 381
 382                 $log->debug('Starting COPY import for '.$cdbi->table, DEBUG);
 383                 $cdbi->sql_copy_start($cdbi->table, $col_list)->execute;
 384
 385                 my $dbh = $cdbi->db_Main;
 386                 for my $node ( @fm_nodes ) {
 387                         next unless ($node);
 388                         my $line = join("\t", map { defined($node->$_()) ? $node->$_() : '\N' } @cols);
 389                         $log->debug("COPY line: [$line]",DEBUG);
 390                         $dbh->func($line."\n", 'putline');
 391                 }
 392
 393                 $dbh->func('endcopy');
 394
 395                 return scalar(@fm_nodes);
 396         }
 397
 398 }
 399
 400 {
 401         #---------------------------------------------------------------------
 402         package asset::call_number_note;
 403
 404         asset::call_number->table( 'asset.call_number_note' );
 405         asset::call_number->sequence( 'asset.call_number_note_id_seq' );
 406
 407         #---------------------------------------------------------------------
 408         package asset::copy_note;
 409
 410         asset::copy->table( 'asset.copy_note' );
 411         asset::copy->sequence( 'asset.copy_note_id_seq' );
 412
 413         #---------------------------------------------------------------------
 414         package asset::call_number;
 415
 416         asset::call_number->table( 'asset.call_number' );
 417         asset::call_number->sequence( 'asset.call_number_id_seq' );
 418
 419         #---------------------------------------------------------------------
 420         package asset::copy;
 421
 422         asset::copy->table( 'asset.copy' );
 423         asset::copy->sequence( 'asset.copy_id_seq' );
 424
 425         #---------------------------------------------------------------------
 426         package biblio::record_entry;
 427
 428         biblio::record_entry->table( 'biblio.record_entry' );
 429         biblio::record_entry->sequence( 'biblio.record_entry_id_seq' );
 430
 431         #---------------------------------------------------------------------
 432         package biblio::record_node;
 433
 434         biblio::record_node->table( 'biblio.record_data' );
 435         biblio::record_node->sequence( 'biblio.record_data_id_seq' );
 436
 437         #---------------------------------------------------------------------
 438         package biblio::record_marc;
 439
 440         biblio::record_marc->table( 'biblio.record_marc' );
 441         biblio::record_marc->sequence( 'biblio.record_marc_id_seq' );
 442
 443         #---------------------------------------------------------------------
 444         package biblio::record_mods;
 445
 446         biblio::record_mods->table( 'biblio.record_mods' );
 447         biblio::record_mods->sequence( 'biblio.record_mods_id_seq' );
 448
 449         #---------------------------------------------------------------------
 450         package biblio::record_note;
 451
 452         biblio::record_note->table( 'biblio.record_note' );
 453         biblio::record_note->sequence( 'biblio.record_note_id_seq' );
 454
 455         #---------------------------------------------------------------------
 456         package actor::user;
 457
 458         actor::user->table( 'actor.usr' );
 459         actor::user->sequence( 'actor.usr_id_seq' );
 460
 461         #---------------------------------------------------------------------
 462         package actor::org_unit_type;
 463
 464         actor::org_unit_type->table( 'actor.org_unit_type' );
 465         actor::org_unit_type->sequence( 'actor.org_unit_type_id_seq' );
 466
 467         #---------------------------------------------------------------------
 468         package actor::org_unit;
 469
 470         actor::org_unit->table( 'actor.org_unit' );
 471         actor::org_unit->sequence( 'actor.org_unit_id_seq' );
 472
 473         #---------------------------------------------------------------------
 474
 475         #-------------------------------------------------------------------------------
 476         package metabib::metarecord;
 477
 478         metabib::metarecord->table( 'metabib.metarecord' );
 479         metabib::metarecord->sequence( 'metabib.metarecord_id_seq' );
 480
 481         #-------------------------------------------------------------------------------
 482
 483         #-------------------------------------------------------------------------------
 484         package metabib::title_field_entry;
 485
 486         metabib::title_field_entry->table( 'metabib.title_field_entry' );
 487         metabib::title_field_entry->sequence( 'metabib.title_field_entry_id_seq' );
 488         metabib::title_field_entry->columns( 'FTS' => 'index_vector' );
 489
 490 #       metabib::title_field_entry->add_trigger(
 491 #               before_create => \&OpenILS::Application::Storage::Driver::Pg::tsearch2_trigger
 492 #       );
 493 #       metabib::title_field_entry->add_trigger(
 494 #               before_update => \&OpenILS::Application::Storage::Driver::Pg::tsearch2_trigger
 495 #       );
 496
 497         OpenILS::Application::Storage->register_method(
 498                 api_name        => 'open-ils.storage.metabib.title_field_entry.batch.create',
 499                 method          => 'copy_create',
 500                 api_level       => 1,
 501                 'package'       => 'OpenILS::Application::Storage',
 502                 cdbi            => 'metabib::title_field_entry',
 503         );
 504
 505         #-------------------------------------------------------------------------------
 506
 507         #-------------------------------------------------------------------------------
 508         package metabib::author_field_entry;
 509
 510         metabib::author_field_entry->table( 'metabib.author_field_entry' );
 511         metabib::author_field_entry->sequence( 'metabib.author_field_entry_id_seq' );
 512         metabib::author_field_entry->columns( 'FTS' => 'index_vector' );
 513
 514         OpenILS::Application::Storage->register_method(
 515                 api_name        => 'open-ils.storage.metabib.author_field_entry.batch.create',
 516                 method          => 'copy_create',
 517                 api_level       => 1,
 518                 'package'       => 'OpenILS::Application::Storage',
 519                 cdbi            => 'metabib::author_field_entry',
 520         );
 521
 522         #-------------------------------------------------------------------------------
 523
 524         #-------------------------------------------------------------------------------
 525         package metabib::subject_field_entry;
 526
 527         metabib::subject_field_entry->table( 'metabib.subject_field_entry' );
 528         metabib::subject_field_entry->sequence( 'metabib.subject_field_entry_id_seq' );
 529         metabib::subject_field_entry->columns( 'FTS' => 'index_vector' );
 530
 531         OpenILS::Application::Storage->register_method(
 532                 api_name        => 'open-ils.storage.metabib.subject_field_entry.batch.create',
 533                 method          => 'copy_create',
 534                 api_level       => 1,
 535                 'package'       => 'OpenILS::Application::Storage',
 536                 cdbi            => 'metabib::subject_field_entry',
 537         );
 538
 539         #-------------------------------------------------------------------------------
 540
 541         #-------------------------------------------------------------------------------
 542         package metabib::keyword_field_entry;
 543
 544         metabib::keyword_field_entry->table( 'metabib.keyword_field_entry' );
 545         metabib::keyword_field_entry->sequence( 'metabib.keyword_field_entry_id_seq' );
 546         metabib::keyword_field_entry->columns( 'FTS' => 'index_vector' );
 547
 548         OpenILS::Application::Storage->register_method(
 549                 api_name        => 'open-ils.storage.metabib.keyword_field_entry.batch.create',
 550                 method          => 'copy_create',
 551                 api_level       => 1,
 552                 'package'       => 'OpenILS::Application::Storage',
 553                 cdbi            => 'metabib::keyword_field_entry',
 554         );
 555
 556         #-------------------------------------------------------------------------------
 557
 558         #-------------------------------------------------------------------------------
 559         #package metabib::title_field_entry_source_map;
 560
 561         #metabib::title_field_entry_source_map->table( 'metabib.title_field_entry_source_map' );
 562
 563         #-------------------------------------------------------------------------------
 564
 565         #-------------------------------------------------------------------------------
 566         #package metabib::author_field_entry_source_map;
 567
 568         #metabib::author_field_entry_source_map->table( 'metabib.author_field_entry_source_map' );
 569
 570         #-------------------------------------------------------------------------------
 571
 572         #-------------------------------------------------------------------------------
 573         #package metabib::subject_field_entry_source_map;
 574
 575         #metabib::subject_field_entry_source_map->table( 'metabib.subject_field_entry_source_map' );
 576
 577         #-------------------------------------------------------------------------------
 578
 579         #-------------------------------------------------------------------------------
 580         #package metabib::keyword_field_entry_source_map;
 581
 582         #metabib::keyword_field_entry_source_map->table( 'metabib.keyword_field_entry_source_map' );
 583
 584         #-------------------------------------------------------------------------------
 585
 586         #-------------------------------------------------------------------------------
 587         package metabib::metarecord_source_map;
 588
 589         metabib::metarecord_source_map->table( 'metabib.metarecord_source_map' );
 590
 591         #-------------------------------------------------------------------------------
 592
 593         #-------------------------------------------------------------------------------
 594         package metabib::full_rec;
 595
 596         metabib::full_rec->table( 'metabib.full_rec' );
 597         metabib::full_rec->sequence( 'metabib.full_rec_id_seq' );
 598         metabib::full_rec->columns( 'FTS' => 'index_vector' );
 599
 600         OpenILS::Application::Storage->register_method(
 601                 api_name        => 'open-ils.storage.metabib.full_rec.batch.create',
 602                 method          => 'copy_create',
 603                 api_level       => 1,
 604                 'package'       => 'OpenILS::Application::Storage',
 605                 cdbi            => 'metabib::full_rec',
 606         );
 607
 608
 609         #-------------------------------------------------------------------------------
 610 }
 611
 612 1;