# Database

Database Abstraction Layer

## Requirements

+ PHP 8+
+ PDO

### Recommended

+ [`php-di/php-di`](https://packagist.org/packages/php-di/php-di)

## Installation

`composer.json`
```
{
    ...
    "repositories": [
        ...
        {
            "type": "git",
            "url": "https://git.provm.cl/ProVM/database.git"
        }
        ...
    ],
    ...
    "require": {
        ...
        "provm/database": "^2.3"
        ...
    },
    ...
}
```

## Usage

For `MySQL`/`MariaDB`

Without `DI`
```
$database new ProVM\Database\MySQL();
$database->setHost(<host>);
$database->setPort(<port>); // If diferent from 3306
$database->setUsername(<username>);
$database->setPassword(<password>);

$connection = new ProVM\Database\Connection($database);
```
With `DI`
```
$container->set(ProVM\Concept\Database::class, function(Psr\Container\ContainerInterface $container) {
    $database = $container->get(ProVM\Database\MySQL::class);
    $database->setHost(<host>);
    $database->setPort(<port>); // If diferent from 3306
    $database->setUsername(<username>);
    $database->setPassword(<password>);
    return $database;
});
$container->set(ProVM\Concept\Database\Connection::class, function(Psr\Container\ContainerInterface $container) {
    return new ProVM\Database\Connection($container->get(ProVM\Concept\Database::class);
});
```

Run query
```
$rs = $connection->query(<query>);
```
Run prepared statement
```
$rs = $connection->prepare(<query>)->execute(<value>);
or
$rs = $connection->execute(<query>, <values>);
```
Get data from ResultSet
```
$data = $rs->fetchFirst();
$data_object = $rs->fetchFirstAsObject();
$data_array = $rs->fetchAll();
$data_array_of_objects = $rs->fetchAllAsObjects();
```
Use transactions
```
$connection->transaction()->begin();
try {
    $connection->execute($query, $values);
    $connection->transaction()->commit();
} catch (PDOException $exception) {
    $connection->transaction()->rollBack();
}
```

## Definitions

### Database
Database configuration
+ `Database::host` Connection host name [`string`]
+ `Database::port` Connection port number [`int`]
+ `Database::name` Database name to connect [`string`]
+ `Database::username` Optional. The username to connect [`string`]
+ `Database::password` Optional. The password for the username [`string`]
+ `Database->getDsn()` get dsn string for PDO connection. [`string`]
+ `Database->needsUser()` If a user is needed for the connection.

### Connection
Connection handling
+ `Connection::query` Query the database
+ `Connection::prepare` Prepare query statement
+ `Connection::execute` Prepare and execute a query statement
+ `Connection::transaction` Return a transaction

### Transaction
Transaction
+ `Transaction::begin` Begin transaction
+ `Transaction::commit` Commit changes
+ `Transaction::rollBack` Roll back changes

### ResultSet
Result set to handle PDOStatement
+ `ResultSet::execute` Execute a prepared statement
+ `ResultSet::fetchAll` Return query results as array of associative arrays
+ `ResultSet::fetchAllAsObjects` Return query results as array of objects
+ `ResultSet::fetchFirst` Return first result as associative array
+ `ResultSet::fetchFirstAsObject` Return first result as object

## TODO