#!/usr/bin/env ruby
#-------------------------------------------------------------------------
# 
# Open Source License/Disclaimer, Forecast Systems Laboratory NOAA/OAR/FSL, 
# 325 Broadway Boulder, CO 80305
# 
# This software is distributed under the Open Source Definition, which may 
# be found at http://www.opensource.org/osd.html.
# 
# In particular, redistribution and use in source and binary forms, with or 
# without modification, are permitted provided that the following conditions
# are met:
# 
#     . Redistributions of source code must retain this notice, this list 
#     of conditions and the following disclaimer. 
# 
#     . Redistributions in binary form must provide access to this notice,
#     this list of conditions and the following disclaimer, and the 
#     underlying source code. 
# 
#     . All modifications to this software must be clearly documented, and
#     are solely the responsibility of the agent making the modifications. 
# 
#     . If significant modifications or enhancements are made to this 
#     software, the FSL Software Policy Manager (softwaremgr@fsl.noaa.gov)
#     should be notified. 
# 
#-------------------------------------------------------------------------
# 
# THIS SOFTWARE AND ITS DOCUMENTATION ARE IN THE PUBLIC DOMAIN AND ARE 
# FURNISHED "AS IS." THE AUTHORS, THE UNITED STATES GOVERNMENT, ITS 
# INSTRUMENTALITIES, OFFICERS, EMPLOYEES, AND AGENTS MAKE NO WARRANTY, 
# EXPRESS OR IMPLIED, AS TO THE USEFULNESS OF THE SOFTWARE AND DOCUMENTATION 
# FOR ANY PURPOSE. THEY ASSUME NO RESPONSIBILITY (1) FOR THE USE OF THE
# SOFTWARE AND DOCUMENTATION; OR (2) TO PROVIDE TECHNICAL SUPPORT TO USERS.
# 
#-------------------------------------------------------------------------
# 
#
# WARNING: This is OPAQUE Facility Division (FD) Software. All OPAQUE FD 
# Software is subject to change without notice.

# TODO - address the sort issue, which can confuse tests

require 'runit/testcase'
require 'runit/cui/testrunner'
require 'runit/testsuite'

require 'postgres'
$:.unshift '../lib'
$:.unshift './lib'
require 'btpgsql.rb'


DBNAME = 'btpgsql' 

$verbose = false 

$passwd    = nil
$pghost    = nil
$pgport    = nil
$pgoptions = nil
$pgtty     = nil
$dbname    = ARGV[0] || DBNAME 
$login     = nil
$passwd    = nil



module BtPgsql

  # A test class for BtPgsql
  class TestSchema < RUNIT::TestCase

    # setup cleans out tables!
    def setup
      @parent     = Relation.new 'parent'
      @child      = Relation.new 'child'
      @grandchild = Relation.new 'grandchild'

      @parent.truncate     
      @child.truncate      
      @grandchild.truncate 
    end

    def test_A_seq_insert_in_future
      tt_start = '2010-01-01 00:00:00'
      @parent.transaction do
	@parent.seq_insert [0,'parent 0','-infinity','epoch', tt_start]
	@parent.seq_insert [0,'parent 0','epoch','infinity', tt_start]
      end
      tuples = @parent.select
      expected = 
      [ 
	[
	  '0', 'parent 0',
	  '-infinity', '1970-01-01 00:00:00',
	  tt_start, 'infinity',
	  '0', '0',
	],
	[
	  '0', 'parent 0',
	  '1970-01-01 00:00:00', 'infinity',
	  tt_start, 'infinity',
	  '0', '1',
	],
      ]
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}

if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end
end

      assert tuples.sort == expected.sort
    end

    def test_A_seq_insert
      @parent.transaction do
	@parent.seq_insert [0,'parent 0','-infinity','epoch']
	@parent.seq_insert [0,'parent 0','epoch','infinity']
      end

      tuples = @parent.select

      expected = 
      [ 
	[
	  '0', 'parent 0',
	  '-infinity', '1970-01-01 00:00:00',
	  tuples[0]['tt_start'], 'infinity',
	  '0', '0',
	],
	[
	  '0', 'parent 0',
	  '1970-01-01 00:00:00', 'infinity',
	  tuples[0]['tt_start'], 'infinity',
	  '0', '1',
	],
      ]
