SQL::Easy - extremely easy access to sql data
version 2.0.0
Let image we have db 'blog' with one table:
CREATE TABLE `posts` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `dt` datetime NOT NULL, `title` VARCHAR(255) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; insert INTO `posts` (`dt`, `title`) values ('1', '2010-07-14 18:30:31', 'Hello, World!'), ('2', '2010-08-02 17:13:35', 'use perl or die') ;
Then we we can do some things with SQL::Easy
use SQL::Easy; my $se = SQL::Easy->new( database => 'blog', user => 'user', password => 'secret', host => '127.0.0.1', # default '127.0.0.1' port => 3306, # default 3306 connection_check_threshold => 30, # default 30 ); # get scalar my $posts_count = $se->get_one('select count(id) from posts'); # get list my ($dt, $title) = $se->get_row( 'select dt, title from posts where id = ?', 1, ); # get arrayref my $posts = $se->get_data( 'select dt_post, title from posts order by id', ); # We will get # [ # { # 'dt_post' => '2010-07-14 18:30:31', # 'title' => 'Hello, World!' # }, # { # 'dt_post' => '2010-08-02 17:13:35', # 'title' => 'use perl or die' # } # ]; my $post_id = $se->insert( 'insert into images ( dt_post, title ) values ( now(), ? )', 'My new idea', ); # $post_id is the id of the new row in table # Sometimes you don't need the any return value (when you delete or update # rows), you only need to execute some sql. You can do it by $se->execute( 'update posts set title = ? where id = ?', 'JAPH', 2, );
If it passed more than 'connection_check_threshold' seconds between requests the module will check that db connection is alive and reconnect if it went away.
On cpan there are a lot of ORMs. The problem is that sometimes ORM are too complex. You don't need ORM in a simple script with couple requests. ORM is sometimes difficult to use, you need to learn its syntax. From the other hand you already knows SQL language.
SQL::Easy give you easy access to data stored in databases using well known SQL language.
SQL::Easy version numbers uses Semantic Versioning standart. Please visit http://semver.org/ to find out all about this great thing.
Get: 1) $class 2) $params - hashref with constraction information
Return: 1) object
my $se = SQL::Easy->new( database => 'blog', user => 'user', password => 'secret', host => '127.0.0.1', # default '127.0.0.1' port => 3306, # default 3306 connection_check_threshold => 30, # default 30 );
Or, if you already have dbh:
my $se2 = SQL::Easy->new( dbh => $dbh, );
For example, if you are woring with Dancer::Plugin::Database you can use this command to create SQL::Easy object:
my $se3 = SQL::Easy->new( dbh => database(), );
This is one special parameter `cb_before_execute`. It should recieve callback. This callback is run just before the sql is executed. The callback recieves hash with keys 'sql' and 'bind_variables' that contains the values. The return value of this callback is returned.
my $se4 = SQL::Easy->new( ... cb_before_execute => sub { my (%params) = @_; my $sql = delete $params{sql}; my $bind_variables = delete $params{bind_variables}; print $sql . "\n"; print join("\n", @{$bind_variables}) . "\n"; return ''; } );
Get: 1) $self
Return: 1) $ with dbi handler
Get: 1) $self 2) $sql 3) @bind_variables
Return: 1) $ with the first value of request result
Return: 1) @ with first row in result table
Return: 1) @ with first column in result table
Return: 1) $ with array of hashes with the result of the query
Sample usage:
my $a = $se->get_data('select * from t1'); print scalar @{$a}; # quantity of returned rows print $a->[0]{filename}; # element 'filename' in the first row for(my $i = 0; $i <= $#{$a}; $i++) { print $a->[$i]{filename}, "\n"; }
Return: 1) $ with tab separated db data
print $se->get_tsv_data( 'select dt_post, title from posts order by id limit 2', );
It will output the text below (with the tabs as separators).
dt_post title 2010-07-14 18:30:31 Hello, World! 2010-08-02 17:13:35 use perl or die
Return: 1) $ with id of inserted record
Sub executes sql with bind variables and returns id of inseted record
Return: -
Sub just executes sql that it recieves and returns nothing interesting
Igor Sverdlov
The source code for this module is hosted on GitHub https://github.com/bessarabov/SQL-Easy
Please report any bugs or feature requests in GitHub Issues https://github.com/bessarabov/SQL-Easy
Ivan Bessarabov <ivan@bessarabov.ru>
This software is copyright (c) 2010 by Ivan Bessarabov.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.
To install SQL::Easy, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::Easy
CPAN shell
perl -MCPAN -e shell install SQL::Easy
For more information on module installation, please visit the detailed CPAN module installation guide.