#!/usr/bin/perl -w

use strict;

use Test::More tests => 1 + (6 * 36) + 9;

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

our %Have;

our @Tables = qw(vendors products prices colors products_colors);
our $Include_Tables = join('|', @Tables, 'no_pk_test2?');

our %Reserved_Words;

#
# Tests
#

FOO:
{
  package MyCM;

  @MyCM::ISA = qw(Rose::DB::Object::ConventionManager);

  sub auto_foreign_key_name 
  {
    $JCS::Called_Custom_CM{$_[0]->parent->class}++;
    shift->SUPER::auto_foreign_key_name(@_);
  }
}

my $i = 1;

foreach my $db_type (qw(mysql pg pg_with_schema informix sqlite oracle))
{
  SKIP:
  {
    unless($Have{$db_type})
    {
      skip("$db_type tests", 36 + scalar @{$Reserved_Words{$db_type} ||= []});
    }
  }

  next  unless($Have{$db_type});

  $i++;

  Rose::DB->default_type($db_type);
  Rose::DB::Object::Metadata->unregister_all_classes;

  my $class_prefix = ucfirst($db_type eq 'pg_with_schema' ? 'pgws' : $db_type);

  #$Rose::DB::Object::Metadata::Debug = 1;

  %JCS::Called_Custom_CM = ();

  my $pre_init_hook = 0;

  my $db = Rose::DB->new;
  my $loader = 
    Rose::DB::Object::Loader->new(
      db              => $db,
      class_prefix    => $class_prefix,
      ($db_type eq 'mysql' ? (require_primary_key => 0) : ()),
      pre_init_hook   => sub { $pre_init_hook++ });

  my %extra_loader_args;

  if($db_type eq 'sqlite')
  {
    $loader->warn_on_missing_primary_key(0);
    $loader->warn_on_missing_pk(1);
  }
  elsif($db_type eq 'pg')
  {
    $loader->include_predicated_unique_indexes(1);
  }
  elsif($db_type eq 'mysql')
  {
    $loader->warn_on_missing_pk(0);
    $loader->warn_on_missing_primary_key(1);
    $extra_loader_args{'warn_on_missing_pk'} = undef;
    $extra_loader_args{'warn_on_missing_primary_key'} = undef;
  }

  $loader->convention_manager($i % 2 ? 'MyCM' : MyCM->new);

  my @classes;

  my $i = 0;

  # Test aliased parameter conflicts
  foreach my $a (0, 1, undef)
  {
    foreach my $b (0, 1, undef)
    {
      if(($a || 0) != ($b || 0))
      {
        $i++;

        eval
        {
          $loader->make_classes(warn_on_missing_pk => $a,
                                warn_on_missing_primary_key => $b);
        };

        ok($@, "warn_on_missing_pk conflict $i - $db_type");
      }      
    }
  }

  CATCH_WARNINGS:
  {
    my $warnings;
    local $SIG{'__WARN__'} = sub { $warnings .= "@_\n" };
    @classes = $loader->make_classes(include_tables => $Include_Tables . 
                                     ($db_type eq 'mysql' ? '|read' : ''),
                                     %extra_loader_args);

    #foreach my $class (@classes)
    #{
    #  next unless($class->isa('Rose::DB::Object'));
    #  print $class->meta->perl_class_definition, "\n";
    #}

    if($db_type eq 'sqlite')
    {
      ok($warnings =~ /\QWarning: table 'no_pk_test' has no primary key defined.  Skipping./,
         "warn_on_missing_primary_key - $db_type");
    }
    else
    {
      is($warnings, undef, "warn_on_missing_primary_key - $db_type");
    }
  }

  ok(scalar keys %JCS::Called_Custom_CM >= 3, "custom convention manager - $db_type");
  ok($pre_init_hook > 0, "pre_init_hook - $db_type");

  if($db_type eq 'informix')
  {
    foreach my $class (@classes)
    {
      next  unless($class->isa('Rose::DB::Object'));
      $class->meta->allow_inline_column_values(1);

      if($class->meta->column('release_day'))
      {
        is($class->meta->column('release_day')->type, 'datetime year to month', 
           "datetime year to month - $db_type");
      }
    }
  }
  else
  {
    ok(1, "skip datetime year to month - $db_type");
  }

  if(defined Rose::DB->new->schema)
  {
    ok(!scalar(grep { /NoPk2/i } @classes), "pk classes only - $db_type");
  }
  else
  {
    if($db_type eq 'mysql')
    {
      ok(1, "pk classes - $db_type");
    }
    else
    {
      ok(!scalar(grep { /NoPk\b/i } @classes), "pk classes only - $db_type");
    }
  }

  my $product_class     = $class_prefix . '::Product';
  my $price_class       = $class_prefix . '::Price';
  my $map_manager_class = $class_prefix . '::ProductsColor::Manager';

  ##
  ## Run tests
  ##

  if($db_type =~ /^(?:mysql|pg|sqlite)$/)
  {
    my $serial =
      ($db_type ne 'mysql' || $db->dbh->{'Driver'}{'Version'} >= 4.002) ? 
      'serial' : 'integer';

    is($product_class->meta->column('id')->type, $serial, "serial column - $db_type");
  }
  else
  {
    SKIP: { skip("serial coercion test for $db_type", 1) }
  }

  if($db_type eq 'pg')
  {
    my $uk = $product_class->meta->unique_key_by_name('products_uk_test');
    ok($uk && $uk->has_predicate, "include unique index with predicate - $db_type");
  }
  elsif($db_type eq 'pg_with_schema')
  {
    my $uk = $product_class->meta->unique_key_by_name('products_uk_test');
    ok(!$uk, "skip unique index with predicate - $db_type");
  }
  else
  {
    SKIP: { skip("unique index with predicate for $db_type", 1) }
  }

  if($db_type eq 'pg')
  {
    is($product_class->meta->column('release_date')->type, 'timestamp',
      "timestamp - $db_type");

    is($product_class->meta->column('release_date_tz')->type, 'timestamp with time zone',
      "timestamp with time zone - $db_type");
  }
  else
  {
    SKIP: { skip("timestamp with time zone tests for $db_type", 2) }
  }

  if($db_type eq 'mysql' && $db->dbh->{'Driver'}{'Version'} >= 4.002)
  {
    is($price_class->meta->column('id')->type, 'bigserial', "bigserial column - $db_type");
  }
  else
  {
    SKIP: { skip("bigserial test for $db_type", 1) }
  }

  if($db_type eq 'informix' || $db_type eq 'oracle')
  {
    SKIP: { skip("count distinct multi-pk doesn't work in \u$db_type yet", 1) }
  }
  else
  {
    my $count = $map_manager_class->get_objects_count(require_objects => [ 'color' ]);
    is($count, 0, "count distinct multi-pk - $db_type");
  }

  my $p = $product_class->new(name => "Sled $i");

  if($p->can('release_day'))
  {
    $p->release_day('2001-02');
    die "datetime year to month not truncated"  unless($p->release_day->day == 1);
    $p->release_day('2001-02-05');
    die "datetime year to month not truncated"  unless($p->release_day->day == 1);
  }

  # Check reserved methods
  foreach my $word (@{$Reserved_Words{$db_type} ||= []})
  {
    ok($p->$word(int(rand(10)) + 1), "reserved word: $word - $db_type");
  }

  is($p->db->class, 'Rose::DB', "db 1 - $db_type");

  if($db_type =~ /^pg/)
  {
    ok($p->can('tee_time') && $p->can('tee_time5'), "time methods - $db_type");
    is($p->meta->column('tee_time5')->scale, 5, "time precision check 1 - $db_type");
    is($p->meta->column('tee_time')->scale || 0, 0, "time precision check 2 - $db_type");
    my $t = $p->tee_time5->as_string;
    $t =~ s/0+$//;
    is($p->tee_time5->as_string, '12:34:56.12345', "time default 1 - $db_type");
    $t = $p->meta->column('tee_time5')->default;
    $t =~ s/0+$//;
    is($t, '12:34:56.12345', "time default 2 - $db_type");
    is($price_class->meta->column('mprice')->length, undef, "money 1 - $db_type");
  }
  elsif($db_type eq 'informix')
  {
    ok(!$p->can('tee_time') && !$p->can('tee_time5'), "time methods - $db_type");
    ok(!$p->meta->column('tee_time5'), "time precision check 1 - $db_type");
    ok(!$p->meta->column('tee_time'), "time precision check 2 - $db_type");
    is($p->meta->column('bint1')->type, 'bigint', "bigint 1 - $db_type");
    ok($p->bint1 =~ /^\+?9223372036854775800$/, "bigint 2 - $db_type");
    SKIP: { skip("money tests - $db_type", 1) }
  }
  else
  {
    ok(!$p->can('tee_time') && !$p->can('tee_time5'), "time methods - $db_type");
    ok(!$p->meta->column('tee_time5'), "time precision check 1 - $db_type");
    ok(!$p->meta->column('tee_time'), "time precision check 2 - $db_type");
    ok(1, "time default 1 - $db_type");
    ok(1, "time default 2 - $db_type");
    SKIP: { skip("money tests - $db_type", 1) }
  }

  OBJECT_CLASS:
  {
    no strict 'refs';
    ok(${"${product_class}::ISA"}[0] =~ /^${class_prefix}::DB::Object::AutoBase\d+$/, "base class 1 - $db_type");
  }

  $p->vendor(name => "Acme $i");

  $p->prices({ price => 1.25, region => 'US' },
             { price => 4.25, region => 'UK' });

  $p->colors({ name => 'red'   }, 
             { name => 'green' });

  $p->save;

  $p = $product_class->new(id => $p->id)->load;
  is($p->vendor->name, "Acme $i", "vendor 1 - $db_type");


  my @prices = sort { $a->price <=> $b->price } $p->prices;

  is(scalar @prices, 2, "prices 1 - $db_type");
  is($prices[0]->price, 1.25, "prices 2 - $db_type");
  is($prices[1]->price, 4.25, "prices 3 - $db_type");

  my @colors = sort { $a->name cmp $b->name } $p->colors;

  is(scalar @colors, 2, "colors 1 - $db_type");
  is($colors[0]->name, 'green', "colors 2 - $db_type");
  is($colors[1]->name, 'red', "colors 3 - $db_type");

  my $mgr_class = $class_prefix . '::Product::Manager';

  #local $Rose::DB::Object::Manager::Debug = 1;
  #$DB::single = 1;

  my $prods = $mgr_class->get_products(query => [ id => $p->id ]);

  is(ref $prods, 'ARRAY', "get_products 1 - $db_type");
  is(@$prods, 1, "get_products 2 - $db_type");
  is($prods->[0]->id, $p->id, "get_products 3 - $db_type");

  #$DB::single = 1;
  #local $Rose::DB::Object::Debug = 1;

  # Reserved tablee name tests
  if($db_type eq 'mysql')
  {
    my $o = Mysql::Read->new(read => 'Foo')->save;
    $o = Mysql::Read->new(id => $o->id)->load;
    is($o->read, 'Foo', "reserved table name 1 - $db_type");
    my $os = Mysql::Read::Manager->get_read;
    ok(@$os == 1 && $os->[0]->read eq 'Foo', "reserved table name 2 - $db_type");

    ok(Mysql::NoPkTest->isa('Rose::DB::Object'), "require_primary_key 1 - $db_type")
  }
  else
  {
    SKIP:
    {
      skip("reserved table name and no pk tests", 3);
    }
  }
}


