Fixed bug #14479: Speed up Oracle layer by not defaulting to dbms_lob.instr for LIKE...
authorXavier Perseguers <typo3@perseguers.ch>
Thu, 3 Jun 2010 15:27:42 +0000 (15:27 +0000)
committerXavier Perseguers <typo3@perseguers.ch>
Thu, 3 Jun 2010 15:27:42 +0000 (15:27 +0000)
git-svn-id: https://svn.typo3.org/TYPO3v4/Extensions/dbal/trunk@33885 735d13b6-9817-0410-8766-e36946ffe9aa

typo3/sysext/dbal/ChangeLog
typo3/sysext/dbal/class.ux_t3lib_sqlparser.php
typo3/sysext/dbal/tests/db_oracle_testcase.php

index d784d51..5a0bbe9 100644 (file)
@@ -1,3 +1,7 @@
+2010-06-03  Xavier Perseguers  <typo3@perseguers.ch>
+
+       * Fixed bug #14479: Speed up Oracle layer by not defaulting to dbms_lob.instr for LIKE query (thanks to Michiel Roos)
+
 2010-05-28  Xavier Perseguers  <typo3@perseguers.ch>
 
        * Fixed bug #7015: t3lib_DB displays wrong SQL in error case
index 1d9b088..527a049 100644 (file)
@@ -533,7 +533,20 @@ class ux_t3lib_sqlparser extends t3lib_sqlparser {
                                                                                        } else {
                                                                                                $compareValue = $v['value'][1] . $this->compileAddslashes(trim($v['value'][0], '%')) . $v['value'][1]; 
                                                                                        }
-                                                                                       $output .= '(dbms_lob.instr(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ', ' . $compareValue . ',1,1) > 0)';
+                                                                                               // To be on the safe side
+                                                                                       $isLob = TRUE;
+                                                                                       if ($v['table']) {
+                                                                                                       // Table and field names are quoted:
+                                                                                               $tableName = substr($v['table'], 1, strlen($v['table']) - 2);
+                                                                                               $fieldName = substr($v['field'], 1, strlen($v['field']) - 2);
+                                                                                               $fieldType = $GLOBALS['TYPO3_DB']->sql_field_metatype($tableName, $fieldName);
+                                                                                               $isLob = ($fieldType === 'B' || $fieldType === 'XL');
+                                                                                       }
+                                                                                       if ($isLob) {
+                                                                                               $output .= '(dbms_lob.instr(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ', ' . $compareValue . ',1,1) > 0)';
+                                                                                       } else {
+                                                                                               $output .= '(instr(' . trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']) . ', ' . $compareValue . ',1,1) > 0)';
+                                                                                       }
                                                                                break;
                                                                        default:
                                                                                $output .= ' ' . $v['comparator'];
index 6debe34..2c033b7 100644 (file)
@@ -297,7 +297,7 @@ class db_oracle_testcase extends BaseTestCase {
                        . ' AND sys_refindex.ref_string LIKE CONCAT(tx_dam_file_tracking.file_path, tx_dam_file_tracking.file_name)'
                ));
                $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
-               $expected .= ' AND (dbms_lob.instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."file_path","tx_dam_file_tracking"."file_name"),1,1) > 0)';
+               $expected .= ' AND (instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."file_path","tx_dam_file_tracking"."file_name"),1,1) > 0)';
                $this->assertEquals($expected, $query);
        }
 
@@ -427,7 +427,7 @@ class db_oracle_testcase extends BaseTestCase {
                $query = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery($selectFields, $fromTables, $whereClause, $groupBy, $orderBy));
 
                $expected = 'SELECT * FROM "sys_refindex", "tx_dam_file_tracking" WHERE "sys_refindex"."tablename" = \'tx_dam_file_tracking\'';
