###################################
package Class::DBI::Loader::AnyDBD;
###################################
use strict;
use vars qw($VERSION);
$VERSION = "0.01";

use Class::DBI::AnyDBD;
use base qw(Class::DBI::Loader::Generic);

=pod

=head1 NAME

Class::DBI::Loader::AnyDBD -- Class DBI loader for any DBD

=head1 SYNOPSIS

B<Load and access existing tables>

  use 'Class::DBI::Loader::AnyDBD';
  my $loader = Class::DBI::Loader::AnyDBD->new(
      dsn    => 'dbi:CSV:',                       # or any other DBI DSN
      tables => [qw/Teacher Subject Assignment/],
      #
      # ... any other Class::DBI::Loader args
  );
  print Teacher->retrieve('Abdul Aziz')->tid;
  #
  # ... any other Class::DBI methods

B<Load, create, populate, and access new tables>

  use 'Class::DBI::Loader::AnyDBD';
  my $loader = Class::DBI::Loader::AnyDBD->new(
      dsn    => 'dbi:DBM:',
      tables => [ 'MyTmp' ],
  );
  MyTmp->create_table(" CREATE TEMP TABLE MyTmp (tid INT,tname TEXT) ");
  MyTmp->create({tid=>99,tname=>'Sanjay Patel'});
  print MyTmp->retrieve(99)->tname;

=head1 DESCRIPTION

This module is based on and works similarly to L<Class::DBI::Loader> - it
automatically creates Class::DBI objects from the tables in a database.

Here's how it differs from Class::DBI::Loader

  * It can be used with any DBD, even if no Class::DBI::Foo or
    Class::DBI::Loader::Foo modules for that DBD are available.

  * It can be used to create tables, including temporary in-memory
    tables (if supported by the DBD).  When you create a new
    table, it is automatically loaded as a Class::DBI object and
    can be accessed just like pre-existing tables.

  * It loads only the tables you specify, not all tables in a database.
    You can specify any mixture of pre-existing tables and tables to
    be created in the script.

  * It presumes that the first column in each table is a primary key.
    If any other column is a primary key, you must specify that manually
    with Table->columns(Primary=>\@columns).

  * It does not guess at releationships. To join tables, you must
    specify relationships with Table->has_a() and Table->has_many().

=head1 USAGE

This module is a subclass of Class::DBI::Loader::Generic so most Class::DBI::Loader methods work, see L<Class::DBI::Loader> for details.  The only method which behaves differently is new() - it has a single extra parameter.  The only new method is create_table() - it creates and sets up a new table.

=head2 new()

The call to new takes all the same parameters as Class::DBI::Loader::new() and one additional required parameter "tables".  The tables parameter is an arrayref of the tables you want to load:

    my $loader = Class::DBI::Loader::AnyDBD->new(
        dsn    => 'dbi:Whatever:',
        tables => [ 'Table1', 'Table2', 'Table3', ... 'TableN' ],
    );

A table in the list may refer to an exisiting table, in which case the module reads the table's structure and gives you a Class::DBI object for the table with the table name and columns defined.  A table in the list may also refer to a table that doesn't yet exist in the database.  In that case an empty Class::DBI object is created and you will need to use the create_table() method to use the object.

=head2 create_table()

This method can be used to issue SQL data definition commands such as CREATE TABLE and CREATE VIEW.  It sends the commands to the database, and then runs Class::DBI::AnyDBD::set_up_tables() which in turn automatically call $class->table and $class->()columns.  In other words, it both creates the table, and then sets up the table as a Class::DBI object.

The create_table() method takes a single required parameter, a valid SQL statement that creates a table or view.

  #
  # First load a non-existant table called 'MyTmp'
  # and thus create a MyTemp object
  #

  my $loader = Class::DBI::Loader::AnyDBD->new(
      dsn    => 'dbi:DBM:',
      tables => [ 'MyTmp' ],
  );

  #
  # Then use the MyTmp object to create the table itself
  # and fill the MyTmp object with the table & columns specified
  #

  MyTmp->create_table(" CREATE TEMP TABLE MyTmp (tid INT,tname TEXT) ");



  # Then use the table as we would any other Class::DBI table

  MyTmp->create({tid=>99,tname=>'Sanjay Patel'});
  print MyTmp->retrieve(99)->tname;

