[mv] Patch: fix multiple '?' expansions in mv_sql_query

["Christopher P. Lindsey" <lindsey@mallorn.com>]

******    message to minivend-users from "Christopher P. Lindsey" <lindsey@mallorn.com>     ******

Tonight I started adding search functionality to the store that I'm working
on and came across some problems with mv_sql_query and '?' expansion in
minivend 3.14-3.

First I tried something like:

   <INPUT TYPE=hidden NAME=mv_sql_query VALUE=" select p.code, p.description, p.invasive, p.sci_name, h.hardiness_name, s.size_name, p.price from plant as p left join size as s on s.size_id = p.size_id left join category as c on c.category_id = p.category_id left join hardiness as h on h.hardiness_id = p.hardiness_id where p.sci_name like %?% OR p.code like %?% ">

but that didn't work, resulting in an SQL query like

   select p.code, p.description, p.invasive, p.sci_name, h.hardiness_name, s.size_name, p.price from plant as p left join size as s on s.size_id = p.size_id left join category as c on c.category_id = p.category_id left join hardiness as h on h.hardiness_id = p.hardiness_id where p.sci_name like %NULL% OR p.code like %NULL%

(thank goodness for --log in mysqld!)  

It looked like the '%' signs were making it break,so I tried this instead:

   <INPUT TYPE=hidden NAME=mv_sql_query VALUE=" select p.code, p.description, p.invasive, p.sci_name, h.hardiness_name, s.size_name, p.price from plant as p left join size as s on s.size_id = p.size_id left join category as c on c.category_id = p.category_id left join hardiness as h on h.hardiness_id = p.hardiness_id where p.sci_name like ? OR p.code like ? ">

It resulted in an a query like

   select p.code, p.description, p.invasive, p.sci_name, h.hardiness_name, s.size_name, p.price from plant as p left join size as s on s.size_id = p.size_id left join category as c on c.category_id = p.category_id left join hardiness as h on h.hardiness_id = p.hardiness_id where p.sci_name likeAbiesOR p.code like NULL

While poking around the code I discovered that

   a) using the LIKE statement doesn't automatically do substring matching,
      even if mv_substring_match is set
   b) '?' is expanded to lose whitespace on both sides of it
   c) '?' can only be used once

I have a patch below that addresses all of these issues.  Regarding the 
first point, it seemed more intuitive to me to allow searches with this
syntax:

   where p.sci_name like %?% OR p.code like %?%

then to depend on mv_substring_match.  This is against 3.14-3.

I do have a question, though...  Would it make more sense to just
globally replace everything except escaped '?' characters in mv_sql_query?
Then we wouldn't have to worry about the spacing issues and people
could do other queries like 'A_' without having to add another special
case.

Chris

----------------------------------------------------------------------

*** lib/Vend/Scan.pm.orig	Sat Jan 22 00:15:55 2000
--- lib/Vend/Scan.pm	Sat Jan 22 00:29:13 2000
***************
*** 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,550 ----
  		$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% /g;
! 		$options->{sql_query} =~ s/(\s)\?([\s]|$)/ %s /g;
  # DEBUG
  #Vend::Util::logDebug
  #("mv_sql_query: $options->{sql_query} specs: '" . join("','", @specs) . "'\n")
*** lib/Vend/Table/DBI.pm.orig	Sat Jan 22 00:15:55 2000
--- lib/Vend/Table/DBI.pm	Sat Jan 22 00:15:57 2000
***************
*** 321,335 ****
  					\s+in[(\s]+							# enumerated
  				)
  				'?(%?)%s(%?)'?									# The parameter
! 			}{$1 . $2 . $s->quote("$3$arg$4", $1)}ixe 
  	or
  
! 		$query =~ s/'(%?)%s(%?)'/$s->[$DBI]->quote("$1$arg$2")/e 
  	or
  		defined $s->[$CONFIG]->{QUOTEALL}
! 			and $query =~ s/(([^%])%s)/$s->[$DBI]->quote($arg)/e
  	or
! 		$query =~ s/([^%])%s/$1$arg/;
  	}
  	return $query;
  }
--- 321,335 ----
  					\s+in[(\s]+							# enumerated
  				)
  				'?(%?)%s(%?)'?									# The parameter
! 			}{$1 . $2 . $s->quote("$3$arg$4", $1)}ixeg 
  	or
  
! 		$query =~ s/'(%?)%s(%?)'/$s->[$DBI]->quote("$1$arg$2")/eg 
  	or
  		defined $s->[$CONFIG]->{QUOTEALL}
! 			and $query =~ s/(([^%])%s)/$s->[$DBI]->quote($arg)/eg
  	or
! 		$query =~ s/([^%])%s/$1$arg/g;
  	}
  	return $query;
  }
-
To unsubscribe from the list, DO NOT REPLY to this message.  Instead, send
email with 'UNSUBSCRIBE minivend-users' in the body to Majordomo@minivend.com.
Archive of past messages: http://www.minivend.com/minivend/minivend-list