Attachment 30642 Details for Bug 12167 – Bug 12167 - Made SQL even more ANSI SQL.

[patch] Bug 12167 - Made SQL even more ANSI SQL.

Bug-12167---Made-SQL-even-more-ANSI-SQL.patch (text/plain), 4.61 KB, created by Mark Tompsett on 2014-08-09 00:33:04 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Mark Tompsett

Created: 2014-08-09 00:33:04 UTC

Size: 4.61 KB

patch

obsolete

>From bfcd032a058e6675666ed26bd40b174690b13da2 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.
>---
> C4/NewsChannels.pm                     |    5 ++---
> installer/data/mysql/updatedatabase.pl |    8 ++++++++
> t/db_dependent/NewsChannels.t          |   25 +++++++++++++++++++++++--
> 3 files changed, 33 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 531bb12..7209670 100755
>--- a/installer/data/mysql/updatedatabase.pl
>+++ b/installer/data/mysql/updatedatabase.pl
>@@ -8589,6 +8589,14 @@ if ( CheckVersion($DBversion) ) {
>     SetVersion ($DBversion);
> }
> 
>+$DBversion = "3.17.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);
>+}
>+
>+
> =head1 FUNCTIONS
> 
> =head2 TableExists($table)
>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.7.9.5

Actions: View | Diff | Splinter Review

Attachments on bug 12167: 27825 | 27826 | 30171 | 30172 | 30173 | 30174 | 30175 | 30176 | 30599 | 30600 | 30601 | 30642 | 30663 | 30882 | 30883 | 30884 | 30885 | 31139 | 38721 | 38722 | 38723 | 38724