Name

OpenXPKI::Server::Database - Handles database connections and encapsulates DB specific drivers/functions.

Description

This class contains the API to interact with the configured OpenXPKI database.

Database drivers

While OpenXPKI supports several database types out of the box it still allows you to include new DBMS specific drivers without the need to change existing code.

For more details see OpenXPKI::Server::Database::Role::Driver.

Class structure

         .----------------------------.
    .----| OpenXPKI::Server::Database |---.--------------------.
    |    '----------------------------'   |                    |
    |                   |                 |                    |
    |                   |                 v                    v
    |                   |      .---------------------. .---------------.
    |             .-----'      | SQL::Abstract::More | | DBIx::Handler |
    |             |            '---------------------' '---------------'
    |             |                       .
    |             v                   injected
    |  .---------------------.            .
    |  | O:S:D::QueryBuilder |<...........'
    |  '---------------------'
    |             |     .--------------.
    |             '---->| O:S:D::Query |
    |                   '--------------'
    |
    |  .------------------.
    '->| O:S:D::Driver::* |
       '------------------'
         .
       consumes
         .    .---------------------.
         ....>| O:S:D::Role::Driver |
         .    '---------------------'
         .    .------------------------------.    .--------------------------------.
         ....>| O:S:D::Role::SequenceSupport | or | O:S:D::Role::SequenceEmulation |
         .    '------------------------------'    '--------------------------------'
         .    .------------------------------.    .--------------------------------.
         '...>| O:S:D::Role::MergeSupport    | or | O:S:D::Role::MergeEmulation    |
              '------------------------------'    '--------------------------------'

Attributes

Constructor parameters

log - Log object (OpenXPKI::Server::Log, required)
db_params - HashRef with parameters for the DBI data source name string (required).
Required keys in this hash:

type - last part of a package in the "OpenXPKI::Server::Database::Driver::*" namespace. (Str, required)
Any of the "Constructor parameters" in OpenXPKI::Server::Database::Role::Driver
Additional parameters required by the specific driver

•: autocommit - Bool to switch on "AutoCommit" in DBI (optional, default: 0)

Others

driver - database specific driver instance (consumer of OpenXPKI::Server::Database::Role::Driver)

query_builder - OpenXPKI query builder to create abstract SQL queries (OpenXPKI::Server::Database::QueryBuilder)

Usage:

    # returns an OpenXPKI::Server::Database::Query object
    my $query = $db->query_builder->select(
        from => 'certificate',
        columns  => [ 'identifier' ],
        where => { pki_realm => 'democa' },
    );

db_version - database version, equals the result of "$dbh->get_version(...)" (Str)
sqlam - low level SQL query builder (internal work horse, an instance of SQL::Abstract::More)

Methods

Note: all methods might throw an OpenXPKI::Exception if there are errors in the query or during it's execution.

new

Constructor.

Named parameters: see attributes section above.

select

Selects rows from the database and returns the results as a DBI::st statement handle.

Please note that "NULL" values will be converted to Perl "undef".

Subqueries can be realized using "subselect".

Named parameters:

columns - List of column names (ArrayRef[Str], required)
from - Table name (or list of) (Str | ArrayRef[Str], required)
from_join - A string to describe table relations for FROM .. JOIN following the spec in "join" in SQL::Abstract::More (Str)
```
    from_join => "certificate  req_key=req_key  csr"
    
```
Please note that you cannot specify "from" and "from_join" at the same time.
where - WHERE clause following the spec in "WHERE-CLAUSES" in SQL::Abstract (Str | ArrayRef | HashRef)
group_by - GROUP BY column (or list of) (Str | ArrayRef)
having - HAVING clause following the spec in "WHERE-CLAUSES" in SQL::Abstract (Str | ArrayRef | HashRef)
order_by - Plain ORDER BY string or list of columns. Each column name can be preceded by a "-" for descending sort (Str | ArrayRef)
limit - (Int)
offset - (Int)

subselect

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

The returned structure is understood by SQL::Abstract which is used internally.

E.g. to create the following query:

    SELECT title FROM books
    WHERE (
        author_id IN (
            SELECT id FROM authors
            WHERE ( legs > 2 )
        )
    )

you can use "subselect()" as follows:

    CTX('dbi')->select(
        from => "books",
        columns => [ "title" ],
        where => {
            author_id => CTX('dbi')->subselect("IN" => {
                from => "authors",
                columns => [ "id" ],
                where => { legs => { '>' => 2 } },
            }),
        },
    );

Positional parameters:

$operator - SQL operator between column and subquery (Str, required).
Operators can be e.g. 'IN', 'NOT IN', '> MAX' or '< ALL'.
$query - The query parameters in a HashRef as they would be given to "select" (HashRef, required)

select_one

Selects one row from the database and returns the results as a HashRef (column name => value) by calling "$sth->fetchrow_hashref".

For parameters see "select".

Returns "undef" if the query had no results.

Please note that "NULL" values will be converted to Perl "undef".

