1DBD::Mock(3pm)        User Contributed Perl Documentation       DBD::Mock(3pm)
2
3
4

NAME

6       DBD::Mock - Mock database driver for testing
7

SYNOPSIS

9           use DBI;
10
11           # 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
15           # 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
19           # 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

DESCRIPTION

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
36       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
44       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
50   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
59           [DBI]
60           dsn      = DBI:Pg:dbname=myapp
61           user     = foo
62           password = bar
63
64       And this file is read in at process startup and the handle stored for
65       other procedures to use:
66
67           package ObjectDirectory;
68
69           my ( $DBH );
70
71           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
80           sub get_database_handle {
81               return $DBH;
82           }
83
84       A procedure might use it like this (ignoring any error handling for the
85       moment):
86
87           package My::UserActions;
88
89           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
103       So for the purposes of our tests we just want to ensure that:
104
105       1. The right SQL is being executed
106       2. The right parameters are bound
107
108       Assume whether the SQL actually works or not is irrelevant for this
109       test :-)
110
111       To do that our test might look like:
112
113           my $config = ObjectDirectory->read_configuration( ... );
114           $config->{DBI}{dsn} = 'DBI:Mock:';
115           ObjectDirectory->run_at_startup( $config );
116
117           my $login_name = 'foobar';
118           my $user = My::UserActions->fetch_user( $login_name );
119
120           # 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
125           # Ask the database handle for the history
126           # of all statements executed against it
127           my $history = $dbh->{mock_all_history};
128
129           # Now query that history record to
130           # see if our expectations match reality
131           is(scalar(@{$history}), 1, 'Correct number of statements executed' ;
132
133           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
138           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
142           # Reset the handle for future operations
143           $dbh->{mock_clear_history} = 1;
144
145       The list of properties and what they return is listed below. But in an
146       overall view:
147
148       •   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
155       •   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
161   A Word of Warning
162       This may be an incredibly naive implementation of a DBD. But it works
163       for me...
164

DBD::Mock

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
170   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
177               # install the DBD::Mock driver
178               my $drh = DBI->install_driver('Mock');
179
180               $drh->{mock_connect_fail} = 1;
181
182               # this connection will fail
183               my $dbh = DBI->connect('dbi:Mock:', '', '') || die "Cannot connect";
184
185               # this connection will throw an exception
186               my $dbh = DBI->connect('dbi:Mock:', '', '', { RaiseError => 1 });
187
188               $drh->{mock_connect_fail} = 0;
189
190               # this will work now ...
191               my $dbh = DBI->connect(...);
192
193           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
199       "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
204       "mock_add_data_sources"
205           This takes a string and adds it to the "mock_data_sources"
206           attribute.
207
208   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
216       "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
223       "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
228               my $dbh = get_handle( ... );
229               my $sth = $dbh->prepare( ... );
230               $dbh->{mock_clear_history} = 1;
231               $sth->execute( 'Foo' );
232
233           You will have no way to learn from the database handle that the
234           statement parameter 'Foo' was bound.
235
236           This is useful mainly to ensure you can isolate the statement
237           histories from each other. A typical sequence will look like:
238
239               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
248       "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
255               # turn the database off
256               $dbh->{mock_can_connect} = 0;
257
258               # turn it back on again
259               $dbh->{mock_can_connect} = 1;
260
261           The statement handle checks this value as well, so something like
262           this will fail in the expected way:
263
264               $dbh = DBI->connect( 'DBI:Mock:', '', '' );
265               $dbh->{mock_can_connect} = 0;
266
267               # blows up!
268               my $sth = eval { $dbh->prepare( 'SELECT foo FROM bar' ) });
269               if ( $@ ) {
270                   # Here, $DBI::errstr = 'No connection present'
271               }
272
273           Turning off the database after a statement prepare will fail on the
274           statement "execute()", which is hopefully what you would expect:
275
276               $dbh = DBI->connect( 'DBI:Mock:', '', '' );
277
278               # ok!
279               my $sth = eval { $dbh->prepare( 'SELECT foo FROM bar' ) });
280               $dbh->{mock_can_connect} = 0;
281
282               # blows up!
283               $sth->execute;
284
285           Similarly:
286
287               $dbh = DBI->connect( 'DBI:Mock:', '', '' );
288
289               # ok!
290               my $sth = eval { $dbh->prepare( 'SELECT foo FROM bar' ) });
291
292               # ok!
293               $sth->execute;
294
295               $dbh->{mock_can_connect} = 0;
296
297               # blows up!
298               my $row = $sth->fetchrow_arrayref;
299
300           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
305       "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
313           Here is a sample usage, partially from the test suite:
314
315               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
327               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
336               # 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
343               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
350               my $sth_generic = $dbh->prepare( 'SELECT foo, bar FROM baz' );
351               $sth_generic->execute;
352
353               # this will fetch rows from the second resultset...
354               my $row = $sth->fetchrow_arrayref;
355
356           It is possible to assign a hashref where the resultset must be
357           given as value for the "results" key:
358
359               $dbh->{mock_add_resultset} = {
360                   results => [
361                       [ 'foo', 'bar' ],
362                       [ 'this_one', 'that_one' ],
363                       [ 'this_two', 'that_two' ],
364                   ],
365               };
366
367           The reason for the hashref form is that you can add options as
368           described in the following.
369
370           You can associate a resultset with a particular SQL statement
371           instead of adding them in the order they will be fetched:
372
373               $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
382           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
390           If the "sql" parameter is a regular expression reference then the
391           results will be returned for any SQL statements that matches it:
392
393               $dbh->{mock_add_resultset} = {
394                   sql     => qr/^SELECT foo FROM/i,
395                   results => [
396                       [ 'foo' ],
397                       [ 'this_one' ],
398                   ],
399               };
400
401           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
406               # Set up our first regex matching result set
407               $dbh->{mock_add_resultset} = {
408                   sql => qr/^SELECT foo/,
409                   results => [ [ 'foo' ], [ 200 ] ],
410               };
411
412               # 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
420               # 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
427               # 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
431               my ($result) = $sth->fetchrow_array();
432               is( $result, 200 );
433
434               # This quere will be served by the statically defined result set
435               $sth = $dbh->prepare('SELECT foo FROM bar');
436               $sth->execute();
437
438               my ($result2) = $sth->fetchrow_array();
439               is( $result2, 50 );
440
441           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
447               $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
453               # or ...
454
455               $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
461           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
465           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
469               $dbh->{mock_add_resultset} = {
470                   sql => 'SELECT foo FROM bar',
471                   results => DBD::Mock->NULL_RESULTSET,
472                   failure => [ 5, 'Ooops!' ],
473               };
474
475           Without the "sql" attribute the next statement will fail in any
476           case:
477
478               $dbh->{mock_add_resultset} = {
479                   results => DBD::Mock->NULL_RESULTSET,
480                   failure => [ 5, 'Ooops!' ],
481               };
482
483       "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
488       "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
494       "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
499               $dbh->{mock_start_insert_id} = 10;
500
501               my $sth = $dbh->prepare('INSERT INTO Foo (foo, bar) VALUES(?, ?)');
502
503               $sth->execute(1, 2);
504               # $dbh->{mock_last_insert_id} == 10
505
506               $sth->execute(3, 4);
507               # $dbh->{mock_last_insert_id} == 11
508
509           For more examples, please refer to the test file
510           t/025_mock_last_insert_id.t.
511
512           To access "last_insert_id" using driver specific attributes like
513           "mysql_insertid" and "mariadb_insertid" then you can use "Attribute
514           Aliasing".
515
516       "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
521           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
527               $dbh->{mock_start_insert_id} = [ 'Foo', 10 ];
528               $dbh->{mock_start_insert_id} = [ 'Baz', 20 ];
529
530               my $sth1 = $dbh->prepare('INSERT INTO Foo (foo, bar) VALUES(?, ?)');
531
532               my $sth2 = $dbh->prepare('INSERT INTO Baz (baz, buz) VALUES(?, ?)');
533
534               $sth1->execute(1, 2);
535               # $dbh->{mock_last_insert_id} == 10
536
537               $sth2->execute(3, 4);
538               # $dbh->{mock_last_insert_id} == 20
539
540           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
545               $dbh->{mock_start_insert_id} = [ q{"Foo"}, 10 ];
546
547       "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
553           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
558           The syntax is simple:
559
560               $dbh->{mock_add_parser} = sub {
561                   my ( $sql ) = @_;
562                   unless ( $sql =~ /some regex/ ) {
563                       die "does not contain secret fieldname";
564                   }
565               };
566
567           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
570               $dbh->{mock_add_parser} = \&parse_update_sql;
571               $dbh->{mock_add-parser} = \&parse_insert_sql;
572
573           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
577           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
582               my $parser = SQL::Parser->new( 'mysql', { RaiseError => 1 } );
583               $dbh->{mock_add_parser} = $parser;
584
585       "mock_data_sources" & "mock_add_data_sources"
586           These properties will dispatch to the Driver's properties of the
587           same name.
588
589   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
598       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
601           # turn it on
602           $DBD::Mock::AttributeAliasing++;
603
604           # turn it off
605           $DBD::Mock::AttributeAliasing = 0;
606
607       Once this is turned on, you will need to choose a database specific
608       attribute aliasing table like so:
609
610           DBI->connect('dbi:Mock:MySQL', '', '');
611
612       Or, by using the database name if using driver DSNs:
613
614           DBI->connect('dbi:Mock:host=localhost;port=3306;database=MySQL', '', '');
615
616       The "MySQL" in the DSN will be picked up and the MySQL specific
617       attribute aliasing will be used.
618
619       Right now there is only minimal support for MySQL and MariaDB:
620
621       MySQL
622           Currently the "mysql_insertid" attribute for $dbh and $sth are
623           aliased to the $dbh attribute "mock_last_insert_id".
624
625       MariaDB
626           Currently the "mariadb_insertid" attribute for $dbh and $sth are
627           aliased to the $dbh attribute "mock_last_insert_id".
628
629       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
633   Database Driver Methods
634       "last_insert_id"
635           This returns the value of "mock_last_insert_id".
636
637       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
644       "begin_work"
645           This will create a statement with SQL of "BEGIN WORK" and no
646           parameters.
647
648       "commit"
649           This will create a statement with SQL of "COMMIT" and no
650           parameters.
651
652       "rollback"
653           This will create a statement with SQL of "ROLLBACK" and no
654           parameters.
655
656   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
661       "mock_statement"
662           The SQL statement this statement handle was "prepare"d with. So if
663           the handle was created with:
664
665               my $sth = $dbh->prepare( 'SELECT * FROM foo' );
666
667           This would return:
668
669               SELECT * FROM foo
670
671           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
676       "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
681           Note that this returns the same thing as the normal statement
682           property "FIELD".
683
684       "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
689               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
693           This would return:
694
695               [ 7738, 'yes' ]
696
697           The same result will occur if you pass the parameters via
698           "execute()" instead:
699
700               my $sth = $dbh->prepare( 'SELECT * FROM foo WHERE id = ? AND is_active = ?' );
701               $sth->execute( 7783, 'yes' );
702
703           The same using named parameters
704
705               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
709       "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
715               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
719           This would return:
720
721               [ SQL_INTEGER, undef ]
722
723           Passing parameters via "execute()" will always populate the array
724           with "undef", so for:
725
726               $sth->execute( 7783, 'yes' );
727
728           This would return:
729
730               [ undef, undef ]
731
732       "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
736               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
741               $sth->execute( 1023, 'no' );
742
743           Then "$sth->{mock_execution_history}" would be:
744
745               [
746                   {
747                       params => [ 7783, 'yes' ],
748                       attrs  => [ SQL_INTEGER, undef ],
749                   }, {
750                       params => [ 1023, 'no' ],
751                       attrs  => [ undef, undef ],
752                   }
753               ]
754
755       "mock_records"
756           An arrayref of arrayrefs representing the records the mock
757           statement was stocked with.
758
759       "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
763       "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
767       "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
772       "mock_is_executed"
773           Whether "execute()" has been called against the statement handle.
774           Returns 'yes' if so, 'no' if not.
775
776       "mock_is_finished"
777           Whether "finish()" has been called against the statement handle.
778           Returns 'yes' if so, 'no' if not.
779
780       "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
785       "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

