From 07716ca15bdc8164dcbc6d70fa1e3f5b8bffcff3 Mon Sep 17 00:00:00 2001 From: Mark Tompsett Date: Sat, 29 Jun 2013 20:46:51 -0400 Subject: [PATCH] Bug 7785: remove MySQL-specific syntax from C4::Members::columns() The initial thought was to remove this function. However, tools/import_borrowers.pl uses it. So rather than remove it to solve the problem, it was reworked to a more generic solution which runs faster. By accessing $sth->{NAME} directly, the driver becomes responsible for filling it correctly. This happens when a SELECT is done on the borrowers table. It does not even have to have data in the result set! The columns method could be more generic and used elsewhere too. Comparison between the old method and the STH method showed a significant time difference. The old method took 35 seconds for 40k iterations versus 19 seconds for the same amount of iterations with the STH method regardless of the size of the borrowers table. Signed-off-by: Srdjan Signed-off-by: Jonathan Druart Signed-off-by: Galen Charlton --- C4/Members.pm | 48 ++++++++++++++++++++++++++++++-- t/db_dependent/Members_columns.t | 24 ++++++++++++++++ 2 files changed, 70 insertions(+), 2 deletions(-) create mode 100644 t/db_dependent/Members_columns.t diff --git a/C4/Members.pm b/C4/Members.pm index 6d8d23ae7d..f3dbebde18 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -695,10 +695,54 @@ sub GetMemberIssuesAndFines { return ($overdue_count, $issue_count, $total_fines); } -sub columns(;$) { - return @{C4::Context->dbh->selectcol_arrayref("SHOW columns from borrowers")}; + +=head2 columns + + C4::Member->columns + +=head3 USAGE + + use C4::Member; + my @borrower_columns = C4::Member->columns; + +=head3 RETURNS + + The array of borrowers' table columns on success. + An empty array on failure. + +=head3 NOTES + + This runs significantly faster than the previous code while + being mostly SQL-agnostic. + +=cut + +sub columns { + + # Pure ANSI SQL goodness. + my $sql = 'SELECT * FROM borrowers WHERE 1=0;'; + + # Get the database handle. + my $dbh = C4::Context->dbh; + + # Run the SQL statement to load STH's readonly properties. + my $sth = $dbh->prepare($sql); + my $rv = $sth->execute(); + + # This only fails if the table doesn't exist. + # This will always be called AFTER an install or upgrade, + # so borrowers will exist! + my @data; + if ($sth->{NUM_OF_FIELDS}>0) { + @data = @{$sth->{NAME}}; + } + else { + @data = (); + } + return @data; } + =head2 ModMember my $success = ModMember(borrowernumber => $borrowernumber, diff --git a/t/db_dependent/Members_columns.t b/t/db_dependent/Members_columns.t new file mode 100644 index 0000000000..7a70ce41d1 --- /dev/null +++ b/t/db_dependent/Members_columns.t @@ -0,0 +1,24 @@ +#!/usr/bin/perl +# +# This is to test C4/Members +# It requires a working Koha database with the sample data + +use Modern::Perl; + +use Test::More tests => 2; + +BEGIN { + use_ok('C4::Members'); +} + +my @borrowers_columns = C4::Members->columns; +ok( + $#borrowers_columns > 1, + 'C4::Member->column returned a reasonable number of columns (' + . ( $#borrowers_columns + 1 ) . ')' + ) + or diag( +'WARNING: Check that the borrowers table exists and has the correct fields defined.' + ); + +exit; -- 2.39.5