NAME

 Collection::AutoSQL - class for collections of data, stored in database.

SYNOPSIS

  use Collection::AutoSQL;
  my $metaobj = new Collection::AutoSQL::
           dbh => $dbh,         #database connect
           table => 'metadata', #table name
           field=> 'mid',       #key field (IDs), usually primary,autoincrement
           cut_key =>1,         #delete field mid from readed records, 
                                #or delete_key=>1
           sub_ref =>
              #callback for create objects for readed records
              sub { my $id = shift; new MyObject:: shift }
 
             dbtype => 'pg' # set type of DataBase to PostgreSQL (default: mysql)

DESCRIPTION

Provide simply access to records, with unique field.

For exampe:

 HAVE mysql table:

 mysql> \u orders
 mysql> select * from beers;
 +-----+--------+-----------+
 | bid | bcount | bname     |
 +-----+--------+-----------+
 |   1 |      1 | heineken  |
 |   2 |      1 | broadside |
 |   3 |      2 | tiger     |
 |   4 |      2 | castel    |
 |   5 |      3 | karhu     |
 +-----+--------+-----------+
 5 rows in set (0.00 sec)

 my $beers = new Collection::AutoSQL::
  dbh     => $dbh,          #database connect
  table   => 'beers',       #table name
  field   => 'bid',         #key field (IDs), usually primary,autoincrement
  cut_key => 1;             #delete field 'bid' from readed records,


 my $heineken = $beers->fetch_one(1);
 #SELECT * FROM beers WHERE bid in (1)

 print Dumper($heineken);

 ...

      $VAR1 = {
             'bcount' => '1',
             'bname' => 'heineken'
              };
 ...
 
 $heineken->{bcount}++;

 my $karhu = $beers->fetch(5);
 #SELECT * FROM beers WHERE bid in (5)
 
 $karhu->{bcount}++;
 
 $beers->store;
 #UPDATE beers SET bcount='2',bname='heineken' where bid=1
 #UPDATE beers SET bcount='4',bname='karhu' where bid=5

 my $hash = $beers->fetch({bcount=>[4,1]});
 #SELECT * FROM beers WHERE  ( bcount in (4,1) )
 
 print Dumper($hash);
 
 ...

 $VAR1 = {
          '2' => {
                   'bcount' => '1',
                   'bname' => 'broadside'
                 },
          '5' => {
                   'bcount' => '4',
                   'bname' => 'karhu'
                 }
        };

  ...

METHODS

get_dbh

 Return current $dbh.

get_ids_where(<SQL where expression>)

Return ref to ARRAY of readed IDs.

_expand_rules ( <term1>[, <term2> ] )

convert array of terms to scructs with type field

Got { test => 1, guid => $two },'key'

Return array:

    (

        {
            'values' => [1],
            'term'   => '=',
            'field'  => 'test'
        },
        {
            'values' => ['4D56A984-0B5E-11DC-8292-3DE558089BC5'],
            'term'   => '=',
            'field'  => 'guid',
            'type' => 'varchar'
        }
    )

_prepare_where <query hash>

return <where> expression or undef else

list_ids [ flow=>$Flow],

Return list of ids

params:

 flow - Flow:: object for streaming results
 onpage - [pagination] count of ids on page
 page - [pagination] requested page ( depend on onpage)
 exp - ref to expression for select
 desc - revert sorting ([1,0])
 where -  custom where if needed, instead expr ['where sring', $query_param1,..]
 query - custom query
 uniq - set uniq flag ( eq GROUP BY (key) )
 order - ORDER BY field

return: [array] - array of ids

if used flow param: "string" - if error undef - ok

expamles:

    $c->list_ids() #return [array of ids]

    $c->list_ids(flow=>$flow, exp=>{ type=>"t1", "date<"=>12341241 },
        page=>2, onpage=>10, desc=>1  )

SEE ALSO

Collection::ActiveRecord, Collection, README

AUTHOR

Zahatski Aliaksandr, <zag@cpan.org>

COPYRIGHT AND LICENSE

Copyright (C) 2005-2011 by Zahatski Aliaksandr

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.8 or, at your option, any later version of Perl 5 you may have available.

cpanm Collection

perl -MCPAN -e shell
install Collection