Aran Clary Deltac > DBIx-ResultSet-0.17 > DBIx::ResultSet

Download:
DBIx-ResultSet-0.17.tar.gz

Dependencies

Annotate this POD

View/Report Bugs
Module Version: 0.17   Source  

NAME ^

DBIx::ResultSet - Lightweight SQL query building and execution.

SYNOPSIS ^

    use DBIx::ResultSet;
    
    # Same arguments as DBI and DBIx::Connector.
    my $connector = DBIx::ResultSet->connect(
        $dsn, $user, $pass,
        $attr, #optional
    );
    
    my $users = $connector->resultset('users');
    my $adult_users = $users->search({ age => {'>=', 18} });
    
    print 'Users: ' . $users->count() . "\n";
    print 'Adult users: ' . $adult_users->count() . "\n";

DESCRIPTION ^

This module provides an API that simplifies the creation and execution of SQL queries. This is done by providing a thin wrapper around SQL::Abstract, DBIx::Connector, DBI, Data::Page, and the DateTime::Format::* modules.

This module is not an ORM. If you want an ORM use DBIx::Class, it is superb.

Some tips and tricks are recorded in the cookbook.

CONNECTING ^

In order to start using this module you must first configure the connection to your database. This is done using the connect() class method:

    # Same arguments as DBI and DBIx::Connector.
    my $connector = DBIx::ResultSet->connect(
        $dsn, $user, $pass,
        $attr, #optional
    );

The connect() class method is a shortcut for creating a DBIx::ResultSet::Connector object. When created this way, the AutoCommit DBI attribute will default to 1. This is done per the strong recommendations by "new" in DBIx::Connector.

By default the underlying DBIx::Connector object will be called with mode('fixup'). While not recommended, you can change the default connection mode by specifying the ConnectionMode attribute, as in:

    my $connector = DBIx::ResultSet->connect(
        $dsn, $user, $pass,
        { ConnectionMode => 'ping' },
    );

Alternatively you could create a DBIx::ResultSet::Connector object directly and pass your own custom-rolled DBIx::Connector object. For example:

    my $dbix_connector = DBIx::Connector->new(
        $dsn, $username, $password,
        { AutoCommit => 1 },
    );
    my $connector = DBIx::ResultSet::Connector->new(
        dbix_connector => $dbix_connector,
    );

SEARCH METHODS ^

search

    my $old_rs = $connector->resultset('users')->search({ status => 0 });
    my $new_rs = $old_rs->search({ age > 18 });
    print 'Disabled adults: ' . $new_rs->count() . "\n";

Returns a new result set object that overlays the passed in where clause on top of the old where clause, creating a new result set. The original result set's where clause is left unmodified.

search() never executes SQL queries. You can call search() as many times as you like and iteratively build a resultset as much as you want, but no SQL will be issued until you call one of the manipulation or retrieval methods.

MANIPULATION METHODS ^

These methods create, change, or remove data.

insert

    $users_rs->insert(
        { user_name=>'bob2003', email=>'bob@example.com' }, # fields to insert
    );
    # Executes: INSERT INTO users (user_name, email) VALUES (?, ?);

Creates and executes an INSERT statement.

