rancoud/database

Database package

6.0.12 2024-11-09 21:16 UTC

Requires

• php: >=7.4.0
• ext-pdo: *

Requires (Dev)

• friendsofphp/php-cs-fixer: ^2.16 || ^3.0
• phpunit/phpunit: ^9.1 || ^10.0 || ^11.0
• squizlabs/php_codesniffer: ^3.5

Suggests

• ext-pdo_mysql: Needed to connect MySQL
• ext-pdo_pgsql: Needed to connect PostgreSQL
• ext-pdo_sqlite: Needed to connect SQLite

MIT

• Sebastien Rancoud

README

Request Database (use PDO). Supported drivers: MySQL, PostgreSQL, SQLite.

Installation

composer require rancoud/database

How to use it?

Connection to a 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');

Examples

For example we have a table users with this schema:

In the table we have these data:

Select methods

The output is always an array.

SelectAll

Return all rows

$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

Return only the first row

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

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

SelectCol

Return only the first column

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

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

SelectVar

Return only the first value of first line

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

// Output be like
'swifts'

Select + (Read OR ReadAll)

Having the statement and use read to get row by row or readAll for all data. Useful when you want to use a specific fetch mode.

$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]
]

Count

Return only the value when using SELECT COUNT(*) FROM ....

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

// Output be like
3

Insert

// 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

// 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

// 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

Transactions

Nested transactions are supported for MySQL, PostgreSQL, SQLite.

$database->startTransaction();

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

Named instances

You have to name your instances.
Then you can get them by their name.

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

/** A few moments later **/

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

Configurator

Constructor Settings

Here is the description of the array passed to the construct

Mandatory keys

Optional keys

Methods

• 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

Database

Constructor

Mandatory

General Commands

• 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

Transactions

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

Errors

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

Save Queries

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

Specific Commands

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

Low Level

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

Static Method

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

How to Dev

docker compose build && docker compose run lib composer ci for launching tests