Line # Revision Author
1 1 abw #============================================================= -*-perl-*-
2 #
3 # t/dbi.t
4 #
5 # Test script for the DBI plugin.
6 #
7 # This is free software; you can redistribute it and/or modify it
8 # under the same terms as Perl itself.
9 #
10 # $Id: dbi.t,v 2.16 2002/09/04 12:04:01 darren Exp $
11 #
12 #========================================================================
13
14 use strict;
15 use lib qw( ./lib ../lib ./blib/lib ./blib/arch ../blib/lib ../blib/arch );
16 use vars qw( $DEBUG $PK $run $dsn $user $pass );
17 use Template::Test;
18 use Template::Stash;
19 use File::Spec::Functions qw( catfile );
20
21 $^W = 1;
22 $DEBUG = 0;
23 $Template::Test::PRESERVE = 1;
24
25 our $CONFIG_DIR = -d 't' ? 't' : '.';
26 our $CONFIG_FILE = 'dbi_test.cfg';
27 our $CONFIG_PATH = catfile($CONFIG_DIR, $CONFIG_FILE);
28
29
30 #------------------------------------------------------------------------
31 # See if DBI and Tie::DBI are installed
32 #------------------------------------------------------------------------
33
34 eval "use DBI";
35 if ($@) {
36 skip_all("DBI module not installed");
37 }
38
39 eval "use Tie::DBI";
40 my $tiedbi = $@ ? 0 : 1;
41
42 # warn "Tie::DBI not found, skipping those tests\n" unless $tiedbi;
43
44 #------------------------------------------------------------------------
45 # load the configuration file created by Makefile.PL which defines
46 # the $run, $dsn, $user and $pass variables.
47 #------------------------------------------------------------------------
48
49 eval { require "$CONFIG_PATH" };
50 if ($@) {
51 skip_all("cannot load $CONFIG_PATH");
52 }
53 unless ($run) {
54 skip_all('skipping DBI tests at user request');
55 }
56
57 # new feature in DBI plugin v2.30+ is to allow user to drop initial 'dbi:'
58 my $short_dsn = $dsn;
59 $short_dsn =~ s/^dbi://i;
60
61 # another hack: if we want to test Tie::DBI updates then we have to build
62 # database with primary keys to force uniqueness. However, different database
63 # have different ways of defining primary keys, so we're only going to test
64 # it on mysql
65 $PK = ($short_dsn =~ /^mysql/i) ? 'PRIMARY KEY' : '';
66
67 my $attr = {
68 PrintError => 0,
69 ChopBlanks => 1,
70 };
71
72 my $dbh;
73 eval {
74 $dbh = DBI->connect($dsn, $user, $pass, $attr);
75 };
76
77 if ($@ || ! $dbh) {
78 warn <<EOF;
79 DBI connect() failed:
80 $DBI::errstr
81
82 Please ensure that your database server is running and that you specified
83 the correct connection parameters. If necessary, re-run the Makefile.PL
84 and specify new parameters, or answer 'n' when prompted:
85
86 - Do you want to run the DBI tests?
87
88 EOF
89 ntests(1);
90 ok(0);
91 exit(0);
92 };
93
94 init_database($dbh);
95
96 my $vars = {
97 dbh => $dbh,
98 dsn => $dsn,
99 user => $user,
100 pass => $pass,
101 attr => $attr,
102 short => $short_dsn,
103 mysql => $PK ? 1 : 0,
104 tiedbi => $tiedbi,
105 };
106
107 # NOTE: Template::Stash::XS does not handle tied hashes so we must force
108 # the use of the regular Template::Stash
109 my $stash = Template::Stash->new($vars);
110
111 test_expect(\*DATA, { STASH => $stash }, $vars);
112
113 cleanup_database($dbh);
114
115 $dbh->disconnect();
116
117 #------------------------------------------------------------------------
118 # init_database($dsn, $user, $pass)
119 #------------------------------------------------------------------------
120
121 sub init_database {
122 my $dbh = shift;
123
124 # ensure tables don't already exist (in case previous test run failed).
125 sql_query($dbh, 'DROP TABLE usr', 1);
126 sql_query($dbh, 'DROP TABLE grp', 1);
127
128 # create some tables
129 sql_query($dbh, "CREATE TABLE grp (
130 id Char(16) $PK,
131 name Char(32)
132 )");
133
134 sql_query($dbh, "CREATE TABLE usr (
135 id Char(16) $PK,
136 name Char(32),
137 grp Char(16)
138 )");
139
140 # add some records to the 'grp' table
141 sql_query($dbh, "INSERT INTO grp
142 VALUES ('foo', 'The Foo Group')");
143 sql_query($dbh, "INSERT INTO grp
144 VALUES ('bar', 'The Bar Group')");
145 sql_query($dbh, "INSERT INTO grp
146 VALUES ('baz', 'The Baz Group')");
147
148 # add some records to the 'usr' table
149 sql_query($dbh, "INSERT INTO usr
150 VALUES ('abw', 'Andy Wardley', 'foo')");
151 sql_query($dbh, "INSERT INTO usr
152 VALUES ('sam', 'Simon Matthews', 'foo')");
153
154 sql_query($dbh, "INSERT INTO usr
155 VALUES ('hans', 'Hans von Lengerke', 'bar')");
156 sql_query($dbh, "INSERT INTO usr
157 VALUES ('mrp', 'Martin Portman', 'bar')");
158
159 sql_query($dbh, "INSERT INTO usr
160 VALUES ('craig', 'Craig Barratt', 'baz')");
161
162 }
163
164
165 #------------------------------------------------------------------------
166 # sql_query($dbh, $sql, $quiet)
167 #------------------------------------------------------------------------
168
169 sub sql_query {
170 my ($dbh, $sql, $quiet) = @_;
171
172 my $sth = $dbh->prepare($sql)
173 || warn "prepare() failed: $DBI::errstr\n";
174
175 $sth->execute()
176 || $quiet || warn "execute() failed: $DBI::errstr\n";
177
178 $sth->finish();
179 }
180
181
182 #------------------------------------------------------------------------
183 # cleanup_database($dsn, $user, $pass)
184 #------------------------------------------------------------------------
185
186 sub cleanup_database {
187 my $dbh = shift;
188
189 sql_query($dbh, 'DROP TABLE usr');
190 sql_query($dbh, 'DROP TABLE grp');
191 };
192
193
194 #========================================================================
195
196 __END__
197
198 #------------------------------------------------------------------------
199 # test plugin loading with implicit and explicit connect() method
200 #------------------------------------------------------------------------
201
202 -- test --
203 [% USE DBI -%]
204 [% DBI.connect(dsn, user, pass, ChopBlanks => 1) -%]
205 [% FOREACH user = DBI.query("SELECT name FROM usr WHERE id='abw'") -%]
206 * [% user.name %]
207 [% END %]
208
209 -- expect --
210 * Andy Wardley
211
212 -- test --
213 [% USE dbi -%]
214 [% dbi.connect(dsn, user, pass, ChopBlanks => 1) -%]
215 [% FOREACH user = dbi.query("SELECT name FROM usr WHERE id='sam'") -%]
216 * [% user.name %]
217 [% END %]
218
219 -- expect --
220 * Simon Matthews
221
222 -- test --
223 [% USE db = DBI -%]
224 [% db.connect(dsn, user, pass, ChopBlanks => 1) -%]
225 [% FOREACH user = db.query("SELECT name FROM usr WHERE id='hans'") -%]
226 * [% user.name %]
227 [% END %]
228
229 -- expect --
230 * Hans von Lengerke
231
232 -- test --
233 [% USE db = DBI(data_source => dsn,
234 username => user,
235 password => pass,
236 ChopBlanks => 1) -%]
237 [% FOREACH user = db.query("SELECT name FROM usr WHERE id='mrp'") -%]
238 * [% user.name %]
239 [% END %]
240
241 -- expect --
242 * Martin Portman
243
244 -- test --
245 [% USE dbi -%]
246 [% dbi.connect(dsn=dsn, user=user, pass=pass ChopBlanks=1) -%]
247 [% FOREACH user = dbi.query("SELECT name FROM usr WHERE id='abw'") -%]
248 * [% user.name %]
249 [% END %]
250
251 -- expect --
252 * Andy Wardley
253
254
255 -- test --
256 [% USE dbi -%]
257 [% dbi.connect(database=dsn, user=user, pass=pass, ChopBlanks=1) -%]
258 [% FOREACH user = dbi.query("SELECT name FROM usr WHERE id='abw'") -%]
259 * [% user.name %]
260 [% END %]
261
262 -- expect --
263 * Andy Wardley
264
265 -- test --
266 [% USE DBI -%]
267 [% TRY;
268 DBI.query('blah blah');
269 CATCH;
270 error;
271 END
272 %]
273 -- expect --
274 DBI error - data source not defined
275
276
277
278 #------------------------------------------------------------------------
279 # test short form of dsn, e.g. 'mysql:dbase' instead of 'dbi:mysql:dbase'
280 #------------------------------------------------------------------------
281
282 -- test --
283 [% USE dbi -%]
284 [% dbi.connect(short, user, pass ChopBlanks=1) -%]
285 [% FOREACH user = dbi.query("SELECT name FROM usr WHERE id='abw'") -%]
286 * [% user.name %]
287 [% END %]
288
289 -- expect --
290 * Andy Wardley
291
292
293 #------------------------------------------------------------------------
294 # disconnect() and subsequent connect()
295 #------------------------------------------------------------------------
296
297 -- test --
298 [% USE DBI(dsn, user, pass, attr) -%]
299 [% DBI.disconnect -%]
300 [% TRY;
301 DBI.query('blah blah');
302 CATCH;
303 error;
304 END
305 %]
306 -- expect --
307 DBI error - data source not defined
308
309 -- test --
310 [% USE DBI(dsn, user, pass, attr) -%]
311 [% DBI.disconnect -%]
312 [% DBI.connect(dsn, user, pass, attr) -%]
313 [% FOREACH user = DBI.query("SELECT name FROM usr WHERE id='abw'") -%]
314 * [% user.name %]
315 [% END %]
316
317 -- expect --
318 * Andy Wardley
319
320
321 #------------------------------------------------------------------------
322 # validate 'loop' reference to iterator
323 #------------------------------------------------------------------------
324
325 -- test --
326 [% USE dbi(dsn, user, pass, attr) -%]
327 [% FOREACH user = dbi.query('SELECT * FROM usr ORDER BY id') -%]
328 [% loop.number %]: [% user.id %] - [% user.name %]
329 [% END %]
330 -- expect --
331 1: abw - Andy Wardley
332 2: craig - Craig Barratt
333 3: hans - Hans von Lengerke
334 4: mrp - Martin Portman
335 5: sam - Simon Matthews
336
337 -- test --
338 # DBI plugin before TT 2.00 used 'count' instead of 'number'
339 [% USE dbi(dsn, user, pass, attr) -%]
340 [% FOREACH user = dbi.query('SELECT * FROM usr ORDER BY id') -%]
341 [% loop.count %]: [% user.id %] - [% user.name %]
342 [% END %]
343 -- expect --
344 1: abw - Andy Wardley
345 2: craig - Craig Barratt
346 3: hans - Hans von Lengerke
347 4: mrp - Martin Portman
348 5: sam - Simon Matthews
349
350
351 #------------------------------------------------------------------------
352 # test 'loop' reference to iterator in nested queries
353 #------------------------------------------------------------------------
354
355 -- test --
356 [% USE dbi(dsn, user, pass, attr) -%]
357 [% FOREACH group = dbi.query('SELECT * FROM grp
358 ORDER BY id') -%]
359 Group [% loop.number %]: [% group.name %] ([% group.id %])
360 [% FOREACH user = dbi.query("SELECT * FROM usr
361 WHERE grp='$group.id'
362 ORDER BY id") -%]
363 #[% loop.number %]: [% user.name %] ([% user.id %])
364 [% END -%]
365 [% END -%]
366
367 -- expect --
368 Group 1: The Bar Group (bar)
369 #1: Hans von Lengerke (hans)
370 #2: Martin Portman (mrp)
371 Group 2: The Baz Group (baz)
372 #1: Craig Barratt (craig)
373 Group 3: The Foo Group (foo)
374 #1: Andy Wardley (abw)
375 #2: Simon Matthews (sam)
376
377
378 #------------------------------------------------------------------------
379 # test prev and next iterator methods
380 #------------------------------------------------------------------------
381
382 -- test --
383 [% USE dbi(dsn, user, pass, attr) -%]
384 [% FOREACH user = dbi.query('SELECT * FROM usr ORDER BY id') -%]
385 [% loop.prev ? "[$loop.prev.id] " : "[no prev] " -%]
386 [% user.id %] - [% user.name -%]
387 [% loop.next ? " [$loop.next.id]" : " [no next]" %]
388 [% END %]
389 -- expect --
390 [no prev] abw - Andy Wardley [craig]
391 [abw] craig - Craig Barratt [hans]
392 [craig] hans - Hans von Lengerke [mrp]
393 [hans] mrp - Martin Portman [sam]
394 [mrp] sam - Simon Matthews [no next]
395
396
397 #------------------------------------------------------------------------
398 # test do() to perform SQL queries without returning results
399 #------------------------------------------------------------------------
400
401 -- test --
402 [% USE DBI(dsn, user, pass, attr) -%]
403 [% CALL DBI.do("INSERT INTO usr VALUES ('numb', 'Numb Nuts', 'bar')") -%]
404 [% FOREACH user = DBI.query("SELECT * FROM usr
405 WHERE grp = 'bar'
406 ORDER BY id") -%]
407 * [% user.name %] ([% user.id %])
408 [% END %]
409
410 -- expect --
411 * Hans von Lengerke (hans)
412 * Martin Portman (mrp)
413 * Numb Nuts (numb)
414
415 -- test --
416 [% USE dbi(dsn, user, pass, attr) -%]
417 [% IF dbi.do("DELETE FROM usr WHERE id = 'numb'") -%]
418 deleted the user
419 [% ELSE -%]
420 failed to delete the user
421 [% END -%]
422 [% FOREACH user = dbi.query("SELECT * FROM usr
423 WHERE grp = 'bar'
424 ORDER BY id") -%]
425 * [% user.name %] ([% user.id %])
426 [% END %]
427 -- expect --
428 deleted the user
429 * Hans von Lengerke (hans)
430 * Martin Portman (mrp)
431
432
433 #------------------------------------------------------------------------
434 # prepare()
435 #------------------------------------------------------------------------
436
437 -- test --
438 [% USE dbi(dsn=dsn, user=user, pass=pass, ChopBlanks=1) -%]
439 [% user_query = dbi.prepare('SELECT * FROM usr
440 WHERE grp = ?
441 ORDER BY id') -%]
442 Foo:
443 [% users = user_query.execute('foo') -%]
444 [% FOREACH user = users -%]
445 #[% users.index %]: [% user.name %] ([% user.id %])
446 [% END -%]
447
448 Bar:
449 [% users = user_query.execute('bar') -%]
450 [% FOREACH user = users -%]
451 #[% users.index %]: [% user.name %] ([% user.id %])
452 [% END -%]
453 -- expect --
454 Foo:
455 #0: Andy Wardley (abw)
456 #1: Simon Matthews (sam)
457
458 Bar:
459 #0: Hans von Lengerke (hans)
460 #1: Martin Portman (mrp)
461
462
463 -- test --
464 [% USE dbi(dsn, user, pass, attr) -%]
465 [% group_query = dbi.prepare('SELECT * FROM grp
466 ORDER BY id') -%]
467 [% user_query = dbi.prepare('SELECT * FROM usr
468 WHERE grp = ?
469 ORDER BY id') -%]
470 [% groups = group_query.execute -%]
471 [% FOREACH group = groups -%]
472 Group [% groups.count %] : [% group.name %]
473 [% users = user_query.execute(group.id) -%]
474 [% FOREACH user = users -%]
475 User [% users.index %] : [% user.name %] ([% user.id %])
476 [% END -%]
477 [% END %]
478 -- expect --
479 Group 1 : The Bar Group
480 User 0 : Hans von Lengerke (hans)
481 User 1 : Martin Portman (mrp)
482 Group 2 : The Baz Group
483 User 0 : Craig Barratt (craig)
484 Group 3 : The Foo Group
485 User 0 : Andy Wardley (abw)
486 User 1 : Simon Matthews (sam)
487
488
489 -- test --
490 [% USE dbi(dsn, user, pass, attr) -%]
491 [% CALL dbi.prepare('SELECT * FROM usr WHERE id = ?') -%]
492 [% FOREACH uid = [ 'abw', 'sam' ] -%]
493 ===
494 [% FOREACH user = dbi.execute(uid) -%]
495 * [% user.name %] ([% user.id %])
496 [% END -%]
497 ===
498 [% END %]
499
500 -- expect --
501 ===
502 * Andy Wardley (abw)
503 ===
504 ===
505 * Simon Matthews (sam)
506 ===
507
508
509 #------------------------------------------------------------------------
510 # test that dbh can be passed as a named parameter and remains open
511 #------------------------------------------------------------------------
512
513 -- test --
514 [% USE dbi(dbh => dbh) -%]
515 [% FOREACH dbi.query("SELECT * FROM usr WHERE id = 'abw'") -%]
516 * [% name %]
517 [% END -%]
518 [% dbi.connect(dsn, user, pass, ChopBlanks=1) -%]
519 [% FOREACH user = dbi.query("SELECT * FROM usr WHERE id = 'abw'") -%]
520 * [% user.name %]
521 [% END -%]
522 -- expect --
523 * Andy Wardley
524 * Andy Wardley
525
526
527
528 #------------------------------------------------------------------------
529 # test get_all()
530 #------------------------------------------------------------------------
531
532 -- test --
533 [% USE dbi(dsn, user, pass, attr) -%]
534 [% people = dbi.query('SELECT * FROM usr ORDER BY id').get_all -%]
535 [% FOREACH p = people -%]
536 <person id="[% p.id %]">
537 <name>[% p.name %]</name>
538 </person>
539 [% END; global.people = people %]
540 -- expect --
541 <person id="abw">
542 <name>Andy Wardley</name>
543 </person>
544 <person id="craig">
545 <name>Craig Barratt</name>
546 </person>
547 <person id="hans">
548 <name>Hans von Lengerke</name>
549 </person>
550 <person id="mrp">
551 <name>Martin Portman</name>
552 </person>
553 <person id="sam">
554 <name>Simon Matthews</name>
555 </person>
556
557 -- test --
558 [% FOREACH p = global.people.reverse -%]
559 <person>[% p.name %]</person>
560 [% END %]
561 -- expect --
562 <person>Simon Matthews</person>
563 <person>Martin Portman</person>
564 <person>Hans von Lengerke</person>
565 <person>Craig Barratt</person>
566 <person>Andy Wardley</person>
567
568 -- test --
569 [% USE dbi(dsn, user, pass, attr) -%]
570 [% people = dbi.query('SELECT * FROM usr ORDER BY id') -%]
571 first: [% people.get.name %]
572 [% FOREACH p = people.get_all -%]
573 rest: [% p.name %]
574 [% END %]
575 -- expect --
576 first: Andy Wardley
577 rest: Craig Barratt
578 rest: Hans von Lengerke
579 rest: Martin Portman
580 rest: Simon Matthews
581
582 #------------------------------------------------------------------------
583 # test size() and max() methods
584 #------------------------------------------------------------------------
585
586 -- test --
587 [% USE dbi(dsn, user, pass, attr) -%]
588 [% users = dbi.query('SELECT * FROM usr ORDER BY id') -%]
589 size: [% users.size +%]
590 max: [% users.max +%]
591 -- expect --
592 size: 5
593 max: 4
594
595 -- test --
596 [% USE dbi(dsn, user, pass, attr) -%]
597 There are [% dbi.query('SELECT * FROM usr').size -%] users
598 -- expect --
599 There are 5 users
600
601 -- test --
602 [% USE dbi(dsn, user, pass, attr) -%]