-               $expected .= ' AND (dbms_lob.instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."path","tx_dam_file_tracking"."filename"),1,1) > 0)';
+               $expected .= ' AND (instr("sys_refindex"."ref_string", CONCAT("tx_dam_file_tracking"."path","tx_dam_file_tracking"."filename"),1,1) > 0)';
                $this->assertEquals($expected, $query);
        }
 
@@ -635,6 +635,61 @@ class db_oracle_testcase extends BaseTestCase {
                $this->assertEquals($expected, $query);
        }
 
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=14479
+        */
+       public function likeIsRemappedAccordingToFieldType() {
+               $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '*',
+                       'tt_content',
+                       'tt_content.bodytext LIKE \'foo%\''
+               ));
+               $expected = 'SELECT * FROM "tt_content" WHERE (dbms_lob.instr("tt_content"."bodytext", \'foo\',1,1) > 0)';
+               $this->assertEquals($expected, $select);
+
+               $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '*',
+                       'fe_users',
+                       'fe_users.usergroup LIKE \'2\''
+               ));
+               $expected = 'SELECT * FROM "fe_users" WHERE (instr("fe_users"."usergroup", \'2\',1,1) > 0)';
+               $this->assertEquals($expected, $select);
+       }
+
+       /**
+        * @test
+        * @see http://bugs.typo3.org/view.php?id=14479
+        */
+       public function instrIsUsedForCEOnPages() {
+               $select = $this->cleanSql($GLOBALS['TYPO3_DB']->SELECTquery(
+                       '*',
+                       'tt_content',
+                       'uid IN (62) AND tt_content.deleted=0 AND tt_content.t3ver_state<=0' .
+                               ' AND tt_content.hidden=0 AND (tt_content.starttime<=1264487640)' .
+                               ' AND (tt_content.endtime=0 OR tt_content.endtime>1264487640)' .
+                               ' AND (tt_content.fe_group=\'\' OR tt_content.fe_group IS NULL OR tt_content.fe_group=\'0\'' .
+                               ' OR (tt_content.fe_group LIKE \'%,0,%\' OR tt_content.fe_group LIKE \'0,%\' OR tt_content.fe_group LIKE \'%,0\'' .
+                               ' OR tt_content.fe_group=\'0\')' .
+                               ' OR (tt_content.fe_group LIKE\'%,-1,%\' OR tt_content.fe_group LIKE \'-1,%\' OR tt_content.fe_group LIKE \'%,-1\'' .
+                               ' OR tt_content.fe_group=\'-1\'))'
+               ));
+               $expected = 'SELECT * FROM "tt_content"';
+               $expected .= ' WHERE "uid" IN (62) AND "tt_content"."deleted" = 0 AND "tt_content"."t3ver_state" <= 0';
+               $expected .= ' AND "tt_content"."hidden" = 0 AND ("tt_content"."starttime" <= 1264487640)';
+               $expected .= ' AND ("tt_content"."endtime" = 0 OR "tt_content"."endtime" > 1264487640)';
+               $expected .= ' AND ("tt_content"."fe_group" = \'\' OR "tt_content"."fe_group" IS NULL OR "tt_content"."fe_group" = \'0\'';
+               $expected .= ' OR ((instr("tt_content"."fe_group", \',0,\',1,1) > 0)';
+               $expected .= ' OR (instr("tt_content"."fe_group", \'0,\',1,1) > 0)';
+               $expected .= ' OR (instr("tt_content"."fe_group", \',0\',1,1) > 0)';
+               $expected .= ' OR "tt_content"."fe_group" = \'0\')';
+               $expected .= ' OR ((instr("tt_content"."fe_group", \',-1,\',1,1) > 0)';
+               $expected .= ' OR (instr("tt_content"."fe_group", \'-1,\',1,1) > 0)';
+               $expected .= ' OR (instr("tt_content"."fe_group", \',-1\',1,1) > 0)';
+               $expected .= ' OR "tt_content"."fe_group" = \'-1\'))';
+               $this->assertEquals($expected, $select); 
+       }
+
        ///////////////////////////////////////
        // Tests concerning DB management
        ///////////////////////////////////////