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