DBIx::Simple::Examples(3pm)

1DBIx::Simple::Examples(U3s)er Contributed Perl DocumentatDiBoInx::Simple::Examples(3)
2
3
4

NAME

6       DBIx::Simple::Examples - Examples of how to use DBIx::Simple
7

DESCRIPTION

9       DBIx::Simple provides a simplified interface to DBI, Perl's powerful
10       database module.
11

EXAMPLES

13   General
14           #!/usr/bin/perl -w
15           use strict;
16           use DBIx::Simple;
17
18           # Instant database with DBD::SQLite
19           my $db = DBIx::Simple->connect('dbi:SQLite:dbname=file.dat')
20               or die DBIx::Simple->error;
21
22           # Connecting to a MySQL database
23           my $db = DBIx::Simple->connect(
24               'DBI:mysql:database=test',     # DBI source specification
25               'test', 'test',                # Username and password
26               { RaiseError => 1 }            # Additional options
27           );
28
29           # Using an existing database handle
30           my $db = DBIx::Simple->connect($dbh);
31
32           # Abstracted example: $db->query($query, @variables)->what_you_want;
33
34           $db->commit or die $db->error;
35
36   Simple Queries
37           $db->query('DELETE FROM foo WHERE id = ?', $id) or die $db->error;
38
39           for (1..100) {
40               $db->query(
41                   'INSERT INTO randomvalues VALUES (?, ?)',
42                   int rand(10),
43                   int rand(10)
44               ) or die $db->error;
45           }
46
47           $db->query(
48               'INSERT INTO sometable VALUES (??)',
49               $first, $second, $third, $fourth, $fifth, $sixth
50           );
51           # (??) is expanded to (?, ?, ?, ?, ?, ?) automatically
52
53   Single row queries
54           my ($two)          = $db->query('SELECT 1 + 1')->list;
55           my ($three, $four) = $db->query('SELECT 3, 2 + 2')->list;
56
57           my ($name, $email) = $db->query(
58               'SELECT name, email FROM people WHERE email = ? LIMIT 1',
59               $mail
60           )->list;
61
62       Or, more efficiently:
63
64           $db->query('SELECT 1 + 1')->into(my $two);
65           $db->query('SELECT 3, 2 + 2')->into(my ($three, $four));
66
67           $db->query(
68               'SELECT name, email FROM people WHERE email = ? LIMIT 1',
69               $mail
70           )->into(my ($name, $email));
71
72   Fetching all rows in one go
73       One big flattened list (primarily for single column queries)
74
75           my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat;
76
77       Rows as array references
78
79           for my $row ($db->query('SELECT name, email FROM people')->arrays) {
80               print "Name: $row->[0], Email: $row->[1]\n";
81           }
82
83       Rows as hash references
84
85           for my $row ($db->query('SELECT name, email FROM people')->hashes) {
86               print "Name: $row->{name}, Email: $row->{email}\n";
87           }
88
89   Fetching one row at a time
90       Rows into separate variables
91
92           {
93               my $result = $db->query('SELECT name, email FROM people');
94               $result->bind(my ($name, $email));
95               while ($result->fetch) {
96                   print "Name: $name, Email: $email\n";
97               }
98           }
99
100       or:
101
102           {
103               my $result = $db->query('SELECT name, email FROM people');
104               while ($result->into(my ($name, $email))) {
105                   print "Name: $name, Email: $email\n";
106               }
107           }
108
109       Rows as lists
110
111           {
112               my $result = $db->query('SELECT name, email FROM people');
113               while (my @row = $result->list) {
114                   print "Name: $row[0], Email: $row[1]\n";
115               }
116           }
117
118       Rows as array references
119
120           {
121               my $result = $db->query('SELECT name, email FROM people');
122               while (my $row = $result->array) {
123                   print "Name: $row->[0], Email: $row->[1]\n";
124               }
125           }
126
127       Rows as hash references
128
129           {
130               my $result = $db->query('SELECT name, email FROM people');
131               while (my $row = $result->hash) {
132                   print "Name: $row->{name}, Email: $row->{email}\n";
133               }
134           }
135
136   Building maps (also fetching all rows in one go)
137       map
138
139       A hash of hashes
140
141           my $customers =
142               $db
143               -> query('SELECT id, name, location FROM people')
144               -> map_hashes('id');
145
146           # $customers = { $id => { name => $name, location => $location }, ... }
147
148       A hash of arrays
149
150           my $customers =
151               $db
152               -> query('SELECT id, name, location FROM people')
153               -> map_arrays(0);
154
155           # $customers = { $id => [ $name, $location ], ... }
156
157       A hash of values (two-column queries)
158
159           my $names =
160               $db
161               -> query('SELECT id, name FROM people')
162               -> map;
163
164           # $names = { $id => $name, ... }
165
166       group
167
168       A hash of arrays of hashes
169
170           my $customers =
171               $db
172               -> query('SELECT id, name, location FROM people')
173               -> group_hashes('location');
174
175           # $customers = { $location => [ { id => $id, name => $name }, ... ], ... }
176
177       A hash of arrays of arrays
178
179           my $customers =
180               $db
181               -> query('SELECT id, name, location FROM people')
182               -> group_arrays(2);
183
184           # $customers = { $location => [ [ $id, $name ], ... ], ... }
185
186       A hash of arrays of values (two-column queries)
187
188           my $names =
189               $db
190               -> query('SELECT location, name FROM people')
191               -> group;
192
193           # $names = { $location => [ $name, $name, ... ], ... }
194

