ePages 6.10 - DE_EPAGES/Database/API/DBISybase.pm

Package DE_EPAGES::Database::API::DBISybase

Functions

atIsolationLevel
createDatabase
createDevice
createEpagesDatabase
createTempTable
defaultDevicePath
disconnect
doEscapeChars
dropAllTables
encodeSpaces
escapeChar
escapeFromExtension
existsDatabase
existsDevice
existsForeignKey
existsIndex
existsLogin
existsProcedure
getAllTableReferences
getDatabaseUsages
getIndex
getTableLockingScheme
killConnections
lockTables
modifyTableColumnDropDefault
modifyTableColumnReplaceDefault
modifyTableLockingScheme
operatingSystem
ping
processID
setDatabaseOption
setPriority
tempTableName
transaction
truncateTransactionLog

atIsolationLevel

executes the function sub at specified transaction isolation level.

Syntax
$dbi->atIsolationLevel($IsolationLevel, $Sub);
Input
$IsolationLevel (string)
values (
READ UNCOMMITTED|
READ COMMITTED|
REPEATABLE READ|
SERIALIZABLE
)
$Sub (ref.code)
function to execute select statement

createDatabase

Creates database in devices.

Syntax
$dbi->createDatabase($DBName, $DataDeviceName, $DataSize, $LogDevice, $LogSize);
Example
$dbi->createDatabase('storedb', 'storedbdatdev', '2000', 'storedblogdev', 1000 );
Input
$DatabaseName (string)
database name
$DataDeviceName (string)
data device name
$DataSize (integer)
size of data device for database in MegaByte
$LogDeviceName (string)
log device name
$LogSize (integer)
size of log device for database in MegaByte

createDevice

Creates data or log device.

Syntax
$dbi->createDevice($DeviceName, $FileName, $Size);
$dbi->createDevice($DeviceName, $FileName, $Size, $hOptions);
Example
$dbi->createDevice('storedbdat2dev', '/opt/eproot/Sybase/data/sitedbdatdev.dat', '2000' );
$dbi->createDevice('storedbdat2dev', '/opt/eproot/Sybase/data/sitedbdatdev.dat', '2000', {
    'dsync' => 'false',
    'directio' => 'true'
});
Input
$DeviceName (string)
logical device name
$FileName (string)
device file name
$Size (integer)
size of device in MegaByte
$hOptions (boolean)
sybase-specific options (dsync, directio)

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
$DBName (string)
database name
$DBLogin (string)
database login used as alias for dbo user of the new database (default: 'usr_'.$DBName)
$hOptions (ref.hash)
  • DBPassword - password for user $DBLogin (default: 'epages') - string
  • DatDir - path for new data device (default defaultDevicePath) - string
  • DatSize - size of data device in Megabytes (100) - integer
  • LogDir - path for new log device (default $DataPath) - string
  • LogSize - size of log device in Megabytes (default $DataSize/2)
  • TruncateLog - automatically clear transaction logs (default: true)

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
$Table (string)
table name and parameters

defaultDevicePath

Look at sysdevices table to find a device (saved in /data/ directory).

Syntax
$DefaultPath = $dbi->defaultDevicePath;
Return
$DefaultPath (string)
default path for new data devices

disconnect

Disconnects the underlying database handle and finish cached statements.

Syntax
$dbi->disconnect;

doEscapeChars

Returns which character should be escaped.

Syntax
$doEscapeChars = $dbi->doEscapeChars;
Return
$doEscapeChars (string)
'%', '_', '[' and ']'

dropAllTables

Removes all user tables and all stored procedures from the database (deleting all data, of course).

Syntax
$dbi->dropAllTables();

encodeSpaces

Encodes spaces to simulate Sybase-like string encoding. Currently two operations are executed on the given string:

Syntax
$string = encodeSpaces( $string );
Input
$string (string)
String to encode
Return
$string (string)
Encoded string

escapeChar

Returns the escape character ~ to escape like parameter.

Syntax
$escapeChar = $dbi->escapeChar;
Return
$escapeChar (string)
'~'

escapeFromExtension

Returns string should be added after each like. escapeChar is used to get the escape char.

Syntax
$EscapeFromExtension = $dbi->escapeFromExtension;
Return
$EscapeFromExtension (string)
escape '~'

existsDatabase

Exists the database ?

Syntax
$Exists = $dbi->existsDatabase($DBName);
Input
$DBName (string)
logical database name
Return
$Exists (boolean)
database exists

existsDevice

Exists the device ?

Syntax
$Exists = $dbi->existsDevice($DeviceName);
Input
$DeviceName (string)
logical device name
Return
$Exists (boolean)
device exists

existsForeignKey

Returns true if the foreign key exists.

Syntax
$Exists = $dbi->existsForeignKey( $Name );
Example
if( $dbi->existsForeignKey( 'sp_InsertAttribute' ) ) { ... };
Input
$Name (string)
foreign key name
Return
$Exists (boolean)
true if the foreign key exists