update

    $users_rs->update(
        { phone => '555-1234' }, # fields to update
    );
    # Executes: UPDATE users SET phone = ?;

    $users_rs->search({ is_admin=>1 })->update({ phone=>'555-1234 });
    # Executes: UPDATE users SET phone = ? WHERE is_admin = ?;

Creates and executes an UPDATE statement.

delete

    # Delete all users!
    $users_rs->delete();
    # Executes: DELETE FROM users;
    
    # Or just the ones that are disabled.
    users_rs->search({status=>0})->delete();
    # Executes: DELETE FROM users WHERE status = 0;

Creates and executes a DELETE statement.

auto_pk

    $users_rs->insert({ user_name=>'jdoe' });
    my $user_id = $users_rs->auto_pk();
    # Executes (MySQL):  SELECT LAST_INSERT_ID();
    # Executes (SQLite): SELECT LAST_INSERT_ROWID();
    # etc...

Currently only MySQL and SQLite are supported. Oracle support will be added soon, and other databases making their way in as needed.

RETRIEVAL METHODS ^

These methods provide common shortcuts for retrieving data.

array_row

    my $user = $users_rs->search({ user_id => 32 })->array_row(
        ['created', 'email', 'phone'], # optional, fields to retrieve
    );
    print $user->[1]; # email

Creates and executes a SELECT statement and then returns an array reference. The array will contain only the first row that is retrieved, so you'll normally be doing this on a resultset that has already been limited to a single row by looking up by the table's primary key(s).

hash_row

    my $user = $users_rs->search({ user_id => 32 })->hash_row(
        ['created', 'email', 'phone'], # optional, fields to retrieve
    );
    print $user->{email}; # email

This works just the same as array_row(), above, but instead it returns a hash ref.

array_of_array_rows

    my $disabled_users = $users_rs->array_of_array_rows(
        ['user_id', 'email', 'phone'], # optional, fields to retrieve
    );
    print $disabled_users->[2]->[1];

Returns an array ref of array refs, one for each row returned.

array_of_hash_rows

    my $disabled_users = $rs->array_of_hash_rows(
        ['user_id', 'email', 'phone'], # optional, fields to retrieve
    );
    print $disabled_users->[2]->{email};

Returns an array ref of hash refs, one for each row.

hash_of_hash_rows

    my $disabled_users = $rs->hash_of_hash_rows(
        'user_name',                                # column to key the hash by
        ['user_id', 'user_name', 'email', 'phone'], # optional, fields to retrieve
    );
    print $disabled_users->{jsmith}->{email};

Returns a hash ref where the key is the value of the column that you specify as the first argument, and the value is a hash ref contains that row's data.

count

    my $total_users = $users_rs->count();

Returns that number of records that match the resultset.

column

    my $user_ids = $users_rs->column(
        'user_id', # column to retrieve
    );
    print 'User IDs: ' . join( ', ', @$user_ids );

Returns an array ref containing a single column's value for all matching rows.

STH METHODS ^

Get DBI statement handles when you have more specialized needs.

select_sth

    my ($sth, @bind) = $rs->select_sth(
        ['user_name', 'user_id'], # optional, fields to retrieve
    );
    $sth->execute( @bind );
    $sth->bind_columns( \my( $user_name, $user_id ) );
    while ($sth->fetch()) { ... }

If you want a little more power, or want you DB access a little more effecient for your particular situation, then you might want to get at the select sth.

insert_sth

    my $insert_sth;
    foreach my $user_name (qw( jsmith bthompson gfillman )) {
        my $fields = {
            user_name => $user_name,
            email     => $user_name . '@mycompany.com',
        };

        $insert_sth ||= $rs->insert_sth(
            $fields, # fields to insert
        );

        $insert_sth->execute(
            $rs->bind_values( $fields ),
        );
    }

If you're going to insert a *lot* of records you probably don't want to be re-generating the SQL every time you call insert().

bind_values

This mehtod calls SQL::Abstract's values() method. Normally this will be used in conjunction with insert_sth().

SQL METHODS ^

These methods just produce SQL, allowing you to have complete control of how it is executed.

select_sql

    my ($sql, @bind) = $users_rs->select_sql(
        ['email', 'age'], # optional, fields to retrieve
    );

Returns the SQL and bind values for a SELECT statement. This is useful if you want to handle DBI yourself, or for building subselects. See the DBIx::ResultSet::Cookbook for examples of subselects.

where_sql

    my ($sql, @bind) = $users_rs->search({ title => 'Manager' })->where_sql();

This works just like select_sql(), but it only returns the WHERE portion of the SQL query. This can be useful when you are doing complex joins where you need to write raw SQL, but you still want to build up your WHERE clause without writing SQL.

Note, that if order_by, limit, offset, rows, or page clauses or specified then the returned SQL will include those clauses as well.

ATTRIBUTES ^

connector

The DBIx::ResultSet::Connector object that this resultset is bound too.

pager

    my $rs = $connector->resultset('users')->search({}, {page=>2, rows=>50});
    my $pager = $rs->pager(); # a pre-populated Data::Page object

A Data::Page object pre-populated based on page() and rows(). If page() has not been specified then trying to access page() will throw an error.

The total_entries and last_page methods are proxied from the pager in to this class so that you can call:

    print $rs->total_entries();

Instead of:

    print $rs->pager->total_entries();

table

The name of the table that this result set will be using for queries.

where

The where clause hash ref to be used when executing queries.

clauses

Additional clauses, such as order_by, limit, offset, etc.

SEE ALSO ^

AUTHOR ^

Aran Clary Deltac <bluefeet@gmail.com>

LICENSE ^

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

syntax highlighting: