#!/usr/bin/perl
use strict;
use warnings;
use Class::DBI::mysql;

# See the ./run script in this directory to set up 
# the database and enironment.  
# Essentially we need something like
#   $ export DBI_DSN=dbi:mysql:database=testcd
#   $ export DBI_USER=root
#   $ export DBI_DATA=testcd
#   $ export DBI_PASS=...
#   $ ./TestCD.pl

# The base class for this database simply defines the database connection.
package TestCD::Database;
use base 'Class::DBI::mysql';
__PACKAGE__->set_db('Main', @ENV{qw/DBI_DSN DBI_USER DBI_PASS/});
__PACKAGE__->autoupdate(1);

# Set up all the appropriate accessor methods for each table.
# Define the relations between the foreign keys.
# The "has_a" class must be defined before 
# the corresponding "has_many" class.

package TestCD::Track;
use base 'TestCD::Database';
__PACKAGE__->set_up_table('track');
# The "has_a" relation specifies that the 
# $track->song method should return a TestCD::Song object, not just an id.
# The documentation calls this "inflating".
__PACKAGE__->has_a(song => 'TestCD::Song');
__PACKAGE__->has_a(cd   => 'TestCD::CD');

package TestCD::Song;
use base 'TestCD::Database';
__PACKAGE__->set_up_table('song');
# The "has_many" relation defines a $song->tracks method
# that returns an array of all the Track objects which 
# have contain a key for this song.
# It also creates a $song->add_to_tracks method that takes care of the link.
__PACKAGE__->has_many(tracks => 'TestCD::Track');

package TestCD::CD;
use base 'TestCD::Database';
__PACKAGE__->set_up_table('cd');
# This variation on has_many can be used to specify what is returned.
__PACKAGE__->has_many(seqnums => ['TestCD::Track' => 'seqnum'] );
# We could also specify order of returned array , something like
#     track => 'TestCD::Track', {order_by => 'which_column'}

# -----------------------------------------------

package main;

# Take it for a spin.

# === songs ===

my $name = '%Random%';
print "Looking for a song whose name contains '$name'.\n";
# In search_like, % means any number of chars, _ means one char.
# (This is standard SQL syntax.)
my ($song) = TestCD::Song->search_like(name=>$name);
if ($song) {
  print "Found one whose name is '" . $song->name . "'.\n";
  print "Its ID is '" . $song->id . "'.\n";
  print "Appending ' and again' to its name.\n";
  $song->name($song->name . ' and again' );  # change object in memory.
  $song->update;                             # update database.
}
else {
  print "Couldn't find a song with like '$name'.\n";
}
print "\n";

# Add yet another song.
# Its given the next sequential id automatically.
print "Appending a new song to the list.\n\n";
TestCD::Song->create( {name => 'Yet Another Song'} );

# == listing of all songs, and all CD's that each song is on.

print " Song name (id) \n";
print " -------------- \n";
foreach my $song (TestCD::Song->retrieve_all){
  # Get a list of all the tracks for this song.
  # For each track, look up the CD its on, and get the CD's title.
  my (@cds) = map {$_->cd->title} $song->tracks;
  print " " . $song->name . "(id " . $song->id . ") ";
  print "isn't on any CDs.\n"                    if @cds < 1;
  print "is on the CD '" . $cds[0] . "'\n"             if @cds == 1;
  print "is on CDs (" . join(', ',@cds) . ")\n"     if @cds > 1;
}
print "\n";

# ----------------------------------------------------------

# Can I retrieve by a name if that's given as a unique key?  Yes.
my $cd = TestCD::CD->retrieve( title=>'Celtic Groove Brand' );
print "It's title is '" . $cd->title . "'.\n";