DBD::Mock::Pool

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
796           use DBD::Mock qw(Pool);
797           # followed by ...
798           use DBI;
799
800       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

DBD::Mock::StatementTrack

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
810       "new( %params )"
811           Takes the following parameters:
812
813"return_data": Arrayref of return data records
814
815"fields": Arrayref of field names
816
817"bound_params": Arrayref of bound parameters
818
819"bound_param_attrs": Arrayref of bound parameter attributes
820
821       "statement" (Statement attribute "mock_statement")
822           Gets/sets the SQL statement used.
823
824       "fields"  (Statement attribute "mock_fields")
825           Gets/sets the fields to use for this statement.
826
827       "bound_params"  (Statement attribute "mock_params")
828           Gets/set the bound parameters to use for this statement.
829
830       "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
834       "current_record_num" (Statement attribute "mock_current_record_num")
835           Gets/sets the current record number.
836
837       "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
842       "is_executed( $yes_or_no )" (Statement attribute "mock_is_executed")
843           Sets the state of the tracker "executed" flag.
844
845       "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
850       "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
854       "num_fields"
855           Returns the number of fields set in the "fields" parameter.
856
857       "num_rows"
858           Returns the number of records in the current result set.
859
860       "num_params"
861           Returns the number of parameters set in the "bound_params"
862           parameter.
863
864       "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
869       "bound_param_trailing( @params )"
870           Pushes @params onto the list of already-set bound parameters.
871
872       "mark_executed()"
873           Tells the tracker that the statement has been executed and resets
874           the current record number to 0.
875
876       "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
882       "to_string()"
883           Tries to give a decent depiction of the object state for use in
884           debugging.
885

