Festi ObjectDB

Festi ObjectDB is a database abstraction layer for the Festi Framework, designed to provide a unified interface for working with different database engines. It simplifies database interactions by supporting multiple adapters such as PDO, Cassandra, HandlerSocket, and WPDB. This allows developers to switch between databases without modifying application logic.

Installation

To install Festi ObjectDB using Composer, add the following to your composer.json file:

{
    "repositories": [
        { "type": "composer", "url": "https://packages.festi.io/" }
    ],

    "require": {
       "festi-team/festi-framework-database": "dev-develop"
    }
}

Usage

PDO

$db = new PDO(
    $GLOBALS['config']['db']['dsn'],
    $GLOBALS['config']['db']['user'],
    $GLOBALS['config']['db']['pass']
);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); 
$db->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_EMPTY_STRING); 
$db->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL); 

$res = $db->query('SET NAMES utf8');

if (!$res) {
    throw new Exception('Database connection error');
}

$db = DataAccessObject::factory($db);

Wordpress

$db = DataAccessObject::factory($GLOBALS['wpdb']);

Cassandra

$cluster = Cassandra::cluster()->
                    withContactPoints('IP')->
                    withCredentials("UserName", "Password")->
                    build();

$session  = $cluster->connect('KeySpace');  

$db = DataAccessObject::factory($session);

$sql = "SELECT * FROM sensors_data";
$result = $db->getCol($sql);

ORM

/**
 * Class CandidateValuesObject
 * @table(name:"candidates")
 */
class CandidateValuesObject extends ValuesObject
{
    /**
     * @primaryKey
     * @column
     * @return int|null
     * @throws SystemException
     */
    public function getID(): ?int
    {
        return $this->get('id');
    } // end getID

    /**
     * @param string $name
     * @column(name:"lastname")
     * @null
     */
    public function setLastName(string $name): void
    {
        $this->set('last_name', $name);
    } // end setLastName

    /**
     * @oneToMany(foreignKey:"id_candidate", id:"id", entity:"CandidateEducationValuesObject")
     * @param CandidateEducationValuesObject[] $items
     */
    public function setEducations(array $items): void
    {
        $this->_educations = $items;
    } // end setEducations
}

ORM PHP8

#[Table("scraper_actions")]
class ActionValuesObject
{
    private ?int $id;
    private string $caption;
    private array $items = array();

    #[Column]
    #[PrimaryKey]
    public function setID(?int $id): void
    {
        $this->id = $id;
    }

    public function getID(): ?int
    {
        return $this->id;
    }

    #[Column("title")]
    #[Nullable]
    public function setCaption(string $caption): void
    {
        $this->caption = $caption;
    }

    public function getCaption(): string
    {
        return $this->caption;
    }

    #[OneToMany("id", "id_action", ActionItem::class)]
    public function setItems(array $items): void
    {
        $this->items = $items;
    }

    public function getItems(): array
    {
        return $this->items;
    }
}

#[Table("scraper_action_items")]
class ActionItem
{
    private ?int $id;
    private string $caption;
    private int $actionId;

    #[Column]
    #[PrimaryKey]
    public function setID(?int $id): void
    {
        $this->id = $id;
    }

    public function getID(): ?int
    {
        return $this->id;
    }

    #[Column("title")]
    #[Nullable]
    public function setCaption(string $caption): void
    {
        $this->caption = $caption;
    }

    public function getCaption(): string
    {
        return $this->caption;
    }

    public function setActionID(int $id): void
    {
        $this->actionId = $id;
    }

    public function getActionID(): int
    {
        return $this->actionId;
    }
}

$action = new ActionValuesObject();

$action->setID(null);
$action->setCaption("Test");

$manager = EntityManager::createInstance($connection, new AttributesEntityParser());

$manager->sync($action);

Driver

A driver can function as an adapter for database-specific APIs, such as those required for creating tables, executing queries, and utilizing syntax specific to the database being used.

Create Table

This code is intended as a proof-of-concept and should not be used in production environments. It can be used for unit testing purposes, and provides a safe way to create a table in a database with support for different database engines.

$table = new Table();
$table->setName('test_table');

$primaryKey = new Column('id');
$primaryKey->setPrimaryKey(true);

