[TASK][!!!] Improve Backend search
authorDmitry Dulepov <dmitry@typo3.org>
Wed, 18 May 2011 11:52:59 +0000 (14:52 +0300)
committerAndreas Wolf <andreas.wolf@ikt-werk.de>
Mon, 11 Jul 2011 15:00:04 +0000 (17:00 +0200)
There are several problems with current Backend search:
* it searches far more fields than necessary (for example, TSConfig)
* it searches "uid" and other numeric fields even if the value is not
  numeric
* it uses LIKE x search on numeric fields and forces DAM to make an
  XCLASS
* it searches all tables even if they do not make sense to be searched
* it is too slow
* it searches only first four levels deep in the page tree

All these problems come from the [wrong] idea that TYPO3 can detect
where it can search. Instead, tables should be marked as "searchable"
and provide a list of fields that make sense to search. Also it makes
sense to search some fields as case insensitive. This task solves all
described issues.

After this change extensions will need to explicitely mark their tables
as searchable. Until that, those table will not be searched.

Search by default is NOT case sensitive. If the developer wants a case
sensitive search on the column, (s)he should ensure proper locale on
that column (as described at http://bit.ly/1zw8dC) and mark the column
as case-sensitive for search (see TYPO3 core API for more details).

Change-Id: I2b194dc1c746a4fc3f7663358ed08d7c9f6e11a8
Resolves: #26829
Releases: 4.6
Reviewed-on: http://review.typo3.org/2189
Reviewed-by: Stefan Neufeind
Tested-by: Stefan Neufeind
Reviewed-by: Markus Klein
Reviewed-by: Philipp Gampe
Tested-by: Philipp Gampe
Reviewed-by: Kay Strobach
Tested-by: Kay Strobach
Reviewed-by: Andreas Wolf
Tested-by: Andreas Wolf
Reviewed-by: Xavier Perseguers
t3lib/stddb/tables.php
typo3/class.db_list.inc
typo3/js/backendsearch.js
typo3/js/livesearch.js
typo3/sysext/cms/ext_tables.php
typo3/sysext/cms/tbl_tt_content.php

index d1df1a3..d14c06e 100644 (file)
@@ -179,6 +179,7 @@ $TCA['pages'] = array(
                        '255' => 'recycler.gif',
                ),
                'dynamicConfigFile' => 'T3LIB:tbl_pages.php',
+               'searchFields' => 'title,alias,nav_title,subtitle,url,keywords,description,abstract,author,author_email',
        )
 );
 
@@ -221,7 +222,8 @@ $TCA['be_users'] = array(
                'useColumnsForDefaultValues' => 'usergroup,lockToDomain,options,db_mountpoints,file_mountpoints,fileoper_perms,userMods',
                'dividers2tabs' => TRUE,
                'dynamicConfigFile' => 'T3LIB:tbl_be.php',
-               'versioningWS_alwaysAllowLiveEdit' => TRUE
+               'versioningWS_alwaysAllowLiveEdit' => TRUE,
+               'searchFields' => 'username,email,realName',
        )
 );
 
@@ -256,7 +258,8 @@ $TCA['be_groups'] = array(
                'useColumnsForDefaultValues' => 'lockToDomain, fileoper_perms',
                'dividers2tabs' => TRUE,
                'dynamicConfigFile' => 'T3LIB:tbl_be.php',
-               'versioningWS_alwaysAllowLiveEdit' => TRUE
+               'versioningWS_alwaysAllowLiveEdit' => TRUE,
+               'searchFields' => 'title',
        )
 );
 
@@ -281,7 +284,8 @@ $TCA['sys_filemounts'] = array(
                'iconfile' => '_icon_ftp.gif',
                'useColumnsForDefaultValues' => 'path,base',
                'dynamicConfigFile' => 'T3LIB:tbl_be.php',
-               'versioningWS_alwaysAllowLiveEdit' => TRUE
+               'versioningWS_alwaysAllowLiveEdit' => TRUE,
+               'searchFields' => 'title,path',
        )
 );
 
index e6bb58e..69d47d5 100644 (file)
@@ -158,8 +158,8 @@ class recordList extends t3lib_recordList {
                        $this->itemsLimitSingleTable = t3lib_utility_Math::forceIntegerInRange(intval($this->modTSconfig['properties']['itemsLimitSingleTable']), 1, 10000);
                }
 
