Working with Databases

Festi uses an internal ORM called ObjectDB to interact with databases efficiently.

The core principle of this ORM is that for each database entity, you create a class with an Object suffix and extend it from the DataAccessObject class. These classes encapsulate all database-related operations.

These Object classes can be placed either globally in the objects folder at the project root or within plugins. However, global placement is not recommended as it increases dependencies between plugins.

Important: Avoid placing raw SQL queries inside plugin classes. Instead, use Object classes to handle all database interactions.

Currently, provides first-party support for databases: PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, and Cassandra.

Creating a Database AccessObject

Let's create an Object class for the users table. The goal is to define a class that handles user data retrieval and updates.

Create a new file inside the objects directory or in the plugin folder:

class UsersObject extends DataAccessObject
{
}

Now, extend the class with database methods for managing users:

class UsersObject extends DataAccessObject
{
    public function search(array $search, array $orderBy = []): array
    {
        $sql = "SELECT * FROM users";    

         return $this->select($sql, $search, $orderBy);
   }

    public function get(array $search = []): array
    {
        $sql = $this->getSelectSQL($search);

        return $this->getRow($sql);
    }

    public function add(array $values): int
    {
        return $this->insert('users', $values);
    }

    public function change(array $values, array $search): int
    {
        $this->update('users', $data, $search);
    }

    protected function getSql(): string
    {
        $sql = "SELECT * FROM users";

        return $sql;
    }
}

Organizing Object Classes in a Plugin

Each plugin can define its own Object classes to encapsulate database access.

Recommended Plugin Structure

plugins/
└── Users/
    ├── domain                   # Domain classes
    ├── UsersPlugin.php          # Main plugin class
    ├── TimerObject.php          # DAO (DataAccessObject)
    ├── UsersObject.php          # DAO (DataAccessObject)    
    └── ...                      # Other plugin files

The filename must match the object name with Object suffix.
In the plugin, use $this->object->timer to access TimerObject.

Accessing the Object in a Plugin

To use the UsersObject in a plugin, instantiate it using one of the following methods:

Using the Core:
```
$this->core->getObject('Users');
```
Using the Plugin instance:
```
$this->getObject('Users');
```
Using the plugin's object manager:
```
$this->object->users;
```

Best practice: If you need an object that belongs to the current plugin, always use $this->object->[METHOD_NAME].

Overriding the DAO Layer

Every object a plugin obtains — through $this->object->users, $this->getObject('Users') or $this->getSystemObject() — is built by an IDataAccessObjectResolver. The framework ships one, CoreDataAccessObjectResolver, which asks Core exactly as before. A project that injects nothing keeps the current behaviour; overriding the resolver is entirely opt-in.

The plugin still decides which object it wants: the entity name, the plugin that owns it, and where its class file lives. The resolver only decides how that object is built.

The interface

namespace Festi\Core\Object;

interface IDataAccessObjectResolver
{
    public function getObject(
        string $name,
        string|bool $pluginName = false,
        string|false|null $path = null
    ): mixed;
}

Parameter	Meaning
`$name`	Entity name, e.g. `Users`. Already namespaced when the plugin lives in a namespace and ships a namespaced object class.
`$pluginName`	Plugin owning the entity, or `false` for framework objects.
`$path`	Directory holding the object classes. May be `false` when the plugin directory does not exist — accept it, do not type this parameter `?string`.

Replacing it

Implement the interface and inject it into the plugin before it initialises:

use Festi\Core\Object\CoreDataAccessObjectResolver;
use Festi\Core\Object\IDataAccessObjectResolver;

class ProjectObjectResolver implements IDataAccessObjectResolver
{
    private array $_objects = [];

    public function __construct(
        private CoreDataAccessObjectResolver $_default
    ) {
    }

    public function register(string $name, object $object): void
    {
        $this->_objects[$name] = $object;
    }

    public function getObject(
        string $name,
        string|bool $pluginName = false,
        string|false|null $path = null
    ): mixed
    {
        if (array_key_exists($name, $this->_objects)) {
            return $this->_objects[$name];
        }

        return $this->_default->getObject($name, $pluginName, $path);
    }
}

// plugins/MyProject/init.php
$resolver = new ProjectObjectResolver(new CoreDataAccessObjectResolver());
$resolver->register('Users', new InMemoryUsersObject());

$plugin = $this->getPluginInstance('Users');
$plugin->setDataAccessObjectResolver($resolver);

Inject before the plugin initialises. onInit() resolves the plugin's own object into $this->object, so a resolver injected afterwards will not be asked for it.

What it is good for

Testing without a database. Substitute a fake object and the plugin's logic runs with no connection at all — this is how the framework's own ObjectPluginTest covers object resolution.
A different connection for some entities, for example routing reporting entities at a read replica.
Decorating every object with logging, a read-through cache or tenant scoping, by wrapping whatever the default resolver returns.

What it does not change

