From 9e74ede2b7ddaf65620930b048e0dba636f631a9 Mon Sep 17 00:00:00 2001
From: "Steinar H. Gunderson" <sesse@debian.org>
Date: Thu, 15 Nov 2007 22:42:55 +0100
Subject: [PATCH] Do proper NULLS LAST handling, now that we have 8.3.

---
 perl/Sesse/pr0n/Index.pm   | 10 ++++------
 perl/Sesse/pr0n/Listing.pm |  2 +-
 2 files changed, 5 insertions(+), 7 deletions(-)

diff --git a/perl/Sesse/pr0n/Index.pm b/perl/Sesse/pr0n/Index.pm
index 0fbd828..4a993b1 100644
--- a/perl/Sesse/pr0n/Index.pm
+++ b/perl/Sesse/pr0n/Index.pm
@@ -17,15 +17,13 @@ sub handler {
 		$abspath = 1;
 		$tag = undef; 
 
-		# augh, this needs 8.3, so we'll have to fiddle around a bit instead
-		# $datesort = 'DESC NULLS LAST';
-		$datesort = 'DESC';
+		$datesort = 'DESC NULLS LAST';
 	} elsif ($r->uri =~ /^\/\+tags\/([a-zA-Z0-9-]+)\/?$/) {
 		$tag = $1;
 		$event = "+tags/$tag";
 		$abspath = 1;
 		
-		$datesort = 'DESC';
+		$datesort = 'DESC NULLS LAST';
 	} else {
 		# Find the event
 		$r->uri =~ /^\/([a-zA-Z0-9-]+)\/?$/
@@ -33,7 +31,7 @@ sub handler {
 		$event = $1;
 		$abspath = 0;
 		$tag = undef;
-		$datesort = 'ASC';
+		$datesort = 'ASC NULLS LAST';
 	}
 
 	# Fix common error: pr0n.sesse.net/event -> pr0n.sesse.net/event/
@@ -180,7 +178,7 @@ sub handler {
 	# Find all images related to this event.
 	my $limit = (defined($start) && defined($num) && !$settings{'fullscreen'}) ? (" LIMIT $num OFFSET " . ($start-1)) : "";
 
-	my $q = $dbh->prepare("SELECT *, (date - INTERVAL '6 hours')::date AS day FROM images WHERE vhost=? $where ORDER BY COALESCE((date - INTERVAL '6 hours')::date, '1970-01-01') $datesort,takenby,date,filename $limit")
+	my $q = $dbh->prepare("SELECT *, (date - INTERVAL '6 hours')::date AS day FROM images WHERE vhost=? $where ORDER BY (date - INTERVAL '6 hours')::date $datesort,takenby,date,filename $limit")
 		or dberror($r, "prepare()");
 	$q->execute($r->get_server_name)
 		or dberror($r, "image enumeration");
diff --git a/perl/Sesse/pr0n/Listing.pm b/perl/Sesse/pr0n/Listing.pm
index b871e88..03285a1 100644
--- a/perl/Sesse/pr0n/Listing.pm
+++ b/perl/Sesse/pr0n/Listing.pm
@@ -63,7 +63,7 @@ sub handler {
 		# main listing
 #		my $q = $dbh->prepare('SELECT t1.id,t1.date,t1.name FROM events t1 LEFT JOIN images t2 ON t1.id=t2.event WHERE t1.vhost=? GROUP BY t1.id,t1.date,t1.name ORDER BY COALESCE(MAX(t2.date),\'1970-01-01 00:00:00\'),t1.id') or
 #			dberror($r, "Couldn't list events");
-		my $q = $dbh->prepare('SELECT event,date,name FROM events e JOIN last_picture_cache c USING (vhost,event) WHERE vhost=? ORDER BY last_picture DESC')
+		my $q = $dbh->prepare('SELECT event,date,name FROM events e JOIN last_picture_cache c USING (vhost,event) WHERE vhost=? ORDER BY last_picture DESC NULLS LAST')
 			or dberror($r, "Couldn't list events");
 		$q->execute($r->get_server_name)
 			or dberror($r, "Couldn't get events");
-- 
2.39.2