Subversion Repositories eFlore/Applications.del

 *

 * Backend pour PictoFlora (del.html#page_recherche_images)

 *

 *

 * == Notes ==

 *

 * tri=votes et tri=tags: affectent le choix des images affichées (donc getIdImages())

 * Cependant ce total ne nous intéresse même pas (MoyenneVotePresenteur.java s'en occupe).

 * Seul tri=date_transmission nous évite l'AVG() + GROUP BY

 *

 * protocole: il affecte l'affichage des information, mais le JSON contient déjà

 * l'intégralité (chercher les données de vote pour 1 ou plusieurs protocoles) est quasi-identique.

 * Par contre, le tri par moyenne des votes, sous-entend "pour un protocole donné".

 * Dès lors le choix d'un protocole doit avoir été fait afin de régler le JOIN et ainsi l'ORDER BY.

 * (cf requestFilterParams())

 *

 * Histoire: auparavant (pré-r142x) un AVG + GROUP BY étaient utilisés pour générer on-the-fly les valeurs

 * utilsées ensuite pour l'ORDER BY. La situation à base de del_image_stat

 * est déjà bien meilleur sans être pour autant optimale. cf commentaire de sqlAddConstraint()

 *

 *

 * Tags:

 * Le comportement habituel dans le masque *général*: les mots sont séparés par des espaces,

 * implod()ed par des AND (tous les mots doivent matcher).

 * Et le test effectué doit matcher sur:

 * %(les tags d'observations)% *OU* %(les tags d'images)% *OU* %(les tags publics)%

 *

 * Le comportement habituel dans le masque *tag*: les mots ne sont *pas* splittés (1 seule expression),

 * Et le test effectué doit matcher sur:

 * ^(expression)% *OU* %(expression)% [cf getConditionsImages()]

 *

 * Par défaut les tags sont comma-separated (OU logique).

 * Cependant pour conserver le comportement du masque général qui sous-entend un ET logique sur

 * des tags séparés par des espaces recherche 

 *

 * TODO:

 * -affiner la gestion de passage de mots-clefs dans le masque général.

 * - subqueries dans le FROM pour les critère WHERE portant directement sur v_del_image

 * plutôt que dans WHERE (qui nécessite dès lors un FULL-JOIN)

 * (http://www.mysqlperformanceblog.com/2007/04/06/using-delayed-join-to-optimize-count-and-limit-queries/)

 * - éviter de dépendre d'une jointure systématique sur `cel_obs`, uniquement pour `(date_)transmission

 * (cf VIEW del_image)

 * - réorganiser les méthodes statiques parmis Observation, ListeObservations2 et ListImages2

 * - *peut-être*: passer requestFilterParams() en méthode de classe

 *

error_reporting(E_ALL);

// del/services/0.1/images?navigation.depart=0&navigation.limite=12&tri=votes&ordre=desc

// del/services/0.1/images?navigation.depart=0&navigation.limite=12&tri=votes&ordre=desc&masque=plop

// del/services/0.1/images?navigation.depart=0&navigation.limite=12&tri=votes&ordre=desc&protocole=3

class ListeImages2 {

	static $format_image_possible = array('O','CRX2S','CRS','CXS','CS','XS','S','M','L','XL','X2L','X3L');

	static $tri_possible = array('date_observation', 'votes', 'tags');

	// en plus de ceux dans ListeObservations2

	static $parametres_autorises = array('protocole', 'masque.tag_cel', 'masque.tag_pictoflora', 'masque.milieu');

	static $default_params = array('navigation.depart' => 0, 'navigation.limite' => 10,

								   'tri' => 'date_transmission', 'ordre' => 'desc',

								   // spécifiques à PictoFlora:

								   'format' => 'XL');

	static $default_proto = 3; // proto par défaut: capitalisation d'img (utilisé uniquement pour tri=(tags|votes))

	static $mappings = array(

		'observations' => array( // v_del_image

			"id_observation" => 1,

			"date_observation" => 1,

			"date_transmission" => 1, 

			"famille" => "determination.famille",

			"nom_sel" => "determination.ns",

			"nom_sel_nn" => "determination.nn",

			"nom_referentiel" => "determination.referentiel",

			"nt" => "determination.nt",

			"ce_zone_geo" => "id_zone_geo",

			"zone_geo" => 1,

			"lieudit" => 1,

			"station" => 1,

			"milieu" => 1,

			"mots_cles_texte" => "mots_cles_texte",

			"commentaire" => 1,

			"ce_utilisateur" => "auteur.id",

			"nom_utilisateur" => "auteur.nom",

			"prenom_utilisateur" => "auteur.prenom",

		),

		'images' => array( // v_del_image

			'id_image' => 1,

			// l'alias suivant est particulier: in-fine il doit s'appeler mots_cles_texte

		$this->gestionBdd = $conteneur->getGestionBdd();

	}

	public function consulter($ressources, $parametres) {

		   Par contre, ci-dessous nous prenons i_(nom|prenom|courriel)_utilisateur.

		   Notons cependant qu'aucun moyen ne devrait permettre que i_*_utilisateur != *_utilisateur

		   Le propriétaire d'une obs et de l'image associée est *toujours* le même. */

		array_walk(self::$mappings['images'], create_function('&$val, $k', 'if($val==1) $val = $k;'));

		// pour les votes, les mappings de "Observation" nous suffisent

		array_walk(Observation::$mappings['votes'], create_function('&$val, $k', 'if($val==1) $val = $k;'));

		// la nécessité du 'groupby' dépend des 'join's utilisés (LEFT ou INNER) ainsi que de la cardinalité

		// de `ce_image` dans ces tables jointes.

		// Contrairement à IdentiPlantes, nous n'avons de HAVING pour PictoFlora, mais par contre un ORDER BY

		$db = $this->bdd;

		// filtrage de l'INPUT général, on réutilise 90% de identiplante en terme de paramètres autorisés

		// ($parametres_autorises) sauf... masque.type qui fait des modif' de WHERE sur les mots-clefs.

		// Évitons ce genre de chose pour PictoFlora et les risques de conflits avec masque.tag

		// même si ceux-ci sont improbables (pas d'<input> pour cela).

		$params_ip = ListeObservations2::requestFilterParams($parametres,

														  array_diff(ListeObservations2::$parametres_autorises,

																	 array('masque.type')),

														  $this->conteneur);

		// Cette variable est utile au filtrage des tags

		// Elle peut-être redéfinie en interne par sqlAddMasqueConstraint() ci-dessous

		// lorsque celui-ci transforme le masque général en masque par champ

		// et ré-appelle self::requestFilterParams()

		// tag_explode_semantic est lui utilisé lors de l'ajout des contraintes sur tags self::sqlAddConstraint()

		$parametres['tag_explode_semantic'] = 'OR'; // "AND" ou "OR" (si 'tag_explode_char' NOT NULL)

		// notre propre filtrage sur l'INPUT

		$params_pf = self::requestFilterParams($parametres,

		$params = array_merge(ListeObservations2::$default_params, // paramètre par défaut Identiplante

							  self::$default_params, // paramètres par défaut PictoFlora

							  $params_ip, // les paramètres passés, traités par Identiplante

							  $params_pf); // les paramètres passés, traités par PictoFlora

		// XXX: temp tweak

		// création des contraintes (génériques, de ListeObservations2)

		// création des contraintes spécifiques impliquées par le masque général

		// l'ORDER BY s'avére complexe

		self::sqlOrderBy($params, $db, $req);

		// 1) grunt-work: *la* requête de récupération des id valides (+ SQL_CALC_FOUND_ROWS)

		// $idobs_tab = ListeObservations2::getIdObs($params, $req, $db);

		$idobs_tab = self::getIdImages($params, $req, $db);

		// Ce n'est pas la peine de continuer s'il n'y a pas eu de résultats dans la table del_obs_images

		if(!$idobs_tab) {

			header('HTTP/1.0 404 Not Found');

			// don't die (phpunit)

			throw(new Exception());

		}

		// idobs est une liste (toujours ordonnée) des id d'observations recherchées

		$idobs = array_values(array_map(create_function('$a', 'return $a["id_image"];'), $idobs_tab));

		$total = $db->recuperer('SELECT FOUND_ROWS() AS c'); $total = intval($total['c']);

		$liaisons = self::chargerImages($db, $idobs);

		/* 

		   // Q&D

		   $images = array();

		   $o = new Observation($this->conteneur);

		   foreach($idobs as $i) {

		   $images[$i] = $o->consulter(array($i), array('justthrow' => 1));

		   }

		*/

		// on charge les votes pour ces images et pour *tous* les protocoles

		$votes = Observation::chargerVotesImage($db, $liaisons, NULL);

		// subtilité, nous passons ici le tableau d'images indexé par id_image qui est bien plus pratique pour

		// associer les vote à un tableau, puisque nous ne connaissons pas les id d'observation.

		// Mais magiquement (par référence), cela va remplir notre tableau indexé par couple d'id (id_image, id_observation)

		// cf reformateImagesDoubleIndex() à qui revient la tâche de créer ces deux versions simultanément lorsque

		// c'est encore possible.

		if($votes) Observation::mapVotesToImages($votes, $images_keyed_by_id_image);

		$resultat = new ResultatService();

		$resultat->corps = array('entete' => ListeObservations2::makeJSONHeader($total, $params, Config::get('url_service')),

								 'resultats' => $images);

		return $resultat;

	}

	/**

	 * TODO: partie spécifique liées à la complexité de PictoFlora:

	 * génération de la clause ORDER BY (génère la valeur de la clef orderby' de $req)

	 * nécessaire ? tableau sprintf(key (tri) => value (ordre), key => value ...).

	 * Cependant il est impensable de joindre sur un AVG() des valeurs des votes pour

	 * *chaque* couple (id_image, protocole) de la base afin de trouver les images

	 * les "mieux notées", ou bien les images ayant le "plus de tags" (COUNT())

	 */

	static function sqlOrderBy($p, $db, &$req) {

		// parmi self::$tri_possible

		if($p['tri'] == 'votes') { // LEFT JOIN sur "dis" ci-dessous

			$req['orderby'] = 'dis.moyenne ' . $p['ordre'];

			return;

		}

		if($p['tri'] == 'tags') { // LEFT JOIN sur "dis" ci-dessous

			$req['orderby'] = 'dis.nb_tags ' . $p['ordre'];

			return;

		}

		// tri == 'date_transmission'

		$req['orderby'] = 'date_transmission ' . $p['ordre'];

		// avant cel:r1860, date_transmission pouvait être NULL

		// or nous voulons de la consistence (notamment pour phpunit)

		$req['orderby'] .= ', id_observation ' . $p['ordre'];

	}

	/*

	 * in $p: un tableau de paramètres, dont:

	 * - 'masque.tag_cel': *tableau* de mots-clefs à chercher parmi cel_image.mots_clefs_texte

	 * - 'masque.tag_pictoflora': *tableau* de mots-clefs à chercher parmi del_image_tag.tag_normalise

	 * - 'tag_explode_semantic': défini si les éléments sont tous recherchés ou NON

	 *

	 * in/ou: $req: un tableau de structure de requête MySQL

	 *

	 * Attention, le fait que nous cherchions masque.tag_cel OU/ET masque.tag_cel

	 * ne dépend pas de nous, mais du niveau supérieur de construction de la requête:

	 * Soit directement $this->consulter() si des masque.tag* sont passés

	 * (split sur ",", "AND" entre chaque condition, "OR" pour chaque valeur de tag)

	 * Soit via sqlAddMasqueConstraint():

	 * (pas de split, "OR" entre chaque condition) [ comportement historique ]

	 * équivalent à:

	 * (split sur " ", "OR" entre chaque condition, "AND" pour chaque valeur de tag)

	 *

	 */

	static function sqlAddConstraint($p, $db, &$req, Conteneur $c = NULL) {

		// TODO implement dans ListeObservations2 ?

		if(!empty($p['masque.milieu'])) {

			$req['where'][] = 'vdi.milieu LIKE '.$db->proteger('%' . $p['masque.milieu'].'%');

		}

		/* Pour le tri par AVG() des votes nous avons toujours un protocole donné,

		   celui-ci indique sur quels votes porte l'AVG.

		   (c'est un *vote* qui porte sur un protocole et non l'image elle-même) */

		/* TODO: perf problème:

		   1) SQL_CALC_FOUND_ROWS: fixable en:

		   	- dissociant le comptage de la récup d'id + javascript async

			- ou ne rafraîchir le total *que* pour les requête impliquant un changement de pagination

				(paramètre booléen "with-total" par exemple)

		   2) jointure forcées: en utilisant `del_imagè`, nous forçons les 2 premiers

		   	JOIN sur cel_obs_images et cel_obs pour filtrer sur "transmission".

			Dénormaliser cette valeur et l'intégrer à `cel_images` ferait économiser cette couteuse

			jointure, ... lorsqu'aucun masque portant sur `cel_obs` n'est utilisé

		   3) non-problème: l'ordre des joins est forcé par l'usage de la vue:

		   	(cel_images/cel_obs_images/cel_obs/del_image_stat)

			Cependant c'est à l'optimiseur de définir son ordre préféré. */

		if($p['tri'] == 'votes') {

			// $p['protocole'] *est* défini (cf requestFilterParams())

			// petite optimisation: INNER JOIN si ordre DESC car les 0 à la fin

			if($p['ordre'] == 'desc') {

				// pas de group by nécessaire pour cette jointure

				// PRIMARY KEY (`ce_image`, `ce_protocole`)

				$req['join'][] = sprintf('INNER JOIN del_image_stat dis'.

										 ' ON vdi.id_image = dis.ce_image'.

										 ' AND dis.ce_protocole = %d',

										 $p['protocole']);

			} else {

				$req['join'][] = sprintf('LEFT JOIN del_image_stat dis'.

										 ' ON vdi.id_image = dis.ce_image'.

										 ' AND dis.ce_protocole = %d',

										 $p['protocole']);

				// nécessaire (dup ce_image dans del_image_stat)

				$req['groupby'][] = 'vdi.id_observation';

			}

		}

		if($p['tri'] == 'tags') {

			$req['join'][] = sprintf('%s JOIN del_image_stat dis ON vdi.id_image = dis.ce_image',

									 ($p['ordre'] == 'desc') ? 'INNER' : 'LEFT');

			// nécessaire (dup ce_image dans del_image_stat)

			$req['groupby'][] = 'vdi.id_observation';

		}

		// TODO: support du "ET", "OU", "(", ")"

		// http://codehackit.blogspot.fr/2011/08/expression-parser-in-php.html

		// http://blog.angeloff.name/post/2012/08/05/php-recursive-patterns/

		if(!empty($p['masque.tag_cel'])) {

			if($p['tag_explode_semantic'] == 'AND') {

				// TODO: utiliser les tables de mots clefs normaliées dans tb_cel ?

				// et auquel cas laisser au client le choix du couteux "%" ?

				$tags = $p['masque.tag_cel'];

				array_walk($tags, create_function('&$val, $k, $db',

												  '$val = sprintf("CONCAT(vdi.mots_cles_texte,vdi.i_mots_cles_texte) LIKE %s",

 																  $db->proteger("%".$val."%"));'),

						   $db);

				$req['where'][] = implode(' AND ', $tags);

			}

			else { // OR assumed

				$req['where'][] = sprintf("CONCAT(vdi.mots_cles_texte,vdi.mots_cles_texte) REGEXP %s",

										  $db->proteger(implode('|', $p['masque.tag_cel'])));

			}

		}

		// XXX: utiliser tag plutôt que tag_normalise ?

		if(!empty($p['masque.tag_pictoflora'])) {

			// pas de LEFT JOIN ? ou bien peut-être en cas de tri, mais nous parlons bien ici d'un masque

			/* $req['join'][] = 'LEFT JOIN del_image_tag dit ON dit.ce_image = vdi.id_image';

			   $req['where'][] = 'dit.actif = 1'; */

			if($p['tag_explode_semantic'] == 'AND') {

				// optimsation: en cas de "AND" on sort() l'input et le GROUP_CONCAT()

				// donc nous utilisons des ".*" plutôt que de multiples conditions et "|"

				sort($p['masque.tag_pictoflora']);

				$req['where'][] = sprintf("vdi.id_image IN (SELECT ce_image FROM del_image_tag WHERE actif = 1".

										  " GROUP BY ce_image".

										  " HAVING GROUP_CONCAT(tag_normalise ORDER BY tag_normalise) REGEXP %s)",

										  $db->proteger(implode('.*', $p['masque.tag_pictoflora'])));

			}

			else { // OR assumed

				$req['where'][] = sprintf("vdi.id_image IN (SELECT ce_image FROM del_image_tag WHERE actif = 1".

										  " GROUP BY ce_image".

										  " HAVING GROUP_CONCAT(tag_normalise) REGEXP %s)",

										  $db->proteger(implode('|', $p['masque.tag_pictoflora'])));

			}

		}

	}

	static function getIdImages($p, $req, $db) {

		return $db->recupererTous(sprintf(

			'SELECT SQL_CALC_FOUND_ROWS id_image' .

			' FROM v_del_image vdi'.

			' %s' . // LEFT JOIN if any

			' WHERE %s'. // where-clause ou TRUE

			' %s'. // group-by

			' ORDER BY %s'.

			' LIMIT %d, %d -- %s',

			$req['join'] ? implode(' ', array_unique($req['join'])) : '',

			$req['where'] ? implode(' AND ', $req['where']) : 'TRUE',

			$req['groupby'] ? ('GROUP BY ' . implode(', ', array_unique($req['groupby']))) : '',

			$req['orderby'],

			$p['navigation.depart'], $p['navigation.limite'], __FILE__ . ':' . __LINE__));

	}

	static function chargerImages($db, $idImg) {

		$obs_fields = Observation::sqlFieldsToAlias(self::$mappings['observations'], NULL);

		$image_fields = Observation::sqlFieldsToAlias(self::$mappings['images'], NULL);

		return $db->recupererTous(sprintf('SELECT '.

										  ' CONCAT(id_image, "-", id_observation) AS jsonindex,'.

										  ' %1$s, %2$s FROM v_del_image '.

										  ' WHERE %3$s'.

										  ' -- %4$s',

										  $obs_fields, $image_fields,

										  sprintf('id_image IN (%s)', implode(',', $idImg)),

										  __FILE__ . ':' . __LINE__));

	}

	/* "masque" ne fait jamais que faire une requête sur la plupart des champs, (presque) tous traités

	   de manière identique à la seule différence que:

	   1) ils sont combinés par des "OU" logiques plutôt que des "ET".

	   2) les tags sont traités différemment pour conserver la compatibilité avec l'utilisation historique:

	   	  Tous les mots-clefs doivent matcher et sont séparés par des espaces

		  (dit autrement, str_replace(" ", ".*", $mots-clefs-requête) =~ $mots-clefs-mysql)

	   Pour plus d'information: ListeObservations2::sqlAddMasqueConstraint() */

	static function sqlAddMasqueConstraint($p, $db, &$req, Conteneur $c = NULL) {

		if(!empty($p['masque'])) {

			$or_params = array('masque.auteur' => $p['masque'],

							   'masque.departement' => $p['masque'],

							   'masque.commune' => $p['masque'], // TODO/XXX ?

							   'masque.id_zone_geo' => $p['masque'],

							   // tous-deux remplacent masque.tag

							   'masque.tag_cel' => $p['masque'],

							   'masque.tag_pictoflora' => $p['masque'],

							   'masque.ns' => $p['masque'],

							   'masque.famille' => $p['masque'],

							   'masque.date' => $p['masque'],

							   'masque.genre' => $p['masque'],

							   'masque.milieu' => $p['masque'],

							   // tri est aussi nécessaire car affecte les contraintes de JOIN

							   'tri' => $p['tri'],

							   'ordre' => $p['ordre']);

			$or_masque = array_merge(

				self::requestFilterParams($or_params),

				array('tag_explode_char' => ' ', 'tag_explode_semantic' => 'AND')); // spéciaux

			// pas de select, groupby & co ici

			$or_req = array('join' => array(), 'where' => array());

			ListeObservations2::sqlAddConstraint($or_masque, $db, $or_req);

			if($or_req['where']) {

				$req['where'][] = '(' . implode(' OR ', $or_req['where']) . ')';

				// utile au cas ou des jointures seraient rajoutées

				$req['join'] = array_unique(array_merge($req['join'], $or_req['join']));

			}

		}

	}

	// complete & override ListeObservations2::requestFilterParams()

	static function requestFilterParams(Array $params, $parametres_autorises = NULL) {

		if($parametres_autorises) { // filtrage de toute clef inconnue

			$params = array_intersect_key($params, array_flip($parametres_autorises));

		}

		$p = array();

		$p['tri'] = ListeObservations2::unsetIfInvalid($params, 'tri', self::$tri_possible);

		$p['format'] = ListeObservations2::unsetIfInvalid($params, 'format', self::$format_image_possible);

		// et pour identiplantes ?

		if(isset($params['masque.milieu'])) $p['masque.milieu'] = trim($params['masque.milieu']);

		// compatibilité

		if(isset($params['masque.tag'])) {

			$params['masque.tag_cel'] = $params['masque.tag_pictoflora'] = $params['masque.tag'];

		}

		/* Nous n'implod()ons pas ici, car la sémantique des éléments multiples est

		   variable (tag_explode_semantic) et considérée plus tard, notamment lors de la

		   construction du SQL (sqlAddConstraint()).

		   Cette sémantique peut varier selon que ces tags proviennent du formulaire de

		   recherche avancée ou via le masque général (et l'appel récursif à requestFilterParams()

		   qu'il implique) */

		if(isset($params['masque.tag_cel'])) {

			if(isset($params['tag_explode_char'])) {

				$x = explode($params['tag_explode_char'], $params['masque.tag_cel']);

				$x = array_map('trim', $x);

				$p['masque.tag_cel'] = array_filter($x);

			} else {

				// toujours un tableau

				$p['masque.tag_cel'] = array(trim($params['masque.tag_cel']));

			}

		}

		if(isset($params['masque.tag_pictoflora'])) {

				$x = explode($params['tag_explode_char'], $params['masque.tag_pictoflora']);

				$x = array_map('trim', $x);

				$p['masque.tag_pictoflora'] = array_filter($x);

			} else {

				// toujours un tableau

				$p['masque.tag_pictoflora'] = array(trim($params['masque.tag_pictoflora']));

		if($p['tri'] == 'votes' || $p['tri'] == 'tags') {

			// ces critère de tri des image à privilégier ne s'applique qu'à un protocole donné

			if(!isset($params['protocole']) || !is_numeric($params['protocole']))

				$p['protocole'] = self::$default_proto;

			else

				$p['protocole'] = intval($params['protocole']);

		}

		return array_filter($p, create_function('$a','return !in_array($a, array("",false,null),true);'));

	}