#      expected.each do |tuple|
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}

if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end
end

      assert tuples.sort == expected.sort


      @child.transaction do
	@child.seq_insert [0,0,'child 0','-infinity','epoch'],
			  [0,0,'child 0','epoch','infinity']
      end

      tuples = @child.select

      expected = 
      [ 
	[
	  '0', '0', 'child 0',
	  '-infinity', '1970-01-01 00:00:00',
	  tuples[0]['tt_start'], 'infinity',
	  '0', '0',
	],
	[
	  '0', '0', 'child 0',
	  '1970-01-01 00:00:00', 'infinity',
	  tuples[0]['tt_start'], 'infinity',
	  '0', '1',
	],
      ]
#      expected.each do |tuple|
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}

if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end
end
      
      assert tuples.sort == expected.sort

      @grandchild.transaction do
	@grandchild.seq_insert [
	  [0,0,'grandchild 0','-infinity','epoch'],
	  [0,0,'grandchild 0','epoch','infinity'],
	]
      end

      tuples = @grandchild.select

      expected = 
      [ 
	[
	  '0', '0', 'grandchild 0',
	  '-infinity', '1970-01-01 00:00:00',
	  tuples[0]['tt_start'], 'infinity',
	  '0', '0',
	],
	[
	  '0', '0', 'grandchild 0',
	  '1970-01-01 00:00:00', 'infinity',
	  tuples[0]['tt_start'], 'infinity',
	  '0', '1',
	],
      ]
#      expected.each do |tuple|
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}

if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end
end
      
      assert tuples.sort == expected.sort


      assert_exception RuntimeError do
	@child.transaction do
	  @child.seq_insert [
	    [0,0,'child 0','-infinity','epoch'],
	  ]
	end
      end

      assert_exception RuntimeError do
	@grandchild.transaction do
	  @grandchild.seq_insert [
	    [0,42,'grandchild 0','-infinity','epoch'],
	  ]
	end
      end
    end

    def test_B_foreign_key_coverage
      assert_no_exception do
	@parent.transaction do
	  @parent.seq_insert [
	    [0,'parent 0','-infinity','epoch'],
	    [0,'parent 0','epoch','infinity'],
	  ]
	end
	@child.transaction do
	  @child.seq_insert [
	    [0,0,'child 0','-infinity','infinity'],
	  ]
	end
	@grandchild.transaction do
	  @grandchild.seq_insert [
	    [0,0,'grandchild 0','-infinity','epoch'],
	    [0,0,'grandchild 0','epoch','infinity'],
	  ]
	end
      end
    end

    def test_C_foreign_key_coverage
      assert_no_exception do
	@parent.transaction do
	  @parent.seq_insert [
	    [0,'parent 0','-infinity','epoch'],
	    [0,'parent 0','epoch','2002-01-01'],
	    [0,'parent 0','2002-01-01','infinity'],
	  ]
	end
	@child.transaction do
	  @child.seq_insert [
	    [0,0,'child 0','-infinity','1970-01-01'],
	    [0,0,'child 0','1970-01-01','infinity'],
	  ]
	end
	@grandchild.transaction do
	  @grandchild.seq_insert [
	    [0,0,'grandchild 0','-infinity','infinity'],
	  ]
	end
      end
    end

    def test_D_foreign_key_coverage
      assert_no_exception do
	@parent.transaction do
	  @parent.seq_insert [
	    [0,'parent 0','-infinity','epoch'],
	    [0,'parent 0','epoch','2002-01-01'],
	    [0,'parent 0','2002-01-01','infinity'],
	  ]
	end
	@child.transaction do
	  @child.seq_insert [
	    [0,0,'child 0','-infinity','1970-01-01'],
	    [0,0,'child 0','1970-01-01','infinity'],
	  ]
	end
	@grandchild.transaction do
	  @grandchild.seq_insert [
	    [0,0,'grandchild 0','-infinity','infinity'],
	  ]
	end
      end
    end

    def test_E_foreign_key_coverage
      assert_exception RuntimeError do
	@parent.transaction do
	  # note the gap in there!
	  @parent.seq_insert [
	    [0,'parent 0','-infinity','epoch'],
	    [0,'parent 0','epoch','2002-01-01'],
	    [0,'parent 0','2002-02-01','infinity'],
	  ]
	end
	@child.transaction do
	  @child.seq_insert [
	    [0,0,'child 0','-infinity','1970-01-01'],
	    [0,0,'child 0','1970-01-01','infinity'],
	  ]
	end
      end
    end

    def test_F_foreign_key_coverage
      assert_exception RuntimeError do
	@parent.transaction do
	  @parent.seq_insert [
	    [0,'parent 0','epoch','2002-01-01'],
	    [0,'parent 0','2002-02-01','infinity'],
	  ]
	end
	@child.transaction do
	  @child.seq_insert [
	    [0,0,'child 0','-infinity','1970-01-01'],
	    [0,0,'child 0','1970-01-01','infinity'],
	  ]
	end
      end
    end

