FreeBSD Manual Pages
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.2-RELEASE+and+Ports>