Catmandu::Store::DBI::Bag - implementation of a Catmandu::Bag for DBI
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 uses $iterator as iterator, #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 its 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. }
Catmandu::Store::DBI::Bag provides some method overrides specific for DBI interfaces, to make querying more efficient.
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.
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.
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.
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.
To install Catmandu::DBI, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Catmandu::DBI
CPAN shell
perl -MCPAN -e shell install Catmandu::DBI
For more information on module installation, please visit the detailed CPAN module installation guide.