Nucleo del database

Nette Database Core è il livello di astrazione del database e fornisce le funzionalità principali.

Installazione

Scaricare e installare il pacchetto utilizzando Composer:

composer require nette/database

Connessione e configurazione

Per connettersi al database, è sufficiente creare un'istanza della classe Nette\Database\Connection:

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

Il parametro $dsn (nome dell'origine dati) è lo stesso utilizzato da PDO, ad esempio host=127.0.0.1;dbname=test. In caso di fallimento, viene lanciato Nette\Database\ConnectionException.

Tuttavia, un modo più sofisticato offre la configurazione dell'applicazione. Aggiungiamo una sezione database che crea gli oggetti necessari e un pannello del database nella barra Tracy.

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

L'oggetto connessione che riceviamo come servizio da un contenitore DI, ad esempio:

class Model
{
	// passa Nette\Database\Explorer per lavorare con il livello Database Explorer
	public function __construct(
		private Nette\Database\Connection $database,
	) {
	}
}

Per ulteriori informazioni, vedere Configurazione del database.

Query

Per interrogare il database utilizzare il metodo query() che restituisce ResultSet.

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

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

echo $result->getRowCount(); // restituisce il numero di righe se è conosciuto

Su ResultSet è possibile iterare una sola volta; se abbiamo bisogno di iterare più volte, è necessario convertire il risultato in array con il metodo fetchAll().

È possibile aggiungere facilmente dei parametri alla query, come il punto interrogativo:

$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 è un array

$db->query('SELECT * FROM users WHERE name = ' . $name); // WRONG!!!

In caso di fallimento, query() lancia Nette\Database\DriverException o uno dei suoi discendenti:

• ConstraintViolationException – violazione di qualsiasi vincolo
• ForeignKeyConstraintViolationException – chiave esterna non valida
• NotNullConstraintViolationException – violazione della condizione NOT NULL
• UniqueConstraintViolationException – conflitto di un indice univoco

Oltre a query(), esistono altri metodi utili:

// restituisce l'array associativo id => nome
$pairs = $database->fetchPairs('SELECT id, name FROM users');

// restituisce tutte le righe come array
$rows = $database->fetchAll('SELECT * FROM users');

// restituisce una singola riga
$row = $database->fetch('SELECT * FROM users WHERE id = ?', $id);

// restituisce un singolo campo
$name = $database->fetchField('SELECT name FROM users WHERE id = ?', $id);

In caso di fallimento, tutti questi metodi lanciano Nette\Database\DriverException.

Inserire, aggiornare e cancellare

Il parametro che inseriamo nella query SQL può anche essere un array (in questo caso è possibile saltare l'istruzione jolly ?), which may be useful for the INSERT ):

$database->query('INSERT INTO users ?', [ // qui si può omettere il punto interrogativo
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

$id = $database->getInsertId(); // restituisce l'autoincremento della riga inserita

$id = $database->getInsertId($sequence); // o il valore della sequenza

Inserimento multiplo:

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

Si possono passare anche file, oggetti DateTime o enumerazioni:

$database->query('INSERT INTO users', [
	'name' => $name,
	'created' => new DateTime, // o $database::literal('NOW()')
	'avatar' => fopen('image.gif', 'r'), // inserisce il contenuto del file
	'status' => State::New, // enum State
]);

Aggiornamento delle righe:

$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(); // restituisce il numero delle righe interessate

Per l'UPDATE si possono utilizzare gli operatori += e -=:

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

Eliminazione:

$result = $database->query('DELETE FROM users WHERE id = ?', $id);
echo $result->getRowCount(); // restituisce il numero di righe interessate

Query avanzate

Inserire o aggiornare, se esiste già:

$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

Si noti che Nette Database riconosce il contesto SQL in cui è inserito il parametro dell'array e costruisce il codice SQL di conseguenza. Quindi, dal primo array genera (id, name, year) VALUES (123, 'Jim', 1978), mentre il secondo si converte in name = 'Jim', year = 1978.

Possiamo anche descrivere l'ordinamento utilizzando un array, in cui le chiavi sono nomi di colonne e i valori sono booleani che determinano se ordinare in ordine crescente:

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

Se il rilevamento non funziona, è possibile specificare la forma dell'insieme con un carattere jolly ? seguito da un suggerimento. Sono supportati questi suggerimenti:

La clausola WHERE utilizza l'operatore ?and, quindi le condizioni sono collegate da AND:

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

Che può essere facilmente modificato in OR utilizzando il carattere jolly ?or:

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

Possiamo usare gli operatori nelle condizioni:

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

E anche le enumerazioni:

$result = $database->query('SELECT * FROM users WHERE', [
	'name' => ['Jim', 'Jack'],
	'role NOT IN' => ['admin', 'owner'], // enumeration + operator NOT IN
]);
// SELECT * FROM users WHERE
//   `name` IN ('Jim', 'Jack') AND `role` NOT IN ('admin', 'owner')

Possiamo anche includere un pezzo di codice SQL personalizzato utilizzando il cosiddetto letterale SQL:

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

In alternativa:

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

Anche i letterali SQL possono avere i loro parametri:

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

Grazie a questi si possono creare interessanti combinazioni:

$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')

Nome della variabile

Esiste un carattere jolly ?name da utilizzare se il nome della tabella o della colonna è una variabile. (Attenzione, non permettete all'utente di manipolare il contenuto di tale variabile):

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

Transazioni

Esistono tre metodi per gestire le transazioni:

$database->beginTransaction();

$database->commit();

$database->rollback();

Un modo elegante è offerto dal metodo transaction(). Si passa il callback che viene eseguito nella transazione. Se viene lanciata un'eccezione durante l'esecuzione, la transazione viene abbandonata; se tutto va bene, la transazione viene impegnata.

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

Come si può vedere, il metodo transaction() restituisce il valore di ritorno della callback.

Il metodo transaction() può anche essere annidato, il che semplifica l'implementazione di repository indipendenti.