t/db-object-changes-only-1.t

#!/usr/bin/perl -w

use strict;

use Test::More tests => 494;

BEGIN 
{
  require 't/test-lib.pl';
  use_ok('Rose::DB::Object');
  use_ok('Rose::DB::Object::Util');
}

Rose::DB::Object::Util->import(':all');

our($PG_HAS_CHKPASS, $HAVE_PG, $HAVE_MYSQL, $HAVE_INFORMIX, $HAVE_SQLITE);

#
# PostgreSQL
#

SKIP: foreach my $db_type (qw(pg pg_with_schema))
{
  skip("PostgreSQL tests", 226)  unless($HAVE_PG);

  Rose::DB->default_type($db_type);

  TEST_HACK:
  {
    no warnings;
    *MyPgObject::init_db = sub { Rose::DB->new($db_type) };
  }

  my $o = MyPgObject->new(name => 'John', 
                          k1   => 1,
                          k2   => undef,
                          k3   => 3);

  ok(ref $o && $o->isa('MyPgObject'), "new() 1 - $db_type");

  $o->flag2('TRUE');
  $o->date_created('now');
  $o->last_modified($o->date_created);
  $o->save_col(7);

  if(rand >= 0.5)
  {
    ok($o->save, "save() 1 - $db_type");
  }
  else
  {
    ok($o->insert, "insert() 1 - $db_type");
  }

  is($o->meta->primary_key->sequence_names->[0], 'rose_db_object_test_id_seq', 
     "pk sequence name - $db_type");

  ok(is_in_db($o), "is_in_db - $db_type");

  is($o->id, 1, "auto-generated primary key - $db_type");

  ok($o->load, "load() 1 - $db_type");

  $o->name('C' x 50);
  is($o->name, 'C' x 32, "varchar truncation - $db_type");

  $o->name('John');

  $o->code('A');
  is($o->code, 'A     ', "character padding - $db_type");

  $o->code('C' x 50);
  is($o->code, 'C' x 6, "character truncation - $db_type");

  my $ouk;
  ok($ouk = MyPgObject->new(k1 => 1,
                            k2 => undef,
                            k3 => 3)->load, "load() uk 1 - $db_type");

  ok(!$ouk->not_found, "not_found() uk 1 - $db_type");

  is($ouk->id, 1, "load() uk 2 - $db_type");
  is($ouk->name, 'John', "load() uk 3 - $db_type");

  ok($ouk->save, "save() uk 1 - $db_type");

  my $o2 = MyPgObject->new(id => $o->id);

  ok(ref $o2 && $o2->isa('MyPgObject'), "new() 2 - $db_type");

  is($o2->bits->to_Bin, '00101', "bits() (bitfield default value) - $db_type");

  ok($o2->load, "load() 2 - $db_type");
  ok(!$o2->not_found, "not_found() 1 - $db_type");

  is($o2->name, $o->name, "load() verify 1 - $db_type");
  is($o2->date_created, $o->date_created, "load() verify 2 - $db_type");
  is($o2->last_modified, $o->last_modified, "load() verify 3 - $db_type");
  is($o2->status, 'active', "load() verify 4 (default value) - $db_type");
  is($o2->flag, 1, "load() verify 5 (default boolean value) - $db_type");
  is($o2->flag2, 1, "load() verify 6 (boolean value) - $db_type");
  is($o2->save_col, 7, "load() verify 7 (aliased column) - $db_type");
  is($o2->start->ymd, '1980-12-24', "load() verify 8 (date value) - $db_type");

  $o2->set_status('foo');
  is($o2->get_status, 'foo', "get_status() - $db_type");
  $o2->set_status('active');
  eval { $o2->set_status };
  ok($@, "set_status() - $db_type");

  is($o2->bits->to_Bin, '00101', "load() verify 9 (bitfield value) - $db_type");

  my $clone = $o2->clone;
  ok($o2->start eq $clone->start, "clone() 1 - $db_type");
  $clone->start->set(year => '1960');
  ok($o2->start ne $clone->start, "clone() 2 - $db_type");

  $o2->start('5/24/2001');

  sleep(1); # keep the last modified dates from being the same

  $o2->last_modified('now');
  ok($o2->save, "save() 2 - $db_type");
  ok($o2->load, "load() 3 - $db_type");

  ok(!has_modified_columns($o2), "no modified columns after load() - $db_type");

  $o2->name('John 2');
  $o2->save(changes_only => 1);

  is($o2->date_created, $o->date_created, "save() verify 1 - $db_type");
  ok($o2->last_modified ne $o->last_modified, "save() verify 2 - $db_type");
  is($o2->start->ymd, '2001-05-24', "save() verify 3 (date value) - $db_type");

  my $bo = MyPgObject->new(id => $o->id);
  $bo->load;
  $bo->flag(0);
  $bo->save;

  $bo = MyPgObject->new(id => $o->id);
  $bo->load;

  ok(!$bo->flag, "boolean check - $db_type");

  $bo->flag(0);
  $bo->save;

  my $o3 = MyPgObject->new();

  my $db = $o3->db or die $o3->error;

  ok(ref $db && $db->isa('Rose::DB'), "db() - $db_type");

  is($db->dbh, $o3->dbh, "dbh() - $db_type");

  my $o4 = MyPgObject->new(id => 999);
  ok(!$o4->load(speculative => 1), "load() nonexistent - $db_type");
  ok($o4->not_found, "not_found() 2 - $db_type");

  ok($o->load, "load() 4 - $db_type");

  SKIP:
  {
    if($PG_HAS_CHKPASS)
    {
      $o->{'password_encrypted'} = ':8R1Kf2nOS0bRE';

      ok($o->password_is('xyzzy'), "chkpass() 1 - $db_type");
      is($o->password, 'xyzzy', "chkpass() 2 - $db_type");

      $o->password('foobar');

      ok($o->password_is('foobar'), "chkpass() 3 - $db_type");
      is($o->password, 'foobar', "chkpass() 4 - $db_type");

      $o->code('C1');
      #local $Rose::DB::Object::Debug = 1;
      ok($o->save, "save() 3 - $db_type");

      $o = MyPgObject->new(id => $o->id)->load;      
      $o->code('C2');
      $o->save;

      $o = MyPgObject->new(id => $o->id)->load;
      ok($o->password_is('foobar'), "chkpass() 6 - $db_type");
    }
    else
    {
      skip("chkpass tests", 6);
    }
  }

  my $o5 = MyPgObject->new(id => $o->id);

  ok($o5->load, "load() 5 - $db_type");

  SKIP:
  {
    if($PG_HAS_CHKPASS)
    {
      ok($o5->password_is('foobar'), "chkpass() 7 - $db_type");
      is($o5->password, 'foobar', "chkpass() 8 - $db_type"); 
    }
    else
    {
      skip("chkpass tests", 2);
    }
  }

  $o5->nums([ 4, 5, 6 ]);
  ok($o5->save, "save() 4 - $db_type");
  ok($o->load, "load() 6 - $db_type");

  is($o5->nums->[0], 4, "load() verify 10 (array value) - $db_type");
  is($o5->nums->[1], 5, "load() verify 11 (array value) - $db_type");
  is($o5->nums->[2], 6, "load() verify 12 (array value) - $db_type");

  my @a = $o5->nums;

  is($a[0], 4, "load() verify 13 (array value) - $db_type");
  is($a[1], 5, "load() verify 14 (array value) - $db_type");
  is($a[2], 6, "load() verify 15 (array value) - $db_type");
  is(@a, 3, "load() verify 16 (array value) - $db_type");

  ok($o->delete, "delete() - $db_type");

  $o = MyPgObject->new(name => 'John', id => 9);
  $o->save_col(22);
  ok($o->save, "save() 4 - $db_type");
  $o->save_col(50);
  ok($o->save, "save() 5 - $db_type");

  $ouk = MyPgObject->new(save_col => 50);
  ok($ouk->load, "load() aliased unique key - $db_type");

  eval { $o->meta->alias_column(nonesuch => 'foo') };
  ok($@, "alias_column() nonesuch - $db_type");

  # This is okay now
  #eval { $o->meta->alias_column(id => 'foo') };
  #ok($@, "alias_column() primary key - $db_type");

  $o = MyPgObject->new(id => 777);

  $o->meta->error_mode('fatal');

  $o->dbh->{'PrintError'} = 0;

  eval { $o->load };
  ok($@ && $o->not_found, "load() not found fatal - $db_type");

  $o->id('abc');

  eval { $o->load };
  ok($@ && !$o->not_found, "load() fatal - $db_type");

  eval { $o->save };
  ok($@, "save() fatal - $db_type");

  $o = MyPgObject->new(id => 9999); # no such id

  $o->meta->error_mode('fatal');

  eval { $o->load() };
  ok($@, "load() non-speculative implicit - $db_type");  
  ok(!$o->load(speculative => 1), "load() speculative explicit 1 - $db_type");
  eval { $o->load(speculative => 0) };
  ok($@, "load() non-speculative explicit 2 - $db_type");

  $o->meta->default_load_speculative(1);

  ok(!$o->load(), "load() speculative implicit - $db_type");  
  ok(!$o->load(speculative => 1), "load() speculative explicit 2 - $db_type");
  eval { $o->load(speculative => 0) };
  ok($@, "load() non-speculative explicit 2 - $db_type");

  # Reset for next trip through loop
  $o->meta->default_load_speculative(0);
  $o->meta->error_mode('return');

  $o = MyPgObject->new(name => 'John', 
                       k1   => 1,
                       k2   => undef,
                       k3   => 3)->save;

  is($o->dur->months, 2, "interval months 1 - $db_type");
  is($o->dur->days, 5, "interval days 1 - $db_type");
  is($o->dur->seconds, 3, "interval seconds 1 - $db_type");

  $o->dur(DateTime::Duration->new(years => 7, nanoseconds => 3000));

  is($o->dur->in_units('years'), 7, "interval in_units years 1 - $db_type");
  is($o->dur->in_units('months'), 84, "interval in_units months 1 - $db_type");
  is($o->dur->nanoseconds, 3000, "interval nanoseconds 1 - $db_type");
  is($o->dur->days, 0, "interval days 2 - $db_type");
  is($o->dur->minutes, 0, "interval minutes 2 - $db_type");
  is($o->dur->seconds, 0, "interval seconds 2 - $db_type");

  $o->save;

  $o = MyPgObject->new(id => $o->id)->load;

  is($o->dur->in_units('years'), 7, "interval in_units years 2 - $db_type");
  is($o->dur->in_units('months'), 84, "interval in_units months 2 - $db_type");
  is($o->dur->nanoseconds, 3000, "interval nanoseconds 2 - $db_type");
  is($o->dur->days, 0, "interval days 3 - $db_type");
  is($o->dur->minutes, 0, "interval minutes 3 - $db_type");
  is($o->dur->seconds, 0, "interval seconds 3 - $db_type");

  is($o->epoch(format => '%Y-%m-%d %H:%M:%S'), '1999-11-30 21:30:00', "epoch 1 - $db_type");

  $o->hiepoch('943997400.123456');
  is($o->hiepoch(format => '%Y-%m-%d %H:%M:%S.%6N'), '1999-11-30 21:30:00.123456', "epoch hires 1 - $db_type");

  $o->epoch('5/6/1980 12:34:56');

  $o->save;

  $o = MyPgObject->new(id => $o->id)->load;

  is($o->epoch(format => '%Y-%m-%d %H:%M:%S'), '1980-05-06 12:34:56', "epoch 2 - $db_type");
  is($o->hiepoch(format => '%Y-%m-%d %H:%M:%S.%6N'), '1999-11-30 21:30:00.123456', "epoch hires 2 - $db_type");

  is($o->bint1, '9223372036854775800', "bigint 1 - $db_type");
  is($o->bint2, '-9223372036854775800', "bigint 2 - $db_type");
  is($o->bint3, '9223372036854775000', "bigint 3 - $db_type");
  is($o->bint4, undef, "bigint 3.1 - $db_type");

  $o->bint1($o->bint1 + 1);
  $o->save;

  $o = MyPgObject->new(id => $o->id)->load;
  is($o->bint1, '9223372036854775801', "bigint 4 - $db_type");

  $o->bint3(5);
  eval { $o->bint3(7) };
  ok($@, "bigint 5 - $db_type");

  #local $Rose::DB::Object::Debug = 1;  
  $o = MyPgObject2->new->save(changes_only => 1);
  $o = MyPgObject2->new(id => $o->id)->load;

  is($o->num, 123, "insert changes only 1 - $db_type");
  is($o->flag, 2, "insert changes only 2 - $db_type");

  $o = MyPgObject2->new(flag => 7)->save(changes_only => 1);
  $o = MyPgObject2->new(id => $o->id)->load;

  is($o->num, 123, "insert changes only 3 - $db_type");
  is($o->flag, 7, "insert changes only 4 - $db_type");

  #local $Rose::DB::Object::Debug = 1;  
  $o = MyPgObject3->new->save;
  $o = MyPgObject3->new(id => $o->id)->load;

  is($o->num, 123, "insert changes only 5 - $db_type");
  is($o->flag, 1, "insert changes only 6 - $db_type");

  $o = MyPgObject2->new(flag => 7)->save(changes_only => 1);
  $o = MyPgObject2->new(id => $o->id)->load;

  is($o->num, 123, "insert changes only 7 - $db_type");
  is($o->flag, 7, "insert changes only 8 - $db_type");

  MyPgObject3->meta->allow_inline_column_values(1);
  $o = MyPgObject3->new(dt => 'now()')->save(changes_only => 1);
  $o = MyPgObject3->new(id => $o->id)->load;

  is($o->num, 123, "insert changes only 9 - $db_type");
  is($o->flag, 1, "insert changes only 10 - $db_type");
}