$captionColumn = new Column('caption');
$captionColumn->setNullable(true);

$table->addColumn($primaryKey);
$table->addColumn($captionColumn);

$query = $driver->createTableQuery($table);

DataAccessObject

In most cases, you will need to create a DataAccessObject that follows the Repository Pattern. This helps encapsulate database access logic in a structured way.

Example implementation:

class ScraperObject extends DataAccessObject
{
    const ACTIONS_TABLE_NAME = "scraper_actions";

    public function changeActions(array $values, array $search): int
    {
        return $this->update(static::ACTIONS_TABLE_NAME, $values, $search);
    }

    public function removeActions(array $search): void
    {
        $this->delete(static::ACTIONS_TABLE_NAME, $search);
    }

    public function getByID(int $id): ?ActionValuesObject
    {
        $search = ['id' => $id];
        $sql = "SELECT * FROM ".static::ACTIONS_TABLE_NAME;

        $data = $this->select($sql, $search, null, static::FETCH_ROW);
        if (!$data) {
            return null;
        }
        return new ActionValuesObject($data);
    }
}

You can create an instance of the ScraperObject using:

$scraperObject = DataAccessObject::create(ScraperObject::class);

Alternatively, if a database connection or path is needed:

$scraperObject = DataAccessObject::getInstance(ScraperObject::class, $connection, $path);

Handling Errors

Festi ObjectDB provides exception handling through DatabaseException:

try {
    $db->query("INVALID SQL QUERY");
} catch (DatabaseException $e) {
    echo "Database error: " . $e->getMessage();
}

Testing

Festi ObjectDB provides mock classes for unit testing that allow you to test database-dependent code without requiring a real database connection.

ConfigurableDataAccessObjectMock

ConfigurableDataAccessObjectMock is a configurable mock implementation of IDataAccessObject that uses callbacks to intercept method calls. This allows you to define custom behavior for database operations in your tests.

Basic Usage

use Database\Tests\Mock\ConfigurableDataAccessObjectMock;

$mock = new ConfigurableDataAccessObjectMock();
$mock->onGetRow(fn($sql) => ['id' => 1, 'name' => 'test']);
$mock->onQuery(fn($sql) => true);

Setting Up in Tests

use Database\Tests\Mock\ConfigurableDataAccessObjectMock;

class MyTest extends TestCase
{
    protected function setUp(): void
    {
        parent::setUp();

        // Replace database connection with mock
        $this->core->db = new ConfigurableDataAccessObjectMock();
    }
}

Configuring Method Behavior

The mock provides convenience methods for all IDataAccessObject methods using the on* prefix:

$mock = new ConfigurableDataAccessObjectMock();

// Configure getRow to return specific data
$mock->onGetRow(function($sql) {
    if (strpos($sql, 'users') !== false) {
        return ['id' => 1, 'username' => 'admin'];
    }
    return [];
});

// Configure getAll to return multiple rows
$mock->onGetAll(fn($sql) => [
    ['id' => 1, 'name' => 'Item 1'],
    ['id' => 2, 'name' => 'Item 2']
]);

// Configure getOne to return a single value
$mock->onGetOne(fn($sql) => 42);

// Configure insert to return the insert ID
$mock->onInsert(fn($table, $values) => 123);

// Configure update to return affected rows
$mock->onUpdate(fn($table, $values, $condition) => 1);

// Configure select with conditions
$mock->onSelect(function($sql, $condition, $orderBy, $type) {
    if (isset($condition['id']) && $condition['id'] === 1) {
        return ['id' => 1, 'name' => 'test'];
    }
    return [];
});

Available Callback Methods

onGetRow(callable) - Intercept getRow() calls
onGetAll(callable) - Intercept getAll() calls
onGetOne(callable) - Intercept getOne() calls
onGetAssoc(callable) - Intercept getAssoc() calls
onGetCol(callable) - Intercept getCol() calls
onQuery(callable) - Intercept query() calls
onInsert(callable) - Intercept insert() calls
onUpdate(callable) - Intercept update() calls
onDelete(callable) - Intercept delete() calls
onMassInsert(callable) - Intercept massInsert() calls
onGetInsertID(callable) - Intercept getInsertID() calls
onSelect(callable) - Intercept select() calls
onInTransaction(callable) - Intercept inTransaction() calls
onBegin(callable) - Intercept begin() calls
onCommit(callable) - Intercept commit() calls
onRollback(callable) - Intercept rollback() calls
onGetDriver(callable) - Intercept getDriver() calls

