Package DE_EPAGES::Database::API::DBI
central database interface, is based on standard perl module DBI. Use DE_EPAGES::Database::API::Connection::GetCurrentDBHandle to get the currrent connected DBI handle.
Functions
- addForeignKey
- addPrimaryKey
- addTableColumn
- atIsolationLevel
- buildQueryString
- cacheKey
- createDatabase
- createEpagesDatabase
- createIndex
- createTempTable
- currentDateTime
- datetimeParser
- dbh
- disconnect
- do
- doEscapeChars
- driverName
- dropAllTables
- dropDatabase
- dropForeignKey
- dropForeignKeyIfExists
- dropIndex
- dropIndexIfExists
- dropPrimaryKey
- dropProcedure
- dropTable
- dropTableColumn
- dropTableColumnIfExists
- enableTransactionLog
- encodeSpaces
- escapeChar
- escapeFromExtension
- execute
- existsDatabase
- existsForeignKey
- existsIndex
- existsProcedure
- existsTable
- existsTableColumn
- getAllTableReferences
- getParameterString
- getSearchCollation
- getTableLockingScheme
- getTransactions
- inTransaction
- isTransactionLogEnabled
- killConnections
- lockTables
- logTransaction
- modifyTableColumn
- modifyTableColumnDropDefault
- modifyTableColumnReplaceDefault
- modifyTableLockingScheme
- new
- ping
- primaryKeyColumns
- processID
- quoteBoolean
- quoteDateTime
- quoteFloat
- quoteInteger
- quoteParameter
- quoteString
- reconnect
- recreateIndex
- replaceLikeWildcards
- resetTransactions
- setPriority
- sortDeleteAllTables
- source
- storeName
- tempTableName
- testDateTimeRange
- transaction
- unquoteDateTime
- unquoteResultSet
addForeignKey
Adds a foreign key constaint.
Syntax |
$dbi->addForeignKey($Table, $Constraint, $aKeys, $RefTable, $aRefKeys); |
Example |
$dbi->addForeignKey('product', 'fk_product_object', ['productid'], 'object' => ['objectid']); |
Input |
|
addPrimaryKey
Adds constaint (primary key) $constraint to a table $tablename.
Syntax |
$dbi->addPrimaryKey($Table, $Constraint, $aColumnNames); |
Example |
$dbi->addPrimaryKey('product', 'pk_product', ['productid']); |
Input |
|
addTableColumn
Adds a new column to an existing table $table.
Syntax |
$dbi->addTableColumn($table, $columndefinition); |
Example |
$table = 'tablename'; $columndefinition = 'columnname varchar(30) NULL'; |
Input |
|
atIsolationLevel
executes the function sub at specified transaction isolation level.
Syntax |
$dbi->atIsolationLevel($IsolationLevel, $Sub); |
Input |
|
buildQueryString
Substitute any intermediate spaces in search string with %. e.g. " John ®§&? Doe " -> "John%Doe". String is prepared for sql like statements.
Syntax |
$DBSearchString = $dbi->buildQueryString($SearchString); |
Input |
|
Return |
|
cacheKey
Returns the indentifier of the connection used to identify the right cache. different connections to one database can use the same key.
Syntax |
$CacheKey = $dbi->cacheKey |
Return |
|
createDatabase
Creates a new database.
Syntax |
$dbi->createDatabase($DBName); |
Example |
$dbi->createDatabase('storedb'); |
Input |
|
createEpagesDatabase
Creates a database with epages default sizes and names.
Syntax |
$dbi->createEpagesDatabase($DBName, $DBLogin, $hOptions); |
Example |
$dbi->createEpagesDatabase('sitedb', 'usr_sitedb'); $dbi->createEpagesDatabase('storedb'); |
Input |
|
createIndex
Creates an index on some table columns.
Syntax |
$dbi->createIndex($Table, $IndexName, $Options, $aColumns, $hColumnOptions, $ExtraOptions); $dbi->createIndex('product', 'i_product_super', undef, ['superproductid', 'variationstring'], {'superproductid' => 'DESC', 'variationstring' => 'DESC'}); |
Input |
|
createTempTable
Creates a temporary table with the given name and parameters.
Syntax |
$dbi->createTempTable($Table); $dbi->createTempTable('temptable(objectid int not null primary key)')); |
Input |
|
currentDateTime
Returns the current date and time from the database server. Use this function to generate comparable timestamp values that are independent of the randomly selected application server.
Syntax |
$DateTime = $dbi->currentDateTime |
Example |
print $dbi->currentDateTime->ymd; |
Return |
|
datetimeParser
Returns the parser for datetime objects.
Syntax |
$datetimeParser = $dbi->datetimeParser |
Return |
|
dbh
Returns database handle of the current connection.
Syntax |
$dbh = $dbi->dbh |
Example |
my $sth = $dbi->dbh->prepare( 'SELECT * FROM dual' ); |
Return |
|
disconnect
Disconnects the underlying database handle.
Syntax |
$dbi->disconnect; |
do
Executes an SQL statement without result set, such as INSERT, UPDATE or DELETE.
Syntax |
$dbi->do( $SQL, $aParams ); $dbi->do( $SQL ); |
Example |
$dbi->do('DELETE FROM shops'); $dbi->do('DELETE FROM shops WHERE name = ?', [ $ShopName ] ); |
Input |
|
doEscapeChars
Returns which character should be escaped.
Syntax |
$doEscapeChars = $dbi->doEscapeChars; |
Return |
|
driverName
Returns the name of the DBD driver of the underlying database handle. See also $dbh->{'Driver'}->{'Name'} in the DBI specification. Returns 'Sybase' for DBD::Sybase and 'mysql' for DBD::mysql.
Syntax |
$DriverName = $dbi->driverName |
Return |
|
dropAllTables
Removes all user tables from the database (deleting all data, of cause).
Syntax |
$dbi->dropAllTables(); |
dropDatabase
Deletes a database.
Syntax |
$dbi->dropDatabase($DBName); |
Example |
$dbi->dropDatabase('storedb'); |
Input |
|
dropForeignKey
Drops constaint (foreign key) $constraint from a table $tablename.
Syntax |
$dbi->dropForeignKey($table, $constraint); |
Example |
$dbi->dropForeignKey('product', 'fk_product_object'); |
Input |
|
dropForeignKeyIfExists
Drops constaint (foreign key) $constraint from a table $tablename if this foreign key exists already in the table.
Syntax |
$dbi->dropForeignKeyIfExists($table, $constraint); |
Example |
$dbi->dropForeignKeyIfExists('product', 'fk_product_object'); |
Input |
|
dropIndex
Drops index $indexname from table $tablename.
Syntax |
$dbi->dropIndex($table, $indexname); |
Example |
$dbi->dropIndex('product', 'i_product_super'); |
Input |
|
dropIndexIfExists
Drops index $indexname from table $tablename if this index exists already in the table.
Syntax |
$dbi->dropIndexIfExists($table, $indexname); |
Example |
$dbi->dropIndexIfExists('product', 'i_product_super'); |
Input |
|
dropPrimaryKey
Drops constaint (Primary key) $constraint from a table $tablename.
Syntax |
$dbi->dropPrimaryKey($table, $constraint); |
Example |
$dbi->dropPrimaryKey('product', 'pk_product'); |
Input |
|
dropProcedure
Removes a stored procedure from the database
Syntax |
$dbi->dropProcedure($Procedure); |
Example |
$dbi->dropProcedure('sp_InsertAttribute'); |
Input |
|
dropTable
Removes a table from the database (deleting all data, of cause).
Syntax |
$dbi->dropTable($Table); |
Example |
$dbi->dropTable('product'); |
Input |
|
dropTableColumn
Drops an existing column $column from a table $tablename.
Syntax |
$dbi->dropTableColumn($table, $column); |
Example |
$table = 'tablename'; $column = 'columnname'; |
Input |
|
dropTableColumnIfExists
Drops an existing column $column from a table $tablename.
Syntax |
$dbi->dropTableColumnIfExists($table, $column); |
Example |
$table = 'tablename'; $column = 'columnname'; |
Input |
|
enableTransactionLog
Activates or deactivates transaction logging.
Syntax |
$dbi->enableTransactionLog($enabled); |
Input |
|
encodeSpaces
Encodes spaces to simulate database-like string encoding. The global version does nothing at all.
Syntax |
$string = encodeSpaces( $string ); |
Input |
|
Return |
|
escapeChar
Returns the escape character '' to escape like parameter.
Syntax |
$escapeChar = $dbi->escapeChar; |
Return |
|
escapeFromExtension
Returns string should be added after each like. escapeChar is used to get the escape char.
Syntax |
$EscapeFromExtension = $dbi->escapeFromExtension; |
Return |
|
execute
Executes an SQL statement and fetches all results.
Syntax |
$aRows = $dbi->execute( $SQL, $aParams, $aDttps ); $aRows = $dbi->execute( $SQL ); |
Example |
$aRows = $dbi->execute('SELECT * FROM shops'); $aRows = $dbi->execute('SELECT * FROM shops WHERE name = ?', [ $ShopName ] ); print "Shop id: " . $aRow->[0]->[0]; $aRows = $dbi->execute('SELECT siteid, creationdate FROM object WHERE objectid = ?', [ $ObjectID ], [ 'Integer', 'DateTime']); print "Shop id: " . $aRow->[0]->[0]; |
Input |
|
Return |
|
existsDatabase
Returns true if the database exists.
Syntax |
$Exists = $dbi->existsDatabase($DBName); |
Input |
|
Return |
|
existsForeignKey
Returns true if the foreign key exists.
Syntax |
$Exists = $dbi->existsForeignKey( $Name ); |
Example |
if( $dbi->existsForeignKey( 'sp_InsertAttribute' ) ) { ... }; |
Input |
|
Return |
|
existsIndex
Returns true if the index exists.
Syntax |
$exists = $dbi->existsIndex($Table, $IndexName); |
Example |
if( $dbi->existsIndex('product', 'i_product_super') ) { ... } |
Input |
|
existsProcedure
Returns true if the stored procedure exists.
Syntax |
$Exists = $dbi->existsProcedure( $Procedure ); |
Example |
if( $dbi->existsProcedure( 'sp_InsertAttribute' ) ) { ... }; |
Input |
|
Return |
|
existsTable
Returns true if the database table exists.
Syntax |
$Exists = $dbi->existsTable( $Table ); |
Example |
if( $dbi->existsTable( 'sysobjects' ) ) { ... }; |
Input |
|
Return |
|
existsTableColumn
Returns true if the database table column exists.
Syntax |
$Exists = $dbi->existsTableColumn( $Table, $Column ); |
Example |
if( $dbi->existsTableColumn( 'sysobjects', 'name' ) ) { ... }; |
Input |
|
Return |
|
getAllTableReferences
Determines all table references
Syntax |
$aaReferences = $dbi->getAllTableReferences(); |
Return |
|
getParameterString
Create the parameter string for a procedure call, for example "@Name='MyName', @Number=42". Tests and quotes the parameters according to the data type spefification given in $hTypes.
Syntax |
$ProcParameter = $dbi->getParameterString($hTypes, $hValues); |
Example |
$ProcParameter = $dbi->getParameterString({'Name' => 'String;NotNull', 'Descr' => 'String', 'AnyNumber' => 'Integer' }, {'Name'=>'TestName','AnyNumber'=> 1}); $ProcParameter = "@Name='TestName', @AnyNumber=1"; |
Input |
|
Return |
|
getSearchCollation
Returns additional SQL syntax for specification of collations for searches. This is ususally used for mysql to return 'COLLATE utf8_general_ci'
Syntax |
$SearchCollation = $dbi->getSearchCollation; |
Return |
|
getTableLockingScheme
Gets the locking scheme of a table.
Syntax |
$lockingscheme = $dbi->getTableLockingScheme($table ); |
Example |
$lockingscheme = $dbi->getTableLockingScheme('tablename' ); |
Input |
|
Return |
|
getTransactions
Returns the logged transactions, use (logTransactions to push statements to the collection.
Syntax |
$aStatements = $dbi->getTransactions; |
Return |
|
inTransaction
Returns true if database connection is in transaction mode.
Syntax |
$IsInTransaction = $dbi->inTransaction; |
Return |
|
isTransactionLogEnabled
Returns whether transaction logging is currently enabled.
Syntax |
$enabled = $dbi->isTransactionLogEnabled; |
Return |
|
killConnections
Terminates all connections to the given database.
Syntax |
$dbi->killConnections( $DBName ); |
Input |
|
lockTables
Locks the specified tables during execution of a code block. The actual implementation is database-specific. The default implementation only executeds $cCode, but does not lock any tables.
Syntax |
$dbi->lockTables( $ahTables, $cCode ); |
Example |
$dbi->lockTables( [ { 'table' => 'test', 'mode' => 'WRITE'}, { 'table' => 'test2', 'mode' => 'WRITE'}, ], sub { $dbi->do('DELETE FROM test2 WHERE id = ?', $id); $dbi->do('DELETE FROM test WHERE id = ?', $id); }); |
Input |
|
logTransaction
Logs the transaction of this dbi and collect this at an internal structure. Use getTransactions and resetTransactions for your own purpose.
Syntax |
$dbi->logTransaction($statement, $aParameter, $aTypes); |
Input |
|
modifyTableColumn
Modifies an existing column of an existing table $tablename.
Syntax |
$dbi->modifyTableColumn($table, $columndefinition); |
Example |
$table = 'tablename'; $columndefinition contains sql code to create a column |
Input |
|
modifyTableColumnDropDefault
Deletes a Default of an existing column of an existing table $tablename.
Syntax |
$dbi->modifyTableColumnDropDefault($table, $column ); |
Example |
$table = 'tablename'; $column = 'columnname'; |
Input |
|
modifyTableColumnReplaceDefault
Modifies a default of an existing column $column of an existing table $tablename.
Syntax |
$dbi->modifyTableColumnReplaceDefault($table, $column ,$defaultvalue); |
Example |
$table = 'tablename'; $column = 'columnname'; $defaultvalue = 0 use functions quote* to quote the value before |
Input |
|
modifyTableLockingScheme
Changes the locking scheme of a table.
Syntax |
$dbi->modifyTableLockingScheme($table, $lockingscheme ); |
Example |
$dbi->modifyTableLockingScheme('tablename', 'datarows'); |
Input |
|
new
Creates a database connection using the supplied connection parameters.
Syntax |
DE_EPAGES::Database::API::DBI->new( $DataSource, $User, $Password, $CacheKey ); |
Example |
DE_EPAGES::Database::API::DBI->new( 'dbi:mysql:storedb', 'root', 'geheim', 'DAL' ); |
Input |
|
ping
test if dbi connection is alive.
Syntax |
$isAlive = $dbi->ping; |
Return |
|
primaryKeyColumns
Returns a list of columns of the primary of a table. Retruns an empty list if the table does not have a primary key.
Syntax |
@Columns = $dbi->primaryKeyColumns($Table); |
Input |
|
Return |
|
processID
Returns the ID of the current database process. Returns 0 if the DBMS does not support this feature.
Syntax |
my $ProcessID = $dbi->processID; |
Input |
|
quoteBoolean
Formats an boolean number for use as argument in an SQL statement.
This function does not copy the parameters locally, because it is optimized for speed.
Syntax |
$StrBool = $dbi->quoteBoolean($Bool); |
Example |
$StrBool = $dbi->quoteBoolean(1); |
Input |
|
Return |
|
quoteDateTime
Converts a DateTime object to a string representation suitable for the as argument in an SQL statement. Returns undef if the
Syntax |
$StrDateTime = $dbi->quoteDateTime( $DateTime ); |
Example |
$StrDateTime = $dbi->quoteDateTime( DateTime->now ); |
Input |
|
Return |
|
quoteFloat
Formats a number for use as argument in an SQL statement.
This function does not copy the parameters locally, because it is optimized for speed.
Syntax |
$StrFloat = $dbi->quoteFloat($Float); |
Example |
$StrFloat = $dbi->quoteFloat(1223.4534); |
Input |
|
Return |
|
quoteInteger
Formats an integer number for use as argument in an SQL statement.
This function does not copy the parameters locally, because it is optimized for speed.
Syntax |
$StrInteger = $dbi->quoteInteger($Integer); |
Example |
$StrInteger = $dbi->quoteInteger(1223); |
Input |
|
Return |
|
quoteParameter
Tests and quotes the parameters according to the data type spefification.
Syntax |
$Parameter = $dbi->quoteParameter($Type, $Value); |
Example |
$Parameter = $dbi->quoteParameter('String', "don't"); |
Input |
|
Return |
|
quoteString
Formats a string for use as argument in an SQL statement.
This function does not copy the parameters locally, because it is optimized for speed.
Syntax |
$result = $dbi->quoteString($instring); |
Example |
$qstring = $dbi->quoteString("Don't worry."); $dbi->do( "DELETE FROM shops WHERE description = $qstring" ); |
Input |
|
Return |
|
reconnect
Rebuilds a database connection, usefull if connect was closed by server.
Syntax |
my $dbh = $dbi->reconnect(); |
Return |
|
recreateIndex
Creates an index on some table columns, drop it before if it still exists.
Syntax |
$dbi->recreateIndex($Table, $IndexName, $Options, $aColumns, $hColumnOptions, $ExtraOptions); $dbi->recreateIndex('product', 'i_product_super', undef, ['superproductid', 'variationstring'], {'superproductid' => 'DESC', 'variationstring' => 'DESC'}); |
Input |
|
replaceLikeWildcards
Replace chars used at where clause operator like (%_). escapeChar and doEscapeChars are used.
Syntax |
$likestring = $dbi->replaceLikeWildcards($string); |
Input |
|
Return |
|
resetTransactions
Clears the statement collection.
Syntax |
$dbi->resetTransactions; |
setPriority
Sets the priority of the current database process.
Syntax |
$dbi->setPriority( $Priority ) |
Example |
$dbi->setPriority( 'LOW' ) |
Input |
|
sortDeleteAllTables
Sort tables by references. Deleting tables in this order is possible.
Syntax |
$aTables = $dbi->sortDeleteAllTables($aaReferences); |
Example |
$aTables = $dbi->sortDeleteAllTables([['product','object'],['object','object'],['object_nextid', undef]]); $aTables = $dbi->sortDeleteAllTables( $dbi->getAllTableReferences ); |
Input |
|
Return |
|
source
Returns connection string to current dbi.
Syntax |
$source = $dbi->source |
Return |
|
storeName
Returns the storename of the connection.
Syntax |
$storeName = $dbi->storeName |
Return |
|
tempTableName
Returns the name of the temporary table in the correct database style.
Syntax |
$dbi->tempTableName($TableName); $dbi->tempTableName('temptable'); |
Input |
|
Return |
|
testDateTimeRange
Test a date/time string for a passes range. Returns Error if the date/time string is to large or to small.
Syntax |
$DateTime = $dbi->testDateTimeRange($StrDateTime); |
Example |
$DateTime = $dbi->testDateTimeRange('20031127/09:56:57'); |
Input |
|
transaction
Executes a code block in a transaction.
Syntax |
$dbi->transaction( $cCode, $cRollback ); |
Example |
$dbi->transaction( sub { $dbi->do( "DELETE FROM objects WHERE classid = 25" ); $dbi->do( "DELETE FROM classes WHERE classid = 25" ); } ); |
Input |
|
unquoteDateTime
Parses a date/time string from a database result set. Returns undef if the database returned a NULL value.
Syntax |
$DateTime = $dbi->unquoteDateTime($StrDateTime); |
Example |
$DateTime = $dbi->unquoteDateTime('20031127/09:56:57'); |
Input |
|
Return |
|
unquoteResultSet
Formats a date/time string to datetime object, and converts string to utf-8 perl strings. If the $ColumnNames are defined the rows will be converted to hashes.
Syntax |
$aaResult = $dbi->unquoteResultSet($aaResult, $aDttps); $ahResult = $dbi->unquoteResultSet($aaResult, $aDttps, $ColumnNames); |
Example |
$result = $self->unquoteResultSet($sth->fetchall_arrayref, ['Integer', 'DateTime', 'String']); |
Input |
|
Return |
|