Patch for sql searches

[Holm Tiffe <holm@freibergnet.de>]

Hi,

I have a little bit fiddeled around sql searches like this:

< FORM METHOD=POST ACTION="[process-search]">
<INPUT TYPE="hidden" NAME=mv_searchtype VALUE="sql">
<INPUT TYPE="hidden" NAME=mv_matchlimit VALUE="3">
<INPUT TYPE="hidden" NAME=mv_sql_query
	 VALUE="select code,category from products where comment ~* ?">
<INPUT MAXLENGTH="30" NAME="mv_searchspec" SIZE="13" VALUE="">
<INPUT TYPE="submit" VALUE="[loc]Search[/loc]">
....

and have found that the query looks than like this:

select code,category from products where comment ~*test

When I use an "=" in the query it looks nearly perfect:

select code,category from products where comment ='test'

the "~*" is an postgresql case insensitive substring search
that fits my needs perfectly.

The patch below fixes the needed missing whitespace after the ~* and
enables the quoting of the string so that it now looks like this:

select code,category from products where comment ~* 'test'

I hope this helps someone.

Holm
-- 
FreibergNet Systemhaus GbR      Holm Tiffe  * Administration, Development
Systemhaus f&#252;r Daten- und Netzwerktechnik           phone +49 3731 781279
Unternehmensgruppe Liebscher & Partner                fax +49 3731 781377
D-09599 Freiberg * Am St. Niclas Schacht 13    http://www.freibergnet.de/

*** lib/Vend/Scan.pm.orig	Fri Aug 13 12:24:07 1999
--- lib/Vend/Scan.pm	Fri Aug 13 12:24:38 1999
***************
*** 543,549 ****
  		$options->{sql_query} =~ s/\0+\s*([!=<>][=<>]?|like)\s*\0+$/ $1 /ig;
  		$options->{sql_query} =~ s/\0+\s*([!=<>][=<>]?|like)\s*$//i;
  		$options->{sql_query} =~ s/\0+/$joiner/g;
! 		$options->{sql_query} =~ s/(\s)\?([\s]|$)/%s/;
  # DEBUG
  #Vend::Util::logDebug
  #("mv_sql_query: $options->{sql_query} specs: '" . join("','", @specs) . "'\n")
--- 543,549 ----
  		$options->{sql_query} =~ s/\0+\s*([!=<>][=<>]?|like)\s*\0+$/ $1 /ig;
  		$options->{sql_query} =~ s/\0+\s*([!=<>][=<>]?|like)\s*$//i;
  		$options->{sql_query} =~ s/\0+/$joiner/g;
! 		$options->{sql_query} =~ s/(\s)\?([\s]|$)/ %s/;
  # DEBUG
  #Vend::Util::logDebug
  #("mv_sql_query: $options->{sql_query} specs: '" . join("','", @specs) . "'\n")
*** lib/Vend/Table/DBI.pm.orig	Fri Aug 13 12:25:33 1999
--- lib/Vend/Table/DBI.pm	Fri Aug 13 12:26:41 1999
***************
*** 318,324 ****
  					\s+like\s+			|       		# substring
  					\s*[!=><][=><]?\s*	|				# compare
  					\s+between\s+		|				# range
! 					\s+in[(\s]+							# enumerated
  				)
  				'?(%?)%s(%?)'?									# The parameter
  			}{$1 . $2 . $s->quote("$3$arg$4", $1)}ixe 
--- 318,325 ----
  					\s+like\s+			|       		# substring
  					\s*[!=><][=><]?\s*	|				# compare
  					\s+between\s+		|				# range
! 					\s+in[(\s]+			|				# enumerated
! 					\s+\~\*\s+							# postgresql ~* (holm)
  				)
  				'?(%?)%s(%?)'?									# The parameter
  			}{$1 . $2 . $s->quote("$3$arg$4", $1)}ixe