Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
SPOPS::DBI::TypeInfo(3User Contributed Perl DocumentatiSPOPS::DBI::TypeInfo(3)

       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.

   Fake	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

	$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' ],

       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.


	$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

	 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.


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


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


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


       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.


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


	$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 <>

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

perl v5.32.0			  2004-02-26	       SPOPS::DBI::TypeInfo(3)


Want to link to this manual page? Use this URL:

home | help