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.