/** * 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); }