#[0,'parent 0','-infinity','epoch'],
#[0,'parent 0','epoch','infinity'],
#[0,0,'child 0','-infinity','epoch'],
#[0,0,'child 0','epoch','infinity'],
#[0,0,'grandchild 0','-infinity','epoch'],
#[0,0,'grandchild 0','epoch','infinity'],

    def test_G_seq_delete
      populate
#puts ''
#puts @parent
      assert_no_exception do
	@parent.transaction do
	  tuples = @parent.select
	  @parent.seq_delete '1960-01-01','1980-01-01',tuples
	end
      end

      tuples = @parent.select 'order by btpk, tt_start, vt_begin'

      expected = 
      [ 
	[
	  '0','parent 0',
	  '-infinity','1970-01-01 00:00:00',
	  tuples[0]['tt_start'], tuples[0]['tt_stop'],
	  '0', '0',
	],
	[
	  '0','parent 0',
	  '1970-01-01 00:00:00','infinity',
	  tuples[0]['tt_start'], tuples[0]['tt_stop'],
	  '0', '1',
	],
	[
	  '0','parent 0',
	  '-infinity','1960-01-01 00:00:00',
	  tuples[2]['tt_start'], 'infinity',
	  '0', '2',
	],
	[
	  '0','parent 0',
	  '1980-01-01 00:00:00','infinity',
	  tuples[2]['tt_start'], 'infinity',
	  '0', '3',
	],
      ]
#      expected.each do |tuple|
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}


if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end

puts ''
tuples.size.times do |i|
  tuples[i].size.times do |j|
    puts "expected[#{i}][#{j}] == tuples[#{i}][#{j}] ? #{expected[i][j] == tuples[i][j]}"
    puts "#{expected[i][j]} == #{tuples[i][j]} ? #{expected[i][j] == tuples[i][j]}"
  end
end
end

#exit
#puts parent
      assert tuples.sort == expected.sort

    end

    def test_H_seq_update
      populate
      assert_no_exception do
	@parent.transaction do
	  tuples = @parent.select
	  @parent.seq_update '1960-01-01','1980-01-01',tuples,
	    'description' => 'PARENT 0'
	end
      end


      tuples = @parent.select 'order by btpk, tt_start, vt_begin'

