/ 
DBIx-Simple-0.07
River stage two • 21 direct dependents • 27 total dependents
/ DBIx::Simple

NAME

DBIx::Simple - Easy-to-use OO interface to DBI, capable of emulating subqueries

SYNOPSIS

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

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

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

Simple Queries

    $db->query('DELETE FROM foo WHERE id = ?', $id);
    die $db->{reason} if not $db->{success};

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

    $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;

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

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 }

Subquery emulation

    $db->esq(1);
    my @projects = $db->query(
	'
	    SELECT project_name
	    FROM   projects
	    WHERE  user_id = (
		SELECT id
		FROM   users
		WHERE  email = ?
	    )
	',
	$email
    )->flat;

DESCRIPTION

This module is aimed at ease of use, not at SQL abstraction or efficiency. The only thing this module does is provide a bone easy interface to the already existing DBI module. With DBIx::Simple, the terms dbh and sth are not used in the documentation (except for this description), although they're omnipresent in the module's source. You don't have to think about them.

A query returns a result object, that can be used directly to pick the sort of output you want. There's no need to check if the query succeeded in between calls, you can stack them safely, and check for success later. This is because failed queries have dummy results, objects of which all methods return undef.

DBIx::Simple object methods

DBIx::Simple->connect( ... )

This argument takes the exact arguments a normal DBI->connect would take. It's the constructor method, and it returns a new DBIx::Simple object. See also DBI.

query($query, @values)

This calls DBI's prepare and execute methods, passing the values along to replace ? placeholders. query returns a new DBIx::Simple::Result object (or DBIx::Simple::Dummy), that can be used immediately to get data out of it. You should always use placeholders instead of the variables themselves, as DBI will automatically quote and escape the values.

DBIx::Simple provides an omniholder placeholder that will expand to (?, ?, ...) with as many question marks as @values. There can be only one omniholder, and since it uses all given values, you shouldn't combine it with normal placeholders. This feature was inspired by the EZDBI module.

omniholder($new_value)

This sets the omniholder string. Use undef or an empty string to disable this feature. Please note that the given $new_value is not a regex. The default omniholder is (??).

emulate_subqueries($bool), esq($bool) - EXPERIMENTAL

Sets if DBIx::Simple should enable subquery emulation. Many databases, like Postgres and SQLite have support for subqueries built in. Some, like MySQL, have not. True (1) enables, false (0) disables. Defaults to false.

In normal MySQL, one would probably use SELECT projects.project FROM projects, users WHERE project.user_id = users.id AND user.email = ? to select the projects that belong to the user with a certain email address. Postgres people would write SELECT project FROM projects WHERE user_id = (SELECT id FROM users WHERE email = ?) instead.

Subqueries can make complex queries readable, but MySQL doesn't have them and many people use MySQL. Now they can have subqueries too!

Emulation is done by simply doing multiple queries.

This feature is experimental. Please let me know if it works and if you like it. Send your comments to <juerd@cpan.org>. Even if everything goes well, I'd like to get some feedback.

commit, rollback

These just call the DBI methods and Do What You Mean.

disconnect

Does What You Mean. Also note that the connection is automatically terminated when the object is destroyed (undef $db to do so explicitly), and that all statements are also finished when the object is destroyed. disconnect Does not destroy the object.

DBIx::Simple::Result object methods

new

The constructor should only be called internally, by DBIx::Simple itself. Some simple minded garbage collection is done in DBIx::Simple, and you shouldn't be directly creating your own result objects. The curious are encouraged to read the module's source code to find out what the arguments to new are.

list

list Returns a list of elements in a single row. This is like a dereferenced $result->array. In scalar context, returns only the first value of the row.

array and hash

These methods return a single row, in an array reference, or a hash reference, respectively. Internally, fetchrow_arrayref or fetchrow_hashref is used.

flat

flat Returns a list of all returned fields, flattened. This can be very useful if you select a single column. Consider flat to be list's plural.

arrays and hashes

These methods return a list of rows of array or hash references. Internally, fetchall_arrayref is dereferenced, or a lot of fetchrow_hashref returns are accumulated.

map_arrays($column_number) and map_hashes($column_name)

These methods build a hash, with the chosen column as keys, and the remaining columns in array or hash references as values. For map_arrays, the column number is optional and defaults to 0 (the first column). The methods return a reference to the built hash.

map

Returns a reference to a hash that was built using the first two columns as key/value pairs. Use this only if your query returns two values per row (other values will be discarded).

rows

Returns the number of rows. This function calls DBI's rows method, and may not do what you want. See DBI for a good explanation.

finish?

There is no finish method. To finish the statement, just let the object go out of scope (you should always use my, and use strict) or destroy it explicitly using undef $result.

FEEDBACK

I'd like to hear from you what you think about DBIx::Simple, and if it has made your life easier :). If you find serious bugs, let me know. If you think an important feature is missing, let me know (but I'm not going to implement functions that aren't used a lot, or that are only for effeciency, because this module has only one goal: simplicity). My email address can be found near the end of this document.

BUGS

Nothing is perfect, but let's try to create perfect things. Of course, this module shares all DBI bugs. If you want to report a bug, please try to find out if it's DBIx::Simple's fault or DBI's fault first, and don't report DBI bugs to me.

Note: the map functions do not check if the key values are unique. If they are not, keys are overwritten.

DISCLAIMER

No warranty, no guarantees. I hereby disclaim all responsibility for what might go wrong.

AUTHOR

Juerd <juerd@cpan.org>

SEE ALSO

DBI