existsIndex

Returns true if the index exists.

Syntax
$exists = $dbi->existsIndex($Table, $IndexName);
Example
if( $dbi->existsIndex('product', 'i_product_super') ) { ... }
Input
$Table (string)
table name
$IndexName (string)
index name

existsLogin

Returns true if the login name exists.

Syntax
$Exists = $dbi->existsLogin($Login);
Input
$Login (string)
login name
Return
$Exists (boolean)
login exists

existsProcedure

Returns true if the stored procedure exists.

Syntax
$Exists = $dbi->existsProcedure( $Procedure );
Example
if( $dbi->existsProcedure( 'sp_InsertAttribute' ) ) { ... };
Input
$Procedure (string)
stored procedure name
Return
$Exists (boolean)
true if the stored procedure exists

getAllTableReferences

Determines all table references

Syntax
$aaReferences = $dbi->getAllTableReferences();
Return
$aaReferences (ref.array.array.string)
list of table references
each list element is a pair of tables [ table => master_table ].
If master_table is undef, then the table does not depend on other
tables

getDatabaseUsages

Gets the total size and usage of all databases

Syntax
$ahDatabases = $dbi->getDatabaseUsages();
Return
$ahDatabases (ref.array.hash)
list of all databases on the server with following keys:
  • Name - database name - string
  • Size - database size in MBytes - float
  • Available - free space left in MBytes - float

getIndex

Returns index information.

Syntax
$exists = $dbi->getIndex($Table, $IndexName);
Example
$dbi->getIndex('product', 'i_product_super')
Input
$Table (string)
table name
$IndexName (string)
index name

getTableLockingScheme

Gets the locking scheme of a table.

Syntax
$lockingscheme = $dbi->getTableLockingScheme($table );
Example
$lockingscheme = $dbi->getTableLockingScheme('tablename' );
Input
$table (string)
table name
Return
$lockingscheme (string)
locking scheme (datarows,datapages or allpages)

killConnections

Terminates all connections to the given database.

Syntax
$dbi->killConnections( $DBName );
Input
$DBName (string)
database name

lockTables

Locks the specified tables during execution of a code block. Notes: For the Sybase implementation, the locks will be held until the end of the transaction. If lockTables() is run within a transaction, the locks may still hold after lockTables() returns. If lockTables() is run in AutoCommit mode, a transaction is implicitely created for $cCode.

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
$ahTables (ref.array.hash)
tables and locking modes. Hash keys are:
  • table - table name - string
  • mode - locking mode (READ|WRITE) - string
$cCode (code ref)
code block

modifyTableColumnDropDefault

Deletes a Default of an existing column of an existing table $tablename.

Syntax
$dbi->modifyTableColumnDropDefault($table, $column );
Example
$table = 'tablename';
$column = 'columnname';
Input
$table (string)
table name
$column (string)
column name

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
$table (string)
table name
$column (string)
column name
$defaultvalue (string)
default value of the column

modifyTableLockingScheme

Changes the locking scheme of a table.

Syntax
$dbi->modifyTableLockingScheme($table, $lockingscheme );
Example
$dbi->modifyTableLockingScheme('tablename', 'datarows');
Input
$table (string)
table name
$lockingscheme (string)
locking scheme (datarows,datapages or allpages)

operatingSystem

Returns the operating system name. Usable for file name concatenation.

Syntax
$OperatingSystem = $dbi->operatingSystem;
Return
$OperatingSystem (string)
os name (MSWin32, Linux or Unix)

ping

test if dbi connection is alive. Implementation is copy of DBD::Sybase::ping with correct/extended sybase get results.

Syntax
$isAlive = $dbi->ping;
Return
$isAlive (boolean)
is connection alive

processID

Returns the ID of the current database process.

Syntax
my $ProcessID = $dbi->processID;
Input
$ProcessID (integer)
process id

setDatabaseOption

Set a database option.

Syntax
$Exists = $dbi->setDatabaseOption($DatabaseName, $Option, $Value);
Input
$DatabaseName (string)
logical database name
$Option (string)
option string
$Value (boolean)
is option on/off (1/0)

setPriority

Sets the priority of the current database process.

Syntax
$dbi->setPriority( $Priority )
Example
$dbi->setPriority( 'LOW' )
Input
$Priority (DateTime object)
priority (LOW|NORMAL|HIGH)

tempTableName

Returns the name of the temporary table in the correct database style.

Syntax
$dbi->tempTableName($TableName);
$dbi->tempTableName('temptable');
Input
$TableName (string)
table name
Return
$TableName (string)
correct table name

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
$cCode (code reference)
code block
$cRollback (code reference)
roll back code block (optional)

truncateTransactionLog

Clears the transaction log of the current database.

Syntax
$dbi->truncateTransactionLog();