路路路
On Sat, 26 Jun 2004, Meino Christian Cramer wrote:
Hi,
a problem with "other chars" hits me... 
I try to read a database of shortwave broadcasters (formatted ascii)
into a SQLIte-Ruby database.
The initialization is ok, but from time to time the population of the
db breaks, cause some broadcasters station names include invalid
chars like
R.N'Djamena
and the "'" kills everything.
Escaping that character in the textfile, which is read to populate
the db, doesn't work, cause "\'" ist read as "\" + "'", which
again is one "'" to much.
With mysql there was a method called ".Mysql_escape_string" which
does this job. In the SQLite and SQLite-Ruby docs I haven't found
anything similiar...
How can I solve this problem?
Kind regards and thank you very much in advance,
Meino
===============================================================================
file: quote.rb
#
# 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 = true, accum = nil
#{{{
quote object, esc, "'", accum
#}}}
end
module_function 'q'
public 'q'
#
# same a q but with double quotes
#
def qq object, esc = true, accum = nil
#{{{
quote object, esc, '"', accum
#}}}
end
module_function 'qq'
public 'qq'
#
# impl of q and qq
#
def quote object, esc = true, q = "'", 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, q }#{ 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
cheers.
-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
===============================================================================
