1 <?php

2 namespace TYPO3\CMS\Dbal\Database;

3 

16 use TYPO3\CMS\Core\Utility\GeneralUtility;

17 

25 class SqlParser extends \TYPO3\CMS\Core\Database\SqlParser {

26 

30  protected $databaseConnection;

31 

35  public function __construct(DatabaseConnection $databaseConnection = NULL) {

36  parent::__construct();

37 

38  $this->databaseConnection = $databaseConnection ?: $GLOBALS['TYPO3_DB'];

39  }

40 

48  protected function getValueInQuotes(&$parseString, $quote) {

49  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {

50  case 'adodb':

51  if ($this->databaseConnection->runningADOdbDriver('mssql')) {

52  $value = $this->getValueInQuotesMssql($parseString, $quote);

53  } else {

54  $value = parent::getValueInQuotes($parseString, $quote);

55  }

56  break;

57  default:

58  $value = parent::getValueInQuotes($parseString, $quote);

59  }

60  return $value;

61  }

62 

70  protected function getValueInQuotesMssql(&$parseString, $quote) {

71  $previousIsQuote = FALSE;

72  $inQuote = FALSE;

73  // Go through the whole string

74  for ($c = 0; $c < strlen($parseString); $c++) {

75  // If the parsed string character is the quote string

76  if ($parseString[$c] === $quote) {

77  // If we are already in a quote

78  if ($inQuote) {

79  // Was the previous a quote?

80  if ($previousIsQuote) {

81  // If yes, replace it by a \

82  $parseString[$c - 1] = '\\';

83  }

84  // Invert the state

85  $previousIsQuote = !$previousIsQuote;

86  } else {

87  // So we are in a quote since now

88  $inQuote = TRUE;

89  }

90  } elseif ($inQuote && $previousIsQuote) {

91  $inQuote = FALSE;

92  $previousIsQuote = FALSE;

93  } else {

94  $previousIsQuote = FALSE;

95  }

96  }

97  $parts = explode($quote, substr($parseString, 1));

98  $buffer = '';

99  foreach ($parts as $v) {

100  $buffer .= $v;

101  $reg = array();

102  preg_match('/\\\\$/', $v, $reg);

103  if ($reg && strlen($reg[0]) % 2) {

104  $buffer .= $quote;

105  } else {

106  $parseString = ltrim(substr($parseString, strlen($buffer) + 2));

107  return $this->parseStripslashes($buffer);

108  }

109  }

110  return '';

111  }

112 

123  public function compileFieldList($selectFields, $compileComments = TRUE, $functionMapping = TRUE) {

124  $output = '';

125  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {

126  case 'native':

127  $output = parent::compileFieldList($selectFields, $compileComments);

128  break;

129  case 'adodb':

130  // Traverse the selectFields if any:

131  if (is_array($selectFields)) {

132  $outputParts = array();

133  foreach ($selectFields as $k => $v) {

134  // Detecting type:

135  switch ($v['type']) {

136  case 'function':

137  $outputParts[$k] = $v['function'] . '(' . $v['func_content'] . ')';

138  break;

139  case 'flow-control':

140  if ($v['flow-control']['type'] === 'CASE') {

141  $outputParts[$k] = $this->compileCaseStatement($v['flow-control'], $functionMapping);

142  }

143  break;

144  case 'field':

145  $outputParts[$k] = ($v['distinct'] ? $v['distinct'] : '') . ($v['table'] ? $v['table'] . '.' : '') . $v['field'];

146  break;

147  }

148  // Alias:

149  if ($v['as']) {

150  $outputParts[$k] .= ' ' . $v['as_keyword'] . ' ' . $v['as'];

151  }

152  // Specifically for ORDER BY and GROUP BY field lists:

153  if ($v['sortDir']) {

154  $outputParts[$k] .= ' ' . $v['sortDir'];

155  }

156  }

157  // TODO: Handle SQL hints in comments according to current DBMS

158  if (FALSE && $selectFields[0]['comments']) {

159  $output = $selectFields[0]['comments'] . ' ';

160  }

161  $output .= implode(', ', $outputParts);

162  }

163  break;

164  }

165  return $output;

166  }

167 

176  protected function compileCaseStatement(array $components, $functionMapping = TRUE) {

177  $output = '';

178  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {

179  case 'native':

180  $output = parent::compileCaseStatement($components);

181  break;

182  case 'adodb':

183  $statement = 'CASE';

184  if (isset($components['case_field'])) {

185  $statement .= ' ' . $components['case_field'];

186  } elseif (isset($components['case_value'])) {

187  $statement .= ' ' . $components['case_value'][1] . $components['case_value'][0] . $components['case_value'][1];

188  }

189  foreach ($components['when'] as $when) {

190  $statement .= ' WHEN ';

191  $statement .= $this->compileWhereClause($when['when_value'], $functionMapping);

192  $statement .= ' THEN ';

193  $statement .= $when['then_value'][1] . $when['then_value'][0] . $when['then_value'][1];

194  }

195  if (isset($components['else'])) {

196  $statement .= ' ELSE ';

197  $statement .= $components['else'][1] . $components['else'][0] . $components['else'][1];

198  }

199  $statement .= ' END';

200  $output = $statement;

201  break;

202  }

203  return $output;

204  }

205 

214  protected function compileAddslashes($str) {

215  // DatabaseConnection::quoteWhereClause() returns an unmodified where clause in native mode,

216  // escaping of special characters needs to be done here.

217  if ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type'] === 'native') {

218  return parent::compileAddslashes($str);

219  }

220 

221  // Return unmodified value, DBMS specific escaping is handled in DatabaseConnection::quoteWhereClause()

222  return $str;

223  }

