module SqlPostgres::Translate
Translation functions. These are internal and should not be used by clients unless you want extra work when they change.
Public Class Methods
Convert a datetime to Postgres format (ie “2003-10-18 11:30:24.000000-07”)
# File lib/sqlpostgres/Translate.rb, line 301 def datetime_to_sql(d) d.strftime("%Y-%m-%d %H:%M:%S.%N%z").gsub(/Z$/, "+0000") end
# File lib/sqlpostgres/Translate.rb, line 395 def deep_collect(a) if a.is_a?(Array) a.collect do |e| deep_collect(e) do |v| yield(v) end end else yield(a) end end
Escape an array to be inserted into a Postgres array column. Array columns are a Postgres extension.
# File lib/sqlpostgres/Translate.rb, line 165 def escape_array(a) if a.is_a?(Array) if a.empty? "'{}'" else pieces = a.collect do |e| escape_array(e) end "ARRAY[#{pieces.join(', ')}]" end else escape_sql(a) end end
Escape a string to be inserted into a bytea (byte array) column.
- s
-
The value to convert. Should be one of:
-
String
-
nil
-
:default
-
The following characters get converted to mega-backslashed octal:
\x00-\x1f ' \ \x7f-\xff
# File lib/sqlpostgres/Translate.rb, line 273 def escape_bytea(s, pgconn) return "null" if s.nil? return "default" if s == :default raise s.inspect if s.is_a?(Array) #DEBUG value = "'" + pgconn.escape_bytea(s) + "'" value = "E" + value if pgconn.server_version < 9_01_00 value end
Escape an array to be inserted into a Posgres bytea[] column. Array columns (and bytea columns) are a Postgres extension.
# File lib/sqlpostgres/Translate.rb, line 184 def escape_bytea_array(a) escape_bytea_quote(a) do |e| e. gsub(/\\/, '\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\'). gsub(/\000/, "\\\\\\\\\\\\\\\\000") end end
Escape a “char” type (the special Postgres internal type used in system tables).
- s
-
A String of length 1.
# File lib/sqlpostgres/Translate.rb, line 327 def escape_qchar(s) if s.nil? "null" else "E'" + escape_char(s[0].ord) + "'" end end
Turn a Ruby object into an SQL string.
The conversion depends upon the type of thing:
- [String, …]
-
An arbitrary expression, possibly with value substitution. Converted by calling
substitute_values
Examples:
['foo'] -> 'foo' ['%s + %s', 1, 1.414] -> '1 + 1.414'
- [:in, …]
-
A list of values that will be converted by recursively calling
escape_sql
, separated with commas, and surrounded by parentheses.
Examples: [:in, 1, 2] -> "(1, 2)" [:in, 'foo', 'bar'] => "('foo', 'bar')"
- String
-
Backslashes and single-quotes are escaped; the resulting string is then enclosed in single-quotes.
Examples:
"foo" -> "'foo'" "fool's gold' -> %q"'fool\\'s gold'" 'foo\\bar' -> %q"'foo\\\\bar'"
- false
-
Converted to “false”
- true
-
Converted to “true”
- Integer
-
Converted to a string
Examples:
123 -> "123" -2 -> "-2"
- BigDecimal
-
Converted ot a a string
Examples:
BigDecimal("123.456789012345") -> "123.456789012345"
- Float
-
Converted to a string with 15 digits of precision, using exponential notation of necessary.
Examples:
0 -> "0" -1 -> "-1" 3.1415926535898 -> "3.1415926535898" 1e100 -> "1e+100"
- Time
-
Converted to a timestamp with microseconds.
Examples:
Time.local(2000, 1, 2, 3, 4, 5, 6) -> "timestamp '2000-01-02 03:04:05.000006'"
- :default
-
Converted to “default”
- nil
-
Converted to “null”
Select
-
The statement method is called to get the SQL, which is then wrapped in parentheses.
Example, supposing that select.statement is “select 1 as i”:
select -> "(select 1 as i)"
- anything else
-
Treated as a String (after calling to_s on it)
# File lib/sqlpostgres/Translate.rb, line 92 def escape_sql(thing) return "null" if thing.nil? if thing.is_a?(Array) substitute_values(thing) elsif thing.respond_to?(:to_sql) thing.to_sql elsif thing.is_a?(Time) "timestamp '#{timeToSql(thing)}'" elsif thing.is_a?(DateTime) "timestamp with time zone '#{datetime_to_sql(thing)}'" elsif thing.is_a?(Integer) thing.to_s elsif thing.is_a?(Float) "%.14g" % thing elsif thing.is_a?(Date) "date '#{thing}'" elsif thing.is_a?(BigDecimal) thing.to_s('f') elsif thing == false "false" elsif thing == true "true" elsif thing == :default "default" elsif thing.respond_to?(:statement) "(#{thing.statement})" else string_to_sql(thing.to_s, '\\') end end
Translate
a Postgres string representation of an array into a Ruby array of strings.
# File lib/sqlpostgres/Translate.rb, line 219 def sql_to_array(s) begin a, t = sql_to_array2(s) raise ArgumentError unless t.empty? a rescue ArgumentError raise ArgumentError, s.inspect end end
Convert a date from Postgres format (ie “2003-10-18”) to a Date
# File lib/sqlpostgres/Translate.rb, line 308 def sql_to_date(s) Date.parse(s) end
Convert a time with timezone from Postgres format (ie “2003-10-18 11:30:24-07”) to a DateTime.
# File lib/sqlpostgres/Translate.rb, line 316 def sql_to_datetime(s) DateTime.parse(s) end
Convert an arbitrary expression to SQL, possibly with value substitution. expression is an array. If the first element of the array is a String, then it is the format specificer The format specifier contains a %s for each value. The remainin items, if they exist, are values. Each value is turned into a string by calling escape_sql and is then substituted for a %s in the format specifier. If the first element of the array is :in, then the remaining items are to be formatted using escape_sql, separated by commas, and surrounded by parentheses. Examples:
** example: translate_substitute_values
p Translate.substitute_values(['foo']) # "foo" p Translate.substitute_values(['%s + %s', 1, 2]) # "1 + 2" p Translate.substitute_values([:in, 1, 2]) # "(1, 2)" p Translate.substitute_values([:in, 'foo', 'bar']) # "(E'foo', # E'bar')"
**
# File lib/sqlpostgres/Translate.rb, line 146 def substitute_values(expression) if expression.is_a?(Array) pieces = expression[1..-1].collect do |value| escape_sql(value) end if expression.first == :in "(#{pieces.join(', ')})" else expression[0] % pieces end else expression end end
Convert a time to SQL format, including microseconds: (YYYY-mm-dd HH:MM:SS.uuuuuu)
# File lib/sqlpostgres/Translate.rb, line 293 def timeToSql(time) time.strftime("%Y-%m-%d %H:%M:%S.") + ("%06d" % time.usec) end
Unescape a bytea string read from postgres.
# File lib/sqlpostgres/Translate.rb, line 285 def unescape_bytea(s, pgconn) pgconn.unescape_bytea(s) end
Unescape a “char” type. This is a special internal type (yes, the quotes are part of the type name).
- s
-
A String of length 1. If empty, it really means “000”.
# File lib/sqlpostgres/Translate.rb, line 353 def unescape_qchar(s) if s.empty? "\000" else return s end end
Private Class Methods
# File lib/sqlpostgres/Translate.rb, line 200 def escape_array_noquote(a, &escaper) pieces = Array(a).map do |e| if e.is_a?(Array) escape_array_noquote(e, &escaper) else escaped = escaper.call(e.to_s). gsub(/'/, "\\\\'"). gsub(/"/, "\\\\\\\"") '"' + escaped + '"' end end "{" + pieces.join(',') + "}" end
# File lib/sqlpostgres/Translate.rb, line 193 def escape_bytea_quote(a, &escaper) return "null" if a.nil? "'#{escape_array_noquote(a, &escaper)}'" end
Escape a character, converting non-printable (0x0-0x1f, 0x7f-0xff), backslash, and single-quote into an octal escape sequence.
- c
-
The character code to convert (an integer between 0 and 255)
- backslahes
-
The backslashes to use in the escape sequence
# File lib/sqlpostgres/Translate.rb, line 371 def escape_char(c, backslashes = '\\') "#{backslashes}%03o" % c end
# File lib/sqlpostgres/Translate.rb, line 230 def sql_to_array2(s) if s !~ /\A\{/m raise ArgumentError else t = $' a = [] loop do case t when /\A\},?/ return [a, $'] when /\A\{/ e, t = sql_to_array2(t) a << e when /\A([^"][^\},]*),?/m t = $' || "" a << $1 when /\A"((?:[^\\]|\\\\|\\")*?)",?/m t = $' || "" a << $1.gsub(/\\\\/, "\\").gsub(/\\"/, '"') else raise ArgumentError end end a end end
Escape a string, converting non-printable (x0-x1f, x7f-xff), backshlash, and single-quote into octal escape sequences and/or unicode.
- thing
-
The string to convert
- backslashes
-
The backslashes to use in the escape sequence.
# File lib/sqlpostgres/Translate.rb, line 387 def string_to_sql(thing, backslashes) "E'" + thing.gsub(/[\x0-\x1f\x80-\xff'\\]/) do |c| escape_char(c[0].ord, backslashes) end + "'" end
Private Instance Methods
Convert a datetime to Postgres format (ie “2003-10-18 11:30:24.000000-07”)
# File lib/sqlpostgres/Translate.rb, line 301 def datetime_to_sql(d) d.strftime("%Y-%m-%d %H:%M:%S.%N%z").gsub(/Z$/, "+0000") end
# File lib/sqlpostgres/Translate.rb, line 395 def deep_collect(a) if a.is_a?(Array) a.collect do |e| deep_collect(e) do |v| yield(v) end end else yield(a) end end
Escape an array to be inserted into a Postgres array column. Array columns are a Postgres extension.
# File lib/sqlpostgres/Translate.rb, line 165 def escape_array(a) if a.is_a?(Array) if a.empty? "'{}'" else pieces = a.collect do |e| escape_array(e) end "ARRAY[#{pieces.join(', ')}]" end else escape_sql(a) end end
# File lib/sqlpostgres/Translate.rb, line 200 def escape_array_noquote(a, &escaper) pieces = Array(a).map do |e| if e.is_a?(Array) escape_array_noquote(e, &escaper) else escaped = escaper.call(e.to_s). gsub(/'/, "\\\\'"). gsub(/"/, "\\\\\\\"") '"' + escaped + '"' end end "{" + pieces.join(',') + "}" end
Escape a string to be inserted into a bytea (byte array) column.
- s
-
The value to convert. Should be one of:
-
String
-
nil
-
:default
-
The following characters get converted to mega-backslashed octal:
\x00-\x1f ' \ \x7f-\xff
# File lib/sqlpostgres/Translate.rb, line 273 def escape_bytea(s, pgconn) return "null" if s.nil? return "default" if s == :default raise s.inspect if s.is_a?(Array) #DEBUG value = "'" + pgconn.escape_bytea(s) + "'" value = "E" + value if pgconn.server_version < 9_01_00 value end
Escape an array to be inserted into a Posgres bytea[] column. Array columns (and bytea columns) are a Postgres extension.
# File lib/sqlpostgres/Translate.rb, line 184 def escape_bytea_array(a) escape_bytea_quote(a) do |e| e. gsub(/\\/, '\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\'). gsub(/\000/, "\\\\\\\\\\\\\\\\000") end end
# File lib/sqlpostgres/Translate.rb, line 193 def escape_bytea_quote(a, &escaper) return "null" if a.nil? "'#{escape_array_noquote(a, &escaper)}'" end
Escape a character, converting non-printable (0x0-0x1f, 0x7f-0xff), backslash, and single-quote into an octal escape sequence.
- c
-
The character code to convert (an integer between 0 and 255)
- backslahes
-
The backslashes to use in the escape sequence
# File lib/sqlpostgres/Translate.rb, line 371 def escape_char(c, backslashes = '\\') "#{backslashes}%03o" % c end
Escape a “char” type (the special Postgres internal type used in system tables).
- s
-
A String of length 1.
# File lib/sqlpostgres/Translate.rb, line 327 def escape_qchar(s) if s.nil? "null" else "E'" + escape_char(s[0].ord) + "'" end end
Turn a Ruby object into an SQL string.
The conversion depends upon the type of thing:
- [String, …]
-
An arbitrary expression, possibly with value substitution. Converted by calling
substitute_values
Examples:
['foo'] -> 'foo' ['%s + %s', 1, 1.414] -> '1 + 1.414'
- [:in, …]
-
A list of values that will be converted by recursively calling
escape_sql
, separated with commas, and surrounded by parentheses.
Examples: [:in, 1, 2] -> "(1, 2)" [:in, 'foo', 'bar'] => "('foo', 'bar')"
- String
-
Backslashes and single-quotes are escaped; the resulting string is then enclosed in single-quotes.
Examples:
"foo" -> "'foo'" "fool's gold' -> %q"'fool\\'s gold'" 'foo\\bar' -> %q"'foo\\\\bar'"
- false
-
Converted to “false”
- true
-
Converted to “true”
- Integer
-
Converted to a string
Examples:
123 -> "123" -2 -> "-2"
- BigDecimal
-
Converted ot a a string
Examples:
BigDecimal("123.456789012345") -> "123.456789012345"
- Float
-
Converted to a string with 15 digits of precision, using exponential notation of necessary.
Examples:
0 -> "0" -1 -> "-1" 3.1415926535898 -> "3.1415926535898" 1e100 -> "1e+100"
- Time
-
Converted to a timestamp with microseconds.
Examples:
Time.local(2000, 1, 2, 3, 4, 5, 6) -> "timestamp '2000-01-02 03:04:05.000006'"
- :default
-
Converted to “default”
- nil
-
Converted to “null”
Select
-
The statement method is called to get the SQL, which is then wrapped in parentheses.
Example, supposing that select.statement is “select 1 as i”:
select -> "(select 1 as i)"
- anything else
-
Treated as a String (after calling to_s on it)
# File lib/sqlpostgres/Translate.rb, line 92 def escape_sql(thing) return "null" if thing.nil? if thing.is_a?(Array) substitute_values(thing) elsif thing.respond_to?(:to_sql) thing.to_sql elsif thing.is_a?(Time) "timestamp '#{timeToSql(thing)}'" elsif thing.is_a?(DateTime) "timestamp with time zone '#{datetime_to_sql(thing)}'" elsif thing.is_a?(Integer) thing.to_s elsif thing.is_a?(Float) "%.14g" % thing elsif thing.is_a?(Date) "date '#{thing}'" elsif thing.is_a?(BigDecimal) thing.to_s('f') elsif thing == false "false" elsif thing == true "true" elsif thing == :default "default" elsif thing.respond_to?(:statement) "(#{thing.statement})" else string_to_sql(thing.to_s, '\\') end end
Translate
a Postgres string representation of an array into a Ruby array of strings.
# File lib/sqlpostgres/Translate.rb, line 219 def sql_to_array(s) begin a, t = sql_to_array2(s) raise ArgumentError unless t.empty? a rescue ArgumentError raise ArgumentError, s.inspect end end
# File lib/sqlpostgres/Translate.rb, line 230 def sql_to_array2(s) if s !~ /\A\{/m raise ArgumentError else t = $' a = [] loop do case t when /\A\},?/ return [a, $'] when /\A\{/ e, t = sql_to_array2(t) a << e when /\A([^"][^\},]*),?/m t = $' || "" a << $1 when /\A"((?:[^\\]|\\\\|\\")*?)",?/m t = $' || "" a << $1.gsub(/\\\\/, "\\").gsub(/\\"/, '"') else raise ArgumentError end end a end end
Convert a date from Postgres format (ie “2003-10-18”) to a Date
# File lib/sqlpostgres/Translate.rb, line 308 def sql_to_date(s) Date.parse(s) end
Convert a time with timezone from Postgres format (ie “2003-10-18 11:30:24-07”) to a DateTime.
# File lib/sqlpostgres/Translate.rb, line 316 def sql_to_datetime(s) DateTime.parse(s) end
Escape a string, converting non-printable (x0-x1f, x7f-xff), backshlash, and single-quote into octal escape sequences and/or unicode.
- thing
-
The string to convert
- backslashes
-
The backslashes to use in the escape sequence.
# File lib/sqlpostgres/Translate.rb, line 387 def string_to_sql(thing, backslashes) "E'" + thing.gsub(/[\x0-\x1f\x80-\xff'\\]/) do |c| escape_char(c[0].ord, backslashes) end + "'" end
Convert an arbitrary expression to SQL, possibly with value substitution. expression is an array. If the first element of the array is a String, then it is the format specificer The format specifier contains a %s for each value. The remainin items, if they exist, are values. Each value is turned into a string by calling escape_sql and is then substituted for a %s in the format specifier. If the first element of the array is :in, then the remaining items are to be formatted using escape_sql, separated by commas, and surrounded by parentheses. Examples:
** example: translate_substitute_values
p Translate.substitute_values(['foo']) # "foo" p Translate.substitute_values(['%s + %s', 1, 2]) # "1 + 2" p Translate.substitute_values([:in, 1, 2]) # "(1, 2)" p Translate.substitute_values([:in, 'foo', 'bar']) # "(E'foo', # E'bar')"
**
# File lib/sqlpostgres/Translate.rb, line 146 def substitute_values(expression) if expression.is_a?(Array) pieces = expression[1..-1].collect do |value| escape_sql(value) end if expression.first == :in "(#{pieces.join(', ')})" else expression[0] % pieces end else expression end end
Convert a time to SQL format, including microseconds: (YYYY-mm-dd HH:MM:SS.uuuuuu)
# File lib/sqlpostgres/Translate.rb, line 293 def timeToSql(time) time.strftime("%Y-%m-%d %H:%M:%S.") + ("%06d" % time.usec) end
Unescape a bytea string read from postgres.
# File lib/sqlpostgres/Translate.rb, line 285 def unescape_bytea(s, pgconn) pgconn.unescape_bytea(s) end
Unescape a “char” type. This is a special internal type (yes, the quotes are part of the type name).
- s
-
A String of length 1. If empty, it really means “000”.
# File lib/sqlpostgres/Translate.rb, line 353 def unescape_qchar(s) if s.empty? "\000" else return s end end