ApTest::DBI - base class for the ATM RDBMS updater libraries


Shane P. McCarron <>


Copyright 2001-2009 Applied Testing and Technology, Inc. All Rights Reserved.


The methods in this object provide a base class. Classes that subclass from this will need to provide local methods for several methods in order to have the level of integration they need. Most notably, methods SQL_tname and, if a class requires multiple tables, SQL_tlist must be overridden.

Note that any subclass MUST provide an implementation for SQL_tname. The implementation in this module raises an error and dies.


SQL_available_drivers - return a list of driver names

@list = ApTest::DBI::SQL_available_drivers() ;

Returns a list of the drivers that are installed and supported.

SQL_begin_work - start a transaction

$sql->SQL_begin_work() ;

Starts a SQL transaction. If one is already running, just increases the transaction stack depth. If transactions are not supported by the database engine, silently ignores it.

Returns nothing.

SQL_checkTable - check if the required table is available.

my $available = $sql->SQL_checkTable();

Returns true if the tables needed for the class are available, and false otherwise. Note that all tables returned by SQL_tlist must be available. Also note that if the attribute SQL_MUST_CREATE_TABLES is true, then this method will always return false. Finally, if the attribute SQL_TABLE_EXISTS returns true, then this will return true.

SQL_commit - perform a sql commit

$sql->SQL_commit() ;

Will only actually to the commit if we are at the top of the "begin_work" stack and if transactions are supported.

Returns nothing.

SQL_config - get/set configuration parameters for connection

$cfgHashRef = ApTest::DBI->SQL_config ( suite [,cfhHashRef ] ) ;

$cfhHashRef = $sql->SQL_config( suite [, cfgHashRef ] ) ;

If called outside of an object context, will return the settings. If called in an object context, will cache the settings for the suite within the object in the theory that we will need them again.

suite is the name of the suite we are working with.

cfgHashRef is an optional reference to a populated set of connection configuration information. If provided, this data is updated into the persistent store. The contains the following data items:


Boolean indicating if the SQL connection for the suite is active at all.


The name of a driver to use.


The query portion of the DSN (e.g., host=xxx;port=nnn;database=xyz).


The username to use with the database.


The password to use with the database.


A prefix string to use on tables for this suite. This optional field can be used to prevent collisions among test suites if they are all using the same storage area.


A string to be appended to the create command to do some user-defined special operation.


Boolean indicating if the SQL store is up to date and ready for use. This flag is set to false whenever the connection information is changed or the ApTest Manager field definitions are changed, and is set to true upon completion of a successful synchronize operation. It can also be set to false if a SQL operation fails during the normal course of operations.


A reference to a hash of the various tables created for this test suite. Each table has an entry in the hash. That entry is a reference to a hash of columns and column types. ApTest Manager uses this information to determine if the SQL store's structure matches that of ApTest Manager and is therefor ready for use.

Returns a reference to a hash containing the connection configuration information. If a suite has no defined connection information, returns an empty connection info structure. If the global SQL active param is 1, then returns the global data from If it is -1, returns the specific data.

SQL_connect - connect to a database

my $dbH = ApTest::DBI::SQL_connect ( suite [, cfg ] ) ;
my $dbH = $sql->SQL_connect( [suite [, cfg ] ] ) ;

suite is an optional suite name. If not provided, the "suite" method of the object will be called.

cfg is a reference to an optional SQL_config hash. If not provided, the stored configuration for the current suite will be used.

Returns a handle to a SQL database for this test suite. Returns undef if there was a problem initializing the connection.

SQL_connection - get / set the connection to the SQL data store

my $dbH = $sql->SQL_connection( [conn, ] );

conn is a handle to an active connection. If provided, this object will take advantage of an existing connection. We will also assume that we are in a connect

Returns a handle to an active SQL connection. Might initiate the connection first.

SQL_createOption - get the user-specified table create option

my $option = $sql->SQL_createOption() ;

Returns a user-defined test suite creation option, or "" if there is no defined option.

SQL_driverMap - get a mapping of known driver names

my $hashRef = ApTest::DBI::driverMap ;

Returns a reference to a hash mapping known DBD drivers to common names;

SQL_getInfo - get database tunable parameter settings

my $hashRef = ApTest::DBI::SQL_getInfo($suite) ;
my $hashRef = $sql->SQL_getInfo() ;

