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()): array
    {
        $sql = "SELECT * FROM users";    

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

    public function get(array $search = array()): 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].

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 = array(
    'sql_or' => array(
        array(
            'col1' => 'val1',
        ),
        array(
            '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 = array(
    'sql_and' => array(
        array(
            'sql_or' => array(
                array(
                    'col1' => 5,
                ),
                array(
                    'col1&IS' => 'NULL',
                ),
            ),
        ),
        array(
            'sql_or' => array(
                array(
                    'col2&IN' => array(1, 2, 3),
                ),
                array(
                    '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`	`array('val1', 'val2', 'val3')`	`column IN ('val1', 'val2', 'val3')`
`column&NOT IN`	`'val1, val2, val3'`	`column NOT IN ('val1', 'val2', 'val3')`
`column&NOT IN`	`array('val1', 'val2', 'val3')`	`column NOT IN ('val1', 'val2', 'val3')`
`sql_or`	`array('col1 = 5', 'col2 = 8')`	`((col1 = 5 ) OR (col2 = 8))`
`sql_or`	`array(array('col1' => 5), array('col2' => 8, 'col3' => 7))`	`((col1 = 5) OR (col2 = 8 AND col3 = 7))`
`sql_and`	`array('col1 = 5', 'col2 = 8')`	`col1 = 5 AND col2 = 8`
`sql_and`	`array(array('col1' => 5), array('col2' => 8, 'col3' => 7))`	`col1 = 5 AND col2 = 8 AND col3 = 7`
`something&or_sql`	`array('col1 = 5', 'col2 = 8')`	`(col1 = 5 OR col2 = 8)`
`col&or`	`array('val1', array('col2' => 5, 'col3' => 8))`	`(col = 'val1' OR col2 = '5' OR col3 = '8')`
`col&or&>=`	`array(7, array('col2' => 5, 'col3' => 8))`	`(col >= 7 OR col2 = '5' OR col3 = '8')`
`col&match`	`'something'`	`MATCH (col) AGAINST ('something')`
`col&between`	`array(3)`	`"col" >= '3'`
`col&between`	`array(1 => 7)`	`"col" <= '7'`
`col&between`	`array(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`