DBD::Mock::StatementTrack::Iterator

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
894       "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
900       "reset()"
901           This will reset the internal pointer to the beginning of the
902           statement history.
903

DBD::Mock::Session

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
910           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
936       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
950       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
956       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
969       "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
976       "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
983       "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
989       "reset"
990           Calling this method will reset the state of the session object so
991           that it can be reused.
992

EXPERIMENTAL FUNCTIONALITY

994       All functionality listed here is highly experimental and should be used
995       with great caution (if at all).
996
997       Connection Callbacks
998           This feature allows you to define callbacks that get executed when
999           "DBI->connect" is called.
1000
1001           To set a series of callbacks you use the
1002           "DBD::Mock::dr::set_connect_callbacks" function
1003
1004               use DBD::Mock::dr;
1005
1006               DBD::Mock::dr::set_connect_callbacks( sub {
1007                   my ( $dbh, $dsn, $user, $password, $attributes ) = @_;
1008
1009                   $dbh->{mock_add_resultset} = {
1010                       sql => 'SELECT foo FROM bar',
1011                       results => [[ 'foo' ], [ 10 ]]
1012                   };
1013               } );
1014
1015           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
1018               DBD::Mock::dr::set_connect_callbacks(
1019                   sub {
1020                       my ( $dbh, $dsn, $user, $password, $attributes ) = @_;
1021
1022                       $dbh->{mock_add_resultset} = {
1023                           sql => 'SELECT foo FROM bar',
1024                           results => [[ 'foo' ], [ 10 ]]
1025                       };
1026                   },
1027
1028                   sub {
1029                       my ( $dbh, $dsn, $user, $password, $attributes ) = @_;
1030
1031                       $dbh->{mock_add_resultset} = {
1032                           sql => 'SELECT foo FROM bar',
1033                           results => [[ 'foo' ], [ 10 ]]
1034                       };
1035                   }
1036               );
1037
1038           Or you can extend the existing set of callbacks with the
1039           "DBD::Mock::dr::add_connect_callbacks" function
1040
1041               DBD::Mock::dr::add_connect_callbacks( sub {
1042                   ( my $dbh, $dsn, $user, $password, $attributes ) = @_;
1043
1044                   $dbh->{mock_add_resultset} = {
1045                       sql => 'SELECT bar FROM foo',
1046                       results => [[ 'bar' ], [ 50 ]]
1047                   };
1048               } );
1049
1050       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
1055           To mock the table info for a search of the "testSchema" database
1056           schema you would use the following:
1057
1058               $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
1070           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
1074           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
1078           If you need to cover listing schemas then you'd use:
1079
1080               $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
1089           To clear the current mocked table info set the database handle's
1090           "mock_clear_table_info" attribute to 1
1091
1092               $dbh->{mock_clear_table_info} = 1;
1093
1094       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
1099               $dbh->{mock_add_resultset} = {
1100                   sql => 'SELECT a FROM b WHERE c = ?',
1101                   callback => sub {
1102                       my @bound_params = @_;
1103
1104                       my %result = (
1105                           fields => [ "a" ],
1106                           rows => [[ 1] ]
1107                       );
1108
1109                       if ($bound_params[0] == 1) {
1110                           $result{rows} = [ [32] ];
1111                       } elsif ($bound_params[0] == 2) {
1112                           $result{rows} = [ [43] ];
1113                       }
1114
1115                       return %result;
1116                   },
1117               };
1118
1119               my $sth = $dbh->prepare('SELECT a FROM b WHERE c = ?');
1120
1121               my $rows = $sth->execute(1);
1122               my ($result) = $sth->fetchrow_array();  # $result will be 32
1123
1124               $rows = $sth->execute(2);
1125               ($result) = $sth->fetchrow_array();  # $result this time will be 43
1126
1127               $rows = $sth->execute(33); # $results this time will be 1
1128
1129               ($result) = $sth->fetchrow_array();
1130
1131           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
1138               $dbh->{mock_add_resultset} = {
1139                   sql => 'SELECT x FROM y WHERE z = ?',
1140                   results => [ ["x"] ],
1141                   callback => sub {
1142                       my @bound_params = @_;
1143
1144                       my %result = ( rows => [[ 1] ] );
1145
1146                       if ($bound_params[0] == 1) {
1147                           $result{rows} = [ [32] ];
1148                       } elsif ($bound_params[0] == 2) {
1149                           $result{rows} = [ [43] ];
1150                       }
1151
1152                       return %result;
1153                   },
1154               };
1155
1156               my $sth = $dbh->prepare('SELECT x FROM y WHERE z = ?');
1157
1158               my $rows = $sth->execute(1);
1159               my ($result) = $sth->fetchrow_array();  # $result will be 32
1160
1161               $rows = $sth->execute(2);
1162               ($result) = $sth->fetchrow_array();  # $result will be 43
1163
1164               $rows = $sth->execute(33);
1165               ($result) = $sth->fetchrow_array();  # $result will be 1
1166
1167           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
1174           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
1182               $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
1187                       my %result = (
1188                           fields => [],
1189                           rows => [],
1190                           last_insert_id => 99,
1191                       );
1192
1193                       return %result;
1194                   },
1195               };
1196
1197       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
1202           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
1206               $dbh->{mock_add_resultset} = {
1207                   sql => 'SELECT foo FROM bar',
1208                   prepare_attributes => {
1209                       sqlite_unprepared_statements => '   ',
1210                   },
1211                   results => [[ 'foo' ], [ 10 ]]
1212               };
1213
1214           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
1218               $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
1226           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
1231           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
1235               $dbh->{mock_add_resultset} = {
1236                   sql => 'SELECT baz FROM qux',
1237                   callback => sub {
1238                       my @bound_params = @_;
1239
1240                       my %result = (
1241                           fields => [ 'baz'],
1242                           rows => [],
1243                           execute_attributes => {
1244                               foo => 'bar'
1245                           },
1246                       );
1247
1248                       return %result;
1249                   }
1250               };
1251
1252           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

