#!/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__