Explorateur de bases de données

Pour utiliser Database Explorer, commencez par une table – appelez table() sur un objet Nette\Database\Explorer. La manière la plus simple d'obtenir une instance d'objet de contexte est décrite ici, ou, pour le cas où Nette Database Explorer est utilisé comme un outil autonome, elle peut être créée manuellement.

$books = $explorer->table('book'); // le nom de la table de la base de données est 'book'.

L'appel renvoie une instance de l'objet Selection, qui peut être itéré pour récupérer tous les livres. Chaque élément (une ligne) est représenté par une instance de ActiveRow avec des données mappées à ses propriétés :

foreach ($books as $book) {
	echo $book->title;
	echo $book->author_id;
}

L'obtention d'une ligne spécifique se fait par la méthode get(), qui renvoie directement une instance ActiveRow.

$book = $explorer->table('book')->get(2); // renvoie le livre avec l'id 2
echo $book->title;
echo $book->author_id;

Voyons un cas d'utilisation courant. Vous avez besoin de récupérer des livres et leurs auteurs. Il s'agit d'une relation 1:N courante. La solution la plus courante est de récupérer les données en utilisant une seule requête SQL avec des jointures de tables. La deuxième possibilité est de récupérer les données séparément, d'exécuter une requête pour obtenir les livres, puis d'obtenir un auteur pour chaque livre au moyen d'une autre requête (par exemple, dans votre cycle foreach). Cela pourrait être facilement optimisé pour n'exécuter que deux requêtes, une pour les livres, et une autre pour les auteurs nécessaires – et c'est exactement la façon dont Nette Database Explorer le fait.

Dans les exemples ci-dessous, nous allons travailler avec le schéma de base de données de la figure. Il existe des liens OneHasMany (1:N) (auteur du livre author_id et traducteur éventuel translator_id, qui peut être null) et ManyHasMany (M:N) entre le livre et ses étiquettes.

Un exemple, incluant un schéma, se trouve sur GitHub.

Structure de la base de données utilisée dans les exemples

Le code suivant liste le nom de l'auteur pour chaque livre et toutes ses balises. Nous verrons dans un instant comment cela fonctionne en interne.

$books = $explorer->table('book');

foreach ($books as $book) {
	echo 'titre: ' . $book->title;
	echo 'écrit par: ' . $book->author->name; // $book->author est une ligne de la table 'author'.

	echo 'tags: ';
	foreach ($book->related('book_tag') as $bookTag) {
		echo $bookTag->tag->name . ', '; // $bookTag->tag est une ligne de la table 'tag'.
	}
}

Vous serez heureux de constater l'efficacité du fonctionnement de la couche de base de données. L'exemple ci-dessus fait un nombre constant de requêtes qui ressemblent à ceci :

SELECT * FROM `book`
SELECT * FROM `author` WHERE (`author`.`id` IN (11, 12))
SELECT * FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 4, 2, 3))
SELECT * FROM `tag` WHERE (`tag`.`id` IN (21, 22, 23))

Si vous utilisez le cache (activé par défaut), aucune colonne ne sera interrogée inutilement. Après la première requête, le cache stockera les noms des colonnes utilisées et Nette Database Explorer exécutera les requêtes uniquement avec les colonnes nécessaires :

SELECT `id`, `title`, `author_id` FROM `book`
SELECT `id`, `name` FROM `author` WHERE (`author`.`id` IN (11, 12))
SELECT `book_id`, `tag_id` FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 4, 2, 3))
SELECT `id`, `name` FROM `tag` WHERE (`tag`.`id` IN (21, 22, 23))

Sélections

Voir les possibilités de filtrer et de restreindre les lignes Nette\Database\Table\Selection:

Nous pouvons utiliser ce que l'on appelle une interface fluide, par exemple $table->where(...)->order(...)->limit(...). Plusieurs conditions where ou whereOr sont liées par l'opérateur AND.

