WebSVN – Applications.papyrus – Diff – /trunk/papyrus/applettes/moteur_recherche/moteur_recherche_sphinx.php


<?php
/*
 * Moteur de recherche SPHINX
 * @author        Raphaël Droz <raphael@tela-botanica.org
 * @copyright     Tela-Botanica 2013
 */
 
/*
  sudo urpmi lib64sphinxclient-devel
  sudo pecl install sphinx
 
  see also: http://www.ibm.com/developerworks/library/os-sphinx/
  see also: http://sphinxsearch.com/docs/manual-2.0.7.html#extended-syntax
  TODO: http://sphinxsearch.com/blog/2010/08/17/how-sphinx-relevance-ranking-works/
  TODO: attention, projet,bazaar,spip et papyrus sont latin9, ainsi en est-il de l'input attendu du <form>
  		mais pour coste... c'est full utf-8
*/
 
/* returned struct:
'poids' => 0,
'url' => '',
'titre' => '',
'hreflang' => '',
'accesskey' => '',
'title' => '', // balise 'title'
'date_creation' => '',
'description' => ''
*/
 
define('SPHINX_DSN', '127.0.0.1:9306');
define('_MRS_SPHINX_BASEHOST', $_SERVER['HTTP_HOST']);
 
// pour strftime()
date_default_timezone_set('Europe/Paris');
setlocale(LC_TIME, 'fr_FR');
 
class MoteurRecherche_SPIP {
	public function get($ids, $q = NULL) {
		if(!$ids) return array();
		$db = DB::connect($GLOBALS['_MOTEUR_RECHERCHE_']['spip'][0]['bdd_dsn']);
		$req = $db->query(sprintf(<<<EOF
SELECT id_article AS id, titre, texte, date AS date_creation, lang as hreflang
FROM spip_articles
WHERE statut = "%s"
AND id_article IN (%s)
EOF
								  ,
								  "publie",
								  implode(',', $ids)));
 
		(DB::isError($req)) ? die($req->getMessage()) : '';
		$content = array();
		while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {
			$rec['url_simple'] = sprintf("%s/article%d.html",
										 trim($GLOBALS['_MOTEUR_RECHERCHE_']['spip'][0]['url'], '/'),
										 $rec['id']);
			$rec['url'] = sprintf("%s?var_recherche=%s",
								  $rec['url_simple'],
								  More_Recherche::traiterMotif($q, 'url'));
			$rec['description'] = More_Recherche::couperTexte($rec['texte'], MORE_RESULTAT_TAILLE_DESCRIPTION);
			unset($rec['texte']);
			$content[$rec['id']] = $rec;
 
		}
		return $content;
	}
}
 
class MoteurRecherche_BAZAR {
	public function get($ids, $q = NULL) {
		if(!$ids) return array();
		$db = DB::connect($GLOBALS['_MOTEUR_RECHERCHE_']['bazar'][0]['bdd_dsn']);
		$req = $db->query(sprintf(<<<EOF
SELECT bf_id_fiche AS id,
	   bf_description AS texte,
	   bf_titre AS titre,
	   bf_date_debut_evenement AS date_creation
FROM bazar_fiche
WHERE bf_id_fiche IN (%s)
EOF
								  ,
								  implode(',', $ids)));
 
		(DB::isError($req)) ? die($req->getMessage()) : '';
		$content = array();
		while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {
			$rec['url_simple'] = $rec['url'] = sprintf(trim($GLOBALS['_MOTEUR_RECHERCHE_']['bazar'][0]['url'], '/'), $rec['id']);
			$rec['description'] = More_Recherche::couperTexte($rec['texte'], MORE_RESULTAT_TAILLE_DESCRIPTION);
			unset($rec['texte']);
			$content[$rec['id']] = $rec;
		}
		return $content;		
	}
}
 
class MoteurRecherche_PROJET {
	public function get($ids, $q = NULL) {
		if(!$ids) return array();
		$db = $GLOBALS['_MOTEUR_RECHERCHE_']['bd']['papyrus'];
		$req = $db->query(sprintf(<<<EOF
SELECT p_id AS id, p_titre AS titre, p_description, p_date_creation AS date_creation
FROM projet WHERE p_id IN (%s)
EOF
								  ,
								  implode(',', $ids)));
 
		(DB::isError($req)) ? die($req->getMessage()) : '';
		$content = array();
		while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {
			$rec['url_simple'] = $rec['url'] = sprintf("%s?id_projet=%d",
													   trim($GLOBALS['_MOTEUR_RECHERCHE_']['projet'][0]['url'], '/'),
													   $rec['id']);
			$rec['description'] = substr(strip_tags($rec['p_description']), 0, 400 + 2 * MORE_RESULTAT_TAILLE_DESCRIPTION);
			unset($rec['p_description']);
			$content[$rec['id']] = $rec;
		}
		return $content;		
	}
}
 
