#!/usr/bin/perl -w

use strict;

use Test::More tests => 32;

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

our(%HAVE, $DID_SETUP);

#
# Tests
#

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

foreach my $db_type (qw(mysql pg pg_with_schema informix sqlite))
{
  SKIP:
  {
    skip("$db_type tests", 6)  unless($HAVE{$db_type});
  }

  next  unless($HAVE{$db_type});

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

  unless($DID_SETUP++)
  {
    # Load classes
    use FindBin qw($Bin);
    use lib "$Bin/lib";
    require My::DB::Gene::Main;
    require My::DB::Unigene::Main;
  }

  # Run tests

  is(join(', ', map { $_->name } My::DB::Gene2Unigene->meta->foreign_keys),
     'Rose_db_object_g_main, Rose_db_object_ug_main', "foreign_keys 1 - $db_type");  

  is(join(', ', map { $_->name . ' ' . $_->type} My::DB::Gene::Main->meta->relationships),
     'unigenes many to many', "relationships 1 - $db_type");

  is(join(', ', map { $_->name . ' ' . $_->type} My::DB::Unigene::Main->meta->relationships),
     'genes many to many', "relationships 2 - $db_type");

  is(scalar @Rose::DB::Object::Metadata::Deferred_Relationships || 0, 0,
     "deferred relationships - $db_type");

  # XXX: switch entirely to per-db SQL?
  #My::DB::Gene::Main->meta->init_with_db(Rose::DB->new);
  #My::DB::Unigene::Main->meta->init_with_db(Rose::DB->new);

  my $g = My::DB::Gene::Main->new;
  eval { $g->unigenes };
  ok(!$@, "unigenes - $db_type");

  $g = My::DB::Unigene::Main->new;
  eval { $g->genes };
  ok(!$@, "genes - $db_type");
}