#puts @parent
#puts tuples

      expected = 
      [ 
	[
	  '0','parent 0',
	  '-infinity','1970-01-01 00:00:00',
	  tuples[0]['tt_start'], tuples[0]['tt_stop'],
	  '0', '0',
	],
	[
	  '0','parent 0',
	  '1970-01-01 00:00:00','infinity',
	  tuples[0]['tt_start'], tuples[0]['tt_stop'],
	  '0', '1',
	],
	[
	  '0','parent 0',
	  '-infinity','1960-01-01 00:00:00',
	  tuples[2]['tt_start'], 'infinity',
	  '0', '2',
	],
	[
	  '0','PARENT 0',
	  '1960-01-01 00:00:00', '1970-01-01 00:00:00',
	  tuples[2]['tt_start'], 'infinity',
	  '0', '3',
	],
	[
	  '0','PARENT 0',
	  '1970-01-01 00:00:00', '1980-01-01 00:00:00',
	  tuples[2]['tt_start'], 'infinity',
	  '0', '5',
	],
	[
	  '0','parent 0',
	  '1980-01-01 00:00:00', 'infinity',
	  tuples[2]['tt_start'], 'infinity',
	  '0', '4',
	],
      ]
#      expected.each do |tuple|
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}

if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end

  puts ''
  tuples.size.times do |i|
    tuples[i].size.times do |j|
      puts "expected[#{i}][#{j}] == tuples[#{i}][#{j}] ? #{expected[i][j] == tuples[i][j]}"
      puts "#{expected[i][j]} == #{tuples[i][j]} ? #{expected[i][j] == tuples[i][j]}"
    end
  end
end

#exit
      assert tuples.sort == expected.sort
    end

    def test_I_warp
      @parent.transaction do
	@parent.seq_insert [
	  [0,'parent 0','-infinity','2002-01-01'],
	  [0,'parent 0','2002-01-01','infinity'],
	]
      end
      @child.transaction do
	@child.seq_insert [
	  [0,0,'child 0','-infinity','2002-01-01'],
	  [0,0,'child 0','2002-01-01','infinity'],
	]
      end
      @grandchild.transaction do
	@grandchild.seq_insert [
	  [0,0,'grandchild 0','-infinity','2002-01-01'],
	  [0,0,'grandchild 0','2002-01-01','infinity'],
	]
      end

      assert_no_exception do
	@parent.transaction do
	  @parent.warp 'epoch'
	end
      end

      #puts @parent
      #puts @child
      #puts @grandchild
#exit

      tuples = @parent.select 'order by btpk, tt_start, vt_begin'

      expected = 
      [ 
	[
	  '0','parent 0',
	  '-infinity','2002-01-01 00:00:00',
	  tuples[0]['tt_start'], tuples[0]['tt_stop'],
	  '0', '0',
	],
	[
	  '0','parent 0',
	  '2002-01-01 00:00:00','infinity',
	  tuples[0]['tt_start'], 'infinity',
	  '0', '1',
	],
	[
	  '0','parent 0',
	  '1970-01-01 00:00:00','2002-01-01 00:00:00',
	  tuples[2]['tt_start'], 'infinity',
	  '0', '2',
	],
      ]
#      expected.taint
#      expected.each do |tuple|
#	tuple.taint
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}

#puts ''
#tuples.size.times do |i|
#puts "#{i}"
#puts tuples[i] == expected[i]
#  tuples[0].size.times do |j|
#    puts "expected[#{i}][#{j}] == tuples[#{i}][#{j}] ? #{expected[i][j] == tuples[i][j]}"
#    puts "#{expected[i][j]} == #{tuples[i][j]} ? #{expected[i][j] == tuples[i][j]}"
#  end
#end
if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end
end

      assert tuples.sort == expected.sort
    end

    def test_J_seq_push

      assert_no_exception do
	@parent.seq_push [0,'parent 0']
	@parent.seq_push [0,'Parent 0','1970-01-01 00:00:00']
	@parent.seq_push [0,'PArent 0','1980-01-01 00:00:00']
	@parent.seq_push [0,'PARent 0','1990-01-01 00:00:00']
      end


      @parent.truncate
      assert_no_exception do
	@parent.seq_push [0,'parent 0','-infinity','infinity']
      end

      tuples = @parent.select 'order by btpk, tt_start, vt_begin'

      expected = 
      [ 
	[
	  '0','parent 0',
	  '-infinity','infinity',
	  tuples[0]['tt_start'], 'infinity',
	  '0', '0',
	],
      ]