EXAMPLES WITH SQL::Interp

196       If you have SQL::Interp installed, you can use the semi-abstracting
197       method "iquery". This works just like "query", but with parts of the
198       query interleaved with the bind arguments, passed as references.
199
200       You should read SQL::Interp. These examples are not enough to fully
201       understand all the possibilities.
202
203       The following examples are based on the documentation of SQL::Interp.
204
205           my $result = $db->iquery('INSERT INTO table', \%item);
206           my $result = $db->iquery('UPDATE table SET', \%item, 'WHERE y <> ', \2);
207           my $result = $db->iquery('DELETE FROM table WHERE y = ', \2);
208
209           # These two select syntax produce the same result
210           my $result = $db->iquery('SELECT * FROM table WHERE x = ', \$s, 'AND y IN', \@v);
211           my $result = $db->iquery('SELECT * FROM table WHERE', {x => $s, y => \@v});
212
213           for ($result->hashes) { ... }
214
215       Use a syntax highlighting editor for good visual distinction.
216
217       If you need the helper functions "sql" and "sql_type", you can import
218       them with "use SQL::Interp;"
219

EXAMPLES WITH SQL::Abstract

221       If you have SQL::Abstract installed, you can use the abstracting
222       methods "select", "insert", "update", "delete". These work like
223       "query", but instead of a query and bind arguments, use abstracted
224       arguments.
225
226       You should read SQL::Abstract. These examples are not enough to fully
227       understand all the possibilities.
228
229       The SQL::Abstract object is available (writable) through the "abstract"
230       property.
231
232       The following examples are based on the documentation of SQL::Abstract.
233
234   Overview
235       If you don't like the defaults, just assign a new object:
236
237           $db->abstract = SQL::Abstract->new(
238               case    => 'lower',
239               cmp     => 'like',
240               logic   => 'and',
241               convert => 'upper'
242           );
243
244       If you don't assign any object, one will be created automatically using
245       the default options. The SQL::Abstract module is loaded on demand.
246
247           my $result = $db->select($table, \@fields, \%where, \@order);
248           my $result = $db->insert($table, \%fieldvals || \@values);
249           my $result = $db->update($table, \%fieldvals, \%where);
250           my $result = $db->delete($table, \%where);
251
252           for ($result->hashes) { ... }
253
254   Complete examples
255       select
256
257           my @tickets = $db->select(
258               'tickets', '*', {
259                   requestor => 'inna',
260                   worker    => ['nwiger', 'rcwe', 'sfz'],
261                   status    => { '!=', 'completed' }
262               }
263           )->hashes;
264
265       insert
266
267       If you already have your data as a hash, inserting becomes much easier:
268
269           $db->insert('people', \%data);
270
271       Instead of:
272
273           $db->query(
274               q[
275                   INSERT
276                   INTO people (name, phone, address, ...)
277                   VALUES (??)
278               ],
279               @data{'name', 'phone', 'address', ... }
280           );
281
282       update, delete
283
284           $db->update(
285               'tickets', {
286                   worker    => 'juerd',
287                   status    => 'completed'
288               },
289               { id => $id }
290           )
291
292           $db->delete('tickets', { id => $id });
293
294       where
295
296       The "where" method is not wrapped directly, because it doesn't generate
297       a query and thus doesn't really have anything to do with the database
298       module.
299
300       But using the "abstract" property, you can still easily access it:
301
302           my $where = $db->abstract->where({ foo => $foo });
303

