OTRS comes with a database layer that supports different databases.
The database layer (Kernel::System::DB) has two input options: SQL and XML.
The SQL interface should be used for normal database actions (SELECT, INSERT, UPDATE, ...). It can be used like a normal Perl DBI interface.
$Kernel::OM->Get('Kernel::System::DB')->Do(
SQL=> "INSERT INTO table (name, id) VALUES ('SomeName', 123)",
);
$Kernel::OM->Get('Kernel::System::DB')->Do(
SQL=> "UPDATE table SET name = 'SomeName', id = 123",
);
$Kernel::OM->Get('Kernel::System::DB')->Do(
SQL=> "DELETE FROM table WHERE id = 123",
);
my $SQL = "SELECT id FROM table WHERE tn = '123'";
$Kernel::OM->Get('Kernel::System::DB')->Prepare(SQL => $SQL, Limit => 15);
while (my @Row = $Kernel::OM->Get('Kernel::System::DB')->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.
my $SQL = "SELECT id FROM table WHERE tn = ? AND group = ?";
$Kernel::OM->Get('Kernel::System::DB')->Prepare(
SQL => $SQL,
Limit => 15,
Bind => [ $Tn, $Group ],
);
while (my @Row = $Kernel::OM->Get('Kernel::System::DB')->FetchrowArray()) {
$Id = $Row[0];
}
return $Id;
Use the Bind attribute where ever you can, especially for long statements. If
you use Bind you do not need the function Quote().
String:
my $QuotedString = $Kernel::OM->Get('Kernel::System::DB')->Quote("It's a problem!");
Integer:
my $QuotedInteger = $Kernel::OM->Get('Kernel::System::DB')->Quote('123', 'Integer');
Number:
my $QuotedNumber = $Kernel::OM->Get('Kernel::System::DB')->Quote('21.35', 'Number');
Please use the Bind attribute instead of Quote() where ever you can.
The XML interface should be used for INSERT, CREATE TABLE, DROP TABLE and ALTER TABLE. 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.
The <Insert> syntax has changed in >=2.2. Values are now used in the tag content (not longer in an attribute).
<Insert Table="some_table">
<Data Key="id">1</Data>
<Data Key="description" Type="Quote">exploit</Data>
</Insert>
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="calendar_event_title">
<IndexColumn Name="title"/>
</Index>
<Unique Name="calendar_event_title">
<UniqueColumn Name="title"/>
</Unique>
<ForeignKey ForeignTable="users">
<Reference Local="owner_id" Foreign="id"/>
</ForeignKey>
</TableCreate>
The following shows an example of add, change and drop columns.
<TableAlter Name="calendar_event">
<ColumnAdd Name="test_name" Type="varchar" Size="20" Required="true"/>
<ColumnChange NameOld="test_name" NameNew="test_title" Type="varchar" Size="30" Required="true"/>
<ColumnChange NameOld="test_title" NameNew="test_title" Type="varchar" Size="100" Required="false"/>
<ColumnDrop Name="test_title"/>
<IndexCreate Name="index_test3">
<IndexColumn Name="test3"/>
</IndexCreate>
<IndexDrop Name="index_test3"/>
<UniqueCreate Name="uniq_test3">
<UniqueColumn Name="test3"/>
</UniqueCreate>
<UniqueDrop Name="uniq_test3"/>
</TableAlter>
The next shows an example how to rename a table.
<TableAlter NameOld="calendar_event" NameNew="calendar_event_new"/>
my @XMLARRAY = @{$Self->ParseXML(String => $XML)};
my @SQL = $Kernel::OM->Get('Kernel::System::DB')->SQLProcessor(
Database => \@XMLARRAY,
);
push(@SQL, $Kernel::OM->Get('Kernel::System::DB')->SQLProcessorPost());
for (@SQL) {
$Kernel::OM->Get('Kernel::System::DB')->Do(SQL => $_);
}