From e38d3bfabd8be963070bd318cd595e69124832ed Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Fri, 30 Dec 2005 17:46:13 +0000
Subject: [PATCH] fixing worst-case for "global" search, adding page up and
 down

git-svn-id: svn://svn.open-ils.org/ILS/trunk@2561 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 .../Application/Storage/Publisher/asset.pm    | 167 +++++++++++++++---
 1 file changed, 146 insertions(+), 21 deletions(-)

diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/asset.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/asset.pm
index c798a65ffb..eb236ef032 100644
--- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/asset.pm
+++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/asset.pm
@@ -10,17 +10,17 @@ use base qw/OpenILS::Application::Storage/;
 # see /home/miker/cn_browse-test.sql for page up and down sql ...
 # XXX
 
-sub cn_browse_target {
+sub cn_browse_pagedown {
 	my $self = shift;
 	my $client = shift;
 
 	my %args = @_;
 
-	my $cn = $args{label};
+	my $cn = uc($args{label});
 	my $org = $args{org_unit};
 	my $depth = $args{depth};
-	my $size = $args{page_size} || 10;
-	$size /= 2;
+	my $boundry_id = $args{boundry_id};
+	my $size = $args{page_size} || 20;
 	$size = int($size);
 
 	my $table = asset::call_number->table;
@@ -30,37 +30,162 @@ sub cn_browse_target {
 		$descendants = "actor.org_unit_descendants($org,$depth)";
 	}
 
-	my $top_sql = <<"	SQL";
+	my $sql = <<"	SQL";
 		select * from (
 			select
 			        cn.label,
 			        cn.owning_lib,
-			        cn.record,
+		        	cn.record,
 			        cn.id
 			from
 			        $table cn
 			        join $descendants d
-			                on (d.id = cn.owning_lib)
+		        	        on (d.id = cn.owning_lib)
 			where
-			        upper(label) < ?
-			order by 1 desc, 4, 2, 3
-			limit $size
+			        upper(label) > ?
+			        or ( cn.id > ? and upper(label) = ? )
+			order by upper(label)
+			limit 1000
 		) as foo
+		order by 1,4
+		limit $size;
+	SQL
+
+	my $sth = asset::call_number->db_Main->prepare($sql);
+	$sth->execute($cn, $boundry_id, $cn);
+	while ( my $row = $sth->fetchrow_hashref ) {
+		$client->respond($row);
+	}
+	$sth->finish;
+
+	return undef;
+}
+__PACKAGE__->register_method(
+	method		=> 'cn_browse_pagedown',
+	api_name	=> 'open-ils.storage.asset.call_number.browse.page_down',
+	argc		=> 4,
+	stream		=> 1,
+);
+
+sub cn_browse_pageup {
+	my $self = shift;
+	my $client = shift;
+
+	my %args = @_;
+
+	my $cn = uc($args{label});
+	my $org = $args{org_unit};
+	my $depth = $args{depth};
+	my $boundry_id = $args{boundry_id};
+	my $size = $args{page_size} || 20;
+	$size = int($size);
+
+	my $table = asset::call_number->table;
+
+	my $descendants = "actor.org_unit_descendants($org)";
+	if (defined $depth) {
+		$descendants = "actor.org_unit_descendants($org,$depth)";
+	}
+
+	my $sql = <<"	SQL";
+		select * from (
+			select * from (
+				select
+				        cn.label,
+				        cn.owning_lib,
+			        	cn.record,
+				        cn.id
+				from
+				        $table cn
+				        join $descendants d
+			        	        on (d.id = cn.owning_lib)
+				where
+				        upper(label) < ?
+				        or ( cn.id < ? and upper(label) = ? )
+				order by upper(label) desc
+				limit 1000
+			) as foo
+			order by 1 desc, 4 desc
+			limit $size
+		) as bar
+		order by 1,4;
+	SQL
+
+	my $sth = asset::call_number->db_Main->prepare($sql);
+	$sth->execute($cn, $boundry_id, $cn);
+	while ( my $row = $sth->fetchrow_hashref ) {
+		$client->respond($row);
+	}
+	$sth->finish;
+
+	return undef;
+}
+__PACKAGE__->register_method(
+	method		=> 'cn_browse_pageup',
+	api_name	=> 'open-ils.storage.asset.call_number.browse.page_up',
+	argc		=> 4,
+	stream		=> 1,
+);
+
+sub cn_browse_target {
+	my $self = shift;
+	my $client = shift;
+
+	my %args = @_;
+
+	my $cn = uc($args{label});
+	my $org = $args{org_unit};
+	my $depth = $args{depth};
+	my $size = $args{page_size} || 20;
+	$size /= 2;
+	$size = int($size);
+
+	my $table = asset::call_number->table;
+
+	my $descendants = "actor.org_unit_descendants($org)";
+	if (defined $depth) {
+		$descendants = "actor.org_unit_descendants($org,$depth)";
+	}
+
+	my $top_sql = <<"	SQL";
+		select * from (
+			select * from (
+				select
+				        cn.label,
+				        cn.owning_lib,
+			        	cn.record,
+				        cn.id
+				from
+				        $table cn
+				        join $descendants d
+			        	        on (d.id = cn.owning_lib)
+				where
+				        upper(label) < ?
+				order by upper(label) desc
+				limit 1000
+			) as foo
+			order by 1 desc, 4 desc
+			limit $size
+		) as bar
 		order by 1,4;
 	SQL
 
 	my $bottom_sql = <<"	SQL";
-		select
-        		cn.label,
-		        cn.owning_lib,
-		        cn.record,
-		        cn.id
-		from
-		        $table cn
-		        join $descendants d
-		                on (d.id = cn.owning_lib)
-		where
-		        upper(label) >= ?
+		select * from (
+			select
+	        		cn.label,
+			        cn.owning_lib,
+			        cn.record,
+		        	cn.id
+			from
+			        $table cn
+			        join $descendants d
+		        	        on (d.id = cn.owning_lib)
+			where
+			        upper(label) >= ?
+			order by upper(label)
+			limit 1000
+		) as foo
 		order by 1,4
 		limit $size;
 	SQL
-- 
2.43.2