#
# MySQL
#

SKIP: foreach my $db_type ('mysql')
{
  skip("MySQL tests", 105)  unless($HAVE_MYSQL);

  Rose::DB->default_type($db_type);

  my $o = MyMySQLObject->new(name => 'John',
                             k1   => 1,
                             k2   => undef,
                             k3   => 3);

  ok(ref $o && $o->isa('MyMySQLObject'), "new() 1 - $db_type");

  $o->flag2('true');
  $o->date_created('now');
  $o->last_modified($o->date_created);
  $o->save_col(22);

  $o->bitz3('11');

  if(rand >= 0.5)
  {
    ok($o->save, "save() 1 - $db_type");
  }
  else
  {
    ok($o->insert, "insert() 1 - $db_type");
  }

  ok($o->load, "load() 1 - $db_type");

  my $ox = MyMySQLObject->new(id => $o->id)->load;
  is($ox->bitz2->to_Bin(), '00', "spot check bitfield 1 - $db_type");
  is($ox->bitz3->to_Bin(), '0011', "spot check bitfield 2 - $db_type");

  eval { $o->name('C' x 50) };
  ok($@, "varchar overflow fatal - $db_type");

  $o->name('John');

  $o->code('A');
  is($o->code, 'A     ', "character padding - $db_type");

  eval { $o->code('C' x 50) };
  ok($@, "code overflow fatal - $db_type");
  $o->code('C' x 6);

  is($o->enums, 'foo', "enum 1 - $db_type");
  eval { $o->enums('blee') };
  ok($@, "enum 2 - $db_type");

  $o->enums('bar');

  my $ouk;

  ok($ouk = MyMySQLObject->new(k1 => 1,
                               k2 => undef,
                               k3 => 3)->load, "load() uk 1 - $db_type");

  ok(!$ouk->not_found, "not_found() uk 1 - $db_type");

  is($ouk->id, 1, "load() uk 2 - $db_type");
  is($ouk->name, 'John', "load() uk 3 - $db_type");

  ok($ouk->save, "save() uk 1 - $db_type");

  my $o2 = MyMySQLObject->new(id => $o->id);

  ok(ref $o2 && $o2->isa('MyMySQLObject'), "new() 2 - $db_type");

  is($o2->bits->to_Bin, '00101', "bits() (bitfield default value) - $db_type");
  is($o2->bitz2->to_Bin, '00', "bitz2() (bitfield default value) - $db_type");

  ok($o2->load, "load() 2 - $db_type");

  ok(!$o2->not_found, "not_found() 1 - $db_type");

  is($o2->name, $o->name, "load() verify 1 - $db_type");
  is($o2->date_created, $o->date_created, "load() verify 2 - $db_type");
  is($o2->last_modified, $o->last_modified, "load() verify 3 - $db_type");
  is($o2->status, 'active', "load() verify 4 (default value) - $db_type");
  is($o2->flag, 1, "load() verify 5 (default boolean value) - $db_type");
  is($o2->flag2, 1, "load() verify 6 (boolean value) - $db_type");
  is($o2->save_col, 22, "load() verify 7 (aliased column) - $db_type");
  is($o2->start->ymd, '1980-12-24', "load() verify 8 (date value) - $db_type");

  $o2->set_status('foo');
  is($o2->get_status, 'foo', 'get_status()');
  $o2->set_status('active');
  eval { $o2->set_status };
  ok($@, 'set_status()');

  is($o2->bits->to_Bin, '00101', "load() verify 9 (bitfield value) - $db_type");
  is($o2->bitz2->to_Bin, '00', "load() verify 10 (bitfield value) - $db_type");
  is($o2->bitz3->to_Bin, '0011', "load() verify 11 (bitfield value) - $db_type");

  my $clone = $o2->clone;
  ok($o2->start eq $clone->start, "clone() 1 - $db_type");
  $clone->start->set(year => '1960');
  ok($o2->start ne $clone->start, "clone() 2 - $db_type");

  $o2->name('John 2');
  $o2->start('5/24/2001');

  sleep(1); # keep the last modified dates from being the same

  $o2->last_modified('now');
  ok($o2->save, "save() 2 - $db_type");
  ok($o2->load, "load() 3 - $db_type");

  is($o2->date_created, $o->date_created, "save() verify 1 - $db_type");
  ok($o2->last_modified ne $o->last_modified, "save() verify 2 - $db_type");
  is($o2->start->ymd, '2001-05-24', "save() verify 3 (date value) - $db_type");

  my $o3 = MyMySQLObject->new();

  my $db = $o3->db or die $o3->error;

  ok(ref $db && $db->isa('Rose::DB'), "db() - $db_type");

  is($db->dbh, $o3->dbh, "dbh() - $db_type");

  my $o4 = MyMySQLObject->new(id => 999);
  ok(!$o4->load(speculative => 1), "load() nonexistent - $db_type");
  ok($o4->not_found, "not_found() 2 - $db_type");

  $o->nums([ 4, 5, 6 ]);
  ok($o->save, "save() 3 - $db_type");
  ok($o->load, "load() 4 - $db_type");

  is($o->nums->[0], 4, "load() verify 10 (array value) - $db_type");
  is($o->nums->[1], 5, "load() verify 11 (array value) - $db_type");
  is($o->nums->[2], 6, "load() verify 12 (array value) - $db_type");

  my @a = $o->nums;

  is($a[0], 4, "load() verify 13 (array value) - $db_type");
  is($a[1], 5, "load() verify 14 (array value) - $db_type");
  is($a[2], 6, "load() verify 15 (array value) - $db_type");
  is(@a, 3, "load() verify 16 (array value) - $db_type");

  ok($o->delete, "delete() - $db_type");

  $o = MyMySQLObject->new(name => 'John', id => 9);
  $o->save_col(22);

  ok($o->save, "save() 4 - $db_type");
  $o->save_col(50);
  ok($o->save, "save() 5 - $db_type");

  $ouk = MyMySQLObject->new(save_col => 50);
  ok($ouk->load, "load() aliased unique key - $db_type");

  eval { $o->meta->alias_column(nonesuch => 'foo') };
  ok($@, "alias_column() nonesuch - $db_type");

  # This is okay now
  #eval { $o->meta->alias_column(id => 'foo') };
  #ok($@, "alias_column() primary key - $db_type");

  $o = MyMySQLObject->new(id => 777);

  $o->meta->error_mode('fatal');

  $o->dbh->{'PrintError'} = 0;

  eval { $o->load };
  ok($@ && $o->not_found, "load() not found fatal - $db_type");

  my $old_table = $o->meta->table;

  $o->meta->table('nonesuch');

  eval { $o->load };
  ok($@ && !$o->not_found, "load() fatal - $db_type");

  eval { $o->save };
  ok($@, "save() fatal - $db_type");

  $o->meta->table($old_table);  
  $o->meta->error_mode('return');

  $o = MyMPKMySQLObject->new(name => 'John');

  ok($o->save, "save() 1 multi-value primary key with generated values - $db_type");

  is($o->k1, 1, "save() verify 1 multi-value primary key with generated values - $db_type");
  is($o->k2, 2, "save() verify 2 multi-value primary key with generated values - $db_type");

  $o = MyMPKMySQLObject->new(name => 'Alex');

  ok($o->save, "save() 2 multi-value primary key with generated values - $db_type");

  is($o->k1, 3, "save() verify 3 multi-value primary key with generated values - $db_type");
  is($o->k2, 4, "save() verify 4 multi-value primary key with generated values - $db_type");

  is($ox->bitz3->to_Bin(), '0011', "spot check bitfield 3 - $db_type");

  $ox->bitz3->Bit_On(3);
  set_column_value_modified($ox, 'bitz3');
  is($ox->bitz3->to_Bin(), '1011', "spot check bitfield 4 - $db_type");

  $ox->save(insert => 1);

  $ox = MyMySQLObject->new(id => $ox->id)->load;
  is($ox->bitz3->to_Bin(), '1011', "spot check bitfield 5 - $db_type");

  $ox->bitz3->Bit_On(2);
  set_column_value_modified($ox, 'bitz3');
  $ox->save;
  $ox = MyMySQLObject->new(id => $ox->id)->load;
  is($ox->bitz3->to_Bin(), '1111', "spot check bitfield 6 - $db_type");

  $o = MyMySQLObject->new(id => 9999); # no such id

  $o->meta->error_mode('fatal');

  eval { $o->load() };
  ok($@, "load() non-speculative implicit - $db_type");  
  ok(!$o->load(speculative => 1), "load() speculative explicit 1 - $db_type");
  eval { $o->load(speculative => 0) };
  ok($@, "load() non-speculative explicit 2 - $db_type");

  $o->meta->default_load_speculative(1);

  ok(!$o->load(), "load() speculative implicit - $db_type");  
  ok(!$o->load(speculative => 1), "load() speculative explicit 2 - $db_type");
  eval { $o->load(speculative => 0) };
  ok($@, "load() non-speculative explicit 2 - $db_type");

  $o->meta->default_load_speculative(0);

  $o = MyMySQLObject->new(id => 1)->load;

  is($o->dur->months, 2, "interval months 1 - $db_type");
  is($o->dur->days, 5, "interval days 1 - $db_type");
  is($o->dur->seconds, 3, "interval seconds 1 - $db_type");

  $o->dur(DateTime::Duration->new(years => 7, nanoseconds => 3000));

  is($o->dur->in_units('years'), 7, "interval in_units years 1 - $db_type");
  is($o->dur->in_units('months'), 84, "interval in_units months 1 - $db_type");
  is($o->dur->nanoseconds, 3000, "interval nanoseconds 1 - $db_type");
  is($o->dur->days, 0, "interval days 2 - $db_type");
  is($o->dur->minutes, 0, "interval minutes 2 - $db_type");
  is($o->dur->seconds, 0, "interval seconds 2 - $db_type");

  $o->save;

  $o = MyMySQLObject->new(id => $o->id)->load;

  is($o->dur->in_units('years'), 7, "interval in_units years 2 - $db_type");
  is($o->dur->in_units('months'), 84, "interval in_units months 2 - $db_type");
  is($o->dur->nanoseconds, 3000, "interval nanoseconds 2 - $db_type");
  is($o->dur->days, 0, "interval days 3 - $db_type");
  is($o->dur->minutes, 0, "interval minutes 3 - $db_type");
  is($o->dur->seconds, 0, "interval seconds 3 - $db_type");

  is($o->meta->column('dur')->scale, 6, "interval scale - $db_type");

  is($o->epoch(format => '%Y-%m-%d %H:%M:%S'), '1999-11-30 21:30:00', "epoch 1 - $db_type");

  $o->hiepoch('943997400.123456');
  is($o->hiepoch(format => '%Y-%m-%d %H:%M:%S.%6N'), '1999-11-30 21:30:00.123456', "epoch hires 1 - $db_type");

  $o->epoch('5/6/1980 12:34:56');

  $o->save;

  $o = MyMySQLObject->new(id => $o->id)->load;

  is($o->epoch(format => '%Y-%m-%d %H:%M:%S'), '1980-05-06 12:34:56', "epoch 2 - $db_type");
  is($o->hiepoch(format => '%Y-%m-%d %H:%M:%S.%6N'), '1999-11-30 21:30:00.123456', "epoch hires 2 - $db_type");

  #local $Rose::DB::Object::Debug = 1;  
  $o = MyMySQLObject3->new->save;
  $o = MyMySQLObject3->new(id => $o->id)->load;

  is($o->num, 123, "insert changes only 5 - $db_type");
  is($o->flag, 1, "insert changes only 6 - $db_type");
}

