Archives par mot-clé : SQL

Les requêtes imbriquées c’est (parfois) le pied

Publié dans SQL | Laisser un commentaire

J’ai une table annonce1 contenant 10 000 tuples, et une autre annonce2 qui en contient aussi 10 000. Tous les tuples sont différents. Le but ? Fusionner ces 2 tables. Export/Import classique créé des problèmes de Primary Key qui se dupliquent (mon ID est en auto_increment).
Moralité :

INSERT INTO annonce (ann_surface, ann_prix)
	SELECT ann_surface, ann_prix
	FROM annonce2
	WHERE ann_surface>150 AND ann_ville!=0;

Une petite requête SQL imbriquée (embedded) et pof le tour est joué !
Deux choses importantes à remarquer :

  • La syntaxe : le VALUES n’apparait pas ici. (moi je m’étais fait avoir)
  • Inutile de préciser de préfixes même si le champ des tables ont le même nom. D’ailleurs j’ai testé avec annonce1 a1 (a1.ann_surface, ... et cela n’a pas fonctionné. J’en déduis que les préfixes doivent être interdit avec la commande INSERT.

La doc MySQL n’est pas avare à propos de la commande INSERT, pas plus que les commentaires. Profitons-en !

Comment importer une grosse base de données

Publié dans SQL | Un commentaire

Ce billet aurait aussi pu se nommer « Vive la ligne de commandes ! ».
Je pense arriver au bout de mes peines avec ma grosse tables de 3 millions de tuples (qui n’en fait d’ailleurs toujours pas 3 millions, alors j’espère que ça tiendra le coup quand tous les tuples seront là), et j’ai notamment pas mal galéré pour la transférer d’une base de données à une autre. (local -> serveur de développement) Si on utilise PHP, ou PhpMyAdmin, pour transférer sa BDD, on se retrouve vite avec de très long temps d’attente et avec une machine qui rame jusqu’à la mort. Quelques idées pour survivre entier à ce calvaire :
sql

  • Préférer le transfert à l’aide de fichiers. Car le copier/coller de requêtes fonctionnent pour de petites tables, mais ça fait très vite ramer votre machine de course pour de grosses tables. Il suffit d’exporter les tables de son choix via PhpMyAdmin (à l’accueil d’une base > exporter : pour exporter les tables de son choix. A l’accueil d’une table > exporter : pour exporter directement la table en question), puis dans les importer via PhpMyAdmin, ou en ligne de commandes. Il vaut mieux éviter de cocher la case « Insertion Complète » qui créé une seule requête INSERT INTO table (val1, val2) VALUES ('blabla', 'bloublou'), ('blibli', 'bleble'), ..., ('blublu', 'bloblo'); ce qui peut être un peu trop lourd. Mieux vaut créer autant de requêtes qu’il y a d’insertions dans ce cas. (le mieux serait de faire une requête pour une trentaine d’insertions, c’est vraiment plus rapide, mais cela oblige à coder tout ça soit même. Edit: apparemment, il faut choisir « Insertions Etentdues ».)
  • Préférer l’utilisation des lignes de commandes. Pour plusieurs raisons : plus de problèmes avec les timeout de MySQL ou de PHP puisqu’on effectue une commande à la fois. (sauf si on commande est trop lourde. Dans le cas d’une insertion complète par exemple), on contrôle réellement tout ce qui se passe en temps réel et on peut réellement tout arrêter en temps réel. (suffit de fermer le terminal quoi !), on ne passe par aucun intermédiaire et c’est donc vraiment plus rapide.

Continuer la lecture

Tout ce qu’il faut savoir pour crawler tranquilement

Publié dans PHP | Laisser un commentaire

Ah, avant toute chose qu’est-ce que j’entends par le mot « crawler » ? On pourrait appeler ça « scrapper », « looter », voir même « pirater ». En fait l’idée est de créer un petit programme dont le but va être de récupérer des informations sur un site Web qui se trouve en ligne (ou pas). Exactement comme fait le GoogleBot ou les autres robots de moteurs de recherche. Et comme font aussi les robots qui cherchent des adresses e-mails pour faire du spam… Bref, on voit qu’il peut y avoir un petit problème éthique derrière le scrolling, mais c’est une discipline qui peut être tout à fait noble et utile autant pour le crawleur que pour le crawlé. Mais je ne vais pas faire de débat philosophique, plutôt parler technique.
Durant le stage que j’effectue actuellement, j’ai pas mal l’occasion de crawler des sites Web, voici donc un résumé de fonctions ou de techniques qui peuvent être utiles.

Le crawling en résumé

L’idée principale est de faire automatiquement rapidement ce que l’on aurait dû faire à la main en mettant beaucoup de temps. Pour cela, on fait une petite étude des urls du site que l’on souhaite crawler. Par exemple, si c’est une liste d’article, les pages de chaque article auront peut-être un url avec quelque chose du genre : article.php?id=1452112. A partir de la :

 $url = 'article.php?id={id}'; for($i=1; $i<=$nbArticle; $i++) {
	$urlAScroller = str_replace('{id}', $i, $url);
	$content = file_get_contents($urlAScroller);
	// Regex et explode sur $content pour récupérer les informations souhaitées }

Continuer la lecture

Cahier des charges : Doodle like en PHP objet

Publié dans Projets | 2 commentaires

Bon, il me semble que j’ai fait un bon tour de la théorie au sujet du PHP objet (j’ai encore au moins 2 billets théoriques à écrire), et je l’applique déjà depuis plusieurs semaines durant mon stage. Mais maintenant j’aimerai bien me faire un petit projet tout en PHP objet. Je ne sais pas si vous connaissez Doodle ? Je trouve le concept vraiment génial. Ce site permet à n’importe qui de créer des sondages classiques, ou alors des sortes de sondages permettant de fixer facilement une date d’un rendez-vous pour autant de personnes que l’on souhaite. Je vais donc réaliser dans les jours à venir (et j’espère arriver aux termes de ce projet) un Doodle like (j’ai pas encore d’idées de nom… mais ça viendra) !
Voici donc un cahier des charges d’un Doodle like.

Feuille de route

  • Version tout en PHP (orienté objet) sans aucun Javascript ou autre. Fonctionnalité : « Trouver une date ».
  • Ajout du Javascript pour la fonctionnalité « Trouver une date » et le reste du site.
  • Ajout de fonctionnalités « Faire un choix », « Sondage », « Qui prend quoi, pour un repas », ou autre, et amélioration des fonctionnalités présentes. En PHP (orienté objet) d’abord puis en améliorant avec le Javascript.

Continuer la lecture

Optimiser ses requêtes MySQL

Publié dans SQL | 4 commentaires

Je travaille actuellement sur une table MySQL qui va contenir près de 7 millions de tuples (avec une quinzaine de colonnes chacun bien sûr, ce ne serait pas drôle sinon !), il se trouve qu’elle en contient déjà près de 3 millions, et que je suis obligé de faire une vingtaine de requêtes différentes dans cette table pour chaque page et que, par conséquent, le site qui l’utilise plante à fond. La mémoire utilisable par MySQL est dépassée. Alors certes, on peut modifier la taille de la mémoire utilisable par MySQL (et il faudrait aussi sûrement modifier la durée maximum d’exécution d’un script PHP), mais le but c’est quand même de charger des pages en moins de 30 secondes. J’ai donc cherché des réponses sur le Web et auprès des développeurs de ma boîte. Voici donc quelques points que j’ai pu retenir.

Structure de la table

Il vaut mieux éviter, sauf si c’est nécessaire, l’utilisation de champ NULL par défaut. En effet, comme on peut le voir sur cet article d’Apprendre PHP NULL est une valeur spéciale qui nécessite un traitement spécial, MySQL ne peut pas le tester avec un égal et ne peut le comparer à la chaine vide ou à 0, il est obligé d’utiliser IS ou le symbole équivalent <=>. NULL par défaut est d’autant plus à bannir pour les index. Utiliser des colonnes avec le meilleur type et la meilleure taille possible. Un prénom n’a par exemple par besoin d’être en VARCHAR(255), un VARCHAR(20) suffit plus que largement. Une bonne technique pour optimiser ses tables est d’utiliser PROCEDURE ANALYSE(). On créé ses tables au feeling (je parle juste de la structure, vous ne couperez pas à la normalisation tout ça, héhé), on les remplie avec un contenu à peu près définitif est on utilise une requête du style :

SELECT id, titre, message FROM blog PROCEDURE ANALYSE()

MySQL va nous répondre un joli petit array avec la taille max et min de chaque colonne id, titre et message, quelques autres informations intéressantes et surtout une proposition de type(taille) optimisée à votre table. Encore une fois, c’est l’article d’Apprendre PHP qui en montre un exemple vraiment parlant.
Continuer la lecture

Une seule et même fonction pour différents systèmes de gestion de base de données

Publié dans PHP objet | 3 commentaires

Je suis depuis longtemps déjà un féru utilisateur de FluxBB (anciennement PunBB), un système de forum bien fichu, valide W3C, très léger et donc configurable à souhait. En ce moment ce projet open source bat un peu de l’aile, mais de nouvelles mises à jours sortent encore assez régulièrement et la nouvelle version (attendue depuis des années) semble de nouveau repartir sur de bonnes bases. Mais bref, je parle de cela car FluxBB gère les bases de données MySQL, PostgreSQL (ou PgSQL) et même SQLite je crois. Pour se faire, au lieu d’avoir un switch ou des if else à chaque requête à effectuer, ils ont créé une class qui regroupe toutes les fonctions utiles (connect, query, fetch_array, …) et dans le code ils y font appelle après avoir instanciée cette class : $db->query, $db->fetch_array. Reste à sélectionner le type de sa base de données dans la partie administration et hop, on change de SGBD sans rien changer au code ! Pratique ! Je n’ai pas encore fini d’apprendre le PHP objet, mais j’avais envi de pratiquer un peu, alors sans regarder ailleurs, j’ai commencé à coder. Et voici une première version. Ma classe oublie surement pas mal de fonctions, et je n’ai pas pu la tester partout, et puis il manque la gestion des erreurs, j’aurai pu ajouter un compteur de requêtes, mais c’est ma première véritable class, alors soyez indulgent ^^

<?php
class Sgbd {
	public $nom;
	public function __construct($nom) {
		$this->nom = $nom;
	}
	public function connect($hote, $port, $nomBdd, $utilisateur, $mdp) {
		switch ($this->nom) {
			case 'pg':
			$requete = 'host='.$hote.' port='.($port == NULL ? '5432' : $port).' dbname='.$nomBdd.' user='.$utilisateur.' password='.$mdp;
			pg_connect($requete);
			break;
			default:
			mysql_connect($hote.($port == NULL ? ':3307' : ':'.$port), $utilisateur, $mdp);
			mysql_select_db($nomBdd);
		}
	}
	public function close() {
		switch ($this->nom) {
			case 'pg':
			pg_close();
			break;
			default:
			mysql_close();
		}
	}
	public function query($requete) {
		switch ($this->nom) {
			case 'pg':
			$result = pg_query($requete);
			break;
			default:
			$result = mysql_query($requete);
		}
		return $result;
	}
	public function fetch_array($result) {
		switch ($this->nom) {
			case 'pg':
			$arr = pg_fetch_array($result);
			break;
			default:
			$arr = mysql_fetch_array($result, MYSQL_BOTH);
		}
		return $arr;
	}
	public function fetch_row($result) {
		switch ($this->nom) {
			case 'pg':
			$row = pg_fetch_row($result);
			break;
			default:
			$row = mysql_fetch_row($result);
		}
		return $row;
	}
	public function num_rows($result) {
		switch ($this->nom) {
			case 'pg':
			$num_rows = pg_num_rows($result);
			break;
			default:
			$num_rows = mysql_num_rows($result);
		}
		return $num_rows;
	}
}
$sgbd = new Sgbd('mysql');
?>