1DBD::Mock(3pm) User Contributed Perl Documentation DBD::Mock(3pm)
2
6 DBD::Mock - Mock database driver for testing
7
9 use DBI;
10 # connect to your as normal, using 'Mock' as your driver name
12 my $dbh = DBI->connect( 'DBI:Mock:', '', '' )
13 || die "Cannot create handle: $DBI::errstr\n";
14 # create a statement handle as normal and execute with parameters
16 my $sth = $dbh->prepare( 'SELECT this, that FROM foo WHERE id = ?' );
17 $sth->execute( 15 );
18 # Now query the statement handle as to what has been done with it
20 my $mock_params = $sth->{mock_params};
21 print "Used statement: ", $sth->{mock_statement}, "\n",
22 "Bound parameters: ", join( ', ', @{ $mock_params } ), "\n";
23
25 Testing with databases can be tricky. If you are developing a system
26 married to a single database then you can make some assumptions about
27 your environment and ask the user to provide relevant connection
28 information. But if you need to test a framework that uses DBI,
29 particularly a framework that uses different types of persistence
30 schemes, then it may be more useful to simply verify what the framework
31 is trying to do -- ensure the right SQL is generated and that the
32 correct parameters are bound. "DBD::Mock" makes it easy to just modify
33 your configuration (presumably held outside your code) and just use it
34 instead of "DBD::Foo" (like DBD::Pg or DBD::mysql) in your framework.
35 There is no distinct area where using this module makes sense. (Some
37 people may successfully argue that this is a solution looking for a
38 problem...) Indeed, if you can assume your users have something like
39 DBD::AnyData or DBD::SQLite or if you do not mind creating a dependency
40 on them then it makes far more sense to use these legitimate driver
41 implementations and test your application in the real world -- at least
42 as much of the real world as you can create in your tests...
43 And if your database handle exists as a package variable or something
45 else easily replaced at test-time then it may make more sense to use
46 Test::MockObject to create a fully dynamic handle. There is an
47 excellent article by chromatic about using Test::MockObject in this and
48 other ways, strongly recommended. (See "SEE ALSO" for a link)
49 How does it work?
51 "DBD::Mock" comprises a set of classes used by DBI to implement a
52 database driver. But instead of connecting to a datasource and
53 manipulating data found there it tracks all the calls made to the
54 database handle and any created statement handles. You can then inspect
55 them to ensure what you wanted to happen actually happened. For
56 instance, say you have a configuration file with your database
57 connection information:
58 [DBI]
60 dsn = DBI:Pg:dbname=myapp
61 user = foo
62 password = bar
63 And this file is read in at process startup and the handle stored for
65 other procedures to use:
66 package ObjectDirectory;
68 my ( $DBH );
70 sub run_at_startup {
72 my ( $class, $config ) = @_;
73 $config ||= read_configuration( ... );
74 my $dsn = $config->{DBI}{dsn};
75 my $user = $config->{DBI}{user};
76 my $pass = $config->{DBI}{password};
77 $DBH = DBI->connect( $dsn, $user, $pass ) || die ...;
78 }
79 sub get_database_handle {
81 return $DBH;
82 }
83 A procedure might use it like this (ignoring any error handling for the
85 moment):
86 package My::UserActions;
88 sub fetch_user {
90 my ( $class, $login ) = @_;
91 my $dbh = ObjectDirectory->get_database_handle;
92 my $sql = q{
93 SELECT login_name, first_name, last_name, creation_date, num_logins
94 FROM users
95 WHERE login_name = ?
96 };
97 my $sth = $dbh->prepare( $sql );
98 $sth->execute( $login );
99 my $row = $sth->fetchrow_arrayref;
100 return ( $row ) ? User->new( $row ) : undef;
101 }
102 So for the purposes of our tests we just want to ensure that:
104 1. The right SQL is being executed
106 2. The right parameters are bound
107 Assume whether the SQL actually works or not is irrelevant for this
109 test :-)
110 To do that our test might look like:
112 my $config = ObjectDirectory->read_configuration( ... );
114 $config->{DBI}{dsn} = 'DBI:Mock:';
115 ObjectDirectory->run_at_startup( $config );
116 my $login_name = 'foobar';
118 my $user = My::UserActions->fetch_user( $login_name );
119 # Get the handle from ObjectDirectory;
121 # this is the same handle used in the
122 # 'fetch_user()' procedure above
123 my $dbh = ObjectDirectory->get_database_handle();
124 # Ask the database handle for the history
126 # of all statements executed against it
127 my $history = $dbh->{mock_all_history};
128 # Now query that history record to
130 # see if our expectations match reality
131 is(scalar(@{$history}), 1, 'Correct number of statements executed' ;
132 my $login_st = $history->[0];
134 like($login_st->statement,
135 qr/SELECT login_name.*FROM users WHERE login_name = ?/sm,
136 'Correct statement generated' );
137 my $params = $login_st->bound_params;
139 is(scalar(@{$params}), 1, 'Correct number of parameters bound');
140 is($params->[0], $login_name, 'Correct value for parameter 1' );
141 # Reset the handle for future operations
143 $dbh->{mock_clear_history} = 1;
144 The list of properties and what they return is listed below. But in an
146 overall view:
147 · A database handle contains the history of all statements created
149 against it. Other properties set for the handle (e.g.,
150 "PrintError", "RaiseError") are left alone and can be queried as
151 normal, but they do not affect anything. (A future feature may
152 track the sequence/history of these assignments but if there is no
153 demand it probably will not get implemented.)
154 · A statement handle contains the statement it was prepared with plus
156 all bound parameters or parameters passed via "execute()". It can
157 also contain predefined results for the statement handle to
158 "fetch", track how many fetches were called and what its current
159 record is.
160 A Word of Warning
162 This may be an incredibly naive implementation of a DBD. But it works
163 for me...
164
166 Since this is a normal DBI statement handle we need to expose our
167 tracking information as properties (accessed like a hash) rather than
168 methods.
169 Database Driver Properties
171 "mock_connect_fail"
172 This is a boolean property which when set to true (1) will not
173 allow DBI to connect. This can be used to simulate a DSN error or
174 authentication failure. This can then be set back to false (0) to
175 resume normal DBI operations. Here is an example of how this works:
176 # install the DBD::Mock driver
178 my $drh = DBI->install_driver('Mock');
179 $drh->{mock_connect_fail} = 1;
181 # this connection will fail
183 my $dbh = DBI->connect('dbi:Mock:', '', '') || die "Cannot connect";
184 # this connection will throw an exception
186 my $dbh = DBI->connect('dbi:Mock:', '', '', { RaiseError => 1 });
187 $drh->{mock_connect_fail} = 0;
189 # this will work now ...
191 my $dbh = DBI->connect(...);
192 This feature is conceptually different from the "mock_can_connect"
194 attribute of the $dbh in that it has a driver-wide scope, where
195 "mock_can_connect" is handle-wide scope. It also only prevents the
196 initial connection, any $dbh handles created prior to setting
197 "mock_connect_fail" to true (1) will still go on working just fine.
198 "mock_data_sources"
200 This is an ARRAY reference which holds fake data sources which are
201 returned by the Driver and Database Handle's "data_source()"
202 method.
203 "mock_add_data_sources"
205 This takes a string and adds it to the "mock_data_sources"
206 attribute.
207 Database Handle Properties
209 "mock_all_history"
210 Returns an array reference with all history (a.k.a.
211 "DBD::Mock::StatementTrack") objects created against the database
212 handle in the order they were created. Each history object can then
213 report information about the SQL statement used to create it, the
214 bound parameters, etc..
215 "mock_all_history_iterator"
217 Returns a "DBD::Mock::StatementTrack::Iterator" object which will
218 iterate through the current set of "DBD::Mock::StatementTrack"
219 object in the history. See the
220 "DBD::Mock::StatementTrack::Iterator" documentation below for more
221 information.
222 "mock_clear_history"
224 If set to a true value all previous statement history operations
225 will be erased. This includes the history of currently open
226 handles, so if you do something like:
227 my $dbh = get_handle( ... );
229 my $sth = $dbh->prepare( ... );
230 $dbh->{mock_clear_history} = 1;
231 $sth->execute( 'Foo' );
232 You will have no way to learn from the database handle that the
234 statement parameter 'Foo' was bound.
235 This is useful mainly to ensure you can isolate the statement
237 histories from each other. A typical sequence will look like:
238 set handle to framework
240 perform operations
241 analyze mock database handle
242 reset mock database handle history
243 perform more operations
244 analyze mock database handle
245 reset mock database handle history
246 ...
247 "mock_can_connect"
249 This statement allows you to simulate a downed database connection.
250 This is useful in testing how your application/tests will perform
251 in the face of some kind of catastrophic event such as a network
252 outage or database server failure. It is a simple boolean value
253 which defaults to on, and can be set like this:
254 # turn the database off
256 $dbh->{mock_can_connect} = 0;
257 # turn it back on again
259 $dbh->{mock_can_connect} = 1;
260 The statement handle checks this value as well, so something like
262 this will fail in the expected way:
263 $dbh = DBI->connect( 'DBI:Mock:', '', '' );
265 $dbh->{mock_can_connect} = 0;
266 # blows up!
268 my $sth = eval { $dbh->prepare( 'SELECT foo FROM bar' ) });
269 if ( $@ ) {
270 # Here, $DBI::errstr = 'No connection present'
271 }
272 Turning off the database after a statement prepare will fail on the
274 statement "execute()", which is hopefully what you would expect:
275 $dbh = DBI->connect( 'DBI:Mock:', '', '' );
277 # ok!
279 my $sth = eval { $dbh->prepare( 'SELECT foo FROM bar' ) });
280 $dbh->{mock_can_connect} = 0;
281 # blows up!
283 $sth->execute;
284 Similarly:
286 $dbh = DBI->connect( 'DBI:Mock:', '', '' );
288 # ok!
290 my $sth = eval { $dbh->prepare( 'SELECT foo FROM bar' ) });
291 # ok!
293 $sth->execute;
294 $dbh->{mock_can_connect} = 0;
296 # blows up!
298 my $row = $sth->fetchrow_arrayref;
299 Note: The handle attribute "Active" and the handle method "ping"
301 will behave according to the value of "mock_can_connect". So if
302 "mock_can_connect" were to be set to 0 (or off), then both "Active"
303 and "ping" would return false values (or 0).
304 "mock_add_resultset( \@resultset | \%resultset_and_options )"
306 This stocks the database handle with a record set, allowing you to
307 seed data for your application to see if it works properly. Each
308 recordset is a simple arrayref of arrays with the first arrayref
309 being the field names used. Every time a statement handle is
310 created it asks the database handle if it has any resultsets
311 available and if so uses it.
312 Here is a sample usage, partially from the test suite:
314 my @user_results = (
316 [ 'login', 'first_name', 'last_name' ],
317 [ 'cwinters', 'Chris', 'Winters' ],
318 [ 'bflay', 'Bobby', 'Flay' ],
319 [ 'alincoln', 'Abe', 'Lincoln' ],
320 );
321 my @generic_results = (
322 [ 'foo', 'bar' ],
323 [ 'this_one', 'that_one' ],
324 [ 'this_two', 'that_two' ],
325 );
326 my $dbh = DBI->connect( 'DBI:Mock:', '', '' );
328 $dbh->{mock_add_resultset} = \@user_results; # add first resultset
329 $dbh->{mock_add_resultset} = \@generic_results; # add second resultset
330 my ( $sth );
331 eval {
332 $sth = $dbh->prepare( 'SELECT login, first_name, last_name FROM foo' );
333 $sth->execute();
334 };
335 # this will fetch rows from the first resultset...
337 my $row1 = $sth->fetchrow_arrayref;
338 my $user1 = User->new( login => $row->[0],
339 first => $row->[1],
340 last => $row->[2] );
341 is( $user1->full_name, 'Chris Winters' );
342 my $row2 = $sth->fetchrow_arrayref;
344 my $user2 = User->new( login => $row->[0],
345 first => $row->[1],
346 last => $row->[2] );
347 is( $user2->full_name, 'Bobby Flay' );
348 ...
349 my $sth_generic = $dbh->prepare( 'SELECT foo, bar FROM baz' );
351 $sth_generic->execute;
352 # this will fetch rows from the second resultset...
354 my $row = $sth->fetchrow_arrayref;
355 It is possible to assign a hashref where the resultset must be
357 given as value for the "results" key:
358 $dbh->{mock_add_resultset} = {
360 results => [
361 [ 'foo', 'bar' ],
362 [ 'this_one', 'that_one' ],
363 [ 'this_two', 'that_two' ],
364 ],
365 };
366 The reason for the hashref form is that you can add options as
368 described in the following.
369 You can associate a resultset with a particular SQL statement
371 instead of adding them in the order they will be fetched:
372 $dbh->{mock_add_resultset} = {
374 sql => 'SELECT foo, bar FROM baz',
375 results => [
376 [ 'foo', 'bar' ],
377 [ 'this_one', 'that_one' ],
378 [ 'this_two', 'that_two' ],
379 ],
380 };
381 This will return the given results when the statement '"SELECT foo,
383 bar FROM baz"' is prepared/executed. Note that they will be
384 returned every time the statement is prepared/executed, not just
385 the first. It should also be noted that if you want, for some
386 reason, to change the result set bound to a particular SQL
387 statement, all you need to do is add the result set again with the
388 same SQL statement and "DBD::Mock" will overwrite it.
389 If the "sql" parameter is a regular expression reference then the
391 results will be returned for any SQL statements that matches it:
392 $dbh->{mock_add_resultset} = {
394 sql => qr/^SELECT foo FROM/i,
395 results => [
396 [ 'foo' ],
397 [ 'this_one' ],
398 ],
399 };
400 If an SQL statement matches both a specified SQL statement result
402 set and a regular expression result set then the specified SQL
403 statement takes precedence. If two regular expression result sets
404 match then the first one added takes precedence:
405 # Set up our first regex matching result set
407 $dbh->{mock_add_resultset} = {
408 sql => qr/^SELECT foo/,
409 results => [ [ 'foo' ], [ 200 ] ],
410 };
411 # Set up our second regex matching result set
413 # Note - This results set would never be used as the one above will match
414 # and thus take precedence
415 $dbh->{mock_add_resultset} = {
416 sql => qr/^SELECT foo FROM/,
417 results => [ [ 'foo' ], [ 300 ] ],
418 };
419 # Set up our first statically defined result set
421 # This result set will take precedence over the regex matching ones above
422 $dbh->{mock_add_resultset} = {
423 sql => 'SELECT foo FROM bar',
424 results => [[ 'foo' ], [ 50 ]]
425 };
426 # This query will be served by the first regex matching result set
428 my $sth = $dbh->prepare('SELECT foo FROM oof');
429 $sth->execute()
430 my ($result) = $sth->fetchrow_array();
432 is( $result, 200 );
433 # This quere will be served by the statically defined result set
435 $sth = $dbh->prepare('SELECT foo FROM bar');
436 $sth->execute();
437 my ($result2) = $sth->fetchrow_array();
439 is( $result2, 50 );
440 It should also be noted that the "rows" method will return the
442 number of records stocked in the result set. So if your
443 code/application makes use of the "$sth->rows" method for things
444 like "UPDATE" and "DELETE" calls you should stock the result set
445 like so:
446 $dbh->{mock_add_resultset} = {
448 sql => 'UPDATE foo SET baz = 1, bar = 2',
449 # this will appear to have updated 3 rows
450 results => [[ 'rows' ], [], [], []],
451 };
452 # or ...
454 $dbh->{mock_add_resultset} = {
456 sql => 'DELETE FROM foo WHERE bar = 2',
457 # this will appear to have deleted 1 row
458 results => [[ 'rows' ], []],
459 };
460 Now I admit this is not the most elegant way to go about this, but
462 it works for me for now, and until I can come up with a better
463 method, or someone sends me a patch ;) it will do for now.
464 If you want a given statement to fail, you will have to use the
466 hashref method and add a "failure" key. That key must be handed an
467 arrayref with the error number and error string, in that order.
468 $dbh->{mock_add_resultset} = {
470 sql => 'SELECT foo FROM bar',
471 results => DBD::Mock->NULL_RESULTSET,
472 failure => [ 5, 'Ooops!' ],
473 };
474 Without the "sql" attribute the next statement will fail in any
476 case:
477 $dbh->{mock_add_resultset} = {
479 results => DBD::Mock->NULL_RESULTSET,
480 failure => [ 5, 'Ooops!' ],
481 };
482 "mock_get_info"
484 This attribute can be used to set up values for "get_info()". It
485 takes a hashref of attribute_name/value pairs. See DBI for more
486 information on the information types and their meaning.
487 "mock_session"
489 This attribute can be used to set a current "DBD::Mock::Session"
490 object. For more information on this, see the "DBD::Mock::Session"
491 docs below. This attribute can also be used to remove the current
492 session from the $dbh simply by setting it to "undef".
493 "mock_last_insert_id"
495 This attribute is incremented each time an "INSERT" statement is
496 passed to "prepare" on a per-handle basis. It's starting value can
497 be set with the "mock_start_insert_id" attribute (see below).
498 $dbh->{mock_start_insert_id} = 10;
500 my $sth = $dbh->prepare('INSERT INTO Foo (foo, bar) VALUES(?, ?)');
502 $sth->execute(1, 2);
504 # $dbh->{mock_last_insert_id} == 10
505 $sth->execute(3, 4);
507 # $dbh->{mock_last_insert_id} == 11
508 For more examples, please refer to the test file
510 t/025_mock_last_insert_id.t.
511 To access "last_insert_id" using driver specific attributes like
513 "mysql_insertid" and "mariadb_insertid" then you can use "Attribute
514 Aliasing".
515 "mock_start_insert_id"
517 This attribute can be used to set a start value for the
518 "mock_last_insert_id" attribute. It can also be used to effectively
519 reset the "mock_last_insert_id" attribute as well.
520 This attribute also can be used with an ARRAY ref parameter, it's
522 behavior is slightly different in that instead of incrementing the
523 value for every "prepare" it will only increment for each
524 "execute". This allows it to be used over multiple "execute" calls
525 in a single $sth. It's usage looks like this:
526 $dbh->{mock_start_insert_id} = [ 'Foo', 10 ];
528 $dbh->{mock_start_insert_id} = [ 'Baz', 20 ];
529 my $sth1 = $dbh->prepare('INSERT INTO Foo (foo, bar) VALUES(?, ?)');
531 my $sth2 = $dbh->prepare('INSERT INTO Baz (baz, buz) VALUES(?, ?)');
533 $sth1->execute(1, 2);
535 # $dbh->{mock_last_insert_id} == 10
536 $sth2->execute(3, 4);
538 # $dbh->{mock_last_insert_id} == 20
539 Note that "DBD::Mock"'s matching of table names in "INSERT"
541 statements is fairly simple, so if your table names are quoted in
542 the insert statement ("INSERT INTO "Foo"") then you need to quote
543 the name for "mock_start_insert_id":
544 $dbh->{mock_start_insert_id} = [ q{"Foo"}, 10 ];
546 "mock_add_parser"
548 DBI provides some simple parsing capabilities for "SELECT"
549 statements to ensure that placeholders are bound properly. And
550 typically you may simply want to check after the fact that a
551 statement is syntactically correct, or at least what you expect.
552 But other times you may want to parse the statement as it is
554 prepared rather than after the fact. There is a hook in this mock
555 database driver for you to provide your own parsing routine or
556 object.
557 The syntax is simple:
559 $dbh->{mock_add_parser} = sub {
561 my ( $sql ) = @_;
562 unless ( $sql =~ /some regex/ ) {
563 die "does not contain secret fieldname";
564 }
565 };
566 You can also add more than one for a handle. They will be called in
568 order, and the first one to fail will halt the parsing process:
569 $dbh->{mock_add_parser} = \&parse_update_sql;
571 $dbh->{mock_add-parser} = \&parse_insert_sql;
572 Depending on the "PrintError" and "RaiseError" settings in the
574 database handle any parsing errors encountered will issue a "warn"
575 or "die". No matter what the statement handle will be "undef".
576 Instead of providing a subroutine reference you can use an object.
578 The only requirement is that it implements the method "parse()" and
579 takes a SQL statement as the only argument. So you should be able
580 to do something like the following (untested):
581 my $parser = SQL::Parser->new( 'mysql', { RaiseError => 1 } );
583 $dbh->{mock_add_parser} = $parser;
584 "mock_data_sources" & "mock_add_data_sources"
586 These properties will dispatch to the Driver's properties of the
587 same name.
588 Attribute Aliasing
590 Basically this feature allows you to alias attributes to other
591 attributes. So for instance, you can alias a commonly expected
592 attribute like "mysql_insertid" to something "DBD::Mock" already has
593 like "mock_last_insert_id". While you can also just set
594 "mysql_insertid" yourself, this functionality allows it to take
595 advantage of things like the autoincrementing of the
596 "mock_last_insert_id" attribute.
597 The functionality is off by default so as to not cause any issues with
599 backwards compatibility, but can easily be turned on and off like this:
600 # turn it on
602 $DBD::Mock::AttributeAliasing++;
603 # turn it off
605 $DBD::Mock::AttributeAliasing = 0;
606 Once this is turned on, you will need to choose a database specific
608 attribute aliasing table like so:
609 DBI->connect('dbi:Mock:MySQL', '', '');
611 Or, by using the database name if using driver DSNs:
613 DBI->connect('dbi:Mock:host=localhost;port=3306;database=MySQL', '', '');
615 The "MySQL" in the DSN will be picked up and the MySQL specific
617 attribute aliasing will be used.
618 Right now there is only minimal support for MySQL and MariaDB:
620 MySQL
622 Currently the "mysql_insertid" attribute for $dbh and $sth are
623 aliased to the $dbh attribute "mock_last_insert_id".
624 MariaDB
626 Currently the "mariadb_insertid" attribute for $dbh and $sth are
627 aliased to the $dbh attribute "mock_last_insert_id".
628 It is possible to add more aliases though, using the
630 "DBD::Mock:_set_mock_attribute_aliases" function (see the source code
631 for details)
632 Database Driver Methods
634 "last_insert_id"
635 This returns the value of "mock_last_insert_id".
636 In order to capture "begin_work()", "commit()", and "rollback()",
638 "DBD::Mock" will create statements for them, as if you had issued them
639 in the appropriate SQL command line program. They will go through the
640 standard "prepare()"-"execute()" cycle, meaning that any custom SQL
641 parsers will be triggered and "DBD::Mock::Session" will need to know
642 about these statements.
643 "begin_work"
645 This will create a statement with SQL of "BEGIN WORK" and no
646 parameters.
647 "commit"
649 This will create a statement with SQL of "COMMIT" and no
650 parameters.
651 "rollback"
653 This will create a statement with SQL of "ROLLBACK" and no
654 parameters.
655 Statement Handle Properties
657 "Active"
658 Returns true if the handle is a "SELECT" and has more records to
659 fetch, false otherwise. (From the DBI.)
660 "mock_statement"
662 The SQL statement this statement handle was "prepare"d with. So if
663 the handle was created with:
664 my $sth = $dbh->prepare( 'SELECT * FROM foo' );
666 This would return:
668 SELECT * FROM foo
670 The original statement is unmodified so if you are checking against
672 it in tests you may want to use a regex rather than a straight
673 equality check. (However if you use a phrasebook to store your SQL
674 externally you are a step ahead...)
675 "mock_fields"
677 Fields used by the statement. As said elsewhere we do no analysis
678 or parsing to find these, you need to define them beforehand. That
679 said, you do not actually need this very often.
680 Note that this returns the same thing as the normal statement
682 property "FIELD".
683 "mock_params"
685 Returns an arrayref of parameters bound to this statement in the
686 order specified by the bind type. For instance, if you created and
687 stocked a handle with:
688 my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = ? AND is_active = ?' );
690 $sth->bind_param( 2, 'yes' );
691 $sth->bind_param( 1, 7783 );
692 This would return:
694 [ 7738, 'yes' ]
696 The same result will occur if you pass the parameters via
698 "execute()" instead:
699 my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = ? AND is_active = ?' );
701 $sth->execute( 7783, 'yes' );
702 The same using named parameters
704 my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = :id AND is_active = :active' );
706 $sth->bind_param( ':id' => 7783 );
707 $sth->bind_param( ':active' => 'yes' );
708 "mock_param_attrs"
710 Returns an arrayref of any attributes (parameter type) defined for
711 bound parameters (note: you rarely need to define attributes for
712 bound parameters). Where an attribute/type hasn't been that slot
713 in the returned arrayref will be "undef". e.g. for:
714 my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = ? AND is_active = ?' );
716 $sth->bind_param( 2, 'yes' );
717 $sth->bind_param( 1 7783, SQL_INTEGER );
718 This would return:
720 [ SQL_INTEGER, undef ]
722 Passing parameters via "execute()" will always populate the array
724 with "undef", so for:
725 $sth->execute( 7783, 'yes' );
727 This would return:
729 [ undef, undef ]
731 "mock_execution_history"
733 Returns an arrayref where each entry contains the details for an
734 execution of the prepared statement. e.g. after:
735 my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = ? AND is_active = ?' );
737 $sth->bind_param( 2, 'yes' );
738 $sth->bind_param( 1 7783, SQL_INTEGER );
739 $sth->execute();
740 $sth->execute( 1023, 'no' );
742 Then "$sth->{mock_execution_history}" would be:
744 [
746 {
747 params => [ 7783, 'yes' ],
748 attrs => [ SQL_INTEGER, undef ],
749 }, {
750 params => [ 1023, 'no' ],
751 attrs => [ undef, undef ],
752 }
753 ]
754 "mock_records"
756 An arrayref of arrayrefs representing the records the mock
757 statement was stocked with.
758 "mock_num_records"
760 Number of records the mock statement was stocked with; if never
761 stocked it is still 0. (Some weirdos might expect undef...)
762 "mock_num_rows"
764 This returns the same value as mock_num_records. And is what is
765 returned by the "rows" method of the statement handle.
766 "mock_current_record_num"
768 Current record the statement is on; returns 0 in the instances when
769 you have not yet called "execute()" and if you have not yet called
770 a "fetch" method after the execute.
771 "mock_is_executed"
773 Whether "execute()" has been called against the statement handle.
774 Returns 'yes' if so, 'no' if not.
775 "mock_is_finished"
777 Whether "finish()" has been called against the statement handle.
778 Returns 'yes' if so, 'no' if not.
779 "mock_is_depleted"
781 Returns 'yes' if all the records in the recordset have been
782 returned. If no "fetch()" was executed against the statement, or If
783 no return data was set this will return 'no'.
784 "mock_my_history"
786 Returns a "DBD::Mock::StatementTrack" object which tracks the
787 actions performed by this statement handle. Most of the actions are
788 separately available from the properties listed above, so you
789 should never need this.
790
792 This module can be used to emulate Apache::DBI style DBI connection
793 pooling. Just as with "Apache::DBI", you must enable "DBD::Mock::Pool"
794 before loading DBI.
795 use DBD::Mock qw(Pool);
797 # followed by ...
798 use DBI;
799 While this may not seem to make a lot of sense in a single-process
801 testing scenario, it can be useful when testing code which assumes a
802 multi-process "Apache::DBI" pooled environment.
803
805 Under the hood this module does most of the work with a
806 "DBD::Mock::StatementTrack" object. This is most useful when you are
807 reviewing multiple statements at a time, otherwise you might want to
808 use the "mock_*" statement handle attributes instead.
809 "new( %params )"
811 Takes the following parameters:
812 · "return_data": Arrayref of return data records
814 · "fields": Arrayref of field names
816 · "bound_params": Arrayref of bound parameters
818 · "bound_param_attrs": Arrayref of bound parameter attributes
820 "statement" (Statement attribute "mock_statement")
822 Gets/sets the SQL statement used.
823 "fields" (Statement attribute "mock_fields")
825 Gets/sets the fields to use for this statement.
826 "bound_params" (Statement attribute "mock_params")
828 Gets/set the bound parameters to use for this statement.
829 "return_data" (Statement attribute "mock_records")
831 Gets/sets the data to return when asked (that is, when someone
832 calls "fetch" on the statement handle).
833 "current_record_num" (Statement attribute "mock_current_record_num")
835 Gets/sets the current record number.
836 "is_active()" (Statement attribute "Active")
838 Returns true if the statement is a "SELECT" and has more records to
839 fetch, false otherwise. (This is from the DBI, see the 'Active'
840 docs under 'ATTRIBUTES COMMON TO ALL HANDLES'.)
841 "is_executed( $yes_or_no )" (Statement attribute "mock_is_executed")
843 Sets the state of the tracker "executed" flag.
844 "is_finished( $yes_or_no )" (Statement attribute "mock_is_finished")
846 If set to "yes" tells the tracker that the statement is finished.
847 This resets the current record number to 0 and clears out the array
848 ref of returned records.
849 "is_depleted()" (Statement attribute "mock_is_depleted")
851 Returns true if the current record number is greater than the
852 number of records set to return.
853 "num_fields"
855 Returns the number of fields set in the "fields" parameter.
856 "num_rows"
858 Returns the number of records in the current result set.
859 "num_params"
861 Returns the number of parameters set in the "bound_params"
862 parameter.
863 "bound_param( $param_num, $value )"
865 Sets bound parameter $param_num to $value. Returns the arrayref of
866 currently-set bound parameters. This corresponds to the
867 "bind_param" statement handle call.
868 "bound_param_trailing( @params )"
870 Pushes @params onto the list of already-set bound parameters.
871 "mark_executed()"
873 Tells the tracker that the statement has been executed and resets
874 the current record number to 0.
875 "next_record()"
877 If the statement has been depleted (all records returned) returns
878 "undef"; otherwise it gets the current record for returning,
879 increments the current record number and returns the current
880 record.
881 "to_string()"
883 Tries to give a decent depiction of the object state for use in
884 debugging.
885
887 This object can be used to iterate through the current set of
888 "DBD::Mock::StatementTrack" objects in the history by fetching the
889 "mock_all_history_iterator" attribute from a database handle. This
890 object is very simple and is meant to be a convenience to make writing
891 long test script easier. Aside from the constructor ("new") this object
892 has the following methods.
893 "next()"
895 Calling "next" will return the next "DBD::Mock::StatementTrack"
896 object in the history. If there are no more
897 "DBD::Mock::StatementTrack" objects available, then this method
898 will return false.
899 "reset()"
901 This will reset the internal pointer to the beginning of the
902 statement history.
903
905 The "DBD::Mock::Session" object is an alternate means of specifying the
906 SQL statements and result sets for "DBD::Mock". The idea is that you
907 can specify a complete 'session' of usage, which will be verified
908 through "DBD::Mock". Here is an example:
909 my $session = DBD::Mock::Session->new('my_session' => (
911 {
912 statement => "SELECT foo FROM bar", # as a string
913 results => [[ 'foo' ], [ 'baz' ]]
914 },
915 {
916 statement => qr/UPDATE bar SET foo \= \'bar\'/, # as a reg-exp
917 results => [[]]
918 },
919 {
920 statement => sub { # as a CODE ref
921 my ($SQL, $state) = @_;
922 return $SQL eq "SELECT foo FROM bar";
923 },
924 results => [[ 'foo' ], [ 'bar' ]]
925 },
926 {
927 # with bound parameters
928 statement => "SELECT foo FROM bar WHERE baz = ? AND borg = ?",
929 # check exact bound param value,
930 # then check it against regexp
931 bound_params => [ 10, qr/\d+/ ],
932 results => [[ 'foo' ], [ 'baz' ]]
933 }
934 ));
935 As you can see, a session is essentially made up a list of HASH
937 references we call 'states'. Each state has a "statement" and a set of
938 "results". If "DBD::Mock" finds a session in the "mock_session"
939 attribute, then it will pass the current $dbh and SQL statement to that
940 "DBD::Mock::Session". The SQL statement will be checked against the
941 "statement" field in the current state. If it passes, then the
942 "results" of the current state will get fed to "DBD::Mock" through the
943 "mock_add_resultset" attribute. We then advance to the next state in
944 the session, and wait for the next call through "DBD::Mock". If at any
945 time the SQL statement does not match the current state's "statement",
946 or the session runs out of available states, an error will be raised
947 (and propagated through the normal DBI error handling based on your
948 values for "RaiseError" and "PrintError").
949 As can be seen in the session element, bound parameters can also be
951 supplied and tested. In this statement, the SQL is compared, then when
952 the statement is executed, the bound parameters are also checked. The
953 bound parameters must match in both number of parameters and the
954 parameters themselves, or an error will be raised.
955 As can also be seen in the example above, "statement" fields can come
957 in many forms. The simplest is a string, which will be compared using
958 "eq" against the currently running statement. The next is a reg-exp
959 reference, this too will get compared against the currently running
960 statement. The last option is a CODE ref, this is sort of a catch-all
961 to allow for a wide range of SQL comparison approaches (including using
962 modules like SQL::Statement or SQL::Parser for detailed functional
963 comparisons). The first argument to the CODE ref will be the currently
964 active SQL statement to compare against, the second argument is a
965 reference to the current state HASH (in case you need to alter the
966 results, or store extra information). The CODE is evaluated in boolean
967 context and throws and exception if it is false.
968 "new ($session_name, @session_states)"
970 A $session_name can be optionally be specified, along with at least
971 one @session_states. If you don't specify a $session_name, then a
972 default one will be created for you. The @session_states must all
973 be HASH references as well, if this conditions fail, an exception
974 will be thrown.
975 "verify_statement ($dbh, $SQL)"
977 This will check the $SQL against the current state's "statement"
978 value, and if it passes will add the current state's "results" to
979 the $dbh. If for some reason the "statement" value is bad, not of
980 the prescribed type, an exception is thrown. See above for more
981 details.
982 "verify_bound_params ($dbh, $params)"
984 If the "bound_params" slot is available in the current state, this
985 will check the $params against the current state's "bound_params"
986 value. Both number of parameters and the parameters themselves must
987 match, or an error will be raised.
988 "reset"
990 Calling this method will reset the state of the session object so
991 that it can be reused.
992
994 All functionality listed here is highly experimental and should be used
995 with great caution (if at all).
996 Connection Callbacks
998 This feature allows you to define callbacks that get executed when
999 "DBI->connect" is called.
1000 To set a series of callbacks you use the
1002 "DBD::Mock::dr::set_connect_callbacks" function
1003 use DBD::Mock::dr;
1005 DBD::Mock::dr::set_connect_callbacks( sub {
1007 my ( $dbh, $dsn, $user, $password, $attributes ) = @_;
1008 $dbh->{mock_add_resultset} = {
1010 sql => 'SELECT foo FROM bar',
1011 results => [[ 'foo' ], [ 10 ]]
1012 };
1013 } );
1014 To set more than one callback to you can simply add extra callbacks
1016 to your call to "DBD::Mock::dr::set_connect_callbacks"
1017 DBD::Mock::dr::set_connect_callbacks(
1019 sub {
1020 my ( $dbh, $dsn, $user, $password, $attributes ) = @_;
1021 $dbh->{mock_add_resultset} = {
1023 sql => 'SELECT foo FROM bar',
1024 results => [[ 'foo' ], [ 10 ]]
1025 };
1026 },
1027 sub {
1029 my ( $dbh, $dsn, $user, $password, $attributes ) = @_;
1030 $dbh->{mock_add_resultset} = {
1032 sql => 'SELECT foo FROM bar',
1033 results => [[ 'foo' ], [ 10 ]]
1034 };
1035 }
1036 );
1037 Or you can extend the existing set of callbacks with the
1039 "DBD::Mock::dr::add_connect_callbacks" function
1040 DBD::Mock::dr::add_connect_callbacks( sub {
1042 ( my $dbh, $dsn, $user, $password, $attributes ) = @_;
1043 $dbh->{mock_add_resultset} = {
1045 sql => 'SELECT bar FROM foo',
1046 results => [[ 'bar' ], [ 50 ]]
1047 };
1048 } );
1049 table_info
1051 This feature adds support for DBI's "table_info" method ( Note this
1052 functionality is unstable when used with DBI version 1.634 and
1053 below).
1054 To mock the table info for a search of the "testSchema" database
1056 schema you would use the following:
1057 $dbh->{mock_add_table_info} = {
1059 cataloge => undef,
1060 schema => 'testSchema',
1061 table => undef,
1062 type => undef,
1063 table_info => [
1064 [ 'TABLE_CAT', 'TABLE_SCHEM', 'TABLE_NAME', 'TABLE_TYPE', 'REMARKS' ],
1065 [ undef, 'testSchema', 'foo', 'TABLE', undef ],
1066 [ undef, 'testSchema', 'bar', 'VIEW', undef ],
1067 ],
1068 };
1069 The "cataloge", "schema", "table" and "type" parameters need to
1071 explicitly match what you expect table_info to be called with
1072 (note: "table_info" treats "undef" and '' the same).
1073 Similar to the "mock_results_sets", the "table_info" parameter's
1075 first entry is an arrayref of column names, and the rest are the
1076 values of the rows returned (one arrayref per row).
1077 If you need to cover listing schemas then you'd use:
1079 $dbh->{mock_add_table_info} = {
1081 schema => '%',
1082 table_info => [
1083 [ 'TABLE_CAT', 'TABLE_SCHEM', 'TABLE_NAME', 'TABLE_TYPE', 'REMARKS' ],
1084 [ undef, 'testSchema', undef, undef, undef ],
1085 [ undef, 'testSchema_2', undef, undef, undef ],
1086 ],
1087 }
1088 To clear the current mocked table info set the database handle's
1090 "mock_clear_table_info" attribute to 1
1091 $dbh->{mock_clear_table_info} = 1;
1093 Result Set Callbacks
1095 If you need your result sets to be more dynamic (e.g. if they need
1096 to return different results based upon bound parameters) then you
1097 can use a callback.
1098 $dbh->{mock_add_resultset} = {
1100 sql => 'SELECT a FROM b WHERE c = ?',
1101 callback => sub {
1102 my @bound_params = @_;
1103 my %result = (
1105 fields => [ "a" ],
1106 rows => [[ 1] ]
1107 );
1108 if ($bound_params[0] == 1) {
1110 $result{rows} = [ [32] ];
1111 } elsif ($bound_params[0] == 2) {
1112 $result{rows} = [ [43] ];
1113 }
1114 return %result;
1116 },
1117 };
1118 my $sth = $dbh->prepare('SELECT a FROM b WHERE c = ?');
1120 my $rows = $sth->execute(1);
1122 my ($result) = $sth->fetchrow_array(); # $result will be 32
1123 $rows = $sth->execute(2);
1125 ($result) = $sth->fetchrow_array(); # $result this time will be 43
1126 $rows = $sth->execute(33); # $results this time will be 1
1128 ($result) = $sth->fetchrow_array();
1130 The callback needs to return a hash with a "rows" key that is an
1132 array ref of arrays containing the values to return as the answer
1133 to the query. In addition a "fields" key can also be returned with
1134 an array ref of field names. If a "fields" key isn't present in the
1135 returned the hash then the fields will be taken from the
1136 "mock_add_resultset"'s "results" parameter.
1137 $dbh->{mock_add_resultset} = {
1139 sql => 'SELECT x FROM y WHERE z = ?',
1140 results => [ ["x"] ],
1141 callback => sub {
1142 my @bound_params = @_;
1143 my %result = ( rows => [[ 1] ] );
1145 if ($bound_params[0] == 1) {
1147 $result{rows} = [ [32] ];
1148 } elsif ($bound_params[0] == 2) {
1149 $result{rows} = [ [43] ];
1150 }
1151 return %result;
1153 },
1154 };
1155 my $sth = $dbh->prepare('SELECT x FROM y WHERE z = ?');
1157 my $rows = $sth->execute(1);
1159 my ($result) = $sth->fetchrow_array(); # $result will be 32
1160 $rows = $sth->execute(2);
1162 ($result) = $sth->fetchrow_array(); # $result will be 43
1163 $rows = $sth->execute(33);
1165 ($result) = $sth->fetchrow_array(); # $result will be 1
1166 By default result sets which only define their field names in their
1168 callback return values will have a "NUM_OF_FIELDS" property of 0
1169 until after the statement has actually been executed. This is to
1170 make sure that "DBD::Mock" stays compatible with previous versions.
1171 If you need the "NUM_OF_FIELDS" property to be undef in this
1172 situation then set the $DBD::Mock::DefaultFieldsToUndef flag to 1.
1173 If you're mocking an INSERT statement with a callback and you want
1175 to explicitly set the database's "last_insert_id" value then you
1176 can use the "last_insert_id" key in the result set. If you don't
1177 specify a "last_insert_id" then the standard "DBD::Mock" logic for
1178 generating an value for the last inserted item will be followed.
1179 This will allow you to mock MySQL/MariaDB INSERT queries that use
1180 "ON DUPLICATE KEY UPDATE" logic to set the "last_insert_id".
1181 $dbh->{mock_add_resultset} = {
1183 sql => 'INSERT INTO y ( x ) VALUES ( ? ) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID( id )',
1184 callback => sub {
1185 my @bound_params = @_;
1186 my %result = (
1188 fields => [],
1189 rows => [],
1190 last_insert_id => 99,
1191 );
1192 return %result;
1194 },
1195 };
1196 Result Set Custom Attributes
1198 If you're mocking a database driver that has it's own custom
1199 attributes attached to its statement handles then you can use the
1200 result sets "prepare_attributes" and "execute_attributes" options.
1201 The "prepare_attributes" option takes a hashref that maps statement
1203 handle attribute names to their values. The attributes are set at
1204 the point that the statement is prepared.
1205 $dbh->{mock_add_resultset} = {
1207 sql => 'SELECT foo FROM bar',
1208 prepare_attributes => {
1209 sqlite_unprepared_statements => ' ',
1210 },
1211 results => [[ 'foo' ], [ 10 ]]
1212 };
1213 The "execute_attributes" option also takes a hashref that maps
1215 statement handle attribute names to their values, however these
1216 will only be set when the statement is executed.
1217 $dbh->{mock_add_resultset} = {
1219 sql => 'SELECT foo FROM bar',
1220 execute_attributes => {
1221 syb_result_type => 1,
1222 },
1223 results => [[ 'foo' ], [ 10 ]]
1224 };
1225 If an attribute is also present in the "prepare_attributes" option
1227 then the "prepare_attributes" version will take precedence up to
1228 the point the statement handle is executed, at which point the
1229 "execute_attributes" version will take precedence.
1230 It is also possible to set "execute_attributes" from a result set's
1232 callback by returning them under the "execute_attributes" key in
1233 your callback's response.
1234 $dbh->{mock_add_resultset} = {
1236 sql => 'SELECT baz FROM qux',
1237 callback => sub {
1238 my @bound_params = @_;
1239 my %result = (
1241 fields => [ 'baz'],
1242 rows => [],
1243 execute_attributes => {
1244 foo => 'bar'
1245 },
1246 );
1247 return %result;
1249 }
1250 };
1251 If a result set has an "execute_attributes" option and a callback
1253 that also returns an "execute_attributes" key then the callback's
1254 "execute_attributes" value will take precedence.
1255
1257 Odd $dbh attribute behavior
1258 When writing the test suite I encountered some odd behavior with
1259 some $dbh attributes. I still need to get deeper into how DBD's
1260 work to understand what it is that is actually doing wrong.
1261
1263 Make DBD specific handlers
1264 Each DBD has its own quirks and issues, it would be nice to be able
1265 to handle those issues with "DBD::Mock" in some way. I have an
1266 number of ideas already, but little time to sit down and really
1267 flesh them out. If you have any suggestions or thoughts, feel free
1268 to email me with them.
1269 Enhance the "DBD::Mock::StatementTrack" object
1271 I would like to have the "DBD::Mock::StatementTrack" object handle
1272 more of the "mock_*" attributes. This would encapsulate much of the
1273 "mock_*" behavior in one place, which would be a good thing.
1274
1276 DBI
1277 DBD::NullP, which provided a good starting point
1279 Test::MockObject, which provided the approach
1281 Test::MockObject article -
1283 <http://www.perl.com/pub/a/2002/07/10/tmo.html>
1284 Perl Code Kata: Testing Databases -
1286 <http://www.perl.com/pub/a/2005/02/10/database_kata.html>
1287
1289 Thanks to Ryan Gerry for his patch in RT #26604.
1290 Thanks to Marc Beyer for his patch in RT #16951.
1291 Thanks to Justin DeVuyst for the mock_connect_fail idea.
1292 Thanks to Thilo Planz for the code for "bind_param_inout".
1293 Thanks to Shlomi Fish for help tracking down RT Bug #11515.
1294 Thanks to Collin Winter for the patch to fix the "begin_work()",
1295 "commit()" and "rollback()" methods.
1296 Thanks to Andrew McHarg <amcharg@acm.org> for "fetchall_hashref()",
1297 "fetchrow_hashref()" and "selectcol_arrayref()" methods and tests.
1298 Thanks to Andrew W. Gibbs for the "mock_last_insert_ids" patch and
1299 test.
1300 Thanks to Chas Owens for patch and test for the "mock_can_prepare",
1301 "mock_can_execute", and "mock_can_fetch" features.
1302 Thanks to Tomas Zemresfor the unit test in RT #71438.
1303 Thanks to Bernhard Graf for multiple patches fixing a range of issues
1304 and adding a new One Shot Failure feature to "mock_add_resultset".
1305 Thanks to Erik Huelsmann for testing the new result set custom
1306 attributes feature.
1307
1309 Copyright (C) 2004 Chris Winters <chris@cwinters.com>
1310 Copyright (C) 2004-2007 Stevan Little <stevan@iinteractive.com>
1312 Copyright (C) 2007 Rob Kinyon <rob.kinyon@gmail.com>
1314 Copyright (C) 2011 Mariano Wahlmann <dichoso _at_ gmail.com>
1316 Copyright (C) 2019 Jason Cooper <JLCOOPER@cpan.org>
1318 This library is free software; you can redistribute it and/or modify it
1320 under the same terms as Perl itself.
1321
1323 Chris Winters <chris@cwinters.com>
1324 Stevan Little <stevan@iinteractive.com>
1326 Rob Kinyon <rob.kinyon@gmail.com>
1328 Mariano Wahlmann <dichoso _at_ gmail.com>
1330 Jason Cooper <JLCOOPER@cpan.org>
1332perl v5.32.1 2021-02-22 DBD::Mock(3pm)