Re: how to find out column types?

[prev] [thread] [next] [Date index for 2004/12/28]

This is a multi-part message in MIME format.
--------------060404040301070003000506
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Ted Zlatanov wrote:
> Here's my version of Class::DBI::Plugin::Type's import() function.
> This is pretty much the whole of the module minus the docs.  I just
> add a $full parameter, which gets you the whole information hash
> back.  Without $full, you get the current behavior.

Thanks. I've reworked it to add a second method, column_info(). I prefer 
an extra method over an optional parameter. See attached.

I'm not sure that returning the type_name for column_info() is intuitive 
for databases like SQLite. I might change it to return undef where the 
column info hash isn't available.

> Simon: sorry for ruining your nice map() call.  Feel free to
> reassemble it if you prefer.
> 
> I haven't been able to figure out how to use primary_key(); if anyone
> can show me how to incorporate that I would appreciate it greatly.  I
> think I am using it correctly but I get an empty list every time, so I
> must be missing something.  Getting the primary key would be really
> useful to me.

I'm not sure that this is something CDBI::Plugin::Type should do. 
Class::DBI already provides a primary_columns(). The Class::DBI::<DBD> 
classes also query the database for a list of primary keys but don't 
provide any accessors for that data. For some reason, they don't use 
DBI->primary_key() internally. They each query the primary keys in a 
database-dependent manner.

--simonflk

--------------060404040301070003000506
Content-Type: text/x-perl;
 name="Type.pm"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="Type.pm"

# $Id: Type.pm,v 1.2 2004/12/28 20:56:07 simonflack Exp $
package Class::DBI::Plugin::Type;

use strict;
use warnings;
use Class::Data::Inheritable;

our $VERSION = '0.02b';

sub import {
    no strict 'refs';
    my $caller = caller();
    #if ($caller->isa("Class::DBI::mysql") and
    #    $caller->can("column_type")) {
    #    return; # My work here is done
    #}

    return if $caller->can("sql_dummy");
    $caller->set_sql(dummy => <<'');
        SELECT *
        FROM __TABLE__
        WHERE 1=0

    $caller->mk_classdata("_types");
    no warnings 'redefine';
    *{$caller."::_types"} = sub {
        my $class = shift;

        # Query the datasource for type info the first time this is called
        if (my $types = $class->__types_accessor()) {
            return $types;
        } else {
            my $sth = $class->sql_dummy;
            $sth->execute;
            my %hash;
            @hash{@{$sth->{NAME}}} = 
                map {
                    my $info = scalar $class->db_Main->type_info($_);
                    if ($info) { [$info->{TYPE_NAME}, $info] }
                    else { [$_, $_] } # Typeless databases (SQLite)
                } @{$sth->{TYPE}};
            $sth->finish;
            $class->__types_accessor(\%hash);
            return \%hash;
        }
    };

    *{$caller."::column_type"} = sub {
        my ($self, $column) = @_;
        return unless $self->_types->{$column};
        return $self->_types->{$column}[0];
    };

    *{$caller."::column_info"} = sub {
        my ($self, $column) = @_;
        return unless $self->_types->{$column};
        return $self->_types->{$column}[1];
    };

}

1;
__END__

=head1 NAME

Class::DBI::Plugin::Type - Determine type information for columns

=head1 SYNOPSIS

  package Music::Artist;
  use base 'Class::DBI';
  use Class::DBI::Plugin::Type;
  Music::Artist->table('artist');
  Music::Artist->columns(All => qw/artistid name/);

  print Music::Artist->column_type("artistid"); # integer

=head1 DESCRIPTION

This module allows C<Class::DBI>-based classes to query their columns
for data type information in a database-independent manner.

=head1 METHODS

This module adds the following methods to your C<Class::DBI>-based classes:

=head2 column_type

Returns the data type name for the specified column. E.g. integer.

    my $type = Music::Artist->column_type('name');

=head2 column_info

Returns the C<DBI-E<gt>type_info> for the specified column.

    my $info = Music::Artist->column_info('name');
    print $info->{COLUMN_SIZE};

=head1 SEE ALSO

L<Class::DBI::AsForm>

=head1 AUTHOR

This module is now maintained by Simon Flack, E<lt>simonflk#cpan.orgE<gt>

=head1 AUTHOR EMERITUS

Simon Cozens, E<lt>simon#cpan.orgE<gt>

=head1 COPYRIGHT AND LICENSE

Copyright 2004 by Simon Cozens

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

This module was generously sponsored by the Perl Foundation.

=cut

--------------060404040301070003000506--