Model

Table of contents

1. Database Setup
   1. Configuration Parameters
2. Data Object Wiring
   1. Basic CRUD Operations
3. Custom Models & Complex Queries
   1. ModelContracts Methods
   2. Implementation Example
4. The DataBase Interface (DBI)

The Model (M) layer in the Puko Framework’s HMVC architecture is responsible for connecting your application to one or more databases for CRUD operations. All database operations are handled by the DataBase Interface (DBI) singleton object.

Database Setup

To simplify the connection setup, use the pukoconsole tool:

php puko setup db

Configuration Parameters

Note: You can configure additional connections as needed.

Puko stores the connection settings in config/database.php and generates corresponding PHP class models (Data Object Wiring) in the plugins/model/<schema> directory. These generated files should not be modified.

Data Object Wiring

Data object wiring is inspired by the Data Access Object (DAO) pattern. It provides an intuitive way to interact with your database columns through object properties, including full IDE auto-completion.

Assume you have a database table named inventory:

Important: Table names must only contain letters without special characters or spaces.

Basic CRUD Operations

• Create/Save:

$inventory = new plugins\model\primary\inventory();
$inventory->id = $_POST['id'];
$inventory->name = $_POST['name'];
$inventory->created = date('Y-m-d H:i:s');
$inventory->descriptions = $_POST['descriptions'];

$inventory->save();

• Read:

$inventory = new plugins\model\primary\inventory(1);
echo (array) $inventory;

• Update/Modify:

$inventory = new plugins\model\primary\inventory(1);
$inventory->name = "Updated Name";
$inventory->modify();

• Delete/Remove:

$inventory = new plugins\model\primary\inventory(1);
$inventory->remove();

• Get All Data:

$all = plugins\model\primary\inventoryContracts::GetAll();

Custom Models & Complex Queries

For more complex scenarios, such as joins or stored procedures, you can extend the generated model and implement ModelContracts.

Create a new model: model/InventoryModel.php

class InventoryModel extends inventory implements ModelContracts {
    // Implement the 9 abstract methods from ModelContracts
}

ModelContracts Methods

• GetData(): Returns all database entries in an array format.
• GetById($id): Returns a single row specified by its ID.
• IsExists($id): Returns true if a record exists for the given ID.
• IsExistsWhere($column, $value): Returns true if a record exists matching a custom condition.
• GetDataSize(): Returns the total count of records.
• GetDataSizeWhere($condition): Returns the count of records based on a specific condition.
• GetLastData(): Returns the most recently inserted record.
• SearchData($keyword): Returns search results in an array format.
• GetDataTable($condition): Returns search results formatted for DataTables JSON.

Implementation Example

public static function SearchData($keyword = []) {
    $strings = "";
    foreach ($keyword as $column => $values) {
        $strings .= sprintf(" AND (%s = '%s') ", $column, $values);
    }

    $sql = sprintf("SELECT i.id, i.created, i.name, i.descriptions
                    FROM inventory i
                    WHERE (i.created IS NOT NULL) %s;", $strings);

    return DBI::Prepare($sql)->GetData();
}

The DataBase Interface (DBI)

The DBI class offers powerful features for interacting with your database directly.

• Prepare: Passes a SQL query to the DBI class.
• GetData($params): Retrieves all data in an indexed array format. Supports prepared statements (e.g., @1, @2).
• FirstRow($params): Retrieves only the first row in a single array format.
• Run($params): Executes queries that do not return a result set (e.g., UPDATE, DELETE, or Stored Procedures).

Example with Prepared Statements:

$sql = "SELECT * FROM inventory WHERE (id = @1) AND (name = @2);";
$response = DBI::Prepare($sql)->GetData($id, $name);