/**
* Query marker
*
* @return string
*/
public function queryMaker()
{
$output = '';
if (is_array($GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['t3lib_fullsearch'])) {
$this->hookArray = $GLOBALS['TYPO3_CONF_VARS']['EXTCONF']['t3lib_fullsearch'];
}
$msg = $this->procesStoreControl();
if (!$this->backendUserAuthentication->userTS['mod.']['dbint.']['disableStoreControl']) {
$output .= '<h2>Load/Save Query</h2>';
$output .= '<div>' . $this->makeStoreControl() . '</div>';
$output .= $msg;
}
// Query Maker:
$qGen = GeneralUtility::makeInstance(QueryGenerator::class);
$qGen->init('queryConfig', $GLOBALS['SOBE']->MOD_SETTINGS['queryTable']);
if ($this->formName) {
$qGen->setFormName($this->formName);
}
$tmpCode = $qGen->makeSelectorTable($GLOBALS['SOBE']->MOD_SETTINGS);
$output .= '<div id="query"></div>' . '<h2>Make query</h2><div>' . $tmpCode . '</div>';
$mQ = $GLOBALS['SOBE']->MOD_SETTINGS['search_query_makeQuery'];
// Make form elements:
if ($qGen->table && is_array($GLOBALS['TCA'][$qGen->table])) {
if ($mQ) {
// Show query
$qGen->enablePrefix = 1;
$qString = $qGen->getQuery($qGen->queryConfig);
switch ($mQ) {
case 'count':
$qExplain = $this->databaseConnection->SELECTquery('count(*)', $qGen->table, $qString . BackendUtility::deleteClause($qGen->table));
break;
default:
$qExplain = $qGen->getSelectQuery($qString);
if ($mQ == 'explain') {
$qExplain = 'EXPLAIN ' . $qExplain;
}
}
if (!$this->backendUserAuthentication->userTS['mod.']['dbint.']['disableShowSQLQuery']) {
$output .= '<h2>SQL query</h2><div>' . $this->tableWrap(htmlspecialchars($qExplain)) . '</div>';
}
$res = @$this->databaseConnection->sql_query($qExplain);
if ($this->databaseConnection->sql_error()) {
$out = '<p><strong>Error: <span class="text-danger">' . $this->databaseConnection->sql_error() . '</span></strong></p>';
$output .= '<h2>SQL error</h2><div>' . $out . '</div>';
} else {
$cPR = $this->getQueryResultCode($mQ, $res, $qGen->table);
$this->databaseConnection->sql_free_result($res);
$output .= '<h2>' . $cPR['header'] . '</h2><div>' . $cPR['content'] . '</div>';
}
}
}
return '<div class="query-builder">' . $output . '</div>';
}
/**
* @throws Exception
* @return array of options
*/
protected function getDataFromSqlServer()
{
$query = $this->dbObj->SELECTquery($this->selectPart, $this->fromPart, $this->wherePart, $this->groupByPart, $this->orderByPart, $this->limitPart);
// this method only combines the parts
$dataSource = Tx_PtExtlist_Domain_DataBackend_DataBackendFactory::getInstanceByListIdentifier($this->filterConfig->getListIdentifier())->getDataSource();
if (!method_exists($dataSource, 'executeQuery')) {
throw new Exception('The defined dataSource has no method executeQuery and is therefore not usable with this dataProvider!', 1315216209);
}
$data = $dataSource->executeQuery($query)->fetchAll();
if (TYPO3_DLOG) {
\TYPO3\CMS\Core\Utility\GeneralUtility::devLog('MYSQL QUERY : ' . $this->filterConfig->getListIdentifier() . ' -> Filter::ExplicitSQLQuery', 'pt_extlist', 1, array('executionTime' => $dataSource->getLastQueryExecutionTime(), 'query' => $query));
}
return $data;
}
/**
* Precompiles a SELECT prepared SQL statement.
*
* @param array $components
* @return array Precompiled SQL statement
*/
protected function precompileSELECTquery(array $components)
{
$parameterWrap = '__' . dechex(time()) . '__';
foreach ($components['parameters'] as $key => $params) {
if ($key === '?') {
foreach ($params as $index => $param) {
$components['parameters'][$key][$index][0] = $parameterWrap . $param[0] . $parameterWrap;
}
} else {
$components['parameters'][$key][0] = $parameterWrap . $params[0] . $parameterWrap;
}
}
$select_fields = $this->SQLparser->compileFieldList($components['SELECT']);
$from_table = $this->SQLparser->compileFromTables($components['FROM']);
$where_clause = $this->SQLparser->compileWhereClause($components['WHERE']);
$groupBy = $this->SQLparser->compileFieldList($components['GROUPBY']);
$orderBy = $this->SQLparser->compileFieldList($components['ORDERBY']);
$limit = $components['LIMIT'];
$precompiledParts = array();
$this->lastHandlerKey = $this->handler_getFromTableList($components['ORIG_tableName']);
$hType = (string) $this->handlerCfg[$this->lastHandlerKey]['type'];
$precompiledParts['handler'] = $hType;
$precompiledParts['ORIG_tableName'] = $components['ORIG_tableName'];
switch ($hType) {
case 'native':
$query = parent::SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
$precompiledParts['queryParts'] = explode($parameterWrap, $query);
break;
case 'adodb':
$query = parent::SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy);
$precompiledParts['queryParts'] = explode($parameterWrap, $query);
$precompiledParts['LIMIT'] = $limit;
break;
case 'userdefined':
$precompiledParts['queryParts'] = array('SELECT' => $select_fields, 'FROM' => $from_table, 'WHERE' => $where_clause, 'GROUPBY' => $groupBy, 'ORDERBY' => $orderBy, 'LIMIT' => $limit);
break;
}
return $precompiledParts;
}
/**
* Returns the last built SQL SELECT query with tabs removed.
*
* This function tries to retrieve the last built SQL SELECT query from the database object property $this->debug_lastBuiltQuery (works only since T3 3.8.0 with $GLOBALS['TYPO3_DB']->store_lastBuiltQuery = true). If this does not succeed, a fallback method is used (for former versions of class.\TYPO3\CMS\Core\Database\DatabaseConnection.php [TYPO3 3.6.0-3.8.0beta1] or $GLOBALS['TYPO3_DB']->store_lastBuiltQuery _not_ set to true) to retrieve the query string from \TYPO3\CMS\Core\Database\DatabaseConnection::SELECTquery() - as this is an overhead (\TYPO3\CMS\Core\Database\DatabaseConnection::SELECTquery() is called a second time after the call from \TYPO3\CMS\Core\Database\DatabaseConnection::exec_SELECT_query) IMO this should not be a permanent solution.
*
* @param DatabaseConnection TYPO3 database object (instance of \TYPO3\CMS\Core\Database\DatabaseConnection) used for last executed SQL query
* @param string select field name(s) passed to last executed SQL query (see comment of \TYPO3\CMS\Core\Database\DatabaseConnection::exec_SELECTquery())
* @param string from clause/table name(s) passed to last executed SQL query (see comment of \TYPO3\CMS\Core\Database\DatabaseConnection::exec_SELECTquery())
* @param string where clause passed to last executed SQL query (see comment of \TYPO3\CMS\Core\Database\DatabaseConnection::exec_SELECTquery())
* @param string (optional) order by clause passed to last executed SQL query (see comment of \TYPO3\CMS\Core\Database\DatabaseConnection::exec_SELECTquery())
* @param string (optional) group by clause passed to last executed SQL query (see comment of \TYPO3\CMS\Core\Database\DatabaseConnection::exec_SELECTquery())
* @param string (optional) limit clause passed to last executed SQL query (see comment of \TYPO3\CMS\Core\Database\DatabaseConnection::exec_SELECTquery())
* @return string last built SQL query with tabs removed
* @see class.\TYPO3\CMS\Core\Database\DatabaseConnection.php, \TYPO3\CMS\Core\Database\DatabaseConnection::exec_SELECTquery()
* @author Rainer Kuhn
*/
public static function returnLastBuiltSelectQuery(DatabaseConnection $dbObject, $select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '')
{
// try to get query from debug_lastBuiltQuery (works only for T3 3.8.0 with $GLOBALS['TYPO3_DB']->store_lastBuiltQuery = true)
$query = $dbObject->debug_lastBuiltQuery;
// fallback for former versions of class.\TYPO3\CMS\Core\Database\DatabaseConnection.php (TYPO3 3.6.0-3.8.0beta1) or $GLOBALS['TYPO3_DB']->store_lastBuiltQuery _not_ set to true
if (strlen($query) < 1) {
$query = $dbObject->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);
}
// remove tabs and return query string
return str_replace(chr(9), '', $query);
}
/**
* @test
*
* @return void
*/
public function selectQueryCreateValidQueryWithLimitClause()
{
$queryGenerated = $this->subject->SELECTquery($this->testField, $this->testTable, 'id=1', '', '', '1,2');
$queryExpected = "SELECT {$this->testField} FROM {$this->testTable} WHERE id=1 LIMIT 1,2";
$this->assertSame($queryExpected, $queryGenerated);
}
/**
* Fetches the UIDs of all maximal versions for all extensions.
* This is done by doing a subselect in the WHERE clause to get all
* max versions and then the UID of that record in the outer select.
*
* @param int $repositoryUid
* @return array
*/
protected function fetchMaximalVersionsForAllExtensions($repositoryUid)
{
$extensionUids = $this->databaseConnection->exec_SELECTgetRows('a.uid AS uid', self::TABLE_NAME . ' a', 'integer_version=(' . $this->databaseConnection->SELECTquery('MAX(integer_version)', self::TABLE_NAME . ' b', 'b.repository=' . (int) $repositoryUid . ' AND a.extension_key=b.extension_key') . ') AND repository=' . (int) $repositoryUid, '', '', '', 'uid');
return array_keys($extensionUids);
}
