NAME

Kernel::System::Ticket::TicketSearch::SQL – SQL statement and result handling.

DESCRIPTION

This class takes care about constructing the SQL statements and bind parameters, as well as querying the database using Kernel::System::DB.

The related parts of a certain SQL statement will be collected in attributes, during an execution of method TicketSearch in module Kernel::System::Ticket::TicketSearch.

Therefore the focus of the related SQL statements is always on the ticket table and it's related identifiers, ticket numbers and possibly change times.

Finally the SQL query will be generated, using CPAN module SQL::Abstract::More, which is fully compatible with module SQL::Abstract.

For more information about the usage of both modules, please refer to the following links:

https://metacpan.org/pod/SQL::Abstract https://metacpan.org/pod/SQL::Abstract::More

Guidelines

Instantiating

Since this module not just constructs SQL queries and bind parameters, but also executes them against the database and possibly uses caching for results, the object must be initialized with an already instantiated Kernel::System::Ticket::TicketSearch::Parameters object, to work properly.

    my $SQL = Kernel::System::Ticket::TicketSearch::SQL->new(
        Parameters => Kernel::System::Ticket::TicketSearch::Parameters->new( { UserID => 123 } ),
    );

Otherwise the object will throw an exception at instantiation time.

Constructing SQL queries

As already mentioned, the query parts will be collected at first and construct to a related query with bind parameters afterwards. For this procedure, the module contains several attributes, that provides handles (methods) to be filled up with query conditions and values.

One goal for most attributes, is to unify the added data and construct as efficient query structures as possible, to reduce the consumes time, such a query takes.

Consider an SQL query like the following

    SELECT t.id FROM ticket AS t WHERE t.id NOT IN (1,2,3) ORDER BY t.id ASC

Using this object, it would look as follows

    $SQL->ColumnsAdd('t.id');
    $SQL->OrderByAdd( { -asc => 't.id' } );
    $SQL->WhereAdd(
        {
            't.id' => { -not_in => [ 1, 2, 3 ] },
        }
    );

The order in which the conditions are added is not necessary. Please refer to the related attributes in this class, to see the possible methods for queries.

Execute queries against the database

After the SQL query has been prepared, it can be executed with the Query method. The related result type and caching possibilities will be respected.

    my $Count  = $SQL->Query(); # On result type 'COUNT'
    my @Result = $SQL->Query(); # On result type 'ARRAY' or 'CHANGE'
    my %Result = $SQL->Query(); # On result type 'HASH'

PUBLIC INTERFACE

ATTRIBUTES

has Columns

Array reference, containing the columns to be selected from the database.

has Join

Array reference, containing the tables to be joined with corresponding conditions.

has Where

Array reference, containing the SQL WHERE clauses.

has OrderBy

Array reference, containing the SQL ORDER BY clauses.

has GroupBy

Array reference, containing the SQL ORDER BY clauses.

has Limit

The SQL LIMIT integer, that is used to limit the returned amount of results.

has Parameters

Parameter object containing the prepared ticket search parameters. Please refer to Kernel::System::Ticket::TicketSearch::Parameters.

has DBObject

Instantiated database object.

has SQLObject

The SQL generator object, that might be used from external for i.e. generating and appending sub-queries.

has UseCache

Boolean indicator, if the caching mechanism must be used. Caching will be used, if the article table is joined and a cache time-to-live or the parameter FullTextIndex was provided to the TicketSearch method.

METHODS

Query()

Combines and optimizes the collected SQL parts and executes the query.

Query()

Returns the SQL statement and the related binds.

PRIVATE INTERFACE

ATTRIBUTES

has _Columns

Prepared and unified SELECT columns.

has _From

Transforms the FROM statement, based on prepared and unified added JOIN statements.

has _Where

Prepared and unified WHERE conditions.

has _GroupBy

Prepared and unified GROUP BY conditions.

has _OrderBy

Prepared and unified ORDER BY conditions.

has _CacheKey

Generated cache key out of the current SQL statement.

METHODS

_Cache()

Checks either if cache entries exist for the given SQL query and returns them or takes a new cache entry as an argument and saves it with a key generated out of the current SQL statement.

Only consider caching, if the related circumstances are met. Please consider UseCache method description for more information.

    my $StoredCacheValue = $Self->_Cache();

    $Self->_Cache($NewCacheValue);

_COUNT()

Prepares and returns the results for type 'COUNT'.

_ATTRIBUTE()

Prepares and returns the results for type 'ATTRIBUTE'.

_CHANGE()

Prepares and returns the results for type 'CHANGE'.

_HASH()

Prepares and returns the results for type 'HASH'.

_ARRAY()

Prepares and returns the results for type 'ARRAY'.