Oleksandr Kharchenko > DBIx-Brev-0.02 > DBIx::Brev

Download:
DBIx-Brev-0.02.tar.gz

Dependencies

Annotate this POD

View/Report Bugs
Module Version: 0.02   Source  

NAME ^

DBIx::Brev - Brevity is the soul of wit. Swiss-Army chainsaw for perl-DB one liners and to make code laconic and focused :). Very handy and very perlish set of subroutines to make you happy while working with database!

SYNOPSIS ^

  perl -MDBIx::Brev=mydb -e"printf q{profit: %s\n},sql_value(q{select sum(amount) from Profit})"
  
  %car_price = map @$_, sql_query q{select id,price from Cars};

DESCRIPTION ^

DBIx::Brev provides framework for using DBI in more convenient way:

1) Establish connection using db aliases, setup in config file provided by Config::General (apache style)

2) Keep & reestablish connection automatically using DBIx::Connector facilities.

3) Using default connection paradigm to make DBI code laconic.

4) Switch easily & instantly between databases using cached connections

5) You can switch context by db_use($db_alias) or you can execute each of sql_xxx/inserts subroutines by specifiing explicit db alias as a first parameter without switching default database connection.

To make it work put to ~/dbi.conf or /etc/dbi.conf database aliases and switch easily between databases. It's suitable for one liners where less code is best approach. Also it will be good for everyone who likes laconic code.

PPM DBIx::Brev is pure perl module and can be used without installation additional modules and without creating dbi.conf with aliases. This module can be installed and used just by copying it to some PERL5LIB directory. Config::General, DBIx::Connector, SQL::SplitStatement powerlift it and provide some useful features but if you have restricted environment, where you can't deploy those modules, it will work all right without them. ^

For example perl -MDBIx::Brev=dbi:SQLite:dbname=~/svn/reponame.sqlite -e'print sql_value(q{select count(*) from commits})'

use DBIx::Brev "dbi:Sybase:server=ENGINEERING",username=>"admin",password=>"ytrewq";

EXPORT ^

sql_exec, sql_map sql_query, sql_query_hash, sql_hash, sql_value, db_use, quote,

db_use

my $dbc = db_use('test_db');

Connects to specified database using alias and switch current dbc to it, Then all the sql_ subroutines will work with that database unless explicit db_alias or db connections is specified. Take into account that unless you call it in void context:

  db_use('test_db');

It will NOT switch default database account if it is already set. It is to force using alias instead of $dbc when you want execute some query with explicit database connection:

  my $profit = sql_value 'mydb', 'select sum(amount) from Profit';

Of course you can do it this way as well

  my $dbc = db_use('mydb'); 
  my $profit = sql_value $dbc, 'select sum(amount) from Profit';

But what the point?

sql_value

 my $max = sql_value("select max(id) from B_Hotel");
 my ($min,$max,$count) = sql_value("select min(id),max(id),count(*) from B_Hotel");

sql_value takes only first row of query. returns value of first column from first row in scalar context or array of column's values from first row completely in array context. You can also specify explicit db alias or $dbc as for all other sql_ subroutines. That way specified db will be used to run the query. Default database context will not be changed.

sql_query

 my %car_price = map @$_,sql_query("select id,price from Cars");
 my $germa_car_ids = join ",",sql_query "select id from Cars where Country=?",'Germany';

returns array/ref to array (depending on context) with records for sql query. if query consists only one column it scalarizes the result so it returns simple array with values. it can be useful for selecting list of ids.

sql_map

 my %entity_aggregate = sql_map {$_=>aggregate($_)} q{select id from Entity};

 sql_map {$_} $sql

is almost the same as

 map $_,sql_query $sql

But sql_query uses $sth->fetchall_arrayref, while sql_map iterates with $sth->fetch and then execute callback on fetched record.

sql_hash

works like sql_value but returns hash with column_names as keys which represents first row from the result of query it's useful when using * in query:

 my %record=sql_hash("select * from Cars where id=?",$car_id);

sql_query_hash

It's like sql_query & sql_hash , so it returns array of hashes

sql_exec

sql_exec [$dbc],$sql,[,$attr],@bind_values

Use it when you need execute update/insert/delete statements

 sql_exec "update Car set price=price*? where demand > ?",1.05,1000;

It returns total number of affected rows.

sql_exec supports execution of multiple SQL statements in single transaction. It uses SQL::SplitStatement split_with_placeholders to split statements, so you can use placeholders for each statement.

You can specify options which affects sql_exec:

 sql_exec {splitter_options=>{},no_commit=>1,%sth_prepare_attr},$sql;

$no_commit means it will not commit after all statements are executed.

$splitter_options is passed to SQL::SplitStatement->new($splitter_options)

%sth_prepare_attr is passed to each $dbh->do($sql,\%attr,@bind_values)

inserts

    $records = [[1,'first'],[2,'second']];
    inserts "insert into table_name(id,name)",$records;

Inserts multiple records at one (or few) statements. It uses ANSI SQL syntax to do that

    insert into table_name(id,name)
    select 1,'first'
    union all
    select 2,'second'

So it builds that huge sql statement and then executes it. It splits records into chunks of 500 records per statement. This can be redefined:

    inserts "insert into t",$many_records,{step=>40_000}

Also if you don't want inserts quotize values you can pass array of strings to it

    # quote binary values for sqlite as a x'HEX_DIGITS' literals
    $records = [map sprintf("x'%s'",unpack('h*',$_)),@binvalues];
    inserts "insert into table(blob_field)",$records;

sql_in

  $r = sql_query 'db1','select * from car_details where car_id '.
  sql_in('db2','select id from car where country=?','Germany');
  
  This subroutine executes specified query and then creates IN ($list) part of statement:
  
  IN (1,3,4)
  
  I believe it's more convenient and straightforward then annoying (repetitive)
  
  $dbh = dbc->dbh();
  $ids = join ',', map $dbh->quote($_), sql_query q{select id from Cars};
  $r = sql_query 'select * from car_details where id in (' .
  ($ids?$ids:'NULL') . ')';

CONFIGURATION WITH DATABASE ALIASES ^

It is simple text file ~/dbi.conf or /etc/dbi.conf with sections which define DB alias this way

 <database dbm>
 data_source=dbi:SQLite:dbname=/home/user/db/mydb
 </database>
 <database mysqldb>
 data_source=DBI:mysql:database=$database;host=$hostname;port=$port
 username=$user
 password=$password
 </database>

If you want call it different name you can use environment variable DBI_CONF or call

 DBIx::Brev::load_config($config_file)

Before first invocation of

  db_use($db_alias);

See DBIx-Brev.t for example. It creates config file on the fly and then use load_config and then db_use.

SEE ALSO ^

DBI DBIx::Connector Config::General

AUTHOR ^

Oleksandr Kharchenko <okharch@okharch.com>

COPYRIGHT AND LICENSE ^

Copyright (C) 2013 by Oleksandr Kharchenko <okharch@okharch.com>

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.16.3 or, at your option, any later version of Perl 5 you may have available.

syntax highlighting: