www.voj-tech.net


Data Access in vtCompose

Most applications at some point need to read and/or write data stored in some form of a database system. Application frameworks usually offer programmers a software infrastructure to access data in a relational or non-relational system. For example in .NET Framework we have ADO.NET which also serves as the foundation for Entity Framework. In vtCompose there is a set of classes which is inspired by ADO.NET allowing us to access and modify data in a relational database.

Let us go through an example of using vtCompose to access a simple database schema explaining every piece of code needed on the way. It should be noted that at the moment it is only possible to use vtCompose to access a PostgreSQL database. It is only a matter of extending and implementing a few abstract classes to add support for another popular relational database system.

SQL Schema

We shall first define a schema we will use in the example. Let us have a database of products and orders where products act as order items. A product has a name and a price. An order has a shipping address. It is possible to specify an arbitrary quantity of each product within an order.

CREATE TABLE products (
    id serial,
    name text NOT NULL,
    price numeric NOT NULL,

    PRIMARY KEY (id)
);

CREATE TABLE orders (
    id serial,
    shipping_address text NOT NULL,

    PRIMARY KEY (id)
);

CREATE TABLE order_items (
    order_id integer REFERENCES orders (id),
    product_id integer REFERENCES products (id),
    quantity integer NOT NULL DEFAULT 1,

    PRIMARY KEY (order_id, product_id)
);

The Boilerplate

The necessary boilerplate code of our PHP script consists of including the vtCompose class loader, importing the required classes and interfaces and registering the class loader. It is optional to also register the vtCompose error handler but it might be considered a good practice. Should a PHP error occur the handler will throw a VTCompose\ErrorHandling\ErrorException while respecting the error_reporting php.ini directive (more on error handling on the Error Handling page).

<?php

require_once 'VTCompose/Autoloading/Autoloader.php';

use VTCompose\Autoloading\Autoloader;
use VTCompose\Data\DataAdapter;
use VTCompose\Data\DatabaseConnection\PostgreSql\Connection;
use VTCompose\Data\DatabaseModel\Database;
use VTCompose\Data\DatabaseModel\IntegerColumn;
use VTCompose\Data\DatabaseModel\Relationship;
use VTCompose\Data\DatabaseModel\StringColumn;
use VTCompose\Data\DatabaseModel\Table;
use VTCompose\Data\DataTable;
use VTCompose\Data\ExpressionEquals;
use VTCompose\Data\ExpressionParameter;
use VTCompose\Data\IBooleanExpression;
use VTCompose\Enum;
use VTCompose\ErrorHandling\ErrorHandler;

(new Autoloader())->register();
(new ErrorHandler())->register();

Database Model in vtCompose

To be able to use the data access infrastructure provided by the framework it is necessary to create a model of the database. Every database table is represented by an instance of a class extending the VTCompose\Data\DatabaseModel\Table class. If using more than one schema in the database or when the schema qualifier is required when referencing schema objects one must define a class extending the VTCompose\Data\DatabaseModel\Schema class. Finally, the database itself is represented by an instance of a class extending the VTCompose\Data\DatabaseModel\Database class. Schema instances are attached to the Database instance and Table instances can either be attached to Schema instances or, when the schema distinction is not necessary, to the Database instance.

A set of columns needs to be defined when creating a table. There are a few parameters a column constructor takes. These are the Table instance, a unique identifier of the column within the table, the column name, an optional boolean flag specifying whether the column allows NULL values and an optional boolean flag specifying whether the column is the primary key, or a part of a composite primary key for that matter.

Furthermore a set of so-called parent relationships needs to be defined for every table. As you can guess a parent relationship represents a foreign key in the database. A parent relationship will be defined in a table if the table references another table in the database.

As you can see in the code snippet below we use the VTCompose\Enum class as a base class when defining classes to hold our identifier constants. This approach is not required however it is a common practice in vtCompose. The Enum base class provides a few static methods which allow us to treat our class as if it was an enumeration available in other languages such as C#. It is beyond the scope of this article to talk more about the Enum class.

To represent a column of type numeric we use the VTCompose\Data\DatabaseModel\StringColumn class rather than the VTCompose\Data\DatabaseModel\FloatingPointColumn class which is also available. This is simply because a price is a value of an exact precision and we do not want to get rounding errors. Therefore instead of PHP floating point values we will get strings when we load our product prices. A higher level software component would be responsible for formatting the strings for display or for converting them to a different data type, possibly integer representing the values in whole cents.

final class TableId extends Enum {
    const PRODUCTS = 0;
    const ORDERS = 1;
    const ORDER_ITEMS = 2;
}

