database-repository

a-jay85 的头像作者 a-jay85

使用BaseMysqliRepository和预编译语句的数据库仓库模式,适用于IBL5。在创建仓库、编写数据库查询或扩展BaseMysqliRepository时使用。

📖 中文导读:使用BaseMysqliRepository和预编译语句的数据库仓库模式,适用于IBL5。在创建仓库、编写数据库查询或扩展BaseMysqliRepository时使用。 以下正文为该技能的原始 SKILL.md 内容(英文),上方安装命令可直接使用。

name: database-repository description: Database repository pattern using BaseMysqliRepository with prepared statements for IBL5. Use when creating repositories, writing database queries, or extending BaseMysqliRepository.

Database Repository Pattern

All IBL repositories extend BaseMysqliRepository for standardized prepared statements.

BaseMysqliRepository Methods

executeQuery($query, $types, ...$params)

Execute prepared statement, returns statement object.

$stmt = $this->executeQuery(
    "SELECT * FROM ibl_plr WHERE tid = ? AND age <= ?",
    "ii",
    $teamId,
    $maxAge
);
$result = $stmt->get_result();
$stmt->close();

fetchOne($query, $types, ...$params)

Single row as associative array (or null).

return $this->fetchOne(
    "SELECT * FROM ibl_plr WHERE pid = ? LIMIT 1",
    "i",
    $playerId
);

fetchAll($query, $types, ...$params)

All rows as array of associative arrays.

return $this->fetchAll(
    "SELECT * FROM ibl_plr WHERE teamname = ? ORDER BY ordinal",
    "s",
    $teamName
);

execute($query, $types, ...$params)

INSERT/UPDATE/DELETE, returns affected rows.

return $this->execute(
    "UPDATE ibl_plr SET teamname = ? WHERE pid = ?",
    "si",
    $newTeam,
    $playerId
);

getLastInsertId()

Get auto-increment ID after INSERT.

$this->execute("INSERT INTO table (name) VALUES (?)", "s", $name);
return $this->getLastInsertId();

Type Specification String

Char Type Use For
i integer INT, SMALLINT, BIGINT, DATE as Unix timestamp
s string VARCHAR, TEXT, CHAR
d double FLOAT, DOUBLE, DECIMAL
b blob BLOB, BINARY (rare)

Error Codes

Code Meaning
1001 Type/parameter count mismatch
1002 Prepare failed (invalid SQL)
1003 Execute failed (constraint violation)

Repository Template

<?php

declare(strict_types=1);

namespace ModuleName;

use ModuleName\Contracts\ModuleRepositoryInterface;

class ModuleRepository extends \BaseMysqliRepository implements ModuleRepositoryInterface
{
    /**
     * @see ModuleRepositoryInterface::findById()
     */
    public function findById(int $id): ?array
    {
        return $this->fetchOne(
            "SELECT * FROM ibl_table WHERE id = ? LIMIT 1",
            "i",
            $id
        );
    }

    /**
     * @see ModuleRepositoryInterface::findByTeam()
     */
    public function findByTeam(int $teamId): array
    {
        return $this->fetchAll(
            "SELECT * FROM ibl_table WHERE tid = ? ORDER BY ordinal",
            "i",
            $teamId
        );
    }

    /**
     * @see ModuleRepositoryInterface::update()
     */
    public function update(int $id, string $value): int
    {
        return $this->execute(
            "UPDATE ibl_table SET column = ? WHERE id = ?",
            "si",
            $value,
            $id
        );
    }
}

NULL Handling

mysqli's bind_param() has no NULL type. Build conditional queries:

if ($value === null) {
    return $this->fetchAll("SELECT * FROM t WHERE col IS NULL");
} else {
    return $this->fetchAll("SELECT * FROM t WHERE col = ?", "s", $value);
}

Date/DateTime Handling

Bind as Unix timestamps with i type:

$this->fetchOne(
    "SELECT * FROM games WHERE date > FROM_UNIXTIME(?)",
    "i",
    time()
);

Templates

See templates/CustomRepository.php for starter template.