The Perl and Raku Conference 2025: Greenville, South Carolina - June 27-29 Learn more

 / 
SQL-Easy-2.0.0
River stage zero No dependents
/ SQL::Easy

NAME

SQL::Easy - extremely easy access to sql data

VERSION

version 2.0.0

SYNOPSIS

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.

DESCRIPTION

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.

METHODS

new

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_dbh

Get: 1) $self

Return: 1) $ with dbi handler

get_one

Get: 1) $self 2) $sql 3) @bind_variables

Return: 1) $ with the first value of request result

get_row

Get: 1) $self 2) $sql 3) @bind_variables

Return: 1) @ with first row in result table

get_col

Get: 1) $self 2) $sql 3) @bind_variables

Return: 1) @ with first column in result table

get_data

Get: 1) $self 2) $sql 3) @bind_variables

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";
}

get_tsv_data

Get: 1) $self 2) $sql 3) @bind_variables

Return: 1) $ with tab separated db data

Sample usage:


            

              
              
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

insert

Get: 1) $self 2) $sql 3) @bind_variables

Return: 1) $ with id of inserted record

Sub executes sql with bind variables and returns id of inseted record

execute

Get: 1) $self 2) $sql 3) @bind_variables

Return: -

Sub just executes sql that it recieves and returns nothing interesting

CONTRIBUTORS

  • Igor Sverdlov

SOURCE CODE

The source code for this module is hosted on GitHub https://github.com/bessarabov/SQL-Easy

BUGS

Please report any bugs or feature requests in GitHub Issues https://github.com/bessarabov/SQL-Easy

AUTHOR

Ivan Bessarabov <ivan@bessarabov.ru>

COPYRIGHT AND LICENSE

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.