-                       // Set select levels:
-               $sL=intval($this->searchLevels);
+                       // Set search levels:
+               $searchLevels = intval($this->searchLevels);
                $this->perms_clause = $GLOBALS['BE_USER']->getPagePermsClause(1);
 
                        // this will hide records from display - it has nothing todo with user rights!!
@@ -187,12 +187,15 @@ class recordList extends t3lib_recordList {
                        }
                }
 
-               if ($sL>0)      {
-                       $tree = $this->getTreeObject($this->id, $sL, $this->perms_clause);
+               if ($searchLevels > 0) {
+                       $tree = $this->getTreeObject($this->id, $searchLevels, $this->perms_clause);
                        $pidList = implode(',', $GLOBALS['TYPO3_DB']->cleanIntArray($tree->ids));
                        $this->pidSelect = 'pid IN (' . $pidList . ')';
+               } elseif ($searchLevels < 0) {
+                               // Search everywhere
+                       $this->pidSelect = '1=1';
                } else {
-                       $this->pidSelect = 'pid='.intval($id);
+                       $this->pidSelect = 'pid=' . intval($id);
                }
 
                        // Initialize languages:
@@ -273,7 +276,7 @@ class recordList extends t3lib_recordList {
                                                $this->pidSelect = 'pid='.intval($this->id);
                                        }
                                }
-#debug($this->pidSelect,$tableName);
+
                                        // Finally, render the list:
                                $this->HTMLcode.=$this->getTable($tableName, $this->id, implode(',',$fields));
                        }
