| Catmandu::Store::DBI::Bag(3pm) | User Contributed Perl Documentation | Catmandu::Store::DBI::Bag(3pm) |
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 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.
}
Catmandu::Store::DBI::Bag provides some method overrides specific for DBI interfaces, to make querying more efficient.
Equivalent to the "store" accessor, but ensures that the table for this bag exists.
Overrides equivalent method in Catmandu::Bag.
Either returns a generic Catmandu::Iterator or a more efficient Catmandu::Store::DBI::Iterator.
Expect the following behaviour:
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 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:
In order to make your chained statements efficient, do the following:
To configure table columns, see Catmandu::Store::DBI.
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.
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.
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.
| 2023-02-02 | perl v5.36.0 |