DBIx::Simple::Examples(3pm)

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, ... ], ... }

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 });

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 {}!