Changeset 1008 for trunk


Ignore:
Timestamp:
Jan 20, 2006, 3:34:37 PM (19 years ago)
Author:
plg
Message:

Search engine redesign, first part :

  • new table #search to store search rules associated to a search id.
  • search rules are not passed through GET anymore, the search array build in search.php is serialized in #search table, so no need to rebuild it in function include/functions_category.inc.php::category_initialize
  • search array build code is improved (efficiency and layout) in search.php
  • SQL related to search is build in a dedicated function include/functions::get_sql_search_clause
  • direct search author:<...>, date_avalaible:<...>, date_creation:<...>, keywords:<...> from picture.php are not available anymore. They will come back later, with improvement (new design). Same for date_*:<> in calendar calendar category.
Location:
trunk
Files:
1 added
6 edited

Legend:

Unmodified
Added
Removed

  • trunk/include/constants.php

    r953 r1008  
    6161define('CADDIE_TABLE', $prefixeTable.'caddie');
    6262define('UPGRADE_TABLE', $prefixeTable.'upgrade');
     63define('SEARCH_TABLE', $prefixeTable.'search');
    6364?>

  • trunk/include/functions.inc.php

    r960 r1008  
    742742
    743743/**
    744  * returns the corresponding value from $themeconf if existing. Else, the key is
    745  * returned
     744 * returns the corresponding value from $themeconf if existing. Else, the
     745 * key is returned
    746746 *
    747747 * @param string key
    … …  
    754754  return $themeconf[$key];
    755755}
     756
     757/**
     758 * Prepends and appends a string at each value of the given array.
     759 *
     760 * @param array
     761 * @param string prefix to each array values
     762 * @param string suffix to each array values
     763 */
     764function prepend_append_array_items($array, $prepend_str, $append_str)
     765{
     766  array_walk(
     767    $array,
     768    create_function('&$s', '$s = "'.$prepend_str.'".$s."'.$append_str.'";')
     769    );
     770
     771  return $array;
     772}
     773
     774/**
     775 * returns the SQL clause from a search identifier
     776 *
     777 * Search rules are stored in search table as a serialized array. This array
     778 * need to be transformed into an SQL clause to be used in queries.
     779 *
     780 * @param int search_id
     781 * @return string
     782 */
     783function get_sql_search_clause($search_id)
     784{
     785  if (!is_numeric($search_id))
     786  {
     787    die('Search id must be an integer');
     788  }
     789 
     790  $query = '
     791SELECT rules
     792  FROM '.SEARCH_TABLE.'
     793  WHERE id = '.$_GET['search'].'
     794;';
     795  list($serialized_rules) = mysql_fetch_row(pwg_query($query));
     796 
     797  $search = unserialize($serialized_rules);
     798
     799//   echo '<pre>';
     800//   print_r($search);
     801//   echo '</pre>';
     802
     803  // SQL where clauses are stored in $clauses array during query
     804  // construction
     805  $clauses = array();
     806 
     807  foreach (array('file','name','comment','keywords','author') as $textfield)
     808  {
     809    if (isset($search['fields'][$textfield]))
     810    {
     811      $local_clauses = array();
     812      foreach ($search['fields'][$textfield]['words'] as $word)
     813      {
     814        array_push($local_clauses, $textfield." LIKE '%".$word."%'");
     815      }
     816
     817      // adds brackets around where clauses
     818      $local_clauses = prepend_append_array_items($local_clauses, '(', ')');
     819
     820      array_push(
     821        $clauses,
     822        implode(
     823          ' '.$search['fields'][$textfield]['mode'].' ',
     824          $local_clauses
     825          )
     826        );
     827    }
     828  }
     829 
     830  if (isset($search['fields']['allwords']))
     831  {
     832    $fields = array('file', 'name', 'comment', 'keywords', 'author');
     833    // in the OR mode, request bust be :
     834    // ((field1 LIKE '%word1%' OR field2 LIKE '%word1%')
     835    // OR (field1 LIKE '%word2%' OR field2 LIKE '%word2%'))
     836    //
     837    // in the AND mode :
     838    // ((field1 LIKE '%word1%' OR field2 LIKE '%word1%')
     839    // AND (field1 LIKE '%word2%' OR field2 LIKE '%word2%'))
     840    $word_clauses = array();
     841    foreach ($search['fields']['allwords']['words'] as $word)
     842    {
     843      $field_clauses = array();
     844      foreach ($fields as $field)
     845      {
     846        array_push($field_clauses, $field." LIKE '%".$word."%'");
     847      }
     848      // adds brackets around where clauses
     849      array_push(
     850        $word_clauses,
     851        implode(
     852          "\n          OR ",
     853          $field_clauses
     854          )
     855        );
     856    }
     857   
     858    array_walk(
     859      $word_clauses,
     860      create_function('&$s','$s="(".$s.")";')
     861      );
     862   
     863    array_push(
     864      $clauses,
     865      "\n         ".
     866      implode(
     867        "\n         ".
     868              $search['fields']['allwords']['mode'].
     869        "\n         ",
     870        $word_clauses
     871        )
     872      );
     873  }
     874 
     875  foreach (array('date_available', 'date_creation') as $datefield)
     876  {
     877    if (isset($search['fields'][$datefield]))
     878    {
     879      array_push(
     880        $clauses,
     881        $datefield." = '".$search['fields'][$datefield]['date']."'"
     882        );
     883    }
     884   
     885    foreach (array('after','before') as $suffix)
     886    {
     887      $key = $datefield.'-'.$suffix;
     888     
     889      if (isset($search['fields'][$key]))
     890      {
     891        array_push(
     892          $clauses,
     893         
     894          $datefield.
     895          ($suffix == 'after'             ? ' >' : ' <').
     896          ($search['fields'][$key]['inc'] ? '='  : '').
     897          " '".$search['fields'][$key]['date']."'"
     898         
     899          );
     900      }
     901    }
     902  }
     903 
     904  if (isset($search['fields']['cat']))
     905  {
     906    if ($search['fields']['cat']['sub_inc'])
     907    {
     908      // searching all the categories id of sub-categories
     909      $cat_ids = get_subcat_ids($search['fields']['cat']['words']);
     910    }
     911    else
     912    {
     913      $cat_ids = $search['fields']['cat']['words'];
     914    }
     915   
     916    $local_clause = 'category_id IN ('.implode(',', $cat_ids).')';
     917    array_push($clauses, $local_clause);
     918  }
     919 
     920  // adds brackets around where clauses
     921  $clauses = prepend_append_array_items($clauses, '(', ')');
     922 
     923  $where_separator =
     924    implode(
     925      "\n    ".$search['mode'].' ',
     926      $clauses
     927      );
     928 
     929  $search_clause = $where_separator;
     930 
     931  if (isset($forbidden))
     932  {
     933    $search_clause.= "\n    AND ".$forbidden;
     934  }
     935
     936  return $search_clause;
     937}
    756938?>

  • trunk/include/functions_category.inc.php

    r1004 r1008  
    104104      $page['cat'] = $cat;
    105105    }
    106     if ($cat == 'search' and isset($_GET['search']))
     106    if ($cat == 'search'
     107        and isset($_GET['search'])
     108        and is_numeric($_GET['search']))
    107109    {
    108110      $page['cat'] = $cat;
    … …  
    382384      if ( $page['cat'] == 'search' )
    383385      {
    384         // analyze search string given in URL (created in search.php)
    385         $tokens = explode('|', $_GET['search']);
    386 
    387         if (isset($tokens[1]) and $tokens[1] == 'AND')
    388         {
    389           $search['mode'] = 'AND';
    390         }
    391         else
    392         {
    393           $search['mode'] = 'OR';
    394         }
    395 
    396         $search_tokens = explode('--', $tokens[0]);
    397         foreach ($search_tokens as $search_token)
    398         {
    399           $tokens = explode(':', $search_token);
    400           $field_name = $tokens[0];
    401           $field_content = $tokens[1];
    402 
    403           $tokens = explode('~', $tokens[1]);
    404           if (isset($tokens[1]))
    405           {
    406             $search['fields'][$field_name]['mode'] = $tokens[1];
    407           }
    408           else
    409           {
    410             $search['fields'][$field_name]['mode'] = '';
    411           }
    412 
    413           $search['fields'][$field_name]['words'] = array();
    414           $tokens = explode(',', $tokens[0]);
    415           foreach ($tokens as $token)
    416           {
    417             array_push($search['fields'][$field_name]['words'],
    418                        htmlentities($token));
    419           }
    420         }
    421        
    422386        $page['title'] = $lang['search_result'];
    423387        if ( $calling_page == 'picture' )
    … …  
    427391        }
    428392
    429         // SQL where clauses are stored in $clauses array during query
    430         // construction
    431         $clauses = array();
     393        $page['where'] = 'WHERE '.get_sql_search_clause($_GET['search']);
    432394       
    433         $textfields = array('file', 'name', 'comment', 'keywords', 'author');
    434         foreach ($textfields as $textfield)
    435         {
    436           if (isset($search['fields'][$textfield]))
    437           {
    438             $local_clauses = array();
    439             foreach ($search['fields'][$textfield]['words'] as $word)
    440             {
    441               array_push($local_clauses, $textfield." LIKE '%".$word."%'");
    442             }
    443             // adds brackets around where clauses
    444             array_walk($local_clauses,create_function('&$s','$s="(".$s.")";'));
    445             array_push($clauses,
    446                        implode(' '.$search['fields'][$textfield]['mode'].' ',
    447                                $local_clauses));
    448           }
    449         }
    450 
    451         if (isset($search['fields']['allwords']))
    452         {
    453           $fields = array('file', 'name', 'comment', 'keywords', 'author');
    454           // in the OR mode, request bust be :
    455           // ((field1 LIKE '%word1%' OR field2 LIKE '%word1%')
    456           // OR (field1 LIKE '%word2%' OR field2 LIKE '%word2%'))
    457           //
    458           // in the AND mode :
    459           // ((field1 LIKE '%word1%' OR field2 LIKE '%word1%')
    460           // AND (field1 LIKE '%word2%' OR field2 LIKE '%word2%'))
    461           $word_clauses = array();
    462           foreach ($search['fields']['allwords']['words'] as $word)
    463           {
    464             $field_clauses = array();
    465             foreach ($fields as $field)
    466             {
    467               array_push($field_clauses, $field." LIKE '%".$word."%'");
    468             }
    469             // adds brackets around where clauses
    470             array_push($word_clauses, implode(' OR ', $field_clauses));
    471           }
    472           array_walk($word_clauses, create_function('&$s','$s="(".$s.")";'));
    473           array_push($clauses,
    474                      implode(' '.$search['fields']['allwords']['mode'].' ',
    475                                $word_clauses));
    476         }
    477 
    478         $datefields = array('date_available', 'date_creation');
    479         foreach ($datefields as $datefield)
    480         {
    481           $key = $datefield;
    482           if (isset($search['fields'][$key]))
    483           {
    484             $local_clause = $datefield." = '";
    485             $local_clause.= str_replace('.', '-',
    486                                         $search['fields'][$key]['words'][0]);
    487             $local_clause.= "'";
    488             array_push($clauses, $local_clause);
    489           }
    490 
    491           foreach (array('after','before') as $suffix)
    492           {
    493             $key = $datefield.'-'.$suffix;
    494             if (isset($search['fields'][$key]))
    495             {
    496               $local_clause = $datefield;
    497               if ($suffix == 'after')
    498               {
    499                 $local_clause.= ' >';
    500               }
    501               else
    502               {
    503                 $local_clause.= ' <';
    504               }
    505               if (isset($search['fields'][$key]['mode'])
    506                   and $search['fields'][$key]['mode'] == 'inc')
    507               {
    508                 $local_clause.= '=';
    509               }
    510               $local_clause.= " '";
    511               $local_clause.= str_replace('.', '-',
    512                                           $search['fields'][$key]['words'][0]);
    513               $local_clause.= "'";
    514               array_push($clauses, $local_clause);
    515             }
    516           }
    517         }
    518 
    519         if (isset($search['fields']['cat']))
    520         {
    521           if ($search['fields']['cat']['mode'] == 'sub_inc')
    522           {
    523             // searching all the categories id of sub-categories
    524             $cat_ids = get_subcat_ids($search['fields']['cat']['words']);
    525           }
    526           else
    527           {
    528             $cat_ids = $search['fields']['cat']['words'];
    529           }
    530          
    531           $local_clause = 'category_id IN ('.implode(',', $cat_ids).')';
    532           array_push($clauses, $local_clause);
    533         }
    534 
    535         // adds brackets around where clauses
    536         array_walk($clauses, create_function('&$s', '$s = "(".$s.")";'));
    537         $page['where'] = 'WHERE '.implode(' '.$search['mode'].' ', $clauses);
    538         if ( isset( $forbidden ) ) $page['where'].= ' AND '.$forbidden;
     395        if (isset($forbidden))
     396        {
     397          $page['where'].= "\n    AND ".$forbidden;
     398        }
    539399
    540400        $query = '

  • trunk/install/phpwebgallery_structure.sql

    r1004 r1008  
    178178
    179179--
     180-- Table structure for table `phpwebgallery_search`
     181--
     182
     183DROP TABLE IF EXISTS `phpwebgallery_search`;
     184CREATE TABLE `phpwebgallery_search` (
     185  `id` int(10) unsigned NOT NULL auto_increment,
     186  `last_seen` date default NULL,
     187  `rules` text,
     188  PRIMARY KEY  (`id`)
     189) TYPE=MyISAM;
     190
     191--
    180192-- Table structure for table `phpwebgallery_sessions`
    181193--

  • trunk/picture.php

    r1004 r1008  
    676676{
    677677  $infos['INFO_AUTHOR'] =
    678     '<a href="'.
    679       PHPWG_ROOT_PATH.'category.php?cat=search'.
    680       '&amp;search=author:'.$picture['current']['author']
    681       .'">'.$picture['current']['author'].'</a>';
     678    // FIXME because of search engine partial rewrite, giving the author
     679    // name threw GET is not supported anymore. This feature should come
     680    // back later, with a better design
     681//     '<a href="'.
     682//       PHPWG_ROOT_PATH.'category.php?cat=search'.
     683//       '&amp;search=author:'.$picture['current']['author']
     684//       .'">'.$picture['current']['author'].'</a>';
     685    $picture['current']['author'];
    682686}
    683687else
    … …  
    690694{
    691695  $infos['INFO_CREATION_DATE'] =
    692     '<a href="'.
    693       PHPWG_ROOT_PATH.'category.php?cat=search'.
    694       '&amp;search=date_creation:'.$picture['current']['date_creation']
    695       .'">'.format_date($picture['current']['date_creation']).'</a>';
     696    // FIXME because of search engine partial rewrite, giving the author
     697    // name threw GET is not supported anymore. This feature should come
     698    // back later, with a better design (calendar view).
     699//     '<a href="'.
     700//       PHPWG_ROOT_PATH.'category.php?cat=search'.
     701//       '&amp;search=date_creation:'.$picture['current']['date_creation']
     702//       .'">'.format_date($picture['current']['date_creation']).'</a>';
     703    format_date($picture['current']['date_creation']);
    696704}
    697705else
    … …  
    702710// date of availability
    703711$infos['INFO_AVAILABILITY_DATE'] =
    704   '<a href="'.
    705     PHPWG_ROOT_PATH.'category.php?cat=search'.
    706     '&amp;search=date_available:'.
    707     substr($picture['current']['date_available'], 0, 10)
    708     .'">'.
    709   format_date($picture['current']['date_available'], 'mysql_datetime').
    710   '</a>';
     712// FIXME because of search engine partial rewrite, giving the author
     713// name threw GET is not supported anymore. This feature should come
     714// back later, with a better design (calendar view).
     715//
     716//   '<a href="'.
     717//     PHPWG_ROOT_PATH.'category.php?cat=search'.
     718//     '&amp;search=date_available:'.
     719//     substr($picture['current']['date_available'], 0, 10)
     720//     .'">'.
     721//   format_date($picture['current']['date_available'], 'mysql_datetime').
     722//   '</a>';
     723format_date($picture['current']['date_available'], 'mysql_datetime');
    711724
    712725// size in pixels
    … …  
    752765{
    753766  $infos['INFO_KEYWORDS'] =
    754     preg_replace(
    755       '/([^,]+)/',
    756       '<a href="'.
    757         PHPWG_ROOT_PATH.'category.php?cat=search&amp;search=keywords:$1'
    758         .'">$1</a>',
    759       $picture['current']['keywords']
    760       );
     767    // FIXME because of search engine partial rewrite, giving the author
     768    // name threw GET is not supported anymore. This feature should come
     769    // back later, with a better design (tag classification).
     770//     preg_replace(
     771//       '/([^,]+)/',
     772//       '<a href="'.
     773//         PHPWG_ROOT_PATH.'category.php?cat=search&amp;search=keywords:$1'
     774//         .'">$1</a>',
     775//       $picture['current']['keywords']
     776//       );
     777    $picture['current']['keywords'];
    761778}
    762779else

  • trunk/search.php

    r1004 r1008  
    3838  if (isset($_POST['search_allwords'])
    3939      and !preg_match('/^\s*$/', $_POST['search_allwords']))
    40   {
    41     $local_search = array();
    42     $search_allwords = $_POST['search_allwords'];
     40  {   
    4341    $drop_char_match = array(
    4442      '-','^','$',';','#','&','(',')','<','>','`','\'','"','|',',','@','_',
    … …  
    4745      ' ',' ',' ',' ',' ',' ',' ',' ',' ',' ','','',' ',' ',' ',' ','',' ',
    4846      ' ',' ',' ',' ',' ',' ',' ',' ','' ,' ',' ',' ',' ',' ');
    49     $search_allwords = str_replace($drop_char_match,
    50                                    $drop_char_replace,
    51                                    $search_allwords);
    5247       
    5348    // Split words
    54     $words = preg_split('/\s+/', $search_allwords);
    55     $words = array_unique($words);
    56     $search['fields']['allwords'] = array();
    57     $search['fields']['allwords']['words'] = $words;
    58     $search['fields']['allwords']['mode'] = $_POST['mode'];
     49    $search['fields']['allwords'] = array(
     50      'words' => array_unique(
     51        preg_split(
     52          '/\s+/',
     53          str_replace(
     54            $drop_char_match,
     55            $drop_char_replace,
     56            $_POST['search_allwords']
     57            )
     58          )
     59        ),
     60      'mode' => $_POST['mode'],
     61      );
    5962  }
    6063 
    6164  if ($_POST['search_author'])
    6265  {
    63     $search['fields']['author'] = array();
    64     $search['fields']['author']['words'] = array($_POST['search_author']);
     66    $search['fields']['author'] = array(
     67      'words' => array($_POST['search_author']),
     68      );
    6569  }
    6670 
    6771  if (isset($_POST['cat']))
    6872  {
    69     $search['fields']['cat'] = array();
    70     $search['fields']['cat']['words'] = $_POST['cat'];
    71     if ($_POST['subcats-included'] == 1)
    72     {
    73       $search['fields']['cat']['mode'] = 'sub_inc';
    74     }
     73    $search['fields']['cat'] = array(
     74      'words'   => $_POST['cat'],
     75      'sub_inc' => ($_POST['subcats-included'] == 1) ? true : false,
     76      );
    7577  }
    7678
    … …  
    8082  if (!empty($_POST['start_year']))
    8183  {
    82     $year = $_POST['start_year'];
    83     $month = $_POST['start_month'] != 0 ? $_POST['start_month'] : '01';
    84     $day = $_POST['start_day'] != 0 ? $_POST['start_day'] : '01';
    85     $date = $year.'-'.$month.'-'.$day;
     84//     $year = $_POST['start_year'];
     85//     $month = $_POST['start_month'] != 0 ? $_POST['start_month'] : '01';
     86//     $day = $_POST['start_day'] != 0 ? $_POST['start_day'] : '01';
     87//     $date = $year.'-'.$month.'-'.$day;
    8688   
    87     $search['fields'][$type_date.'-after']['words'] = array($date);
    88     $search['fields'][$type_date.'-after']['mode'] = 'inc';
     89//     $search['fields'][$type_date.'-after']['words'] = array($date);
     90//     $search['fields'][$type_date.'-after']['mode'] = 'inc';
     91
     92    $search['fields'][$type_date.'-after'] = array(
     93      'date' => join(
     94        '-',
     95        array(
     96          $_POST['start_year'],
     97          $_POST['start_month'] != 0 ? $_POST['start_month'] : '01',
     98          $_POST['start_day']   != 0 ? $_POST['start_day']   : '01',
     99          )
     100        ),
     101      'inc' => true,
     102      );
    89103  }
    90104
    91105  if (!empty($_POST['end_year']))
    92106  {
    93     $year = $_POST['end_year'];
    94     $month = $_POST['end_month'] != 0 ? $_POST['end_month'] : '12';
    95     $day = $_POST['end_day'] != 0 ? $_POST['end_day'] : '31';
    96     $date = $year.'-'.$month.'-'.$day;
     107//     $year = $_POST['end_year'];
     108//     $month = $_POST['end_month'] != 0 ? $_POST['end_month'] : '12';
     109//     $day = $_POST['end_day'] != 0 ? $_POST['end_day'] : '31';
     110//     $date = $year.'-'.$month.'-'.$day;
    97111   
    98     $search['fields'][$type_date.'-before']['words'] = array($date);
    99     $search['fields'][$type_date.'-before']['mode'] = 'inc';
    100   }
     112    $search['fields'][$type_date.'-before'] = array(
     113      'date' => join(
     114        '-',
     115        array(
     116          $_POST['end_year'],
     117          $_POST['end_month'] != 0 ? $_POST['end_month'] : '12',
     118          $_POST['end_day']   != 0 ? $_POST['end_day']   : '31',
     119          )
     120        ),
     121      'inc' => true,
     122      );
     123  }
     124 
     125  if (!empty($search))
     126  {
     127    // default search mode : each clause must be respected
     128    $search['mode'] = 'AND';
     129
     130//     echo '<pre>';
     131//     print_r($_POST);
     132//     echo '</pre>';
     133
     134//     echo '<pre>';
     135//     print_r($search);
     136//     echo '</pre>';
    101137   
    102   // search string (for URL) creation
    103   $search_string = '';
    104   $tokens = array();
    105   if (!empty($search))
    106   {
    107     foreach (array_keys($search['fields']) as $field)
    108     {
    109       $token = $field.':';
    110       $token.= implode(',', $search['fields'][$field]['words']);
    111       if (isset($search['fields'][$field]['mode']))
    112       {
    113         $token.= '~'.$search['fields'][$field]['mode'];
    114       }
    115       array_push($tokens, $token);
    116     }
    117     $search_string.= implode('--', $tokens);
    118     if (count($tokens) > 1)
    119     {
    120       $search_string.= '|AND';
    121     }
     138    // register search rules in database, then they will be available on
     139    // thumbnails page and picture page.
     140    $query ='
     141INSERT INTO '.SEARCH_TABLE.'
     142  (rules)
     143  VALUES
     144  (\''.serialize($search).'\')
     145;';
     146    pwg_query($query);
     147
     148    $search_id = mysql_insert_id();
    122149  }
    123150  else
    … …  
    129156if (isset($_POST['submit']) and count($errors) == 0)
    130157{
    131   $url = 'category.php?cat=search&search='.$search_string;
     158  $url = 'category.php?cat=search&search='.$search_id;
    132159  redirect($url);
    133160}
Note: See TracChangeset for help on using the changeset viewer.