=head1 AUTHOR & COPYRIGHT

This module is copyright (c), 2005, Jeff Zucker, all rights reserved.

The module may be freely modified and distirbuted under the same terms as Perl itself.

=cut

sub _db_class { return 'Class::DBI::AnyDBD' }

sub new {
    my ( $class, %args ) = @_;
    if ( $args{debug} ) {
        no strict 'refs';
        *{"$class\::debug"} = sub { 1 };
    }
    my $additional = $args{additional_classes} || [];
    $additional = [$additional] unless ref $additional eq 'ARRAY';
    my $additional_base = $args{additional_base_classes} || [];
    $additional_base = [$additional_base]
      unless ref $additional_base eq 'ARRAY';
    my $left_base = $args{left_base_classes} || [];
    $left_base = [$left_base] unless ref $left_base eq 'ARRAY';
    my $tables  = $args{tables};  #jz
    $args{options}->{RaiseError} ||= 1;
    $args{options}->{PrintError} ||= 0;
    $args{options}->{AutoCommit} ||= 1;
    my $self = bless {
        _datasource =>
          [ $args{dsn}, $args{user}, $args{password}, $args{options} ],
        _namespace       => $args{namespace},
        _additional      => $additional,
        _additional_base => $additional_base,
        _left_base       => $left_base,
        _constraint      => $args{constraint} || '.*',
        _exclude         => $args{exclude},
        _relationships   => $args{relationships},
        _inflect         => $args{inflect},
        _tables          => $tables,         #jz
        CLASSES          => {},
    }, $class;
    $self->_load_classes;
    $self->_relationships if $self->{_relationships};
    $self;
}

sub _tables {
    my($self) = @_;
    my $dbh = DBI->connect(@{ $self->{_datasource} }) or croak($DBI::errstr);
    my @tables = @{ $self->{_tables} } if $self->{_tables};
       @tables = () unless @tables;
    return @tables;
}
1;
__END__

###########################
package Class::DBI::AnyDBD;
###########################
use strict;
use vars qw($VERSION);
$VERSION = "0.01";

=pod

=head1 NAME

Class::DBI::AnyDBD - set up Class::DBI tables for any DBD

=head1 SYNOPSIS

 use strict;

 package User;
 use base qw(Class::DBI::AnyDBD);
 __PACKAGE__->set_db(Main => "dbi:CSV:csv_eol=\n");  # or any other DBI DSN
 __PACKAGE__->set_up_table('User');

 package main;
 print User->retrieve(1)->uname;

=head1 DESCRIPTION

This module provides a set_up_table() method for any DBD.  It gets a list of all columns in the table and sets $class->table() and $class->columns() automatically.  It does not set up relationships or primary keys (other than defaulting to the first column as the primary key), these must be done by hand (e.g. with $class->has_a) if needed.

For DBDs that have their own Class::DBI::Foo module (e.g. Class::DBI::Pg or Class::DBI::SQLite), you are better off using that DBD-specific module rather than Class::DBI::AnyDBD.  This module is meant to support  the many DBDs that don't have specific Class::DBI modules (DBD::CSV, DBD::XBase, DBD::Amazon to name only a few).

This modules is most useful when used in conjunction with L<Class::DBI::Loader::AnyDBD>.

=head1 AUTHOR & COPYRIGHT

This module is copyright (c), 2005, Jeff Zucker, all rights reserved.

The module may be freely modified and distirbuted under the same terms as Perl itself.

=cut

use base qw/Class::DBI/;
sub set_up_table {
    my($class, $table) = @_;
    $table ||= $class;
    my $sth;
    my @columns = ();
    my @pks = ();
    eval {
        my $sth = $class->db_Main->prepare("SELECT * FROM $table WHERE 1=0");
        $sth->execute();
        @columns = @{ $sth->{NAME} };
        $sth->finish;
        @pks = ( $columns[0] );
    };
    # die $@ if $@;
    $class->table($table);
    $class->columns(Primary => @pks);
    $class->columns(All => @columns);
}
sub create_table {
    my($class,$sql)=@_;
    $class->db_Main->do($sql);
    $class->set_up_table;
}
1;
__END__