@@ -469,7 +472,7 @@ class recordList extends t3lib_recordList {
                $pC = ($table=='pages' && $this->perms_clause)?' AND '.$this->perms_clause:'';
 
                        // Adding search constraints:
-               $search = $this->makeSearchString($table);
+               $search = $this->makeSearchString($table, $id);
 
                        // Compiling query array:
                $queryParts = array(
@@ -519,39 +522,81 @@ class recordList extends t3lib_recordList {
        }
 
        /**
-        * Creates part of query for searching after a word ($this->searchString) fields in input table
+        * Creates part of query for searching after a word ($this->searchString)
+        * fields in input table.
         *
-        * @param       string          Table, in which the fields are being searched.
-        * @return      string          Returns part of WHERE-clause for searching, if applicable.
+        * @param string $table Table, in which the fields are being searched.
+        * @param integer $currentPid Page id for the possible search limit. -1 only if called from an old XCLASS.
+        * @return string Returns part of WHERE-clause for searching, if applicable.
         */
-       function makeSearchString($table)       {
-
-                       // Make query, only if table is valid and a search string is actually defined:
-               if ($GLOBALS['TCA'][$table] && $this->searchString) {
+       function makeSearchString($table, $currentPid = -1) {
+               $result = '';
 
-                               // Loading full table description - we need to traverse fields:
-                       t3lib_div::loadTCA($table);
+               $currentPid = intval($currentPid);
+               $tablePidField = ($table == 'pages' ? 'uid' : 'pid');
 
-                               // Initialize field array:
-                       $sfields=array();
-                       $sfields[]='uid';       // Adding "uid" by default.
-
-                               // Traverse the configured columns and add all columns that can be searched:
-                       foreach($GLOBALS['TCA'][$table]['columns'] as $fieldName => $info) {
-                               if ($info['config']['type']=='text' || ($info['config']['type']=='input' && !preg_match('/date|time|int/',$info['config']['eval'])))    {
-                                       $sfields[]=$fieldName;
+                       // Make query, only if table is valid and a search string is actually defined:
+               if ($this->searchString) {
+                       $result = ' AND 0=1';
+                       if ($GLOBALS['TCA'][$table] && $GLOBALS['TCA'][$table]['ctrl']['searchFields']) {
+                               $searchableFields = t3lib_div::trimExplode(',', $GLOBALS['TCA'][$table]['ctrl']['searchFields'], TRUE);
+
+                                       // Loading full table description - we need to traverse fields:
+                               t3lib_div::loadTCA($table);
+
+                               if (t3lib_div::testInt($this->searchString)) {
+                                       $whereParts = array(
+                                               'uid=' . $this->searchString
+                                       );
+
+                                       foreach($searchableFields as $fieldName)        {
+                                               if (isset($GLOBALS['TCA'][$table]['columns'][$fieldName])) {
+                                                       $fieldConfig = &$GLOBALS['TCA'][$table]['columns'][$fieldName]['config'];
+                                                       if ($fieldConfig['type'] == 'input' && $fieldConfig['eval'] && t3lib_div::inList($fieldConfig['eval'], 'int')) {
+                                                               $condition = $fieldName . '=' . $this->searchString;
+                                                               if (is_array($fieldConfig['search']) && in_array('pidonly', $fieldConfig['search']) && $currentPid > 0) {
+                                                                       $condition = '(' . $condition . ' AND ' . $tablePidField . '=' . $currentPid . ')';
+                                                               }
+                                                               $whereParts[] = $condition;
+                                                       }
+                                               }
+                                       }
+                               } else {
+                                       $whereParts = array();
+                                       $like = '\'%' .
+                                               $GLOBALS['TYPO3_DB']->quoteStr($GLOBALS['TYPO3_DB']->escapeStrForLike($this->searchString, $table), $table) .
+                                               '%\'';
+                                       foreach($searchableFields as $fieldName) {
+                                               if (isset($GLOBALS['TCA'][$table]['columns'][$fieldName])) {
+                                                       $fieldConfig = &$GLOBALS['TCA'][$table]['columns'][$fieldName]['config'];
+                                                       $format = 'LCASE(%s) LIKE LCASE(%s)';
+                                                       if (is_array($fieldConfig['search'])) {
+                                                               if (in_array('case', $fieldConfig['search'])) {
+                                                                       $format = '%s LIKE %s';
+                                                               }
+                                                               if (in_array('pidonly', $fieldConfig['search']) && $currentPid > 0) {
+                                                                       $format = '(' . $format . ' AND ' . $tablePidField . '=' . $currentPid . ')';
+                                                               }
+                                                               if ($fieldConfig['search']['andWhere']) {
+                                                                       $format = '((' . $fieldConfig['search']['andWhere'] . ') AND (' . $format . '))';
+                                                               }
+                                                       }
+                                                       if ($fieldConfig['type'] == 'text' ||
+                                                                       $fieldConfig['type'] == 'flex' ||
+                                                                       ($fieldConfig['type'] == 'input' && (!$fieldConfig['eval'] || !preg_match('/date|time|int/',$fieldConfig['eval'])))) {
+                                                               $whereParts[] = sprintf($format, $fieldName, $like);
+                                                       }
+                                               }
+                                       }
                                }
-                       }
-
-                               // If search-fields were defined (and there always are) we create the query:
-                       if (count($sfields))    {
-                               $like = ' LIKE \'%'.$GLOBALS['TYPO3_DB']->quoteStr($this->searchString, $table).'%\'';          // Free-text searching...
-                               $queryPart = ' AND ('.implode($like.' OR ',$sfields).$like.')';
 
-                                       // Return query:
-                               return $queryPart;
+                                       // If search-fields were defined (and there always are) we create the query:
+                               if (count($whereParts)) {
+                                       $result = ' AND (' . implode(' OR ', $whereParts) . ')';
+                               }
                        }
                }
+               return $result;
        }
 
        /**
@@ -844,4 +889,4 @@ if (defined('TYPO3_MODE') && isset($GLOBALS['TYPO3_CONF_VARS'][TYPO3_MODE]['XCLA
        include_once($GLOBALS['TYPO3_CONF_VARS'][TYPO3_MODE]['XCLASS']['typo3/class.db_list.inc']);
 }
 
-?>
\ No newline at end of file
+?>
index 4865835..a68bb7d 100644 (file)
@@ -114,7 +114,7 @@ var BackendSearch = Class.create({
                                                break;
                                        case 'search':
                                                this.jump(
-                                                       unescape(TYPO3.configuration.listModulePath + 'db_list.php?id=' + jsonResponse.firstMountPoint + '&search_field=' + jsonResponse.searchFor + '&search_levels=4'),
+                                                       unescape(TYPO3.configuration.listModulePath + 'db_list.php?id=' + jsonResponse.firstMountPoint + '&search_field=' + jsonResponse.searchFor + '&search_levels=' + (jsonResponse.firstMountPoint > 0 ? '4' : '-1')),
                                                        'web_list',
                                                        'web'
                                                );
index 97c5690..bd27540 100644 (file)
@@ -44,7 +44,7 @@ TYPO3.BackendLiveSearch = Ext.extend(Ext.form.ComboBox, {
        listWidth: 315,
        listHovered: false,
        loadingText: null,
-       minChars: 2,
+       minChars: 1,
        resizable: false,
        title: null,
        width: 205,
index 3a40c27..622eba3 100755 (executable)
@@ -103,7 +103,8 @@ $TCA['tt_content'] = array (
                'thumbnail' => 'image',
                'requestUpdate' => 'list_type,rte_enabled',
                'dynamicConfigFile' => t3lib_extMgm::extPath($_EXTKEY).'tbl_tt_content.php',
-               'dividers2tabs' => 1
+               'dividers2tabs' => 1,
+               'searchFields' => 'header,header_link,subheader,bodytext,pi_flexform',
        )
 );
 
@@ -129,7 +130,8 @@ $TCA['fe_users'] = array (
                ),
                'useColumnsForDefaultValues' => 'usergroup,lockToDomain,disable,starttime,endtime',
                'dynamicConfigFile' => t3lib_extMgm::extPath($_EXTKEY).'tbl_cms.php',
-               'dividers2tabs' => 1
+               'dividers2tabs' => 1,
+               'searchFields' => 'username,name,first_name,last_name,middle_name,address,telephone,fax,email,title,zip,city,country,company',
        ),
        'feInterface' => array (
                'fe_admin_fieldList' => 'username,password,usergroup,name,address,telephone,fax,email,title,zip,city,country,www,company',
@@ -156,7 +158,8 @@ $TCA['fe_groups'] = array (
                ),
                'useColumnsForDefaultValues' => 'lockToDomain',
                'dynamicConfigFile' => t3lib_extMgm::extPath($_EXTKEY).'tbl_cms.php',
-               'dividers2tabs' => 1
+               'dividers2tabs' => 1,
+               'searchFields' => 'title,description',
        )
 );
 
@@ -178,7 +181,8 @@ $TCA['sys_domain'] = array (
                'typeicon_classes' => array(
                        'default' => 'mimetypes-x-content-domain',
                ),
-               'dynamicConfigFile' => t3lib_extMgm::extPath($_EXTKEY).'tbl_cms.php'
+               'dynamicConfigFile' => t3lib_extMgm::extPath($_EXTKEY).'tbl_cms.php',
+               'searchFields' => 'domainName,redirectTo',
        )
 );
 
@@ -212,8 +216,8 @@ $TCA['pages_language_overlay'] = array (
                'typeicon_classes' => array(
                        'default' => 'mimetypes-x-content-page-language-overlay',
                ),
-
-               'dividers2tabs'                   => TRUE
+               'dividers2tabs'                   => TRUE,
+               'searchFields' => 'title,subtitle,nav_title,keywords,description,abstract,author,author_email,url',
        )
 );
 
@@ -250,7 +254,8 @@ $TCA['sys_template'] = array (
                        '0' => 'template_add.gif'
                ),
                'dividers2tabs' => 1,
-               'dynamicConfigFile' => t3lib_extMgm::extPath($_EXTKEY).'tbl_cms.php'
+               'dynamicConfigFile' => t3lib_extMgm::extPath($_EXTKEY).'tbl_cms.php',
+               'searchFields' => 'title,constants,config',
        )
 );
 
index 70df57d..1d46e63 100755 (executable)
@@ -487,6 +487,9 @@ $TCA['tt_content'] = array(
                                        ),
                                ),
                                'softref' => 'typolink_tag,images,email[subst],url',
+                               'search' => array(
+                                       'andWhere' => 'CType=\'text\' OR CType=\'textpic\'',
+                               )
                        ),
                ),
                'text_align' => array(
@@ -1629,6 +1632,9 @@ $TCA['tt_content'] = array(
                                        ',
                                        ',media' => file_get_contents(t3lib_extMgm::extPath('cms') . 'flexform_media.xml'),
                                ),
+                               'search' => array(
+                                       'andWhere' => 'CType=\'list\''
+                               )
                        ),
                ),
                'tx_impexp_origuid' => array(