The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
SQL-Bibliosoph-2.46
River stage zero No dependents
++
/ SQL::Bibliosoph

NAME

SQL::Bibliosoph - A SQL Statements Library

SYNOPSIS

    use SQL::Bibliosoph;


    my $bs = SQL::Bibliosoph->new(
            dbh      => $database_handle,
            catalog  => [ qw(users products <billing) ],

    # enables statement benchmarking and debug 
    #  (0.5 = logs queries that takes more than half second)
            benchmark=> 0.5,

    # enables debug to STDERR
            debug    => 1,      

    # enables memcached usage            
            memcached_address => '127.0.0.1:11322',

    # enables memcached usage  (multiple servers)            
            memcached_address => ['127.0.0.1:11322','127.0.0.2:11322']
    );



    # Using dynamic generated functions.  Wrapper funtions 
    # are automaticaly created on module initialization.

    # A query should something like:

    --[ get_products ]
      SELECT id,name FROM  product WHERE country = ?
    
    # Then ...
    my $products_ref = $bs->get_products($country);

    # Forcing numbers in parameters
    # Query:

     --[ get_products ]
      SELECT id,name FROM  product WHERE country = ? LIMIT #?,#?

    # Parameter ordering and repeating
    # Query:
    
     --[ get_products ]
      SELECT id,name 
           FROM  product 
           WHERE 1? IS NULL OR country = 1? 
            AND  price > 2? * 0.9 AND print > 2? * 1.1
           LIMIT #3?,#4?
    
    # then ...    
    my $products_ref = $bs->get_products($country,$price,$start,$limit);

    # The same, but with an array of hashs result (add h_ at the begining)

    my $products_array_of_hash_ref 
        = $bs->h_get_products($country,$price,$start,$limit);

    # Selecting only one row (add row_ at the begining)
    # Query:
    
     --[ get_one ]
      SELECT name,age FROM  person where id = ?;
    
    # then ...    
    my $product_ref = $bs->row_get_one($product_id);
    
    # Selecting only one value (same query as above)
    my $product_name = $bs->row_get_one($product_id)->[1];


    # Selecting only one row, but with HASH ref results
    #   (same query as above) (add rowh_ at the begining)
    my $product_hash_ref = $bs->rowh_get_one($product_id);
    

    # Inserting a row, with an auto_increment PK.
    # Query:
    
    --[ insert_person ]
      INSERT INTO person (name,age) VALUES (?,?);
    
    # then ...    
    my $last_insert_id = $bs->insert_person($name,$age);


    # Usefull when no primary key is defined
    my ($dummy_last_insert_id, $total_inserted) = $bs->insert_person($name,$age);

    Note that last_insert_id is only returned when using MYSQL (undef in other case).
    When using other engine you need to call an other query to get the last value. 
    For example, in ProgreSQL you can define:
    
    --[ LAST_VAL ]
         SELECT lastval()
    
    and then call LAST_VAL after an insert.


    # Updating some rows
    # Query:
    
    --[ age_persons ]
      UPDATE person SET age = age + 1 WHERE birthday = ?
    
    # then ...    
    my $updated_persons = $bs->age_persons($today);




    Memcached usage      

    # Mmemcached queries are only generated for hash, multiple rows, results h_QUERY, using de "ch_" prefix.

    my $products_array_of_hash_ref = $bs->ch_get_products({ttl => 10 }, $country,$price,$start,$limit);
    
    # To define a group of query (for later simulaneous expiration) use:
   
    my $products_array_of_hash_ref = $bs->ch_get_products(
        {ttl => 3600, group => 'product_of_'.$country }, 
        $country,$price,$start,$limit);

    my $products_array_of_hash_ref = $bs->ch_get_prices(
        {ttl => 3600, group => 'product_of_'.$country }, 
        $country,$price,$start,$limit);
 
    # Then, to force refresh in the two previous queries next time they are called, just use:
    #
        $bs->expire_group('product_of_'.$country);

DESCRIPTION

SQL::Bibliosoph is a SQL statement library engine that allow to clearly separate SQL statements from PERL code. It is currently tested on MySQL 5.x, but it should be easly ported to other engines.

The catalog files are prepared a the initialization, for performance reasons. The use of prepared statement also helps to prevents SQL injection attacks. SQL::Bibliosoph supports bind parameters in statements definition and bind parements reordering (See SQL::Bibliosoph::CatalogFile for details).

All functions throw 'carp' on error. The error message is 'SQL ERROR' and the mysql error reported by the driver.

Constructor parameters

dsn

The database handler. For example:

    my $dbh = DBI->connect($dsn, ...);
    my $bb  = SQL::Bibliosoph(dbh => $dbh, ...);

catalog

An array ref containg filenames with the queries. This files should use they SQL::Bibliosoph::CatalogFile format (SEE Perldoc for details). The suggested extension for these files is 'bb'. The name can be preceded with a "<" forcing the catalog the be open in "read-only" mode. In the mode, UPDATE, INSERT and REPLACE statement will be parsed. Note the calling a SQL procedure or function that actually modifies the DB is still allowed!

All the catalogs will be merged, be carefull with namespace collisions. the statement will be prepared at module constuction.

catalog_str

Allows to define a SQL catalog using a string (not a file). The queries will be merged with Catalog files (if any).

constants_from

In order to use the same constants in your PERL code and your SQL modules, you can declare a module using `constants_from` paramenter. Constants exported in that module (using @EXPORT) will be replaced in all catalog file before SQL preparation. The module must be in the @INC path.

Note: constants_from() is ignored in 'catalog_str' queries (sorry, not implemented, yet)

delayed

Do not prepare all the statements at startup. They will be prepared individualy, when they are used for the first time. Defaults to false(0).

benchmark

Use this to enable Query profilling. The elapsed time (in miliseconds) will be printed to STDERR after each query execution, if the time is bigger that `benchmark` (must be given in SECONDS, can be a floating point number).

debug

To enable debug (prints each query, and arguments, very useful during development).

Bibliosoph

n. person having deep knowledge of books. bibliognostic.

AUTHORS

SQL::Bibliosoph by Matias Alejo Garcia (matiu at cpan.org) and Lucas Lain.

CONTRIBUTORS

Juan Ladetto

WOLS

COPYRIGHT

Copyright (c) 2007-2010 Matias Alejo Garcia. All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

SUPPORT / WARRANTY

The SQL::Bibliosoph is free Open Source software. IT COMES WITHOUT WARRANTY OF ANY KIND.

SEE ALSO

SQL::Bibliosoph::CatalogFile

At http://nits.com.ar/bibliosoph you can find:

    * Examples
    * VIM syntax highlighting definitions for bb files
    * CTAGS examples for indexing bb files.

    You can also find the vim and ctags files in the /etc subdirectory.

    Lasted version at: http://github.com/matiu/SQL--Bibliosoph/tree/master

BUGS

This module is only tested with MySQL. Migration to other DB engines should be simple accomplished. If you would like to use Bibliosoph with other DB, please let me know and we can help you if you do the testing.