Rev 1390 | Blame | Last modification | View Log | RSS feed
<?php/*** @author Raphaël Droz <raphael@tela-botanica.org>* @copyright Copyright (c) 2013, Tela Botanica (accueil@tela-botanica.org)* @license http://www.cecill.info/licences/Licence_CeCILL_V2-fr.txt Licence CECILL* @license http://www.gnu.org/licenses/gpl.html Licence GNU-GPL* @see http://www.tela-botanica.org/wikini/eflore/wakka.php?wiki=ApiIdentiplante01Images** 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**/require_once(dirname(__FILE__) . '/../observations/ListeObservations2.php');require_once(dirname(__FILE__) . '/../observations/Observation.php');restore_error_handler();restore_exception_handler();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// del/services/0.1/images?navigation.depart=0&navigation.limite=12&tri=votes&ordre=desc&protocole=3&masque=plopclass 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 ListeObservations2static $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// mais nous afin d'éviter un conflit d'alias nous le renommons plus tard (reformateImagesDoubleIndex)'i_mots_cles_texte' => 1));public function __construct(Conteneur $conteneur = null) {$this->conteneur = $conteneur == null ? new Conteneur() : $conteneur;$this->conteneur->chargerConfiguration('config_images.ini');$this->gestionBdd = $conteneur->getGestionBdd();$this->bdd = $this->gestionBdd->getBdd();}public function consulter($ressources, $parametres) {/* Certes nous sélectionnons ici (nom|prenom|courriel)_utilisateur de cel_obs, mais il ne nous intéressent pasPar contre, ci-dessous nous prenons i_(nom|prenom|courriel)_utilisateur.Notons cependant qu'aucun moyen ne devrait permettre que i_*_utilisateur != *_utilisateurLe propriétaire d'une obs et de l'image associée est *toujours* le même. */array_walk(self::$mappings['observations'], create_function('&$val, $k', 'if($val==1) $val = $k;'));array_walk(self::$mappings['images'], create_function('&$val, $k', 'if($val==1) $val = $k;'));// pour les votes, les mappings de "Observation" nous suffisentarray_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$req = array('select' => array(), 'join' => array(), 'where' => array(), 'groupby' => array(), 'orderby' => array());$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()$parametres['tag_explode_char'] = ','; // " " (whitespace) ou "," ou NULL// 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,array_merge(ListeObservations2::$parametres_autorises,self::$parametres_autorises));$params = array_merge(ListeObservations2::$default_params, // paramètre par défaut Identiplanteself::$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/* $this->conteneur->setParametre('url_images', sprintf($this->conteneur->getParametre('url_images'),"%09d", $params['format']));*/// création des contraintes (génériques, de ListeObservations2)ListeObservations2::sqlAddConstraint($params, $db, $req, $this->conteneur);// création des contraintes spécifiques (sur les tags essentiellement)self::sqlAddConstraint($params, $db, $req, $this->conteneur);// création des contraintes spécifiques impliquées par le masque généralself::sqlAddMasqueConstraint($params, $db, $req, $this->conteneur);// l'ORDER BY s'avére complexeself::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_imagesif(!$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));}*/list($images, $images_keyed_by_id_image) = ListeObservations2::reformateImagesDoubleIndex($liaisons,$this->conteneur->getParametre('url_images'),$params['format']);// 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_possibleif($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 premiersJOIN sur cel_obs_images et cel_obs pour filtrer sur "transmission".Dénormaliser cette valeur et l'intégrer à `cel_images` ferait économiser cette couteusejointure, ... 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 finif($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ésde 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(ListeObservations2::requestFilterParams($or_params, NULL, $c /* pour masque.departement */),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);self::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 estvariable (tag_explode_semantic) et considérée plus tard, notamment lors de laconstruction du SQL (sqlAddConstraint()).Cette sémantique peut varier selon que ces tags proviennent du formulaire derecherche 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'])) {if(isset($params['tag_explode_char'])) {$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);'));}// met à jour *toutes* les stats de nombre de tags et de moyenne des votesstatic function _update_statistics($db) {$db->requeter("TRUNCATE TABLE del_image_stat");$db->requeter(<<<EOFINSERT INTO `del_image_stat` (SELECT id_image, divo.ce_protocole, divo.moyenne, dit.ctagsFROM `BASECEL`.`cel_images` ciLEFT JOIN( SELECT ce_image, ce_protocole, AVG(valeur) AS moyenne FROM del_image_voteGROUP BY ce_image, ce_protocole ) AS divoON ci.id_image = divo.ce_imageLEFT JOIN( SELECT ce_image, COUNT(id_tag) as ctags FROM del_image_tagGROUP BY ce_image ) AS ditON ci.id_image = dit.ce_image )EOF);}}