view release on metacpan or  search on metacpan

lib/DBI/BabyConnect.pm  view on Meta::CPAN

=head2 ON_FAILED_DBIEXECUTE_ROLLBACK_AND_EXIT

You may not need to set ON_FAILED_DBIEXECUTE_ROLLBACK_AND_EXIT
to 1 to rollback if you call exit() from within your program
(since exit() will eventually call DBI::BabyConnect::DESTROY),
or if you end the class or program that uses DBI::BabyConnect
(as the DESTROY is the last to be called even in Apache::BabyConnect)
In either case, whenever DESTROY is called, if the autorollback is 1 and autocommit is 0
and the DBI execute has returned with failure, then the rollback is in effect.

The caller can always catch and check the return value of a DBI::BabyConnect method
to see if it has failed a DBI execute. Typically DBI::BabyConnect methods return undef
whenever a DBI execute fails and therefore the caller can check the return
value and decide on whether to call the DBI::BabyConnect object method rollback himself or not,
therefore allowing the caller to continue to work with the instance of DBI::BabyConnect object
and its open DBI connection.
Yet, you can configure the behavior of the DBI::BabyConnect object methods globally
and tell the object methods to automatically rollback and exit on failure.

This option is settable and will work only if AutoRollback is in effect for the
DBI, because DBI::BabyConnect objects delegate all rollbacks to the DBI itself.

  DBI rollback is in effect if and only if:
  RaiseError is 0 (it should be off because otherwise the DBI would have exited earlier due to the error)
  AutoCommit is 0 (DBI will have no effect on rollback is AutoCommit is set to 1)

DBI::BabyConnect will keep track of the success or failure of DBI execute(), hence deciding on
what to do on failure.

DBI will not exit if the conditions on the rollback are not met, but it will
continue without effectively rolling back.

For these DBI::BabyConnect objects that have been instantiated by loading the
DBI::BabyConnect with PERSISTENT_OBJECT_ENABLED set to 1

  use DBI::BabyConnect 1, 1;

this option will do a rollback but the exit() is redirected to Apache::exit() as it
is documented by mod_perl, in which case only the perl script will exit at this point.
See eg/perl/testrollback.pl

If for any reason the HTTP child is terminated, or the CORE::exit() is called, or CORE::die()
is called, or anything that will terminate the program and call the DESTROY of a DBI::BabyConnect
instance, then this DESTROY will still check to see if a rollback conditions are met
to do an effective rollback; this is different than the behavior of other application
that do persistence using Apache, as the mechanism of rollback is carried externally of Apache
handlers and is being dispatched within the DBI::BabyConnect object itself.

=head2 DBSETTING_FORCE_SINGLESPACE_FOR_EMPTY_STRING

When inserting new data, a scalar that refers to an empty string "" will normally
keep the default value of the attribute in the database, i.e. NULL. You can
set DBSETTING_FORCE_SINGLESPACE_FOR_EMPTY_STRING=1 to force the writing of
a single space instead of keeping the default NULL.

=head2 ENABLE_STATISTICS_ON_DO

When ENABLE_STATISTICS_ON_DO is set to 1, a DBI::BabyConnect object maintains
a table to hold statistics about the L<"do">'s requested by identifying each entry
with the query string being passed to the L<"do"> method. The programmer can
then call get_do_stat() to get the object that hold the statistics.
Do not enable this unless you need to collect statistics, for instance in
data warehousing environment the queries to do() are limited in format
and are time consuming, so you may desire to collect statistics about these
do()'s queries.


=head2 ENABLE_STATISTICS_ON_SPC

When ENABLE_STATISTICS_ON_SPC is set to 1, a DBI::BabyConnect object maintains
a table to hold statistics about the spc()'s requested by identifying each entry
with the stored procedure name passed to the spc() method. The programmer can
then call get_spc_stat() to get the object that hold the statistics.
Do not enable this unless you need to collect statistics, for instance in
data warehousing environment the stored procedure names passed spc() are limited in number
and are time consuming, so you may desire to collect statistics about these
spc()'s stored procedures.


=head1 Database Descriptors File

The databases.pl file holds a set of database descriptors. The database descriptor
is an object whose attributes describe a specific connection to a data source, that is
to what database to connect, how to connect, and to handle the connection
programmatically in case of failure.



	BABYDB_001 =>
	{
		Driver => 'Mysql',
		Server=>'',
		UserName=>'admin',
		Password=>'adminxyz',
		# Mysql defines a database name, CAREFUL it may be case sensitive!
		DataName=>'BABYDB',
		PrintError=>1,
		RaiseError=>1,
		AutoRollback => 1,
		AutoCommit=>1,
		LongTruncOk=>1,
		LongReadLen => 900000,
	}

A descriptor specifies the driver name, the database name, and how to authenticate to connect
to the database. DBI::BabyConnect allows you to have multiple descriptors each of which
can be used by a DBI::BabyConnect object instance to connect to the data source.

Because it is possible to have multiple descriptors, and you can instantiate multiple
DBI::BabyConnect objects, then it is possible to connect to several data sources 
from a single program. For example, it is possible to connect concurrently from the same
program to MySQL database located on a server A, to another MySQL database located
on server B, to an Oracle database located on server C, and so on.

=head2 Database Handle Attributes

For each of the active database connection, there are six attributes
that are defined:

=over 2

=item 1 
RaiseError 

=item 2
PrintError 

=item 3
AutoCommit 

=item 4
AutoRollback 

lib/DBI/BabyConnect.pm  view on Meta::CPAN

The following three statistical functions collect statistics per DBI::BabyConnect object:
get_do_stats, get_spc_stats, get_running_time