DataAccessObject::getInstance() memoises instances by entity name in a process-wide static registry. A resolver that defers to the default is therefore consulted once per name per process, not once per call.
A plugin that ships no object class still resolves to null rather than an error. That decision is made before the resolver is reached, so a custom resolver is never asked for an object the plugin does not have.

Method Naming Recommendations

Search methods should start with search
Update methods should start with change
Insert methods should start with add
Use getter and setter naming conventions for clarity

Transactions

$this->object->begin();

// ...

$this->object->commit();

To rollback and undo changes:

$this->object->rollback();

Building SQL Conditions with `getSqlCondition`

Festi provides a condition builder to construct SQL WHERE clauses dynamically. Conditions are passed as an associative array and automatically converted into valid SQL expressions.

It takes an array as input, from which a WHERE SQL query expression is generated. Each element is interpreted as a part of the expression, and these parts are combined using AND. More details can be found in the ObjectDB documentation.

Example 1: Using sql_or for OR conditions

$search = [
    'sql_or' => [
        [
            'col1' => 'val1',
        ],
        [
            'col1&IS' => 'NULL',
        ],
    ],
    'col2'       => 1,
    'col3&ILIKE' => 'abc%',
];

WHERE
    ((col1 = 'val1') OR (col1 IS NULL)) AND
    col2 = '1' AND
    col3 ILIKE 'abc%'

Example 2: Nested sql_and and sql_or conditions

$search = [
    'sql_and' => [
        [
            'sql_or' => [
                [
                    'col1' => 5,
                ],
                [
                    'col1&IS' => 'NULL',
                ],
            ],
        ],
        [
            'sql_or' => [
                [
                    'col2&IN' => [1, 2, 3],
                ],
                [
                    'col2&IS' => 'NULL',
                ],
            ],
        ],
    ],

    'col3' => 1,
];

WHERE
    ((col1 = '5') OR (col1 IS NULL)) AND
    ((col2 IN ('1', '2', '3')) OR (col2 IS NULL)) AND
    col3 = '1'

Condition Operators Reference

key	value	result
-	`'column = 5'`	`column = 5`
`col&<action>`	`'item'`	`col <action> 'item'`
`column`	`5`	`column = '5'`
`column`	`null`	`column IS NULL`
`column&IN`	`'val1, val2, val3'`	`column IN ('val1', 'val2', 'val3')`
`column&IN`	`['val1', 'val2', 'val3']`	`column IN ('val1', 'val2', 'val3')`
`column&NOT IN`	`'val1, val2, val3'`	`column NOT IN ('val1', 'val2', 'val3')`
`column&NOT IN`	`['val1', 'val2', 'val3']`	`column NOT IN ('val1', 'val2', 'val3')`
`sql_or`	`['col1 = 5', 'col2 = 8']`	`((col1 = 5 ) OR (col2 = 8))`
`sql_or`	`[['col1' => 5], ['col2' => 8, 'col3' => 7]]`	`((col1 = 5) OR (col2 = 8 AND col3 = 7))`
`sql_and`	`['col1 = 5', 'col2 = 8']`	`col1 = 5 AND col2 = 8`
`sql_and`	`[['col1' => 5], ['col2' => 8, 'col3' => 7]]`	`col1 = 5 AND col2 = 8 AND col3 = 7`
`something&or_sql`	`['col1 = 5', 'col2 = 8']`	`(col1 = 5 OR col2 = 8)`
`col&or`	`['val1', ['col2' => 5, 'col3' => 8]]`	`(col = 'val1' OR col2 = '5' OR col3 = '8')`
`col&or&>=`	`[7, ['col2' => 5, 'col3' => 8]]`	`(col >= 7 OR col2 = '5' OR col3 = '8')`
`col&match`	`'something'`	`MATCH (col) AGAINST ('something')`
`col&between`	`[3]`	`"col" >= '3'`
`col&between`	`[1 => 7]`	`"col" <= '7'`
`col&between`	`[3, 7]`	`"col" BETWEEN '3' AND '7'`
`col&between`	`'3 AND 7'`	`"col" BETWEEN 3 AND 7`
`col&soundex`	`'val1'`	`SOUNDEX(col) = SOUNDEX('val1')`

In sql_or and sql_and, the getSqlCondition function is applied to the inner array.
In col&or&>=, the getSqlCondition function is applied to the second element of the value, but its elements are combined using OR.
In something&or_sql, there must be something in place of "something," but it is not taken into account.

In the table above, can be any expression. However, if value is one of the following:

NOW()
NOT NULL
NULL
CURRENT_DATE()
CURRENT_TIME()
CURRENT_DATE
CURRENT_TIME
NOW

it will not be escaped. All other value entries will be escaped.

Examples for <action>

key	value	result
`col&!=`	`7`	`col != '7'`
`column&IS`	`'null'`	`column IS NULL`
`col&IS NOT`	`'NULL'`	`col IS NOT NULL`
`col&IS`	`'NOT NULL'`	`col IS NOT NULL`
`col&>=`	`'CURRENT_TIME'`	`col >= CURRENT_TIME`