##
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:


.. code-block:: perl

     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


.. code-block:: perl

     $DBObject->Connect();



Disconnect()
============


to disconnect from a database


.. code-block:: perl

     $DBObject->Disconnect();



Version()
=========


to get the database version


.. code-block:: perl

     my $DBVersion = $DBObject->Version();
 
     returns: "MySQL 5.1.1";



CheckRequiredDatabaseVersion()
==============================


Check if the required database version is installed or not.


.. code-block:: perl

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


Returns:


.. code-block:: perl

     my %VersionInfo = (
         'DatabaseType'       => 'MariaDB',
         'VersionString'      => 'MariaDB 10.6.12',
         'InstalledVersion'   => '10.6.12',
         'MinimumVersion'     => '5.0.0',
         'RequirementsPassed' => 1,
     );



Quote()
=======


to quote sql parameters


.. code-block:: perl

     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


.. code-block:: perl

     my $ErrorMessage = $DBObject->Error();



Do()
====


to insert, update or delete values


.. code-block:: perl

     $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


.. code-block:: perl

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


or in case you want just to get row 10 until 30


.. code-block:: perl

     $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:


.. code-block:: perl

     $DBObject->Prepare(
         SQL    => "SELECT id, name, content FROM table",
         Encode => [ 1, 1, 0 ],
     );


you also can use DBI bind values, required for large strings:


.. code-block:: perl

     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


.. code-block:: perl

     $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.


.. code-block:: perl

     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.


.. code-block:: perl

     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


.. code-block:: perl

     $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.


.. code-block:: perl

     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.


.. code-block:: perl

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


.. code-block:: perl

   my $ResultAsArrayRef = [
     [ 1, 'itemOne' ],
     [ 2, 'itemTwo' ],
     [ 3, 'itemThree' ],
     [ 4, 'itemFour' ],
   ];



GetDatabaseFunction()
=====================


to get database functions like


.. code-block:: perl

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


.. code-block:: perl

     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


.. code-block:: perl

     my @SQL = $DBObject->SQLProcessorPost();



QueryCondition()
================


generate SQL condition query based on a search expression


.. code-block:: perl

     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.


.. code-block:: perl

     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:


.. code-block:: perl

     my $SQL = "ticket_id IN (1, 2, 3, 4, 5, 6)"


Return a separated IN condition for more then \ ``MaxParamCountForInCondition``\  values:


.. code-block:: perl

     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:


.. code-block:: perl

     $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:


.. code-block:: perl

     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


.. code-block:: perl

     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


.. code-block:: perl

     my $Success = $DBObject->Ping(
         AutoConnect => 0,  # default 1
     );