class MoteurRecherche_PAPYRUS {
	public function get($ids, $q = NULL) {
		if(!$ids) return array();
        $db = $GLOBALS['_MOTEUR_RECHERCHE_']['bd']['papyrus'];
		$req = $db->query(sprintf(<<<EOF
SELECT mc.gmc_ce_menu AS id,
	   IF(gm_nom != '', gm_nom, IF(gm_titre != '', gm_titre, gm_titre_alternatif)) AS titre,
	   gmc_contenu AS texte,
	   gm_description_libre, gm_description_resume,
	   gm_mots_cles,gm_source, gm_auteur, gm_contributeur, gm_editeur, gm_categorie, gm_date_creation AS date_creation
FROM gen_menu m
LEFT JOIN gen_menu_contenu mc ON mc.gmc_ce_menu = m.gm_id_menu AND mc.gmc_bool_dernier = 1
WHERE mc.gmc_ce_menu IN (%s)
EOF
								  ,
								  implode(',', $ids)));
 
		(DB::isError($req)) ? die($req->getMessage()) : '';
		$content = array();
		while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {
			// Création de l'url
			// TODO : utiliser comme pour spip un fichier de config spécifique pour virer PAP_URL d'ici
			$une_url = new Pap_URL(PAP_URL);
			$une_url->setId($rec['id']);
			$rec['url_simple'] = $une_url->getURL();
			$une_url->addQueryString('var_recherche', More_Recherche::traiterMotif($q, 'url'), true);
			$rec['url'] = $une_url->getURL();
 
			$rec['description'] =  htmlentities($rec['gm_description_resume']);
			unset($rec['gm_description_resume']);
			$content[$rec['id']] = $rec;
		}
		return $content;		
	}
}
 
class MoteurRecherche_COSTE {
	public function get($ids, $q = NULL) {
		if(!$ids) return array();
		// DB access is dumb, let's use this one and pray
		$db = $GLOBALS['_MOTEUR_RECHERCHE_']['bd']['papyrus'];
		$req = $db->query(sprintf(<<<EOF
SELECT c.flore_bdtfx_nn AS id, c.nom_sci AS titre, dsc.body AS description
FROM tb_eflore.coste_v2_00 c
LEFT JOIN tela_prod_wikini.florecoste_pages dsc ON c.page_wiki_dsc = dsc.tag AND dsc.latest = 'Y'
WHERE c.flore_bdtfx_nn IN (%s)
EOF
								  ,
								  implode(',', $ids)));
 
		(DB::isError($req)) ? die($req->getMessage()) : '';
		$content = array();
		while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {
			$rec['url_simple'] = $rec['url'] = sprintf("http://%s/bdtfx-nn-%d", _MRS_SPHINX_BASEHOST, $rec['id']);
			// TODO: interpret wikini
			$rec['description'] = substr($rec['description'], 0, 400 + 2 * MORE_RESULTAT_TAILLE_DESCRIPTION);
			$content[$rec['id']] = $rec;
		}
		return $content;		
	}
}
 
 
// http://stackoverflow.com/questions/348410/sort-an-array-based-on-another-array
function _sortArrayByArray($array, $orderArray) {
	$ordered = array();
	foreach($orderArray as $key) {
		if(array_key_exists($key, $array)) {
			$ordered[$key] = $array[$key];
			unset($array[$key]);
		}
	}
	return $ordered + $array;
}
 
// adaption aux templates existants: [score] => [weight]
function _weight2score(&$item, $key, $max) {
	$item['score'] = intval($item['weight'] / $max * 100);
	$item['date_creation'] = isset($item['date_creation']) ? strftime("%d %B %Y", strtotime($item['date_creation'])) : '';
	unset($item['weight']);
}
 
