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