package Class::DBI::DB2;

=head1 NAME

Class::DBI::DB2 - Extensions to Class::DBI for DB2

=head1 SYNOPSIS

  package Music::DBI;
  use base 'Class::DBI::DB2';
  __PACKAGE__->set_db('Main', 'dbi:DB2:dbname', 'user', 'password');

  package Artist;
  use base 'Music::DBI';
  __PACKAGE__->set_up_table('Artist');
  
  # ... see the Class::DBI documentation for details on Class::DBI usage

=head1 DESCRIPTION

Class::DBI::DB2 automates the setup of Class::DBI columns and primary key
for IBM DB2.

This is an extension to Class::DBI that currently implements:

	* Automatic column name discovery.
	
	* Automatic primary key detection.

Instead of setting Class::DBI as your base class, use this.

=head1 AUTHOR

Mark Ferris E<lt>mark.ferris@geac.com<gt>

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself.

=head1 SEE ALSO

L<Class::DBI> L<Class::DBI::mysql> L<Class::DBI::Pg>

=cut

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

use vars qw($VERSION);
$VERSION = '0.10';

=head1 METHODS

=head2 set_up_table

	__PACKAGE__->set_up_table("table_name");

Traditionally, to use Class::DBI, you have to set up the columns:

	__PACKAGE__->columns(All => qw/list of columns/);
	__PACKAGE__->columns(Primary => 'column_name');

While this allows for more flexibility if you're going to arrange your
columns into a variety of groupings, sometimes you just want to create the
'all columns' list.

This call will extract the list of all the columns, and the primary key
and set them up for you. It will die horribly if the table contains
no primary key, or has a composite primary key.

=cut

sub set_up_table {
	my ( $class, $schema ) = @_;
	$class->table(my $table = uc(shift) || $class->table);
	my $dbh     = $class->db_Main;
#	my $catalog = "";

	# find primary keys(s)
	my $sth = $dbh->prepare(<<"SQL");
SELECT c.COLNO FROM SYSCAT.KEYCOLUSE kc,  SYSCAT.TABCONST tc,  SYSCAT.COLUMNS c 
WHERE kc.CONSTNAME=tc.CONSTNAME AND kc.TABSCHEMA=tc.TABSCHEMA 
AND kc.TABNAME=tc.TABNAME AND kc.TABSCHEMA=c.TABSCHEMA AND kc.TABNAME=c.TABNAME 
AND kc.COLNAME=c.COLNAME AND kc.TABSCHEMA = ? AND kc.TABNAME = __TABLE__ AND tc.TYPE = 'P' 
ORDER BY kc.COLSEQ
SQL

  $sth->execute( uc($schema) );
	my $prinum = $sth->fetchall_arrayref;
	$sth->finish;

	# find all columns
	$sth = $dbh->prepare(<<"SQL");
SELECT COLNAME, COLNO, TYPENAME, NULLS FROM SYSCAT.COLUMNS 
WHERE TABSCHEMA = ? and TABNAME = __TABLE__ order by colno 
SQL

  $sth->execute( uc($schema) );
  my $columns = $sth->fetchall_arrayref;
  $sth->finish;

  my(@cols, $primary);
  foreach my $col(@$columns) {
	  push @cols, $col->[0];
	  next unless $prinum && $col->[1] eq $prinum;
	  $primary = $col->[0]; 
  }
	$class->_croak("$table has no primary key") unless $primary;
	$class->columns( All     => @cols );
	$class->columns( Primary => $primary );
}

1;