final class ProductsColumnId extends Enum {
    const ID = 0;
    const NAME = 1;
    const PRICE = 2;
}

final class OrdersColumnId extends Enum {
    const ID = 0;
    const SHIPPING_ADDRESS = 1;
}

final class OrderItemsColumnId extends Enum {
    const ORDER_ID = 0;
    const PRODUCT_ID = 1;
    const QUANTITY = 2;
}

final class OrderItemsParentRelationshipId extends Enum {
    const ORDER_ID = 0;
    const PRODUCT_ID = 1;
}

class ProductsTable extends Table {

    public function getName() {
        return 'products';
    }

    protected function createColumns() {
        return [
            new IntegerColumn($this, ProductsColumnId::ID, 'id', false, true),
            new StringColumn($this, ProductsColumnId::NAME, 'name', false),
            new StringColumn($this, ProductsColumnId::PRICE, 'price', false),
        ];
    }

    protected function createParentRelationships() {
        return [];
    }

}

class OrdersTable extends Table {

    public function getName() {
        return 'orders';
    }

    protected function createColumns() {
        return [
            new IntegerColumn($this, OrdersColumnId::ID, 'id', false, true),
            new StringColumn($this, OrdersColumnId::SHIPPING_ADDRESS, 'shipping_address', false),
        ];
    }

    protected function createParentRelationships() {
        return [];
    }

}

class OrderItemsTable extends Table {

    public function getName() {
        return 'order_items';
    }

    protected function createColumns() {
        return [
            new IntegerColumn($this, OrderItemsColumnId::ORDER_ID, 'order_id', false, true),
            new IntegerColumn($this, OrderItemsColumnId::PRODUCT_ID, 'product_id', false, true),
            new IntegerColumn($this, OrderItemsColumnId::QUANTITY, 'quantity', false),
        ];
    }

    protected function createParentRelationships() {
        $tables = $this->getDatabase()->getTables();

        return [
            new Relationship($this, OrderItemsParentRelationshipId::ORDER_ID, $tables[TableId::ORDERS],
                    [OrderItemsColumnId::ORDER_ID => OrdersColumnId::ID]),
            new Relationship($this, OrderItemsParentRelationshipId::PRODUCT_ID, $tables[TableId::PRODUCTS],
                    [OrderItemsColumnId::PRODUCT_ID => ProductsColumnId::ID]),
        ];
    }

}

class OrdersDatabase extends Database {
    protected function createTables() {
        return [
            new ProductsTable($this, TableId::PRODUCTS),
            new OrdersTable($this, TableId::ORDERS),
            new OrderItemsTable($this, TableId::ORDER_ITEMS),
        ];
    }
}

Finally, Some Action

We are now ready to start writing code to actually communicate with our database. First we need to instantiate our database model and a database connection. (As indicated above there is currently only a PostgreSQL connection implementation available in vtCompose.) We can then create an instance of the VTCompose\Data\DataAdapter class passing the database model and the connection as arguments to the constructor.

We set our connection string using a dedicated setter but it is also possible to pass a connection string as an argument to the constructor of the VTCompose\Data\DatabaseConnection\PostgreSql\Connection class. The effect would be the same. Finally, we open the connection. Note that we could have opened the connection before creating the DataAdapter instance. The order in which this is done does not matter.

$ordersDatabase = new OrdersDatabase();
$connection = new Connection();
$dataAdapter = new DataAdapter($connection, $ordersDatabase);

$connection->setConnectionString('dbname=orders');
$connection->open();

The first operation which we demonstrate here is to insert data into tables. This is natural as it then allows us to retrieve and modify this previously inserted data.

Let us get hold of the row collection of an instance of the VTCompose\Data\DataTable class based on the products table. Using the DataTable::newRow() method we create product rows which we subsequently populate with some hardcoded example data and add to the collection one by one. At the end we call the DataAdapter::store() method which does all the magic and inserts the rows into the actual database table. Based on the internal state of the rows the method inserts new rows rather than updates existing ones.

The row collection is an instance of the VTCompose\Data\DataRowCollection class which implements the VTCompose\Collection\IList interface and as such provides all the functionality of collections in vtCompose. See the Collections page for more on collections.

$tables = $ordersDatabase->getTables();
$productsTable = $tables[TableId::PRODUCTS];
$ordersTable = $tables[TableId::ORDERS];
$orderItemsTable = $tables[TableId::ORDER_ITEMS];

$dataTable = new DataTable($productsTable);
$dataRows = $dataTable->getRows();

$printerDataRow = $dataTable->newRow();
$printerDataRow->setItemArray([
    ProductsColumnId::NAME  => 'MagicColour Printer Home',
    ProductsColumnId::PRICE => '43.97',
]);
$dataRows->add($printerDataRow);

