DROP TABLE public.nodes;
DROP TABLE public.sequences;
DROP TABLE public.taxa;

CREATE TABLE public.taxa (
    id SERIAL NOT NULL PRIMARY KEY
    , common_name CHARACTER(60)
    , UNIQUE (common_name)
);

CREATE TABLE public.nodes (
    id SERIAL NOT NULL PRIMARY KEY
    , description CHARACTER(60)
    , taxon_id INT REFERENCES taxa (id)
);

CREATE TABLE public.sequences (
    id SERIAL NOT NULL PRIMARY KEY
    , description CHARACTER(60)
    , seq TEXT NOT NULL
    , taxon_id INT REFERENCES taxa (id)
);

##</code><code>##



package MyDB;

use base qw(Rose::DB);

__PACKAGE__->use_private_registry;

__PACKAGE__->register_db(
    driver   => 'pg',
    database => 'mydb',
    host     => 'localhost',
    username => $ENV{USER},
    password => '',
);
1; 

package MyDB::Object;

use MyDB;

use base qw(Rose::DB::Object);

sub init_db { MyDB->new };

1;

package MyDB::Node;

use base qw(MyDB::Object);

__PACKAGE__->meta->table('nodes');
__PACKAGE__->meta->auto_initialize;
__PACKAGE__->meta->make_manager_class('nodes');
1;


package MyDB::Sequence;

use base qw(MyDB::Object);

__PACKAGE__->meta->table('sequences');
__PACKAGE__->meta->auto_initialize;
__PACKAGE__->meta->make_manager_class('sequences');

1;

package MyDB::Taxon;

use base qw(MyDB::Object);

__PACKAGE__->meta->table('taxa');
__PACKAGE__->meta->auto_initialize;

1;



my $t = MyDB::Taxon->new(common_name => 'Homo Sapiens');
$t->add_sequences({ description => '1', seq => 'AA'},
                  { description => '2', seq => 'GG'},);
$t->add_nodes({ description => '1', }, );
$t->save;

# a little bit more complicated than necessary just
# to demonstrate queries
my $nodes = MyDB::Node::Manager->get_nodes(
        query => [ 'taxon.id' => $t->id],
        require_objects => [ 'taxon'],
    );

foreach my $node (@$nodes) {
    print "NODE: ". $node->description . ' '
          . $node->taxon->common_name . "\n";
};

my $seqs = MyDB::Sequence::Manager->get_sequences(
        query => [ 'taxon_id' => $t->id],
    );

foreach my $seq (@$seqs) {
    print "SEQ: ". $seq->description . ' '
          . $seq->taxon->common_name . "\n";
};

# change taxon
$nodes->[0]->taxon(MyDB::Taxon->new(common_name=>'Guppy'));
$nodes->[0]->save;

# old taxon
$nodes->[0]->taxon($t);
#$nodes->[0]->save;