1package OpenXPKI::Server::Database::Role::Driver; 2use Moose::Role; 3use utf8; 4 5=head1 NAME 6 7OpenXPKI::Server::Database::Role::Driver - Moose role that every database driver 8has to consume 9 10=cut 11 12################################################################################ 13# Attributes 14# 15 16# Standardize some connection parameters names for all drivers 17has 'name' => ( is => 'ro', isa => 'Str', required => 1 ); 18has 'namespace' => ( is => 'ro', isa => 'Str' ); # = schema 19has 'host' => ( is => 'ro', isa => 'Str' ); 20has 'port' => ( is => 'ro', isa => 'Int' ); 21has 'user' => ( is => 'ro', isa => 'Str' ); 22has 'passwd' => ( is => 'ro', isa => 'Str' ); 23 24################################################################################ 25# Required in drivers classes that consume this role 26# 27 28# Returns String: DBI compliant case sensitive driver name 29requires 'dbi_driver'; 30 31# Returns String: DSN parameters after "dbi:<driver>:" 32requires 'dbi_dsn'; 33 34# Returns HashRef: optional parameters to pass to connect() 35requires 'dbi_connect_params'; 36 37# May execute custom commands after connecting, receives $dbh handle (DBI) 38requires 'on_connect'; 39 40# Returns HashRef: optional parameters for SQL::Abstract::More 41requires 'sqlam_params'; 42 43# Returns Int: next insert ID ("serial") 44requires 'next_id'; 45 46# Returns OpenXPKI::Server::Database::Query: query to create a new sequence 47requires 'sequence_create_query'; 48 49# Returns OpenXPKI::Server::Database::Query: query to drop a sequence 50requires 'sequence_drop_query'; 51 52# Returns OpenXPKI::Server::Database::Query: query to drop a table 53requires 'table_drop_query'; 54 55# Returns OpenXPKI::Server::Database::Query: MERGE query (="REPLACE" = "UPSERT" = UPDATE or INSERT) 56requires 'merge_query'; 57 58# Returns OpenXPKI::Server::Database::Query: to count the rows of a given SELECT statement 59requires 'count_rows'; 60 61# Returns a HashRef of qr() expressions and their replacement strings 62requires 'do_sql_replacements'; 63 641; 65 66=head1 SYNOPSIS 67 68 package OpenXPKI::Server::Database::Driver::MyDB2; 69 use Moose; 70 with 'OpenXPKI::Server::Database::Role::SequenceSupport'; 71 with 'OpenXPKI::Server::Database::Role::MergeEmulation'; 72 with 'OpenXPKI::Server::Database::Role::Driver'; 73 74 # required by OpenXPKI::Server::Database::Role::Driver 75 sub dbi_driver { 'DB2' } # DBI compliant driver name 76 sub dbi_dsn { # DSN string including all parameters. 77 my $self = shift; 78 return sprintf("dbi:%s:dbname=%s", 79 $self->dbi_driver, 80 $self->name, 81 ); 82 } 83 sub dbi_connect_params { {} } # Additional parameters for DBI's connect() 84 sub sqlam_params { { # Parameters for SQL::Abstract::More 85 limit_offset => 'FetchFirst', 86 } } 87 88 # required by OpenXPKI::Server::Database::Role::SequenceSupport 89 sub nextval_query { # SQL query to retrieve next sequence value 90 my ($self, $seq) = @_; 91 return "VALUES NEXTVAL FOR $seq"; 92 } 93 94 __PACKAGE__->meta->make_immutable; 95 96Then e.g. in your database.yaml: 97 98 main: 99 type: MyDB2 100 ... 101 102The above code is actually the current driver for IBM DB2 databases. 103 104=head1 DESCRIPTION 105 106This Moose role must be consumed by every OpenXPKI database driver. It defines 107some standard attributes which represent database connection parameters of the 108same name (not all are required for every DBMS). Furthermore it requires the 109consuming class to implement certain methods. 110 111=head2 Transaction isolation level 112 113Please make sure that the database transaction isolation level is 114"READ COMMITTED" as OpenXPKI expects this. If your DBMS has another default 115transaction level please change it in L</dbi_on_connect_do>. 116 117Example for MySQL: 118 119 sub dbi_on_connect_do { 120 "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED" 121 } 122 123=head2 Writing an own driver 124 125If you have a DBMS that is not yet supported by OpenXPKI you can write and use 126a new driver without changing existing code. The only requirement is that there 127is a L<DBI> driver for your DBMS (look for it on 128L<MetaCPAN|https://metacpan.org/search?q=DBD%3A%3A&search_type=modules>). 129 130To connect OpenXPKI to your (not yet supported) DBMS follow these steps: 131 132=over 133 134=item 1. Write a driver class in the C<OpenXPKI::Server::Database::Driver::*> 135namespace that consumes the following Moose roles: 136 137=over 138 139=item * L<OpenXPKI::Server::Database::Role::SequenceSupport> if your DBMS has native support for sequences, 140 141=item * L<OpenXPKI::Server::Database::Role::SequenceEmulation> otherwise. 142 143=item * L<OpenXPKI::Server::Database::Role::MergeSupport> if your DBMS has native support for some form of an SQL MERGE query (="REPLACE" = "UPSERT" = "INSERT or UPDATE"), 144 145=item * L<OpenXPKI::Server::Database::Role::MergeEmulation> otherwise. 146 147=item * L<OpenXPKI::Server::Database::Role::Driver> 148 149=back 150 151... and implement the methods that these roles require. 152 153=item 2. Reference your driver class by it's driver name (the last part after 154C<*::Driver::>, case sensitive) in your configuration file. 155 156=item 3. Submit your code to the OpenXPKI team :) 157 158=back 159 160=head1 ATTRIBUTES 161 162=over 163 164=item * B<name> - Database name (I<Str>, required) 165 166=item * B<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 (I<Str>, optional) 167 168=item * B<host> - Database host: IP or hostname (I<Str>, optional) 169 170=item * B<port> - Database TCP port (I<Int>, optional) 171 172=item * B<user> - Database username (I<Str>, optional) 173 174=item * B<passwd> - Database password (I<Str>, optional) 175 176=back 177 178=head1 METHODS 179 180Please note that the following methods are implemented in the driver class that 181consumes this Moose role. 182 183=head2 dbi_driver 184 185Returns the DBI compliant case sensitive driver name (I<Str>). 186 187=head2 dbi_dsn 188 189Returns the DSN that is passed to L<DBI/connect> (I<Str>). 190 191=head2 dbi_connect_params 192 193Returns optional parameters that are passed to L<DBI/connect> (I<HashRef>). 194 195=head2 dbi_on_connect_do 196 197Returns optional commands to be executed after connecting to the database 198(I<ArrayRef> or I<Str>). 199 200=head2 sqlam_params 201 202Returns optional parameters that are passed to L<SQL::Abstract::More/new> (I<HashRef>). 203 204=head2 sequence_create_query 205 206Returns an L<OpenXPKI::Server::Database::Query> object containing the SQL query 207that creates a new sequence (or a table emulating a sequence, if the driver has 208got the role L<OpenXPKI::Server::Database::Role::SequenceEmulation>). 209 210Parameters: 211 212=over 213 214=item * B<$dbi> - OpenXPKI database handler (C<OpenXPKI::Server::Database>, required) 215 216=item * B<$seq> - Name of SQL sequence to be created (I<Str>, required) 217 218=back 219 220=head2 sequence_drop_query 221 222Returns an L<OpenXPKI::Server::Database::Query> object containing the SQL query 223that removes a sequence (or a table emulating a sequence, if the driver has 224got the role L<OpenXPKI::Server::Database::Role::SequenceEmulation>). 225 226Parameters: 227 228=over 229 230=item * B<$dbi> - OpenXPKI database handler (C<OpenXPKI::Server::Database>, required) 231 232=item * B<$seq> - Name of SQL sequence to be removed (I<Str>, required) 233 234=back 235 236=head2 next_id 237 238Returns the next insert id, i.e. the value of the given sequence (I<Int>). 239 240Parameters: 241 242=over 243 244=item * B<$dbi> - OpenXPKI database handler (C<OpenXPKI::Server::Database>, required) 245 246=item * B<$seq> - Name of SQL sequence whose next value shall be returned (I<Str>, required) 247 248=back 249 250=head2 merge_query 251 252Builds a MERGE query (or emulates it by either an INSERT or an UPDATE query) 253and returns a L<OpenXPKI::Server::Database::Query> object which contains SQL 254string and bind parameters. 255 256Parameters: 257 258=over 259 260=item * B<$dbi> - OpenXPKI database handler (C<OpenXPKI::Server::Database>, required) 261 262=item * B<$into> - Table name including schema (if applicable) (I<Str>, required) 263 264=item * B<$set> - Columns that are always set (INSERT or UPDATE). Hash with 265column name / value pairs. 266 267=item * B<$set_once> - Columns that are only set on INSERT (additional to those 268in the C<where> parameter. Hash with column name / value pairs. 269 270=item * B<$where> - WHERE clause specification that must contain the PRIMARY KEY 271columns and only allows "AND" and "equal" operators: 272C<<{ col1 => val1, col2 => val2 }>> (I<HashRef>) 273 274=back 275 276=cut 277 278#=head2 table_drop_query 279# 280#Returns an L<OpenXPKI::Server::Database::Query> object containing the SQL query 281#that removes a table. If possible the query should contain something like 282#C<IF EXISTS> so that the DMBS does not complain about non-existing tables. 283# 284#Parameters: 285# 286#=over 287# 288#=item * B<$dbi> - OpenXPKI database handler (C<OpenXPKI::Server::Database>, required) 289# 290#=item * B<$table> - Name of table to be dropped (I<Str>, required) 291# 292#=back 293