#!/usr/bin/perl
############
#
# Generate an Entity Relationship Diagram 
# (or sql definitions in other languages )
# from .sql file table definitions.
#
# see http://search.cpan.org/~kclark/SQL-Translator-0.05/lib/SQL/
#       Translator.pm
#       Translator/Parser/MySQL.pm
#       Translator/Producer/ClassDBI.pm
#       Translator/Producer/GraphViz.pm
# 
# Though in practice I think I'd rather use one of those
# Class::DBI automatic "look at the mysql and create the methods" thingies
# than Producer::GraphViz
#
#############

use strict;
use warnings;

use SQL::Translator;
# use SQL::Translator::Parser::MySQL;        # all these load automatically
# use SQL::Translator::Producer::GraphViz;   
# use SQL::Translator::Producer::ClassDBI;

# --- ERD image --------------------------------------------

my $translator = 
  SQL::Translator->new(
		       debug => 0,

		       from => 'MySQL',
		       filename => 'createcd.sql',

		       to => 'GraphViz',
		       producer_args => {
					 output_type   => 'png',
					 # dot | neato | twopi
					 layout        => 'neato',
					 width         => 5,   # inches
					 height        => 5,
					 out_file      => 'schema.png',
					 add_color     => 1,
					 show_constraints => 1,
					 show_fields    => 1,
					 show_datatypes => 0,
					 show_col_sizes => 1,
					}
		      ) 
  or die SQL::Translator->error;
$translator->translate or die $translator->error;

# --- POD output ------------------------------------------
# $translator=SQL::Translator->new(
# 				 from => 'MySQL',
# 				 filename => 'createcd.sql',
# 				 to => 'POD',
# 				 )
#   or die SQL::Translator->error;
# my $output = $translator->translate or die $translator->error;
# print $output;


__END__


# ========= This code =================================

 $translator=SQL::Translator->new(
 				 from => 'MySQL',
 				 filename => 'createcd.sql',
 				 to => 'ClassDBI',
 				 )
   or die SQL::Translator->error;
 my $dbi_output = $translator->translate or die $translator->error;
 print $dbi_output;


# ========= produces this output =======================

package DBI;

# 
# Created by SQL::Translator::Producer::ClassDBI
# Created on Wed Jun 23 23:41:08 2004
# 

use strict;
use base 'Class::DBI::mysql';

DBI->set_db('Main', 'dbi:mysql:_', '', '');

# -------------------------------------------------------------------
package cd;
use base 'DBI';
use Class::DBI::Pager;

cd->set_up_table('cd');


#
# Primary key accessor
#
sub cd {
    shift->id
}

sub tracks {
    return shift->track_cd
}

cd->has_many(
    'track_cd', 'track' => 'cd'
);

# -------------------------------------------------------------------
package song;
use base 'DBI';
use Class::DBI::Pager;

song->set_up_table('song');


#
# Primary key accessor
#
sub song {
    shift->id
}

sub tracks {
    return shift->track_song
}

song->has_many(
    'track_song', 'track' => 'song'
);

# -------------------------------------------------------------------
package track;
use base 'DBI';
use Class::DBI::Pager;

track->set_up_table('track');


#
# Primary key accessor
#
sub track {
    shift->id
}

track->has_a(
    song => 'song'
);

sub track {
    return shift->song
}

track->has_a(
    cd => 'cd'
);

sub track {
    return shift->cd
}

1;


# ========= This code =================================


 $translator=SQL::Translator->new(
 				 from => 'MySQL',
 				 filename => 'createcd.sql',
 				 to => 'POD',
 				 )
   or die SQL::Translator->error;
 my $output = $translator->translate or die $translator->error;
 print $output;

# ========== produces ===================================

=pod

=head1 DESCRIPTION

Schema

=head1 TABLES

=head2 cd

=head3 FIELDS

=head4 id

=over 4

=item * int(11)

=item * PRIMARY KEY

=item * Nullable 'No' 

=back

=head4 title

=over 4

=item * varchar(255)

=item * Default 'Untitled' 

=item * Nullable 'No' 

=back

=head3 CONSTRAINTS

=head4 PRIMARY KEY

=over 4

=item * Fields = id

=back

=head2 song

=head3 FIELDS

=head4 id

=over 4

=item * int(11)

=item * PRIMARY KEY

=item * Nullable 'No' 

=back

=head4 name

=over 4

=item * varchar(255)

=item * Default 'blank' 

=item * Nullable 'No' 

=back

=head3 CONSTRAINTS

=head4 PRIMARY KEY

=over 4

=item * Fields = id

=back

=head2 track

=head3 FIELDS

=head4 id

=over 4

=item * int(11)

=item * PRIMARY KEY

=item * Nullable 'No' 

=back

=head4 seqnum

=over 4

=item * int(11)

=item * Default '-1' 

=item * Nullable 'No' 

=back

=head4 song

=over 4

=item * int(11)

=item * Default '0' 

=item * Nullable 'No' 

=back

=head4 cd

=over 4

=item * int(11)

=item * Default '0' 

=item * Nullable 'No' 

=back

=head3 INDICES

=head4 NORMAL

=over 4

=item * Fields = song

=back

=head4 NORMAL

=over 4

=item * Fields = cd

=back

=head3 CONSTRAINTS

=head4 PRIMARY KEY

=over 4

=item * Fields = id

=back

=head4 FOREIGN KEY

=over 4

=item * Fields = song

=item * Reference Table = L</song>

=item * Reference Fields = L</id>

=item * On delete = CASCADE=back

=head4 FOREIGN KEY

=over 4

=item * Fields = cd

=item * Reference Table = L</cd>

=item * Reference Fields = L</id>

=item * On delete = CASCADE=back

=head1 PRODUCED BY

SQL::Translator::Producer::POD

=cut

# ====  pod2text translator.pl ========================================

DESCRIPTION
    Schema

TABLES
  cd
   FIELDS
   id
    * int(11)
    * PRIMARY KEY
    * Nullable 'No'

   title
    * varchar(255)
    * Default 'Untitled'
    * Nullable 'No'

   CONSTRAINTS
   PRIMARY KEY
    * Fields = id

  song
   FIELDS
   id
    * int(11)
    * PRIMARY KEY
    * Nullable 'No'

   name
    * varchar(255)
    * Default 'blank'
    * Nullable 'No'

   CONSTRAINTS
   PRIMARY KEY
    * Fields = id

  track
   FIELDS
   id
    * int(11)
    * PRIMARY KEY
    * Nullable 'No'

   seqnum
    * int(11)
    * Default '-1'
    * Nullable 'No'

   song
    * int(11)
    * Default '0'
    * Nullable 'No'

   cd
    * int(11)
    * Default '0'
    * Nullable 'No'

   INDICES
   NORMAL
    * Fields = song

   NORMAL
    * Fields = cd

   CONSTRAINTS
   PRIMARY KEY
    * Fields = id

   FOREIGN KEY
    * Fields = song
    * Reference Table = "song"
    * Reference Fields = "id"
    * On delete = CASCADE=back

   FOREIGN KEY
        * Fields = cd
        * Reference Table = "cd"
        * Reference Fields = "id"
        * On delete = CASCADE=back

PRODUCED BY
            SQL::Translator::Producer::POD