Generic Interception

You can also use the generic intercept() method for any method:

$mock->intercept('customMethod', fn($arg) => 'result');

Custom Driver Mock

You can set a custom driver mock instance:

use Database\Tests\Mock\ObjectDriverMock;

$driverMock = new ObjectDriverMock();
$mock->setDriver($driverMock);

Default Behavior

If no callback is registered for a method, the mock returns sensible defaults: - getRow() returns empty array [] - getAll() returns empty array [] - getOne() returns null - query() returns true - insert() returns 1 - update() returns true - delete() returns true - getInsertID() returns 1 - select() returns empty array [] - Transaction methods return true - getTables() returns empty array []

ObjectDriverMock

ObjectDriverMock is a minimal mock implementation of IObjectDriver that provides stub implementations with sensible defaults. It's useful when you need a driver mock but don't need to verify driver behavior.

use Database\Tests\Mock\ObjectDriverMock;

$driverMock = new ObjectDriverMock();
$driverMock->quoteTableName('users'); // Returns `users`
$driverMock->quoteColumnName('id');   // Returns `id`

The mock implements all IObjectDriver methods with default implementations: - quoteTableName() - Returns backtick-quoted table name - quoteColumnName() - Returns backtick-quoted column name - getErrorCode() - Returns integer cast of error code - Query creation methods return empty strings - getTableIndexes() returns empty array - getColumns() returns empty array - cleanDatabase() returns true

Creating Conditions

Festi ObjectDB uses associative arrays to build database query conditions dynamically. This approach is faster, more readable, and easier to maintain than traditional ORM-based approaches.

See ./docs/SearchFilters.md for the complete reference of supported operator suffixes.

Why Use This Approach?

Performance – Directly maps to SQL queries without unnecessary abstraction layers.
Flexibility – Enables complex condition structures without requiring ORM-based query builders.
Faster Development – Reduces boilerplate code, making query writing faster.
Easier Maintenance – Keeps conditions structured and readable, reducing debugging time.
Reduced Time to Market – Allows for quick database queries without needing to learn ORM syntax.

Basic Condition Example

$search = array(
    'area' => $area
);

This generates a simple condition: WHERE area = :area.

Using Comparison Operators

$search = array(
    'id&>' => $lastID,
    'id_author&<' => $this->core->user->getID()
);

This translates to:

WHERE id > :lastID AND id_author < :userID

Using IN Conditions

$search = array(
    'id&IN' => $authorIDs
);

This translates to:

WHERE id IN (:authorIDs)

Using IS NULL/IS NOT NULL Conditions

$search = array(
    'is_initiator&IS' => null
);

This translates to:

WHERE is_initiator IS NULL

$search = array(
    'is_initiator&IS NOT' => null
);
````

This translates to:

`WHERE is_initiator IS NOT NULL`