$cartridgeDataRow = $dataTable->newRow();
$cartridgeDataRow->setItemArray([
    ProductsColumnId::NAME  => 'MagicColour Cartridge',
    ProductsColumnId::PRICE => '9.99',
]);
$dataRows->add($cartridgeDataRow);

$usbCableDataRow = $dataTable->newRow();
$usbCableDataRow->setItemArray([
    ProductsColumnId::NAME  => 'USB Cable',
    ProductsColumnId::PRICE => '2.50',
]);
$dataRows->add($usbCableDataRow);

$dataAdapter->store($dataTable);

Next up is the orders table. Again, we create an example record in the database. The only difference here is that we use a different method of the VTCompose\Data\DataRow class to populate its instance. This time we use the DataRow::setItem() method which only allows setting one item (i.e. column value) at a time.

$dataTable = new DataTable($ordersTable);
$orderDataRow = $dataTable->newRow();
$orderDataRow->setItem(OrdersColumnId::SHIPPING_ADDRESS,
        'Mr John Smith, 132 My Street, Bigtown BG23 4YZ, England');
$dataTable->getRows()->add($orderDataRow);
$dataAdapter->store($dataTable);

In the following code snippet we insert rows into the order_items table which references the products and orders tables. The crucial point here is that we can refer back to the product and order DataRow instances for the primary key values of the records because when new rows are inserted vtCompose fetches values of primary keys (or composite primary key parts) which were supplied by defaults on the database side.

$orderId = $orderDataRow[OrdersColumnId::ID];

$dataTable = new DataTable($orderItemsTable);
$dataRows = $dataTable->getRows();

$orderItemDataRow = $dataTable->newRow();
$orderItemDataRow->setItemArray([
    OrderItemsColumnId::ORDER_ID    => $orderId,
    OrderItemsColumnId::PRODUCT_ID  => $printerDataRow[ProductsColumnId::ID],
]);
$dataRows->add($orderItemDataRow);

$orderItemDataRow = $dataTable->newRow();
$orderItemDataRow->setItemArray([
    OrderItemsColumnId::ORDER_ID    => $orderId,
    OrderItemsColumnId::PRODUCT_ID  => $cartridgeDataRow[ProductsColumnId::ID],
    OrderItemsColumnId::QUANTITY    => 2,
]);
$dataRows->add($orderItemDataRow);

$dataAdapter->store($dataTable);

We have now inserted our test data and so we can try something else. In the following piece of code we demonstrate how to count rows in a database table. It is an operation which allows specifying an optional condition. In this case the condition selects rows to count. The condition is expressed as an instance of a class implementing the VTCompose\Data\IBooleanExpression interface. In this particular example we construct a condition of type VTCompose\Data\ExpressionEquals which results in the equality comparison operator used in the generated SQL statement. SQL expressions are implemented using the composite design pattern and as such the ExpressionEquals constructor has two parameters; the left-hand side and the right-hand side expression. In order to only count rows matching our order we use the order_id column of the order_items table as the left-hand side expression and an instance of the VTCompose\Data\ExpressionParameter class as the right-hand side expression. The latter represents a parameter of the SQL statement which we can assign an arbitrary value to.

Because we will want to count rows in the order_items table and print the result later again we define a function to do it so that we avoid duplicating code.

function countAndPrintOrderItems(DataAdapter $dataAdapter, IBooleanExpression $condition) {
    $numItems = $dataAdapter->countRows(TableId::ORDER_ITEMS, $condition);
    echo "Number of distinct items in the order: $numItems\n";
}

$orderIdColumn = $orderItemsTable->getColumns()[OrderItemsColumnId::ORDER_ID];
$condition = new ExpressionEquals($orderIdColumn, new ExpressionParameter($orderId));

countAndPrintOrderItems($dataAdapter, $condition);

With our example data inserted the previous piece of code outputs the following:

Number of distinct items in the order: 2

The DataAdapter::load() method loads rows from database table(s). As was the case with the DataAdapter::countRows() method we need to specify the table to load from and, again, we have the option of specifying a condition the selected rows need to satisfy. By default the method follows all the parent relationships of the specified table and of all the tables pointed at by those parent relationships recursively and that way loads parent rows. In case a cycle in the parent relationship graph is detected the method stops expanding parent relationships beyond that point. It should be noted that the DataAdapter::countRows() method follows parent relationships as well and as such allows referencing tables pointed at by the relationships in the selection condition. Both methods take an optional parameter specifying which parent relationships to follow, if any.

