In many apps there is a form filtering out rows from a recordset.
The user can fill in zero or more of these fields.
Example: Buying a flat.
Living Area, Number of rooms, Price, Date, City etc.
Often these parameters have minimum and maximum.
I have made some helping functions to make this process smother:
def q(value) # quote
value.kind_of?(String) ? "''#{value}''" :
"#{value}"
end
def a(*arg) # and
arg.compact.join(" and ")
end
def o(*arg) # or
arg.nil? ? nil : "(#{arg.compact.join(" or ")})"
end
def eq(name, value)
value.nil? || value=="" ? nil : "#{name}=#{q(value)}"
end
def neq(name, value)
value.nil? || value=="" ? nil : "#{name}!=#{q(value)}"
end
def lt(name,value)
value.nil? || value=="" ? nil : "#{name}<#{q(value)}"
end
def lte(name,value)
value.nil? || value=="" ? nil :
"#{name}<=#{q(value)}"
end
def gt(name,value)
value.nil? || value=="" ? nil : "#{name}>#{q(value)}"
end
def gte(name,value)
value.nil? || value=="" ? nil :
"#{name}>=#{q(value)}"
end
def between(name,low,high)
a(gte(name,low),lt(name,high))
end
def i(name,list) # in
list.empty? ? nil : "#{name} in (#{list.map! {|e|
q(e)}.join(",")})"
end
def like(name,value)
[nil, "", "%", "%%"].include?(value) ? nil :
"#{name} like
#{q(value)}"
end
def assert(expect,actual)
expect==actual ? print(".") : print("\nexpect:
#{expect}\nactual:
#{actual}\n")
end
def test
assert "1", q(1)
assert "''a''", q("a")
assert "''''", q("")
assert "price>=500 and price<1000",
between("price",500,1000)
assert "price>100 and price<500",
a(gt("price",100),
lt("price",500))
assert "rooms in (1,2,3)", i("rooms",[1,2,3])
assert "friends in
(''adam'',''eve'')",
i("friends",[''adam'',''eve''])
assert nil, i("rooms",[])
assert "size>100", gt("size",100)
assert "size>=100", gte("size",100)
assert "size<500", lt("size",500)
assert "size<=500", lte("size",500)
assert "price<500", a(gt("price",nil),
lt("price",500))
assert "", a(gt("price",nil), lt("price",nil))
assert "sex=''female''", eq("sex",
"female")
assert "sex!=''male''", neq("sex",
"male")
size = a(gt("size",100), lt("size",150))
price = a(gt("price",nil), lt("price",500))
city = eq("city", "Berlin")
assert "size>100 and size<150 and (price<500 or
city=''Berlin'')",a(size, o(price,city))
assert "(size>100 and size<150 or price<500 and
city=''Berlin'')",o(size, a(price,city))
assert "city=''Berlin''",
eq("city","Berlin")
assert nil, eq("city",nil)
assert "city like
''%furt''",like("city","%furt")
assert "city like
''%furt%''",like("city","%furt%")
assert "city like
''furt%''",like("city","furt%")
assert nil,like("city","")
assert nil,like("city",nil)
assert nil,like("city","%")
assert nil,like("city","%%")
#assert "0<b and b<c and c<9",
ramp("0","b","c","9")
#assert "0<b and b<9",
ramp("0","b",nil,"9")
#assert "0<=b and b<=9",
rampeq("0","b",nil,"9")
print("\nReady!")
end
test
Application code sample: ======================================
cond = a(like("r.flightnumber",
"%#{@report.flightnumberFilter}%"),
like("r.description",
"%#{@report.descriptionFilter}%"),
like("u.name", "%#{@report.pilotFilter}%"),
gte("r.flightdate", @report.fromdateFilter),
lte("r.flightdate", @report.todateFilter))
cond="1=1" if cond=="" # Empty WHERE clause forbidden
@reports = Report.find(:all, :select => "r.*", :joins =>
"AS r INNER
JOIN Users AS u ON r.user_id = u.id", :conditions => cond, :order =>
"flightnumber, flightdate")
Christer
--
Posted via http://www.ruby-forum.com/.
Hello Christer ! 2005/12/2, Christer Nilsson <janchrister.nilsson@gmail.com>:> def testYou're missing: assert '', q(nil) and a lot of other ones too. Interesting stuff ! -- François Beausoleil http://blog.teksol.info/ _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Francois, do you have an assertion that fails ? -- Posted via http://www.ruby-forum.com/.
2005/12/2, Christer Nilsson <janchrister.nilsson@gmail.com>:> Francois, do you have an assertion that fails ?No, I mean to say that you don't have a test case for when nil is passed to your q method. Thanks, -- François Beausoleil http://blog.teksol.info/ _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On Dec 2, 2005, at 8:54 AM, Francois Beausoleil wrote:> 2005/12/2, Christer Nilsson <janchrister.nilsson-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:Christer and Francois- Here is my new version that makes the setup and building of the where clause into less code. It doesn''t handle everything yet but check it out. I think its a good start on a small dsl. I still need to convert the Where.new to take a block instead of how it works now but let me know what you think. class Where attr_reader :args def initialize @args = [] end def method_missing(sym, *args) @args << [sym,args.flatten].flatten end def build(options=@args) @opts = options @opts.each do |triplet| instance_variable_set("@#{triplet[0].to_sym}", triplet[2]) end self.where end def where q = [] ary = [] @opts.each do |triplet| iv = instance_variable_get("@#{triplet[0]}") unless iv.nil? || iv.to_s == '''' q << "#{triplet[0]} #{triplet[1]} ?" ary << iv end end return [q.join(" and ")].concat(ary) end end # Usage cond = Cond.new cond.month ''<='', 11 cond.year ''='', 2005 cond.name ''LIKE'', ''ruby%'' cond.build # => ["month <= ? and year = ? and name LIKE ?", 11, 2005, "ruby%"] cond = Cond.new cond.city ''LIKE'', ''yakima%'' cond.state ''='', ''WA'' cond.comment ''LIKE'', ''%ruby%rails%'' cond.date ''<='', ''12-05-2005'' cond.build # => ["city LIKE ? and state = ? and comment LIKE ? and date <= ?", "yakima%", "WA", "%ruby%rails%", "12-05-2005"] Or: params = {:person=>{:name => ''Ezra'', :city => ''Yakima'', :state => ''WA''}} cond = Cond.new cond.name ''LIKE'', "%#{params[:person][:name]}%" cond.city ''='', params[:person][:city] cond.state ''='', params[:person][:state] cond.build # => ["name LIKE ? and city = ? and state = ?", "%Ezra%", "Yakima", "WA"] What do you think? Is it worth pursuing and fleshing out more functionality? Cheers- -Ezra Zygmuntowicz Yakima Herald-Republic WebMaster http://yakimaherald.com 509-577-7732 ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org
On Dec 2, 2005, at 8:21 AM, Christer Nilsson wrote:> In many apps there is a form filtering out rows from a recordset. > The user can fill in zero or more of these fields. > Example: Buying a flat. > Living Area, Number of rooms, Price, Date, City etc. > Often these parameters have minimum and maximum. > > I have made some helping functions to make this process smother: > > def q(value) # quote > value.kind_of?(String) ? "''#{value}''" : "#{value}" > end > def a(*arg) # and > arg.compact.join(" and ") > end > def o(*arg) # or > arg.nil? ? nil : "(#{arg.compact.join(" or ")})" > end > def eq(name, value) > value.nil? || value=="" ? nil : "#{name}=#{q(value)}" > end > def neq(name, value) > value.nil? || value=="" ? nil : "#{name}!=#{q(value)}" > end > def lt(name,value) > value.nil? || value=="" ? nil : "#{name}<#{q(value)}" > end > def lte(name,value) > value.nil? || value=="" ? nil : "#{name}<=#{q(value)}" > end > def gt(name,value) > value.nil? || value=="" ? nil : "#{name}>#{q(value)}" > end > def gte(name,value) > value.nil? || value=="" ? nil : "#{name}>=#{q(value)}" > end > def between(name,low,high) > a(gte(name,low),lt(name,high)) > end > def i(name,list) # in > list.empty? ? nil : "#{name} in (#{list.map! {|e| q(e)}.join > (",")})" > end > def like(name,value) > [nil, "", "%", "%%"].include?(value) ? nil : "#{name} like > #{q(value)}" > end > > def assert(expect,actual) > expect==actual ? print(".") : print("\nexpect: #{expect}\nactual: > #{actual}\n") > end > def test > assert "1", q(1) > assert "''a''", q("a") > assert "''''", q("") > assert "price>=500 and price<1000", between("price",500,1000) > assert "price>100 and price<500", a(gt("price",100), > lt("price",500)) > assert "rooms in (1,2,3)", i("rooms",[1,2,3]) > assert "friends in (''adam'',''eve'')", i("friends",[''adam'',''eve'']) > assert nil, i("rooms",[]) > > assert "size>100", gt("size",100) > assert "size>=100", gte("size",100) > assert "size<500", lt("size",500) > assert "size<=500", lte("size",500) > assert "price<500", a(gt("price",nil), lt("price",500)) > assert "", a(gt("price",nil), lt("price",nil)) > > assert "sex=''female''", eq("sex", "female") > assert "sex!=''male''", neq("sex", "male") > > size = a(gt("size",100), lt("size",150)) > price = a(gt("price",nil), lt("price",500)) > city = eq("city", "Berlin") > assert "size>100 and size<150 and (price<500 or > city=''Berlin'')",a(size, o(price,city)) > assert "(size>100 and size<150 or price<500 and > city=''Berlin'')",o(size, a(price,city)) > > assert "city=''Berlin''", eq("city","Berlin") > assert nil, eq("city",nil) > > assert "city like ''%furt''",like("city","%furt") > assert "city like ''%furt%''",like("city","%furt%") > assert "city like ''furt%''",like("city","furt%") > assert nil,like("city","") > assert nil,like("city",nil) > assert nil,like("city","%") > assert nil,like("city","%%") > > #assert "0<b and b<c and c<9", ramp("0","b","c","9") > #assert "0<b and b<9", ramp("0","b",nil,"9") > #assert "0<=b and b<=9", rampeq("0","b",nil,"9") > > print("\nReady!") > end > > test > > Application code sample: ======================================> > cond = a(like("r.flightnumber", "%#{@report.flightnumberFilter}%"), > like("r.description", "%#{@report.descriptionFilter}%"), > like("u.name", "%#{@report.pilotFilter}%"), > gte("r.flightdate", @report.fromdateFilter), > lte("r.flightdate", @report.todateFilter)) > > cond="1=1" if cond=="" # Empty WHERE clause forbidden > > @reports = Report.find(:all, :select => "r.*", :joins => "AS r INNER > JOIN Users AS u ON r.user_id = u.id", :conditions => cond, :order => > "flightnumber, flightdate") > > Christer >Nice job Christer. Looks good. Now you just need to come up with a way to escape the values so there is no sql injection attack vectors. If we both keep it up we will eventually come up with somehting nice. Cheers- -Ezra Zygmuntowicz Yakima Herald-Republic WebMaster http://yakimaherald.com 509-577-7732 ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org
On Dec 2, 2005, at 11:16 AM, Ezra Zygmuntowicz wrote:> > On Dec 2, 2005, at 8:54 AM, Francois Beausoleil wrote: > >> 2005/12/2, Christer Nilsson <janchrister.nilsson-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>: > > > Christer and Francois- > > Here is my new version that makes the setup and building of the > where clause into less code. It doesn''t handle everything yet but > check it out. I think its a good start on a small dsl. I still need > to convert the Where.new to take a block instead of how it works > now but let me know what you think. > > class Whereclass Cond # sorry about that typo on my part.> > attr_reader :args > > def initialize > @args = [] > end > > def method_missing(sym, *args) > @args << [sym,args.flatten].flatten > end > > def build(options=@args) > @opts = options > @opts.each do |triplet| > instance_variable_set("@#{triplet[0].to_sym}", triplet[2]) > end > self.where > end > > def where > q = [] > ary = [] > @opts.each do |triplet| > iv = instance_variable_get("@#{triplet[0]}") > unless iv.nil? || iv.to_s == '''' > q << "#{triplet[0]} #{triplet[1]} ?" > ary << iv > end > end > return [q.join(" and ")].concat(ary) > end > > end > > # Usage > > cond = Cond.new > cond.month ''<='', 11 > cond.year ''='', 2005 > cond.name ''LIKE'', ''ruby%'' > cond.build > # => ["month <= ? and year = ? and name LIKE ?", 11, 2005, "ruby%"] > > cond = Cond.new > cond.city ''LIKE'', ''yakima%'' > cond.state ''='', ''WA'' > cond.comment ''LIKE'', ''%ruby%rails%'' > cond.date ''<='', ''12-05-2005'' > cond.build > # => ["city LIKE ? and state = ? and comment LIKE ? and date <= ?", > "yakima%", "WA", "%ruby%rails%", "12-05-2005"] > > Or: > > params = {:person=>{:name => ''Ezra'', :city => ''Yakima'', :state => > ''WA''}} > > cond = Cond.new > cond.name ''LIKE'', "%#{params[:person][:name]}%" > cond.city ''='', params[:person][:city] > cond.state ''='', params[:person][:state] > cond.build > # => ["name LIKE ? and city = ? and state = ?", "%Ezra%", "Yakima", > "WA"] > > What do you think? Is it worth pursuing and fleshing out more > functionality? > > > Cheers- > > -Ezra Zygmuntowicz > Yakima Herald-Republic > WebMaster > http://yakimaherald.com > 509-577-7732 > ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org > > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-Ezra Zygmuntowicz Yakima Herald-Republic WebMaster http://yakimaherald.com 509-577-7732 ezra-gdxLOakOTQ9oetBuM9ipNAC/G2K4zDHf@public.gmane.org