#
# Informix
#

SKIP: foreach my $db_type ('informix')
{
  skip("Informix tests", 73)  unless($HAVE_INFORMIX);

  Rose::DB->default_type($db_type);

  my $o = MyInformixObject->new(name => 'John', 
                                id   => 1,
                                k1   => 1,
                                k2   => undef,
                                k3   => 3);

  ok(ref $o && $o->isa('MyInformixObject'), "new() 1 - $db_type");

  $o->meta->allow_inline_column_values(1);

  $o->flag2('true');
  $o->date_created('current year to fraction(5)');
  $o->last_modified($o->date_created);
  $o->save_col(22);

  if(rand >= 0.5)
  {
    ok($o->save, "save() 1 - $db_type");
  }
  else
  {
    ok($o->insert, "insert() 1 - $db_type");
  }

  ok($o->load, "load() 1 - $db_type");

  $o->name('C' x 50);
  is($o->name, 'C' x 32, "varchar truncation - $db_type");

  $o->name('John');

  $o->code('A');
  is($o->code, 'A     ', "character padding - $db_type");

  $o->code('C' x 50);
  is($o->code, 'C' x 6, "character truncation - $db_type");

  my $ouk;
  ok($ouk = MyInformixObject->new(k1 => 1,
                                  k2 => undef,
                                  k3 => 3)->load, "load() uk 1 - $db_type");

  ok(!$ouk->not_found, "not_found() uk 1 - $db_type");

  is($ouk->id, 1, "load() uk 2 - $db_type");
  is($ouk->name, 'John', "load() uk 3 - $db_type");

  ok($ouk->save, "save() uk 1 - $db_type");

  my $o2 = MyInformixObject->new(id => $o->id);

  ok(ref $o2 && $o2->isa('MyInformixObject'), "new() 2 - $db_type");

  is($o2->bits->to_Bin, '00101', "bits() (bitfield default value) - $db_type");

  ok($o2->load, "load() 2 - $db_type");
  ok(!$o2->not_found, "not_found() 1 - $db_type");

  is($o2->name, $o->name, "load() verify 1 - $db_type");
  is($o2->date_created, $o->date_created, "load() verify 2 - $db_type");
  is($o2->last_modified, $o->last_modified, "load() verify 3 - $db_type");
  is($o2->status, 'active', "load() verify 4 (default value) - $db_type");
  is($o2->flag, 1, "load() verify 5 (default boolean value) - $db_type");
  is($o2->flag2, 1, "load() verify 6 (boolean value) - $db_type");
  is($o2->save_col, 22, "load() verify 7 (aliased column) - $db_type");
  is($o2->start->ymd, '1980-12-24', "load() verify 8 (date value) - $db_type");

  $o2->set_status('foo');
  is($o2->get_status, 'foo', 'get_status()');
  $o2->set_status('active');
  eval { $o2->set_status };
  ok($@, 'set_status()');

  is($o2->bits->to_Bin, '00101', "load() verify 9 (bitfield value) - $db_type");

  my $clone = $o2->clone;
  ok($o2->start eq $clone->start, "clone() 1 - $db_type");
  $clone->start->set(year => '1960');
  ok($o2->start ne $clone->start, "clone() 2 - $db_type");

  $o2->name('John 2');
  $o2->start('5/24/2001');

  sleep(1); # keep the last modified dates from being the same

  $o2->last_modified('current year to second');
  ok($o2->save, "save() 2 - $db_type");
  ok($o2->load, "load() 3 - $db_type");

  is($o2->date_created, $o->date_created, "save() verify 1 - $db_type");
  ok($o2->last_modified ne $o->last_modified, "save() verify 2 - $db_type");
  is($o2->start->ymd, '2001-05-24', "save() verify 3 (date value) - $db_type");

  my $o3 = MyInformixObject->new();

  my $db = $o3->db or die $o3->error;

  ok(ref $db && $db->isa('Rose::DB'), "db() - $db_type");

  is($db->dbh, $o3->dbh, "dbh() - $db_type");

  my $o4 = MyInformixObject->new(id => 999);
  ok(!$o4->load(speculative => 1), "load() nonexistent - $db_type");
  ok($o4->not_found, "not_found() 2 - $db_type");

  $o->nums([ 4, 5, 6 ]);
  $o->names([ qw(a b 3.1) ]);

  ok($o->save, "save() 3 - $db_type");
  ok($o->load, "load() 4 - $db_type");

  is($o->nums->[0], 4, "load() verify 10 (array value) - $db_type");
  is($o->nums->[1], 5, "load() verify 11 (array value) - $db_type");
  is($o->nums->[2], 6, "load() verify 12 (array value) - $db_type");

  $o->nums(7, 8, 9);

  my @a = $o->nums;

  is($a[0], 7, "load() verify 13 (array value) - $db_type");
  is($a[1], 8, "load() verify 14 (array value) - $db_type");
  is($a[2], 9, "load() verify 15 (array value) - $db_type");
  is(@a, 3, "load() verify 16 (array value) - $db_type");

  is($o->names->[0], 'a', "load() verify 10 (set value) - $db_type");
  is($o->names->[1], 'b', "load() verify 11 (set value) - $db_type");
  is($o->names->[2], '3.1', "load() verify 12 (set value) - $db_type");

  $o->names('c', 'd', '4.2');

  @a = $o->names;

  is($a[0], 'c', "load() verify 13 (set value) - $db_type");
  is($a[1], 'd', "load() verify 14 (set value) - $db_type");
  is($a[2], '4.2', "load() verify 15 (set value) - $db_type");
  is(@a, 3, "load() verify 16 (set value) - $db_type");

  ok($o->delete, "delete() - $db_type");

  $o = MyInformixObject->new(name => 'John', id => 9);

  $o->flag2('true');
  $o->date_created('current year to fraction(5)');
  $o->last_modified($o->date_created);
  $o->save_col(22);

  ok($o->save, "save() 4 - $db_type");
  $o->save_col(50);

  ok($o->save, "save() 5 - $db_type");

  $ouk = MyInformixObject->new(save_col => 50);
  ok($ouk->load, "load() aliased unique key - $db_type");

  eval { $o->meta->alias_column(nonesuch => 'foo') };
  ok($@, "alias_column() nonesuch - $db_type");

  # This is okay now
  #eval { $o->meta->alias_column(id => 'foo') };
  #ok($@, "alias_column() primary key - $db_type");

  $o = MyInformixObject->new(id => 777);

  $o->meta->error_mode('fatal');

  $o->dbh->{'PrintError'} = 0;

  eval { $o->load };
  ok($@ && $o->not_found, "load() not found fatal - $db_type");

  $o->id('abc');

  eval { $o->load };
  ok($@ && !$o->not_found, "load() fatal - $db_type");

  eval { $o->save };
  ok($@, "save() fatal - $db_type");

  #$o->meta->error_mode('return');

  $o = MyInformixObject->new(id => 9999); # no such id

  $o->meta->error_mode('fatal');

  eval { $o->load() };
  ok($@, "load() non-speculative implicit - $db_type");  
  ok(!$o->load(speculative => 1), "load() speculative explicit 1 - $db_type");
  eval { $o->load(speculative => 0) };
  ok($@, "load() non-speculative explicit 2 - $db_type");

  $o->meta->default_load_speculative(1);

  ok(!$o->load(), "load() speculative implicit - $db_type");  
  ok(!$o->load(speculative => 1), "load() speculative explicit 2 - $db_type");
  eval { $o->load(speculative => 0) };
  ok($@, "load() non-speculative explicit 2 - $db_type");
}