224 

225  /*************************

226  *

227  * Compiling queries

228  *

229  *************************/

237  protected function compileINSERT($components) {

238  $query = '';

239  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {

240  case 'native':

241  $query = parent::compileINSERT($components);

242  break;

243  case 'adodb':

244  $values = array();

245  if (isset($components['VALUES_ONLY']) && is_array($components['VALUES_ONLY'])) {

246  $valuesComponents = $components['EXTENDED'] === '1' ? $components['VALUES_ONLY'] : array($components['VALUES_ONLY']);

247  $tableFields = array_keys($this->databaseConnection->cache_fieldType[$components['TABLE']]);

248  } else {

249  $valuesComponents = $components['EXTENDED'] === '1' ? $components['FIELDS'] : array($components['FIELDS']);

250  $tableFields = array_keys($valuesComponents[0]);

251  }

252  foreach ($valuesComponents as $valuesComponent) {

253  $fields = array();

254  $fc = 0;

255  foreach ($valuesComponent as $fV) {

256  $fields[$tableFields[$fc++]] = $fV[0];

257  }

258  $values[] = $fields;

259  }

260  $query = count($values) === 1 ? $values[0] : $values;

261  break;

262  }

263  return $query;

264  }

265 

273  public function compileCREATETABLE($components) {

274  $query = array();

275  // Execute query (based on handler derived from the TABLE name which we actually know for once!)

276  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->handler_getFromTableList($components['TABLE'])]['type']) {

277  case 'native':

278  $query[] = parent::compileCREATETABLE($components);

279  break;

280  case 'adodb':

281  // Create fields and keys:

282  $fieldsKeys = array();

283  $indexKeys = array();

284  foreach ($components['FIELDS'] as $fN => $fCfg) {

285  $handlerKey = $this->databaseConnection->handler_getFromTableList($components['TABLE']);

286  $fieldsKeys[$fN] = $this->databaseConnection->quoteName($fN, $handlerKey, TRUE) . ' ' . $this->compileFieldCfg($fCfg['definition']);

287  }

288  if (isset($components['KEYS']) && is_array($components['KEYS'])) {

289  foreach ($components['KEYS'] as $kN => $kCfg) {

290  if ($kN === 'PRIMARYKEY') {

291  foreach ($kCfg as $field) {

292  $fieldsKeys[$field] .= ' PRIMARY';

293  }

294  } elseif ($kN === 'UNIQUE') {

295  foreach ($kCfg as $n => $field) {

296  $indexKeys = array_merge($indexKeys, $this->databaseConnection->handlerInstance[$this->databaseConnection->handler_getFromTableList($components['TABLE'])]->DataDictionary->CreateIndexSQL($n, $components['TABLE'], $field, array('UNIQUE')));

297  }

298  } else {

299  $indexKeys = array_merge($indexKeys, $this->databaseConnection->handlerInstance[$this->databaseConnection->handler_getFromTableList($components['TABLE'])]->DataDictionary->CreateIndexSQL($components['TABLE'] . '_' . $kN, $components['TABLE'], $kCfg));

300  }

301  }

302  }

303  // Generally create without OID on PostgreSQL

304  $tableOptions = array('postgres' => 'WITHOUT OIDS');

305  // Fetch table/index generation query:

306  $tableName = $this->databaseConnection->quoteName($components['TABLE'], NULL, TRUE);

307  $query = array_merge($this->databaseConnection->handlerInstance[$this->databaseConnection->lastHandlerKey]->DataDictionary->CreateTableSQL($tableName, implode(',' . chr(10), $fieldsKeys), $tableOptions), $indexKeys);

308  break;

309  }

