Database Core

Nette Database Core является уровнем абстракции базы данных и обеспечивает основную функциональность.

Установка

Загрузите и установите пакет с помощью Composer:

composer require nette/database

Подключение и настройка

Чтобы подключиться к базе данных, просто создайте экземпляр класса Nette\Database\Connection:

$database = new Nette\Database\Connection($dsn, $user, $password);

Параметр $dsn (имя источника данных) — такой же, как используется в PDO, например host=127.0.0.1;dbname=test. В случае неудачи выбрасывается исключение Nette\Database\ConnectionException.

Однако, более сложный способ предлагает конфигурация приложения. Мы добавим раздел database, и он создаст необходимые объекты и панель Database в панели отладки Tracy.

database:
	dsn: 'mysql:host=127.0.0.1;dbname=test'
	user: root
	password: password

Объект соединения, который мы получаем как сервис от DI-контейнера, например:

class Model
{
	// передаем Nette\Database\Explorer для работы с уровнем Database Explorer
	public function __construct(
		private Nette\Database\Connection $database,
	) {
	}
}

Для получения дополнительной информации смотрите конфигурацию базы данных.

Запросы

Для запроса к базе данных используйте метод query(), который возвращает ResultSet.

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

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

echo $result->getRowCount(); // возвращает количество строк, если оно известно

Над ResultSet можно выполнить итерацию только один раз, если нам нужно выполнить итерацию несколько раз, необходимо преобразовать результат в массив с помощью метода fetchAll().

Вы можете легко добавить параметры в запрос, обратите внимание на знак вопроса:

$database->query('SELECT * FROM users WHERE name = ?', $name);

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

$database->query('SELECT * FROM users WHERE id IN (?)', $ids); // $ids - массив

ВНИМАНИЕ, никогда не объединяйте строки во избежание уязвимости через SQL-инъекции!

$db->query('SELECT * FROM users WHERE name = ' . $name); // НЕПРАВИЛЬНО!!!

В случае неудачи query() выбрасывает либо исключение Nette\Database\DriverException, либо одно из его дочерних исключений:

ConstraintViolationException – нарушение любого из условий
ForeignKeyConstraintViolationException – недопустимый внешний ключ
NotNullConstraintViolationException – нарушение условия NOT NULL
UniqueConstraintViolationException – конфликт уникального индекса

Помимо query(), существуют и другие полезные методы:

// возвращает ассоциативный массив id => name
$pairs = $database->fetchPairs('SELECT id, name FROM users');

// возвращает все строки в виде массива
$rows = $database->fetchAll('SELECT * FROM users');

// возвращает одну строку
$row = $database->fetch('SELECT * FROM users WHERE id = ?', $id);

// возвращает одно поле
$name = $database->fetchField('SELECT name FROM users WHERE id = ?', $id);

В случае неудачи все эти методы выбрасывают исключение Nette\Database\DriverException.

Вставка, обновление и удаление

Параметр, который мы вставляем в SQL-запрос, также может быть массивом (в этом случае можно пропустить подстановочный знак ?), что может быть полезно для оператора INSERT:

$database->query('INSERT INTO users ?', [ // здесь может быть опущен знак вопроса
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

$id = $database->getInsertId(); // возвращает автоинкремент вставленной строки

$id = $database->getInsertId($sequence); // или значение последовательности

Вставка нескольких значений:

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

Мы также можем передавать файлы, объекты DateTime или перечисления:

$database->query('INSERT INTO users', [
	'name' => $name,
	'created' => new DateTime, // или $database::literal('NOW()')
	'avatar' => fopen('image.gif', 'r'), // вставляет содержимое файла
	'status' => State::New, // enum State
]);

Обновление строк:

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

echo $result->getRowCount(); // возвращает количество затронутых строк

Для UPDATE мы можем использовать операторы += и -=:

$database->query('UPDATE users SET', [
	'age+=' => 1, // note +=
], 'WHERE id = ?', $id);
// UPDATE users SET `age` = `age` + 1

Удаление:

$result = $database->query('DELETE FROM users WHERE id = ?', $id);
echo $result->getRowCount(); // возвращает количество затронутых строк

Продвинутые запросы

Вставка или обновление, если запись уже существует:

$database->query('INSERT INTO users', [
	'id' => $id,
	'name' => $name,
	'year' => $year,
], 'ON DUPLICATE KEY UPDATE', [
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`id`, `name`, `year`) VALUES (123, 'Jim', 1978)
//   ON DUPLICATE KEY UPDATE `name` = 'Jim', `year` = 1978

Обратите внимание, что Nette Database распознает SQL-контекст, в который вставлен параметр массива, и строит SQL-код соответствующим образом. Так, из первого массива он генерирует (id, name, year) VALUES (123, 'Jim', 1978), а второй преобразует в name = 'Jim', year = 1978.

Мы также можем описать сортировку с помощью массива, в котором ключами являются имена столбцов, а значениями — значения типа boolean, определяющие, следует ли сортировать в порядке возрастания:

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

Если обнаружение не сработало, вы можете указать форму сборки с помощью подстановочного знака ?, за которым следует подсказка. Поддерживаются следующие подсказки:

?values	(key1, key2, …) VALUES (value1, value2, …)
?set	key1 = value1, key2 = value2, …
?and	key1 = value1 AND key2 = value2 …
?or	key1 = value1 OR key2 = value2 …
?order	key1 ASC, key2 DESC

В предложении WHERE используется оператор ?and, поэтому условия связаны AND:

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

Который можно легко изменить на OR, используя подстановочный знак ?or:

$result = $database->query('SELECT * FROM users WHERE ?or', [
	'name' => $name,
	'year' => $year,
]);
// SELECT * FROM users WHERE `name` = 'Jim' OR `year` = 1978

Мы можем использовать операторы в условиях:

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

А также перечисления:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => ['Jim', 'Jack'],
	'role NOT IN' => ['admin', 'owner'], // перечисление + оператор NOT IN
]);
// SELECT * FROM users WHERE
//   `name` IN ('Jim', 'Jack') AND `role` NOT IN ('admin', 'owner')

Мы также можем включить часть пользовательского SQL-кода, используя так называемый SQL-литерал:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	'year >' => $database::literal('YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (`year` > YEAR())

В качестве альтернативы:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > YEAR()'),
]);
// SELECT * FROM users WHERE (`name` = 'Jim') AND (year > YEAR())

