La séptima vida

...o el gato así lo espera/teme

Introduction to DBIx::Mint

DBIx::Mint, just like other object-relational mappers, allows you to work with a database through an object-oriented interface. Database records become objects, and database tables become classes. Relations between tables are mapped into methods that manipulate objects of the respective classes.

This is the first of a series of articles that describe the usage of DBIx::Mint. I hope they will be a good companion to the documentation, which is written as a reference and thus it might not describe properly how the different pieces of Mint work together. The series will build on an example application, a small database to keep the inventory of parts for a fictional business.

The example: nanoERP

Imagine we have a garage workshop where we build electronic devices to sell over the internet. We just got an order from one of our customers! Let's print the list of components needed to build the item we just sold. Now, we go to the component bins and fill a basket with the resistors, capacitors, diodes, and all the other components, and update their respective quantities in our system. At the end of the day, we have to review our inventory and order any items we need.

We'll put all of that information in a database. We'll have a table to record information about our items, either products or components. The bill of materials, or the list of components for a product, will be a table of parent/child relationships where a product (parent) may have many components (children). The fact that a component may be used by many different products means that we have a many-to-many relationship and thus another table is needed.

We'll also keep information about our customers. Orders are really a many-to-many relationship between customers and items, since a customer may place orders for many different products, and each product can be sold to any number of clients. The same relationship exists with component suppliers, as we can buy each component from a number of vendors and each vendor may supply many of our components. Figure 1 shows an entity-relationship diagram that implements our example definition.

ER diagram for nanoERP
ER diagram for nanoERP

Setting up the database

The first thing that we need to do is to create the database. Using SQLite, the database would be created using the schema in the first appendix of this article. Let's save the schema as nanoerp.sql and then let's create the database file with sqlite3:

    $ cat nanoerp.sql | sqlite3 nanoerp.db

Up to this point, we have an empty database ready to play with. Let's make our classes!

Mapping a table

Adding DBIx::Mint to our classes is straightforward. Since DBIx::Mint is built using Moo, it makes sense to write our classes with Moo, too. Our items class is:

package NanoERP::Item;

use Moo;
with 'DBIx::Mint::Table';

use strict;
use warnings;

has reference   => (is => 'rw', required => 1);
has description => (is => 'rw');
has available   => (is => 'rw');

1;

As you can see, we have an attribute for each table column and not much more. The only thing worth noticing is that NanoERP::Item consumes the role DBIx::Mint::Table.

DBIx::Mint::Table, a role for your classes

Through the Table role, Mint adds several methods to your classes and thus implements their persistence in the database. These methods are capable of working with one class and one table only, and implement the basic functions of:

  • Creating new records
  • Saving changes into existing records
  • Retrieving records
  • Deleting records

That is, DBIx::Mint::Table implement most of the actual mapping functions. To extract information from multiple tables (using joins and actually selecting which attributes to fetch) you should use DBIx::Mint::ResultSet instead.

At this stage, Mint does not know anything about the table we want to link our NanoERP::Item class to. We must define a schema for that.

Schema definition

What information does Mint need to link a class to a table? Just the names of the class, the table, its primary key, and whether the key auto-increments or not. In SQLite, an INTEGER PRIMARY KEY is auto-incremented if it is not explicitly set. Placing our schema definition in a file of its own, the schema for the first two classes is:

package NanoERP::Schema;

use DBIx::Mint;
use strict;
use warnings;

my $s = DBIx::Mint->instance->schema;

$s->add_class(
    class   => 'NanoERP::Client',
    table   => 'clients',
    pk      => 'id',
    auto_pk => 1
);

$s->add_class(
    class   => 'NanoERP::Item',
    table   => 'items',
    pk      => 'reference',
    auto_pk => 0
);

1;

The above file is still incomplete, but it is sufficient to test our two classes. Later, we will include the other class definitions and the relationships between them. We are ready to test the basic create, read, update and delete functions, so let's connect to the database.

Connecting to the database

OK. We will write a test for our NanoERP::Client class, which is even simpler than that for the items. The class listing is in Appendix 2. We will use the test program to demonstrate some of the methods implemented by DBIx::Mint::Table; then we'll make sure our methods work as expected. The full test program is shown in Appendix 3.

The first thing we do in our test is to open a database connection. DBIx::Mint uses the normal DBI connection arguments and, for SQLite, the following incantation will work nicely:

# Connect to database
DBIx::Mint->connect('dbi:SQLite:dbname=nanoerp.db', '', '', {
    AutoCommit    => 1,
    RaiseError    => 1,
    Callbacks     => {
        connected => sub {
            my $dbh = shift;
            $dbh->do(q{ PRAGMA foreign_keys = ON  });
            $dbh->do(q{ PRAGMA encoding = 'UTF-8' });
            return;
        },
    },
});

DBIx::Mint will store the connection in a singleton, so that it is available all over your application.

Testing our newly created mapping

There are two ways to create a new object: You can use the standard new method and then call insert on the resulting object, or you can call create as a class method:

    # Test for creating a client
    my $client = NanoERP::Client->create(name => 'Endemox Corporation');
    isa_ok $client, 'NanoERP::Client';
    is     $client->name, 'Endemox Corporation',
        'Client is an object and we got its name';
    $obj_id = $client->id;

Because id is auto-incrementing, we did not supply it for the creation of our customer. It was directly assigned by the database. We saved this id value in the $obj_id variable to use it later. The above code verifies that the object was created correctly. Now let's find the new client and let's make sure it is indeed the same object:

    # Retrieve the newly created client
    my $client = NanoERP::Client->find({name => 'Endemox Corporation'});
    isa_ok $client, 'NanoERP::Client';
    is     $client->name, 'Endemox Corporation',
        'We went to the database and retrieved the client';
    is     $client->id,   $obj_id,
        'And we made sure it is the same client';

Let's now change the name of the customer:

    # Change the name of the client
    $client->name('EndeMox');
    $client->update;

Did the change work? Let's retrieve the object again and test it:

    # Fetch with new name
    my $client = NanoERP::Client->find({name => 'EndeMox'});
    isa_ok $client, 'NanoERP::Client';
    is     $client->name, 'EndeMox',
        'Retrieved the client after updating its name';
    is     $client->id,   $obj_id,
        'And we made sure it is the same client';

Finally, we delete the test client with the following instruction:

    # Clean table...
    NanoERP::Client->delete({ name => 'EndeMox' });

Or using the client object itself,

    $client->delete();

You can find the documentation for the methods composed from DBIx::Mint::Table in MetaCPAN.

Conclusion

This is the first of a set of articles about DBIx::Mint, an ORM for Perl. An ORM allows you to map the classes of your application to the tables in a database. DBIx::Mint keeps the database connection in a singleton, which makes it available wherever you use DBIx::Mint. The mapping is done by including the DBIx::Mint::Table role in your classes and by adding the relationships between tables and classes in a schema definition.

To learn more about the topics in this article, you can read the documentation for DBIx::Mint in MetaCPAN.

In subsequent articles, we will explore declaring relationships between classes, extracting information with complex database queries, and using Moo to implement database triggers.