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

ATTENZIONE, non concatenare mai le stringhe per evitare vulnerabilità da SQL injection!

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

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

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.

Riflessione

Nette Database offre strumenti per l'introspezione della struttura del database attraverso la classe Nette\Database\Reflection. Questa classe consente di recuperare informazioni su tabelle, colonne, indici e chiavi esterne. È possibile utilizzare la riflessione per generare schemi, creare applicazioni flessibili che lavorano con i database o costruire strumenti generali per i database.

È possibile ottenere un oggetto reflection da un'istanza di connessione al database:

$reflection = $database->getReflection();

Lavorare con le tabelle

Utilizzando la riflessione, è possibile iterare su tutte le tabelle del database:

// Elencare i nomi di tutte le tabelle
foreach ($reflection->tables as $tableName => $table) {
    echo $tableName . "\n";
}

// Controllare se una tabella esiste
if ($reflection->hasTable('users')) {
    echo "The 'users' table exists";
}

// Recuperare una tabella specifica
$table = $reflection->getTable('users');

Informazioni sulla colonna

Per ogni tabella è possibile ottenere informazioni dettagliate sulle sue colonne:

// Iterare su tutte le colonne
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";
}

// Recuperare una colonna specifica
$idColumn = $table->getColumn('id');

Indici e chiavi primarie

La riflessione fornisce informazioni su indici e chiavi primarie:

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

// Elencare tutti gli indici
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";
}

// Recuperare la chiave primaria
if ($table->primaryKey) {
    echo "Primary key: " . $listColumnNames($table->primaryKey->columns) . "\n";
}

Chiavi straniere

È inoltre possibile ottenere informazioni sulle chiavi esterne:

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";
}