où()

Nette Database Explorer peut ajouter automatiquement les opérateurs nécessaires pour les valeurs passées :

Vous pouvez fournir un caractère de remplacement même sans opérateur de colonne. Ces appels sont les mêmes.

$table->where('id = ? OR id = ?', 1, 2);
$table->where('id ? OR id ?', 1, 2);

Cette fonctionnalité permet de générer l'opérateur correct en fonction de la valeur :

$table->where('id ?', 2);    // id = 2
$table->where('id ?', null); // id IS NULL
$table->where('id', $ids);   // id IN (...)

La sélection gère correctement les conditions négatives, et fonctionne également pour les tableaux vides :

$table->where('id', []);       // id IS NULL AND FALSE
$table->where('id NOT', []);   // id IS NULL OR TRUE
$table->where('NOT (id ?)', $ids);  // NOT (id IS NULL AND FALSE)

// ceci lancera une exception, cette syntaxe n'est pas supportée.
$table->where('NOT id ?', $ids);

whereOr()

Exemple d'utilisation sans paramètres :

// WHERE (user_id IS NULL) OR (SUM(`field1`) > SUM(`field2`))
$table->whereOr([
	'user_id IS NULL',
	'SUM(field1) > SUM(field2)',
]);

On utilise les paramètres. Si vous ne spécifiez pas d'opérateur, Nette Database Explorer ajoutera automatiquement l'opérateur approprié :

// WHERE (`field1` IS NULL) OR (`field2` IN (3, 5)) OR (`amount` > 11)
$table->whereOr([
	'field1' => null,
	'field2' => [3, 5],
	'amount >' => 11,
]);

La clé peut contenir une expression contenant des points d'interrogation joker, puis passer des paramètres dans la valeur :

// WHERE (`id` > 12) OR (ROUND(`id`, 5) = 3)
$table->whereOr([
	'id > ?' => 12,
	'ROUND(id, ?) = ?' => [5, 3],
]);

commande()

Exemples d'utilisation :

$table->order('field1');               // ORDER BY `field1`
$table->order('field1 DESC, field2');  // ORDER BY `field1` DESC, `field2`
$table->order('field = ? DESC', 123);  // ORDER BY `field` = 123 DESC

select()

Exemples d'utilisation :

$table->select('field1');                 // SELECT `field1`
$table->select('col, UPPER(col) AS abc'); // SELECT `col`, UPPER(`col`) AS abc
$table->select('SUBSTR(title, ?)', 3);    // SELECT SUBSTR(`title`, 3)

limit()

Exemples d'utilisation :

$table->limit(1);     // LIMIT 1
$table->limit(1, 10); // LIMIT 1 OFFSET 10

page()

Une autre façon de définir la limite et le décalage :

$page = 5;
$itemsPerPage = 10;
$table->page($page, $itemsPerPage); // LIMIT 10 OFFSET 40

Récupérer le dernier numéro de page, passé à la variable $lastPage:

$table->page($page, $itemsPerPage, $lastPage);

group()

Exemples d'utilisation :

$table->group('field1');          // GROUP BY `field1`
$table->group('field1, field2');  // GROUP BY `field1`, `field2`

ayant()

Exemples d'utilisation :

$table->having('COUNT(items) >', 100);  // HAVING COUNT(`items`) > 100

Filtrage par une autre valeur de la table

Vous avez souvent besoin de filtrer les résultats en fonction d'une condition qui implique une autre table de la base de données. Ces types de conditions nécessitent une jointure de table. Cependant, vous n'avez plus besoin de les écrire.

Disons que vous devez obtenir tous les livres dont l'auteur s'appelle “Jon”. Tout ce que vous devez écrire est la clé de jointure de la relation et le nom de la colonne dans la table jointe. La clé de jointure est dérivée de la colonne qui fait référence à la table que vous voulez joindre. Dans notre exemple (voir le schéma de la base de données), il s'agit de la colonne author_id, et il suffit d'en utiliser la première partie – author (le suffixe _id peut être omis). name est une colonne de la table author que nous souhaitons utiliser. Il est tout aussi facile de créer une condition pour le traducteur de livres (qui est relié par la colonne translator_id ).

