The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
DBIx-StORM-0.09
++
/ DBIx::StORM::Tutorial

NAME

DBIx::StORM::Tutorial - A brief introduction to the DBIx::StORM perl extension for object-relational mapping.

TWO-MINUTE INTRODUCTION

You can establish a new connection in pretty much the same way as you create a DBI connection - just pass it standard DBI parameters:

  my $connection = DBIx::StORM->connect($dbi_dsn, $username, $password);

A connection works like a reference to a hash of of Tables:

  my $my_table = $connection->{my_table}; # Fetches my_table from the DB

A Table is a type of RecordSet. A RecordSet is an array reference you can foreach over to see what's inside:

  foreach my $record (@$my_table) {
    print "I found a row in the database: $record\n"; 
  }

Each record in the RecordSet works like a hash reference too, so you can easily extract the columns:

  foreach my $record (@$my_table) {
    my $name = $record->{name};
    print "Record $record has value $name in the name column\n"; 
  }

You can grep and sort RecordSets using perl code to get a new RecordSet (sadly you need the ugly sub keywords):

  my $bobs_results = $my_table->grep(sub { $_->{name} eq "Bob" });

  my $results_sorted_by_age = $bobs_results->sort(sub {
    $a->{age} <=> $b->{age}
  });

DESCRIPTION

DBIx::StORM is an object-relational mapper designed to provide easy database-vendor-independent access to a DBI-accessible database by allowing you to access a relational database in an object-oriented fashion.

There are many more complete ORMs available but DBIx::StORM has a different take on how interaction with the database should be achieved.

In this tutorial we'll show how DBIx::StORM can be used to quickly put together a database-driven application.

AN APPLICATION - THE ORDERING SYSTEM

We will be putting together a command-line driven system for logging and amending orders. In our model we will assume that an order is associated with a customer name and address, and that an order has zero or more order items that we will be shipping to the customer. Each order item has a product identifier and a description.

Before using this code you need to create the database tables - how to do this varies from database to database. Sample schema SQL for some databases is kept in the eg/tutorial directory of this distribution.

The full code described below (with some enhancements) is also included in this distribution as eg/tutorial/order_system.pl.

CONNECTING

If you've used DBI previously you should find connecting to your database a breeze:

  my $db = DBIx::StORM->connect("DBI:mysql:database=test")

The StORM connect method takes the same parameters as the DBI connect method does - you can provide usernames, passwords and other details exactly as if you were using DBI.

The connect call returns a StORM connection object on success ($db in the example above), or undef if there's a problem connecting.

CREATING AN ORDER

Having connected we now need to let the user create a new order. This is stored in the orders table and is automatically assigned a unique order ID by the database.

Firstly we select the table by accessing it from the connection object:

  my $table = $dbh->{orders};

At this point we have a StORM Table object, which has an insert() method. This method takes a subroutine describing of the row we wish to insert:

  my $row = $table->insert(sub {
    # Description of new row goes here, see below
  });

On success the insert() method returns a new StORM Record object, and undef if the row can't be inserted for any reason.

Now let's look at how we describe a row:

  sub {
    # Set the customer name...
    $_->{customer_name} = $customer_name;
    # ...and their address
    $_->{customer_address} = $customer_address;
  }

The subroutine you supply is called by StORM with the $_ variable set to the new Record object that is being created. You can assign to columns by setting the hash key of the same name as the column you wish to set ($_->{column_name}). As this is a perl assignment, you can set it to any valid perl expression. The following are all legal:

  $_->{name} = $first_name . " " . $last_name;
  $_->{hypotenuse} = $opposite  / sin($angle);

Let's put this all together and show how to create an order:

  # Insert a new row into the database
  my $order = $db->{orders}->insert(sub {
    # Set the customer name...
    $_->{customer_name} = $customer_name;
    # ...and their address
    $_->{customer_address} = $customer_address;
  });

The row is written to the database immediately, and any magic columns (for example, auto-incrementing columns/sequence columns) should then be filled in. So that the order can be changed in future, let's print the order number for the user. To read a value from the Record you just need to access the hash key for the column.

  print "Your order was successfully created - order id ",
    $order->{id}, "\n";

ADDING AN ORDER ITEM

Adding an item to an order is very similar. The user specifies the product identifier, description, quantity and an order ID to add it to. After some checking that the quantity is valid, we need to ensure that the order number is also valid. We do this by accessing the order.

To do this, we use the grep() method on the Table object. Like perl's grep function, this method takes a subroutine that can choose whether it wishes to accept the row. It is called* once for each Record in the table and if the subroutine returns true then the grep will "accept" that Record.

  my $order = $db->{orders}->grep(sub {
    $_->{id} == $order_id
  });

As more than one Record could potentially match grep() returns a RecordSet object. As we know we will only ever get one Record (as ID is our primary key), we can turn the RecordSet into a Record using the lookup() method on the RecordSet.

