From 4d1eabea4059a99719bfc3f4d2390e7b8ecb7c5a Mon Sep 17 00:00:00 2001 From: Paul Poulain Date: Wed, 20 Jun 2012 09:07:34 +0200 Subject: [PATCH] Borrowers table management first draft, the interesting part is the testrelation.pl script that shows how to search a patron and access other informations from here What is important is the "prefetch" option in read. With it you'll LEFT join the prefetched table, thus improving performances You can also notice the cache duration of 2 seconds in borrower search. The idea is that, if this sub is called more than once on a given page, there will be only one SQL query. http://bugs.koha-community.org/show_bug.cgi?id=8309 --- Koha/BusinessLogic/Borrower.pm | 5 +++ Koha/DB/Borrower.pm | 43 +++++++++++++++++++++ Koha/DataObject/Borrower.pm | 5 +++ testrelations.pl | 80 ++++++++++++++++++++++++++++++++++++++++ 4 files changed, 133 insertions(+) create mode 100644 Koha/BusinessLogic/Borrower.pm create mode 100644 Koha/DB/Borrower.pm create mode 100644 Koha/DataObject/Borrower.pm create mode 100755 testrelations.pl diff --git a/Koha/BusinessLogic/Borrower.pm b/Koha/BusinessLogic/Borrower.pm new file mode 100644 index 0000000..3ed88d5 --- /dev/null +++ b/Koha/BusinessLogic/Borrower.pm @@ -0,0 +1,5 @@ +package Koha::BusinessLogic::Borrower; + +use Modern::Perl; +use Moose; +extends (qw(Koha::DataObject::Borrower)); \ No newline at end of file diff --git a/Koha/DB/Borrower.pm b/Koha/DB/Borrower.pm new file mode 100644 index 0000000..9f033f5 --- /dev/null +++ b/Koha/DB/Borrower.pm @@ -0,0 +1,43 @@ +package Koha::DB::Borrower; + +use Modern::Perl; +use C4::Context; #FIXME = create a Koha package for KOHA_CONF reading + +use Koha::Schema; + +use Moose; + +has 'borrowernumber' => (is => 'rw', required => 1, isa => 'Str'); +has 'surname' => (is => 'rw', required => 0, isa => 'Str'); +has 'firstname' => (is => 'rw', required => 0, isa => 'Str'); +has 'branchcode' => (is => 'rw', required => 0, isa => 'Str'); + + +my $schema = Koha::Schema->connect( + 'dbi:mysql:dbname='.C4::Context->config("database"), + C4::Context->config("user"), + C4::Context->config("pass"), + { AutoCommit => 1 }, + ); + +sub create { + my ($self) = @_; + $schema->resultset('Borrower')->create({ map { $_ => $self->$_ } $schema->source('Borrower')->columns }); +} + +sub read { + my ($self, $borrower) = @_; + # load parameter tables attached to the borrower (branches and categories) + return $schema->resultset('Borrower')->search($borrower, { prefetch => ['branchcode','categorycode'], { cache_for => 2 }}); +} + +sub update { + my ($self, $borrower) = @_; + return $schema->resultset('Borrower')->search({borrowernumber => $borrower->{borrowernumber}})->update($borrower); +} + +sub delete { + my ($self,$borrower) = @_; + $schema->resultset('Borrower')->search($borrower)->delete; +} + diff --git a/Koha/DataObject/Borrower.pm b/Koha/DataObject/Borrower.pm new file mode 100644 index 0000000..3991417 --- /dev/null +++ b/Koha/DataObject/Borrower.pm @@ -0,0 +1,5 @@ +package Koha::DataObject::Borrower; + +use Modern::Perl; +use Moose; +extends (qw(Koha::DB::Borrower)); diff --git a/testrelations.pl b/testrelations.pl new file mode 100755 index 0000000..6fba712 --- /dev/null +++ b/testrelations.pl @@ -0,0 +1,80 @@ +#!/usr/bin/perl + +use v5.10.0; +use Modern::Perl; +#use Koha::BusinessLogic::Branch2; +#use Koha::BusinessLogic::Branchrelation; +use Koha::BusinessLogic::Borrower; + +# how many SQL queries did mysql recieved before this script is executed +my (undef, $before) = split ' ',`mysql -e "show status"|grep Queries`; + +my$borrowernumber='7695'; + +# retrieve a patron +my $borrower=Koha::BusinessLogic::Borrower->read({'borrowernumber' => $borrowernumber})->first; +# show his name +say $borrower->firstname; +say $borrower->surname; +# show the branchname, very easy !!! +say $borrower->branchcode->branchname; +# same thing with category +say $borrower->categorycode->description; +# now retrieve all items issued to this patron +# note that each line result in 3 SQL queries being done +# possible improvement => prefetch the link between +foreach my $checkout ($borrower->issues->all) { + # show the itemnumber + say "Item currently checked-out:".$checkout->itemnumber->itemnumber; + # show book title + say "Item currently checked-out:".$checkout->itemnumber->biblioitemnumber->biblionumber->title; +} +foreach my $checkout ($borrower->old_issues->all) { + # show the itemnumber + say "Item previously checked-out:".$checkout->itemnumber->itemnumber; + # show book title + say "Item previously checked-out:".$checkout->itemnumber->biblioitemnumber->biblionumber->title; +} + +#use Data::Dumper; +# get 1st categorycode +#say "Fetching 1st categorycode"; +#my $branch=Koha::BusinessLogic::Branch2->read({'branchcode' => 'MAURES'})->first; +#say $branch->branchname; +#my $branchrelation = $branch->branchrelations->first; +#say "CODE".$branchrelation->categorycode->categorycode; +# this also add a SQL query, why ? +#say "BRANCH".$branchrelation->branchcode->branchcode; + +# +#say Koha::BusinessLogic::Branch2->read({'branchcode' => 'TEST'})->first->branchrelations->first->categorycode->categorycode; +# the following line add 3 queries => no caching at all +#say Koha::BusinessLogic::Branch2->read({'branchcode' => 'TEST'})->first->branchrelations->first->branchcode->branchcode; + +# get all categorycodes +#say "Fetching all categorycodes"; +#foreach (Koha::BusinessLogic::Branch2->read({'branchcode' => 'TEST'})->first->branchrelations->all) { +# say $_->categorycode->categorycode; +#} + +# how many SQL queries did mysql recieve after this script is executed +my (undef, $after) = split ' ',`mysql -e "show status"|grep Queries`; +# the difference between before and after tell us how many queries have been made +say "Total queries: ".($after-$before-3); # -3 is the number of queries "show status" itself requires + +# testing column = it does not require any SQL query, ++ ! +#my $schema = Koha::Schema->connect( +# 'dbi:mysql:dbname='.C4::Context->config("database"), +# C4::Context->config("user"), +# C4::Context->config("pass"), +# { AutoCommit => 1, +# #cursor_class => 'DBIx::Class::Cursor::Cached', +# }, +# ); +#(undef, $before) = split ' ',`mysql -e "show status"|grep Queries`; +#my @temp = $schema->source('Branch')->columns; +#say @temp; +#(undef, $after) = split ' ',`mysql -e "show status"|grep Queries`; +## the difference between before and after tell us how many queries have been made +#say "Total queries for columns: ".($after-$before-3); # -3 is the number of queries "show status" itself requires +# \ No newline at end of file -- 1.7.9.5