Chapter 5. Database Mechanism

OTRS comes with a database layer that supports different databases.

5.1. How it works

The database layer (Kernel::System::DB) has two input options: SQL and XML.

5.1.1. SQL

The SQL interface should be used for normal database actions (SELECT, INSERT, UPDATE, ...). It can be used like a normal Perl DBI interface.

5.1.1.1. INSERT/UPDATE/DELETE

$Self->{DBObject}->Do(
    SQL=> "INSERT INTO table (name, id) VALUES ('SomeName', 123)",
);

$Self->{DBObject}->Do(
    SQL=> "UPDATE table SET name = 'SomeName', id = 123",
);

$Self->{DBObject}->Do(
    SQL=> "DELETE FROM table WHERE id = 123",
);

5.1.1.2. SELECT

my $SQL = "SELECT id FROM table WHERE tn = '123'";

$Self->{DBObject}->Prepare(SQL => $SQL, Limit => 15);

while (my @Row = $Self->{DBObject}->FetchrowArray()) {
    $Id = $Row[0];
}
return $Id;

Take care to use Limit as param and not in the SQL string because not all databases support LIMIT in SQL strings.

5.1.1.3. QUOTE

String:

my $QuotedString = $Self->{DBObject}->Quote("It's a problem!");

Integer:

my $QuotedInteger = $Self->{DBObject}->Quote('123', 'Integer');

Number:

my $QuotedNumber = $Self->{DBObject}->Quote('21.35', 'Number');

The XML interface should be used for INSERT, CREATE TABLE, DROP TABLE and ALTER TABLE (>= OTRS 2.1). As this syntax is different from database to database, using it makes sure that you write applications that can be used in all of them.

5.1.2.1. INSERT

<Insert Table="some_table">
    <Data Key="id" Value="1"/>
    <Data Key="description" Value="exploit" Type="Quote"/>
</Insert>

5.1.2.2. CREATE TABLE

Possible data types are: BIGINT, SMALLINT, INTEGER, VARCHAR (Size=1-1000000), DATE (Format: yyyy-mm-dd hh:mm:ss) and LONGBLOB.

<TableCreate Name="calendar_event">
    <Column Name="id" Required="true" PrimaryKey="true" AutoIncrement="true" Type="BIGINT"/>
    <Column Name="title" Required="true" Size="250" Type="VARCHAR"/>
    <Column Name="content" Required="false" Size="250" Type="VARCHAR"/>
    <Column Name="start_time" Required="true" Type="DATE"/>
    <Column Name="end_time" Required="true" Type="DATE"/>
    <Column Name="owner_id" Required="true" Type="INTEGER"/>
    <Column Name="event_status" Required="true" Size="50" Type="VARCHAR"/>
    <Index Name="title">
        <IndexColumn Name="title"/>
    </Index>
    <Unique>
        <UniqueColumn Name="title"/>
    </Unique>
    <ForeignKey ForeignTable="system_user">
        <Reference Local="owner_id" Foreign="id"/>
    </ForeignKey>
</TableCreate>

5.1.2.3. DROP TABLE

<TableDrop Name="calendar_event"/>

5.1.2.4. ALTER TABLE (>= OTRS 2.1)

<TableAlter Name="calendar_event">
    <ColumnAdd Name="test_name" Type="varchar" Size="20" Required="1"/>
    <ColumnChange NameOld="test_name" NameNew="test_title" Type="varchar" Size="30" Required="1"/>
    <ColumnChange NameOld="test_title" NameNew="test_title" Type="varchar" Size="100" Required="0"/>
    <ColumnDrop Name="test_title"/>
</TableAlter>

5.1.2.5. Code to process XML

my @XMLARRAY = @{$Self->ParseXML(String => $XML)};

my @SQL = $Self->{DBObject}->SQLProcessor(
    Database => \@XMLARRAY,
);
push(@SQL, $Self->{DBObject}->SQLProcessorPost());

foreach (@SQL) {
    $Self->{DBObject}->Do(SQL => $_);
}