#      expected.taint
#      expected.each do |tuple|
#	tuple.taint
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}


if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end
end

      assert tuples.sort == expected.sort

      @parent.truncate

      assert_no_exception do
	@parent.seq_push [0,'parent 0','-infinity',]
      end

      tuples = @parent.select 'order by btpk, tt_start, vt_begin'

      expected = 
      [ 
	[
	  '0','parent 0',
	  '-infinity','infinity',
	  tuples[0]['tt_start'], 'infinity',
	  '0', '0',
	],
      ]
#      expected.taint
#      expected.each do |tuple|
#	tuple.taint
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}

if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end
end

      assert tuples.sort == expected.sort
    end

    def test_K_seq_push
      assert_no_exception do
	@parent.seq_push [0,'parent 0','-infinity','epoch']
	@parent.seq_push [0,'PARENT 0','epoch','infinity']
      end

      tuples = @parent.select 'order by btpk, tt_start, vt_begin'

      expected = 
      [ 
	[
	  '0','parent 0',
	  '-infinity','1970-01-01 00:00:00',
	  tuples[0]['tt_start'], 'infinity',
	  '0', '0',
	],
	[
	  '0','PARENT 0',
	  '1970-01-01 00:00:00', 'infinity',
	  tuples[1]['tt_start'], 'infinity',
	  '0', '1',
	],
      ]
#      expected.taint
#      expected.each do |tuple|
#	tuple.taint
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}

if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end
end

      assert tuples.sort == expected.sort
    end

    def test_L_seq_push
      assert_no_exception do
	@parent.transaction do
	  @parent.seq_push [0,'parent 0','-infinity']
	  @parent.seq_push [0,'Parent 0','epoch']
	  @parent.seq_push [0,'PArent 0','1980-01-01']
	  @parent.seq_push [0,'PARent 0','1990-01-01']
	  @parent.seq_push [0,'PAREnt 0','2000-01-01']
	  @parent.seq_push [0,'PARENt 0','2001-01-01']
	  @parent.seq_push [0,'PARENT 0','2002-01-01']
	end
      end

      tuples = @parent.select 'order by btpk, tt_start, vt_begin'

      expected = 
      [ 
	[
	  '0','parent 0',
	  '-infinity','infinity',
	  tuples[0]['tt_start'], tuples[0]['tt_stop'],
	  '0', '0',
	],
	[
	  '0','parent 0',
	  '-infinity','1970-01-01 00:00:00',
	  tuples[0]['tt_stop'], 'infinity',
	  '0', '1',
	],
	[
	  '0','Parent 0',
	  '1970-01-01 00:00:00', 'infinity',
	  tuples[0]['tt_stop'], tuples[0]['tt_stop'],
	  '0', '2',
	],
	[
	  '0','Parent 0',
	  '1970-01-01 00:00:00', '1980-01-01 00:00:00',
	  tuples[0]['tt_stop'], 'infinity',
	  '0', '3',
	],
	[
	  '0','PArent 0',
	  '1980-01-01 00:00:00', 'infinity',
	  tuples[0]['tt_stop'], tuples[0]['tt_stop'],
	  '0', '4',
	],
	[
	  '0','PArent 0',
	  '1980-01-01 00:00:00', '1990-01-01 00:00:00',
	  tuples[0]['tt_stop'], 'infinity',
	  '0', '5',
	],
	[
	  '0','PARent 0',
	  '1990-01-01 00:00:00', 'infinity',
	  tuples[0]['tt_stop'], tuples[0]['tt_stop'],
	  '0', '6',
	],
	[
	  '0','PARent 0',
	  '1990-01-01 00:00:00', '2000-01-01 00:00:00',
	  tuples[0]['tt_stop'], 'infinity',
	  '0', '7',
	],
	[
	  '0','PAREnt 0',
	  '2000-01-01 00:00:00', 'infinity',
	  tuples[0]['tt_stop'], tuples[0]['tt_stop'],
	  '0', '8',
	],
	[
	  '0','PAREnt 0',
	  '2000-01-01 00:00:00', '2001-01-01 00:00:00',
	  tuples[0]['tt_stop'], 'infinity',
	  '0', '9',
	],
	[
	  '0','PARENt 0',
	  '2001-01-01 00:00:00', 'infinity',
	  tuples[0]['tt_stop'], tuples[0]['tt_stop'],
	  '0', '10',
	],
	[
	  '0','PARENt 0',
	  '2001-01-01 00:00:00', '2002-01-01 00:00:00',
	  tuples[0]['tt_stop'], 'infinity',
	  '0', '11',
	],
	[
	  '0','PARENT 0',
	  '2002-01-01 00:00:00', 'infinity',
	  tuples[0]['tt_stop'], 'infinity',
	  '0', '12',
	],
      ]