BUGS

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

TO DO

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
1270       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

SEE ALSO

1276       DBI
1277
1278       DBD::NullP, which provided a good starting point
1279
1280       Test::MockObject, which provided the approach
1281
1282       Test::MockObject article -
1283       <http://www.perl.com/pub/a/2002/07/10/tmo.html>
1284
1285       Perl Code Kata: Testing Databases -
1286       <http://www.perl.com/pub/a/2005/02/10/database_kata.html>
1287

ACKNOWLEDGEMENTS

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
1311       Copyright (C) 2004-2007 Stevan Little <stevan@iinteractive.com>
1312
1313       Copyright (C) 2007 Rob Kinyon <rob.kinyon@gmail.com>
1314
1315       Copyright (C) 2011 Mariano Wahlmann <dichoso  _at_ gmail.com>
1316
1317       Copyright (C) 2019 Jason Cooper <JLCOOPER@cpan.org>
1318
1319       This library is free software; you can redistribute it and/or modify it
1320       under the same terms as Perl itself.
1321

AUTHORS

1323       Chris Winters <chris@cwinters.com>
1324
1325       Stevan Little <stevan@iinteractive.com>
1326
1327       Rob Kinyon <rob.kinyon@gmail.com>
1328
1329       Mariano Wahlmann <dichoso _at_ gmail.com>
1330
1331       Jason Cooper <JLCOOPER@cpan.org>
1332
1333
1334
1335perl v5.36.0                      2022-07-22                    DBD::Mock(3pm)
Impressum