From 4df11351899972ddc0246fd7d33c0edcc685e751 Mon Sep 17 00:00:00 2001
From: Mark Tompsett <mtompset@hotmail.com>
Date: Fri, 8 Aug 2014 19:55:53 -0400
Subject: [PATCH] Bug 12167: Made SQL even more ANSI SQL.
The comparison against '00-00-0000' is not possible under
PostgreSQL. By providing a database upgrade which replaces all
'0000-00-00' dates with NULL, this portion of the SQL query can
be removed.
Additionally, MySQL can handle CURRENT_DATE(), but PostgreSQL
barfs horribly. By removing the ()'s, it functions in both.
The NewsChannels.t specifically added an explanationdate IS NULL
case to catch what was not tested before.
TEST PLAN
---------
1) Apply patch.
2) ./installer/data/mysql/updatedatabase.pl
3) prove -v t/db_dependent/NewsChannels.t
-- This will confirm that the modified GetNewsToDisplay
function has not broken.
Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>
Update database runs without problem, test pass, no koha-qa errors.
Signed-off-by: Katrin Fischer <Katrin.Fischer.83@web.de>
---
C4/NewsChannels.pm | 5 ++---
installer/data/mysql/updatedatabase.pl | 7 +++++++
t/db_dependent/NewsChannels.t | 25 +++++++++++++++++++++++--
3 files changed, 32 insertions(+), 5 deletions(-)
diff --git a/C4/NewsChannels.pm b/C4/NewsChannels.pm
index fc820d9..b6c7f00 100644
--- a/C4/NewsChannels.pm
+++ b/C4/NewsChannels.pm
@@ -194,15 +194,14 @@ sub GetNewsToDisplay {
WHERE (
expirationdate >= CURRENT_DATE()
OR expirationdate IS NULL
- OR expirationdate = '00-00-0000'
)
- AND CAST( opac_news.timestamp AS DATE ) <= CURRENT_DATE()
+ AND CAST( opac_news.timestamp AS DATE ) <= CURRENT_DATE
AND (lang = '' OR lang = ?)
AND (branchcode IS NULL OR branchcode = ?)
ORDER BY number
}; # expirationdate field is NOT in ISO format?
# casting timestamp to date truncates HH:mm:ss
- # changing to <= CURRENT_DATE() today is captured.
+ # changing to <= CURRENT_DATE, today is captured.
my $sth = $dbh->prepare($query);
$lang = $lang // q{};
$sth->execute($lang,$branch);
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index 5d1b778..119aae2 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -10267,6 +10267,13 @@ if ( CheckVersion($DBversion) ) {
SetVersion($DBversion);
}
+$DBversion = "3.19.00.XXX";
+if ( CheckVersion($DBversion) ) {
+ $dbh->do("UPDATE opac_news SET expirationdate=NULL WHERE expirationdate='0000-00-00';");
+ print "Upgrade to $DBversion done (Bug 12167: Clean up date and time type casting issues)\n";
+ SetVersion ($DBversion);
+}
+
# DEVELOPER PROCESS, search for anything to execute in the db_update directory
# SEE bug 13068
# if there is anything in the atomicupdate, read and execute it.
diff --git a/t/db_dependent/NewsChannels.t b/t/db_dependent/NewsChannels.t
index 332b396..6a817e0 100644
--- a/t/db_dependent/NewsChannels.t
+++ b/t/db_dependent/NewsChannels.t
@@ -3,7 +3,7 @@
use Modern::Perl;
use C4::Dates qw(format_date);
use C4::Branch qw(GetBranchName);
-use Test::More tests => 12;
+use Test::More tests => 14;
BEGIN {
use_ok('C4::NewsChannels');
@@ -27,7 +27,7 @@ my $rv = add_opac_new(); # intentionally bad
ok( $rv == 0, 'Correctly failed on no parameter!' );
my $timestamp = '2000-01-01';
-my ( $timestamp1, $timestamp2 ) = ( $timestamp, $timestamp );
+my ( $timestamp1, $timestamp2, $timestamp3 ) = ( $timestamp, $timestamp, $timestamp );
my ( $title1, $new1, $lang1, $expirationdate1, $number1 ) =
( 'News Title', '<p>We have some exciting news!</p>', q{}, '2999-12-30', 1 );
my $href_entry1 = {
@@ -57,6 +57,20 @@ my $href_entry2 = {
$rv = add_opac_new($href_entry2);
ok( $rv == 1, 'Successfully added the second dummy news item!' );
+my ( $title3, $new3, $lang3, $expirationdate3, $number3 ) =
+ ( 'News Title3', '<p>We make news exciting!</p>', q{}, undef, 1 );
+my $href_entry3 = {
+ title => $title3,
+ new => $new3,
+ lang => $lang3,
+ expirationdate => $expirationdate3,
+ timestamp => $timestamp3,
+ number => $number3,
+ branchcode => 'LIB1',
+};
+$rv = add_opac_new($href_entry3);
+ok( $rv == 1, 'Successfully added the third dummy news item!' );
+
# We need to determine the idnew in a non-MySQLism way.
# This should be good enough.
my $query =
@@ -141,4 +155,11 @@ ok( ref $arrayref_opac_news eq 'ARRAY',
'$arrayref_opac_news is an array reference.' );
ok( (scalar @$arrayref_opac_news) >= 2, 'Successfully tested GetNewsToDisplay for LIB1!' );
+$query =
+q{ SELECT idnew from opac_news WHERE timestamp='2000-01-01' AND expirationdate IS NULL; };
+$sth = $dbh->prepare($query);
+$sth->execute();
+my $nullcheck = $sth->fetchrow();
+ok ($nullcheck && $nullcheck>=0, 'Successfully found third dummy newsitem.');
+
$dbh->rollback;
--
1.9.1