#      expected.taint
#      expected.each do |tuple|
#	tuple.taint
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}

if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end
end

      assert tuples.sort == expected.sort
    end

    def test_M_ri_violators
      sql = <<-sql
	insert into parent
	  values (0,'parent 0','-infinity','epoch','now','infinity',0);
	insert into parent
	  values (0,'parent 0','1960-01-01','1980-01-01','now','infinity',0);
      
	insert into child
	  values (0,0,'child 0','-infinity','epoch','now','infinity',0);
      sql
      $pgconn.exec sql

      violators = @parent.ri_violators

      assert violators['pk'].size == 2 

      violators = @child.ri_violators

      assert violators['fk'].size == 1
    end


    def test_N_coalesce
      @parent.transaction do
	@parent.seq_insert [
	  [0,'parent 0','-infinity','1960-01-01'],
	  [0,'parent 0','1960-01-01','1970-01-01'],
	  [1,'parent 1','-infinity','1960-01-01'],
	  [1,'parent 1','1960-01-01','1970-01-01'],
	  [0,'parent 0','1980-01-01','1990-01-01'],
	  [0,'parent 0','1990-01-01','2000-01-01'],
	  [0,'parent 0','2000-01-01','infinity'],
	  [2,'parent 2','-infinity','infinity'],
	  [1,'parent 1','1970-01-01','infinity'],
	]
      end

#puts @parent

    @parent.coalesce

#puts @parent
#exit

      tuples = @parent.select 'order by btpk, tt_start, vt_begin'

      expected = 
      [ 
	[
	  '0','parent 0',
	  '-infinity','1970-01-01 00:00:00',
	  tuples[0]['tt_start'], 'infinity',
	  '0', tuples[0]['btid'],
	],
	[
	  '0','parent 0',
	  '1980-01-01 00:00:00', 'infinity',
	  tuples[1]['tt_start'], 'infinity',
	  '0', tuples[1]['btid'],
	],
	[
	  '1','parent 1',
	  '-infinity', 'infinity',
	  tuples[2]['tt_start'], 'infinity',
	  '1', tuples[2]['btid'],
	],
	[
	  '2','parent 2',
	  '-infinity', 'infinity',
	  tuples[3]['tt_start'], 'infinity',
	  '2', tuples[3]['btid'],
	],
      ]
#      expected.taint
#      expected.each do |tuple|
#	tuple.taint
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}

if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end
end

      assert tuples.sort == expected.sort

    end

    def test_O_coalesce

      @parent.coalesce

      tuples = @parent.select 'order by btpk, tt_start, vt_begin'

      expected = 
      [ 
      ]
#      expected.taint
#      expected.each do |tuple|
#	tuple.taint
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}

if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end
end

      assert tuples.sort == expected.sort
    end

    def test_P_coalesce
      @parent.transaction do
	@parent.seq_insert [
	  [0,'parent 0','-infinity','1960-01-01'],
	  [0,'parent 0','1960-01-01','1970-01-01'],
	]
      end

      @parent.coalesce

      tuples = @parent.select 'order by btpk, tt_start, vt_begin'

      expected = 
      [ 
	[
	  '0','parent 0',
	  '-infinity','1970-01-01 00:00:00',
	  tuples[0]['tt_start'], 'infinity',
	  '0', tuples[0]['btid'],
	],
      ]