$books = $explorer->table('book');
$books->where('author.name LIKE ?', '%Jon%');
$books->where('translator.name', 'David Grudl');

La logique de la clé de jonction est pilotée par la mise en œuvre des Conventions. Nous vous encourageons à utiliser DiscoveredConventions, qui analyse vos clés étrangères et vous permet de travailler facilement avec ces relations.

La relation entre le livre et son auteur est 1:N. La relation inverse est également possible. Nous l'appelons backjoin. Prenons un autre exemple. Nous souhaitons récupérer tous les auteurs qui ont écrit plus de 3 livres. Pour que la jointure soit inversée, nous utilisons l'instruction : (colon). Colon means that the joined relationship means hasMany (and it's quite logical too, as two dots are more than one dot). Unfortunately, the Selection class isn't smart enough, so we have to help with the aggregation and provide a GROUP BY. La condition doit également être écrite sous la forme d'une instruction HAVING.

$authors = $explorer->table('author');
$authors->group('author.id')
	->having('COUNT(:book.id) > 3');

Vous avez peut-être remarqué que l'expression de jointure fait référence au livre, mais il n'est pas clair si nous faisons la jointure par author_id ou translator_id. Dans l'exemple ci-dessus, Selection fait la jointure par la colonne author_id parce qu'une correspondance avec la table source a été trouvée – la table author. Si une telle correspondance n'avait pas été trouvée et qu'il y avait d'autres possibilités, Nette aurait lancé AmbiguousReferenceKeyException.

Pour effectuer une jointure via la colonne translator_id, fournissez un paramètre facultatif dans l'expression de jointure.

$authors = $explorer->table('author');
$authors->group('author.id')
	->having('COUNT(:book(translator).id) > 3');

Examinons maintenant des expressions de jointure plus complexes.

Nous aimerions trouver tous les auteurs qui ont écrit quelque chose sur PHP. Tous les livres ont une étiquette, nous devons donc sélectionner les auteurs qui ont écrit un livre avec l'étiquette PHP.

$authors = $explorer->table('author');
$authors->where(':book:book_tags.tag.name', 'PHP')
	->group('author.id')
	->having('COUNT(:book:book_tags.tag.id) > 0');

Requêtes agrégées

La méthode count() sans aucun paramètre spécifié sélectionne tous les enregistrements et renvoie la taille du tableau, ce qui est très inefficace. Par exemple, si vous devez calculer le nombre de lignes pour la pagination, spécifiez toujours le premier argument.

Échappement et citation

Database Explorer est intelligent et échappe les paramètres et les identificateurs de citation pour vous. Ces règles de base doivent cependant être respectées :

• les mots-clés, fonctions, procédures doivent être en majuscules
• les colonnes et les tables doivent être en minuscules
• passer des variables comme paramètres, ne pas les concaténer

->where('name like ?', 'John'); // FAUX ! génère: `name` `like` ?
->where('name LIKE ?', 'John'); // CORRECT

->where('KEY = ?', $value); // FAUX ! KEY est un mot-clé
->where('key = ?', $value); // CORRECT. génère: `key` = ?

->where('name = ' . $name); // FAUX ! injection sql !
->where('name = ?', $name); // CORRECT

->select('DATE_FORMAT(created, "%d.%m.%Y")'); // FAUX ! passer des variables comme paramètres, ne pas concaténer
->select('DATE_FORMAT(created, ?)', '%d.%m.%Y'); // CORRECT

Une mauvaise utilisation peut entraîner des failles de sécurité

Récupération des données

Insertion, mise à jour et suppression

La méthode insert() accepte un tableau d'objets Traversables (par exemple ArrayHash qui renvoie des formes) :

$row = $explorer->table('users')->insert([
	'name' => $name,
	'year' => $year,
]);
// INSERT INTO users (`name`, `year`) VALUES ('Jim', 1978)

Si la clé primaire est définie sur la table, un objet ActiveRow contenant la ligne insérée est retourné.

Insertion multiple :

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

Des fichiers ou des objets DateTime peuvent être passés comme paramètres :

$explorer->table('users')->insert([
	'name' => $name,
	'created' => new DateTime, // ou $explorer::literal('NOW()')
	'avatar' => fopen('image.gif', 'r'), // insère le fichier
]);

Mise à jour (renvoie le nombre de lignes affectées) :

$count = $explorer->table('users')
	->where('id', 10) // doit être appelé avant update()
	->update([
		'name' => 'Ned Stark'
	]);
// UPDATE `users` SET `name`='Ned Stark' WHERE (`id` = 10)

Pour la mise à jour, nous pouvons utiliser les opérateurs += a -=:

$explorer->table('users')
	->update([
		'age+=' => 1, // voir +=
	]);
// UPDATE users SET `age` = `age` + 1

Suppression (renvoie le nombre de lignes supprimées) :

$count = $explorer->table('users')
	->where('id', 10)
	->delete();
// DELETE FROM `users` WHERE (`id` = 10)

Travailler avec des relations

A une relation

Une seule relation est un cas d'utilisation courant. Le livre a un auteur. Le livre a un traducteur. L'obtention d'une ligne liée se fait principalement par la méthode ref(). Elle accepte deux arguments : le nom de la table cible et la colonne de jonction source. Voir l'exemple :

$book = $explorer->table('book')->get(1);
$book->ref('author', 'author_id');

Dans l'exemple ci-dessus, nous récupérons l'entrée relative à l'auteur dans la table author, la clé primaire de l'auteur est recherchée dans la colonne book.author_id. La méthode Ref() retourne une instance ActiveRow ou null s'il n'y a pas d'entrée appropriée. La ligne retournée est une instance d'ActiveRow, nous pouvons donc travailler avec elle de la même manière qu'avec l'entrée du livre.

$author = $book->ref('author', 'author_id');
$author->name;
$author->born;

// ou directement
$book->ref('author', 'author_id')->name;
$book->ref('author', 'author_id')->born;

Le livre a aussi un traducteur, donc obtenir le nom du traducteur est assez facile.

$book->ref('author', 'translator_id')->name

Tout cela est bien, mais c'est un peu lourd, ne pensez-vous pas ? Database Explorer contient déjà les définitions des clés étrangères, alors pourquoi ne pas les utiliser automatiquement ? Faisons cela !

Si nous appelons une propriété qui n'existe pas, ActiveRow essaie de résoudre le nom de la propriété appelante comme une relation ‘has one’. Obtenir cette propriété est la même chose qu'appeler la méthode ref() avec un seul argument. Nous appellerons le seul argument key. La clé sera résolue en relation particulière de clé étrangère. La clé passée est comparée aux colonnes de la ligne, et si elle correspond, la clé étrangère définie sur la colonne correspondante est utilisée pour obtenir les données de la table cible correspondante. Voir l'exemple :

$book->author->name;
// identique à
$book->ref('author')->name;

L'instance ActiveRow n'a pas de colonne auteur. Toutes les colonnes de livres sont recherchées pour une correspondance avec key. Dans ce cas, la correspondance signifie que le nom de la colonne doit contenir la clé. Ainsi, dans l'exemple ci-dessus, la colonne author_id contient la chaîne ‘auteur’ et est donc recherchée par la clé ‘auteur’. Si vous voulez obtenir le traducteur du livre, vous pouvez utiliser par exemple ‘translator’ comme clé, car la clé ‘translator’ correspondra à la colonne translator_id. Vous trouverez plus d'informations sur la logique de correspondance des clés dans le chapitre sur les expressions de jointure.

echo $book->title . ': ';
echo $book->author->name;
if ($book->translator) {
	echo ' (translated by ' . $book->translator->name . ')';
}

Si vous souhaitez récupérer plusieurs livres, vous devez utiliser la même approche. Nette Database Explorer recherchera les auteurs et les traducteurs pour tous les livres recherchés en une seule fois.

$books = $explorer->table('book');
foreach ($books as $book) {
	echo $book->title . ': ';
	echo $book->author->name;
	if ($book->translator) {
		echo ' (translated by ' . $book->translator->name . ')';
	}
}

Le code n'exécutera que ces 3 requêtes :

SELECT * FROM `book`;
SELECT * FROM `author` WHERE (`id` IN (1, 2, 3)); -- ids of fetched books from author_id column
SELECT * FROM `author` WHERE (`id` IN (2, 3));    -- ids of fetched books from translator_id column

A plusieurs relations

La relation “has many” est une relation inversée “has one”. L'auteur a écrit beaucoup de livres. L'auteur a traduit beaucoup de livres. Comme vous pouvez le constater, ce type de relation est un peu plus difficile car la relation est “nommée” (“écrit”, “traduit”). L'instance ActiveRow possède la méthode related(), qui renvoie un tableau d'entrées liées. Les entrées sont également des instances ActiveRow. Voir l'exemple ci-dessous :

$author = $explorer->table('author')->get(11);
echo $author->name . ' has written:';

foreach ($author->related('book.author_id') as $book) {
	echo $book->title;
}

echo 'and translated:';
foreach ($author->related('book.translator_id') as $book) {
	echo $book->title;
}

Méthode related() La méthode accepte la description complète de la jointure passée comme deux arguments ou comme un argument joint par un point. Le premier argument est la table cible, le second est la colonne cible.

$author->related('book.translator_id');
// identique à
$author->related('book', 'translator_id');

Vous pouvez utiliser l'heuristique de Nette Database Explorer basée sur les clés étrangères et fournir uniquement l'argument key. La clé sera comparée à toutes les clés étrangères pointant vers la table actuelle (author table). S'il y a une correspondance, Nette Database Explorer utilisera cette clé étrangère, sinon, il lancera Nette\InvalidArgumentException ou AmbiguousReferenceKeyException. Vous pouvez trouver plus d'informations sur la logique de correspondance des clés dans le chapitre sur les expressions de jointure.

Bien sûr, vous pouvez appeler les méthodes connexes pour tous les auteurs récupérés, Nette Database Explorer récupérera à nouveau les livres appropriés en une seule fois.

$authors = $explorer->table('author');
foreach ($authors as $author) {
	echo $author->name . ' has written:';
	foreach ($author->related('book') as $book) {
		$book->title;
	}
}

L'exemple ci-dessus n'exécutera que deux requêtes :

SELECT * FROM `author`;
SELECT * FROM `book` WHERE (`author_id` IN (1, 2, 3)); -- ids of fetched authors

Création manuelle de l'explorateur

Une connexion à la base de données peut être créée en utilisant la configuration de l'application. Dans ce cas, un service Nette\Database\Explorer est créé et peut être transmis comme dépendance à l'aide du conteneur DI.

Cependant, si Nette Database Explorer est utilisé comme un outil autonome, une instance de l'objet Nette\Database\Explorer doit être créée manuellement.

// $storage implémente Nette\Caching\Storage:
$storage = new Nette\Caching\Storages\FileStorage($tempDir);
$connection = new Nette\Database\Connection($dsn, $user, $password);
$structure = new Nette\Database\Structure($connection, $storage);
$conventions = new Nette\Database\Conventions\DiscoveredConventions($structure);
$explorer = new Nette\Database\Explorer($connection, $structure, $conventions, $storage);