Quick'n'Dirty SQL

I'm a convinced DBIx::Class user, but from time to time I find myself in need of a quick'n'dirty alternative. Something that let's me meddle with a database without the need of setting up result set classes or using loaders, but at the same time something more powerful and expressive than pure DBI.

Therefore, I present to you DBIx::Simple . It's half-way between the thought-out, "enterprisey" DBIx::Class and the raw, "bare-metal" DBI.

Let's see what you can do with it! It tend to use it in conjunction with SQL::Abstract, since I'm familiar with it via the search method of DBIx::Class.

First things first: setting up a DBIx::Simple object is trivial if you already have a connected DBIx::Class schema:

$my_dbs = DBIx::Simple->connect( $my_schema->storage->dbh );

Even if you don't have a schema, you can use the same semantics as with DBIC (namely an array):

$my_dbs = DBIx::Simple->connect( $dsn, $user, $pass, \%options );

Quick'n'Dirty Hashes

Let's say we have a database which describes articles (i.e. there's table articles which has an article_id and a name column) and we want to turn that into a hash with the id as key and the name as value:

%articles = $my_dbs->select('articles')->map;

Note: assumes the table has just these two columns and article_id is the first column.

OK, but what if we want to mark some articles in the database as unavailable and have the hash return only the available articles? Just add an available column and there you go:

$available_articles = $my_dbs->select('articles', [qw/article_id name/], { available => 1 })->map;

Note: now we specify the two columns which make up the hash, along with the select condition so order and additional columns are not relevant anymore.

You might have noticed that the first code snippet creates a hash, while the second one creates a hash reference. DBIx::Simple will detect the context and return the appropriate value.

Quick'n'Dirty Arrays

You can also just pull out a list of all articles names, instead of a hash:

@article_list = $my_dbs->select('articles', ['name'])->flat;

Oh, just a list of unavailable articles, but you want the ids instead of the names? OK:

$unavailable_articles = $my_dbs->select('articles', ['article_id'], { available => 0 })->flat;

Note: as with map, DBIx::Simple will allow you to create either arrays or array references depending on context.

Quick'n'Dirty Complex Data Structures

Now let's consider something more complex. Let's get all the data pertaining to articles in a big array. Each record will be a hash with column names as keys and record values as values:

@all_articles = $my_dbs->select('articles')->hashes;

That's all there is to it. Of course you can get an array of arrays (with the arrays method, of course). For arrays of arrays it's best to specify a column list so as not to rely on the implicit column order provided by the database:

@articles_as_arrays = $my_dbs->select('articles', [qw/article_id name available supplier/])->arrays;

But let's go to something even more wonderful, like a hash of hashes. The first level hash will have the article_id as key and the second level hashes will have column names as hashes (just like in the previous examples):

$article_hash = $my_dbs->select('articles')->map_hashes('article_id');

As you can see, any way you want to slice and dice your database data, DBIx::Simple will lend a helpful hand.

Quick'n'Dirty Updates

Quickly make all articles from the ACME supplier as unavailable, because he's temporarily unable to deliver? No problem:

$my_dbs->update('articles', { available => 0 }, { supplier => 'ACME'} );

The first hash represents column to set and the second columns on which to filter.

Quick'n'Dirty Cleanup

And finally, as a bonus, a method to quickly empty all tables in a MySQL database (hopefully, it's the test database and not the production one):

@table_list = $my_dbs->query('SHOW TABLES')->flat;
$my_dbs->delete($table) foreach my $table (@table_list);

That's it! But there are plenty more examples and informations available on CPAN.