Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
OpenXPKI::Server::DataUser(Contributed Perl DocumOpenXPKI::Server::Database(3)

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
       o   log - Log object (OpenXPKI::Server::Log, required)

       o   db_params - HashRef with parameters for the DBI data	source name
	   string (required).

	   Required keys in this hash:

	   o   type - last part	of a package in	the
	       "OpenXPKI::Server::Database::Driver::*" namespace. (Str,
	       required)

	   o   Any of the "Constructor parameters" in
	       OpenXPKI::Server::Database::Role::Driver

	   o   Additional parameters required by the specific driver

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

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

       o   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' },
	       );

       o   db_version -	database version, equals the result of
	   "$dbh->get_version(...)" (Str)

       o   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:

       o   columns - List of column names (ArrayRef[Str], required)

       o   from	- Table	name (or list of) (Str | ArrayRef[Str],	required)

       o   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.

       o   where - WHERE clause	following the spec in "WHERE-CLAUSES" in
	   SQL::Abstract (Str |	ArrayRef | HashRef)

       o   group_by - GROUP BY column (or list of) (Str	| ArrayRef)

       o   having - HAVING clause following the	spec in	"WHERE-CLAUSES"	in
	   SQL::Abstract (Str |	ArrayRef | HashRef)

       o   order_by - Plain ORDER BY string or list of columns.	Each column
	   name	can be preceded	by a "-" for descending	sort (Str | ArrayRef)

       o   limit - (Int)

       o   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:

       o   $operator - SQL operator between column and subquery	(Str,
	   required).

	   Operators can be e.g. 'IN', 'NOT IN', '> MAX' or '< ALL'.

       o   $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".

   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:

       o   into	- Table	name (Str, required)

       o   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:

       o   table - Table name (Str, required)

       o   set - Hash with column name / value pairs. Please note that "undef"
	   is interpreted as "NULL" (HashRef, required)

       o   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:

       o   into	- Table	name (Str, required)

       o   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)

       o   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)

       o   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:

       o   from	- Table	name (Str, required)

       o   where - WHERE clause	following the spec in "WHERE-CLAUSES" in
	   SQL::Abstract (Str |	ArrayRef | HashRef)

       o   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).

   in_txn
       Returns "true" if a transaction is currently running, i.e. after
       "start_txn" was called but before "commit" or "rollback"	where called.

   commit
       Commits a transaction.

       Logs an error if	"start_txn" was	not called first.

   rollback
       Rolls back a transaction.

       Logs an error if	"start_txn" was	not called first.

   insert_and_commit
       Calling this method is the same as:

	   $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:

       o   $query - query to run (either a OpenXPKI::Server::Database::Query
	   or a	literal	SQL string)

       o   $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.

perl v5.32.0			  2020-08-29	 OpenXPKI::Server::Database(3)

Name | Description | Attributes | Methods | Low level methods

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=OpenXPKI::Server::Database&sektion=3&manpath=FreeBSD+12.1-RELEASE+and+Ports>

home | help