Provided by: libdbix-simple-perl_1.37-1_all bug

NAME

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

DESCRIPTION

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

EXAMPLES

   General
           #!/usr/bin/perl -w
           use strict;
           use DBIx::Simple;

           # Instant database with DBD::SQLite
           my $db = DBIx::Simple->connect('dbi:SQLite:dbname=file.dat')
               or die DBIx::Simple->error;

           # Connecting to a MySQL database
           my $db = DBIx::Simple->connect(
               'DBI:mysql:database=test',     # DBI source specification
               'test', 'test',                # Username and password
               { RaiseError => 1 }            # Additional options
           );

           # Using an existing database handle
           my $db = DBIx::Simple->connect($dbh);

           # Abstracted example: $db->query($query, @variables)->what_you_want;

           $db->commit or die $db->error;

   Simple Queries
           $db->query('DELETE FROM foo WHERE id = ?', $id) or die $db->error;

           for (1..100) {
               $db->query(
                   'INSERT INTO randomvalues VALUES (?, ?)',
                   int rand(10),
                   int rand(10)
               ) or die $db->error;
           }

           $db->query(
               'INSERT INTO sometable VALUES (??)',
               $first, $second, $third, $fourth, $fifth, $sixth
           );
           # (??) is expanded to (?, ?, ?, ?, ?, ?) automatically

   Single row queries
           my ($two)          = $db->query('SELECT 1 + 1')->list;
           my ($three, $four) = $db->query('SELECT 3, 2 + 2')->list;

           my ($name, $email) = $db->query(
               'SELECT name, email FROM people WHERE email = ? LIMIT 1',
               $mail
           )->list;

       Or, more efficiently:

           $db->query('SELECT 1 + 1')->into(my $two);
           $db->query('SELECT 3, 2 + 2')->into(my ($three, $four));

           $db->query(
               'SELECT name, email FROM people WHERE email = ? LIMIT 1',
               $mail
           )->into(my ($name, $email));

   Fetching all rows in one go
       One big flattened list (primarily for single column queries)

           my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat;

       Rows as array references

           for my $row ($db->query('SELECT name, email FROM people')->arrays) {
               print "Name: $row->[0], Email: $row->[1]\n";
           }

       Rows as hash references

           for my $row ($db->query('SELECT name, email FROM people')->hashes) {
               print "Name: $row->{name}, Email: $row->{email}\n";
           }

   Fetching one row at a time
       Rows into separate variables

           {
               my $result = $db->query('SELECT name, email FROM people');
               $result->bind(my ($name, $email));
               while ($result->fetch) {
                   print "Name: $name, Email: $email\n";
               }
           }

       or:

           {
               my $result = $db->query('SELECT name, email FROM people');
               while ($result->into(my ($name, $email))) {
                   print "Name: $name, Email: $email\n";
               }
           }

       Rows as lists

           {
               my $result = $db->query('SELECT name, email FROM people');
               while (my @row = $result->list) {
                   print "Name: $row[0], Email: $row[1]\n";
               }
           }

       Rows as array references

           {
               my $result = $db->query('SELECT name, email FROM people');
               while (my $row = $result->array) {
                   print "Name: $row->[0], Email: $row->[1]\n";
               }
           }

       Rows as hash references

           {
               my $result = $db->query('SELECT name, email FROM people');
               while (my $row = $result->hash) {
                   print "Name: $row->{name}, Email: $row->{email}\n";
               }
           }

   Building maps (also fetching all rows in one go)
       map

       A hash of hashes

           my $customers =
               $db
               -> query('SELECT id, name, location FROM people')
               -> map_hashes('id');

           # $customers = { $id => { name => $name, location => $location }, ... }

       A hash of arrays

           my $customers =
               $db
               -> query('SELECT id, name, location FROM people')
               -> map_arrays(0);

           # $customers = { $id => [ $name, $location ], ... }

       A hash of values (two-column queries)

           my $names =
               $db
               -> query('SELECT id, name FROM people')
               -> map;

           # $names = { $id => $name, ... }

       group

       A hash of arrays of hashes

           my $customers =
               $db
               -> query('SELECT id, name, location FROM people')
               -> group_hashes('location');

           # $customers = { $location => [ { id => $id, name => $name }, ... ], ... }

       A hash of arrays of arrays

           my $customers =
               $db
               -> query('SELECT id, name, location FROM people')
               -> group_arrays(2);

           # $customers = { $location => [ [ $id, $name ], ... ], ... }

       A hash of arrays of values (two-column queries)

           my $names =
               $db
               -> query('SELECT location, name FROM people')
               -> group;

           # $names = { $location => [ $name, $name, ... ], ... }

EXAMPLES WITH SQL::Interp

       If you have SQL::Interp installed, you can use the semi-abstracting method "iquery". This
       works just like "query", but with parts of the query interleaved with the bind arguments,
       passed as references.

       You should read SQL::Interp. These examples are not enough to fully understand all the
       possibilities.

       The following examples are based on the documentation of SQL::Interp.

           my $result = $db->iquery('INSERT INTO table', \%item);
           my $result = $db->iquery('UPDATE table SET', \%item, 'WHERE y <> ', \2);
           my $result = $db->iquery('DELETE FROM table WHERE y = ', \2);

           # These two select syntax produce the same result
           my $result = $db->iquery('SELECT * FROM table WHERE x = ', \$s, 'AND y IN', \@v);
           my $result = $db->iquery('SELECT * FROM table WHERE', {x => $s, y => \@v});

           for ($result->hashes) { ... }

       Use a syntax highlighting editor for good visual distinction.

       If you need the helper functions "sql" and "sql_type", you can import them with "use
       SQL::Interp;"

