From: "Ara.T.Howard" <ahoward@...>
Date: 2004-06-29T23:52:56+09:00
Subject: Re: SQLite-Ruby and "other chrs"

On Tue, 29 Jun 2004, gabriele renzi wrote:

> il Mon, 28 Jun 2004 21:34:51 -0600, "Ara.T.Howard" <ahoward@noaa.gov>
> ha scritto::
>
> I'm not sure you can have a generalized quoting method. Different
> rdbms may require different escaping, I think, it is wring?

no, you are right and that's why i've not released this in any real capacity -
nevertheless it's useful now:

   ~/eg/ruby > cat a.rb
   require 'quote.rb'
   include Quote

   tuple = %w( rdbms's quote differently )

   pgsql = q(tuple, '\\')
   sqlite = q(tuple, "'")

   puts(pgsql.join(' , '))
   puts(sqlite.join(' , '))

   ~/eg/ruby > ruby a.rb
   'rdbms\'s' , 'quote' , 'differently'
   'rdbms''s' , 'quote' , 'differently'


(btw. my origninal post had a typo which your comment made me find.  thanks!)


> And, IMO it would be nice to make a quote method built-in in any rdbms
> library, even if the db does not provide it itself (like it seem
> sqlite does).

i think it should be an extenstion to String (#escape) and Array (#quote) since
more than just rdbms package would use it.  for instance csv, shell commands,
code generation, etc.

cheers.

(see updated quote.rb)

-a
--
===============================================================================
| EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE   :: 303.497.6469
| A flower falls, even though we love it;
| and a weed grows, even though we do not love it. 
|   --Dogen
===============================================================================


#
# a module for quoting sql database entries
#
# eg. 
#
#   tuple = %w( it's hard to quote this )
#   values = Quote.q tuple
#   sql = "insert into tbl values ( #{ values.join ',' } );"
#
#   => insert into tbl values ( 'it''s','hard','to','quote','this' );
#
   module Quote
#{{{
   #
   # escapes (with esc) all occurances of char in s, modifying s in place
   #
     def escape! s, char, esc
#{{{
       re = %r/([#{0x5c.chr << esc}]*)#{char}/
       s.gsub!(re) do
         (($1.size % 2 == 0) ? ($1 << esc) : $1) + char
       end
#}}}
     end
     module_function 'escape!'
     public 'escape!'
   #
   # escapes (with esc) all occurances of char in s, returning a new str
   #
     def escape s, char, esc
#{{{
       ss = "#{ s }"
       escape! ss, char, esc
       ss
#}}}
     end
     module_function 'escape'
     public 'escape'
   #
   # given an object, return a single quoted str of this object, escaping all
   # occurances of ' iff esc is true (default).  this method recognizes Array
   # objects as special cases and will recurse over any sub Arrays untill all
   # sub objects are single quoted
   #
   # p (q [42, 'ford']) # >> ["'42'", "'ford'"]
   #
     def q object, esc = "'", accum = nil
#{{{
       quote object, esc, "'", accum
#}}}
     end
     module_function 'q'
     public 'q'
   #
   # same a q but with double quotes
   #
     def qq object, esc = '"', accum = nil
#{{{
       quote object, esc, '"', accum
#}}}
     end
     module_function 'qq'
     public 'qq'
   #
   # impl of q and qq
   #
     def quote object, esc = nil, q = nil, accum = nil
#{{{
       if Array === object
         if accum
           accum << []
           accum = accum[-1]
         else
           accum = []
         end
         object.each{|object| quote object, esc, q, accum}
         return accum
       end

       quoted =
         if esc
           "#{ q }#{ escape object, q, esc }#{ q }"
         else
           "#{ q }#{ object }#{ q }"
         end

       if accum
         accum << quoted
       else
         quoted
       end
#}}}
     end
     module_function 'quote'
     public 'quote'
#}}}
   end

#
# sample usage
#
if $0 == __FILE__
#{{{

#
# simple
#
    tuple = %w( it's hard to quote this )
    values = Quote.q tuple
    sql = "insert into tbl values ( #{ values.join ',' } );"
    puts sql
    puts

#
# less simple
#

   include Quote

   tests =
     [
       [%Q|a\\\"bc|, '"', '\\'],
       [%Q|a\\\\\"bc|, '"', '\\'],
       [%q|a'b'c|, "'", '\\'],
       [%q|a#b#c|, '#', '\\'],
     ]

   tests.each do |test|
     s, char, esc = test
     e = escape s, char, esc
     puts "s : <#{ s }>"
     puts "e : <#{ e }>"
     puts
   end


   quoted = q 'hi'
   puts quoted

   quoted = q ['hi', 'hello']
   puts(quoted.join(' '))

   quoted = q [['hi', 'hello'],['howdy','hullo']]
   puts(quoted[0].join(' '))
   puts(quoted[1].join(' '))

   quoted = q [[['hi', 'hello'],['howdy','hullo']]]
   puts(quoted[0][0].join(' '))
   puts(quoted[0][1].join(' '))

   quoted = q [[['h\'i', 'he\'llo'],['howdy','hullo']]]
   puts(quoted[0][0].join(' '))
   puts(quoted[0][1].join(' '))

   quoted = qq 'hi'
   puts(quoted)

   quoted = qq ['hi', 'hello']
   puts(quoted.join(' '))

   quoted = qq [['hi', 'hello'],['howdy','hullo']]
   puts(quoted[0].join(' '))
   puts(quoted[1].join(' '))

   quoted = qq [[['hi', 'hello'],['howdy','hullo']]]
   puts(quoted[0][0].join(' '))
   puts(quoted[0][1].join(' '))

   quoted = qq [[['h"i', 'hel"lo'],['howdy','hullo']]]
   puts(quoted[0][0].join(' '))
   puts(quoted[0][1].join(' '))
#}}}
end