#      expected.taint
#      expected.each do |tuple|
#	tuple.taint
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}


if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end
end

      assert tuples.sort == expected.sort

    end

    def test_Q_coalesce
      @parent.transaction do
	@parent.seq_insert [
	  [0,'parent 0','-infinity','1960-01-01'],
	]
      end

      @parent.coalesce

      tuples = @parent.select 'order by btpk, tt_start, vt_begin'

      expected = 
      [ 
	[
	  '0','parent 0',
	  '-infinity','1960-01-01 00:00:00',
	  tuples[0]['tt_start'], 'infinity',
	  '0', tuples[0]['btid'],
	],
      ]
#      expected.taint
#      expected.each do |tuple|
#	tuple.taint
#	tuple.each do |cell|
#	  cell.taint
#	end
#      end
      tuples.map {|tuple| tuple.map {|cell| cell.untaint}}

if $verbose
      puts "\n\n" 
      puts 'EXPECTED'
      expected.each do |tuple|
	puts tuple.inspect
      end
      puts 'ACTUAL'
      tuples.each do |tuple|
	puts tuple.inspect
      end
end

      assert tuples.sort == expected.sort
    end


    def populate
      @parent.transaction do
	@parent.seq_insert [
	  [0,'parent 0','-infinity','epoch'],
	  [0,'parent 0','epoch','infinity'],
	]
      end
      @child.transaction do
	@child.seq_insert [
	  [0,0,'child 0','-infinity','epoch'],
	  [0,0,'child 0','epoch','infinity'],
	]
      end
      @grandchild.transaction do
	@grandchild.seq_insert [
	  [0,0,'grandchild 0','-infinity','epoch'],
	  [0,0,'grandchild 0','epoch','infinity'],
	]
      end
    end
  end
end


# RUN TEST - MAIN PROGRAM

setup unless ARGV[0]

$pgconn = PGconn.new $pghost,$pgport,$pgoptions,
		     $pgtty,$dbname,$login,$passwd

RUNIT::CUI::TestRunner.run(BtPgsql::TestSchema.suite)

$pgconn.close

teardown unless ARGV[0]



# we begin by setting up the required tables, and populating them
BEGIN {
  class PGconn
    def exec_file path
      sql = (IO.readlines(path)).to_s
      sql.gsub!(%r{--.*$}, '') # kill comments
      sql.gsub!(%r{\n}, ' ')   # kill newlines
      sql.split(%r{;}).each do |statement|
	begin
	  res = self.exec statement 
	  res.clear
	rescue => pgerror
	  raise pgerror unless pgerror.to_s =~ /does not exist/i
	end
      end
    end
  end
  def setup
    # create the database DBNAME 
    pgconn = PGconn.new $pghost,$pgport,$pgoptions,
			 $pgtty,nil,$login,$passwd
    begin
      res = pgconn.exec "drop database #{$dbname}"
      res.clear
    rescue
      # ignore
    end
    res = pgconn.exec "create database #{$dbname}"
    res.clear
    pgconn.close

    # connect to $dbname 
    pgconn = PGconn.new $pghost,$pgport,$pgoptions,
			 $pgtty,$dbname,$login,$passwd

    # set up the bi-temporal support relations
    path = ((File.dirname(__FILE__) << File::SEPARATOR << 'btschema.sql'))
    pgconn.exec_file path

    # insert the data specific to this test
    path = ((File.dirname(__FILE__) << File::SEPARATOR << 'btpgsql.sql'))
    pgconn.exec_file path

    pgconn.close
  end
  def teardown
    $pgconn.close rescue true
    pgconn = PGconn.new
    res = pgconn.exec "drop database #{$dbname}"
    res.clear
    pgconn.close
  end
}