Putting this together gives us:

  # Fetch the order Record object
  my $order = $db->{orders}->grep(sub {
    $_->{id} == $order_id
  })->lookup;

Having successfully found an order we can then insert the order item into the database using the Table's insert() method as above:

  # Now insert the row for the order item
  $db->{order_items}->insert(sub {
    # It's associated with $order
    $_->{order} = $order;

    # And set the other fields
    $_->{product_id}  = $product_id;
    $_->{description} = $description;
    $_->{quantity}    = $qty;
  });

Note that we set the order column to $order. We could be explicit and use $order->{id}, but if we assign a Record to a column directly then StORM will take care of working out which value should be stored (using any foreign keys between the two tables).

* This is a bit of a lie. To call the perl code for every row would be terribly slow on large tables, so StORM works out what you perl code means and asks the database directly.

DISPLAYING AN ORDER

To display an order we first need to find the order's Record. We saw how to do this above, but for some variation let's see another, more succinct way:

  my $order = $db->{orders}->identity($order_id);

The identity() method on a table fetches a Record based on the table's primary key (which is unique to the row). In our case the order ID is also the primary key and so the identity() method can be used to fetch the order. As the primary key can only ever match one record this method returns the Record object directly. undef is returned if there is no such Record.

Now we can display information from the Record:

  print "Order ", $order->{id}, "\n";

Let's now fetch the order items that are part of this order. We can quickly access the order items by using the associated() method on the Record. It takes a table name, and using the database's foreign keys it finds all Records in this table that point to the Record. As several Records may match it returns a RecordSet:

  my $order_items = $order->associated("order_items");

For neatness we should sort these by product identifier. We can do this using the RecordSet's sort() method, which works rather like the perl function. Similarly to the grep() method we saw before the sort() method takes two Records as $a and $b and the return value lets StORM know the sort order* (see perl's sort documentation for the details of the return value):

  my $sorted_order_items = $order_items->sort(sub {
    $a->{product_id} <=> $b->{product_id}
  });

And as before we can "stack" these methods to do both at once:

  # Now get the items from the order_items table using the
  # foreign key in reverse.
  my $order_items = $order->associated("order_items")->sort(sub {
    $a->{product_id} <=> $b->{product_id}
  });

That's all well and good, but now we need to access the Records inside, and as there are potentially several Records inside we can't use lookup() again. Fortunately the solution is straighforward - if you treat a RecordSet you can foreach over it:

  foreach my $item (@$order_items) {
    # $item is a StORM record - you can do things with $item here
  }

Using the hash-style access on the Records, we get this:

  foreach my $item (@$order_items) {
    print " ", $item->{quantity}, " x ",
      $item->{product_id}, ": ",
      $item->{description}, "\n";
  }

CHANGING A QUANTITY OF AN ORDER ITEM

To let the user change the quantity of an order item, we need the user to specify an order ID and a product identifier. We can then use the grep() method on the order items Table to fetch the Record:

  my $order_item = $db->{order_items}->grep(sub {
    $_->{product_id} == $product_id and
    $_->{order} == $order_id
  })->lookup;

The final lookup() coerces the RecordSet into a Record as before.

Changing the Record is rather like reading, except we assign to the hash key this time:

  $order_item->{quantity} = $new_qty;

The change is written to the database immediately (if you want to make a lot of changes at once you may wish to change this using the autocommit method).

This is handy for changing one Record, but it is possible to modify an entire RecordSet at once - see the RecordSet documentation for the update() method.

DELETING AN ORDER ITEM

In case a customer changes their mind we need to be able to remove an item from an order. Like changing, above, we will take an order ID and a product identifer and fetch the order_item Record:

  my $order_item = $db->{order_items}->grep(sub {
    $_->{product_id} == $product_id and
    $_->{order} == $order_id
  })->lookup;

Then we call the delete() method on the Record:

  $order_item->delete;

Again, the action happens immediately.

If you wish to delete lots of Records at once then see the RecordSet documentation for the delete() method.

THAT'S ALL FOLKS

This tutorial has hopefully whetted your appetite and shown how you can use StORM for creating, reading, updating and deleting rows in your database.

There are more methods available than covered here. In particular the DBIx::StORM::Record and DBIx::StORM::RecordSet classes cover much of the everyday data manipulation.

If you require a more application-centric approach to database access you can integrate StORM more tightly using DBIx::StORM::Class and DBIx::StORM::Inflater.

AUTHOR

Luke Ross, <luke@lukeross.name>

You can also join the StORM mailing list by sending an email to mailto:storm-devel-subscribe@lukeross.name.

COPYRIGHT AND LICENSE

Copyright (C) 2006-2008 by Luke Ross

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.6.0 or, at your option, any later version of Perl 5 you may have available.