310  return $query;

311  }

312 

320  public function compileALTERTABLE($components) {

321  $query = '';

322  // Execute query (based on handler derived from the TABLE name which we actually know for once!)

323  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {

324  case 'native':

325  $query = parent::compileALTERTABLE($components);

326  break;

327  case 'adodb':

328  $tableName = $this->databaseConnection->quoteName($components['TABLE'], NULL, TRUE);

329  $fieldName = $this->databaseConnection->quoteName($components['FIELD'], NULL, TRUE);

330  switch (strtoupper(str_replace(array(' ', "\n", "\r", "\t"), '', $components['action']))) {

331  case 'ADD':

332  $query = $this->databaseConnection->handlerInstance[$this->databaseConnection->lastHandlerKey]->DataDictionary->AddColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));

333  break;

334  case 'CHANGE':

335  $query = $this->databaseConnection->handlerInstance[$this->databaseConnection->lastHandlerKey]->DataDictionary->AlterColumnSQL($tableName, $fieldName . ' ' . $this->compileFieldCfg($components['definition']));

336  break;

337  case 'DROP':

338 

339  case 'DROPKEY':

340  break;

341  case 'ADDKEY':

342 

343  case 'ADDPRIMARYKEY':

344 

345  case 'ADDUNIQUE':

346  $query .= ' (' . implode(',', $components['fields']) . ')';

347  break;

348  case 'DEFAULTCHARACTERSET':

349 

350  case 'ENGINE':

351  // ??? todo!

352  break;

353  }

354  break;

355  }

356  return $query;

357  }

358 

