[Sommaire] [Imprimer]

   1  <?php
   2  /* -*- tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   3  /*
   4  # ***** BEGIN LICENSE BLOCK *****
   5  # This file is part of Plume CMS, a website management application.
   6  # Copyright (C) 2001-2005 Loic d'Anterroches and contributors.
   7  #
   8  # Plume CMS is free software; you can redistribute it and/or modify
   9  # it under the terms of the GNU General Public License as published by
  10  # the Free Software Foundation; either version 2 of the License, or
  11  # (at your option) any later version.
  12  #
  13  # Plume CMS is distributed in the hope that it will be useful,
  14  # but WITHOUT ANY WARRANTY; without even the implied warranty of
  15  # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  16  # GNU General Public License for more details.
  17  #
  18  # You should have received a copy of the GNU General Public License
  19  # along with this program; if not, write to the Free Software
  20  # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
  21  #
  22  # ***** END LICENSE BLOCK ***** */
  23  
  24  require_once dirname(__FILE__).'/class.l10n.php';
  25  
  26  class Search extends CError
  27  {
  28  
  29      var $websiteId = ''; //Website id
  30      var $con = null; //DB connection
  31  
  32      /**
  33      Constructor
  34  
  35      @param object DB reference
  36      @param string Website id
  37      */
  38      function Search(&$db, $websiteId='')
  39      {
  40          $this->con = $db;
  41          $this->websiteId = $websiteId;
  42          return true;
  43      }
  44  
  45      /**
  46      Index a text string.
  47  
  48      @param string The text string to index
  49      @param string The identifier of the string (resource id for example)
  50      @param string The type of string
  51      @return bool Succes or not, if error an error message is set
  52      */
  53      function index($string, $id, $type = 'html')
  54      {
  55          if ('html' == $type) {
  56              //To have an accurate number of occurences
  57              $string = $this->balance_string($string);
  58          }
  59          $string = $this->clean_string($string);
  60          $words  = $this->explode_in_words($string);
  61          return $this->save_words($id, $words);
  62      }
  63  
  64  
  65      /**
  66      Clean string.
  67  
  68      Clean a string to be used for the indexation. For the moment consider the string as having
  69      only text (or wiki) but not HTML.
  70  
  71      @param string String to clean
  72      @return string Cleaned string
  73      */
  74      function clean_string($string)
  75      {
  76          //include_once dirname(__FILE__).'/lib.text.php';
  77          //$string = text::removeEntities($string);
  78          //if (strtolower(config::f('encoding') == 'utf-8')) {
  79          //$string = utf8_decode($string);
  80          //}
  81          $string = strtr($string,"ÀÁÂÃÄÅàáâãäåÒÓÔÕÖØòóôõöøÈÉÊËèéêëÌÍÎÏìíîïÙÚÛÜùúûüÿÑñçÇ","AAAAAAaaaaaaOOOOOOooooooEEEEeeeeIIIIiiiiUUUUuuuuyNncC");
  82          $string = preg_replace('/[_]{2,}/', ' ', $string);
  83          //$string = preg_replace('/[^a-zA-Z0-9_\-\s]/', ' ', $string);
  84          return $string;
  85      }
  86  
  87      /**
  88       * Balance HTML string.
  89       *
  90       * Balance an HTML string by identification of words in headlines. Return a
  91       * string with more occurences of the important words.
  92       *
  93       * @param string String to balance
  94       * @return string Balanced string
  95       */
  96      function balance_string($string)
  97      {
  98          return $string;
  99      }
 100  
 101      /**
 102       * Explode in words.
 103       *
 104       * Explode in words a strings and give the number of occurence 
 105       * of each word.
 106       *
 107       * @param string String to be exploded
 108       * @param bool Remove the small words and numerical only (true)
 109       * @return array Associative array, key: word, value: occurence
 110       */
 111      function explode_in_words($string, $remove=true)
 112      {
 113          $words = array();
 114          $twords = preg_split('/\s+/', $string);
 115          foreach ($twords as $word) {
 116              $word = trim(strtolower($word));
 117              if ($remove && preg_match('/^[0-9]+$/', $word)) {
 118                  $word = '';
 119              }
 120              if (!empty($word) && (!$remove or strlen($word) > 2)) {
 121                  if (!empty($words[$word])) {
 122                      $words[$word]++;
 123                  } else {
 124                      $words[$word] = 1;
 125                  }
 126              }
 127          }
 128          return $words;
 129      }
 130  
 131      /**
 132       * Save the indexed resource.
 133       *
 134       * @param string Resource id
 135       * @param array Words and occurences
 136       * @return bool Success
 137       */
 138      function save_words($id, $words)
 139      {
 140          //save the words in the db
 141          return $this->db_save_words($id, $words);
 142      }
 143  
 144      /**
 145       * Add the words to the word table. The words are supposed to be clean.
 146       * Update the occurence table
 147       *
 148       * @param string Resource id
 149       * @param array words
 150       * @return bool Success
 151       */
 152      function db_save_words($id, $words)
 153      {
 154          include_once dirname(__FILE__).'/lib.utils.php';
 155  
 156          $reqExists = 'SELECT * FROM '.$this->con->pfx.'searchwords 
 157                        WHERE word=\'%s\'';
 158          $reqIns    = 'INSERT INTO '.$this->con->pfx.'searchwords SET 
 159                        word=\'%s\'';
 160          $reqInsOcc = 'INSERT INTO '.$this->con->pfx.'searchocc SET
 161                        word_id=\'%s\', 
 162                        resource_id=\''.$this->con->esc($id).'\',
 163                        website_id=\''.$this->con->esc($this->websiteId).'\',
 164                        occ=\'%s\', pondocc=\'%s\'';
 165  
 166          // Remove the previous indexation of the resource
 167          if (false === $this->remove_from_index($id)) {
 168              return false;
 169          }           
 170  
 171          // Add the new words (in the word table and in the occurence table)
 172          // Number of indexed words in the string
 173          reset($words);
 174          $total = 0.0;
 175          foreach ($words as $word => $occ) {
 176              $total += (float) $occ;
 177          }
 178          reset($words);
 179          foreach ($words as $word => $occ) {
 180              $req = sprintf($reqExists, $this->con->escapeStr($word));
 181              if (($rs = $this->con->select($req)) === false) {
 182                  $this->setError('MySQL: '.$this->con->error(), 500);
 183                  return false;
 184              }
 185              if ($rs->nbRow() == 0) {
 186                  // need to add the word to the word table
 187                  $req = sprintf($reqIns, $this->con->escapeStr($word));
 188                  if (!$this->con->execute($req)) {
 189                      $this->setError('MySQL: '.$this->con->error(), 500);
 190                      return false;
 191                  }
 192                  if (false == ($wid = $this->con->getLastID())) {
 193                      $this->setError('MySQL: '.$this->con->error(), 500);
 194                      return false;
 195                  }
 196              } else {
 197                  $wid = $rs->f('word_id');
 198              }
 199              // Add the word to the occurence table
 200              $req = sprintf($reqInsOcc, $wid, $this->con->escapeStr($occ), 
 201                             (float) $occ/$total*100.0);
 202              if (!$this->con->execute($req)) {
 203                  $this->setError('MySQL: '.$this->con->error(), 500);
 204                  return false;
 205              }
 206          }
 207  
 208          // Update the stats table
 209          $reqSel = 'SELECT * FROM '.$this->con->pfx.'search 
 210                     WHERE resource_id=\''.$this->con->escapeStr($id).'\'';
 211          $reqIns = 'INSERT INTO '.$this->con->pfx.'search SET 
 212                     resource_id=\''.$this->con->escapeStr($id).'\',
 213                     website_id=\''.$this->con->escapeStr($this->websiteId).'\', 
 214                     lastindex=\''.date::stamp().'\',
 215                     nbindex=\'1\'';
 216          $reqUpd = 'UPDATE '.$this->con->pfx.'search SET 
 217                     lastindex=\''.date::stamp().'\', nbindex=nbindex+1
 218                     WHERE resource_id=\''.$this->con->escapeStr($id).'\'';
 219  
 220          if (($rs = $this->con->select($reqSel)) === false) {
 221              $this->setError('MySQL: '.$this->con->error(), 500);
 222              return false;
 223          }
 224          if ($rs->nbRow() == 0) {
 225              $req = $reqIns;
 226          } else {
 227              $req = $reqUpd;
 228          }
 229          if (!$this->con->execute($req)) {
 230              $this->setError('MySQL: '.$this->con->error(), 500);
 231              return false;
 232          }
 233          return true;
 234      }
 235      
 236      /**
 237       * Remove a resource from the index. 
 238       *    
 239       * @param string Resource id
 240       * @return bool Succes
 241       */
 242      function remove_from_index($id)
 243      {
 244          // Remove the previous indexation of the resource
 245          $req = 'DELETE FROM '.$this->con->pfx.'searchocc WHERE resource_id=\''
 246              .$this->con->escapeStr($id).'\'';
 247          if (!$this->con->execute($req)) {
 248              $this->setError('MySQL: '.$this->con->error(), 500);
 249              return false;
 250          }   
 251          return true;
 252      }
 253      
 254      /**
 255       * Clean index. 
 256       *
 257       * Clean the index of the words that are not used anymore.
 258       * This a SQL intensive function. 
 259       *    
 260       * @return int Number of word removed or false
 261       */
 262      function clean_index()
 263      {
 264          //Get all the word ids.
 265          $i = 0;
 266          $req = 'SELECT word_id FROM '.$this->con->pfx.'searchwords';
 267          if (($rs = $this->con->select($req)) === false) {
 268              $this->setError('MySQL: '.$this->con->error(), 500);
 269              return false;
 270          }
 271          while (!$rs->EOF()) {
 272              $req =  'SELECT resource_id FROM '.$this->con->pfx.'searchocc ';
 273              $req .= 'WHERE word_id=\''.$this->con->escapeStr($rs->f('word_id'))
 274                  .'\'';
 275              if (($rsocc = $this->con->select($req)) === false) {
 276                  $this->setError('MySQL: '.$this->con->error(), 500);
 277                  return false;
 278              }
 279              if ($rsocc->nbRow() == 0) {
 280                  // the word is not used, delete it
 281                  $req =  'DELETE FROM '.$this->con->pfx.'searchwords ';
 282                  $req .= 'WHERE word_id=\''
 283                      .$this->con->escapeStr($rs->f('word_id')).'\'';
 284                  if (!$this->con->execute($req)) {
 285                      $this->setError('MySQL: '.$this->con->error(), 500);
 286                      return false;
 287                  }
 288                  $i++;
 289              }
 290              $rs->moveNext();
 291          }
 292          return $i;
 293      }
 294      
 295  
 296  
 297      /**
 298       * Return the basic SQL query with place holders to generate 
 299       * the complete search query.
 300       *    
 301       * @param string Query string sent by the user
 302       * @return string SQL basic query string
 303       */
 304      function create_search_query_string($query)
 305      {
 306          $query = $this->clean_string($query);
 307          $words = $this->explode_in_words($query, false);
 308          $kw = array();
 309          reset($words);
 310          $nbwords = 0;
 311          foreach ($words as $word => $occ) {
 312              $kw[] = $this->con->pfx.'searchwords.word LIKE \''.$this->con->escapeStr($word).'\'';
 313              $nbwords++;
 314          }
 315          $orstring = implode(' OR ', $kw);
 316          if (strlen($orstring) == 0) $orstring = '1=0';
 317          $sql  = 'SELECT '.$this->con->pfx.'resources.*, '
 318              .$this->con->pfx.'categories.*, COUNT(*) AS total, '."\n";
 319          $sql .= 'SUM('.$this->con->pfx.'searchocc.occ) AS score '."\n";
 320          $sql .= 'FROM '.$this->con->pfx.'searchocc'."\n";
 321          $sql .= 'LEFT JOIN '.$this->con->pfx.'searchwords ON '
 322              .$this->con->pfx.'searchwords.word_id='.$this->con->pfx
 323              .'searchocc.word_id '."\n";
 324          $sql .= 'LEFT JOIN '.$this->con->pfx.'resources ON '
 325              .$this->con->pfx.'resources.resource_id='.$this->con->pfx
 326              .'searchocc.resource_id '."\n";
 327          $sql .= 'LEFT JOIN '.$this->con->pfx.'categoryasso ON '
 328              .$this->con->pfx.'categoryasso.identifier='.$this->con->pfx
 329              .'resources.identifier '."\n";
 330          $sql .= 'LEFT JOIN '.$this->con->pfx.'categories ON '
 331              .$this->con->pfx.'categoryasso.category_id='.$this->con->pfx
 332              .'categories.category_id '."\n";
 333          $sql .= 'WHERE ('.$orstring.')'."\n";
 334          $sql .= 'AND '.$this->con->pfx.'categoryasso.categoryasso_type=\''
 335              .PX_RESOURCE_CATEGORY_MAIN.'\' '."\n";
 336          $sql .= 'AND '.$this->con->pfx.'resources.website_id=\''
 337              .$this->websiteId.'\' %s '."\n";
 338          $sql .= 'GROUP BY '.$this->con->pfx
 339              .'searchocc.resource_id HAVING total=\''.$nbwords
 340              .'\' ORDER BY total DESC, score DESC';
 341  
 342          return $sql;
 343      }
 344      
 345      /**
 346       * Get the list of resources in a website with the last indexation time.
 347       *    
 348       * @param string Website to look for, if no set, all
 349       * @return mixed RecordSet or false if errors
 350       */
 351      function get_indexed_resources_stats($website='')
 352      {
 353          $website = trim($website);
 354          $extra = '';
 355          if (!empty($website)) {
 356              $extra = 'WHERE '.$this->con->pfx.'resources.website_id=\''
 357                  .$this->con->esc($website).'\''."\n";
 358          }
 359          $sql  = 'SELECT * FROM '.$this->con->pfx.'resources '."\n";
 360          $sql .= 'LEFT JOIN '.$this->con->pfx.'search 
 361             ON '.$this->con->pfx.'resources.resource_id='
 362             .$this->con->pfx.'search.resource_id '."\n";
 363          $sql .= $extra;
 364          $sql .= 'ORDER BY lastindex ASC';
 365          if (($rs = $this->con->select($sql)) === false) {
 366              $this->setError('MySQL: '.$this->con->error(), 500);
 367              return false;
 368          }
 369          return $rs;
 370      }
 371  
 372      /* ===================================================================== *
 373       *                                                                       *
 374       *                Methods for rendering the pages.                       *
 375       *                                                                       *
 376       * Note: All standalone methods.                                         *
 377       * ===================================================================== */
 378  
 379      /**
 380       * Action to display a category.
 381       *
 382       * @param string Server query string
 383       * @return int Success code
 384       */
 385      function action($query)
 386      {
 387          // Easy access
 388          $GLOBALS['_PX_render']['last'] = '';
 389          $last =& $GLOBALS['_PX_render']['last']; 
 390          $GLOBALS['_PX_render']['website'] = '';
 391          $website =& $GLOBALS['_PX_render']['website']; 
 392          $GLOBALS['_PX_render']['res'] = '';
 393          $res =& $GLOBALS['_PX_render']['res']; 
 394  
 395          // Parse query string to find the matching article
 396          $con =& pxDBConnect();
 397          $s = new Search($con, config::f('website_id'));
 398          config::setVar('query_string', Search::parseQueryString($query));
 399  
 400          $sql = $s->create_search_query_string(config::f('query_string'));
 401          $extra = ' AND '.$con->pfx.'resources.publicationdate <= '
 402              .date::stamp().' AND '.$con->pfx.'resources.enddate >= '
 403              .date::stamp().' AND '.$con->pfx.'resources.status = '
 404              .PX_RESOURCE_STATUS_VALIDE;
 405          $sql = sprintf($sql, $extra);
 406  
 407          if (($res = $con->select($sql, 'ResourceSet')) === false) {
 408              $GLOBALS['_PX_render']['error']->setError('MySQL: '
 409                                                        .$con->error(), 500);
 410              return 404;
 411          }
 412  
 413          $GLOBALS['_PX_render']['res']->autoSave(array('score', 'total'));
 414          $GLOBALS['_PX_render']['website'] = FrontEnd::getWebsite();
 415  
 416          header(FrontEnd::getHeader('search.php'));
 417          // Load the template
 418          include config::f('manager_path').'/templates/'
 419              .config::f('theme_id').'/search.php';
 420          return 200;
 421      }
 422  
 423      /**
 424       * Parse query string.
 425       *
 426       * @param string Query string
 427       * @return string Clean query string
 428       */
 429      function parseQueryString($query)
 430      {
 431          $clean = '';
 432          if (preg_match('#^/search/(.*)$#i', $query, $match)) {
 433              $clean = $match[1];
 434              if (empty($clean)) {
 435                  if (!empty($_GET['q'])) {
 436                      $clean = $_GET['q'];
 437                  }
 438              }
 439              $clean = Search::clean_string($clean);
 440              $words = Search::explode_in_words($clean, true);
 441              $clean = '';
 442              foreach ($words as $k => $v) {
 443                  $clean .= $k.' ';
 444              }
 445              $clean = trim($clean);
 446          } 
 447          return $clean;
 448      }
 449  
 450  
 451  }
 452  
 453  
 454  
 455  ?>