Creating records for a junction table with calculated fields and testing the process

Question

I am having trouble writing a test for a piece of code I am working on. I have two tables for which I am trying to create a junction table, as below:

CREATE TABLE charges (
    id bigint PRIMARY KEY,
    amount float,
    some_data hstore
)

CREATE TABLE shipments (
    id bigint PRIMARY KEY,
    weight float,
    some_data hstore
)

CREATE TABLE distributed_details (
    charges_id bigint references charges (id),
    shipments_id biging references shipments (id),
    amount float,
    another_calculated_field varchar
)

Based on some criteria in the some_data fields, I have to pull a set of charges and a set of shipments and distribute each charges.amount across these shipments by shipments.weight. The results will be stored distributed_details. While I am doing this distribution, I also have to perform some other calculations, based on the remaining stuff in some_data, which will be stored in distributed_details.another_calculated_field.

class Distributor
{
    public $detailMapper;

    public function distribute($charges, $shipments)
    {
        foreach ($charges as $charge) {
            $distributedAmounts = $this->calculateDistributedAmounts($charge->getAmount(), $shipments);
            foreach ($shipments as $shipment) {
                $distributed_detail = $this->buildDistributedDetail($charge, $shipment, array_shift($distributedAmounts));
                $this->detailMapper->save($distributed_detail);
            }
        }
    }

    public function calculateDistributedAmounts($shipments) {}
    public function buildDistributedDetail($charge, $shipment, $distributedAmount) {}
}

Is there a good way of testing this function, and making sure that those distributed amounts are actually pulled and assigned to each record? I have delegated the persisting of each detail to a detailMapper inside this method for reasons of memory limits - I sometimes have to pull tens of thousands of shipments, and returning all of the resulting charges in an array will run me out of memory.

score 1 · Answer 1 · answered Nov 26 '13 at 06:07

It is impossible to say without a complete answer. My preference is always to have test cases which run in a production environment safely, meaning they do not commit anything to the database permanently. While I do most of what I do in Perl, the same testing approaches should be able to work for PHP.

Abstract your database connections into a class which handles transactions for you.
Do not use autocommit where it matters, or if you do make sure this is encapsulated properly.
Create a test wrapper class you can substitute for your real connection. This has all the same functionality as your db connection and it avoids autocommit, etc. The difference is that $database->commit() is a noop and so your application only thinks it is committing changes to the db.

This allows you to write test cases for your entire logic including db queries without risking polluting your production environment with test data.

score 0 · Accepted Answer · answered Apr 11 '16 at 18:04

A bit late, but I ended up developing an appropriate solution to this problem. There were two large issues here:

Performance. Pulling tens of thousands of records into memory was not a viable way of handling this process.
Testability.

The solution is made up of multiple parts as well:

Don't load all the shipments into memory. In my particular scenario, I used a Traversable PDO Statement to step through the results one-by-one. There is the caveat that it must re-run the query a second time, but it's possible there are other ways to reset the pointer that don't involve doing this.
Separate the calculations needed to build each junction record from the process needed to pull them. By walking across the shipments and distributing amounts with an implementation similar to Martin Fowler's Quantity pattern, we can test that step separately as well.
Finally, separate persisting the records to your persistence pattern of choice (Data Mapper, Gateway, etc.). Using this final piece you can wrap everything and integration test it if needed.

I don't have a practical example here but this is the general idea.

Creating records for a junction table with calculated fields and testing the process

2 Answers2