NAME

OpenXPKI::Server::Database::Role::Driver - Moose role that every database driver has to consume

SYNOPSIS

    package OpenXPKI::Server::Database::Driver::MyDB2;
    use Moose;
    with 'OpenXPKI::Server::Database::Role::SequenceSupport';
    with 'OpenXPKI::Server::Database::Role::MergeEmulation';
    with 'OpenXPKI::Server::Database::Role::Driver';

    # required by OpenXPKI::Server::Database::Role::Driver
    sub dbi_driver { 'DB2' }           # DBI compliant driver name
    sub dbi_dsn {                      # DSN string including all parameters.
        my $self = shift;
        return sprintf("dbi:%s:dbname=%s",
            $self->dbi_driver,
            $self->name,
        );
    }
    sub dbi_connect_params { {} }      # Additional parameters for DBI's connect()
    sub sqlam_params { {               # Parameters for SQL::Abstract::More
        limit_offset => 'FetchFirst',
    } }

    # required by OpenXPKI::Server::Database::Role::SequenceSupport
    sub nextval_query {                # SQL query to retrieve next sequence value
        my ($self, $seq) = @_;
        return "VALUES NEXTVAL FOR $seq";
    }

    __PACKAGE__->meta->make_immutable;

Then e.g. in your database.yaml:

    main:
        type: MyDB2
        ...

The above code is actually the current driver for IBM DB2 databases.

DESCRIPTION

This Moose role must be consumed by every OpenXPKI database driver. It defines some standard attributes which represent database connection parameters of the same name (not all are required for every DBMS). Furthermore it requires the consuming class to implement certain methods.

Transaction isolation level

Please make sure that the database transaction isolation level is "READ COMMITTED" as OpenXPKI expects this. If your DBMS has another default transaction level please change it in "dbi_on_connect_do".

Example for MySQL:

    sub dbi_on_connect_do {
        "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED"
    }

Writing an own driver

If you have a DBMS that is not yet supported by OpenXPKI you can write and use a new driver without changing existing code. The only requirement is that there is a DBI driver for your DBMS (look for it on MetaCPAN <https://metacpan.org/search?q=DBD%3A%3A&search_type=modules>).

To connect OpenXPKI to your (not yet supported) DBMS follow these steps:

1. Write a driver class in the "OpenXPKI::Server::Database::Driver::*" namespace that consumes the following Moose roles:

OpenXPKI::Server::Database::Role::SequenceSupport if your DBMS has native support for sequences,
OpenXPKI::Server::Database::Role::SequenceEmulation otherwise.
OpenXPKI::Server::Database::Role::MergeSupport if your DBMS has native support for some form of an SQL MERGE query (="REPLACE" = "UPSERT" = "INSERT or UPDATE"),
OpenXPKI::Server::Database::Role::MergeEmulation otherwise.
OpenXPKI::Server::Database::Role::Driver

... and implement the methods that these roles require.

2. Reference your driver class by it's driver name (the last part after *::Driver::, case sensitive) in your configuration file.
3. Submit your code to the OpenXPKI team :)

ATTRIBUTES

name - Database name (Str, required)
namespace - Schema/namespace that will be added as table prefix in all queries. Could e.g. be used to store multiple OpenXPKI installations in one database (Str, optional)
host - Database host: IP or hostname (Str, optional)
port - Database TCP port (Int, optional)
user - Database username (Str, optional)
passwd - Database password (Str, optional)

METHODS

Please note that the following methods are implemented in the driver class that consumes this Moose role.

dbi_driver

Returns the DBI compliant case sensitive driver name (Str).

dbi_dsn

Returns the DSN that is passed to "connect" in DBI (Str).

dbi_connect_params

Returns optional parameters that are passed to "connect" in DBI (HashRef).

dbi_on_connect_do

Returns optional commands to be executed after connecting to the database (ArrayRef or Str).

sqlam_params

Returns optional parameters that are passed to "new" in SQL::Abstract::More (HashRef).

sequence_create_query

Returns an OpenXPKI::Server::Database::Query object containing the SQL query that creates a new sequence (or a table emulating a sequence, if the driver has got the role OpenXPKI::Server::Database::Role::SequenceEmulation).

Parameters:

$dbi - OpenXPKI database handler ("OpenXPKI::Server::Database", required)
$seq - Name of SQL sequence to be created (Str, required)

sequence_drop_query

Returns an OpenXPKI::Server::Database::Query object containing the SQL query that removes a sequence (or a table emulating a sequence, if the driver has got the role OpenXPKI::Server::Database::Role::SequenceEmulation).

Parameters:

$dbi - OpenXPKI database handler ("OpenXPKI::Server::Database", required)
$seq - Name of SQL sequence to be removed (Str, required)

next_id

Returns the next insert id, i.e. the value of the given sequence (Int).

Parameters:

$dbi - OpenXPKI database handler ("OpenXPKI::Server::Database", required)
$seq - Name of SQL sequence whose next value shall be returned (Str, required)

merge_query

Builds a MERGE query (or emulates it by either an INSERT or an UPDATE query) and returns a OpenXPKI::Server::Database::Query object which contains SQL string and bind parameters.

Parameters:

$dbi - OpenXPKI database handler ("OpenXPKI::Server::Database", required)
$into - Table name including schema (if applicable) (Str, required)
$set - Columns that are always set (INSERT or UPDATE). Hash with column name / value pairs.
$set_once - Columns that are only set on INSERT (additional to those in the "where" parameter. Hash with column name / value pairs.
$where - WHERE clause specification that must contain the PRIMARY KEY columns and only allows "AND" and "equal" operators: "<{ col1 =" val1, col2 => val2 }>> (HashRef)