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();
}

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.

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

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