#!/usr/bin/env ruby
require 'rubygems'
require 'bundler/setup'
$:.unshift(File.dirname(__FILE__))
require 'TestSetup'
require 'bigdecimal'
require 'date'
# Test putting stuff in using Insert and getting it back out using
# Select.
class RoundTripTest < Test
include SqlPostgres
include TestUtil
def test
testCases = [
[
["integer", "int", "int4"],
[-2147483648, +2147483647, nil]
],
[
["smallint", "int2"],
[-32768, +32767, nil]
],
[
[
"smallint[]", "int2[]",
"integer[]", "int[]", "int4[]",
"bigint[]", "int8[]",
],
[[], [1], [1, 2], [-1, 0, 1], [[1, 2], [3, 4]], [[1, 2], [3, 4]], nil],
],
[
["bigint", "int8"],
[-9223372036854775808, +9223372036854775807, nil]
],
[
["real", "float4"],
[-1e30, -3.14159, +3.14159, 1e30, nil]
],
[
["real[]", "float4[]"],
[
[[-1e30, -3.14159], [+3.14159, 1e30]],
nil
]
],
[
["decimal (7, 6)", "numeric (7, 6)"],
[BigDecimal("-3.14159"), BigDecimal("+3.14159"), nil]
],
[
["double precision", "float8"],
[-1e290, -3.1415926535897, +3.1415926535897, 1e290, nil]
],
[
["double precision[]", "float8[]"],
[
[[-1e290, -3.1415926535897], [+3.1415926535897, 1e290]],
nil
]
],
[
["serial", "serial4"],
[1, +2147483647]
],
[
["bigserial", "serial8"],
[1, +9223372036854775807]
],
[
["text", "varchar(255)", "character varying (255)"],
[
"",
"Fool's gold",
allCharacters(1),
nil
]
],
[
["text[]", "varchar(255)[]", "character varying(255)[]"],
[
[], ["foo"], ["foo", "bar", "fool's gold"],
["\\", "fool's", "a,b,c", "{}", "\"Hello!\"", "\001"],
# Can't get this character arrays to work with the full
# suite of characters, but we don't use character arrays
# anyhow.
# [allCharacters(1)],
[["a", "b"], ["c", "d"]],
nil,
]
],
[
["character(4)", "char(4)"],
["foo ", "'\"\001 ", nil]
],
[
["character(4)[]", "char(4)[]"],
[
[["foo ", "'\"\001 "], [" ", " a b"]],
nil
]
],
[
["character", "char"],
["a", "\001", "'", '"', nil]
],
[
["character[]", "char[]"],
[["a", "b"], ["c", "d"]],
nil
],
[
['"char"'],
["\001", "\037", " ", "~", "\127", "\130", "\277", "\300", "\377"],
],
[
["name"],
["foo", nil]
],
[
["name[]"],
[
[["foo", "bar"], ["baz", "quux"]],
nil
]
],
[
["bytea"],
[
"",
allCharacters,
nil,
"\xc1\xb7",
"\\123",
"\\668G\345\256L\245",
],
],
# Can't get this to work, but we don't use byte array arrays anyhow.
# [
# ["bytea[]"],
# [
# [["foo", "bar"], ["baz", "quux"]],
# ["\\", "\000", allCharacters],
# nil
# ]
# ],
[
["timestamp", "timestamp without time zone"],
[
PgTimestamp.new(1900, 1, 1, 0, 0, 0),
PgTimestamp.new(1999, 12, 31, 23, 59, 59),
nil
]
],
[
["timestamp[]", "timestamp without time zone[]"],
[
[
PgTimestamp.new(1900, 1, 1, 0, 0, 0),
PgTimestamp.new(1999, 12, 31, 23, 59, 59)
],
nil
]
],
[
["timestamp with time zone"],
[
{
'in'=>DateTime.civil(2001, 1, 1, 0, 0, 0, Rational(7, 24)),
'out'=>DateTime.civil(2000, 12, 31, 10, 0, 0, Rational(-7, 24)),
},
DateTime.civil(1900, 12, 31, 23, 59, 59, Rational(0, 24)),
nil
]
],
[
["timestamp with time zone[]"],
[
[
DateTime.civil(2001, 1, 1, 0, 0, 0, Rational(7, 24)),
DateTime.civil(1900, 12, 31, 23, 59, 59, Rational(0, 24)),
],
nil
]
],
[
["interval"],
[
PgInterval.new,
PgInterval.new('seconds'=>1),
PgInterval.new('minutes'=>1),
PgInterval.new('hours'=>1),
PgInterval.new('days'=>1),
PgInterval.new('days'=>2),
{
'in'=>PgInterval.new('weeks'=>1),
'out'=>PgInterval.new('days'=>7),
},
PgInterval.new('months'=>1),
PgInterval.new('months'=>2),
PgInterval.new('years'=>1),
PgInterval.new('years'=>2),
{
'in'=>PgInterval.new('decades'=>1),
'out'=>PgInterval.new('years'=>10),
},
{
'in'=>PgInterval.new('centuries'=>1),
'out'=>PgInterval.new('years'=>100),
},
{
'in'=>PgInterval.new('millennia'=>1),
'out'=>PgInterval.new('years'=>1000),
},
{
'in'=>PgInterval.new('millennia'=>1, 'centuries'=>2, 'decades'=>3,
'years'=>4, 'months'=>5, 'weeks'=>6,
'days'=>7, 'hours'=>8, 'minutes'=>9,
'seconds'=>10),
'out'=>PgInterval.new('years'=>1234, 'months'=>5, 'days'=>49,
'hours'=>8, 'minutes'=>9, 'seconds'=>10),
},
PgInterval.new('days'=>-1),
{
'in'=>PgInterval.new('days'=>1, 'ago'=>true),
'out'=>PgInterval.new('days'=>-1),
},
{
'in'=>PgInterval.new('days'=>-1, 'ago'=>true),
'out'=>PgInterval.new('days'=>1),
},
PgInterval.new('seconds'=>1.1),
{
'in'=>PgInterval.new('hours'=>1, 'minutes'=>-1, 'seconds'=>1),
'out'=>PgInterval.new('hours'=>0, 'minutes'=>59, 'seconds'=>1),
},
nil
]
],
[
["interval[]"],
[
[
[
PgInterval.new('days'=>1),
PgInterval.new('hours'=>2),
],
[
PgInterval.new('minutes'=>3),
PgInterval.new('seconds'=>4),
],
],
nil,
]
],
[
["date"],
[Date.civil(2001, 1, 1), Date.civil(1900, 12, 31), nil]
],
[
["date[]"],
[[Date.civil(2001, 1, 1), Date.civil(1900, 12, 31)], nil]
],
[
["time"],
[PgTime.new(0, 0, 0), PgTime.new(23, 59, 59), nil]
],
[
["time[]"],
[[PgTime.new(0, 0, 0), PgTime.new(23, 59, 59)], nil]
],
[
["time with time zone"],
[
PgTimeWithTimeZone.new(0, 0, 0, 0, 0),
PgTimeWithTimeZone.new(12, 0, 0, 0, 30),
PgTimeWithTimeZone.new(12, 0, 0, -8, 0),
PgTimeWithTimeZone.new(23, 59, 59, +8, 0),
nil
]
],
[
["time with time zone[]"],
[
[
PgTimeWithTimeZone.new(0, 0, 0, 0, 0),
PgTimeWithTimeZone.new(23, 59, 59, +8, 0),
],
nil
]
],
[
["boolean"],
[false, true, nil],
],
[
["boolean[]"],
[
[false, true],
[[false, false], [false, true], [true, false], [true, true]],
nil,
]
],
[
["point"],
[
PgPoint.new(0, 0),
PgPoint.new(1.2, -3),
PgPoint.new(1e20, -1e20),
nil,
],
],
[
["point[]"],
[
[PgPoint.new(0, 0), PgPoint.new(1.2, -3), PgPoint.new(1e20, -1e20)],
nil,
],
],
[
["lseg"],
[
PgLineSegment.new(0, 0, 0, 0),
PgLineSegment.new(1.2, -2, 1e10, -1e10),
nil
]
],
[
["lseg[]"],
[
[
PgLineSegment.new(0, 0, 0, 0),
PgLineSegment.new(1.2, -2, 1e10, -1e10),
],
nil
]
],
[
["box"],
[
PgBox.new(0, 0, 0, 0),
PgBox.new(1e10, -2, 1.2, -1e10),
nil
]
],
# Can't get this to work, but we don't use box arrays anyhow.
# [
# ["box[]"],
# [
# [
# PgBox.new(0, 0, 0, 0),
# PgBox.new(1.2, -2, 1e10, -1e10),
# ],
# nil
# ]
# ],
[
["path"],
[
PgPath.new(false, PgPoint.new(1, 2)),
PgPath.new(true, PgPoint.new(1, 2), PgPoint.new(3, 4)),
nil,
]
],
[
["path[]"],
[
[
PgPath.new(false, PgPoint.new(1, 2)),
PgPath.new(true, PgPoint.new(1, 2), PgPoint.new(3, 4)),
],
nil
]
],
[
["polygon"],
[
PgPolygon.new(PgPoint.new(1, 2)),
PgPolygon.new(PgPoint.new(1, 2), PgPoint.new(3, 4)),
nil,
]
],
[
["polygon[]"],
[
[
PgPolygon.new(PgPoint.new(1, 2)),
PgPolygon.new(PgPoint.new(1, 2), PgPoint.new(3, 4)),
],
nil
]
],
[
["circle"],
[
PgCircle.new(0, 0, 0),
PgCircle.new(1, 2, 3),
nil,
]
],
[
["circle[]"],
[
[
PgCircle.new(0, 0, 0),
PgCircle.new(1, 2, 3),
],
nil,
]
],
[
["bit varying", "bit varying(6)"],
[
PgBit.new,
PgBit.new("0"),
PgBit.new("010101"),
nil
]
],
[
["bit varying[]", "bit varying(6)[]"],
[
[
PgBit.new,
PgBit.new("0"),
PgBit.new("010101"),
],
nil
]
],
[
["bit(1)", "bit"],
[
PgBit.new("1"),
PgBit.new("0"),
nil
]
],
[
["bit(1)[]", "bit[]"],
[
[
PgBit.new("1"),
PgBit.new("0"),
],
nil
]
],
[
["inet"],
[
PgInet.new("0.0.0.0/0"),
{
'in'=>PgInet.new("255.255.255.255/32"),
'out'=>PgInet.new("255.255.255.255"),
},
PgInet.new("255.255.255.255"),
PgInet.new("1.2.0.0/16"),
nil
],
],
[
["inet[]"],
[
[
PgInet.new("255.255.255.255"),
PgInet.new("1.2.0.0/16")
],
nil,
],
],
[
["cidr"],
[
PgCidr.new("0.0.0.0/0"),
PgCidr.new("255.255.255.255/32"),
PgCidr.new("1.2.0.0/16"),
nil,
],
],
[
["cidr[]"],
[
[
PgCidr.new("0.0.0.0/0"),
PgCidr.new("255.255.255.255/32")
],
nil,
],
],
[
["macaddr"],
[
PgMacAddr.new("08:00:2b:01:02:03"),
PgMacAddr.new("00:00:00:00:00:00"),
nil,
]
],
[
["macaddr[]"],
[
[
PgMacAddr.new("08:00:2b:01:02:03"),
PgMacAddr.new("00:00:00:00:00:00"),
],
nil,
]
],
]
makeTestConnection do |connection|
connection.exec("set client_min_messages = 'warning'")
for testCase in testCases
columnTypes, values = *testCase
for columnType in columnTypes
assertInfo("For column type #{columnType}") do
connection.exec("create temporary table #{table1} "\
"(v #{columnType})")
for value in values
assertInfo("For value #{value.inspect}") do
if value.is_a?(Hash)
value_in = value['in']
value_out = value['out']
else
value_in = value
value_out = value
end
connection.exec("delete from #{table1}")
insert = Insert.new(table1, connection)
case columnType
when 'bytea[]'
insert.insert_bytea_array('v', value_in)
when /\[\]/
insert.insert_array('v', value_in)
when '"char"'
insert.insert_qchar('v', value_in)
when 'bytea'
insert.insert_bytea('v', value_in)
else
insert.insert('v', value_in)
end
insert.exec
select = Select.new(connection)
select.select('v')
select.from(table1)
result = select.exec[0]['v']
if result.respond_to?(:encoding)
result = result.force_encoding('ASCII-8BIT')
end
assertEquals(result, value_out)
end
end
connection.exec("drop table #{table1}")
end
end
end
end
end
end
RoundTripTest.new.run if $0 == __FILE__