DB

DB#

NAME#

Kernel::System::DB - global database interface

DESCRIPTION#

All database functions to connect/insert/update/delete/… to a database.

PUBLIC INTERFACE#

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();

Version()#

to get the database version

my $DBVersion = $DBObject->Version();

returns: "MySQL 5.1.1";

CheckRequiredDatabaseVersion()#

Check if the required database version is installed or not.

my %VersionInfo = $DBObject->CheckRequiredDatabaseVersion();

Returns:

my %VersionInfo = (
    'DatabaseType'       => 'MariaDB',
    'VersionString'      => 'MariaDB 10.6.12',
    'InstalledVersion'   => '10.6.12',
    'MinimumVersion'     => '5.0.0',
    'RequirementsPassed' => 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.

GetSystemTables#

Retrieves tables of Znuny and optionally its installed packages and ignores any other tables that might have been added manually to the database.

my @SystemTables = $DBObject->GetSystemTables(
    IncludePackageTables => 1, # Also include tables of installed packages
);

Returns array with system table names.

GetColumnNames()#

to retrieve the column names of a database statement

$DBObject->Prepare(
    SQL   => "SELECT * FROM table",
    Limit => 10
);

my @Names = $DBObject->GetColumnNames();

GetColumnMaxLengths()#

This method is used to retrieve the maximum length of a column in the database.

my %ColumnMaxLength = $DBObject->GetColumnMaxLengths(
    Table => "customer_user",
);

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',
        ],
);

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
);

DB

Contents

DB#