SPOPS::DBI::TypeInfo - Represent type information for a single table

 # 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 );

This is a lightweight object to maintain state about a field names and DBI types for a particular table in a particular database. It is generally used by SPOPS::SQLInterface, but it is sufficiently decoupled so you might find it useful elsewhere.

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.

This class supports a small number of 'fake' types as well so you do not have to import the DBI constants. These are:

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

More can be added as necessary, but these seemed to cover the spectrum.

These fake types can be used anywhere you set a type for a field: in the constructor, or in add_type(). So the following do the same thing:

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

new( \%params )

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";
 }

All properties are get and set with the same name.

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";

Chris Winters <chris@cwinters.com>

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