EXAMPLES WITH SQL::Abstract

       If you have SQL::Abstract installed, you can use the abstracting methods "select",
       "insert", "update", "delete". These work like "query", but instead of a query and bind
       arguments, use abstracted arguments.

       You should read SQL::Abstract. These examples are not enough to fully understand all the
       possibilities.

       The SQL::Abstract object is available (writable) through the "abstract" property.

       The following examples are based on the documentation of SQL::Abstract.

   Overview
       If you don't like the defaults, just assign a new object:

           $db->abstract = SQL::Abstract->new(
               case    => 'lower',
               cmp     => 'like',
               logic   => 'and',
               convert => 'upper'
           );

       If you don't assign any object, one will be created automatically using the default
       options. The SQL::Abstract module is loaded on demand.

           my $result = $db->select($table, \@fields, \%where, \@order);
           my $result = $db->insert($table, \%fieldvals || \@values);
           my $result = $db->update($table, \%fieldvals, \%where);
           my $result = $db->delete($table, \%where);

           for ($result->hashes) { ... }

   Complete examples
       select

           my @tickets = $db->select(
               'tickets', '*', {
                   requestor => 'inna',
                   worker    => ['nwiger', 'rcwe', 'sfz'],
                   status    => { '!=', 'completed' }
               }
           )->hashes;

       insert

       If you already have your data as a hash, inserting becomes much easier:

           $db->insert('people', \%data);

       Instead of:

           $db->query(
               q[
                   INSERT
                   INTO people (name, phone, address, ...)
                   VALUES (??)
               ],
               @data{'name', 'phone', 'address', ... }
           );

       update, delete

           $db->update(
               'tickets', {
                   worker    => 'juerd',
                   status    => 'completed'
               },
               { id => $id }
           )

           $db->delete('tickets', { id => $id });

       where

       The "where" method is not wrapped directly, because it doesn't generate a query and thus
       doesn't really have anything to do with the database module.

       But using the "abstract" property, you can still easily access it:

           my $where = $db->abstract->where({ foo => $foo });

EXAMPLES WITH DBIx::XHTML_Table

       If you have DBIx::XHTML_Table installed, you can use the result methods "xto" and "html".

       You should read DBIx::XHTML_Table. These examples are not enough to fully understand what
       is going on. When reading that documentation, note that you don't have to pass hash
       references to DBIx::Simple's methods. It is supported, though.

       DBIx::XHTML_Table is loaded on demand.

   Overview
       To print a simple table, all you have to do is:

           print $db->query('SELECT * FROM foo')->html;

       Of course, anything that produces a result object can be used. The same thing using the
       abstraction method "select" would be:

           print $db->select('foo', '*')->html;

       A DBIx::XHTML_Table object can be generated with the "xto" (XHTML_Table Object) method:

           my $table = $db->query($query)->xto;

   Passing attributes
       DBIx::Simple sends the attributes you pass to "html" both to the constructor and the
       output method. This allows you to specify both HTML attributes (like "bgcolor") and
       options for XHTML_Table (like "no_ucfirst" and "no_indent") all at once:

           print $result->html(
               tr         => { bgcolor => [ qw/silver white/ ] },
               no_ucfirst => 1
           );

   Using an XHTML_Table object
       Not everything can be controlled by passing attributes. For full flexibility, the
       XHTML_Table object can be used directly:

           my $table = $db->query($query)->xto(
               tr => { bgcolor => [ qw/silver white/ ] }
           );

           $table->set_group('client', 1);
           $table->calc_totals('credit', '%.2f');

           print $table->output({ no_ucfirst => 1 });  # note the {}!

EXAMPLES WITH Text::Table

       "$result->text("neat")"
               Neither neat nor pretty, but useful for debugging. Uses DBI's "neat_list" method.
               Doesn't display column names.

                   '1', 'Camel', 'mammal'
                   '2', 'Llama', 'mammal'
                   '3', 'Owl', 'bird'
                   '4', 'Juerd', undef

       "$result->text("table")"
               Displays a simple table using ASCII lines.

                   id | animal |  type
                   ---+--------+-------
                    1 |  Camel | mammal
                    2 |  Llama | mammal
                    3 |  Owl   | bird
                    4 |  Juerd |

       "$result->text("box")"
               Displays a simple table using ASCII lines, with an outside border.

                   +----+--------+--------+
                   | id | animal |  type  |
                   +----+--------+--------+
                   |  1 |  Camel | mammal |
                   |  2 |  Llama | mammal |
                   |  3 |  Owl   | bird   |
                   |  4 |  Juerd |        |
                   +----+--------+--------+

       For "table" and "box", you need Anno Siegel's Text::Table module installed.

AUTHOR

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

SEE ALSO

       DBIx::Simple, SQL::Abstract