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() method.
202 "mock_add_data_sources"
204 This takes a string and adds it to the "mock_data_sources"
205 attribute.
206 Database Handle Properties
208 "mock_all_history"
209 Returns an array reference with all history (a.k.a.
210 "DBD::Mock::StatementTrack") objects created against the database
211 handle in the order they were created. Each history object can then
212 report information about the SQL statement used to create it, the
213 bound parameters, etc..
214 "mock_all_history_iterator"
216 Returns a "DBD::Mock::StatementTrack::Iterator" object which will
217 iterate through the current set of "DBD::Mock::StatementTrack"
218 object in the history. See the
219 "DBD::Mock::StatementTrack::Iterator" documentation below for more
220 information.
221 "mock_clear_history"
223 If set to a true value all previous statement history operations
224 will be erased. This includes the history of currently open
225 handles, so if you do something like:
226 my $dbh = get_handle( ... );
228 my $sth = $dbh->prepare( ... );
229 $dbh->{mock_clear_history} = 1;
230 $sth->execute( 'Foo' );
231 You will have no way to learn from the database handle that the
233 statement parameter 'Foo' was bound.
234 This is useful mainly to ensure you can isolate the statement
236 histories from each other. A typical sequence will look like:
237 set handle to framework
239 perform operations
240 analyze mock database handle
241 reset mock database handle history
242 perform more operations
243 analyze mock database handle
244 reset mock database handle history
245 ...
246 "mock_can_connect"
248 This statement allows you to simulate a downed database connection.
249 This is useful in testing how your application/tests will perform
250 in the face of some kind of catastrophic event such as a network
251 outage or database server failure. It is a simple boolean value
252 which defaults to on, and can be set like this:
253 # turn the database off
255 $dbh->{mock_can_connect} = 0;
256 # turn it back on again
258 $dbh->{mock_can_connect} = 1;
259 The statement handle checks this value as well, so something like
261 this will fail in the expected way:
262 $dbh = DBI->connect( 'DBI:Mock:', '', '' );
264 $dbh->{mock_can_connect} = 0;
265 # blows up!
267 my $sth = eval { $dbh->prepare( 'SELECT foo FROM bar' ) });
268 if ( $@ ) {
269 # Here, $DBI::errstr = 'No connection present'
270 }
271 Turning off the database after a statement prepare will fail on the
273 statement execute(), which is hopefully what you would expect:
274 $dbh = DBI->connect( 'DBI:Mock:', '', '' );
276 # ok!
278 my $sth = eval { $dbh->prepare( 'SELECT foo FROM bar' ) });
279 $dbh->{mock_can_connect} = 0;
280 # blows up!
282 $sth->execute;
283 Similarly:
285 $dbh = DBI->connect( 'DBI:Mock:', '', '' );
287 # ok!
289 my $sth = eval { $dbh->prepare( 'SELECT foo FROM bar' ) });
290 # ok!
292 $sth->execute;
293 $dbh->{mock_can_connect} = 0;
295 # blows up!
297 my $row = $sth->fetchrow_arrayref;
298 Note: The handle attribute "Active" and the handle method "ping"
300 will behave according to the value of "mock_can_connect". So if
301 "mock_can_connect" were to be set to 0 (or off), then both "Active"
302 and "ping" would return false values (or 0).
303 "mock_add_resultset( \@resultset | \%resultset_and_options )"
305 This stocks the database handle with a record set, allowing you to
306 seed data for your application to see if it works properly. Each
307 recordset is a simple arrayref of arrays with the first arrayref
308 being the field names used. Every time a statement handle is
309 created it asks the database handle if it has any resultsets
310 available and if so uses it.
311 Here is a sample usage, partially from the test suite:
313 my @user_results = (
315 [ 'login', 'first_name', 'last_name' ],
316 [ 'cwinters', 'Chris', 'Winters' ],
317 [ 'bflay', 'Bobby', 'Flay' ],
318 [ 'alincoln', 'Abe', 'Lincoln' ],
319 );
320 my @generic_results = (
321 [ 'foo', 'bar' ],
322 [ 'this_one', 'that_one' ],
323 [ 'this_two', 'that_two' ],
324 );
325 my $dbh = DBI->connect( 'DBI:Mock:', '', '' );
327 $dbh->{mock_add_resultset} = \@user_results; # add first resultset
328 $dbh->{mock_add_resultset} = \@generic_results; # add second resultset
329 my ( $sth );
330 eval {
331 $sth = $dbh->prepare( 'SELECT login, first_name, last_name FROM foo' );
332 $sth->execute();
333 };
334 # this will fetch rows from the first resultset...
336 my $row1 = $sth->fetchrow_arrayref;
337 my $user1 = User->new( login => $row->[0],
338 first => $row->[1],
339 last => $row->[2] );
340 is( $user1->full_name, 'Chris Winters' );
341 my $row2 = $sth->fetchrow_arrayref;
343 my $user2 = User->new( login => $row->[0],
344 first => $row->[1],
345 last => $row->[2] );
346 is( $user2->full_name, 'Bobby Flay' );
347 ...
348 my $sth_generic = $dbh->prepare( 'SELECT foo, bar FROM baz' );
350 $sth_generic->execute;
351 # this will fetch rows from the second resultset...
353 my $row = $sth->fetchrow_arrayref;
354 It is possible to assign a hashref where the resultset must be
356 given as value for the "results" key:
357 $dbh->{mock_add_resultset} = {
359 results => [
360 [ 'foo', 'bar' ],
361 [ 'this_one', 'that_one' ],
362 [ 'this_two', 'that_two' ],
363 ],
364 };
365 The reason for the hashref form is that you can add options as
367 described in the following.
368 You can associate a resultset with a particular SQL statement
370 instead of adding them in the order they will be fetched:
371 $dbh->{mock_add_resultset} = {
373 sql => 'SELECT foo, bar FROM baz',
374 results => [
375 [ 'foo', 'bar' ],
376 [ 'this_one', 'that_one' ],
377 [ 'this_two', 'that_two' ],
378 ],
379 };
380 This will return the given results when the statement '"SELECT foo,
382 bar FROM baz"' is prepared/executed. Note that they will be
383 returned every time the statement is prepared/executed, not just
384 the first. It should also be noted that if you want, for some
385 reason, to change the result set bound to a particular SQL
386 statement, all you need to do is add the result set again with the
387 same SQL statement and "DBD::Mock" will overwrite it.
388 If the "sql" parameter is a regular expression reference then the
390 results will be returned for any SQL statements that matches it:
391 $dbh->{mock_add_resultset} = {
393 sql => qr/^SELECT foo FROM/i,
394 results => [
395 [ 'foo' ],
396 [ 'this_one' ],
397 ],
398 };
399 If an SQL statement matches both a specified SQL statement result
401 set and a regular expression result set then the specified SQL
402 statement takes precedence. If two regular expression result sets
403 match then the first one added takes precedence:
404 # Set up our first regex matching result set
406 $dbh->{mock_add_resultset} = {
407 sql => qr/^SELECT foo/,
408 results => [ [ 'foo' ], [ 200 ] ],
409 };
410 # Set up our second regex matching result set
412 # Note - This results set would never be used as the one above will match
413 # and thus take precedence
414 $dbh->{mock_add_resultset} = {
415 sql => qr/^SELECT foo FROM/,
416 results => [ [ 'foo' ], [ 300 ] ],
417 };
418 # Set up our first statically defined result set
420 # This result set will take precedence over the regex matching ones above
421 $dbh->{mock_add_resultset} = {
422 sql => 'SELECT foo FROM bar',
423 results => [[ 'foo' ], [ 50 ]]
424 };
425 # This query will be served by the first regex matching result set
427 my $sth = $dbh->prepare('SELECT foo FROM oof');
428 $sth->execute()
429 my ($result) = $sth->fetchrow_array();
431 is( $result, 200 );
432 # This quere will be served by the statically defined result set
434 $sth = $dbh->prepare('SELECT foo FROM bar');
435 $sth->execute();
436 my ($result2) = $sth->fetchrow_array();
438 is( $result2, 50 );
439 It should also be noted that the "rows" method will return the
441 number of records stocked in the result set. So if your
442 code/application makes use of the "$sth->rows" method for things
443 like "UPDATE" and "DELETE" calls you should stock the result set
444 like so:
445 $dbh->{mock_add_resultset} = {
447 sql => 'UPDATE foo SET baz = 1, bar = 2',
448 # this will appear to have updated 3 rows
449 results => [[ 'rows' ], [], [], []],
450 };
451 # or ...
453 $dbh->{mock_add_resultset} = {
455 sql => 'DELETE FROM foo WHERE bar = 2',
456 # this will appear to have deleted 1 row
457 results => [[ 'rows' ], []],
458 };
459 Now I admit this is not the most elegant way to go about this, but
461 it works for me for now, and until I can come up with a better
462 method, or someone sends me a patch ;) it will do for now.
463 If you want a given statement to fail, you will have to use the
465 hashref method and add a "failure" key. That key must be handed an
466 arrayref with the error number and error string, in that order.
467 $dbh->{mock_add_resultset} = {
469 sql => 'SELECT foo FROM bar',
470 results => DBD::Mock->NULL_RESULTSET,
471 failure => [ 5, 'Ooops!' ],
472 };
473 Without the "sql" attribute the next statement will fail in any
475 case:
476 $dbh->{mock_add_resultset} = {
478 results => DBD::Mock->NULL_RESULTSET,
479 failure => [ 5, 'Ooops!' ],
480 };
481 "mock_get_info"
483 This attribute can be used to set up values for get_info(). It
484 takes a hashref of attribute_name/value pairs. See DBI for more
485 information on the information types and their meaning.
486 "mock_session"
488 This attribute can be used to set a current "DBD::Mock::Session"
489 object. For more information on this, see the "DBD::Mock::Session"
490 docs below. This attribute can also be used to remove the current
491 session from the $dbh simply by setting it to "undef".
492 "mock_last_insert_id"
494 This attribute is incremented each time an "INSERT" statement is
495 passed to "prepare" on a per-handle basis. It's starting value can
496 be set with the "mock_start_insert_id" attribute (see below).
497 $dbh->{mock_start_insert_id} = 10;
499 my $sth = $dbh->prepare('INSERT INTO Foo (foo, bar) VALUES(?, ?)');
501 $sth->execute(1, 2);
503 # $dbh->{mock_last_insert_id} == 10
504 $sth->execute(3, 4);
506 # $dbh->{mock_last_insert_id} == 11
507 For more examples, please refer to the test file
509 t/025_mock_last_insert_id.t.
510 To access "last_insert_id" using driver specific attributes like
512 "mysql_insertid" and "mariadb_insertid" then you can use "Attribute
513 Aliasing".
514 "mock_start_insert_id"
516 This attribute can be used to set a start value for the
517 "mock_last_insert_id" attribute. It can also be used to effectively
518 reset the "mock_last_insert_id" attribute as well.
519 This attribute also can be used with an ARRAY ref parameter, it's
521 behavior is slightly different in that instead of incrementing the
522 value for every "prepare" it will only increment for each
523 "execute". This allows it to be used over multiple "execute" calls
524 in a single $sth. It's usage looks like this:
525 $dbh->{mock_start_insert_id} = [ 'Foo', 10 ];
527 $dbh->{mock_start_insert_id} = [ 'Baz', 20 ];
528 my $sth1 = $dbh->prepare('INSERT INTO Foo (foo, bar) VALUES(?, ?)');
530 my $sth2 = $dbh->prepare('INSERT INTO Baz (baz, buz) VALUES(?, ?)');
532 $sth1->execute(1, 2);
534 # $dbh->{mock_last_insert_id} == 10
535 $sth2->execute(3, 4);
537 # $dbh->{mock_last_insert_id} == 20
538 Note that "DBD::Mock"'s matching of table names in "INSERT"
540 statements is fairly simple, so if your table names are quoted in
541 the insert statement ("INSERT INTO "Foo"") then you need to quote
542 the name for "mock_start_insert_id":
543 $dbh->{mock_start_insert_id} = [ q{"Foo"}, 10 ];
545 "mock_add_parser"
547 DBI provides some simple parsing capabilities for "SELECT"
548 statements to ensure that placeholders are bound properly. And
549 typically you may simply want to check after the fact that a
550 statement is syntactically correct, or at least what you expect.
551 But other times you may want to parse the statement as it is
553 prepared rather than after the fact. There is a hook in this mock
554 database driver for you to provide your own parsing routine or
555 object.
556 The syntax is simple:
558 $dbh->{mock_add_parser} = sub {
560 my ( $sql ) = @_;
561 unless ( $sql =~ /some regex/ ) {
562 die "does not contain secret fieldname";
563 }
564 };
565 You can also add more than one for a handle. They will be called in
567 order, and the first one to fail will halt the parsing process:
568 $dbh->{mock_add_parser} = \&parse_update_sql;
570 $dbh->{mock_add-parser} = \&parse_insert_sql;
571 Depending on the "PrintError" and "RaiseError" settings in the
573 database handle any parsing errors encountered will issue a "warn"
574 or "die". No matter what the statement handle will be "undef".
575 Instead of providing a subroutine reference you can use an object.
577 The only requirement is that it implements the method parse() and
578 takes a SQL statement as the only argument. So you should be able
579 to do something like the following (untested):
580 my $parser = SQL::Parser->new( 'mysql', { RaiseError => 1 } );
582 $dbh->{mock_add_parser} = $parser;
583 "mock_data_sources" & "mock_add_data_sources"
585 These properties will dispatch to the Driver's properties of the
586 same name.
587 Attribute Aliasing
589 Basically this feature allows you to alias attributes to other
590 attributes. So for instance, you can alias a commonly expected
591 attribute like "mysql_insertid" to something "DBD::Mock" already has
592 like "mock_last_insert_id". While you can also just set
593 "mysql_insertid" yourself, this functionality allows it to take
594 advantage of things like the autoincrementing of the
595 "mock_last_insert_id" attribute.
596 The functionality is off by default so as to not cause any issues with
598 backwards compatibility, but can easily be turned on and off like this:
599 # turn it on
601 $DBD::Mock::AttributeAliasing++;
602 # turn it off
604 $DBD::Mock::AttributeAliasing = 0;
605 Once this is turned on, you will need to choose a database specific
607 attribute aliasing table like so:
608 DBI->connect('dbi:Mock:MySQL', '', '');
610 Or, by using the database name if using driver DSNs:
612 DBI->connect('dbi:Mock:host=localhost;port=3306;database=MySQL', '', '');
614 The "MySQL" in the DSN will be picked up and the MySQL specific
616 attribute aliasing will be used.
617 Right now there is only minimal support for MySQL and MariaDB:
619 MySQL
621 Currently the "mysql_insertid" attribute for $dbh and $sth are
622 aliased to the $dbh attribute "mock_last_insert_id".
623 MariaDB
625 Currently the "mariadb_insertid" attribute for $dbh and $sth are
626 aliased to the $dbh attribute "mock_last_insert_id".
627 It is possible to add more aliases though, using the
629 "DBD::Mock:_set_mock_attribute_aliases" function (see the source code
630 for details)
631 Database Driver Methods
633 "last_insert_id"
634 This returns the value of "mock_last_insert_id".
635 In order to capture begin_work(), commit(), and rollback(), "DBD::Mock"
637 will create statements for them, as if you had issued them in the
638 appropriate SQL command line program. They will go through the standard
639 prepare()-execute() cycle, meaning that any custom SQL parsers will be
640 triggered and "DBD::Mock::Session" will need to know about these
641 statements.
642 "begin_work"
644 This will create a statement with SQL of "BEGIN WORK" and no
645 parameters.
646 "commit"
648 This will create a statement with SQL of "COMMIT" and no
649 parameters.
650 "rollback"
652 This will create a statement with SQL of "ROLLBACK" and no
653 parameters.
654 Statement Handle Properties
656 "Active"
657 Returns true if the handle is a "SELECT" and has more records to
658 fetch, false otherwise. (From the DBI.)
659 "mock_statement"
661 The SQL statement this statement handle was "prepare"d with. So if
662 the handle was created with:
663 my $sth = $dbh->prepare( 'SELECT * FROM foo' );
665 This would return:
667 SELECT * FROM foo
669 The original statement is unmodified so if you are checking against
671 it in tests you may want to use a regex rather than a straight
672 equality check. (However if you use a phrasebook to store your SQL
673 externally you are a step ahead...)
674 "mock_fields"
676 Fields used by the statement. As said elsewhere we do no analysis
677 or parsing to find these, you need to define them beforehand. That
678 said, you do not actually need this very often.
679 Note that this returns the same thing as the normal statement
681 property "FIELD".
682 "mock_params"
684 Returns an arrayref of parameters bound to this statement in the
685 order specified by the bind type. For instance, if you created and
686 stocked a handle with:
687 my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = ? AND is_active = ?' );
689 $sth->bind_param( 2, 'yes' );
690 $sth->bind_param( 1, 7783 );
691 This would return:
693 [ 7738, 'yes' ]
695 The same result will occur if you pass the parameters via execute()
697 instead:
698 my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = ? AND is_active = ?' );
700 $sth->execute( 7783, 'yes' );
701 The same using named parameters
703 my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = :id AND is_active = :active' );
705 $sth->bind_param( ':id' => 7783 );
706 $sth->bind_param( ':active' => 'yes' );
707 "mock_param_attrs"
709 Returns an arrayref of any attributes (parameter type) defined for
710 bound parameters (note: you rarely need to define attributes for
711 bound parameters). Where an attribute/type hasn't been that slot
712 in the returned arrayref will be "undef". e.g. for:
713 my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = ? AND is_active = ?' );
715 $sth->bind_param( 2, 'yes' );
716 $sth->bind_param( 1 7783, SQL_INTEGER );
717 This would return:
719 [ SQL_INTEGER, undef ]
721 Passing parameters via execute() will always populate the array
723 with "undef", so for:
724 $sth->execute( 7783, 'yes' );
726 This would return:
728 [ undef, undef ]
730 "mock_execution_history"
732 Returns an arrayref where each entry contains the details for an
733 execution of the prepared statement. e.g. after:
734 my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = ? AND is_active = ?' );
736 $sth->bind_param( 2, 'yes' );
737 $sth->bind_param( 1 7783, SQL_INTEGER );
738 $sth->execute();
739 $sth->execute( 1023, 'no' );
741 Then "$sth->{mock_execution_history}" would be:
743 [
745 {
746 params => [ 7783, 'yes' ],
747 attrs => [ SQL_INTEGER, undef ],
748 }, {
749 params => [ 1023, 'no' ],
750 attrs => [ undef, undef ],
751 }
752 ]
753 "mock_records"
755 An arrayref of arrayrefs representing the records the mock
756 statement was stocked with.
757 "mock_num_records"
759 Number of records the mock statement was stocked with; if never
760 stocked it is still 0. (Some weirdos might expect undef...)
761 "mock_num_rows"
763 This returns the same value as mock_num_records. And is what is
764 returned by the "rows" method of the statement handle.
765 "mock_current_record_num"
767 Current record the statement is on; returns 0 in the instances when
768 you have not yet called execute() and if you have not yet called a
769 "fetch" method after the execute.
770 "mock_is_executed"
772 Whether execute() has been called against the statement handle.
773 Returns 'yes' if so, 'no' if not.
774 "mock_is_finished"
776 Whether finish() has been called against the statement handle.
777 Returns 'yes' if so, 'no' if not.
778 "mock_is_depleted"
780 Returns 'yes' if all the records in the recordset have been
781 returned. If no fetch() was executed against the statement, or If
782 no return data was set this will return 'no'.
783 "mock_my_history"
785 Returns a "DBD::Mock::StatementTrack" object which tracks the
786 actions performed by this statement handle. Most of the actions are
787 separately available from the properties listed above, so you
788 should never need this.
789
791 This module can be used to emulate Apache::DBI style DBI connection
792 pooling. Just as with "Apache::DBI", you must enable "DBD::Mock::Pool"
793 before loading DBI.
794 use DBD::Mock qw(Pool);
796 # followed by ...
797 use DBI;
798 While this may not seem to make a lot of sense in a single-process
800 testing scenario, it can be useful when testing code which assumes a
801 multi-process "Apache::DBI" pooled environment.
802
804 Under the hood this module does most of the work with a
805 "DBD::Mock::StatementTrack" object. This is most useful when you are
806 reviewing multiple statements at a time, otherwise you might want to
807 use the "mock_*" statement handle attributes instead.
808 new( %params )
810 Takes the following parameters:
811 • "return_data": Arrayref of return data records
813 • "fields": Arrayref of field names
815 • "bound_params": Arrayref of bound parameters
817 • "bound_param_attrs": Arrayref of bound parameter attributes
819 "statement" (Statement attribute "mock_statement")
821 Gets/sets the SQL statement used.
822 "fields" (Statement attribute "mock_fields")
824 Gets/sets the fields to use for this statement.
825 "bound_params" (Statement attribute "mock_params")
827 Gets/set the bound parameters to use for this statement.
828 "return_data" (Statement attribute "mock_records")
830 Gets/sets the data to return when asked (that is, when someone
831 calls "fetch" on the statement handle).
832 "current_record_num" (Statement attribute "mock_current_record_num")
834 Gets/sets the current record number.
835 is_active() (Statement attribute "Active")
837 Returns true if the statement is a "SELECT" and has more records to
838 fetch, false otherwise. (This is from the DBI, see the 'Active'
839 docs under 'ATTRIBUTES COMMON TO ALL HANDLES'.)
840 is_executed( $yes_or_no ) (Statement attribute "mock_is_executed")
842 Sets the state of the tracker "executed" flag.
843 is_finished( $yes_or_no ) (Statement attribute "mock_is_finished")
845 If set to "yes" tells the tracker that the statement is finished.
846 This resets the current record number to 0 and clears out the array
847 ref of returned records.
848 is_depleted() (Statement attribute "mock_is_depleted")
850 Returns true if the current record number is greater than the
851 number of records set to return.
852 "num_fields"
854 Returns the number of fields set in the "fields" parameter.
855 "num_rows"
857 Returns the number of records in the current result set.
858 "num_params"
860 Returns the number of parameters set in the "bound_params"
861 parameter.
862 "bound_param( $param_num, $value )"
864 Sets bound parameter $param_num to $value. Returns the arrayref of
865 currently-set bound parameters. This corresponds to the
866 "bind_param" statement handle call.
867 bound_param_trailing( @params )
869 Pushes @params onto the list of already-set bound parameters.
870 mark_executed()
872 Tells the tracker that the statement has been executed and resets
873 the current record number to 0.
874 next_record()
876 If the statement has been depleted (all records returned) returns
877 "undef"; otherwise it gets the current record for returning,
878 increments the current record number and returns the current
879 record.
880 to_string()
882 Tries to give a decent depiction of the object state for use in
883 debugging.
884
886 This object can be used to iterate through the current set of
887 "DBD::Mock::StatementTrack" objects in the history by fetching the
888 "mock_all_history_iterator" attribute from a database handle. This
889 object is very simple and is meant to be a convenience to make writing
890 long test script easier. Aside from the constructor ("new") this object
891 has the following methods.
892 next()
894 Calling "next" will return the next "DBD::Mock::StatementTrack"
895 object in the history. If there are no more
896 "DBD::Mock::StatementTrack" objects available, then this method
897 will return false.
898 reset()
900 This will reset the internal pointer to the beginning of the
901 statement history.
902
904 The "DBD::Mock::Session" object is an alternate means of specifying the
905 SQL statements and result sets for "DBD::Mock". The idea is that you
906 can specify a complete 'session' of usage, which will be verified
907 through "DBD::Mock". Here is an example:
908 my $session = DBD::Mock::Session->new('my_session' => (
910 {
911 statement => "SELECT foo FROM bar", # as a string
912 results => [[ 'foo' ], [ 'baz' ]]
913 },
914 {
915 statement => qr/UPDATE bar SET foo \= \'bar\'/, # as a reg-exp
916 results => [[]]
917 },
918 {
919 statement => sub { # as a CODE ref
920 my ($SQL, $state) = @_;
921 return $SQL eq "SELECT foo FROM bar";
922 },
923 results => [[ 'foo' ], [ 'bar' ]]
924 },
925 {
926 # with bound parameters
927 statement => "SELECT foo FROM bar WHERE baz = ? AND borg = ?",
928 # check exact bound param value,
929 # then check it against regexp
930 bound_params => [ 10, qr/\d+/ ],
931 results => [[ 'foo' ], [ 'baz' ]]
932 }
933 ));
934 As you can see, a session is essentially made up a list of HASH
936 references we call 'states'. Each state has a "statement" and a set of
937 "results". If "DBD::Mock" finds a session in the "mock_session"
938 attribute, then it will pass the current $dbh and SQL statement to that
939 "DBD::Mock::Session". The SQL statement will be checked against the
940 "statement" field in the current state. If it passes, then the
941 "results" of the current state will get fed to "DBD::Mock" through the
942 "mock_add_resultset" attribute. We then advance to the next state in
943 the session, and wait for the next call through "DBD::Mock". If at any
944 time the SQL statement does not match the current state's "statement",
945 or the session runs out of available states, an error will be raised
946 (and propagated through the normal DBI error handling based on your
947 values for "RaiseError" and "PrintError").
948 As can be seen in the session element, bound parameters can also be
950 supplied and tested. In this statement, the SQL is compared, then when
951 the statement is executed, the bound parameters are also checked. The
952 bound parameters must match in both number of parameters and the
953 parameters themselves, or an error will be raised.
954 As can also be seen in the example above, "statement" fields can come
956 in many forms. The simplest is a string, which will be compared using
957 "eq" against the currently running statement. The next is a reg-exp
958 reference, this too will get compared against the currently running
959 statement. The last option is a CODE ref, this is sort of a catch-all
960 to allow for a wide range of SQL comparison approaches (including using
961 modules like SQL::Statement or SQL::Parser for detailed functional
962 comparisons). The first argument to the CODE ref will be the currently
963 active SQL statement to compare against, the second argument is a
964 reference to the current state HASH (in case you need to alter the
965 results, or store extra information). The CODE is evaluated in boolean
966 context and throws and exception if it is false.
967 "new ($session_name, @session_states)"
969 A $session_name can be optionally be specified, along with at least
970 one @session_states. If you don't specify a $session_name, then a
971 default one will be created for you. The @session_states must all
972 be HASH references as well, if this conditions fail, an exception
973 will be thrown.
974 "verify_statement ($dbh, $SQL)"
976 This will check the $SQL against the current state's "statement"
977 value, and if it passes will add the current state's "results" to
978 the $dbh. If for some reason the "statement" value is bad, not of
979 the prescribed type, an exception is thrown. See above for more
980 details.
981 "verify_bound_params ($dbh, $params)"
983 If the "bound_params" slot is available in the current state, this
984 will check the $params against the current state's "bound_params"
985 value. Both number of parameters and the parameters themselves must
986 match, or an error will be raised.
987 "reset"
989 Calling this method will reset the state of the session object so
990 that it can be reused.
991
993 All functionality listed here is highly experimental and should be used
994 with great caution (if at all).
995 Connection Callbacks
997 This feature allows you to define callbacks that get executed when
998 "DBI->connect" is called.
999 To set a series of callbacks you use the
1001 "DBD::Mock::dr::set_connect_callbacks" function
1002 use DBD::Mock::dr;
1004 DBD::Mock::dr::set_connect_callbacks( sub {
1006 my ( $dbh, $dsn, $user, $password, $attributes ) = @_;
1007 $dbh->{mock_add_resultset} = {
1009 sql => 'SELECT foo FROM bar',
1010 results => [[ 'foo' ], [ 10 ]]
1011 };
1012 } );
1013 To set more than one callback to you can simply add extra callbacks
1015 to your call to "DBD::Mock::dr::set_connect_callbacks"
1016 DBD::Mock::dr::set_connect_callbacks(
1018 sub {
1019 my ( $dbh, $dsn, $user, $password, $attributes ) = @_;
1020 $dbh->{mock_add_resultset} = {
1022 sql => 'SELECT foo FROM bar',
1023 results => [[ 'foo' ], [ 10 ]]
1024 };
1025 },
1026 sub {
1028 my ( $dbh, $dsn, $user, $password, $attributes ) = @_;
1029 $dbh->{mock_add_resultset} = {
1031 sql => 'SELECT foo FROM bar',
1032 results => [[ 'foo' ], [ 10 ]]
1033 };
1034 }
1035 );
1036 Or you can extend the existing set of callbacks with the
1038 "DBD::Mock::dr::add_connect_callbacks" function
1039 DBD::Mock::dr::add_connect_callbacks( sub {
1041 ( my $dbh, $dsn, $user, $password, $attributes ) = @_;
1042 $dbh->{mock_add_resultset} = {
1044 sql => 'SELECT bar FROM foo',
1045 results => [[ 'bar' ], [ 50 ]]
1046 };
1047 } );
1048 table_info
1050 This feature adds support for DBI's "table_info" method ( Note this
1051 functionality is unstable when used with DBI version 1.634 and
1052 below).
1053 To mock the table info for a search of the "testSchema" database
1055 schema you would use the following:
1056 $dbh->{mock_add_table_info} = {
1058 cataloge => undef,
1059 schema => 'testSchema',
1060 table => undef,
1061 type => undef,
1062 table_info => [
1063 [ 'TABLE_CAT', 'TABLE_SCHEM', 'TABLE_NAME', 'TABLE_TYPE', 'REMARKS' ],
1064 [ undef, 'testSchema', 'foo', 'TABLE', undef ],
1065 [ undef, 'testSchema', 'bar', 'VIEW', undef ],
1066 ],
1067 };
1068 The "cataloge", "schema", "table" and "type" parameters need to
1070 explicitly match what you expect table_info to be called with
1071 (note: "table_info" treats "undef" and '' the same).
1072 Similar to the "mock_results_sets", the "table_info" parameter's
1074 first entry is an arrayref of column names, and the rest are the
1075 values of the rows returned (one arrayref per row).
1076 If you need to cover listing schemas then you'd use:
1078 $dbh->{mock_add_table_info} = {
1080 schema => '%',
1081 table_info => [
1082 [ 'TABLE_CAT', 'TABLE_SCHEM', 'TABLE_NAME', 'TABLE_TYPE', 'REMARKS' ],
1083 [ undef, 'testSchema', undef, undef, undef ],
1084 [ undef, 'testSchema_2', undef, undef, undef ],
1085 ],
1086 }
1087 To clear the current mocked table info set the database handle's
1089 "mock_clear_table_info" attribute to 1
1090 $dbh->{mock_clear_table_info} = 1;
1092 Result Set Callbacks
1094 If you need your result sets to be more dynamic (e.g. if they need
1095 to return different results based upon bound parameters) then you
1096 can use a callback.
1097 $dbh->{mock_add_resultset} = {
1099 sql => 'SELECT a FROM b WHERE c = ?',
1100 callback => sub {
1101 my @bound_params = @_;
1102 my %result = (
1104 fields => [ "a" ],
1105 rows => [[ 1] ]
1106 );
1107 if ($bound_params[0] == 1) {
1109 $result{rows} = [ [32] ];
1110 } elsif ($bound_params[0] == 2) {
1111 $result{rows} = [ [43] ];
1112 }
1113 return %result;
1115 },
1116 };
1117 my $sth = $dbh->prepare('SELECT a FROM b WHERE c = ?');
1119 my $rows = $sth->execute(1);
1121 my ($result) = $sth->fetchrow_array(); # $result will be 32
1122 $rows = $sth->execute(2);
1124 ($result) = $sth->fetchrow_array(); # $result this time will be 43
1125 $rows = $sth->execute(33); # $results this time will be 1
1127 ($result) = $sth->fetchrow_array();
1129 The callback needs to return a hash with a "rows" key that is an
1131 array ref of arrays containing the values to return as the answer
1132 to the query. In addition a "fields" key can also be returned with
1133 an array ref of field names. If a "fields" key isn't present in the
1134 returned the hash then the fields will be taken from the
1135 "mock_add_resultset"'s "results" parameter.
1136 $dbh->{mock_add_resultset} = {
1138 sql => 'SELECT x FROM y WHERE z = ?',
1139 results => [ ["x"] ],
1140 callback => sub {
1141 my @bound_params = @_;
1142 my %result = ( rows => [[ 1] ] );
1144 if ($bound_params[0] == 1) {
1146 $result{rows} = [ [32] ];
1147 } elsif ($bound_params[0] == 2) {
1148 $result{rows} = [ [43] ];
1149 }
1150 return %result;
1152 },
1153 };
1154 my $sth = $dbh->prepare('SELECT x FROM y WHERE z = ?');
1156 my $rows = $sth->execute(1);
1158 my ($result) = $sth->fetchrow_array(); # $result will be 32
1159 $rows = $sth->execute(2);
1161 ($result) = $sth->fetchrow_array(); # $result will be 43
1162 $rows = $sth->execute(33);
1164 ($result) = $sth->fetchrow_array(); # $result will be 1
1165 By default result sets which only define their field names in their
1167 callback return values will have a "NUM_OF_FIELDS" property of 0
1168 until after the statement has actually been executed. This is to
1169 make sure that "DBD::Mock" stays compatible with previous versions.
1170 If you need the "NUM_OF_FIELDS" property to be undef in this
1171 situation then set the $DBD::Mock::DefaultFieldsToUndef flag to 1.
1172 If you're mocking an INSERT statement with a callback and you want
1174 to explicitly set the database's "last_insert_id" value then you
1175 can use the "last_insert_id" key in the result set. If you don't
1176 specify a "last_insert_id" then the standard "DBD::Mock" logic for
1177 generating an value for the last inserted item will be followed.
1178 This will allow you to mock MySQL/MariaDB INSERT queries that use
1179 "ON DUPLICATE KEY UPDATE" logic to set the "last_insert_id".
1180 $dbh->{mock_add_resultset} = {
1182 sql => 'INSERT INTO y ( x ) VALUES ( ? ) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID( id )',
1183 callback => sub {
1184 my @bound_params = @_;
1185 my %result = (
1187 fields => [],
1188 rows => [],
1189 last_insert_id => 99,
1190 );
1191 return %result;
1193 },
1194 };
1195 Result Set Custom Attributes
1197 If you're mocking a database driver that has it's own custom
1198 attributes attached to its statement handles then you can use the
1199 result sets "prepare_attributes" and "execute_attributes" options.
1200 The "prepare_attributes" option takes a hashref that maps statement
1202 handle attribute names to their values. The attributes are set at
1203 the point that the statement is prepared.
1204 $dbh->{mock_add_resultset} = {
1206 sql => 'SELECT foo FROM bar',
1207 prepare_attributes => {
1208 sqlite_unprepared_statements => ' ',
1209 },
1210 results => [[ 'foo' ], [ 10 ]]
1211 };
1212 The "execute_attributes" option also takes a hashref that maps
1214 statement handle attribute names to their values, however these
1215 will only be set when the statement is executed.
1216 $dbh->{mock_add_resultset} = {
1218 sql => 'SELECT foo FROM bar',
1219 execute_attributes => {
1220 syb_result_type => 1,
1221 },
1222 results => [[ 'foo' ], [ 10 ]]
1223 };
1224 If an attribute is also present in the "prepare_attributes" option
1226 then the "prepare_attributes" version will take precedence up to
1227 the point the statement handle is executed, at which point the
1228 "execute_attributes" version will take precedence.
1229 It is also possible to set "execute_attributes" from a result set's
1231 callback by returning them under the "execute_attributes" key in
1232 your callback's response.
1233 $dbh->{mock_add_resultset} = {
1235 sql => 'SELECT baz FROM qux',
1236 callback => sub {
1237 my @bound_params = @_;
1238 my %result = (
1240 fields => [ 'baz'],
1241 rows => [],
1242 execute_attributes => {
1243 foo => 'bar'
1244 },
1245 );
1246 return %result;
1248 }
1249 };
1250 If a result set has an "execute_attributes" option and a callback
1252 that also returns an "execute_attributes" key then the callback's
1253 "execute_attributes" value will take precedence.
1254
1256 Odd $dbh attribute behavior
1257 When writing the test suite I encountered some odd behavior with
1258 some $dbh attributes. I still need to get deeper into how DBD's
1259 work to understand what it is that is actually doing wrong.
1260
1262 Make DBD specific handlers
1263 Each DBD has its own quirks and issues, it would be nice to be able
1264 to handle those issues with "DBD::Mock" in some way. I have an
1265 number of ideas already, but little time to sit down and really
1266 flesh them out. If you have any suggestions or thoughts, feel free
1267 to email me with them.
1268 Enhance the "DBD::Mock::StatementTrack" object
1270 I would like to have the "DBD::Mock::StatementTrack" object handle
1271 more of the "mock_*" attributes. This would encapsulate much of the
1272 "mock_*" behavior in one place, which would be a good thing.
1273
1275 DBI
1276 DBD::NullP, which provided a good starting point
1278 Test::MockObject, which provided the approach
1280 Test::MockObject article -
1282 <http://www.perl.com/pub/a/2002/07/10/tmo.html>
1283 Perl Code Kata: Testing Databases -
1285 <http://www.perl.com/pub/a/2005/02/10/database_kata.html>
1286
1288 Thanks to Ryan Gerry for his patch in RT #26604.
1289 Thanks to Marc Beyer for his patch in RT #16951.
1290 Thanks to Justin DeVuyst for the mock_connect_fail idea.
1291 Thanks to Thilo Planz for the code for "bind_param_inout".
1292 Thanks to Shlomi Fish for help tracking down RT Bug #11515.
1293 Thanks to Collin Winter for the patch to fix the begin_work(), commit()
1294 and rollback() methods.
1295 Thanks to Andrew McHarg <amcharg@acm.org> for fetchall_hashref(),
1296 fetchrow_hashref() and selectcol_arrayref() methods and tests.
1297 Thanks to Andrew W. Gibbs for the "mock_last_insert_ids" patch and
1298 test.
1299 Thanks to Chas Owens for patch and test for the "mock_can_prepare",
1300 "mock_can_execute", and "mock_can_fetch" features.
1301 Thanks to Tomas Zemresfor the unit test in RT #71438.
1302 Thanks to Bernhard Graf for multiple patches fixing a range of issues
1303 and adding a new One Shot Failure feature to "mock_add_resultset".
1304 Thanks to Erik Huelsmann for testing the new result set custom
1305 attributes feature.
1306
1308 Copyright (C) 2004 Chris Winters <chris@cwinters.com>
1309 Copyright (C) 2004-2007 Stevan Little <stevan@iinteractive.com>
1311 Copyright (C) 2007 Rob Kinyon <rob.kinyon@gmail.com>
1313 Copyright (C) 2011 Mariano Wahlmann <dichoso _at_ gmail.com>
1315 Copyright (C) 2019 Jason Cooper <JLCOOPER@cpan.org>
1317 This library is free software; you can redistribute it and/or modify it
1319 under the same terms as Perl itself.
1320
1322 Chris Winters <chris@cwinters.com>
1323 Stevan Little <stevan@iinteractive.com>
1325 Rob Kinyon <rob.kinyon@gmail.com>
1327 Mariano Wahlmann <dichoso _at_ gmail.com>
1329 Jason Cooper <JLCOOPER@cpan.org>
1331perl v5.38.0 2023-07-20 DBD::Mock(3pm)