select_arrays

Selects all rows from the database and returns them as an ArrayRef[ArrayRef]. This is a shortcut to "$dbi->select(...)->fetchall_arrayref([])".

For parameters see "select".

Please note that "NULL" values will be converted to Perl "undef".

select_hashes

Selects all rows from the database and returns them as an ArrayRef[HashRef]. This is a shortcut to "$dbi->select(...)->fetchall_arrayref({})".

For parameters see "select".

Please note that "NULL" values will be converted to Perl "undef".

count

Takes the same arguments as "select", wraps them into a subquery and return the number of rows the select would return. The parameters "order_by", "limit" and "offset" are ignored.

insert

Inserts rows into the database and returns the number of affected rows.

    $db->insert(
        into => "certificate",
        values => {
            identifier => AUTO_ID, # use the sequence associated with this table
            cert_key => $key,
            ...
        }
    );

To automatically set a primary key to the next serial number (i.e. sequence associated with this table) set it to "AUTO_ID". You need to "use OpenXPKI::Server::Database;" to be able to use "AUTO_ID".

Named parameters:

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

update

Updates rows in the database and returns the number of affected rows.

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

Please note that "NULL" values will be converted to Perl "undef".

Named parameters:

table - Table name (Str, required)
set - Hash with column name / value pairs. Please note that "undef" is interpreted as "NULL" (HashRef, required)
where - WHERE clause following the spec in "WHERE-CLAUSES" in SQL::Abstract (Str | ArrayRef | HashRef)

merge

Either directly executes or emulates an SQL MERGE (you could also call it REPLACE) function and returns the number of affected rows.

Please note that e.g. MySQL returns 2 (not 1) if an update was performed. So you should only use the return value to test for 0 / FALSE.

Named parameters:

into - Table name (Str, required)
set - Columns that are always set (INSERT or UPDATE). Hash with column name / value pairs.
Please note that "undef" is interpreted as "NULL" (HashRef, required)
set_once - Columns that are only set on INSERT (additional to those in the "where" parameter. Hash with column name / value pairs.
Please note that "undef" is interpreted as "NULL" (HashRef, required)
where - WHERE clause specification that must contain the PRIMARY KEY columns and only allows "AND" and "equal" operators: "<{ col1 =" val1, col2 => val2 }>> (HashRef)
The values from the WHERE clause are also inserted if the row does not exist (together with those from "set_once")!

delete

Deletes rows in the database and returns the results as a DBI::st statement handle.

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

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

Named parameters:

from - Table name (Str, required)
where - WHERE clause following the spec in "WHERE-CLAUSES" in SQL::Abstract (Str | ArrayRef | HashRef)
all - Set this to 1 instead of specifying "where" to delete all rows (Bool)

start_txn

Records the start of a new transaction (i.e. sets a flag) without database interaction.

If the flag was already set (= another transaction is running), a "ROLLBACK" is performed first and an error message is logged.

Please note that after a "fork()" the flag is be reset as the "DBI" handle is also reset (so there cannot be a running transaction).

    $db->start_txn;
    $db->insert(...);
    $db->commit;

For more informations see "insert" in OpenXPKI::Server::Database.

update_and_commit

Calling this method is the same as:

    $db->start_txn;
    $db->update(...);
    $db->commit;

For more informations see "update" in OpenXPKI::Server::Database.

merge_and_commit

Calling this method is the same as:

    $db->start_txn;
    $db->merge(...);
    $db->commit;

For more informations see "merge" in OpenXPKI::Server::Database.

delete_and_commit

Calling this method is the same as:

    $db->start_txn;
    $db->delete(...);
    $db->commit;

For more informations see "delete" in OpenXPKI::Server::Database.

################################################################################

Low level methods

The following methods allow more fine grained control over the query processing.

dbh

Returns a fork safe DBI handle. Connects to the database if neccessary.

To remain fork safe DO NOT CACHE this (also do not convert into a lazy attribute).

run

Executes the given query and returns a DBI statement handle. Throws an exception in case of errors.

    my $sth;
    eval {
        $sth = $db->run($query);
    };
    if (my $e = OpenXPKI::Exception->caught) {
        die "OpenXPKI exception executing query: $e";
    }
    elsif ($@) {
        die "Unknown error: $e";
    };

Parameters:

$query - query to run (either a OpenXPKI::Server::Database::Query or a literal SQL string)
$return_rownum - return number of affected rows instead of DBI statement handle (optional, default: 0).
If no rows were affected, then "0E0" is returned which Perl will treat as 0 but will regard as true.

disconnect

Disconnects from the database. Might be useful to e.g. remove file locks when using SQLite.

Name

Description

Database drivers

Class structure

Attributes

Constructor parameters

Others

Methods

new

select

subselect

select_one

select_arrays

select_hashes

count

insert

update

merge

delete

start_txn

in_txn

commit

rollback

insert_and_commit

update_and_commit

merge_and_commit

delete_and_commit

Low level methods

dbh

run

disconnect