README

请求数据库（使用PDO）。支持的驱动程序：MySQL、PostgreSQL、SQLite。

安装

composer require rancoud/database

如何使用它？

数据库连接

// Create a configurator
$params = [
    'driver'    => 'mysql',
    'host'      => 'localhost',
    'user'      => 'root',
    'password'  => '',
    'database'  => 'test_database'
];
$databaseConf = new Configurator($params);

// No singleton
$database = new Database($databaseConf);

// With named instances
$database = Database::setInstance($databaseConf, 'primary');

示例

例如，我们有一个名为 users 的表，其模式如下

表中数据如下

选择方法

输出总是数组。

SelectAll

返回所有行

$results = $database->selectAll("SELECT * FROM users");

// Output be like
[
    ['id' => '1', 'username' => 'taylor', 'ranking' => 10],
    ['id' => '2', 'username' => 'alison', 'ranking' => 30],
    ['id' => '3', 'username' => 'swifts', 'ranking' => 20]
]

SelectRow

仅返回第一行

$results = $database->selectRow("SELECT * FROM users");

// Output be like
['id' => '1', 'username' => 'taylor', 'ranking' => 10]

SelectCol

仅返回第一列

$results = $database->selectCol("SELECT username FROM users");

// Output be like
[
    'taylor',
    'alison',
    'swifts'
]

SelectVar

仅返回第一行的第一个值

$results = $database->selectVar("SELECT username FROM users WHERE id = 3");

// Output be like
'swifts'

Select + (读取或读取所有)

具有语句并使用读取按行获取或使用readAll获取所有数据。当您想使用特定的获取模式时很有用。

$statement = $database->select("SELECT * FROM users");
$row = $database->read($statement);

// Output be like
['id' => '1', 'username' => 'taylor', 'ranking' => 10]

$statement = $database->select("SELECT * FROM users");
$rows = $database->readAll($statement);

// Output be like
[
    ['id' => '1', 'username' => 'taylor', 'ranking' => 10],
    ['id' => '2', 'username' => 'alison', 'ranking' => 30],
    ['id' => '3', 'username' => 'swifts', 'ranking' => 20]
]

计数

在执行 SELECT COUNT(*) FROM ... 时仅返回值。

$count = $database->count("SELECT COUNT(*) FROM users");

// Output be like
3

插入

// insert with parameters and get last insert id
$params = ['username' => 'adam', 'ranking' => 100];
$lastInsertId = $database->insert("INSERT INTO users (username, ranking) VALUES (:username, :ranking)", $params, true);

// Output be like
4

更新

// update with parameters and get the number of affected rows
$params = ['username' => 'adam', 'id' => 4];
$affectedRowsCount = $database->update("UPDATE users SET username = :username WHERE id = :id", $params, true);

// Output be like
1

删除

// delete with parameters and get the number of affected rows
$params = ['id' => 4];
$affectedRowsCount = $database->delete("DELETE FROM users WHERE id = :id", $params, true);

// Output be like
1

事务

MySQL、PostgreSQL、SQLite支持嵌套事务。

$database->startTransaction();

if (isOk()) {
    $database->commitTransaction();
} else {
    $database->rollbackTransaction();
}

命名实例

您必须命名您的实例。
然后您可以通过它们的名称获取它们。

Database::setInstance($databaseConfA, 'primary');
Database::setInstance($databaseConfB, 'secondary');

/** A few moments later **/

$db = Database::getInstance('secondary');

配置器

构造函数设置

以下是传递给构造函数的数组的描述

必选键

可选键

方法

• createPDOConnection(): PDO
• disablePersistentConnection(): void
• disableSaveQueries(): void
• enablePersistentConnection(): void
• enableSaveQueries(): void
• getCharset(): string
• getDatabase(): string
• getDSN(): string
• getDriver(): string
• getHost(): string
• getParameters(): array
• getParametersForPDO(): array
• getPassword(): string
• getUser(): string
• hasPersistentConnection(): bool
• hasSavedQueries(): bool
• setCharset(charset: string): void
• setDatabase(database: string): void
• setDriver(driver: string): void
• setHost(host: string): void
• setParameter(key: mixed, value: mixed): void
• setParameters(parameters: array): void
• setPassword(password: string): void
• setUser(user: string): void

数据库

构造函数

必选

通用命令

• selectAll(sql: string, [parameters: array = []]): array
• selectRow(sql: string, [parameters: array = []]): array
• selectCol(sql: string, [parameters: array = []]): array
• selectVar(sql: string, [parameters: array = []]): mixed
• insert(sql: string, [parameters: array = []], [getLastInsertId: bool = false]): ?int
• update(sql: string, [parameters: array = []], [getAffectedRowsCount: bool = false]): ?int
• delete(sql: string, [parameters: array = []], [getAffectedRowsCount: bool = false]): ?int
• count(sql: string, [parameters: array = []]): ?int
• exec(sql: string, [parameters: array = []]): void
• select(sql: string, [parameters: array = []]): PDOStatement
• read(statement: PDOStatement, [fetchType: int = PDO::FETCH_ASSOC]): mixed
• readAll(statement: PDOStatement, [fetchType: int = PDO::FETCH_ASSOC]): array

事务

• startTransaction(): void
• completeTransaction(): void
• commitTransaction(): void
• rollbackTransaction(): void

错误

• hasErrors(): bool
• getErrors(): array
• getLastError(): ?array
• cleanErrors(): void

保存查询

• hasSavedQueries(): bool
• enableSaveQueries(): void
• disableSaveQueries(): void
• cleanSavedQueries(): void
• getSavedQueries(): array

特定命令

• truncateTables(...tables: string): void
• dropTables(...tables: string): void
• useSqlFile(filepath: string): void

低级别

• connect(): void
• disconnect(): void
• getPDO(): ?PDO

静态方法

• setInstance(configurator: Configurator, [name: string = primary]]): self
• hasInstance([name: string = primary]): bool
• getInstance([name: string = primary]): ?self

如何开发

docker compose build && docker compose run lib composer ci 用于启动测试