Models and DAOs

Models

Extending BaseModel provides array/JSON conversion, encryption/decryption, and automatic timestamps. Declare properties as private with getters and setters; mapping is based on method names.

<?php
namespace ExampleUser;

use _Flexagon\Base\BaseModel;

class UserModel extends BaseModel {
    private ?int    $id       = null;
    private string  $name;
    private ?string $address  = null;

    public function getId(): ?int          { return $this->id; }
    public function setId(?int $id): void  { $this->id = $id; }

    public function getName(): string             { return $this->name; }
    public function setName(string $name): void   { $this->name = $name; }

    public function getAddress(): ?string             { return $this->address; }
    public function setAddress(?string $a): void      { $this->address = $a; }
}

Methods provided by BaseModel:

Method	Description
`getArray(bool $containsObject = true, bool $forceAll = false)`	Convert getters to an associative array
`setByArray(array $data)`	Populate through setters; both snake_case and camelCase keys are accepted
`getJson(int $flags = JSON_UNESCAPED_UNICODE)`	JSON serialization (nested objects are flattened to arrays)
`encrypt()` / `decrypt()`	`@encrypt` Encrypt/decrypt annotated properties
`getCreatedAt()` / `getUpdatedAt()`	Unix timestamp
`getCreatedAtDatetimeString()`	`Y-m-d H:i:s` string

Because BaseModel implements JsonSerializable, json_encode($model) works directly.

setByArray() uses reflection to reconcile common type mismatches. Empty strings become null for nullable properties, and scalar values are converted to matching cases for backed-enum properties. This makes it practical to pass form input directly into a model.

DAOs

Extend BaseMySqlDAO and specify $tableName to get the core CRUD operations.

The framework wraps $tableName in backticks (MySQL) or brackets (SQL Server), so a reserved word such as 'order' works as is. A name you have already quoted is not wrapped again: 'audit.users', '[audit].[users]' and '`app`.`users`' all produce the same statement.

<?php
namespace ExampleUser;

use _Flexagon\Base\BaseMySqlDAO;

class UserDAO extends BaseMySqlDAO {
    protected string $tableName = 'users';

    public function insert(UserModel $user): int|false { return $this->_insert($user); }
    public function update(UserModel $user): bool      { return $this->_update($user); }
    public function delete(UserModel $user): bool      { return $this->_delete($user); }

    public function findById(int $id): ?UserModel {
        return $this->_select(['id' => $id], new UserModel());
    }

    /** @return UserModel[] */
    public function findByCity(string $city, int $page = 1, int $perPage = 20): array {
        return $this->_selectList(
            new UserModel(), $page, $perPage,
            '`address` = :CITY', ['CITY' => $city],
            '`created_at` DESC'
        );
    }

    public function countByCity(string $city): int {
        return $this->_selectTotalCount('`address` = :CITY', ['CITY' => $city]);
    }
}

The CRUD methods are protected by design. Each DAO explicitly chooses which operations to expose, keeping its public interface narrow and aligned with the domain.

Method	Description
`_insert($modelOrArray)`	INSERT. Returns the auto-increment value (`false` on failure)
`_update($modelOrArray)`	UPDATE by primary key
`_updateByQuery($modelOrArray, $whereSql, $params)`	UPDATE with a custom condition
`_select($modelOrArray, ?object $vo)`	Fetch one row; returns an object when `$vo` is provided, otherwise an array
`_selectByQuery($whereSql, $params, ?object $vo)`	Fetch one row with a custom condition
`_selectList(?object $vo, $page, $perPage, $whereSql, $params, $orderSql)`	Fetch a paginated list
`_selectTotalCount($whereSql, $params)`	Count matching rows
`_delete($modelOrArray)`	DELETE by primary key
`_deleteByQuery($whereSql, $params)`	DELETE with a custom condition
`_insertAll($modelList, $chunkSize = 500)`	Bulk INSERT using one statement per chunk; returns the number of inserted rows
`_truncate()`	TRUNCATE

For PostgreSQL extend BasePgSqlDAO, for SQLite BaseSqliteDAO, and for SQL Server BaseMsSqlDAO; all provide the same method surface. Every DAO implementation extends BaseSqlDAO, where CRUD operations, schema caching, and transaction handling are shared. Subclasses contain only dialect-specific behavior: identifier quoting (`name` / "name" / [name]), schema inspection (DESCRIBE / information_schema / PRAGMA table_info), and pagination (LIMIT / LIMIT·OFFSET / OFFSET·FETCH).

PostgreSQL: a double-quoted identifier is case-sensitive, and the framework quotes $tableName. Write it as the table is stored, normally lower case ('users', not 'Users'). A bare name resolves through search_path; use 'audit.users' for another schema.

SQLite: the data source is a file, created with DataSourceModel::file().
_Global::$DATA_SOURCES['default'] = DataSourceModel::file(PROJECT_ROOT . '/var/app.sqlite');
The directory must exist; a wrong path fails to connect instead of creating an empty database. INTEGER PRIMARY KEY is the auto-increment column, _truncate() runs DELETE, and foreign keys are enforced. SQLite locks the whole file for writes, so for a site with concurrent writers turn on WAL once (PRAGMA journal_mode = WAL).

When you need direct SQL, use $this->db (a Mysql instance).

public function findTopSpenders(int $limit): array {
    $this->db->executeQuery(
        'SELECT u.* FROM users u JOIN orders o ON o.user_id = u.id
         GROUP BY u.id ORDER BY SUM(o.amount) DESC LIMIT :LIMIT',
        ['LIMIT' => $limit]
    );
    return $this->db->getAllResultAsObject(new UserModel());
}

Column ↔ Property Mapping

Database snake_case columns are mapped automatically to PHP camelCase properties.

DB column	Getter / Setter	Property
`id`	`getId()` / `setId()`	`$id`
`user_name`	`getUserName()` / `setUserName()`	`$userName`
`created_at`	`getCreatedAt()` / `setCreatedAt()`	`$createdAt`

Parameter Binding

Queries use PDO prepared statements with named placeholders. Framework-generated queries use the uppercase column name as the placeholder (user_name → :USER_NAME).

If the same placeholder appears multiple times in a query, SqlUtil::expandQueryParams() automatically expands it into distinct parameters, avoiding PDO's duplicate-binding limitation.

$this->_selectList(new UserModel(), 1, 20,
    '`name` LIKE :KEYWORD OR `address` LIKE :KEYWORD',   // may appear more than once
    ['KEYWORD' => "%{$keyword}%"]
);

Debugging

echo $userDAO->debugQuery();   // actual SQL with parameters substituted
echo $userDAO->getError();     // last error message
$userDAO->debugDumpParams();   // PDOStatement::debugDumpParams()

When _Global::$DEBUG_QUERY_BREAKPOINT is enabled, Mysql::debugQueryAfter() is called after every query. The method is intentionally empty, making it a convenient IDE breakpoint for inspecting executed queries.

Models#

DAOs#

Column ↔ Property Mapping#

Parameter Binding#

Debugging#

Models

DAOs

Column ↔ Property Mapping

Parameter Binding

Debugging