#
# SQLite
#

SKIP: foreach my $db_type ('sqlite')
{
  skip("SQLite tests", 88)  unless($HAVE_SQLITE);

  Rose::DB->default_type($db_type);

  my $o = MySQLiteObject->new;

  $o->k1(0);

  ok(has_modified_columns($o), "has_modified_columns() zero - $db_type");

  $o->k1(undef);

  ok(has_modified_columns($o), "has_modified_columns() undef - $db_type");

  $o = MySQLiteObject->new(name => 'John',
                           k1   => 0,
                           k2   => undef,
                           k3   => 3);

  ok(get_column_value_modified($o, 'k1'), "zero modification - $db_type");

  $o->k1(1);

  ok(ref $o && $o->isa('MySQLiteObject'), "new() 1 - $db_type");

  $o->flag2('true');
  $o->date_created('now');
  $o->last_modified($o->date_created);
  $o->save_col(22);

  if(rand >= 0.5)
  {
    ok($o->save, "save() 1 - $db_type");
  }
  else
  {
    ok($o->insert, "insert() 1 - $db_type");
  }

  ok($o->load, "load() 1 - $db_type");

  $o->name('C' x 50);
  is($o->name, 'C' x 32, "varchar truncation - $db_type");

  $o->name('John');

  $o->code('A');
  is($o->code, 'A     ', "character padding - $db_type");

  $o->code('C' x 50);
  is($o->code, 'C' x 6, "character truncation - $db_type");

  my $ouk;
  ok($ouk = MySQLiteObject->new(k1 => 1,
                                k2 => undef,
                                k3 => 3)->load, "load() uk 1 - $db_type");

  ok(!$ouk->not_found, "not_found() uk 1 - $db_type");

  is($ouk->id->[0], 1, "load() uk 2 - $db_type");
  is($ouk->name, 'John', "load() uk 3 - $db_type");

  ok($ouk->save, "save() uk 1 - $db_type");

  my $o2 = MySQLiteObject->new(id => $o->id);

  ok(ref $o2 && $o2->isa('MySQLiteObject'), "new() 2 - $db_type");

  is($o2->bits->to_Bin, '00101', "bits() (bitfield default value) - $db_type");

  ok($o2->load, "load() 2 - $db_type");
  ok(!$o2->not_found, "not_found() 1 - $db_type");

  is($o2->name, $o->name, "load() verify 1 - $db_type");
  is($o2->date_created, $o->date_created, "load() verify 2 - $db_type");
  is($o2->last_modified, $o->last_modified, "load() verify 3 - $db_type");
  is($o2->status, 'active', "load() verify 4 (default value) - $db_type");
  is($o2->flag, 1, "load() verify 5 (default boolean value) - $db_type");
  is($o2->flag2, 1, "load() verify 6 (boolean value) - $db_type");
  is($o2->save_col, 22, "load() verify 7 (aliased column) - $db_type");
  is($o2->start->ymd, '1980-12-24', "load() verify 8 (date value) - $db_type");

  $o2->set_status('foo');
  is($o2->get_status, 'foo', 'get_status()');
  $o2->set_status('active');
  eval { $o2->set_status };
  ok($@, 'set_status()');

  is($o2->bits->to_Bin, '00101', "load() verify 9 (bitfield value) - $db_type");

  my $clone = $o2->clone;
  ok($o2->start eq $clone->start, "clone() 1 - $db_type");
  $clone->start->set(year => '1960');
  ok($o2->start ne $clone->start, "clone() 2 - $db_type");

  $o2->name('John 2');
  $o2->start('5/24/2001');

  sleep(1); # keep the last modified dates from being the same

  $o2->last_modified('now');
  ok($o2->save, "save() 2 - $db_type");
  ok($o2->load, "load() 3 - $db_type");

  is($o2->date_created, $o->date_created, "save() verify 1 - $db_type");
  ok($o2->last_modified ne $o->last_modified, "save() verify 2 - $db_type");
  is($o2->start->ymd, '2001-05-24', "save() verify 3 (date value) - $db_type");

  my $o3 = MySQLiteObject->new();

  my $db = $o3->db or die $o3->error;

  ok(ref $db && $db->isa('Rose::DB'), "db() - $db_type");

  is($db->dbh, $o3->dbh, "dbh() - $db_type");

  my $o4 = MySQLiteObject->new(id => 999);
  ok(!$o4->load(speculative => 1), "load() nonexistent - $db_type");
  ok($o4->not_found, "not_found() 2 - $db_type");

  $o->nums([ 4, 5, 6 ]);
  ok($o->save, "save() 3 - $db_type");
  ok($o->load, "load() 4 - $db_type");

  is($o->nums->[0], 4, "load() verify 10 (array value) - $db_type");
  is($o->nums->[1], 5, "load() verify 11 (array value) - $db_type");
  is($o->nums->[2], 6, "load() verify 12 (array value) - $db_type");

  my @a = $o->nums;

  is($a[0], 4, "load() verify 13 (array value) - $db_type");
  is($a[1], 5, "load() verify 14 (array value) - $db_type");
  is($a[2], 6, "load() verify 15 (array value) - $db_type");
  is(@a, 3, "load() verify 16 (array value) - $db_type");

  ok($o->delete, "delete() - $db_type");

  $o = MySQLiteObject->new(name => 'John', id => 9);
  $o->save_col(22);
  ok($o->save, "save() 4 - $db_type");
  $o->save_col(50);
  ok($o->save, "save() 5 - $db_type");

  $ouk = MySQLiteObject->new(save_col => 50);
  ok($ouk->load, "load() aliased unique key - $db_type");

  eval { $o->meta->alias_column(nonesuch => 'foo') };
  ok($@, "alias_column() nonesuch - $db_type");

  # This is okay now
  #eval { $o->meta->alias_column(id => 'foo') };
  #ok($@, "alias_column() primary key - $db_type");

  $o = MySQLiteObject->new(id => 777);

  $o->meta->error_mode('fatal');

  $o->dbh->{'PrintError'} = 0;

  eval { $o->load };
  ok($@ && $o->not_found, "load() not found fatal - $db_type");

  my $old_table = $o->meta->table;

  $o->meta->table('nonesuch');

  eval { $o->load };
  ok($@ && !$o->not_found, "load() fatal - $db_type");

  eval { $o->save };
  ok($@, "save() fatal - $db_type");

  $o->meta->table($old_table);  
  $o->meta->error_mode('return');

  $o = MyMPKSQLiteObject->new(name => 'John');

  ok($o->save, "save() 1 multi-value primary key with generated values - $db_type");

  is($o->k1, 1, "save() verify 1 multi-value primary key with generated values - $db_type");
  is($o->k2, 2, "save() verify 2 multi-value primary key with generated values - $db_type");

  $o = MyMPKSQLiteObject->new(name => 'Alex');

  ok($o->save, "save() 2 multi-value primary key with generated values - $db_type");

  is($o->k1, 3, "save() verify 3 multi-value primary key with generated values - $db_type");
  is($o->k2, 4, "save() verify 4 multi-value primary key with generated values - $db_type");

  $o = MySQLiteObject->new(id => 9999); # no such id

  $o->meta->error_mode('fatal');

  eval { $o->load() };
  ok($@, "load() non-speculative implicit - $db_type");  
  ok(!$o->load(speculative => 1), "load() speculative explicit 1 - $db_type");
  eval { $o->load(speculative => 0) };
  ok($@, "load() non-speculative explicit 2 - $db_type");

  $o->meta->default_load_speculative(1);

  ok(!$o->load(), "load() speculative implicit - $db_type");  
  ok(!$o->load(speculative => 1), "load() speculative explicit 2 - $db_type");
  eval { $o->load(speculative => 0) };
  ok($@, "load() non-speculative explicit 2 - $db_type");

  #local $Rose::DB::Object::Debug = 1;  
  $o = MySQLiteObject3->new->save;
  $o = MySQLiteObject3->new(id => $o->id)->load;

  is($o->num, 123, "insert changes only 1 - $db_type");
  is($o->flag, 2, "insert changes only 2 - $db_type");

  $o = MySQLiteObject3->new(flag => 7)->save;
  $o = MySQLiteObject3->new(id => $o->id)->load;

  is($o->num, 123, "insert changes only 1 - $db_type");
  is($o->flag, 7, "insert changes only 2 - $db_type");

  $o->num(123);

  QUIET:
  {
    local $Rose::DB::Object::Debug = 1;
    ok($o->save(changes_only => 1), "noop update smart 1 - $db_type");
  }

  $o->num(undef);
  $o->save;

  $o->num(0);
  ok(has_modified_columns($o), "zero mod 1 - $db_type");
  $o->save(changes_only => 1);

  $o->num(0);
  ok(!has_modified_columns($o), "zero mod 2 - $db_type");
  $o->save(changes_only => 1);

  $o->num(undef);
  ok(has_modified_columns($o), "undef mod - $db_type");
  $o->save(changes_only => 1);
  $o->load;

  $o->num('');
  ok(has_modified_columns($o), "empty string mod 1 - $db_type");
  $o->save(changes_only => 1);
  $o->load;

  is($o->num, '', "empty string mod 2 - $db_type");

  $o = MySQLiteObject4->new(id => 1)->save;
  $o = MySQLiteObject4->new(id => 1)->load;
  ok($o->save, "noop update pk only 1 - $db_type");

  $o->meta->default_insert_changes_only(0);
  $o->meta->default_update_changes_only(0);

  $o = MySQLiteObject4->new(id => 2)->save;
  $o = MySQLiteObject4->new(id => 2)->load;
  ok($o->save, "noop update pk only 2 - $db_type");
}

