Name

OpenXPKI::Server::Database::QueryBuilder - Programmatic interface to SQL queries

Description

This class provides methods to create DBMS specific SQL queries that can be executed later on.

It delegates most of the work to SQL::Abstract::More but offers a slightly modified and stripped down interface (customized for OpenXPKI).

sqlam - SQL query builder (an instance of SQL::Abstract::More)
namespace - namespace (i.e. schema) to prepend to table names (Str, optional)

Constructor.

Named parameters: see attributes section above.

Builds a SELECT query and returns a OpenXPKI::Server::Database::Query object which contains SQL string and bind parameters.

The method parameters are documented in "select" in OpenXPKI::Server::Database.

Builds a subselect to be used within another query and returns a reference to an ArrayRef.

This will take something like this:

    CTX('dbi')->subselect('IN' => {
        from => 'nature',
        columns => [ 'id', 'fruit' ],
        where => { type => 'forbidden' }
    })

and turn it into:

    \[ "IN ($query)" => @bind ]

The method parameters are documented in "subselect" in OpenXPKI::Server::Database.

Builds an INSERT query and returns a OpenXPKI::Server::Database::Query object which contains SQL string and bind parameters.

Named parameters:

into - Table name (Str, required)
values - Hash with column name / value pairs. Please note that "undef" is interpreted as "NULL" (HashRef, required)

Builds an UPDATE query and returns a OpenXPKI::Server::Database::Query object which contains SQL string and bind parameters.

A WHERE clause is required to prevent accidential updates of all rows in a table.

The method parameters are documented in "update" in OpenXPKI::Server::Database.

Builds a DELETE query and returns an OpenXPKI::Server::Database::Query object which contains SQL string and bind parameters.

To prevent accidential deletion of all rows of a table you must specify parameter "all" if you want to do that:

    $dbi->delete(
        from => "mytab",
        all => 1,
    );

The method parameters are documented in "delete" in OpenXPKI::Server::Database.