Also, you can use `'NULL'` as value to check for NULL:
```php
$search = array(
    'is_initiator&IS' => 'NULL',
    // OR
    'is_initiator&IS NOT' => 'NULL',
);

Using `OR` Conditions

$search = array(
    ContentsObject::CONTENTS_TABLE_NAME.'.status' => ContentValuesObject::STATUS_ACTIVE,
    'sql_or' => array(
        array('types.id_type&IN' => $roleIDs),
        array('types.id_type&IS' => null)
    )
);

This translates to:

WHERE contents.status = :status AND (types.id_type IN (:roleIDs) OR types.id_type IS NULL)

Using Increment/Decrement Operations

Festi ObjectDB supports increment and decrement operations directly within update queries using the + and - operators.

$search = [
    'id' => $idUser,
];
$values = [
    'login_count+' => 5,
];
// Increment a counter by 5
$this->update('users', $values, $search);

This translates to: UPDATE users SET login_count = login_count + 5 WHERE id = :userId

$search = [
    'id' => $idUser,
];
$values = [
    'balance-' => 100,
];
// Decrement balance by 100
$this->update('accounts', $values, $search);

This translates to: UPDATE accounts SET balance = balance - 100 WHERE id = :accountId

Using Expression Class for Dynamic Queries

The Expression class allows you to create dynamic SQL expressions that can be used within conditions. This is particularly useful for subqueries and complex conditional logic.

$query = new core\dao\Query($this->connection);
$query->column(new Expression('COUNT(*)'), 'total_count');
$query->from('users');
$query->where([
    'is_active' => true,
]);
$sql = $query->getQuery();

// SELECT COUNT(*) as total_count FROM users WHERE is_active = true


$search = [
    'id' => $id,
];
$values = [
    'full_name' => new Expression("first_name || ' ' || last_name"),
];
$this->update('users', $values, $search);

// UPDATE users SET "full_name" = (first_name || ' ' || last_name) WHERE "id" = XXX

$search = [
    'status' => 'error',
    'status_backup&IS NOT' => null,
];
$values = [
    'status' => new Expression('"status_backup"'),
];
$this->update('users', $values, $search);

// UPDATE tickets
// SET status = status_backup
// WHERE status = 'error' AND status_backup IS NOT NULL;

The Expression class is ideal when you need to: - Create dynamic subqueries based on runtime conditions - Build complex EXISTS/NOT EXISTS conditions - Use subqueries within IN/NOT IN operations - Maintain cleaner, more readable code for complex SQL logic

Using `EXISTS` and `NOT EXISTS` conditions

$search = [
    '&EXISTS' => 'SELECT 1 FROM users WHERE active = 1 AND user_id = articles.author_id'
];

// Using Expression with EXISTS condition
$expression = new Expression("SELECT 1 FROM users WHERE active = 1 AND user_id = articles.author_id");

$search = [
    '&EXISTS' => $expression
];

This translates to: WHERE EXISTS (SELECT 1 FROM users WHERE active = 1 AND user_id = articles.author_id)

// Using Expression with IN condition
$subQuery = new Expression("SELECT category_id FROM user_categories WHERE user_id = :userId");

$search = [
    'category_id&IN' => $subQuery,
    'userId' => $currentUserId,
];

This translates to: WHERE category_id IN (SELECT category_id FROM user_categories WHERE user_id = :userId)

$subQuery = new core\dao\Query($this->db);
$subQuery->column('id')
         ->from('types')
         ->where([
            'id_type&IS' => null,
        ]);

$search = [
    ContentsObject::CONTENTS_TABLE_NAME.'.status' => ContentValuesObject::STATUS_ACTIVE,
    '&NOT EXISTS' => $subQuery,
];

This translates to:
`WHERE contents.status = 'active' AND contents.id_type NOT EXISTS (SELECT id FROM types WHERE id_type IS NULL)`

If you need to use multiple EXISTS or NOT EXISTS conditions, you can do it if add something before the &:


$search = [
    'one&EXISTS' => $subQuery,
    'two&EXISTS' => $subQueryTwo,
    'three&EXISTS' => $subQueryThree,
];


## FAQ

1. Change class name postfix:

define('DAO_CLASS_POSTFIX', 'DAO');


2. Change the default path to objects files

define('DAO_CLASSES_PATH', 'PATH_TO_OBJECTS_DIR'); ```

Festi ObjectDB

Installation

Usage

PDO

Wordpress

Cassandra

ORM

ORM PHP8

Driver

Create Table

DataAccessObject

Handling Errors

Testing

ConfigurableDataAccessObjectMock

Basic Usage

Setting Up in Tests

Configuring Method Behavior

Available Callback Methods

Generic Interception

Custom Driver Mock

Default Behavior

ObjectDriverMock

Creating Conditions

Why Use This Approach?

Basic Condition Example

Using Comparison Operators

Using IN Conditions

Using IS NULL/IS NOT NULL Conditions

Using OR Conditions

Using Increment/Decrement Operations

Using Expression Class for Dynamic Queries

Using EXISTS and NOT EXISTS conditions

Using `OR` Conditions

Using `EXISTS` and `NOT EXISTS` conditions