Dibi: Smart Database Abstraction Library for PHP
************************************************

To install the latest stable Dibi version, use the [Composer|best-practices:composer] command:

```
composer require dibi/dibi
```

You can find version overview on the [Releases | https://github.com/dg/dibi/releases] page.

Requires PHP 8.0 or newer.


Connecting to Database
======================

The database connection is represented by the [Dibi\Connection|api:] object:

```php
$database = new Dibi\Connection([
	'driver'   => 'mysqli',
	'host'     => 'localhost',
	'username' => 'root',
	'password' => '***',
	'database' => 'table',
]);

$result = $database->query('SELECT * FROM users');
```

Alternatively, you can use the `dibi` static registry, which maintains a connection object in globally accessible storage and calls all functions on it:

```php
dibi::connect([
	'driver'   => 'mysqli',
	'host'     => 'localhost',
	'username' => 'root',
	'password' => '***',
	'database' => 'test',
	'charset'  => 'utf8',
]);

$result = dibi::query('SELECT * FROM users');
```

In case of a connection error, it throws `Dibi\Exception`.


Queries
=======

We query the database using the `query()` method, which returns [Dibi\Result |api:Dibi\Result]. Rows are returned as [Dibi\Row |api:Dibi\Row] objects.

You can try all the examples [online at the playground |https://repl.it/@DavidGrudl/dibi-playground].

```php
$result = $database->query('SELECT * FROM users');

foreach ($result as $row) {
	echo $row->id;
	echo $row->name;
}

// array of all rows
$all = $result->fetchAll();

// array of all rows, keyed by 'id'
$all = $result->fetchAssoc('id');

// associative pairs id => name
$pairs = $result->fetchPairs('id', 'name');

// number of result rows, if known, or number of affected rows
$count = $result->getRowCount();
```

The fetchAssoc() method can return [more complex associative arrays |#Result as associative array].

You can easily add parameters to the query - note the question mark:

```php
$result = $database->query('SELECT * FROM users WHERE name = ? AND active = ?', $name, $active);

// or
$result = $database->query('SELECT * FROM users WHERE name = ?', $name, 'AND active = ?', $active);

$ids = [10, 20, 30];
$result = $database->query('SELECT * FROM users WHERE id IN (?)', $ids);
```

<div class=warning>
**WARNING: never concatenate parameters into SQL queries, as this would create [SQL injection |https://en.wikipedia.org/wiki/SQL_injection] vulnerability**
/--
$database->query('SELECT * FROM users WHERE id = ' . $id); // BAD!!!
\--
</div>

Instead of question marks, you can also use so-called [#modifiers].

```php
$result = $database->query('SELECT * FROM users WHERE name = %s', $name);
```

In case of failure, `query()` throws either `Dibi\Exception` or one of its descendants:

- [ConstraintViolationException |api:Dibi\ConstraintViolationException] - violation of some table constraint
- [ForeignKeyConstraintViolationException |api:Dibi\ForeignKeyConstraintViolationException] - invalid foreign key
- [NotNullConstraintViolationException |api:Dibi\NotNullConstraintViolationException] - violation of the NOT NULL condition
- [UniqueConstraintViolationException |api:Dibi\UniqueConstraintViolationException] - collision with unique index

You can also use shortcut methods:

```php
// returns associative pairs id => name, shortcut for query(...)->fetchPairs()
$pairs = $database->fetchPairs('SELECT id, name FROM users');

// returns array of all rows, shortcut for query(...)->fetchAll()
$rows = $database->fetchAll('SELECT * FROM users');

// returns row, shortcut for query(...)->fetch()
$row = $database->fetch('SELECT * FROM users WHERE id = ?', $id);

// returns cell, shortcut for query(...)->fetchSingle()
$name = $database->fetchSingle('SELECT name FROM users WHERE id = ?', $id);
```


Modifiers
=========

In addition to the `?` placeholder, we can also use modifiers:

| %s | string
| %sN | string, but '' translates as NULL
| %bin | binary data
| %b | boolean
| %i | integer
| %iN | integer, but 0 translates as NULL
| %f | float
| %d | date (accepts DateTime, string or UNIX timestamp)
| %dt | datetime (accepts DateTime, string or UNIX timestamp)
| %n | identifier, i.e. table or column name
| %N | identifier, treats period as ordinary character
| %SQL | SQL - directly inserts into SQL (alternative is Dibi\Literal)
| %ex | expands array
| %lmt | special - adds LIMIT to the query
| %ofs | special - adds OFFSET to the query

Example:

```php
$result = $database->query('SELECT * FROM users WHERE name = %s', $name);
```

If `$name` is `null`, `NULL` is inserted into the SQL statement.

If the variable is an array, the modifier is applied to all of its elements and they are inserted into SQL separated by commas:

```php
$ids = [10, '20', 30];
$result = $database->query('SELECT * FROM users WHERE id IN (%i)', $ids);
// SELECT * FROM users WHERE id IN (10, 20, 30)
```

The `%n` modifier is used when the table or column name is a variable. (Beware: do not allow the user to manipulate the content of such a variable):

```php
$table = 'blog.users';
$column = 'name';
$result = $database->query('SELECT * FROM %n WHERE %n = ?', $table, $column, $value);
// SELECT * FROM `blog`.`users` WHERE `name` = 'Jim'
```

Four special modifiers are available for the LIKE operator:

| %like~ | expression starts with string
| %~like | expression ends with string
| %~like~ | expression contains string
| `%like` | expression matches string

Search for names starting with a certain string:

```php
$result = $database->query('SELECT * FROM table WHERE name LIKE %like~', $query);
```


Array Modifiers
===============

The parameter inserted into an SQL query can also be an array. These modifiers determine how to construct the SQL statement from it:

| %and   |        | `key1 = value1 AND key2 = value2 AND ...`
| %or    |        | `key1 = value1 OR key2 = value2 OR ...`
| %a     | assoc  | `key1 = value1, key2 = value2, ...`
| %l %in | list   | `(val1, val2, ...)`
| %v     | values | `(key1, key2, ...) VALUES (value1, value2, ...)`
| %m     | multi  | `(key1, key2, ...) VALUES (value1, value2, ...), (value1, value2, ...), ...`
| %by    | ordering | `key1 ASC, key2 DESC ...`
| %n     | names  | `key1, key2 AS alias, ...`

Example:

```php
$arr = [
	'a' => 'hello',
	'b'  => true,
];

$database->query('INSERT INTO table %v', $arr);
// INSERT INTO `table` (`a`, `b`) VALUES ('hello', 1)

$database->query('UPDATE `table` SET %a', $arr);
// UPDATE `table` SET `a`='hello', `b`=1
```

In the WHERE clause, you can use `%and` or `%or` modifiers:

```php
$result = $database->query('SELECT * FROM users WHERE %and', [
	'name' => $name,
	'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND `year` = 1978
```

See also [#Complex queries].

The `%by` modifier is used for sorting - keys specify the columns, and the boolean value determines whether to sort in ascending order:

```php
$result = $database->query('SELECT id FROM author ORDER BY %by', [
	'id' => true, // ascending
	'name' => false, // descending
]);
// SELECT id FROM author ORDER BY `id`, `name` DESC
```


Insert, Update & Delete
=======================

We insert data into SQL queries as associative arrays. Modifiers and the `?` placeholder are not necessary in these cases.

```php
$database->query('INSERT INTO users', [
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

$id = $database->getInsertId(); // returns the auto-increment of the inserted record

$id = $database->getInsertId($sequence); // or sequence value
```

Multiple INSERT:

```php
$database->query(
	'INSERT INTO users',
	[
		'name' => 'Jim',
		'year' => 1978,
	],
	[
		'name' => 'Jack',
		'year' => 1987,
	]
);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978), ('Jack', 1987)
```

Deleting:

```php
$database->query('DELETE FROM users WHERE id = ?', $id);

// returns number of deleted rows
$affectedRows = $database->getAffectedRows();
```

Updating records:

```php
$database->query('UPDATE users SET', [
	'name' => $name,
	'year' => $year,
], 'WHERE id = ?', $id);
// UPDATE users SET `name` = 'Jim', `year` = 1978 WHERE id = 123

// returns the number of updated rows
$affectedRows = $database->getAffectedRows();
```

Substitute any identifier:

```php
$database->query('INSERT INTO users', [
	'id' => $id,
	'name' => $name,
	'year' => $year,
], 'ON DUPLICATE KEY UPDATE %a', [ // here the modifier %a must be used
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
//   ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978
```


Transaction
===========

There are four methods for dealing with transactions:

```php
$database->beginTransaction();

$database->commit();

$database->rollback();

$database->transaction(function () {
	// some action
});
```


Testing
=======

In order to play with Dibi a little, there is a `test()` method that you pass parameters like to `query()`, but instead of executing the SQL statement, it is echoed on the screen.

The query results can be echoed as a table using `$result->dump()`.

These variables are also available:

```php
dibi::$sql; // the latest SQL query
dibi::$elapsedTime; // its duration in sec
dibi::$numOfQueries;
dibi::$totalTime;
```


Complex Queries
===============

The parameter may also be an object `DateTime`.

```php
$result = $database->query('SELECT * FROM users WHERE created < ?', new DateTime);

$database->query('INSERT INTO users', [
	'created' => new DateTime,
]);
```

Or SQL literal:

```php
$database->query('UPDATE table SET', [
	'date' => $database->literal('NOW()'),
]);
// UPDATE table SET `date` = NOW()
```

Or an expression in which you can use `?` or modifiers:

```php
$database->query('UPDATE `table` SET', [
	'title' => $database::expression('SHA1(?)', 'secret'),
]);
// UPDATE `table` SET `title` = SHA1('secret')
```

When updating, modifiers can be placed directly in the keys:

```php
$database->query('UPDATE table SET', [
	'date%SQL' => 'NOW()', // %SQL means SQL ;)
]);
// UPDATE table SET `date` = NOW()
```

In conditions (ie, for `%and` and `%or` modifiers), it is not necessary to specify the keys:

```php
$result = $database->query('SELECT * FROM `table` WHERE %and', [
	'number > 10',
	'number < 100',
]);
// SELECT * FROM `table` WHERE (number > 10) AND (number < 100)
```

Modifiers or placeholders can also be used in expressions:

```php
$result = $database->query('SELECT * FROM `table` WHERE %and', [
	['number > ?', 10],  // or $database::expression('number > ?', 10)
	['number < ?', 100],
	['%or', [
		'left' => 1,
		'top' => 2,
	]],
]);
// SELECT * FROM `table` WHERE (number > 10) AND (number < 100) AND (`left` = 1 OR `top` = 2)
```

The `%ex` modifier inserts all items of the array into SQL:

```php
$result = $database->query('SELECT * FROM `table` WHERE %ex', [
	$database::expression('left = ?', 1),
	'AND',
	'top IS NULL',
]);
// SELECT * FROM `table` WHERE left = 1 AND top IS NULL
```


Conditions in SQL Statements
============================

Conditional SQL statements are controlled by three modifiers: `%if`, `%else`, and `%end`. The `%if` must be at the end of the string representing SQL and is followed by a variable:

```php
$user = ???

$result = $database->query('
	SELECT *
	FROM table
	%if', isset($user), 'WHERE user=%s', $user, '%end
	ORDER BY name
');
```

The condition can be supplemented with an `%else` section:

```php
$result = $database->query('
	SELECT *
	FROM %if', $cond, 'one_table %else second_table
');
```

Conditions can be nested within each other.


Identifiers and Strings in SQL
==============================

SQL itself goes through processing to meet the conventions of the given database. Identifiers (table and column names) can be enclosed in square brackets or backticks, and strings in single or double quotes, but the server always sends what the database requires. Example:

```php
$database->query("UPDATE `table` SET [status]='I''m fine'");
// MySQL: UPDATE `table` SET `status`='I\'m fine'
// ODBC:  UPDATE [table] SET [status]='I''m fine'
```

Quotes inside strings in SQL are written by doubling them.


Result as Associative Array
===========================

Example: returns results as an associative array where the key will be the value of the `id` field:

```php
$assoc = $result->fetchAssoc('id');
```

The greatest power of `fetchAssoc()` is demonstrated in SQL queries joining several tables with different types of relationships. The database creates a flat table, fetchAssoc restores the shape.

Example: Let's have a customer and order table (N:M relationship) and query:

```php
$result = $database->query('
  SELECT customer_id, customers.name, order_id, orders.number, ...
  FROM customers
  INNER JOIN orders USING (customer_id)
  WHERE ...
');
```

And we'd like to get a nested associative array by Customer ID and then by Order ID:

```php
$all = $result->fetchAssoc('customer_id|order_id');

// we will iterate like this:
foreach ($all as $customerId => $orders) {
   foreach ($orders as $orderId => $order) {
	   ...
   }
}
```

The associative descriptor has similar syntax to when you write arrays using assignment in PHP. Thus `'customer_id|order_id'` represents the assignment series `$all[$customerId][$orderId] = $row;` sequentially for all rows.

Sometimes it would be useful to associate by the customer's name instead of their ID:

```php
$all = $result->fetchAssoc('name|order_id');

// elements are then accessed like this:
$order = $all['Arnold Rimmer'][$orderId];
```

But what if there are multiple customers with the same name? The table should have the form:

```php
$row = $all['Arnold Rimmer'][0][$orderId];
$row = $all['Arnold Rimmer'][1][$orderId];
...
```

So we distinguish multiple possible Rimmers using a regular array. The associative descriptor again has a format similar to assignment, with sequential arrays represented by `[]`:

```php
$all = $result->fetchAssoc('name[]order_id');

// we iterate all Arnolds in the results
foreach ($all['Arnold Rimmer'] as $arnoldOrders) {
   foreach ($arnoldOrders as $orderId => $order) {
	   ...
   }
}
```

Returning to the example with the `customer_id|order_id` descriptor, let's try to list orders for each customer:

```php
$all = $result->fetchAssoc('customer_id|order_id');

foreach ($all as $customerId => $orders) {
   echo "Orders for customer $customerId":

   foreach ($orders as $orderId => $order) {
	   echo "Document number: $order->number";
	   // customer name is in $order->name
   }
}
```

It would be nice to display the customer name instead of ID. But we would have to look it up in the `$orders` array. So let's modify the results to have this shape:

```php
$all[$customerId]->name = 'John Doe';
$all[$customerId]->order_id[$orderId] = $row;
$all[$customerId]->order_id[$orderId2] = $row2;
```

So, between `$customerId` and `$orderId`, we insert an intermediate element. This time not numbered indexes as we used to distinguish individual Rimmers, but directly a database record. The solution is very similar - just remember that a record is symbolized by an arrow:

```php
$all = $result->fetchAssoc('customer_id->order_id');

foreach ($all as $customerId => $row) {
   echo "Orders for customer $row->name":

   foreach ($row->order_id as $orderId => $order) {
	   echo "Document number: $order->number";
   }
}
```


Prefixes & Substitutions
========================

Table and column names can contain variable parts. You will first define them:

```php
// create new substitution :blog:  ==>  wp_
$database->substitute('blog', 'wp_');
```

and then use them in SQL. Note that in SQL they are enclosed in colons:

```php
$database->query("UPDATE [:blog:items] SET [text]='Hello World'");
// UPDATE `wp_items` SET `text`='Hello World'
```


Field Data Types
================

Dibi automatically detects the types of individual query columns and converts cells to native PHP types. We can also specify the type manually. Possible types can be found in the [Dibi\Type |api:Dibi\Type] class.

```php
$result->setType('id', Dibi\Type::INTEGER); // id will be integer
$row = $result->fetch();

is_int($row->id) // true
```


Logging
=======

Dibi has a built-in logger that lets you track all executed SQL statements and measure the duration of their execution. Activation:

```php
$database->connect([
	'driver'   => 'sqlite',
	'database' => 'sample.sdb',
	'profiler' => [
		'file' => 'file.log',
	],
]);
```

A more versatile profiler is the Tracy panel, which is activated when connecting to Nette.


Connect to [Nette |https://nette.org]
=====================================

In the configuration file, we register the DI extension and add the `dibi` section - this creates the required objects and also the database panel in the [Tracy |https://tracy.nette.org] debugger bar.

```neon
extensions:
	dibi: Dibi\Bridges\Nette\DibiExtension22

dibi:
	host: localhost
	username: root
	password: ***
	database: foo
	lazy: true
```

Then the connection object can be [obtained as a service from the DI container |https://doc.nette.org/di-usage], e.g.:

```php
class Model
{
	private $database;

	public function __construct(Dibi\Connection $database)
	{
		$this->database = $database;
	}
}
```


Community Extensions
====================

Various libraries, ORMs and extensions are built on top of Dibi. You can find a complete list of them on "Packagist":https://packagist.org/packages/dibi/dibi/dependents?order_by=downloads&requires=require.

{{maintitle: Dibi – Smart Database Abstraction Library for PHP}}