BEGIN
{
  our %Have;

  our %Reserved_Words =
  (
    'pg' => [ qw(role cast user) ],
    'pg_with_schema' => [ qw(role cast user) ],
    'mysql' => [ qw(read for case) ],
  );

  #
  # PostgreSQL
  #

  my $dbh;

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

  if(!$@ && $dbh)
  {
    $Have{'pg'} = 1;
    $Have{'pg_with_schema'} = 1;

    # Drop existing tables and create schema, ignoring errors
    {
      local $dbh->{'RaiseError'} = 0;
      local $dbh->{'PrintError'} = 0;

      $dbh->do('DROP TABLE no_pk_test CASCADE');
      $dbh->do('DROP TABLE no_pk_test2 CASCADE');
      $dbh->do('DROP TABLE products_colors CASCADE');
      $dbh->do('DROP TABLE colors CASCADE');
      $dbh->do('DROP TABLE prices CASCADE');
      $dbh->do('DROP TABLE products CASCADE');
      $dbh->do('DROP TABLE vendors CASCADE');

      $dbh->do('DROP TABLE Rose_db_object_private.no_pk_test CASCADE');
      $dbh->do('DROP TABLE Rose_db_object_private.no_pk_test2 CASCADE');
      $dbh->do('DROP TABLE Rose_db_object_private.products_colors CASCADE');
      $dbh->do('DROP TABLE Rose_db_object_private.colors CASCADE');
      $dbh->do('DROP TABLE Rose_db_object_private.prices CASCADE');
      $dbh->do('DROP TABLE Rose_db_object_private.products CASCADE');
      $dbh->do('DROP TABLE Rose_db_object_private.vendors CASCADE');

      $dbh->do('DROP SCHEMA Rose_db_object_private CASCADE');
      $dbh->do('CREATE SCHEMA Rose_db_object_private');
    }

    $dbh->do(<<"EOF");
CREATE TABLE no_pk_test
(
  id    SERIAL NOT NULL,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE no_pk_test2
(
  id    SERIAL NOT NULL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE vendors
(
  id    SERIAL NOT NULL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE products
(
  id      SERIAL NOT NULL PRIMARY KEY,
  name    VARCHAR(255) NOT NULL,
  price   DECIMAL(10,2) NOT NULL DEFAULT 0.00,

  @{[ join(', ', map { qq("$_" INT) } @{$Reserved_Words{'pg'}}) . ',' ]}

  vendor_id  INT REFERENCES vendors (id),

  status  VARCHAR(128) NOT NULL DEFAULT 'inactive' 
            CHECK(status IN ('inactive', 'active', 'defunct')),

  tee_time        TIME,
  tee_time5       TIME(5) DEFAULT '12:34:56.12345',

  date_created    TIMESTAMP NOT NULL DEFAULT NOW(),
  release_date    TIMESTAMP,
  release_date_tz TIMESTAMP WITH TIME ZONE,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE UNIQUE INDEX products_uk_test ON products (date_created) WHERE status = 'inactive';
EOF

    $dbh->do(<<"EOF");
CREATE UNIQUE INDEX products_uk1 ON products (LOWER(name))
EOF

    $dbh->do(<<"EOF");
CREATE TABLE prices
(
  id          SERIAL NOT NULL PRIMARY KEY,
  product_id  INT NOT NULL REFERENCES products (id),
  region      CHAR(2) NOT NULL DEFAULT 'US',
  price       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  mprice      MONEY,

  UNIQUE(product_id, region)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE colors
(
  id    SERIAL NOT NULL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE products_colors
(
  product_id  INT NOT NULL REFERENCES products (id),
  color_id    INT NOT NULL REFERENCES colors (id),

  PRIMARY KEY(product_id, color_id)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.no_pk_test
(
  id    SERIAL NOT NULL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.no_pk_test2
(
  id    SERIAL NOT NULL,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.vendors
(
  id    SERIAL NOT NULL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.products
(
  id      SERIAL NOT NULL PRIMARY KEY,
  name    VARCHAR(255) NOT NULL,
  price   DECIMAL(10,2) NOT NULL DEFAULT 0.00,

  @{[ join(', ', map { qq("$_" INT) } @{$Reserved_Words{'pg'}}) . ',' ]}

  vendor_id  INT REFERENCES vendors (id),

  status  VARCHAR(128) NOT NULL DEFAULT 'inactive' 
            CHECK(status IN ('inactive', 'active', 'defunct')),

  tee_time        TIME,
  tee_time5       TIME(5) DEFAULT '12:34:56.12345',

  date_created    TIMESTAMP NOT NULL DEFAULT NOW(),
  release_date    TIMESTAMP,
  release_date_tz TIMESTAMP WITH TIME ZONE,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE UNIQUE INDEX products_uk_test ON Rose_db_object_private.products (date_created) WHERE status = 'inactive';
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.prices
(
  id          SERIAL NOT NULL PRIMARY KEY,
  product_id  INT NOT NULL REFERENCES products (id),
  region      CHAR(2) NOT NULL DEFAULT 'US',
  price       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  mprice      MONEY,

  UNIQUE(product_id, region)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.colors
(
  id    SERIAL NOT NULL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.products_colors
(
  product_id  INT NOT NULL REFERENCES products (id),
  color_id    INT NOT NULL REFERENCES colors (id),

  PRIMARY KEY(product_id, color_id)
)
EOF

    $dbh->disconnect;
  }

  #
  # MySQL
  #

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

    die "MySQL version too old"  unless($db->database_version >= 4_000_000);

    # Drop existing tables, ignoring errors
    {
      local $dbh->{'RaiseError'} = 0;
      local $dbh->{'PrintError'} = 0;

      $dbh->do('DROP TABLE no_pk_test CASCADE');
      $dbh->do('DROP TABLE products_colors CASCADE');
      $dbh->do('DROP TABLE colors CASCADE');
      $dbh->do('DROP TABLE prices CASCADE');
      $dbh->do('DROP TABLE products CASCADE');
      $dbh->do('DROP TABLE vendors CASCADE');
      $dbh->do('DROP TABLE `read` CASCADE');
    }

    # Foreign key stuff requires InnoDB support
    $dbh->do(<<"EOF");
CREATE TABLE vendors
(
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
ENGINE=InnoDB
EOF

    # MySQL will silently ignore the "ENGINE=InnoDB" part and create
    # a MyISAM table instead.  MySQL is evil!  Now we have to manually
    # check to make sure an InnoDB table was really created.
    my $db_name = $db->database;
    my $sth = $dbh->prepare("SHOW TABLE STATUS FROM `$db_name` LIKE ?");
    $sth->execute('vendors');
    my $info = $sth->fetchrow_hashref;

    no warnings 'uninitialized';
    unless(lc $info->{'Type'} eq 'innodb' || lc $info->{'Engine'} eq 'innodb')
    {
      die "Missing InnoDB support";
    }
  };

  if(!$@ && $dbh)
  {
    $Have{'mysql'} = 1;

    $dbh->do(<<"EOF");
CREATE TABLE no_pk_test
(
  id    INT NOT NULL,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE products
(
  id      INT AUTO_INCREMENT PRIMARY KEY,
  name    VARCHAR(255) NOT NULL,
  price   DECIMAL(10,2) NOT NULL DEFAULT 0.00,

  @{[ join(', ', map { "`$_` INT" } @{$Reserved_Words{'mysql'}}) . ',' ]}

  vendor_id  INT,

  status  VARCHAR(128) NOT NULL DEFAULT 'inactive' 
            CHECK(status IN ('inactive', 'active', 'defunct')),

  date_created  TIMESTAMP,
  release_date  TIMESTAMP,

  UNIQUE(name),
  INDEX(vendor_id),

  FOREIGN KEY (vendor_id) REFERENCES vendors (id) ON DELETE NO ACTION ON UPDATE SET NULL
)
ENGINE=InnoDB
EOF

    $dbh->do(<<"EOF");
CREATE TABLE prices
(
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  product_id  INT NOT NULL,
  region      CHAR(2) NOT NULL DEFAULT 'US',
  price       DECIMAL(10,2) NOT NULL DEFAULT 0.00,

  UNIQUE(product_id, region),
  INDEX(product_id),

  FOREIGN KEY (product_id) REFERENCES products (id) ON UPDATE NO ACTION
)
ENGINE=InnoDB
EOF

    $dbh->do(<<"EOF");
CREATE TABLE colors
(
  id    INT AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
ENGINE=InnoDB
EOF

    $dbh->do(<<"EOF");
CREATE TABLE products_colors
(
  product_id  INT NOT NULL,
  color_id    INT NOT NULL,

  PRIMARY KEY(product_id, color_id),

  INDEX(color_id),
  INDEX(product_id),

  FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE NO ACTION,
  FOREIGN KEY (color_id) REFERENCES colors (id) ON UPDATE NO ACTION
)
ENGINE=InnoDB
EOF

    $dbh->do(<<"EOF");
CREATE TABLE `read`
(
  id      INT AUTO_INCREMENT PRIMARY KEY,
  `read`  VARCHAR(255) NOT NULL
)
ENGINE=InnoDB
EOF

    $dbh->disconnect;
  }

  #
  # Informix
  #

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

  if(!$@ && $dbh)
  {
    $Have{'informix'} = 1;

    # Drop existing tables, ignoring errors
    {
      local $dbh->{'RaiseError'} = 0;
      local $dbh->{'PrintError'} = 0;

      $dbh->do('DROP TABLE no_pk_test CASCADE');
      $dbh->do('DROP TABLE products_colors CASCADE');
      $dbh->do('DROP TABLE colors CASCADE');
      $dbh->do('DROP TABLE prices CASCADE');
      $dbh->do('DROP TABLE products CASCADE');
      $dbh->do('DROP TABLE vendors CASCADE');
    }

    $dbh->do(<<"EOF");
CREATE TABLE no_pk_test
(
  id    INT NOT NULL,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE vendors
(
  id    SERIAL NOT NULL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE products
(
  id      SERIAL NOT NULL PRIMARY KEY,
  name    VARCHAR(255) NOT NULL,
  price   DECIMAL(10,2) DEFAULT 0.00 NOT NULL,

  vendor_id  INT REFERENCES vendors (id),

  status  VARCHAR(128) DEFAULT 'inactive' NOT NULL
            CHECK(status IN ('inactive', 'active', 'defunct')),

  rint1         INT,
  bint1         INT8 DEFAULT 9223372036854775800,

  date_created  DATETIME YEAR TO SECOND,
  release_date  DATETIME YEAR TO SECOND,
  release_day   DATETIME YEAR TO MONTH,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE prices
(
  id          SERIAL NOT NULL PRIMARY KEY,
  product_id  INT NOT NULL REFERENCES products (id),
  region      CHAR(2) DEFAULT 'US' NOT NULL,
  price       DECIMAL(10,2) DEFAULT 0.00 NOT NULL,

  UNIQUE(product_id, region)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE colors
(
  id    SERIAL NOT NULL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE products_colors
(
  product_id  INT NOT NULL REFERENCES products (id),
  color_id    INT NOT NULL REFERENCES colors (id),

  PRIMARY KEY(product_id, color_id)
)
EOF

    $dbh->disconnect;
  }

  #
  # SQLite
  #

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

  if(!$@ && $dbh)
  {
    $Have{'sqlite'} = 1;

    # Drop existing tables, ignoring errors
    {
      local $dbh->{'RaiseError'} = 0;
      local $dbh->{'PrintError'} = 0;

      $dbh->do('DROP TABLE no_pk_test');
      $dbh->do('DROP TABLE products_colors');
      $dbh->do('DROP TABLE colors');
      $dbh->do('DROP TABLE prices');
      $dbh->do('DROP TABLE products');
      $dbh->do('DROP TABLE vendors');
    }

    $dbh->do(<<"EOF");
CREATE TABLE 'no_pk_test'
(
  id    INT NOT NULL,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE "vendors"
(
  "id"    INTEGER PRIMARY KEY AUTOINCREMENT,
  name  VARCHAR(255) NOT NULL,

  UNIQUE("name")
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE products
(
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  name    VARCHAR(255) NOT NULL,
  price   DECIMAL(10,2) DEFAULT 0.00 NOT NULL,

  vendor_id  INT REFERENCES vendors (id),

  status  VARCHAR(128) DEFAULT 'inactive' NOT NULL
            CHECK(status IN ('inactive', 'active', 'defunct')),

  date_created  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  release_date  DATETIME,

  UNIQUE('name')
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE prices
(
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id  INT NOT NULL REFERENCES products (id),
  region      CHAR(2) DEFAULT 'US' NOT NULL,
  price       DECIMAL(10,2) DEFAULT 0.00 NOT NULL,

  UNIQUE(product_id, region)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE colors
(
  id    INTEGER PRIMARY KEY AUTOINCREMENT,
  name  VARCHAR(255) NOT NULL,

  UNIQUE(name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE products_colors
(
  product_id  INT NOT NULL REFERENCES products (id),
  color_id    INT NOT NULL REFERENCES colors (id),

  PRIMARY KEY(product_id, color_id)
)
EOF

    $dbh->disconnect;
  }

  #
  # Oracle
  #

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

  if(!$@ && $dbh)
  {
    $Have{'oracle'} = 1;

    # Drop existing tables, ignoring errors
    {
      local $dbh->{'RaiseError'} = 0;
      local $dbh->{'PrintError'} = 0;

      $dbh->do('DROP TABLE no_pk_test');
      $dbh->do('DROP TABLE products_colors');
      $dbh->do('DROP TABLE colors');
      $dbh->do('DROP TABLE prices');
      $dbh->do('DROP TABLE products');
      $dbh->do('DROP TABLE vendors');
      $dbh->do('DROP SEQUENCE vendors_id_seq');
      $dbh->do('DROP SEQUENCE products_id_seq');
      $dbh->do('DROP SEQUENCE prices_id_seq');
      $dbh->do('DROP SEQUENCE colors_id_seq');
    }

    $dbh->do(<<"EOF");
CREATE TABLE no_pk_test
(
  id    INT NOT NULL,
  name  VARCHAR(255) NOT NULL,

  CONSTRAINT no_pk_test_name UNIQUE (name)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE vendors
(
  id    INT NOT NULL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,

  CONSTRAINT vendors_name UNIQUE (name)
)
EOF

    $dbh->do('CREATE SEQUENCE vendors_id_seq');
    $dbh->do(<<"EOF");
CREATE OR REPLACE TRIGGER vendors_insert BEFORE INSERT ON vendors
FOR EACH ROW
BEGIN
    SELECT NVL(:new.id, vendors_id_seq.nextval)
      INTO :new.id FROM dual;
END;
EOF

    $dbh->do(<<"EOF");
CREATE TABLE products
(
  id      INT NOT NULL PRIMARY KEY,
  name    VARCHAR(255) NOT NULL,
  price   DECIMAL(10,2) DEFAULT 0.00 NOT NULL,

  vendor_id  INT,

  status  VARCHAR(128) DEFAULT 'inactive' NOT NULL
            CHECK(status IN ('inactive', 'active', 'defunct')),

  rint1         INT,
  bint1         NUMBER(20) DEFAULT 9223372036854775800,

  date_created  TIMESTAMP,

  CONSTRAINT products_name UNIQUE (name),
  CONSTRAINT products_vendor_id_fk FOREIGN KEY (vendor_id) REFERENCES vendors (id)
)
EOF

    $dbh->do('CREATE SEQUENCE products_id_seq');
    $dbh->do(<<"EOF");
CREATE OR REPLACE TRIGGER products_insert BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    SELECT NVL(:new.id, products_id_seq.nextval)
      INTO :new.id FROM dual;
END;
EOF

    $dbh->do(<<"EOF");
CREATE TABLE prices
(
  id          INT NOT NULL PRIMARY KEY,
  product_id  INT NOT NULL,
  region      CHAR(2) DEFAULT 'US' NOT NULL,
  price       NUMBER(10,2) DEFAULT 0.00 NOT NULL,

  CONSTRAINT prices_uk UNIQUE (product_id, region),
  CONSTRAINT prices_product_id_fk FOREIGN KEY (product_id) REFERENCES products (id)
)
EOF

    $dbh->do('CREATE SEQUENCE prices_id_seq');
    $dbh->do(<<"EOF");
CREATE OR REPLACE TRIGGER prices_insert BEFORE INSERT ON prices
FOR EACH ROW
BEGIN
    SELECT NVL(:new.id, prices_id_seq.nextval)
      INTO :new.id FROM dual;
END;
EOF

    $dbh->do(<<"EOF");
CREATE TABLE colors
(
  id    INT NOT NULL PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,

  CONSTRAINT colors_name UNIQUE (name)
)
EOF

    $dbh->do('CREATE SEQUENCE colors_id_seq');
    $dbh->do(<<"EOF");
CREATE OR REPLACE TRIGGER colors_insert BEFORE INSERT ON colors
FOR EACH ROW
BEGIN
    SELECT NVL(:new.id, colors_id_seq.nextval)
      INTO :new.id FROM dual;
END;
EOF

    $dbh->do(<<"EOF");
CREATE TABLE products_colors
(
  product_id  INT NOT NULL,
  color_id    INT NOT NULL,

  CONSTRAINT products_colors_pk PRIMARY KEY (product_id, color_id),
  CONSTRAINT products_colors_product_id_fk FOREIGN KEY (product_id) REFERENCES products (id), 
  CONSTRAINT products_colors_color_id_fk FOREIGN KEY (color_id) REFERENCES colors (id)
)
EOF

    $dbh->disconnect;
  }
}

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 no_pk_test CASCADE');
    $dbh->do('DROP TABLE no_pk_test2 CASCADE');
    $dbh->do('DROP TABLE products_colors CASCADE');
    $dbh->do('DROP TABLE colors CASCADE');
    $dbh->do('DROP TABLE prices CASCADE');
    $dbh->do('DROP TABLE products CASCADE');
    $dbh->do('DROP TABLE vendors CASCADE');

    $dbh->do('DROP TABLE Rose_db_object_private.no_pk_test CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_private.no_pk_test2 CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_private.products_colors CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_private.colors CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_private.prices CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_private.products CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_private.vendors CASCADE');

    $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 no_pk_test CASCADE');
    $dbh->do('DROP TABLE products_colors CASCADE');
    $dbh->do('DROP TABLE colors CASCADE');
    $dbh->do('DROP TABLE prices CASCADE');
    $dbh->do('DROP TABLE products CASCADE');
    $dbh->do('DROP TABLE vendors CASCADE');
    $dbh->do('DROP TABLE `read` CASCADE');

    $dbh->disconnect;
  }

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

    $dbh->do('DROP TABLE no_pk_test CASCADE');
    $dbh->do('DROP TABLE products_colors CASCADE');
    $dbh->do('DROP TABLE colors CASCADE');
    $dbh->do('DROP TABLE prices CASCADE');
    $dbh->do('DROP TABLE products CASCADE');
    $dbh->do('DROP TABLE vendors CASCADE');

    $dbh->disconnect;
  }

  if($Have{'sqlite'})
  {
    # Informix
    my $dbh = Rose::DB->new('sqlite_admin')->retain_dbh()
      or die Rose::DB->error;

    $dbh->do('DROP TABLE no_pk_test');
    $dbh->do('DROP TABLE products_colors');
    $dbh->do('DROP TABLE colors');
    $dbh->do('DROP TABLE prices');
    $dbh->do('DROP TABLE products');
    $dbh->do('DROP TABLE vendors');

    $dbh->disconnect;
  }

  if($Have{'oracle'})
  {
    # Informix
    my $dbh = Rose::DB->new('oracle_admin')->retain_dbh()
      or die Rose::DB->error;

    $dbh->do('DROP TABLE no_pk_test');
    $dbh->do('DROP TABLE products_colors');
    $dbh->do('DROP TABLE colors');
    $dbh->do('DROP TABLE prices');
    $dbh->do('DROP TABLE products');
    $dbh->do('DROP TABLE vendors');
    $dbh->do('DROP SEQUENCE vendors_id_seq');
    $dbh->do('DROP SEQUENCE products_id_seq');
    $dbh->do('DROP SEQUENCE prices_id_seq');
    $dbh->do('DROP SEQUENCE colors_id_seq');

    $dbh->disconnect;
  }
}