Иван Бессарабов > SQL-Easy-0.06 > SQL::Easy

Download:
SQL-Easy-0.06.tar.gz

Dependencies

Annotate this POD

CPAN RT

Open  0
View/Report Bugs
Module Version: 0.06   Source   Latest Release: SQL-Easy-2.0.0

NAME ^

SQL::Easy - extremely easy access to sql data

VERSION ^

Version 0.06

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 ^

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
        debug    => 0,                     # default 0
    } );

    # 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.

METHODS ^

new

Get: 1) $class 2) $params - hashref with connection 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
        debug    => 0,                     # default 0
    } );

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(),
    } );

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

log_debug

Get: 1) $self 2) $sql

Return: -

If the debug is turned on sub wll print $sql to STDERR

AUTHOR ^

Ivan Bessarabov, <ivan@bessarabov.ru>

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

LICENSE AND COPYRIGHT ^

Copyright 2012 Ivan Bessarabov.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.

syntax highlighting: