Catman...BI::Bag(3pm)

✨ NAME ✨

okay bestie, welcome to the Catmandu::Store::DBI::Bag(3pm) manual 💖 This page is about Catmandu::Store::DBI::Bag — implementation of a Catmandu::Bag for DBI. It's giving database-bag main character energy, no cap. 🫶

---

🌸 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 💖

So basically, Catmandu::Store::DBI::Bag provides some method overrides specific for DBI interfaces, to make querying more efficient. Efficiency queen behavior, honestly. 👑

---

👑 METHODS 👑

🎀 store_with_table 🎀

Equivalent to the "store" accessor, but ensures that the table for this bag exists. Like, it checks that the table is actually there before you start doing the most. No table? Not on her watch. ✨

🔥 select($key => $val) 🔥

Overrides the 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 the 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. Sneaky little limit, but make it fashion. 💅

🦋 first() 🦋

Overrides the 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 the 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.

---

okay that's the whole vibe, bestie. signing off from perl v5.38.2, dated 2024-03-24, from the Catmandu::Store::DBI::Bag(3pm) manual. mwah 💋✨