SQL-литерал также может иметь свои параметры:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('year > ? AND year < ?', $min, $max),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (year > 1978 AND year < 2017)

Благодаря этому мы можем создавать интересные комбинации:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => $name,
	$database::literal('?or', [
		'active' => true,
		'role' => $role,
	]),
]);
// SELECT * FROM users WHERE `name` = 'Jim' AND (`active` = 1 OR `role` = 'admin')

Имя переменной

Существует подстановочный знак `?name', который используется, если имя таблицы или столбца является переменной. (Осторожно, не позволяйте пользователю манипулировать содержимым такой переменной):

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

Транзакции

Существует три метода работы с транзакциями:

$database->beginTransaction();

$database->commit();

$database->rollback();

Элегантный способ предлагает метод transaction(). Вы передаете обратный вызов, который выполняется в транзакции. Если во время выполнения возникает исключение, транзакция сбрасывается, если всё идет хорошо, транзакция фиксируется.

$id = $database->transaction(function ($database) {
	$database->query('DELETE FROM ...');
	$database->query('INSERT INTO ...');
	// ...
	return $database->getInsertId();
});

Как видите, метод transaction() возвращает возвращаемое значение обратного вызова.

Транзакция() также может быть вложенной, что упрощает реализацию независимых хранилищ.

Отражение

Nette Database предоставляет инструменты для изучения структуры базы данных с помощью класса Nette\Database\Reflection. Этот класс позволяет получать информацию о таблицах, столбцах, индексах и внешних ключах. Вы можете использовать отражение для генерации схем, создания гибких приложений, работающих с базами данных, или создания общих инструментов для работы с базами данных.

Объект отражения можно получить из экземпляра соединения с базой данных:

$reflection = $database->getReflection();

Работа с таблицами

Используя отражение, вы можете выполнить итерацию по всем таблицам в базе данных:

// Список имен всех таблиц
foreach ($reflection->tables as $tableName => $table) {
    echo $tableName . "\n";
}

// Проверить, существует ли таблица
if ($reflection->hasTable('users')) {
    echo "The 'users' table exists";
}

// Получить определенную таблицу
$table = $reflection->getTable('users');

Информация о колоннах

Для каждой таблицы можно получить подробную информацию о ее столбцах:

// Итерация по всем столбцам
foreach ($table->columns as $column) {
    echo "Column: " . $column->name . "\n";
    echo "Type: " . $column->nativeType . "\n";
    echo "Nullable: " . ($column->nullable ? 'Yes': 'No') . "\n";
    echo "Default value: " . ($column->default ?? 'None') . "\n";
    echo "Primary key: " . ($column->primary ? 'Yes': 'No') . "\n";
    echo "Auto-increment: " . ($column->autoIncrement ? 'Yes': 'No') . "\n";
}

// Получение определенного столбца
$idColumn = $table->getColumn('id');

Индексы и первичные ключи

Отражение предоставляет информацию об индексах и первичных ключах:

$listColumnNames = fn(array $columns) => implode(', ', array_map(fn($col) => $col->name, $columns));

// Список всех индексов
foreach ($table->indexes as $index) {
    echo "Index: " . ($index->name ?? 'Unnamed') . "\n";
    echo "Columns: " . $listColumnNames($index->columns) . "\n";
    echo "Unique: " . ($index->unique ? 'Yes': 'No') . "\n";
    echo "Primary key: " . ($index->primary ? 'Yes': 'No') . "\n";
}

// Получение первичного ключа
if ($table->primaryKey) {
    echo "Primary key: " . $listColumnNames($table->primaryKey->columns) . "\n";
}

Иностранные ключи

Вы также можете получить информацию о внешних ключах:

foreach ($table->foreignKeys as $fk) {
    echo "Foreign key: " . ($fk->name ?? 'Unnamed') . "\n";
    echo "Local columns: " . $listColumnNames($fk->localColumns) . "\n";
    echo "References table: {$fk->foreignTable->name}\n";
    echo "References columns: " . $listColumnNames($fk->foreignColumns) . "\n";
}