function sphinx_search($q = NULL, $page = 1) {
	if(!$q) return array();
 
	$db = mysql_connect(SPHINX_DSN, NULL, NULL, TRUE);
	$req = mysql_query(sprintf("SELECT group_id, main_id FROM i_projet, i_spip, i_papyrus, i_bazar, i_coste WHERE MATCH('%s') LIMIT 50", $q), $db);
 
	$res = array('spip' => array(), 'bazar' => array(), 'projet' => array(), 'papyrus' => array(), 'coste' => array());
	$ids_par_poids = array();
	while($rec = mysql_fetch_array($req, MYSQL_ASSOC)) {
		$res[$rec['group_id']][$rec['id']] = $rec;
		$ids_par_poids[] = $rec['main_id'];
	}
 
	$docs = array();
 
	// spip
	$spip = new MoteurRecherche_SPIP();
	foreach($spip->get(array_filter(array_keys($res['spip']), 'intval'), $q) as $v) {
		unset($res['spip'][$v['id']]['group_id'],
			  $res['spip'][$v['id']]['main_id'],
			  $res['spip'][$v['id']]['id']);
		// left: weight
		$docs['spip-' . $v['id']] = array_merge($v,	$res['spip'][$v['id']]);
	}
 
	// bazar
	$bazar = new MoteurRecherche_BAZAR();
	foreach($bazar->get(array_filter(array_keys($res['bazar']), 'intval'), $q) as $v) {
		unset($res['bazar'][$v['id']]['group_id'],
			  $res['bazar'][$v['id']]['main_id'],
			  $res['bazar'][$v['id']]['id']);
		// left: weight
		$docs['bazar-' . $v['id']] = array_merge($v, $res['bazar'][$v['id']]);
	}
 
	// projet
	$projet = new MoteurRecherche_PROJET();
	foreach($projet->get(array_filter(array_keys($res['projet']), 'intval'), $q) as $v) {
		unset($res['projet'][$v['id']]['group_id'],
			  $res['projet'][$v['id']]['main_id'],
			  $res['projet'][$v['id']]['id']);
		// left: weight
		$docs['projet-' . $v['id']] = array_merge($v, $res['projet'][$v['id']]);
	}
 
	// papyrus
	$papyrus = new MoteurRecherche_PAPYRUS();
	foreach($papyrus->get(array_filter(array_keys($res['papyrus']), 'intval'), $q) as $v) {
		unset($res['papyrus'][$v['id']]['group_id'],
			  $res['papyrus'][$v['id']]['main_id'],
			  $res['papyrus'][$v['id']]['id']);
		// left: weight
		$docs['papyrus-' . $v['id']] = array_merge($v, $res['papyrus'][$v['id']]);
	}
 
	// coste
	$coste = new MoteurRecherche_COSTE();
	foreach($coste->get(array_filter(array_keys($res['coste']), 'intval'), $q) as $v) {
		unset($res['coste'][$v['id']]['group_id'],
			  $res['coste'][$v['id']]['main_id'],
			  $res['coste'][$v['id']]['id']);
		// left: weight
		$docs['coste-' . $v['id']] = array_merge($v, $res['coste'][$v['id']]);
	}
 
	$sorted = _sortArrayByArray($docs, $ids_par_poids);
 
	// sort
	/*
	// uncomment this:
	print_r(array_keys($sorted); die;
 
	// then:
	ddiff
	<(mysql -h0 -P 9306 <<<"SELECT main_id FROM i_projet, i_spip, i_papyrus, i_bazar, i_coste WHERE MATCH('test') LIMIT 50;"|awk '{print $3}'|sed 1d) \
	<(POST http://localhost/site:reseau<<<"more_motif=test&"|awk -F'=>' '{print $2}'|sed -e 's/ //g' -e '/^$/d')
 
	// both should be equal.
	// [ SELECT main_id, group_id FROM i_projet, i_spip, i_papyrus, i_bazar WHERE MATCH('test') LIMIT 50; ]
	*/
 
	$max = current($sorted);
	$max = $max['weight'];
	array_walk($sorted, '_weight2score', $max);
 
	// var_dump($sorted);die;
	return $sorted;
}
 
 
/*
// http://www.php.net/manual/fr/sphinx.examples.php
$s = new SphinxClient;
$s->setServer("localhost", 9306);
$s->setMatchMode(SPH_MATCH_ANY);
$s->setMaxQueryTime(3);
var_dump($s->query("test"));
*/
 
 
/*
  Note: conversion côté client SQL:
  mysql -h0 -P 9306 < <(iconv -f utf8 -t latin1 <<<"SELECT * FROM i_projet, i_spip, i_papyrus, i_bazar WHERE MATCH('journée');")
*/
 