BEGIN
{
  #
  # PostgreSQL
  #

  my $dbh;

  eval 
  {
    $dbh = Rose::DB->new('pg_admin')->retain_dbh()
      or die Rose::DB->error;
  };

  if(!$@ && $dbh)
  {
    our $HAVE_PG = 1;

    # Drop existing table and create schema, ignoring errors
    {
      local $dbh->{'RaiseError'} = 0;
      local $dbh->{'PrintError'} = 0;
      $dbh->do('DROP TABLE rose_db_object_test');
      $dbh->do('DROP TABLE rose_db_object_test2');
      $dbh->do('DROP TABLE rose_db_object_test3');
      $dbh->do('DROP TABLE rose_db_object_private.rose_db_object_test');
      $dbh->do('DROP TABLE rose_db_object_private.rose_db_object_test2');
      $dbh->do('DROP TABLE rose_db_object_private.rose_db_object_test3');
      $dbh->do('DROP TABLE rose_db_object_chkpass_test');
      $dbh->do('CREATE SCHEMA rose_db_object_private');
    }

    our $PG_HAS_CHKPASS = pg_has_chkpass();

    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_test
(
  id             SERIAL NOT NULL PRIMARY KEY,
  k1             INT,
  k2             INT,
  k3             INT,
  @{[ $PG_HAS_CHKPASS ? 'passwd CHKPASS,' : '' ]}
  name           VARCHAR(32) NOT NULL,
  code           CHAR(6),
  flag           BOOLEAN NOT NULL,
  flag2          BOOLEAN,
  status         VARCHAR(32) DEFAULT 'active',
  bitz           BIT(5) NOT NULL DEFAULT B'00101',
  decs           DECIMAL(10,2),
  start          DATE,
  save           INT,
  nums           INT[],
  dur            INTERVAL(6) DEFAULT '2 months 5 days 3 seconds',
  epoch          INT DEFAULT 943997400,
  hiepoch        DECIMAL(16,6),
  bint1          BIGINT DEFAULT 9223372036854775800,
  bint2          BIGINT DEFAULT -9223372036854775800,
  bint3          BIGINT,
  bint4          BIGINT,
  last_modified  TIMESTAMP,
  date_created   TIMESTAMP,

  UNIQUE(k1, k2, k3)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_private.rose_db_object_test
(
  id             SERIAL NOT NULL PRIMARY KEY,
  k1             INT,
  k2             INT,
  k3             INT,
  @{[ $PG_HAS_CHKPASS ? 'passwd CHKPASS,' : '' ]}
  name           VARCHAR(32) NOT NULL,
  code           CHAR(6),
  flag           BOOLEAN NOT NULL,
  flag2          BOOLEAN,
  status         VARCHAR(32) DEFAULT 'active',
  bitz           BIT(5) NOT NULL DEFAULT B'00101',
  decs           DECIMAL(10,2),
  start          DATE,
  save           INT,
  nums           INT[],
  dur            INTERVAL(6) DEFAULT '2 months 5 days 3 seconds',
  epoch          INT DEFAULT 943997400,
  hiepoch        DECIMAL(16,6),
  bint1          BIGINT DEFAULT 9223372036854775800,
  bint2          BIGINT DEFAULT -9223372036854775800,
  bint3          BIGINT,
  bint4          BIGINT,
  last_modified  TIMESTAMP,
  date_created   TIMESTAMP,

  UNIQUE(k1, k2, k3)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_test2
(
  id     SERIAL PRIMARY KEY,
  num    INT DEFAULT 123,
  flag   INT DEFAULT 1,
  f2     INT
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_private.rose_db_object_test2
(
  id     SERIAL PRIMARY KEY,
  num    INT DEFAULT 123,
  flag   INT DEFAULT 1,
  f2     INT
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_test3
(
  id     SERIAL PRIMARY KEY,
  num    INT DEFAULT 123,
  flag   INT DEFAULT 1,
  dt     TIMESTAMP
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_private.rose_db_object_test3
(
  id     SERIAL PRIMARY KEY,
  num    INT DEFAULT 123,
  flag   INT DEFAULT 1,
  dt     TIMESTAMP
)
EOF

    $dbh->disconnect;

    # Create test subclass

    package MyPgObject;

    use Rose::DB::Object::Helpers qw(clone);

    our @ISA = qw(Rose::DB::Object);

    sub init_db { Rose::DB->new('pg') }

    MyPgObject->meta->default_update_changes_only(1);

    MyPgObject->meta->table('rose_db_object_test');

    MyPgObject->meta->columns
    (
      name     => { type => 'varchar', length => 32, overflow => 'truncate' },
      code     => { type => 'char', length => 6, overflow => 'truncate' },
      id       => { primary_key => 1, not_null => 1 },
      k1       => { type => 'int' },
      k2       => { type => 'int', lazy => 1 },
      k3       => { type => 'int' },
      ($PG_HAS_CHKPASS ? (passwd => { type => 'chkpass', alias => 'password' }) : ()),
      flag     => { type => 'boolean', default => 1 },
      flag2    => { type => 'boolean' },
      status   => { default => 'active', add_methods => [ qw(get set) ] },
      start    => { type => 'date', default => '12/24/1980', lazy => 1 },
      save     => { type => 'scalar' },
      nums     => { type => 'array' },
      bitz     => { type => 'bitfield', bits => 5, default => 101, alias => 'bits' },
      decs     => { type => 'decimal', precision => 10, scale => 2 },
      dur      => { type => 'interval', scale => 6, default => '2 months 5 days 3 seconds' },
      epoch    => { type => 'epoch', default => '11/30/1999 9:30pm' },
      hiepoch  => { type => 'epoch hires', default => '1144004926.123456' },
      bint1    => { type => 'bigint', default => '9223372036854775800' },
      bint2    => { type => 'bigint', default => '-9223372036854775800' },
      bint3    => { type => 'bigint', with_init => 1, check_in => [ '9223372036854775000', 5 ] },
      bint4    => { type => 'bigint', with_init => 1 },
      #last_modified => { type => 'timestamp' },
      date_created  => { type => 'timestamp' },
    );

    sub init_bint4 { undef }

    MyPgObject->meta->add_unique_key('save');

    MyPgObject->meta->add_unique_key([ qw(k1 k2 k3) ]);

    MyPgObject->meta->add_columns(
      Rose::DB::Object::Metadata::Column::Timestamp->new(
        name => 'last_modified'));

    eval { MyPgObject->meta->initialize };
    Test::More::ok($@, 'meta->initialize() reserved method');

    MyPgObject->meta->alias_column(save => 'save_col');

    eval { MyPgObject->meta->initialize };
    Test::More::ok($@, 'meta->initialize() no override');

    MyPgObject->meta->initialize(preserve_existing => 1);

    Test::More::is(MyPgObject->meta->column('id')->is_primary_key_member, 1, 'is_primary_key_member - pg');
    Test::More::is(MyPgObject->meta->column('id')->primary_key_position, 1, 'primary_key_position 1 - pg');
    Test::More::ok(!defined MyPgObject->meta->column('k1')->primary_key_position, 'primary_key_position 2 - pg');
    MyPgObject->meta->column('k1')->primary_key_position(7);
    Test::More::ok(!defined MyPgObject->meta->column('k1')->primary_key_position, 'primary_key_position 3 - pg');

    sub init_bint3 { '9223372036854775000' }

    package MyPgObject2;
    our @ISA = qw(Rose::DB::Object);
    sub init_db { Rose::DB->new('pg') }
    MyPgObject2->meta->setup
    (
      table   => 'rose_db_object_test2',
      columns =>
      [
        id   => { type => 'serial', primary_key => 1 },
        num  => { type => 'int' }, # default is 123
        flag => { type => 'int', default => 2 },
        f2   => { type => 'int' },
      ],
    );

    package MyPgObject3;
    our @ISA = qw(Rose::DB::Object);
    sub init_db { Rose::DB->new('pg') }
    MyPgObject3->meta->setup
    (
      table   => 'rose_db_object_test3',
      columns =>
      [
        id   => { type => 'serial', primary_key => 1 },
        num  => { type => 'int' }, # default is 123
        flag => { type => 'int' }, # default is 1
        dt   => { type => 'timestamp' },
      ],
      default_insert_changes_only => 1,
    );
  }

  #
  # MySQL
  #

  my $db_version;

  eval
  {
    my $db = Rose::DB->new('mysql_admin');
    $dbh = $db->retain_dbh() or die Rose::DB->error;
    $db_version = $db->database_version;
  };

  if(!$@ && $dbh)
  {
    our $HAVE_MYSQL = 1;

    # Drop existing table and create schema, ignoring errors
    {
      local $dbh->{'RaiseError'} = 0;
      local $dbh->{'PrintError'} = 0;
      $dbh->do('DROP TABLE rose_db_object_test');
      $dbh->do('DROP TABLE rose_db_object_test2');
      $dbh->do('DROP TABLE rose_db_object_test3');
    }

    # MySQL 5.0.3 or later has a completely stupid "native" BIT type
    my $bit_col1 = 
      ($db_version >= 5_000_003) ?
        q(bitz  BIT(5) NOT NULL DEFAULT B'00101') :
        q(bitz  BIT(5) NOT NULL DEFAULT '00101');

    my $bit_col2 = 
      ($db_version >= 5_000_003) ?
        q(bitz2  BIT(2) NOT NULL DEFAULT B'00') :
        q(bitz2  BIT(2) NOT NULL DEFAULT '0');

    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_test
(
  id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  k1             INT,
  k2             INT,
  k3             INT,
  name           VARCHAR(32) NOT NULL,
  code           CHAR(6),
  flag           TINYINT(1) NOT NULL,
  flag2          TINYINT(1),
  status         VARCHAR(32) DEFAULT 'active',
  $bit_col1,
  $bit_col2,
  bitz3          BIT(4),
  decs           FLOAT(10,2),
  nums           VARCHAR(255),
  start          DATE,
  save           INT,
  enums          ENUM('foo', 'bar', 'baz') DEFAULT 'foo',
  ndate          DATE NOT NULL DEFAULT '0000-00-00',
  dur            VARCHAR(255) DEFAULT '2 months 5 days 3 seconds',
  epoch          INT DEFAULT 943997400,
  hiepoch        DECIMAL(16,6),
  last_modified  TIMESTAMP,
  date_created   TIMESTAMP,

  UNIQUE(k1, k2, k3)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_test2
(
  k1             INT NOT NULL,
  k2             INT NOT NULL,
  name           VARCHAR(32),

  UNIQUE(k1, k2)
)
EOF
    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_test3
(
  id     INT AUTO_INCREMENT PRIMARY KEY,
  num    INT DEFAULT 123,
  flag   INT DEFAULT 1
)
EOF

    $dbh->disconnect;

    # Create test subclass

    package MyMySQLObject;

    use Rose::DB::Object::Helpers qw(clone);

    our @ISA = qw(Rose::DB::Object);

    sub init_db { Rose::DB->new('mysql') }

    MyMySQLObject->meta->allow_inline_column_values(1);

    MyMySQLObject->meta->default_update_changes_only(1);

    MyMySQLObject->meta->table('rose_db_object_test');

    MyMySQLObject->meta->columns
    (
      name     => { type => 'varchar', length => 32 },
      code     => { type => 'char', length => 6 },
      id       => { primary_key => 1, not_null => 1 },
      k1       => { type => 'int' },
      k2       => { type => 'int', lazy => 1 },
      k3       => { type => 'int' },
      flag     => { type => 'boolean', default => 1 },
      flag2    => { type => 'boolean' },
      status   => { default => 'active', methods => [ qw(get_set get set) ] },
      start    => { type => 'date', default => '12/24/1980', lazy => 1 },
      ndate    => { type => 'date', not_null => 1, default => '0000-00-00' },
      save     => { type => 'scalar' },
      nums     => { type => 'array' },
      enums    => { type => 'enum', values => [ qw(foo bar baz) ], default => 'foo' },
      bitz     => { type => 'bitfield', bits => 5, default => 101, alias => 'bits' },
      bitz2    => { type => 'bits', bits => 2, default => '0' },
      bitz3    => { type => 'bits', bits => 4 },
      decs     => { type => 'decimal', precision => 10, scale => 2 },
      dur      => { type => 'interval', scale => 6, default => '2 months 5 days 3 seconds' },
      epoch    => { type => 'epoch', default => '11/30/1999 9:30pm' },
      hiepoch  => { type => 'epoch hires', default => '1144004926.123456' },
      last_modified => { type => 'timestamp' },
      date_created  => { type => 'timestamp' },
    );

    eval { MyMySQLObject->meta->initialize };
    Test::More::ok($@, 'meta->initialize() reserved method');

    MyMySQLObject->meta->alias_column(save => 'save_col');

    eval { MyMySQLObject->meta->initialize };
    Test::More::ok($@, 'meta->initialize() no override');

    MyMySQLObject->meta->add_unique_key('save');
    MyMySQLObject->meta->add_unique_key([ qw(k1 k2 k3) ]);

    MyMySQLObject->meta->initialize(preserve_existing => 1);

    Test::More::is(MyMySQLObject->meta->column('id')->is_primary_key_member, 1, 'is_primary_key_member - mysql');
    Test::More::is(MyMySQLObject->meta->column('id')->primary_key_position, 1, 'primary_key_position 1 - mysql');
    Test::More::ok(!defined MyMySQLObject->meta->column('k1')->primary_key_position, 'primary_key_position 2 - mysql');
    MyMySQLObject->meta->column('k1')->primary_key_position(7);
    Test::More::ok(!defined MyMySQLObject->meta->column('k1')->primary_key_position, 'primary_key_position 3 - mysql');

    package MyMPKMySQLObject;

    use Rose::DB::Object;

    our @ISA = qw(Rose::DB::Object);

    sub init_db { Rose::DB->new('mysql') }

    MyMPKMySQLObject->meta->default_update_changes_only(1);

    MyMPKMySQLObject->meta->table('rose_db_object_test2');

    MyMPKMySQLObject->meta->columns
    (
      k1          => { type => 'int', not_null => 1 },
      k2          => { type => 'int', not_null => 1 },
      name        => { type => 'varchar', length => 32 },
    );

    MyMPKMySQLObject->meta->primary_key_columns('k1', 'k2');

    my $i = 1;

    MyMPKMySQLObject->meta->setup
    (
      primary_key_generator => sub
      {
        my($meta, $db) = @_;

        my $k1 = $i++;
        my $k2 = $i++;

        return $k1, $k2;
      },
    );

    package MyMySQLObject3;
    our @ISA = qw(Rose::DB::Object);
    sub init_db { Rose::DB->new('mysql') }
    MyMySQLObject3->meta->setup
    (
      table   => 'rose_db_object_test3',
      columns =>
      [
        id   => { type => 'serial', primary_key => 1 },
        num  => { type => 'int' }, # default is 123
        flag => { type => 'int' }, # default is 1
      ],
      default_insert_changes_only => 1,
    );
  }

  #
  # Informix
  #

  eval
  {
    $dbh = Rose::DB->new('informix_admin')->retain_dbh()
      or die Rose::DB->error;
  };

  if(!$@ && $dbh)
  {
    our $HAVE_INFORMIX = 1;

    # Drop existing table and create schema, ignoring errors
    {
      local $dbh->{'RaiseError'} = 0;
      local $dbh->{'PrintError'} = 0;
      $dbh->do('DROP TABLE rose_db_object_test');
    }

    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_test
(
  id             SERIAL NOT NULL PRIMARY KEY,
  k1             INT,
  k2             INT,
  k3             INT,
  name           VARCHAR(32) NOT NULL,
  code           CHAR(6),
  flag           BOOLEAN NOT NULL,
  flag2          BOOLEAN,
  status         VARCHAR(32) DEFAULT 'active',
  bitz           VARCHAR(5) DEFAULT '00101' NOT NULL,
  decs           DECIMAL(10,2),
  nums           VARCHAR(255),
  start          DATE,
  save           INT,
  names          SET(VARCHAR(64) NOT NULL),
  last_modified  DATETIME YEAR TO FRACTION(5),
  date_created   DATETIME YEAR TO FRACTION(5)
)
EOF

    $dbh->disconnect;

    # Create test subclass

    package MyInformixObject;

    use Rose::DB::Object::Helpers qw(clone);

    our @ISA = qw(Rose::DB::Object);

    sub init_db { Rose::DB->new('informix') }

    MyInformixObject->meta->allow_inline_column_values(1);

    MyInformixObject->meta->default_update_changes_only(1);

    MyInformixObject->meta->table('rose_db_object_test');

    MyInformixObject->meta->columns
    (
      name     => { type => 'varchar', length => 32, overflow => 'truncate' },
      code     => { type => 'char', length => 6, overflow => 'truncate' },
      id       => { type => 'serial', primary_key => 1, not_null => 1 },
      k1       => { type => 'int' },
      k2       => { type => 'int', lazy => 1 },
      k3       => { type => 'int' },
      flag     => { type => 'boolean', default => 1 },
      flag2    => { type => 'boolean' },
      status   => { default => 'active', add_methods => [ qw(get set) ] },
      start    => { type => 'date', default => '12/24/1980', lazy => 1 },
      save     => { type => 'scalar' },
      nums     => { type => 'array' },
      bitz     => { type => 'bitfield', bits => 5, default => 101, alias => 'bits' },
      decs     => { type => 'decimal', precision => 10, scale => 2 },
      names    => { type => 'set' },
      last_modified => { type => 'timestamp' },
      date_created  => { type => 'datetime year to fraction(5)' },
    );

    eval { MyInformixObject->meta->initialize };
    Test::More::ok($@, 'meta->initialize() reserved method');

    MyInformixObject->meta->prepare_options({ix_CursorWithHold => 1});    

    MyInformixObject->meta->alias_column(save => 'save_col');

    eval { MyInformixObject->meta->initialize };
    Test::More::ok($@, 'meta->initialize() no override');

    MyInformixObject->meta->add_unique_key('save');
    MyInformixObject->meta->add_unique_key([ qw(k1 k2 k3) ]);

    MyInformixObject->meta->initialize(preserve_existing => 1);

    Test::More::is(MyInformixObject->meta->column('id')->is_primary_key_member, 1, 'is_primary_key_member - informix');
    Test::More::is(MyInformixObject->meta->column('id')->primary_key_position, 1, 'primary_key_position 1 - informix');
    Test::More::ok(!defined MyInformixObject->meta->column('k1')->primary_key_position, 'primary_key_position 2 - informix');
    MyInformixObject->meta->column('k1')->primary_key_position(7);
    Test::More::ok(!defined MyInformixObject->meta->column('k1')->primary_key_position, 'primary_key_position 3 - informix');
  }

  #
  # SQLite
  #

  eval 
  {
    $dbh = Rose::DB->new('sqlite_admin')->retain_dbh()
      or die Rose::DB->error;
  };

  if(!$@ && $dbh)
  {
    our $HAVE_SQLITE = 1;

    # Drop existing table and create schema, ignoring errors
    {
      local $dbh->{'RaiseError'} = 0;
      local $dbh->{'PrintError'} = 0;
      $dbh->do('DROP TABLE rose_db_object_test');
      $dbh->do('DROP TABLE rose_db_object_test2');
      $dbh->do('DROP TABLE rose_db_object_test3');
      $dbh->do('DROP TABLE rose_db_object_test4');
    }

    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_test
(
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  k1             INT,
  k2             INT,
  k3             INT,
  name           VARCHAR(32) NOT NULL,
  code           CHAR(6),
  flag           BOOLEAN NOT NULL,
  flag2          BOOLEAN,
  status         VARCHAR(32) DEFAULT 'active',
  bitz           VARCHAR(5) DEFAULT '00101' NOT NULL,
  decs           DECIMAL(10,2),
  start          DATE,
  save           INT,
  nums           VARCHAR(255),
  nonmod         VARCHAR(255) DEFAULT 'defmod',
  last_modified  TIMESTAMP,
  date_created   TIMESTAMP,

  UNIQUE(k1, k2, k3)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_test2
(
  k1             INT NOT NULL,
  k2             INT NOT NULL,
  name           VARCHAR(32),

  UNIQUE(k1, k2)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_test3
(
  id     INTEGER PRIMARY KEY AUTOINCREMENT,
  num    INT DEFAULT 123,
  flag   INT DEFAULT 1
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE rose_db_object_test4
(
  id INTEGER PRIMARY KEY
)
EOF

    $dbh->disconnect;

    # Create test subclass

    package MySQLiteObject;

    use Rose::DB::Object::Helpers qw(clone);

    our @ISA = qw(Rose::DB::Object);

    sub init_db { Rose::DB->new('sqlite') }

    MySQLiteObject->meta->default_update_changes_only(1);

    MySQLiteObject->meta->table('rose_db_object_test');

    MySQLiteObject->meta->columns
    (
      name     => { type => 'varchar', length => 32, overflow => 'truncate' },
      code     => { type => 'char', length => 6, overflow => 'truncate' },
      id       => { primary_key => 1, not_null => 1 },
      k1       => { type => 'int' },
      k2       => { type => 'int', lazy => 1 },
      k3       => { type => 'int' },
      flag     => { type => 'boolean', default => 1 },
      flag2    => { type => 'boolean' },
      status   => { default => 'active', add_methods => [ qw(get set) ] },
      start    => { type => 'date', default => '12/24/1980', lazy => 1 },
      save     => { type => 'scalar' },
      nums     => { type => 'array' },
      nonmod   => { type => 'varchar', length => 255 },
      bitz     => { type => 'bitfield', bits => 5, default => 101, alias => 'bits' },
      decs     => { type => 'decimal', precision => 10, scale => 2 },
      #last_modified => { type => 'timestamp' },
      date_created  => { type => 'scalar' },
    );

    MySQLiteObject->meta->replace_column(date_created => { type => 'timestamp' });

    MySQLiteObject->meta->add_unique_key('save');

    MySQLiteObject->meta->add_unique_key([ qw(k1 k2 k3) ]);

    MySQLiteObject->meta->add_columns(
      Rose::DB::Object::Metadata::Column::Timestamp->new(
        name => 'last_modified'));

    MySQLiteObject->meta->column('id')->add_trigger(inflate => sub { defined $_[1] ? [ $_[1] ] : undef });
    MySQLiteObject->meta->column('id')->add_trigger(deflate => sub { ref $_[1] ? @{$_[1]}  : $_[1] });

    my $pre_inited = 0;
    MySQLiteObject->meta->pre_init_hook(sub { $pre_inited++ });

    eval { MySQLiteObject->meta->initialize };
    Test::More::ok($@, 'meta->initialize() reserved method');
    Test::More::is($pre_inited, 1, 'meta->pre_init_hook()');

    MySQLiteObject->meta->alias_column(save => 'save_col');

    eval { MySQLiteObject->meta->initialize };
    Test::More::ok($@, 'meta->initialize() no override');

    MySQLiteObject->meta->initialize(preserve_existing => 1);

    Test::More::is(MySQLiteObject->meta->column('id')->is_primary_key_member, 1, 'is_primary_key_member - sqlite');
    Test::More::is(MySQLiteObject->meta->column('id')->primary_key_position, 1, 'primary_key_position 1 - sqlite');
    Test::More::ok(!defined MySQLiteObject->meta->column('k1')->primary_key_position, 'primary_key_position 2 - sqlite');
    MySQLiteObject->meta->column('k1')->primary_key_position(7);
    Test::More::ok(!defined MySQLiteObject->meta->column('k1')->primary_key_position, 'primary_key_position 3 - sqlite');

    package MyMPKSQLiteObject;

    use Rose::DB::Object;

    our @ISA = qw(Rose::DB::Object);

    sub init_db { Rose::DB->new('sqlite') }

    MyMPKSQLiteObject->meta->default_update_changes_only(1);

    MyMPKSQLiteObject->meta->table('rose_db_object_test2');

    MyMPKSQLiteObject->meta->columns
    (
      k1          => { type => 'int', not_null => 1 },
      k2          => { type => 'int', not_null => 1 },
      name        => { type => 'varchar', length => 32 },
    );

    MyMPKSQLiteObject->meta->primary_key_columns('k1', 'k2');

    MyMPKSQLiteObject->meta->initialize;

    my $i = 1;

    MyMPKSQLiteObject->meta->primary_key_generator(sub
    {
      my($meta, $db) = @_;

      my $k1 = $i++;
      my $k2 = $i++;

      return $k1, $k2;
    });

    package MySQLiteObject3;
    our @ISA = qw(Rose::DB::Object);
    sub init_db { Rose::DB->new('sqlite') }
    MySQLiteObject3->meta->setup
    (
      table   => 'rose_db_object_test3',
      columns =>
      [
        id   => { type => 'serial', primary_key => 1 },
        num  => { type => 'int', smart_modification => 1 }, # default is 123
        flag => { type => 'int', default => 2 },
      ],
      default_insert_changes_only => 1,
      default_update_changes_only => 1,
    );

    package MySQLiteObject4;
    our @ISA = qw(Rose::DB::Object);
    sub init_db { Rose::DB->new('sqlite') }
    MySQLiteObject4->meta->setup
    (
      table   => 'rose_db_object_test4',
      columns =>
      [
        id   => { type => 'int', primary_key => 1 },
      ],
      default_insert_changes_only => 1,
      default_update_changes_only => 1,
    );
  }
}

END
{
  # Delete test table

  if($HAVE_PG)
  {
    # PostgreSQL
    my $dbh = Rose::DB->new('pg_admin')->retain_dbh()
      or die Rose::DB->error;

    $dbh->do('DROP TABLE rose_db_object_test');
    $dbh->do('DROP TABLE rose_db_object_private.rose_db_object_test');
    $dbh->do('DROP TABLE rose_db_object_test2');
    $dbh->do('DROP TABLE rose_db_object_test3');
    $dbh->do('DROP TABLE rose_db_object_private.rose_db_object_test2');
    $dbh->do('DROP TABLE rose_db_object_private.rose_db_object_test3');
    $dbh->do('DROP SCHEMA rose_db_object_private CASCADE');    

    $dbh->disconnect;
  }

  if($HAVE_MYSQL)
  {
    # MySQL
    my $dbh = Rose::DB->new('mysql_admin')->retain_dbh()
      or die Rose::DB->error;

    $dbh->do('DROP TABLE rose_db_object_test');
    $dbh->do('DROP TABLE rose_db_object_test2');
    $dbh->do('DROP TABLE rose_db_object_test3');

    $dbh->disconnect;
  }

  if($HAVE_INFORMIX)
  {
    # Informix
    my $dbh = Rose::DB->new('informix_admin')->retain_dbh()
      or die Rose::DB->error;

    $dbh->do('DROP TABLE rose_db_object_test');

    $dbh->disconnect;
  }

  if($HAVE_SQLITE)
  {
    # SQLite
    my $dbh = Rose::DB->new('sqlite_admin')->retain_dbh()
      or die Rose::DB->error;

    $dbh->do('DROP TABLE rose_db_object_test');
    $dbh->do('DROP TABLE rose_db_object_test2');
    $dbh->do('DROP TABLE rose_db_object_test3');
    $dbh->do('DROP TABLE rose_db_object_test4');

    $dbh->disconnect;
  }
}
	Global
`s`	Focus search bar
`?`	Bring up this help dialog
	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)
	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse
	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)