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.
fetch_types()
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:
add_type()
$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.
You can pass in parallel arrayrefs in fields and types.
fields
types
You can pass a hashref of fields to values in map.
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:
$field
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.
$type
Fake Types
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:
$dbh
$sql
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.
table
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.)
get_fields()
get_type()
Returns: the object, which allows method chaining as a shortcut.
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.
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.
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.)
$type_info->database( "production" ); print "Database for metadata: ", $type_info->database(), "\n";
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.
$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.
To install SPOPS, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SPOPS
CPAN shell
perl -MCPAN -e shell install SPOPS
For more information on module installation, please visit the detailed CPAN module installation guide.