NAME
Kernel::System::DB – Global database interface
DESCRIPTION
All database functions to connect/insert/update/delete/… to a database.
METHODS
new()
create database object, with database connect.. Usually you do not use it directly, instead use:
use Kernel::System::ObjectManager;
local $Kernel::OM = Kernel::System::ObjectManager->new(
'Kernel::System::DB' => {
# if you don't supply the following parameters, the ones found in
# Kernel/Config.pm are used instead:
DatabaseDSN => 'DBI:odbc:database=123;host=localhost;',
DatabaseUser => 'user',
DatabasePw => 'somepass',
Type => 'mysql',
Attribute => {
LongTruncOk => 1,
LongReadLen => 100*1024,
},
},
);
my $DBObject = $Kernel::OM->Get('Kernel::System::DB');Connect()
to connect to a database
$DBObject->Connect();Disconnect()
to disconnect from a database
$DBObject->Disconnect();TransactionStart()
Start a SQL Transaction
my $Result = $DBObject->TransactionStart();returns: 1 on Success and 0 on Failure
TransactionCommit()
Complete the SQL Transaction and commit the Queries to the Database
my $Result = $DBObject->TransactionCommit();returns: 1 on Success and 0 on Failure
TransactionRollback()
Abort the SQL Transaction and rollback the Database Changes
my $Result = $DBObject->TransactionRollback();returns: 1 on Success and 0 on Failure
Version()
to get the database version
my $DBVersion = $DBObject->Version();
returns: "MySQL 5.1.1";Quote()
to quote sql parameters
quote strings, date and time:
=============================
my $DBString = $DBObject->Quote( "This isn't a problem!" );
my $DBString = $DBObject->Quote( "2005-10-27 20:15:01" );
quote integers:
===============
my $DBString = $DBObject->Quote( 1234, 'Integer' );
quote numbers (e. g. 1, 1.4, 42342.23424):
==========================================
my $DBString = $DBObject->Quote( 1234, 'Number' );Error()
to retrieve database errors
my $ErrorMessage = $DBObject->Error();Do()
to insert, update or delete values
$DBObject->Do( SQL => "INSERT INTO table (name) VALUES ('dog')" );
$DBObject->Do( SQL => "DELETE FROM table" );
you also can use DBI bind values (used for large strings):
my $Var1 = 'dog1';
my $Var2 = 'dog2';
$DBObject->Do(
SQL => "INSERT INTO table (name1, name2) VALUES (?, ?)",
Bind => [ \$Var1, \$Var2 ],
);Prepare()
to prepare a SELECT statement
$DBObject->Prepare(
SQL => "SELECT id, name FROM table",
Limit => 10,
);or in case you want just to get row 10 until 30
$DBObject->Prepare(
SQL => "SELECT id, name FROM table",
Start => 10,
Limit => 20,
);in case you don't want utf-8 encoding for some columns, use this:
$DBObject->Prepare(
SQL => "SELECT id, name, content FROM table",
Encode => [ 1, 1, 0 ],
);you also can use DBI bind values, required for large strings:
my $Var1 = 'dog1';
my $Var2 = 'dog2';
$DBObject->Prepare(
SQL => "SELECT id, name, content FROM table WHERE name_a = ? AND name_b = ?",
Encode => [ 1, 1, 0 ],
Bind => [ \$Var1, \$Var2 ],
);FetchrowArray()
to process the results of a SELECT statement
$DBObject->Prepare(
SQL => "SELECT id, name FROM table",
Limit => 10
);
while (my @Row = $DBObject->FetchrowArray()) {
print "$Row[0]:$Row[1]\n";
}ListTables()
list all tables in the OTRS database.
my @Tables = $DBObject->ListTables();On databases like Oracle it could happen that too many tables are listed (all belonging to the current user), if the user also has permissions for other databases. So this list should only be used for verification of the presence of expected OTRS tables.
GetColumnNames()
to retrieve the column names of a database statement
$DBObject->Prepare(
SQL => "SELECT * FROM table",
Limit => 10
);
my @Names = $DBObject->GetColumnNames();TestDatabaseNameGet()
returns the name of the test database.
my $TestDBName = $DBObject->TestDatabaseNameGet();returns:
$TestDBName = 'otrstest';GetColumnProperties()
returns column type for specific table.
my $ColumnProperties = $DBObject->GetColumnProperties(
Table => 'ticket', # required
Column => 'id', # required
Database => 'otrstest', # (optional)
);returns:
$ColumnProperties = {
ColumnName => 'id',
Type => 'bigint',
CharacterMaximumLength => undef,
IsNullable => 1,
};SelectAll()
returns all available records of a SELECT statement. In essence, this calls Prepare() and FetchrowArray() to get all records.
my $ResultAsArrayRef = $DBObject->SelectAll(
SQL => "SELECT id, name FROM table",
Limit => 10
);You can pass the same arguments as to the Prepare() method.
Returns undef (if query failed), or an array ref (if query was successful):
my $ResultAsArrayRef = [
[ 1, 'itemOne' ],
[ 2, 'itemTwo' ],
[ 3, 'itemThree' ],
[ 4, 'itemFour' ],
];GetDatabaseFunction()
to get database functions like
- Limit
- DirectBlob
- QuoteSingle
- QuoteBack
- QuoteSemicolon
- NoLikeInLargeText
- CurrentTimestamp
- Encode
- Comment
- ShellCommit
- ShellConnect
- Connect
- LikeEscapeString
my $What = $DBObject->GetDatabaseFunction('DirectBlob');SQLProcessor()
generate database-specific sql syntax (e. g. CREATE TABLE …)
my @SQL = $DBObject->SQLProcessor(
Database =>
[
Tag => 'TableCreate',
Name => 'table_name',
],
[
Tag => 'Column',
Name => 'col_name',
Type => 'VARCHAR',
Size => 150,
],
[
Tag => 'Column',
Name => 'col_name2',
Type => 'INTEGER',
],
[
Tag => 'TableEnd',
],
ForceConstraintSQL => 1, # optional, skips safety measures for incides etc. (used for table structure check)
);SQLProcessorPost()
generate database-specific sql syntax, post data of SQLProcessor(), e. g. foreign keys
my @SQL = $DBObject->SQLProcessorPost();QueryCondition()
generate SQL condition query based on a search expression
my $SQL = $DBObject->QueryCondition(
Key => 'some_col',
Value => '(ABC+DEF)',
);
add SearchPrefix and SearchSuffix to search, in this case
for "(ABC*+DEF*)"
my $SQL = $DBObject->QueryCondition(
Key => 'some_col',
Value => '(ABC+DEF)',
SearchPrefix => '',
SearchSuffix => '*'
Extended => 1, # use also " " as "&&", e.g. "bob smith" -> "bob&&smith"
);
example of a more complex search condition
my $SQL = $DBObject->QueryCondition(
Key => 'some_col',
Value => '((ABC&&DEF)&&!GHI)',
);
for a earch condition over more columns
my $SQL = $DBObject->QueryCondition(
Key => [ 'some_col_a', 'some_col_b' ],
Value => '((ABC&&DEF)&&!GHI)',
);
Returns the SQL string or "1=0" if the query could not be parsed correctly.
my $SQL = $DBObject->QueryCondition(
Key => [ 'some_col_a', 'some_col_b' ],
Value => '((ABC&&DEF)&&!GHI)',
BindMode => 1,
);
return the SQL String with ?-values and a array with values references:
$BindModeResult = (
'SQL' => 'WHERE testa LIKE ? AND testb NOT LIKE ? AND testc = ?'
'Values' => ['a', 'b', 'c'],
)Note that the comparisons are usually performed case insensitively. Only VARCHAR columns with a size less or equal 3998 are supported, as for locator objects the functioning of SQL function LOWER() can't be guaranteed.
QueryInCondition()
Generate a SQL IN condition query based on the given table key and values.
my $SQL = $DBObject->QueryInCondition(
Key => 'table.column',
Values => [ 1, 2, 3, 4, 5, 6 ],
QuoteType => '(undef|Integer|Number)',
BindMode => (0|1),
Negate => (0|1),
);Returns the SQL string:
my $SQL = "ticket_id IN (1, 2, 3, 4, 5, 6)"Return a separated IN condition for more then MaxParamCountForInCondition values:
my $SQL = "( ticket_id IN ( 1, 2, 3, 4, 5, 6 ... ) OR ticket_id IN ( ... ) )"Return the SQL String with ?-values and a array with values references in bind mode:
$BindModeResult = (
'SQL' => 'ticket_id IN (?, ?, ?, ?, ?, ?)',
'Values' => [1, 2, 3, 4, 5, 6],
);
or
$BindModeResult = (
'SQL' => '( ticket_id IN (?, ?, ?, ?, ?, ?) OR ticket_id IN ( ?, ... ) )',
'Values' => [1, 2, 3, 4, 5, 6, ... ],
);Returns the SQL string for a negated in condition:
my $SQL = "ticket_id NOT IN (1, 2, 3, 4, 5, 6)"
or
my $SQL = "( ticket_id NOT IN ( 1, 2, 3, 4, 5, 6 ... ) AND ticket_id NOT IN ( ... ) )"QueryStringEscape()
escapes special characters within a query string
my $QueryStringEscaped = $DBObject->QueryStringEscape(
QueryString => 'customer with (brackets) and & and -',
);
Result would be a string in which all special characters are escaped.
Special characters are those which are returned by _SpecialCharactersGet().
$QueryStringEscaped = 'customer with \(brackets\) and \& and \-';Ping()
checks if the database is reachable
my $Success = $DBObject->Ping(
AutoConnect => 0, # default 1
);Check()
Perform several database checks.
my %CheckResult = $DBObject->Check(
Mode => 'ConsoleCommand', # ConsoleCommand | SupportDataCollector | Installer
Repair => 1, # optional ( 0 | 1) Default 0 (will automatically repair certain issues,
# if supported by the submodule)
);
Returns:
%CheckResult = (
Success => 1,
Result => [
{
Identifier => 'Kernel::System::DB::Check::OutdatedTables', # Shows from which module this result is coming from.
Label => 'Outdated Tables', # Label to show what this check is doing.
State => 'OK', # State can be: OK, Warning, Problem, Info, Unknown.
Value => '', # Additional data in case (used mostly if state is not OK).
Message => '', # Message that describes the Problem if neccessary.
},
{
Identifier => 'Kernel::System::DB::Check::TablePresence',
Label => Table Presence',
State => 'Problem',
Value => 'ticket, ticket_history',
Message => 'Tables found which are not present in the database.',
},
);