EXAMPLES WITH DBIx::XHTML_Table

305       If you have DBIx::XHTML_Table installed, you can use the result methods
306       "xto" and "html".
307
308       You should read DBIx::XHTML_Table. These examples are not enough to
309       fully understand what is going on. When reading that documentation,
310       note that you don't have to pass hash references to DBIx::Simple's
311       methods. It is supported, though.
312
313       DBIx::XHTML_Table is loaded on demand.
314
315   Overview
316       To print a simple table, all you have to do is:
317
318           print $db->query('SELECT * FROM foo')->html;
319
320       Of course, anything that produces a result object can be used. The same
321       thing using the abstraction method "select" would be:
322
323           print $db->select('foo', '*')->html;
324
325       A DBIx::XHTML_Table object can be generated with the "xto" (XHTML_Table
326       Object) method:
327
328           my $table = $db->query($query)->xto;
329
330   Passing attributes
331       DBIx::Simple sends the attributes you pass to "html" both to the
332       constructor and the output method. This allows you to specify both HTML
333       attributes (like "bgcolor") and options for XHTML_Table (like
334       "no_ucfirst" and "no_indent") all at once:
335
336           print $result->html(
337               tr         => { bgcolor => [ qw/silver white/ ] },
338               no_ucfirst => 1
339           );
340
341   Using an XHTML_Table object
342       Not everything can be controlled by passing attributes. For full
343       flexibility, the XHTML_Table object can be used directly:
344
345           my $table = $db->query($query)->xto(
346               tr => { bgcolor => [ qw/silver white/ ] }
347           );
348
349           $table->set_group('client', 1);
350           $table->calc_totals('credit', '%.2f');
351
352           print $table->output({ no_ucfirst => 1 });  # note the {}!
353

EXAMPLES WITH Text::Table

355       "$result->text("neat")"
356               Neither neat nor pretty, but useful for debugging. Uses DBI's
357               "neat_list" method. Doesn't display column names.
358
359                   '1', 'Camel', 'mammal'
360                   '2', 'Llama', 'mammal'
361                   '3', 'Owl', 'bird'
362                   '4', 'Juerd', undef
363
364       "$result->text("table")"
365               Displays a simple table using ASCII lines.
366
367                   id | animal |  type
368                   ---+--------+-------
369                    1 |  Camel | mammal
370                    2 |  Llama | mammal
371                    3 |  Owl   | bird
372                    4 |  Juerd |
373
374       "$result->text("box")"
375               Displays a simple table using ASCII lines, with an outside
376               border.
377
378                   +----+--------+--------+
379                   | id | animal |  type  |
380                   +----+--------+--------+
381                   |  1 |  Camel | mammal |
382                   |  2 |  Llama | mammal |
383                   |  3 |  Owl   | bird   |
384                   |  4 |  Juerd |        |
385                   +----+--------+--------+
386
387       For "table" and "box", you need Anno Siegel's Text::Table module
388       installed.
389

AUTHOR

391       Juerd Waalboer <juerd@cpan.org> <http://juerd.nl/>
392