grifart/tables

A simple library to access and manipulate database records. Built on top of Dibi and hardwired for PostgreSQL.

This library is developed at gitlab.grifart.cz/grifart/tables and distributed using github.com/grifart/tables. GitLab repository is automatically mirrored to GitHub for all protected branches and tags. Development branches can be found only at GitLab.

Installation

composer require grifart/tables

Quick start

Register the tables DI extension. Tables expect that an instance of Dibi is also configured and registered in the container.
```
extensions:
    tables: Grifart\Tables\DI\TablesExtension
```

Create a database table. You can use your favourite database migration tool.

CREATE TABLE "article" (
  "id" uuid NOT NULL PRIMARY KEY,
  "title" varchar NOT NULL,
  "text" text NOT NULL,
  "createdAt" timestamp without time zone NOT NULL,
  "deletedAt" timestamp without time zone DEFAULT NULL,
  "published" boolean NOT NULL
);

Create a definition file for scaffolder. Tables expose a helper that creates all necessary class definitions for you:
```
<?php

use Grifart\Tables\Scaffolding\TablesDefinitions;

// create a DI container, the same way as you do in your application's bootstrap.php, e.g.
$container = App\Bootstrap::boot();

// grab the definitions factory from the container
$tablesDefinitions = $container->getByType(TablesDefinitions::class);

return $tablesDefinitions->for(
    'public', // table schema
    'article', // table name
    ArticleRow::class,
    ArticleChangeSet::class,
    ArticlesTable::class,
    ArticlePrimaryKey::class,
);
```
Once you run scaffolder, it will inspect the database schema and generate a set of four classes:
- ArticlesTable, a service that provides API for accessing and manipulating data in the article table;
- ArticleRow, a simple DTO that wraps a single row from the article table;
- ArticleChangeSet, a mutable wrapper over data to be persisted in the article table,
- ArticlePrimaryKey, a representation of the article table's primary key.
Register the ArticlesTable in your DI container.

services:
    - ArticlesTable

Usage

Use dependency injection to retrieve an instance of the ArticlesTable service in your model layer. The table class exposes the following methods:

Read

You can list all records in the table by calling the getAll() method. The method optionally accepts sorting criteria and a paginator (more on both below).

$rows = $table->getAll($orderBy, $paginator);

To fetch a specific record from the table, use either the find() or get() method with the desired record's primary key. The difference is that find() returns null if the query yields empty result, whereas get() throws an exception in such case:

$row = $table->find(ArticlePrimaryKey::of($articleId));
// or
$row = $table->get(ArticlePrimaryKey::of($articleId));

To retrieve a list of records that match given criteria, you can use the findBy() method and pass a set of conditions to it (more on that below):

$rows = $table->findBy($conditions, $orderBy, $paginator);

There are also two pairs of helper methods to retrieve a single record that matches given criteria: getUniqueBy() and findUniqueBy() look for a unique record and throw an exception when the query yields more than one result. In addition, getUniqueBy() fails if no record is found, whereas findUniqueBy() returns null in such case.

$row = $table->getUniqueBy($conditions);
$rowOrNull = $table->findUniqueBy($conditions);

And getFirstBy() and findFirstBy() return the first record that matches given criteria, regardless of whether there are more of them in the table.

$row = $table->getFirstBy($conditions, $orderBy);
$rowOrNull = $table->findFirstBy($conditions, $orderBy);

If you need to only get a count of records without having to fetch them from the database, you can use the count() method which optionally accepts a set of conditions:

$totalCount = $table->count();
$matchingCount = $table->count($conditions);

Conditions

When it comes to search criteria, the table expects a Condition (or a list thereof). This is how a simple search for published articles might look like:

$rows = $table->findBy(
    Composite::and(
        $table->published->is(equalTo(true)),
        $table->createdAt->is(lesserThanOrEqualTo(Instant::now())),
    ),
);

The code above could be simplified to a list of conditions – if a list is passed, the and relationship is assumed implicitly:

$rows = $table->findBy([
    $table->published->is(equalTo(true)),
    $table->createdAt->is(lesserThanOrEqualTo(Instant::now())),
]);

Also, the is() method defaults to equality check, so you can omit the equalTo() and pass the value directly:

$rows = $table->findBy([
    $table->published->is(true),
    $table->createdAt->is(lesserThanOrEqualTo(Instant::now())),
]);

This package provides a Composite condition that lets you compose the most complex trees of boolean logic together, and a set of most common conditions such as equality, comparison, and null-checks. For a complete list, look into the Conditions/functions.php file.

In addition to these, you can also write your own conditions by implementing the Condition interface. It defines the sole method toSql() which is expected to return a Dibi expression.

Take a look at how a LIKE condition could be implemented. It maps to a LIKE database operation with two operands – a sub-expression (more on that below), and a pattern mapped to a database text:

use Grifart\Tables\Expression;
use Grifart\Tables\Types\TextType;

final class IsLike implements Condition
{
	/**
	 * @param Expression<string> $expression
	 */
	public function __construct(
		private Expression $expression,
		private string $pattern,
	) {}

	public function toSql(): \Dibi\Expression
	{
		return new \Dibi\Expression(
			'? LIKE ?',
			$this->expression->toSql(),
			TextType::varchar()->toDatabase($this->pattern),
		);
	}
}

You can then use the condition like this:

$rows = $table->findBy([
    new IsLike($table->title, 'Top 10%'),
]);

Or create a factory function:

function like(string $pattern) {
    return static fn(Expression $expression) => new IsLike($expression, $pattern);
}

And then use it like this:

$rows = $table->findBy([
    $table->title->is(like('Top 10%')),
]);

Expressions

Expressions are an abstraction over database expressions. All table columns are expressions and as you've seen, the generated ArticlesTable exposes each of them via an aptly named property.

You can also create custom expressions that map to various database functions and operations. You just need to implement the Expression interface which requires you to specify the SQL representation of the expression, and also its type (used for formatting values in conditions):

use Grifart\Tables\Expression;
use Grifart\Tables\Types\IntType;
use Grifart\Tables\Type;

/**
 * @implements Expression<int>
 */
final class Year implements Expression
{
    /**
     * @param Expression<\Brick\DateTime\Instant>|Expression<\Brick\DateTime\LocalDate> $sub
    */
    public function __construct(
        private Expression $sub,
    ) {}

    public function toSql(): \Dibi\Expression
    {
        return new \Dibi\Expression(
            "EXTRACT ('year' FROM ?)",
            $this->sub->toSql(),
        );
    }

    public function getType(): Type
    {
        return IntType::integer();
    }
}

Alternatively, you can extend the ExpressionWithShorthands base class:

/**
 * @extends ExpressionWithShorthands<int>
 */
final class Year extends ExpressionWithShorthands
{
    // ...
}

That way, the convenient is() shorthand will be available on the expression instance:

$rows = $table->findBy(
    (new Year($table->createdAt))->is(equalTo(2021)),
);

Anonymous expressions

You can also use the expr() function to create such expression in place:

$year = fn(Expression $expr) => expr(IntType::integer(), "EXTRACT ('year' FROM ?)", $expr);
$rows = $table->findBy(
    $year($table->createdAt)->is(equalTo(2021)),
);

Ordering

To specify the desired order of records, you can provide a list of sorting criteria. This uses the same expression mechanism as filtering. You can use the ExpressionWithShorthands's methods ascending() and descending():

$rows = $table->getAll(orderBy: [
    $table->createdAt->descending(),
    $table->title, // ->ascending() is the default
]);

Pagination

The getAll and findBy methods also optionally accept an instance of Nette\Utils\Paginator. If you provide it, the table will not only set the correct limit and offset, but also query the database for the total number of items, and update the paginator with that value.

$paginator = new \Nette\Utils\Paginator();
$paginator->setItemsPerPage(20);
$paginator->setPage(2);

$rows = $table->getAll($orderBy, $paginator);

Insert

To insert a new record into the database table, use the $table->insert() method. You have to provide all required values (for columns without a default value) to the method:

$table->insert(
    id: \Ramsey\Uuid\Uuid::uuid4(),
    title: 'Title of the post',
    text: 'Postt text',
    createdAt: \Brick\DateTime\Instant::now(),
    publish

Tables

Install / Use

README