365  public function compileFieldCfg($fieldCfg) {

366  $cfg = '';

367  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {

368  case 'native':

369  $cfg = parent::compileFieldCfg($fieldCfg);

370  break;

371  case 'adodb':

372  // Set type:

373  $type = $this->databaseConnection->MySQLMetaType($fieldCfg['fieldType']);

374  $cfg = $type;

375  // Add value, if any:

376  if (strlen($fieldCfg['value']) && in_array($type, array('C', 'C2'))) {

377  $cfg .= ' ' . $fieldCfg['value'];

378  } elseif (!isset($fieldCfg['value']) && in_array($type, array('C', 'C2'))) {

379  $cfg .= ' 255';

380  }

381  // Add additional features:

382  $noQuote = TRUE;

383  if (is_array($fieldCfg['featureIndex'])) {

384  // MySQL assigns DEFAULT value automatically if NOT NULL, fake this here

385  // numeric fields get 0 as default, other fields an empty string

386  if (isset($fieldCfg['featureIndex']['NOTNULL']) && !isset($fieldCfg['featureIndex']['DEFAULT']) && !isset($fieldCfg['featureIndex']['AUTO_INCREMENT'])) {

387  switch ($type) {

388  case 'I8':

389 

390  case 'F':

391 

392  case 'N':

393  $fieldCfg['featureIndex']['DEFAULT'] = array('keyword' => 'DEFAULT', 'value' => array('0', ''));

394  break;

395  default:

396  $fieldCfg['featureIndex']['DEFAULT'] = array('keyword' => 'DEFAULT', 'value' => array('', '\''));

397  }

398  }

399  foreach ($fieldCfg['featureIndex'] as $feature => $featureDef) {

400  switch (TRUE) {

401  case $feature === 'UNSIGNED' && !$this->databaseConnection->runningADOdbDriver('mysql'):

402  case $feature === 'NOTNULL' && $this->databaseConnection->runningADOdbDriver('oci8'):

403  continue;

404  case $feature === 'AUTO_INCREMENT':

405  $cfg .= ' AUTOINCREMENT';

406  break;

407  case $feature === 'NOTNULL':

408  $cfg .= ' NOTNULL';

409  break;

410  default:

411  $cfg .= ' ' . $featureDef['keyword'];

412  }

413  // Add value if found:

414  if (is_array($featureDef['value'])) {

415  if ($featureDef['value'][0] === '') {

416  $cfg .= ' "\'\'"';

417  } else {

418  $cfg .= ' ' . $featureDef['value'][1] . $this->compileAddslashes($featureDef['value'][0]) . $featureDef['value'][1];

419  if (!is_numeric($featureDef['value'][0])) {

420  $noQuote = FALSE;

421  }

422  }

423  }

424  }

425  }

426  if ($noQuote) {

427  $cfg .= ' NOQUOTE';

428  }

429  break;

430  }

431  // Return field definition string:

432  return $cfg;

433  }

434 

442  public function checkEmptyDefaultValue($featureIndex) {

443  if (!is_array($featureIndex['DEFAULT']['value'])) {

444  return TRUE;

445  }

446  return !is_numeric($featureIndex['DEFAULT']['value'][0]) && empty($featureIndex['DEFAULT']['value'][0]);

447  }

448 