The DBI::BabyConnect::getStatCC returns statistics about all DBI::BabyConnect objects
whenever using DBI::BabyConnect with connection caching and persistence.

DBI::BabyConnect with connection caching and persistence is being used by L<Apache::BabyConnect>.

=for comment Refer to Apache::BabyConnect for more information on using DBI::BabyConnect with Apache2 MD2.

=head2 getStatCC

getStatCC() returns the statistics collected on the open DBI handles owned by
the DBI::BabyConnect objects. The caching of the handles will only work whenever
you instantiate the DBI::BabyConnect by enabling ENABLE_CACHING and PERSISTENT_OBJECT_ENABLED
For example:
  use DBI::BabyConnect 1,1;
will load the DBI::BabyConnect and set ENABLE_CACHING and PERSISTENT_OBJECT_ENABLED to
true.

use DBI::BabyConnect (1,1) is typically called whenever using L<Apache::BabyConnect>, or
whenever loading the module from a Perl script that is run under mod_perl.

 The method getStatCC() takes one optional argument:
 - if you do not pass any argument, then this method will return a string containing the statistics collected on all open handles
 - if you pass a hash reference as the first argument then the statistics table is copied to this hash reference
   and the method will also return the reference to that hash
 - if you pass anything else (as a string), then the method will return a hash reference containing the statistics collected
   on the cached descriptor that matches that string.

See eg/perl/statcc.pl for an example.

=head2 get_running_time

get_running_time() returns a string containing time related information about the DBI::BabyConnect object.
The string returned has the following format:
cumulative-system-time / added-system-time / total-run-time

All three times are expressed in seconds and 1/100 second.
cumulative-system-time represents the system+user time used by the DBI::BabyConnect object
added-system-time represents the system+user time slices added per each DBI method call, and they hould add up to be close to cumulative-system-time
total-run-time represents the time since the DBI::BabyConnect object was instantiated

=head2 htmlStatCC

htmlStatCC() prints in HTML format the statistics collected on the open DBI handles owned by
the DBI::BabyConnect objects. This function is provided so that you can quickly print the
statistical table of all DBI::BabyConnect objects that have been cached by a specific process,
such as the http server process, or one of its child process.

The printing is in HTML format, therefore you need to use this function from a Perl script
that is served under Apache. For an example, see any of the following scripts
eg/perl/testbaby.pl, eg/perl/testcache.pl, or eg/perl/onemore.pl.

See L<"getStatCC"> for description of this the cached statistical table of DBI::BabyConnect
objects.

=head2 get_do_stats 

 This method get_do_stat() takes one optional argument:
 - if you do not pass any argument, then this method will return a string containing the statistics collected
 - if you pass a hash reference as the first argument then the do()'s statistics table is copied to this hash reference
   and the method will also return the reference to that hash
 - if you pass anything else (as a string), then the method will return a hash reference containing the statistics collected
   on the do() query that match that string.

get_do_stat() returns the statistics collected on the do() method. You should have
enabled to collect the statistics by seting L<"ENABLE_STATISTICS_ON_DO"> to 1, otherwise
the statictics table is empty.
Before setting ENABLE_STATISTICS_ON_DO to 1, just know what you are doing otherwise
you will imply a huge penalty on the DBI::BabyConnect object by acquiring an unecessary
data structure to hold the statistics of all do()'s statement. Refer to the section
L<"ENABLE_STATISTICS_ON_DO">.

I added the ENABLE_STATISTICS_ON_DO for some system integrators working in data warehouse,
where the do() robots are usually repetitive for the same set of queries and are time consuming.
If your do() query is taking too long, and your do() queries are limited in number, and
you want to know how many time the same query is being called (and how much system time it is
consuming) then enable ENABLE_STATISTICS_ON_DO, and use the method get_do_stat() to get the
statistics of all your do()'s that have been invoked by a DBI::BabyConnect object.

=head2 get_spc_stats 

Similar to L<"get_do_stats"> but statistics are collected on Stored Procedures whenever
you call spc().

=head1 Database Status and Schema

DBI::BabyConnect provides several functions that can request meta data and schema
information about tables that resides in the data source
to which a DBI::BabyConnect is connected. These functions provide
statistics about the meta data saved within the database, and about
the schema of the database tables. While these functions should be generic and
work with any database, currently they support only MySQL, and they have been
tested with mysql  Ver 14.12 Distrib 5.0.27.

=head2 dbschema

  dbschema( $database, $tablelike )

C<dbschema()> retrieves information about tables from MySQL INFORMATION_SCHEMA.TABLES,
matching these tables that pertains to the specified $database and whose
names are like $tablelike.

Use this method with MySQL to quickly reveal inserts, updates, or any changes on
specific tables. This method may not work with any MySQL release, but it has
been tested with Ver 14.12 Distrib 5.0.27.

For example, given the database name BABYDB, get the status of all these table names
containing TABL in their names. See eg/dbschema.pl for an example.

  print $bbconn-> dbschema('BABYDB','TABL');


=head2 snapTablesInfo

C<snapTablesInfo()> list all the tables that are defined within the database
to which the DBI::BabyConnect object is connected. See eg/tablesinfo.pl for an example.

=head2 snapTableDescription

  snapTableDescription( $table )

C<snapTableDescription()> returns the description of the specified table. However,
the table should be defined within the database that the DBI::BabyConnect object 
is connected to. See eg/tabledescription.pl for an example.

=head2 snapTableMetadata

  snapTableMetadata( $table )

C<snapTableMetadata()> returns a string describing the meta data of a table. However,
the table should be defined within the database that the DBI::BabyConnect object 
is connected to. See eg/tablemeta.pl for an example.

=head2 strucTableMetadata

  strucTableMetadata( $table )