Scroll to navigation

Catmandu::Store::DBI::Bag(3pm) User Contributed Perl Documentation Catmandu::Store::DBI::Bag(3pm)

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.

2023-02-02 perl v5.36.0