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!
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};
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.
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";
sql_exec, sql_map sql_query, sql_query_hash, sql_hash, sql_value, db_use, quote,
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?
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.
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.
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.
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);
It's like sql_query & sql_hash , so it returns array of hashes
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)
$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;
$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') . ')';
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.
DBI DBIx::Connector Config::General
Oleksandr Kharchenko <okharch@okharch.com>
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.
To install DBIx::Brev, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Brev
CPAN shell
perl -MCPAN -e shell install DBIx::Brev
For more information on module installation, please visit the detailed CPAN module installation guide.