NAME

SQL::Easy - extremely easy access to sql data

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.

SYNOPSIS

    use SQL::Easy;
    use Data::Dumper; # this is only for example, you don't need it in real script
    
    # create object
    my $se = SQL::Easy->new( {
            database => 'my_blog',
            user => 'user',
            password => 'secret',
            host => 'localhost'                     # default 'localhost'
            port => 3306,                           # default 3306
            connection_check_threshold => 30,       # default 30
            debug => 0,
    } );

    # or, if you already have dbh:
    # ( example how to you can use SQL::Easy with Dancer::Plugin::Database )
    my $dbh = database();
    my $se2 = SQL::Easy->new( { dbh=>$dbh } );

    # let's find out how many blog posts do I have:
    my $posts_count = $se->return_one('select count(id) from posts');
    print Dumper $posts_count; # will print $VAR1 = 42;

    # some data about post with id 1
    my @a = $se->return_row("select dt_post, title from posts where id = ?", 1);
    print Dumper @a;
    # will print:
    =head2
        $VAR1 = '2010-07-14 18:30:31';
        $VAR2 = 'Hello, World!';
    =cut

    # some data about 2 posts:
    print Dumper $se->return_data("select dt_post, title from posts order by id limit 2");
    =head2
        $VAR1 = [
                  {
                    'dt_post' => '2010-07-14 18:30:31',
                    'title' => 'Hello, World!'
                  },
                  {
                    'dt_post' => '2010-08-02 17:13:35',
                    'title' => 'use perl or die'
                  }
                ];
    =cut

    # Next. Let add new post:
    print Dumper $se->insert("insert into images ( dt_post, title ) values ( now(), ? )", "My new idea");
    # It will print
    # $VAR1 = 43;
    # and 43 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 = ?", "JFDI", 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

GENERAL FUNCTIONS

new

 * Get: 1) hash with connection information 
 * Return: 1) object 

Sub creates an object

return_dbh

 * Get: - 
 * Return: 1) $ with dbi handler 

return_one

 * Get: 1) $ sql 2) @ bind variables
 * Return: 1) $ with first value of request result

return_row

 * Get: 1) $ sql 2) @ bind variables
 * Return: 1) @ with first row in result table

return_col

 * Get: 1) $ sql 2) @ bind variables
 * Return: 1) @ with first column in result table

return_data

 * Get: 1) $ sql 2) @ bind variables
 * Return: 1) $ with array of hashes with the result of the query

Sample usage:

    my $a = $se->return_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";
    }

insert

 * Get: 1) $ sql 2) @ bind variables
 * Return: 1) $ with id of inserted record

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

execute

 * Get: 1) $ sql 2) @ bind variables
 * Return: -

Sub just executes sql that it recieves and returns noting interesting

log_debug

 * Get: 1) $ with debug text 
 * Return: -

If the debug is turned on sub prints sql queries that are executed

AUTHOR

Ivan Bessarabov, <ivan@bessarabov.ru>

SOURCE CODE

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

cpanm SQL::Easy

perl -MCPAN -e shell
install SQL::Easy