NAME

       Catmandu::Store::DBI::Bag - implementation of a Catmandu::Bag for DBI

SYNOPSIS

           my $store = Catmandu::Store::DBI->new(
               data_source => "dbi:SQLite:dbname=/tmp/test.db",
               bags => {
                   data => {
                       mapping => {
                           _id => {
                               column => 'id',
                               type => 'string',
                               index => 1,
                               unique => 1
                           },
                           author => {
                               type => 'string'
                           },
                           subject => {
                               type => 'string',
                           },
                           _data => {
                               column => 'data',
                               type => 'binary',
                               serialize => 'all'
                           }
                       }
                   }
               }
           );

           my $bag = $store->bag('data');

           #SELECT
           {
               #SELECT * FROM DATA WHERE author = 'Nicolas'
               my $iterator = $bag->select( author => 'Nicolas' );
           }
           #CHAINED SELECT
           {
               #SELECT * FROM DATA WHERE author = 'Nicolas' AND subject = 'ICT'
               my $iterator = $bag->select( author => 'Nicolas' )->select( subject => 'ICT' );
           }
           #COUNT
           {
               #SELECT * FROM DATA WHERE author = 'Nicolas'
               my $iterator = $bag->select( author => 'Nicolas' );

               #SELECT COUNT(*) FROM ( SELECT * FROM DATA WHERE author = 'Nicolas' )
               my $count = $iterator->count();
           }
           #DETECT
           {
               #SELECT * FROM DATA WHERE author = 'Nicolas' AND subject = 'ICT' LIMIT 1
               my $record = $bag->select( author => 'Nicolas' )->detect( subject => 'ICT' );
           }

           #NOTES
           {

               #This creates an iterator with a specialized SQL query:

               #SELECT * FROM DATA WHERE author = 'Nicolas'
               my $iterator = $bag->select( author => 'Nicolas' );

               #But this does not
               my $iterator2 = $iterator->select( title => "Hello world" );

               #'title' does not have a corresponding table column, so it falls back to the default implementation,
               #and loops over every record.

           }
           {

               #this is faster..
               my $iterator = $bag->select( author => 'Nicolas' )->select( title => 'Hello world');

               #..than
               my $iterator2 = $bag->select( title => 'Hello world' )->select( author => 'Nicolas' );

               #reason:

               #   the select statement of $iterator creates a specialized query, and so reduces the amount of records to loop over.
               #   $iterator is a L<Catmandu::Store::DBI::Iterator>.

               #   the select statement of $iterator2 does not have a specialized query, so it's a generic L<Catmandu::Iterator>.
               #   the second select statement of $iterator2 receives this generic object as its source, and can only loop over its records.

           }

DESCRIPTION

       Catmandu::Store::DBI::Bag provides some method overrides specific for DBI interfaces, to
       make querying more efficient.

METHODS

   store_with_table
       Equivalent to the "store" accessor, but ensures that the table for this bag exists.

   select($key => $val)
       Overrides equivalent method in Catmandu::Bag.

       Either returns a generic Catmandu::Iterator or a more efficient
       Catmandu::Store::DBI::Iterator.

       Expect the following behaviour:

       •   the key has a corresponding table column configured

           a SQL where clause is created in the background:

           .. WHERE $key = $val

           Chained select statements with existing table columns result in a combined where
           clause:

               .. WHERE $key1 = $val1 AND $key2 = $val2 ..

           The returned object is a Catmandu::Store::DBI::Iterator, instead of the generic
           Catmandu::Iterator.

       •   the key does not have a corresponding table column configured

           The returned object is a generic Catmandu::Iterator.

           This iterator can only loop over the records provided by the previous
           Catmandu::Iterable.

       A few important notes:

       •   A select statement only results in a Catmandu::Store::DBI::Iterator, when it has a
           mapped key, and the previous iterator is either a Catmandu::Store::DBI::Bag or a
           Catmandu::Store::DBI::Iterator.

       •   As soon as the returned object is a generic Catmandu::Iterator, any following select
           statement with mapped columns will not make a more efficient
           Catmandu::Store::DBI::Iterator.

       In order to make your chained statements efficient, do the following:

       •   create indexes on the table columns

       •   put select statements with mapped keys in front, and those with non mapped keys at the
           end.

       To configure table columns, see Catmandu::Store::DBI.

   detect($key => $val)
       Overrides equivalent method in Catmandu::Bag.

       Also returns first record where $key matches $val.

       Works like the select method above, but adds the SQL statement 'LIMIT 1' to the current
       SQL query in the background.

   first()
       Overrides equivalent method in Catmandu::Bag.

       Also returns first record using the current iterator.

       The parent method uses a generator, but fetches only one record.

       This method adds the SQL statement 'LIMIT 1' to the current SQL query.

   count()
       Overrides equivalent method in Catmandu::Bag.

       When the source is a Catmandu::Store::DBI::Bag, or a Catmandu::Store::DBI::Iterator, a
       specialized SQL query is created:

           SELECT COUNT(*) FROM TABLE WHERE (..)

       The select statement of the source is between the parenthesises.