465  public function compileWhereClause($clauseArray, $functionMapping = TRUE) {

466  $output = '';

467  switch ((string)$this->databaseConnection->handlerCfg[$this->databaseConnection->lastHandlerKey]['type']) {

468  case 'native':

469  $output = parent::compileWhereClause($clauseArray);

470  break;

471  case 'adodb':

472  // Prepare buffer variable:

473  $output = '';

474  // Traverse clause array:

475  if (is_array($clauseArray)) {

476  foreach ($clauseArray as $v) {

477  // Set operator:

478  $output .= $v['operator'] ? ' ' . $v['operator'] : '';

479  // Look for sublevel:

480  if (is_array($v['sub'])) {

481  $output .= ' (' . trim($this->compileWhereClause($v['sub'], $functionMapping)) . ')';

482  } elseif (isset($v['func']) && $v['func']['type'] === 'EXISTS') {

483  $output .= ' ' . trim($v['modifier']) . ' EXISTS (' . $this->compileSELECT($v['func']['subquery']) . ')';

484  } else {

485  if (isset($v['func']) && $v['func']['type'] === 'LOCATE') {

486  $output .= ' ' . trim($v['modifier']);

487  switch (TRUE) {

488  case $this->databaseConnection->runningADOdbDriver('mssql') && $functionMapping:

489  $output .= ' CHARINDEX(';

490  $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];

491  $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

492  $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';

493  $output .= ')';

494  break;

495  case $this->databaseConnection->runningADOdbDriver('oci8') && $functionMapping:

496  $output .= ' INSTR(';

497  $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

498  $output .= ', ' . $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];

499  $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';

500  $output .= ')';

501  break;

502  default:

503  $output .= ' LOCATE(';

504  $output .= $v['func']['substr'][1] . $v['func']['substr'][0] . $v['func']['substr'][1];

505  $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

506  $output .= isset($v['func']['pos']) ? ', ' . $v['func']['pos'][0] : '';

507  $output .= ')';

508  }

509  } elseif (isset($v['func']) && $v['func']['type'] === 'IFNULL') {

510  $output .= ' ' . trim($v['modifier']) . ' ';

511  switch (TRUE) {

512  case $this->databaseConnection->runningADOdbDriver('mssql') && $functionMapping:

513  $output .= 'ISNULL';

514  break;

515  case $this->databaseConnection->runningADOdbDriver('oci8') && $functionMapping:

516  $output .= 'NVL';

517  break;

518  default:

519  $output .= 'IFNULL';

520  }

521  $output .= '(';

522  $output .= ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

523  $output .= ', ' . $v['func']['default'][1] . $this->compileAddslashes($v['func']['default'][0]) . $v['func']['default'][1];

524  $output .= ')';

525  } elseif (isset($v['func']) && $v['func']['type'] === 'FIND_IN_SET') {

526  $output .= ' ' . trim($v['modifier']) . ' ';

527  if ($functionMapping) {

528  switch (TRUE) {

529  case $this->databaseConnection->runningADOdbDriver('mssql'):

530  $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

531  if (!isset($v['func']['str_like'])) {

532  $v['func']['str_like'] = $v['func']['str'][0];

533  }

534  $output .= '\',\'+' . $field . '+\',\' LIKE \'%,' . $v['func']['str_like'] . ',%\'';

535  break;

536  case $this->databaseConnection->runningADOdbDriver('oci8'):

537  $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

538  if (!isset($v['func']['str_like'])) {

539  $v['func']['str_like'] = $v['func']['str'][0];

540  }

541  $output .= '\',\'||' . $field . '||\',\' LIKE \'%,' . $v['func']['str_like'] . ',%\'';

542  break;

543  case $this->databaseConnection->runningADOdbDriver('postgres'):

544  $output .= ' FIND_IN_SET(';

545  $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1];

546  $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

547  $output .= ') != 0';

548  break;

549  default:

550  $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

551  if (!isset($v['func']['str_like'])) {

552  $v['func']['str_like'] = $v['func']['str'][0];

553  }

554  $output .= '(' . $field . ' LIKE \'%,' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'%,' . $v['func']['str_like'] . '\'' . ' OR ' . $field . '= ' . $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1] . ')';

555  }

556  } else {

557  switch (TRUE) {

558  case $this->databaseConnection->runningADOdbDriver('mssql'):

559 

560  case $this->databaseConnection->runningADOdbDriver('oci8'):

561 

562  case $this->databaseConnection->runningADOdbDriver('postgres'):

563  $output .= ' FIND_IN_SET(';

564  $output .= $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1];

565  $output .= ', ' . ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

566  $output .= ')';

567  break;

568  default:

569  $field = ($v['func']['table'] ? $v['func']['table'] . '.' : '') . $v['func']['field'];

570  if (!isset($v['func']['str_like'])) {

571  $v['func']['str_like'] = $v['func']['str'][0];

572  }

573  $output .= '(' . $field . ' LIKE \'%,' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'' . $v['func']['str_like'] . ',%\'' . ' OR ' . $field . ' LIKE \'%,' . $v['func']['str_like'] . '\'' . ' OR ' . $field . '= ' . $v['func']['str'][1] . $v['func']['str'][0] . $v['func']['str'][1] . ')';

574  }

575  }

576  } else {

577  // Set field/table with modifying prefix if any:

578  $output .= ' ' . trim($v['modifier']) . ' ';

579  // DBAL-specific: Set calculation, if any:

580  if ($v['calc'] === '&' && $functionMapping) {

581  switch (TRUE) {

582  case $this->databaseConnection->runningADOdbDriver('oci8'):

583  // Oracle only knows BITAND(x,y) - sigh

584  $output .= 'BITAND(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . ',' . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1] . ')';

585  break;

586  default:

587  // MySQL, MS SQL Server, PostgreSQL support the &-syntax

588  $output .= trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . $v['calc'] . $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];

589  }