Rev 2113	Rev 2117
1	`<?php`	1	`<?php`
2	`/*`	2	`/*`
3	`* Moteur de recherche SPHINX`	3	`* Moteur de recherche SPHINX`
4	`* @author Raphaël Droz <raphael@tela-botanica.org`	4	`* @author Raphaël Droz <raphael@tela-botanica.org`
5	`* @copyright Tela-Botanica 2013`	5	`* @copyright Tela-Botanica 2013`
6	`*/`	6	`*/`
7		7
8	`/*`	8	`/*`
9	`sudo urpmi lib64sphinxclient-devel`	9	`sudo urpmi lib64sphinxclient-devel`
10	`sudo pecl install sphinx`	10	`sudo pecl install sphinx`
11		11
12	`see also: http://www.ibm.com/developerworks/library/os-sphinx/`	12	`see also: http://www.ibm.com/developerworks/library/os-sphinx/`
13	`see also: http://sphinxsearch.com/docs/manual-2.0.7.html#extended-syntax`	13	`see also: http://sphinxsearch.com/docs/manual-2.0.7.html#extended-syntax`
14	`TODO: http://sphinxsearch.com/blog/2010/08/17/how-sphinx-relevance-ranking-works/`	14	`TODO: http://sphinxsearch.com/blog/2010/08/17/how-sphinx-relevance-ranking-works/`
15	`TODO: attention, projet,bazaar,spip et papyrus sont latin9, ainsi en est-il de l'input attendu du <form>`	15	`TODO: attention, projet,bazaar,spip et papyrus sont latin9, ainsi en est-il de l'input attendu du <form>`
16	`mais pour coste... c'est full utf-8`	16	`mais pour coste... c'est full utf-8`
17	`*/`	17	`*/`
18		18
19	`/* returned struct:`	19	`/* returned struct:`
20	`'poids' => 0,`	20	`'poids' => 0,`
21	`'url' => '',`	21	`'url' => '',`
22	`'titre' => '',`	22	`'titre' => '',`
23	`'hreflang' => '',`	23	`'hreflang' => '',`
24	`'accesskey' => '',`	24	`'accesskey' => '',`
25	`'title' => '', // balise 'title'`	25	`'title' => '', // balise 'title'`
26	`'date_creation' => '',`	26	`'date_creation' => '',`
27	`'description' => ''`	27	`'description' => ''`
28	`*/`	28	`*/`
29		29
30	`define('SPHINX_DSN', '127.0.0.1:9306');`	30	`define('SPHINX_DSN', '127.0.0.1:9306');`
-		31	`define('_MRS_SPHINX_BASEHOST', $_SERVER['HTTP_HOST']);`
31		32
32	`// pour strftime()`	33	`// pour strftime()`
33	`date_default_timezone_set('Europe/Paris');`	34	`date_default_timezone_set('Europe/Paris');`
34	`setlocale(LC_TIME, 'fr_FR');`	35	`setlocale(LC_TIME, 'fr_FR');`
35		36
36	`class MoteurRecherche_SPIP {`	37	`class MoteurRecherche_SPIP {`
37	`public function get($ids, $q = NULL) {`	38	`public function get($ids, $q = NULL) {`
38	`if(!$ids) return array();`	39	`if(!$ids) return array();`
39	`$db = DB::connect($GLOBALS['_MOTEUR_RECHERCHE_']['spip'][0]['bdd_dsn']);`	40	`$db = DB::connect($GLOBALS['_MOTEUR_RECHERCHE_']['spip'][0]['bdd_dsn']);`
40	`$req = $db->query(sprintf(<<<EOF`	41	`$req = $db->query(sprintf(<<<EOF`
41	`SELECT id_article AS id, titre, texte, date AS date_creation, lang as hreflang`	42	`SELECT id_article AS id, titre, texte, date AS date_creation, lang as hreflang`
42	`FROM spip_articles`	43	`FROM spip_articles`
43	`WHERE statut = "%s"`	44	`WHERE statut = "%s"`
44	`AND id_article IN (%s)`	45	`AND id_article IN (%s)`
45	`EOF`	46	`EOF`
46	`,`	47	`,`
47	`"publie",`	48	`"publie",`
48	`implode(',', $ids)));`	49	`implode(',', $ids)));`
49		50
50	`(DB::isError($req)) ? die($req->getMessage()) : '';`	51	`(DB::isError($req)) ? die($req->getMessage()) : '';`
51	`$content = array();`	52	`$content = array();`
52	`while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {`	53	`while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {`
53	`$rec['url_simple'] = sprintf("%s/article%d.html",`	54	`$rec['url_simple'] = sprintf("%s/article%d.html",`
54	`trim($GLOBALS['_MOTEUR_RECHERCHE_']['spip'][0]['url'], '/'),`	55	`trim($GLOBALS['_MOTEUR_RECHERCHE_']['spip'][0]['url'], '/'),`
55	`$rec['id']);`	56	`$rec['id']);`
56	`$rec['url'] = sprintf("%s?var_recherche=%s",`	57	`$rec['url'] = sprintf("%s?var_recherche=%s",`
57	`$rec['url_simple'],`	58	`$rec['url_simple'],`
58	`More_Recherche::traiterMotif($q, 'url'));`	59	`More_Recherche::traiterMotif($q, 'url'));`
59	`$rec['description'] = More_Recherche::couperTexte($rec['texte'], MORE_RESULTAT_TAILLE_DESCRIPTION);`	60	`$rec['description'] = More_Recherche::couperTexte($rec['texte'], MORE_RESULTAT_TAILLE_DESCRIPTION);`
60	`unset($rec['texte']);`	61	`unset($rec['texte']);`
61	`$content[$rec['id']] = $rec;`	62	`$content[$rec['id']] = $rec;`
62		63
63	`}`	64	`}`
64	`return $content;`	65	`return $content;`
65	`}`	66	`}`
66	`}`	67	`}`
67		68
68	`class MoteurRecherche_BAZAR {`	69	`class MoteurRecherche_BAZAR {`
69	`public function get($ids, $q = NULL) {`	70	`public function get($ids, $q = NULL) {`
70	`if(!$ids) return array();`	71	`if(!$ids) return array();`
71	`$db = DB::connect($GLOBALS['_MOTEUR_RECHERCHE_']['bazar'][0]['bdd_dsn']);`	72	`$db = DB::connect($GLOBALS['_MOTEUR_RECHERCHE_']['bazar'][0]['bdd_dsn']);`
72	`$req = $db->query(sprintf(<<<EOF`	73	`$req = $db->query(sprintf(<<<EOF`
73	`SELECT bf_id_fiche AS id,`	74	`SELECT bf_id_fiche AS id,`
74	`bf_description AS texte,`	75	`bf_description AS texte,`
75	`bf_titre AS titre,`	76	`bf_titre AS titre,`
76	`bf_date_debut_evenement AS date_creation`	77	`bf_date_debut_evenement AS date_creation`
77	`FROM bazar_fiche`	78	`FROM bazar_fiche`
78	`WHERE bf_id_fiche IN (%s)`	79	`WHERE bf_id_fiche IN (%s)`
79	`EOF`	80	`EOF`
80	`,`	81	`,`
81	`implode(',', $ids)));`	82	`implode(',', $ids)));`
82		83
83	`(DB::isError($req)) ? die($req->getMessage()) : '';`	84	`(DB::isError($req)) ? die($req->getMessage()) : '';`
84	`$content = array();`	85	`$content = array();`
85	`while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {`	86	`while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {`
86	`$rec['url_simple'] = $rec['url'] = sprintf(trim($GLOBALS['_MOTEUR_RECHERCHE_']['bazar'][0]['url'], '/'), $rec['id']);`	87	`$rec['url_simple'] = $rec['url'] = sprintf(trim($GLOBALS['_MOTEUR_RECHERCHE_']['bazar'][0]['url'], '/'), $rec['id']);`
87	`$rec['description'] = More_Recherche::couperTexte($rec['texte'], MORE_RESULTAT_TAILLE_DESCRIPTION);`	88	`$rec['description'] = More_Recherche::couperTexte($rec['texte'], MORE_RESULTAT_TAILLE_DESCRIPTION);`
88	`unset($rec['texte']);`	89	`unset($rec['texte']);`
89	`$content[$rec['id']] = $rec;`	90	`$content[$rec['id']] = $rec;`
90	`}`	91	`}`
91	`return $content;`	92	`return $content;`
92	`}`	93	`}`
93	`}`	94	`}`
94		95
95	`class MoteurRecherche_PROJET {`	96	`class MoteurRecherche_PROJET {`
96	`public function get($ids, $q = NULL) {`	97	`public function get($ids, $q = NULL) {`
97	`if(!$ids) return array();`	98	`if(!$ids) return array();`
98	`$db = $GLOBALS['_MOTEUR_RECHERCHE_']['bd']['papyrus'];`	99	`$db = $GLOBALS['_MOTEUR_RECHERCHE_']['bd']['papyrus'];`
99	`$req = $db->query(sprintf(<<<EOF`	100	`$req = $db->query(sprintf(<<<EOF`
100	`SELECT p_id AS id, p_titre, p_description, p_date_creation AS date_creation`	101	`SELECT p_id AS id, p_titre AS titre, p_description, p_date_creation AS date_creation`
101	`FROM projet WHERE p_id IN (%s)`	102	`FROM projet WHERE p_id IN (%s)`
102	`EOF`	103	`EOF`
103	`,`	104	`,`
104	`implode(',', $ids)));`	105	`implode(',', $ids)));`
105		106
106	`(DB::isError($req)) ? die($req->getMessage()) : '';`	107	`(DB::isError($req)) ? die($req->getMessage()) : '';`
107	`$content = array();`	108	`$content = array();`
108	`while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {`	109	`while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {`
109	`$rec['url_simple'] = $rec['url'] = sprintf("%s?id_projet=%d",`	110	`$rec['url_simple'] = $rec['url'] = sprintf("%s?id_projet=%d",`
110	`trim($GLOBALS['_MOTEUR_RECHERCHE_']['projet'][0]['url'], '/'),`	111	`trim($GLOBALS['_MOTEUR_RECHERCHE_']['projet'][0]['url'], '/'),`
111	`$rec['id']);`	112	`$rec['id']);`
112	`$rec['description'] = substr(strip_tags($rec['p_description']), 0, 400 + 2 * MORE_RESULTAT_TAILLE_DESCRIPTION);`	113	`$rec['description'] = substr(strip_tags($rec['p_description']), 0, 400 + 2 * MORE_RESULTAT_TAILLE_DESCRIPTION);`
113	`unset($rec['p_description']);`	114	`unset($rec['p_description']);`
114	`$content[$rec['id']] = $rec;`	115	`$content[$rec['id']] = $rec;`
115	`}`	116	`}`
116	`return $content;`	117	`return $content;`
117	`}`	118	`}`
118	`}`	119	`}`
119		120
120	`class MoteurRecherche_PAPYRUS {`	121	`class MoteurRecherche_PAPYRUS {`
121	`public function get($ids, $q = NULL) {`	122	`public function get($ids, $q = NULL) {`
122	`if(!$ids) return array();`	123	`if(!$ids) return array();`
123	`$db = $GLOBALS['_MOTEUR_RECHERCHE_']['bd']['papyrus'];`	124	`$db = $GLOBALS['_MOTEUR_RECHERCHE_']['bd']['papyrus'];`
124	`$req = $db->query(sprintf(<<<EOF`	125	`$req = $db->query(sprintf(<<<EOF`
125	`SELECT mc.gmc_ce_menu AS id,`	126	`SELECT mc.gmc_ce_menu AS id,`
126	`IF(gm_nom != '', gm_nom, IF(gm_titre != '', gm_titre, gm_titre_alternatif)) AS titre,`	127	`IF(gm_nom != '', gm_nom, IF(gm_titre != '', gm_titre, gm_titre_alternatif)) AS titre,`
127	`gmc_contenu AS texte,`	128	`gmc_contenu AS texte,`
128	`gm_description_libre, gm_description_resume,`	129	`gm_description_libre, gm_description_resume,`
129	`gm_mots_cles,gm_source, gm_auteur, gm_contributeur, gm_editeur, gm_categorie, gm_date_creation AS date_creation`	130	`gm_mots_cles,gm_source, gm_auteur, gm_contributeur, gm_editeur, gm_categorie, gm_date_creation AS date_creation`
130	`FROM gen_menu m`	131	`FROM gen_menu m`
131	`LEFT JOIN gen_menu_contenu mc ON mc.gmc_ce_menu = m.gm_id_menu AND mc.gmc_bool_dernier = 1`	132	`LEFT JOIN gen_menu_contenu mc ON mc.gmc_ce_menu = m.gm_id_menu AND mc.gmc_bool_dernier = 1`
132	`WHERE mc.gmc_ce_menu IN (%s)`	133	`WHERE mc.gmc_ce_menu IN (%s)`
133	`EOF`	134	`EOF`
134	`,`	135	`,`
135	`implode(',', $ids)));`	136	`implode(',', $ids)));`
136		137
137	`(DB::isError($req)) ? die($req->getMessage()) : '';`	138	`(DB::isError($req)) ? die($req->getMessage()) : '';`
138	`$content = array();`	139	`$content = array();`
139	`while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {`	140	`while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {`
140	`// Création de l'url`	141	`// Création de l'url`
141	`// TODO : utiliser comme pour spip un fichier de config spécifique pour virer PAP_URL d'ici`	142	`// TODO : utiliser comme pour spip un fichier de config spécifique pour virer PAP_URL d'ici`
142	`$une_url = new Pap_URL(PAP_URL);`	143	`$une_url = new Pap_URL(PAP_URL);`
143	`$une_url->setId($rec['id']);`	144	`$une_url->setId($rec['id']);`
144	`$rec['url_simple'] = $une_url->getURL();`	145	`$rec['url_simple'] = $une_url->getURL();`
145	`$une_url->addQueryString('var_recherche', More_Recherche::traiterMotif($q, 'url'), true);`	146	`$une_url->addQueryString('var_recherche', More_Recherche::traiterMotif($q, 'url'), true);`
146	`$rec['url'] = $une_url->getURL();`	147	`$rec['url'] = $une_url->getURL();`
147		148
148	`$rec['description'] = htmlentities($rec['gm_description_resume']);`	149	`$rec['description'] = htmlentities($rec['gm_description_resume']);`
149	`unset($rec['gm_description_resume']);`	150	`unset($rec['gm_description_resume']);`
150	`$content[$rec['id']] = $rec;`	151	`$content[$rec['id']] = $rec;`
151	`}`	152	`}`
152	`return $content;`	153	`return $content;`
153	`}`	154	`}`
154	`}`	155	`}`
-		156
-		157	`class MoteurRecherche_COSTE {`
-		158	`public function get($ids, $q = NULL) {`
-		159	`if(!$ids) return array();`
-		160	`// DB access is dumb, let's use this one and pray`
-		161	`$db = $GLOBALS['_MOTEUR_RECHERCHE_']['bd']['papyrus'];`
-		162	`$req = $db->query(sprintf(<<<EOF`
-		163	`SELECT c.flore_bdtfx_nn AS id, c.nom_sci AS titre, dsc.body AS description`
-		164	`FROM tb_eflore.coste_v2_00 c`
-		165	`LEFT JOIN tela_prod_wikini.florecoste_pages dsc ON c.page_wiki_dsc = dsc.tag AND dsc.latest = 'Y'`
-		166	`WHERE c.flore_bdtfx_nn IN (%s)`
-		167	`EOF`
-		168	`,`
-		169	`implode(',', $ids)));`
-		170
-		171	`(DB::isError($req)) ? die($req->getMessage()) : '';`
-		172	`$content = array();`
-		173	`while($rec = $req->fetchRow(DB_FETCHMODE_ASSOC)) {`
-		174	`$rec['url_simple'] = $rec['url'] = sprintf("http://%s/bdtfx-nn-%d", _MRS_SPHINX_BASEHOST, $rec['id']);`
-		175	`// TODO: interpret wikini`
-		176	`$rec['description'] = substr($rec['description'], 0, 400 + 2 * MORE_RESULTAT_TAILLE_DESCRIPTION);`
-		177	`$content[$rec['id']] = $rec;`
-		178	`}`
-		179	`return $content;`
-		180	`}`
-		181	`}`
155		182
156		183
157	`// http://stackoverflow.com/questions/348410/sort-an-array-based-on-another-array`	184	`// http://stackoverflow.com/questions/348410/sort-an-array-based-on-another-array`
158	`function _sortArrayByArray($array, $orderArray) {`	185	`function _sortArrayByArray($array, $orderArray) {`
159	`$ordered = array();`	186	`$ordered = array();`
160	`foreach($orderArray as $key) {`	187	`foreach($orderArray as $key) {`
161	`if(array_key_exists($key, $array)) {`	188	`if(array_key_exists($key, $array)) {`
162	`$ordered[$key] = $array[$key];`	189	`$ordered[$key] = $array[$key];`
163	`unset($array[$key]);`	190	`unset($array[$key]);`
164	`}`	191	`}`
165	`}`	192	`}`
166	`return $ordered + $array;`	193	`return $ordered + $array;`
167	`}`	194	`}`
168		195
169	`// adaption aux templates existants: [score] => [weight]`	196	`// adaption aux templates existants: [score] => [weight]`
170	`function _weight2score(&$item, $key, $max) {`	197	`function _weight2score(&$item, $key, $max) {`
171	`$item['score'] = intval($item['weight'] / $max * 100);`	198	`$item['score'] = intval($item['weight'] / $max * 100);`
172	`$item['date_creation'] = strftime("%d %B %Y", strtotime($item['date_creation']));`	199	`$item['date_creation'] = isset($item['date_creation']) ? strftime("%d %B %Y", strtotime($item['date_creation'])) : '';`
173	`unset($item['weight']);`	200	`unset($item['weight']);`
174	`}`	201	`}`
175		202
176	`function sphinx_search($q = NULL, $page = 1) {`	203	`function sphinx_search($q = NULL, $page = 1) {`
177	`if(!$q) return array();`	204	`if(!$q) return array();`
178		205
179	`$db = mysql_connect(SPHINX_DSN, NULL, NULL, TRUE);`	206	`$db = mysql_connect(SPHINX_DSN, NULL, NULL, TRUE);`
180	`$req = mysql_query(sprintf("SELECT group_id, main_id FROM i_projet, i_spip, i_papyrus, i_bazar WHERE MATCH('%s') LIMIT 50", $q), $db);`	207	`$req = mysql_query(sprintf("SELECT group_id, main_id FROM i_projet, i_spip, i_papyrus, i_bazar, i_coste WHERE MATCH('%s') LIMIT 50", $q), $db);`
181		208
182	`$res = array('spip' => array(), 'bazar' => array(), 'projet' => array(), 'papyrus' => array());`	209	`$res = array('spip' => array(), 'bazar' => array(), 'projet' => array(), 'papyrus' => array(), 'coste' => array());`
183	`$ids_par_poids = array();`	210	`$ids_par_poids = array();`
184	`while($rec = mysql_fetch_array($req, MYSQL_ASSOC)) {`	211	`while($rec = mysql_fetch_array($req, MYSQL_ASSOC)) {`
185	`$res[$rec['group_id']][$rec['id']] = $rec;`	212	`$res[$rec['group_id']][$rec['id']] = $rec;`
186	`$ids_par_poids[] = $rec['main_id'];`	213	`$ids_par_poids[] = $rec['main_id'];`
187	`}`	214	`}`
188		-
189	`$docs = array();`	215	`$docs = array();`
190		216
191	`// spip`	217	`// spip`
192	`$spip = new MoteurRecherche_SPIP();`	218	`$spip = new MoteurRecherche_SPIP();`
193	`foreach($spip->get(array_filter(array_keys($res['spip']), 'intval'), $q) as $v) {`	219	`foreach($spip->get(array_filter(array_keys($res['spip']), 'intval'), $q) as $v) {`
194	`unset($res['spip'][$v['id']]['group_id'],`	220	`unset($res['spip'][$v['id']]['group_id'],`
195	`$res['spip'][$v['id']]['main_id'],`	221	`$res['spip'][$v['id']]['main_id'],`
196	`$res['spip'][$v['id']]['id']);`	222	`$res['spip'][$v['id']]['id']);`
197	`// left: weight`	223	`// left: weight`
198	`$docs['spip-' . $v['id']] = array_merge($v, $res['spip'][$v['id']]);`	224	`$docs['spip-' . $v['id']] = array_merge($v, $res['spip'][$v['id']]);`
199	`}`	225	`}`
200		226
201	`// bazar`	227	`// bazar`
202	`$bazar = new MoteurRecherche_BAZAR();`	228	`$bazar = new MoteurRecherche_BAZAR();`
203	`foreach($bazar->get(array_filter(array_keys($res['bazar']), 'intval'), $q) as $v) {`	229	`foreach($bazar->get(array_filter(array_keys($res['bazar']), 'intval'), $q) as $v) {`
204	`unset($res['bazar'][$v['id']]['group_id'],`	230	`unset($res['bazar'][$v['id']]['group_id'],`
205	`$res['bazar'][$v['id']]['main_id'],`	231	`$res['bazar'][$v['id']]['main_id'],`
206	`$res['bazar'][$v['id']]['id']);`	232	`$res['bazar'][$v['id']]['id']);`
207	`// left: weight`	233	`// left: weight`
208	`$docs['bazar-' . $v['id']] = array_merge($v, $res['bazar'][$v['id']]);`	234	`$docs['bazar-' . $v['id']] = array_merge($v, $res['bazar'][$v['id']]);`
209	`}`	235	`}`
210		236
211	`// projet`	237	`// projet`
212	`$projet = new MoteurRecherche_PROJET();`	238	`$projet = new MoteurRecherche_PROJET();`
213	`foreach($projet->get(array_filter(array_keys($res['projet']), 'intval'), $q) as $v) {`	239	`foreach($projet->get(array_filter(array_keys($res['projet']), 'intval'), $q) as $v) {`
214	`unset($res['projet'][$v['id']]['group_id'],`	240	`unset($res['projet'][$v['id']]['group_id'],`
215	`$res['projet'][$v['id']]['main_id'],`	241	`$res['projet'][$v['id']]['main_id'],`
216	`$res['projet'][$v['id']]['id']);`	242	`$res['projet'][$v['id']]['id']);`
217	`// left: weight`	243	`// left: weight`
218	`$docs['projet-' . $v['id']] = array_merge($v, $res['projet'][$v['id']]);`	244	`$docs['projet-' . $v['id']] = array_merge($v, $res['projet'][$v['id']]);`
219	`}`	245	`}`
220		246
221	`// papyrus`	247	`// papyrus`
222	`$papyrus = new MoteurRecherche_PAPYRUS();`	248	`$papyrus = new MoteurRecherche_PAPYRUS();`
223	`foreach($papyrus->get(array_filter(array_keys($res['papyrus']), 'intval'), $q) as $v) {`	249	`foreach($papyrus->get(array_filter(array_keys($res['papyrus']), 'intval'), $q) as $v) {`
224	`unset($res['papyrus'][$v['id']]['group_id'],`	250	`unset($res['papyrus'][$v['id']]['group_id'],`
225	`$res['papyrus'][$v['id']]['main_id'],`	251	`$res['papyrus'][$v['id']]['main_id'],`
226	`$res['papyrus'][$v['id']]['id']);`	252	`$res['papyrus'][$v['id']]['id']);`
227	`// left: weight`	253	`// left: weight`
228	`$docs['papyrus-' . $v['id']] = array_merge($v, $res['papyrus'][$v['id']]);`	254	`$docs['papyrus-' . $v['id']] = array_merge($v, $res['papyrus'][$v['id']]);`
229	`}`	255	`}`
-		256
-		257	`// coste`
-		258	`$coste = new MoteurRecherche_COSTE();`
-		259	`foreach($coste->get(array_filter(array_keys($res['coste']), 'intval'), $q) as $v) {`
-		260	`unset($res['coste'][$v['id']]['group_id'],`
-		261	`$res['coste'][$v['id']]['main_id'],`
-		262	`$res['coste'][$v['id']]['id']);`
-		263	`// left: weight`
-		264	`$docs['coste-' . $v['id']] = array_merge($v, $res['coste'][$v['id']]);`
-		265	`}`
230		266
231	`$sorted = _sortArrayByArray($docs, $ids_par_poids);`	267	`$sorted = _sortArrayByArray($docs, $ids_par_poids);`
232		268
233	`// sort`	269	`// sort`
234	`/*`	270	`/*`
235	`// uncomment this:`	271	`// uncomment this:`
236	`print_r(array_keys($sorted); die;`	272	`print_r(array_keys($sorted); die;`
237		273
238	`// then:`	274	`// then:`
239	`ddiff`	275	`ddiff`
240	`<(mysql -h0 -P 9306 <<<"SELECT main_id FROM i_projet, i_spip, i_papyrus, i_bazar WHERE MATCH('test') LIMIT 50;"\|awk '{print $3}'\|sed 1d) \`	276	`<(mysql -h0 -P 9306 <<<"SELECT main_id FROM i_projet, i_spip, i_papyrus, i_bazar, i_coste WHERE MATCH('test') LIMIT 50;"\|awk '{print $3}'\|sed 1d) \`
241	`<(POST http://localhost/site:reseau<<<"more_motif=test&"\|awk -F'=>' '{print $2}'\|sed -e 's/ //g' -e '/^$/d')`	277	`<(POST http://localhost/site:reseau<<<"more_motif=test&"\|awk -F'=>' '{print $2}'\|sed -e 's/ //g' -e '/^$/d')`
242		278
243	`// both should be equal.`	279	`// both should be equal.`
244	`// [ SELECT main_id, group_id FROM i_projet, i_spip, i_papyrus, i_bazar WHERE MATCH('test') LIMIT 50; ]`	280	`// [ SELECT main_id, group_id FROM i_projet, i_spip, i_papyrus, i_bazar WHERE MATCH('test') LIMIT 50; ]`
245	`*/`	281	`*/`
246		282
247	`$max = current($sorted);`	283	`$max = current($sorted);`
248	`$max = $max['weight'];`	284	`$max = $max['weight'];`
249	`array_walk($sorted, '_weight2score', $max);`	285	`array_walk($sorted, '_weight2score', $max);`
250		286
251	`//var_dump($sorted);die;`	287	`// var_dump($sorted);die;`
252	`return $sorted;`	288	`return $sorted;`
253	`}`	289	`}`
254		290
255		291
256	`/*`	292	`/*`
257	`// http://www.php.net/manual/fr/sphinx.examples.php`	293	`// http://www.php.net/manual/fr/sphinx.examples.php`
258	`$s = new SphinxClient;`	294	`$s = new SphinxClient;`
259	`$s->setServer("localhost", 9306);`	295	`$s->setServer("localhost", 9306);`
260	`$s->setMatchMode(SPH_MATCH_ANY);`	296	`$s->setMatchMode(SPH_MATCH_ANY);`
261	`$s->setMaxQueryTime(3);`	297	`$s->setMaxQueryTime(3);`
262	`var_dump($s->query("test"));`	298	`var_dump($s->query("test"));`
263	`*/`	299	`*/`
264		300
265		301
266	`/*`	302	`/*`
267	`Note: conversion côté client SQL:`	303	`Note: conversion côté client SQL:`
268	`mysql -h0 -P 9306 < <(iconv -f utf8 -t latin1 <<<"SELECT * FROM i_projet, i_spip, i_papyrus, i_bazar WHERE MATCH('journée');")`	304	`mysql -h0 -P 9306 < <(iconv -f utf8 -t latin1 <<<"SELECT * FROM i_projet, i_spip, i_papyrus, i_bazar WHERE MATCH('journée');")`
269	`*/`	305	`*/`
270		-

Subversion Repositories Applications.papyrus

(root)/trunk/papyrus/applettes/moteur_recherche/moteur_recherche_sphinx.php – Rev 2113 → 2117