$suite is the name of a test suite to check against.

Returns a reference to a hash of interesting setting values for the database associated with test suite $suite, indexed by setting name. The settings retrieved include:


SQL_handle - get the database handle

my $dbH = $sql->SQL_handle() ;

SQL_handle - return the handle to the database connection

$dbH = $sql->SQL_handle() ;

SQL_initialize - drop existing tables.

$sql->SQL_initialize( );

Returns nothing.

SQL_isActive - check to see if SQL is enabled for this suite

ApTest::DBI::SQL_isActive( suite )

$sql->SQL_isActive( [ suite ] )

suite is the name of the suite to check. It is optional when used in an object context. In an object context, the object's suite method will be used to determine the active suite name.

Returns true if SQL is enabled for the suite.

SQL_isReady - check to see if SQL is ready for use

ApTest::DBI::SQL_isReady( suite )

$sql->SQL_isReady( [ suite ] )

suite is the name of the suite to check. It is optional when used in an object context. In an object context, the object's suite method will be used to determine the active suite name.

Returns true if SQL is enabled AND the underlying SQL store is ready to rock.

SQL_notifyAdmin - send a notification on error

$sql->SQL_notifyAdmin(suite, message)

suite is the test suite we are operating on, if any.

message is a message to send. Uses the ATM Notify module to send motification to interested parties.

SQL_release - ensure a SQL database is disconnected


Normally this is called by the enclosing object master release method. If a connection is initiated using some class method, however, then this method can be called and passed the DBI handle.

SQL_rollback - rollback a transaction

$sql->SQL_rollback() ;

If we are in a transaction, perform a rollback and clear the transaction stack.

Returns nothing.

SQL_statement - return a handle to a SQL statement for a table

$stH = $sql->SQL_statement( tname )

tname is the name of a table

Returns a handle of a sql insert statement for all columns of table.

SQL_timestamp - make a SQL timestamp from a perl timestamp

$tstamp = $sql->SQL_timestamp( secs [,needTime [,needSecs]] )

secs is seconds since the epoch.

needTime is a boolean for whether to include the time portion or not.

needSecs is a boolean for whether to include the seconds in the time.

Returns a timestamp formatted correctly for the connected RDBMS.

Note that if secs is not defined or is 0, then the time will be initialized to 1970-01-01 00:00:00 UTC, since that is 0 seconds since the epoch.

SQL_tlist - return a list a tables that match the pattern

@tlist = $sql->tlist();

Returns a list of table names related to the current object. This default implementation ONLY returns the base table name.

SQL_tname - get the table name

my $name = $sql->SQL_tname() ;

Note that this method MUST be overridden in a sub-class.

SQL_tname_maxlen - get the maximum table name length

$len = $sql->SQL_tname_maxlen();

Returns the maximum table name length for the underlying SQL driver.

SQL_truncate - truncate a value to fit in a limited length field

$value = $sql->SQL_truncate(tName, fName, value)

Name of the table we are operating on.


Name of the field in the table.


The value that might need truncation.

Note that fields are ONLY truncated in cases where there is a problem with the underlying SQL store of when the data in ApTest Manager somehow exceeds its limits.

SQL_basetype - return the base type that is available for a type

$btype = $sql->SQL_basetype(SQL_TYPE) ;

SQL_bailOut - flag the datastore as invalid and throw an exception

$sql->SQL_bailOut(message) ;

message is message text that will be put into the log AND reported to the end user. The log will also have a Carp::longmess entry so the entire stack trace is available.

SQL_type - find the best SQL type for an ATM type

$sqltype = $sql->SQL_type(basetype [, maxlen]) 

basetype is an ATM base type. This is one of UUID, DATE, DATETIME, INTEGER, NUMBER, and TEXT.

maxlen is the optional maximum length of the content. If it is not specified, and the resulting type takes a max length creation parameter, then the field maximum will be used by default.

Note that for a given basetype, we will only look up the supported underlying type from the driver once - after which we will cache its name and its number.

SQL_version - check the version of the underlying database

my $ver = $sql->SQL_version( [suite] ) ;

suite is an optional suite name.

Returns a string representing the version.

Copyright © 2000-2013 Applied Testing and Technology, Inc. All rights reserved.