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       A hash of hashes
138
139           my $customers =
140               $db
141               -> query('SELECT id, name, location FROM people')
142               -> map_hashes('id');
143
144           # $customers = { $id => { name => $name, location => $location } }
145
146       A hash of arrays
147
148           my $customers =
149               $db
150               -> query('SELECT id, name, location FROM people')
151               -> map_arrays(0);
152
153           # $customers = { $id => [ $name, $location ] }
154
155       A hash of values (two-column queries)
156
157           my $names =
158               $db
159               -> query('SELECT id, name FROM people')
160               -> map;
161
162           # $names = { $id => $name }
163

EXAMPLES WITH SQL::Interp

165       If you have SQL::Interp installed, you can use the semi-abstracting
166       method "iquery". This works just like "query", but with parts of the
167       query interleaved with the bind arguments, passed as references.
168
169       You should read SQL::Interp. These examples are not enough to fully
170       understand all the possibilities.
171
172       The following examples are based on the documentation of SQL::Interp.
173
174           my $result = $db->iquery('INSERT INTO table', \%item);
175           my $result = $db->iquery('UPDATE table SET', \%item, 'WHERE y <> ', \2);
176           my $result = $db->iquery('DELETE FROM table WHERE y = ', \2);
177
178           # These two select syntax produce the same result
179           my $result = $db->iquery('SELECT * FROM table WHERE x = ', \$s, 'AND y IN', \@v);
180           my $result = $db->iquery('SELECT * FROM table WHERE', {x => $s, y => \@v});
181
182           for ($result->hashes) { ... }
183
184       Use a syntax highlighting editor for good visual distinction.
185
186       If you need the helper functions "sql" and "sql_type", you can import
187       them with "use SQL::Interp;"
188

EXAMPLES WITH SQL::Abstract

190       If you have SQL::Abstract installed, you can use the abstracting
191       methods "select", "insert", "update", "delete". These work like
192       "query", but instead of a query and bind arguments, use abstracted
193       arguments.
194
195       You should read SQL::Abstract. These examples are not enough to fully
196       understand all the possibilities.
197
198       The SQL::Abstract object is available (writable) through the "abstract"
199       property.
200
201       The following examples are based on the documentation of SQL::Abstract.
202
203   Overview
204       If you don't like the defaults, just assign a new object:
205
206           $db->abstract = SQL::Abstract->new(
207               case    => 'lower',
208               cmp     => 'like',
209               logic   => 'and',
210               convert => 'upper'
211           );
212
213       If you don't assign any object, one will be created automatically using
214       the default options. The SQL::Abstract module is loaded on demand.
215
216           my $result = $db->select($table, \@fields, \%where, \@order);
217           my $result = $db->insert($table, \%fieldvals || \@values);
218           my $result = $db->update($table, \%fieldvals, \%where);
219           my $result = $db->delete($table, \%where);
220
221           for ($result->hashes) { ... }
222
223   Complete examples
224       select
225
226           my @tickets = $db->select(
227               'tickets', '*', {
228                   requestor => 'inna',
229                   worker    => ['nwiger', 'rcwe', 'sfz'],
230                   status    => { '!=', 'completed' }
231               }
232           )->hashes;
233
234       insert
235
236       If you already have your data as a hash, inserting becomes much easier:
237
238           $db->insert('people', \%data);
239
240       Instead of:
241
242           $db->query(
243               q[
244                   INSERT
245                   INTO people (name, phone, address, ...)
246                   VALUES (??)
247               ],
248               @data{'name', 'phone', 'address', ... }
249           );
250
251       update, delete
252
253           $db->update(
254               'tickets', {
255                   worker    => 'juerd',
256                   status    => 'completed'
257               },
258               { id => $id }
259           )
260
261           $db->delete('tickets', { id => $id });
262
263       where
264
265       The "where" method is not wrapped directly, because it doesn't generate
266       a query and thus doesn't really have anything to do with the database
267       module.
268
269       But using the "abstract" property, you can still easily access it:
270
271           my $where = $db->abstract->where({ foo => $foo });
272

EXAMPLES WITH DBIx::XHTML_Table

274       If you have DBIx::XHTML_Table installed, you can use the result methods
275       "xto" and "html".
276
277       You should read DBIx::XHTML_Table. These examples are not enough to
278       fully understand what is going on. When reading that documentation,
279       note that you don't have to pass hash references to DBIx::Simple's
280       methods. It is supported, though.
281
282       DBIx::XHTML_Table is loaded on demand.
283
284   Overview
285       To print a simple table, all you have to do is:
286
287           print $db->query('SELECT * FROM foo')->html;
288
289       Of course, anything that produces a result object can be used. The same
290       thing using the abstraction method "select" would be:
291
292           print $db->select('foo', '*')->html;
293
294       A DBIx::XHTML_Table object can be generated with the "xto" (XHTML_Table
295       Object) method:
296
297           my $table = $db->query($query)->xto;
298
299   Passing attributes
300       DBIx::Simple sends the attributes you pass to "html" both to the
301       constructor and the output method. This allows you to specify both HTML
302       attributes (like "bgcolor") and options for XHTML_Table (like
303       "no_ucfirst" and "no_indent") all at once:
304
305           print $result->html(
306               tr         => { bgcolor => [ qw/silver white/ ] },
307               no_ucfirst => 1
308           );
309
310   Using an XHTML_Table object
311       Not everything can be controlled by passing attributes. For full
312       flexibility, the XHTML_Table object can be used directly:
313
314           my $table = $db->query($query)->xto(
315               tr => { bgcolor => [ qw/silver white/ ] }
316           );
317
318           $table->set_group('client', 1);
319           $table->calc_totals('credit', '%.2f');
320
321           print $table->output({ no_ucfirst => 1 });  # note the {}!
322

EXAMPLES WITH Text::Table

324       "$result->text("neat")"
325               Neither neat nor pretty, but useful for debugging. Uses DBI's
326               "neat_list" method. Doesn't display column names.
327
328                   '1', 'Camel', 'mammal'
329                   '2', 'Llama', 'mammal'
330                   '3', 'Owl', 'bird'
331                   '4', 'Juerd', undef
332
333       "$result->text("table")"
334               Displays a simple table using ASCII lines.
335
336                   id | animal |  type
337                   ---+--------+-------
338                    1 |  Camel | mammal
339                    2 |  Llama | mammal
340                    3 |  Owl   | bird
341                    4 |  Juerd |
342
343       "$result->text("box")"
344               Displays a simple table using ASCII lines, with an outside
345               border.
346
347                   +----+--------+--------+
348                   | id | animal |  type  |
349                   +----+--------+--------+
350                   |  1 |  Camel | mammal |
351                   |  2 |  Llama | mammal |
352                   |  3 |  Owl   | bird   |
353                   |  4 |  Juerd |        |
354                   +----+--------+--------+
355
356       For "table" and "box", you need Anno Siegel's Text::Table module
357       installed.
358

AUTHOR

360       Juerd Waalboer <juerd@cpan.org> <http://juerd.nl/>
361