By default the DataAdapter::load() method loads the values of all the columns of all the tables in the resulting data set. Which columns to load values of can be specified by an optional parameter of the method.

By default the method does not apply any specific order to the data set but it can be specified as an array of objects of types implementing the VTCompose\Data\IExpression interface.

The return type of the method is VTCompose\Data\DataSet which has a collection of data tables associated with it. This collection contains instances of the previously used DataTable class and is of type VTCompose\Data\DataTableCollection which implements the VTCompose\Collection\IDictionary interface with table identifiers used as keys. Again, for more on collections see the Collections page.

In the following code example we define a function which takes an instance of the DataAdapter class and an SQL condition as parameters, loads all the rows from the order_items table satisfying the condition, including all the associated parent rows, and prints formatted order information. (The formatting assumes only a single order information is loaded.) We then call the function with the previously used condition. Note that when we call DataRow::getParentRow() we do not execute any SQL statement, we have already loaded the parent row by that time.

function loadAndPrintOrderInfo(DataAdapter $dataAdapter, IBooleanExpression $condition) {
    $dataSet = $dataAdapter->load(TableId::ORDER_ITEMS, $condition);
    $dataTables = $dataSet->getTables();

    $orderDataRow = $dataTables[TableId::ORDERS]->getRows()->first();
    echo "Order Id: {$orderDataRow[OrdersColumnId::ID]}\n";
    echo "Shipping Address: {$orderDataRow[OrdersColumnId::SHIPPING_ADDRESS]}\n\n";

    foreach ($dataTables[TableId::ORDER_ITEMS]->getRows() as $orderItemDataRow) {
        $productDataRow = $orderItemDataRow->getParentRow(OrderItemsParentRelationshipId::PRODUCT_ID);

        echo "Order Item Product Id: {$orderItemDataRow[OrderItemsColumnId::PRODUCT_ID]}\n";
        echo "Order Item Product Name: {$productDataRow[ProductsColumnId::NAME]}\n";
        echo "Order Item Quantity: {$orderItemDataRow[OrderItemsColumnId::QUANTITY]}\n\n";
    }

    return $dataSet;
}

$dataSet = loadAndPrintOrderInfo($dataAdapter, $condition);

Assuming there were no data in our database prior to running the script and the primary key sequence counter values were set to 0 the output is:

Order Id: 1
Shipping Address: Mr John Smith, 132 My Street, Bigtown BG23 4YZ, England

Order Item Product Id: 1
Order Item Product Name: MagicColour Printer Home
Order Item Quantity: 1

Order Item Product Id: 2
Order Item Product Name: MagicColour Cartridge
Order Item Quantity: 2

In the following piece of code we take the previously acquired data set and use the DataRow::setItem() method to change the product associated with an order item as well as the quantity of the item. By calling the DataAdapter::store() method passing the data set as an argument we update the relevant row in the order_items table. We then reload the data set printing the order information along the way again. Note that we need to specify which table to update or insert to when calling the DataAdapter::store() method and passing a whole data set.

$orderItemsDataRow = $dataSet->getTables()[TableId::ORDER_ITEMS]->getRows()[1];
$orderItemsDataRow->setItem(OrderItemsColumnId::PRODUCT_ID, $usbCableDataRow[ProductsColumnId::ID]);
$orderItemsDataRow->setItem(OrderItemsColumnId::QUANTITY, 1);
$dataAdapter->store($dataSet, TableId::ORDER_ITEMS);

$dataSet = loadAndPrintOrderInfo($dataAdapter, $condition);

After our editing the order looks like this:

Order Id: 1
Shipping Address: Mr John Smith, 132 My Street, Bigtown BG23 4YZ, England

Order Item Product Id: 1
Order Item Product Name: MagicColour Printer Home
Order Item Quantity: 1

Order Item Product Id: 3
Order Item Product Name: USB Cable
Order Item Quantity: 1

To delete a row from a table use the DataRow::delete() method and then call the DataAdapter::store() method. Let us call the countAndPrintOrderItems() function after we delete an order item to see how many items there are left.

$dataSet->getTables()[TableId::ORDER_ITEMS]->getRows()[1]->delete();
$dataAdapter->store($dataSet, TableId::ORDER_ITEMS);

countAndPrintOrderItems($dataAdapter, $condition);

Only a single item is left in the order as expected.

Number of distinct items in the order: 1

It is not required to explicitly close the database connection since it is done automatically when the destructor method is called on the connection object. Should you wish to close the connection manually you can do so as in the line of code below.

$connection->close();

In this article we have covered the most typical use cases of the data handling infrastructure in vtCompose. There are other scenarios where you might want to make use of it as well. Let us save them for a discussion at another time.