BEGIN
{
  our %HAVE;

  #
  # 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 Rose_db_object_g_ug CASCADE');
      $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE');
      $dbh->do('DROP TABLE Rose_db_object_g_main CASCADE');
      $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_g_ug CASCADE');
      $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_ug_main CASCADE');
      $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_g_main CASCADE');
      $dbh->do('CREATE SCHEMA Rose_db_object_private');
    }

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_ug_main
(
  ug_id         VARCHAR PRIMARY KEY NOT NULL,
  species       VARCHAR,
  symbol        VARCHAR,
  description   VARCHAR,
  cytoband      VARCHAR,
  scount        INTEGER,
  homol         VARCHAR,
  rest_expr     VARCHAR,
  mgi           VARCHAR
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_g_main
(
  tax_id           INTEGER,
  gene_id          INTEGER PRIMARY KEY,
  symbol           VARCHAR,
  locustag         VARCHAR,
  chromosome       VARCHAR,
  map_location     VARCHAR,
  description      VARCHAR,
  gene_type        VARCHAR,
  symbol_from_nomenclature_auth    VARCHAR,
  full_name_from_nomenclature_auth VARCHAR,
  nomenclature_status              VARCHAR,
  discontinued     BOOLEAN DEFAULT FALSE,
  new_gene_id      INTEGER DEFAULT NULL
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_g_ug
(
  g_ug_id SERIAL PRIMARY KEY,

  gene_id INTEGER REFERENCES Rose_db_object_g_main (gene_id) 
    ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,

  ug_id VARCHAR REFERENCES Rose_db_object_ug_main (ug_id) 
    ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.Rose_db_object_ug_main
(
  ug_id         VARCHAR PRIMARY KEY NOT NULL,
  species       VARCHAR,
  symbol        VARCHAR,
  description   VARCHAR,
  cytoband      VARCHAR,
  scount        INTEGER,
  homol         VARCHAR,
  rest_expr     VARCHAR,
  mgi           VARCHAR
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.Rose_db_object_g_main
(
  tax_id           INTEGER,
  gene_id          INTEGER PRIMARY KEY,
  symbol           VARCHAR,
  locustag         VARCHAR,
  chromosome       VARCHAR,
  map_location     VARCHAR,
  description      VARCHAR,
  gene_type        VARCHAR,
  symbol_from_nomenclature_auth    VARCHAR,
  full_name_from_nomenclature_auth VARCHAR,
  nomenclature_status              VARCHAR,
  discontinued     BOOLEAN DEFAULT FALSE,
  new_gene_id      INTEGER DEFAULT NULL
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.Rose_db_object_g_ug
(
  g_ug_id SERIAL PRIMARY KEY,

  gene_id INTEGER REFERENCES Rose_db_object_private.Rose_db_object_g_main (gene_id) 
    ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,

  ug_id VARCHAR REFERENCES Rose_db_object_private.Rose_db_object_ug_main (ug_id) 
    ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
)
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 && 
                                        mysql_supports_innodb());

    # Drop existing tables, ignoring errors
    {
      local $dbh->{'RaiseError'} = 0;
      local $dbh->{'PrintError'} = 0;
      $dbh->do('DROP TABLE Rose_db_object_g_ug CASCADE');
      $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE');
      $dbh->do('DROP TABLE Rose_db_object_g_main CASCADE');
    }

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_ug_main
(
  ug_id         VARCHAR(255) NOT NULL PRIMARY KEY,
  species       VARCHAR(255),
  symbol        VARCHAR(255),
  description   VARCHAR(255),
  cytoband      VARCHAR(255),
  scount        INT,
  homol         VARCHAR(255),
  rest_expr     VARCHAR(255),
  mgi           VARCHAR(255)
)
ENGINE=InnoDB
EOF
  };

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

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_g_main
(
  gene_id          INT NOT NULL PRIMARY KEY,
  tax_id           INT,
  symbol           VARCHAR(255),
  locustag         VARCHAR(255),
  chromosome       VARCHAR(255),
  map_location     VARCHAR(255),
  description      VARCHAR(255),
  gene_type        VARCHAR(255),
  symbol_from_nomenclature_auth    VARCHAR(255),
  full_name_from_nomenclature_auth VARCHAR(255),
  nomenclature_status              VARCHAR(255),
  discontinued     INT DEFAULT 0,
  new_gene_id      INT DEFAULT NULL
)
ENGINE=InnoDB
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_g_ug
(
  g_ug_id  INT PRIMARY KEY,
  gene_id  INT NOT NULL,
  ug_id    VARCHAR(255) NOT NULL,

  INDEX(gene_id),
  INDEX(ug_id),

  FOREIGN KEY (gene_id) REFERENCES Rose_db_object_g_main (gene_id),
  FOREIGN KEY (ug_id) REFERENCES Rose_db_object_ug_main (ug_id)
)
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 Rose_db_object_g_ug');
      $dbh->do('DROP TABLE Rose_db_object_ug_main');
      $dbh->do('DROP TABLE Rose_db_object_g_main');
    }

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_ug_main
(
  ug_id         VARCHAR(255) NOT NULL PRIMARY KEY,
  species       VARCHAR(255),
  symbol        VARCHAR(255),
  description   VARCHAR(255),
  cytoband      VARCHAR(255),
  scount        INT,
  homol         VARCHAR(255),
  rest_expr     VARCHAR(255),
  mgi           VARCHAR(255)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_g_main
(
  tax_id           INT,
  gene_id          INT PRIMARY KEY,
  symbol           VARCHAR(255),
  locustag         VARCHAR(255),
  chromosome       VARCHAR(255),
  map_location     VARCHAR(255),
  description      VARCHAR(255),
  gene_type        VARCHAR(255),
  symbol_from_nomenclature_auth    VARCHAR(255),
  full_name_from_nomenclature_auth VARCHAR(255),
  nomenclature_status              VARCHAR(255),
  discontinued     INT DEFAULT 0,
  new_gene_id      INT DEFAULT NULL
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_g_ug
(
  g_ug_id  SERIAL PRIMARY KEY,
  gene_id  INT REFERENCES Rose_db_object_g_main (gene_id),
  ug_id    VARCHAR(255) REFERENCES Rose_db_object_ug_main (ug_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 Rose_db_object_g_ug');
      $dbh->do('DROP TABLE Rose_db_object_ug_main');
      $dbh->do('DROP TABLE Rose_db_object_g_main');
    }

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_ug_main
(
  ug_id         VARCHAR(255) NOT NULL PRIMARY KEY,
  species       VARCHAR(255),
  symbol        VARCHAR(255),
  description   VARCHAR(255),
  cytoband      VARCHAR(255),
  scount        INT,
  homol         VARCHAR(255),
  rest_expr     VARCHAR(255),
  mgi           VARCHAR(255)
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_g_main
(
  tax_id           INT,
  gene_id          INT PRIMARY KEY,
  symbol           VARCHAR(255),
  locustag         VARCHAR(255),
  chromosome       VARCHAR(255),
  map_location     VARCHAR(255),
  description      VARCHAR(255),
  gene_type        VARCHAR(255),
  symbol_from_nomenclature_auth    VARCHAR(255),
  full_name_from_nomenclature_auth VARCHAR(255),
  nomenclature_status              VARCHAR(255),
  discontinued     INT DEFAULT 0,
  new_gene_id      INT DEFAULT NULL
)
EOF

    $dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_g_ug
(
  g_ug_id  INTEGER PRIMARY KEY AUTOINCREMENT,
  gene_id  INTEGER REFERENCES Rose_db_object_g_main (gene_id),
  ug_id    VARCHAR(255) REFERENCES Rose_db_object_ug_main (ug_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 Rose_db_object_g_ug CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_g_main CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_g_ug CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_ug_main CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_private.Rose_db_object_g_main 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_g_ug CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_g_main 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 Rose_db_object_g_ug CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_ug_main CASCADE');
    $dbh->do('DROP TABLE Rose_db_object_g_main 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 Rose_db_object_g_ug');
    $dbh->do('DROP TABLE Rose_db_object_ug_main');
    $dbh->do('DROP TABLE Rose_db_object_g_main');

    $dbh->disconnect;
  }
}