NAME

SYNOPSIS

 # Do everything at initialization with DBI types

 my $type_info = SPOPS::DBI::TypeInfo->new({
                     database => 'foo',
                     table    => 'cards',
                     fields   => [ 'face', 'value', 'color' ],
                     types    => [ SQL_VARCHAR, SQL_INTEGER, SQL_VARCHAR ] });

 # Do everything at initialization with fake types

 my $type_info = SPOPS::DBI::TypeInfo->new({
                     database => 'foo',
                     table    => 'cards',
                     fields   => [ 'face', 'value', 'color' ],
                     types    => [ 'char', 'int', 'char' ] });
 ...

 # Cycle through the fields and find the types

 print "Information for ",
       join( '.', $type_info->database, $type_info->table ), "\n";
 foreach my $field ( $type_info->get_fields ) {
     print "Field $field is type ", $type_info->get_type( $field ), "\n";
 }

 # Get the field/type information from the database

 my $type_info = SPOPS::DBI::TypeInfo->new({ database => 'db',
                                             table    => 'MyTable' });
 my $dbh = my_function_to_get_database_handle( ... );
 my $sql = qq/ SELECT * FROM MyTable WHERE 1 = 0 /;
 $type_info->fetch_types( $dbh, $sql );
 print "Type of 'foo' is ", $type_info->get_type( 'foo' );

 # Do the above at one time

 my $dbh = my_function_to_get_database_handle( ... );
 my $type_info = SPOPS::DBI::TypeInfo->new({ table    => 'MyTable' })
                                     ->fetch_types( $dbh );

DESCRIPTION

It is case-insensitive when finding the type to match a field, but stores the fields in the case added or, if you use "fetch_types()", the case the database reports.

   Fake     DBI
   ====================
   int   -> SQL_INTEGER
   num   -> SQL_NUMERIC
   float -> SQL_FLOAT
   char  -> SQL_VARCHAR
   date  -> SQL_DATE

 $type_info->add_type( 'foo', SQL_NUMERIC );
 $type_info->add_type( 'foo', 'num' );

METHODS

Create a new object. There are two types of parameters: the object properties, and the fields and types to be used. The properties are listed in PROPERTIES -- just pass in a value for a property by its name and it will be set.

You have two options for the field names and values.

1.

You can pass in parallel arrayrefs in "fields" and "types".

2.

You can pass a hashref of fields to values in "map".

Example of parallel fields and types:

 my $type_info = SPOPS::DBI::TypeInfo->new({
                    table => 'mytable',
                    fields => [ 'foo', 'bar', 'baz' ],
                    types  => [ SQL_INTEGER, SQL_VARCHAR, SQL_TIMESTAMP ] });

Example of a map:

 my $type_info = SPOPS::DBI::TypeInfo->new({
                    table => 'mytable',
                    map   => { foo => SQL_INTEGER,
                               bar => SQL_VARCHAR,
                               baz => SQL_TIMESTAMP } });,

Returns: new object instance.

get_type( $field )

Retrieves the DBI type for $field. The case of $field does not matter, so the following will return the same value:

 my $type = $type_info->get_type( 'first_name' );
 my $type = $type_info->get_type( 'FIRST_NAME' );
 my $type = $type_info->get_type( 'First_Name' );

Returns: the DBI type for $field. If $field is not registered with this object, returns undef.

add_type( $field, $type )

Adds the type $type for field $field to the object. As noted in "Fake Types", the value for $type may be a 'fake' type which will then get mapped to a DBI type.

If a type for $field has already been set, no action is taken but a warning is issued.

Examples:

 $type_info->add_type( 'first_name', SQL_VARCHAR ); # ok
 $type_info->add_type( 'last_name', 'char' );       # ok
 $type_info->add_type( 'birthdate', SQL_DATE );     # ok
 $type_info->add_type( 'BIRTHDATE', SQL_DATE );     # results in warning
 $type_info->add_type( 'FIRST_NAME', SQL_INTEGER ); # results in warning

Returns: type set for $field

fetch_types( $dbh, [ $sql ] )

Retrieve fields and types from the database, given the database handle $dbh and the SQL $sql. If $sql is not provided we try to use a common one:

  SELECT * FROM $self->table WHERE 1 = 0

If the "table" property is not set and no $sql is passed in the method throws an exception.

Any failures to prepare/execute the query result in a thrown SPOPS::Exception::DBI object.

The object will store the fields as the database returns them, so a call to "get_fields()" may return the fields in an unknown order/case. (Getting the type via "get_type()" will still work, however.)

Returns: the object, which allows method chaining as a shortcut.

get_fields()

Returns a list of fields currently registered with this object. They are returned in the order they were added.

Example:

 print "Fields in type info object: ", join( ", ", $type_info->get_fields );

get_types()

Returns a list of types currently registered with this object. They are returned in the order they were added.

Example:

 print "Types in type info object: ", join( ", ", $type_info->get_types );

as_hash()

Returns the fields and types as a simple hash. The case of the field should be the same as it was specified or retrieved from the database.

Example:

 my %type_map = $type_info->as_hash;
 foreach my $field ( keys %type_map ) {
     print "Field $field is type $type_map{ $field }\n";
 }

PROPERTIES

database

Name of the database this object is representing. (Optional, may be empty.)

Example:

 $type_info->database( "production" );
 print "Database for metadata: ", $type_info->database(), "\n";

table

Name of the table this object is representing. This is optional unless you call "fetch_types()" without a second argument ($sql), since the object will try to create default SQL to find fieldnames and types by using the table name.

Example:

 $type_info->table( "customers" );
 print "Table for metadata: ", $type_info->table(), "\n";

AUTHORS

Thanks to Ray Zimmerman <rz10@cornell.edu> for pointing out the need for this module's functionality.