590  } elseif ($v['calc']) {

591  $output .= trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . $v['calc'];

592  if (isset($v['calc_table'])) {

593  $output .= trim(($v['calc_table'] ? $v['calc_table'] . '.' : '') . $v['calc_field']);

594  } else {

595  $output .= $v['calc_value'][1] . $this->compileAddslashes($v['calc_value'][0]) . $v['calc_value'][1];

596  }

597  } elseif (!($this->databaseConnection->runningADOdbDriver('oci8') && preg_match('/(NOT )?LIKE( BINARY)?/', $v['comparator']) && $functionMapping)) {

598  $output .= trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);

599  }

600  }

601  // Set comparator:

602  if ($v['comparator']) {

603  $isLikeOperator = preg_match('/(NOT )?LIKE( BINARY)?/', $v['comparator']);

604  switch (TRUE) {

605  case $this->databaseConnection->runningADOdbDriver('oci8') && $isLikeOperator && $functionMapping:

606  // Oracle cannot handle LIKE on CLOB fields - sigh

607  if (isset($v['value']['operator'])) {

608  $values = array();

609  foreach ($v['value']['args'] as $fieldDef) {

610  $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];

611  }

612  $compareValue = ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';

613  } else {

614  $compareValue = $v['value'][1] . $this->compileAddslashes(trim($v['value'][0], '%')) . $v['value'][1];

615  }

616  if (GeneralUtility::isFirstPartOfStr($v['comparator'], 'NOT')) {

617  $output .= 'NOT ';

618  }

619  // To be on the safe side

620  $isLob = TRUE;

621  if ($v['table']) {

622  // Table and field names are quoted:

623  $tableName = substr($v['table'], 1, strlen($v['table']) - 2);

624  $fieldName = substr($v['field'], 1, strlen($v['field']) - 2);

625  $fieldType = $this->databaseConnection->sql_field_metatype($tableName, $fieldName);

626  $isLob = $fieldType === 'B' || $fieldType === 'XL';

627  }

628  if (strtoupper(substr($v['comparator'], -6)) === 'BINARY') {

629  if ($isLob) {

630  $output .= '(dbms_lob.instr(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . ', ' . $compareValue . ',1,1) > 0)';

631  } else {

632  $output .= '(instr(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . ', ' . $compareValue . ',1,1) > 0)';

633  }

634  } else {

635  if ($isLob) {

636  $output .= '(dbms_lob.instr(LOWER(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . '), ' . GeneralUtility::strtolower($compareValue) . ',1,1) > 0)';

637  } else {

638  $output .= '(instr(LOWER(' . trim((($v['table'] ? $v['table'] . '.' : '') . $v['field'])) . '), ' . GeneralUtility::strtolower($compareValue) . ',1,1) > 0)';

639  }

640  }

641  break;

642  default:

643  if ($isLikeOperator && $functionMapping) {

644  if ($this->databaseConnection->runningADOdbDriver('postgres') || $this->databaseConnection->runningADOdbDriver('postgres64') || $this->databaseConnection->runningADOdbDriver('postgres7') || $this->databaseConnection->runningADOdbDriver('postgres8')) {

645  // Remap (NOT)? LIKE to (NOT)? ILIKE

646  // and (NOT)? LIKE BINARY to (NOT)? LIKE

647  switch ($v['comparator']) {

648  case 'LIKE':

649  $v['comparator'] = 'ILIKE';

650  break;

651  case 'NOT LIKE':

652  $v['comparator'] = 'NOT ILIKE';

653  break;

654  default:

655  $v['comparator'] = str_replace(' BINARY', '', $v['comparator']);

656  }

657  } else {

658  // No more BINARY operator

659  $v['comparator'] = str_replace(' BINARY', '', $v['comparator']);

660  }

661  }

662  $output .= ' ' . $v['comparator'];

663  // Detecting value type; list or plain:

664  $comparator = strtoupper(str_replace(array(' ', TAB, CR, LF), '', $v['comparator']));

665  if (GeneralUtility::inList('NOTIN,IN', $comparator)) {

666  if (isset($v['subquery'])) {

667  $output .= ' (' . $this->compileSELECT($v['subquery']) . ')';

668  } else {

669  $valueBuffer = array();

670  foreach ($v['value'] as $realValue) {

671  $valueBuffer[] = $realValue[1] . $this->compileAddslashes($realValue[0]) . $realValue[1];

672  }

673 

674  $dbmsSpecifics = $this->databaseConnection->getSpecifics();

675  if ($dbmsSpecifics === NULL) {

676  $output .= ' (' . trim(implode(',', $valueBuffer)) . ')';

677  } else {

678  $chunkedList = $dbmsSpecifics->splitMaxExpressions($valueBuffer);

679  $chunkCount = count($chunkedList);

680 

681  if ($chunkCount === 1) {

682  $output .= ' (' . trim(implode(',', $valueBuffer)) . ')';

683  } else {

684  $listExpressions = array();

685  $field = trim(($v['table'] ? $v['table'] . '.' : '') . $v['field']);

686 

687  switch ($comparator) {

688  case 'IN':

689  $operator = 'OR';

690  break;

691  case 'NOTIN':

692  $operator = 'AND';

693  break;

694  default:

695  $operator = '';

696  }

697 

698  for ($i = 0; $i < $chunkCount; ++$i) {

699  $listPart = trim(implode(',', $chunkedList[$i]));

700  $listExpressions[] = ' (' . $listPart . ')';

701  }

702 

703  $implodeString = ' ' . $operator . ' ' . $field . ' ' . $v['comparator'];

704 

705  // add opening brace before field

706  $lastFieldPos = strrpos($output, $field);

707  $output = substr_replace($output, '(', $lastFieldPos, 0);

708  $output .= implode($implodeString, $listExpressions) . ')';

709  }

710  }

711  }

712  } elseif (GeneralUtility::inList('BETWEEN,NOT BETWEEN', $v['comparator'])) {

713  $lbound = $v['values'][0];

714  $ubound = $v['values'][1];

715  $output .= ' ' . $lbound[1] . $this->compileAddslashes($lbound[0]) . $lbound[1];

716  $output .= ' AND ';

717  $output .= $ubound[1] . $this->compileAddslashes($ubound[0]) . $ubound[1];

718  } elseif (isset($v['value']['operator'])) {

719  $values = array();

720  foreach ($v['value']['args'] as $fieldDef) {

721  $values[] = ($fieldDef['table'] ? $fieldDef['table'] . '.' : '') . $fieldDef['field'];

722  }

723  $output .= ' ' . $v['value']['operator'] . '(' . implode(',', $values) . ')';

724  } else {

725  $output .= ' ' . $v['value'][1] . $this->compileAddslashes($v['value'][0]) . $v['value'][1];

726  }

727  }

728  }

729  }

730  }

731  }

732  break;

733  }

734  return $output;

735  }

736 

743  public function debug_testSQL($SQLquery) {

744  // Getting result array:

745  $parseResult = $this->parseSQL($SQLquery);

746  // If result array was returned, proceed. Otherwise show error and exit.

747  if (is_array($parseResult)) {

748  // Re-compile query:

749  $newQuery = $this->compileSQL($parseResult);

750  // TEST the new query:

751  $testResult = $this->debug_parseSQLpartCompare($SQLquery, $newQuery);

752  // Return new query if OK, otherwise show error and exit:

753  if (!is_array($testResult)) {

754  return $newQuery;

755  } else {

756  debug(array('ERROR MESSAGE' => 'Input query did not match the parsed and recompiled query exactly (not observing whitespace)', 'TEST result' => $testResult), 'SQL parsing failed:');

757  die;

758  }

759  } else {

760  debug(array('query' => $SQLquery, 'ERROR MESSAGE' => $parseResult), 'SQL parsing failed:');

761  die;

762  }

763  }

764 }