public function testExtractSubsets_InsertSubqueryInWhere()
{
$set = QuerySplitter::extractSubsets("INSERT INTO relatie_active SELECT * FROM relatie WHERE id IN (SELECT relatie_id FROM relatie_groep) AND status = 1");
$this->assertEquals(array("INSERT INTO relatie_active #sub1", "SELECT * FROM relatie WHERE id IN (#sub2) AND status = 1", "SELECT relatie_id FROM relatie_groep"), array_map(array(__CLASS__, 'cleanQuery'), $set));
}
/**
* Get a subquery (from base statement).
*
* @param int $subset Number of subquery (start with 1)
* @return Query
*/
public function getSubquery($subset = 1)
{
if (!isset($this->subqueries)) {
$statements = QuerySplitter::extractSubsets($this->statement);
$this->baseParts = QuerySplitter::split($statements[0]);
unset($statements[0]);
foreach ($statements as $i => $statement) {
$this->subqueries[$i] = new static($statement, $this);
}
}
if (!isset($this->subqueries[$subset])) {
throw new \Exception("Unable to get subquery #{$subset}: Query only has " . count($this->subqueries) . (count($this->subqueries) == 1 ? " subquery." : " subqueries."));
}
return $this->subqueries[$subset];
}
/**
* Extract subqueries from sql query (on for SELECT queries) and replace them with #subX in the main query.
* Returns array(main query, subquery1, [subquery2, ...])
*
* @param string $sql
* @param array $sets Do not use!
* @return array
*
* @todo Extract subsets should only go 1 level deep
*/
public static function extractSubsets($sql, &$sets = null)
{
$ret_offset = isset($sets);
$sets = (array) $sets;
// There are certainly no subqueries
if (stripos($sql, 'SELECT', 6) === false) {
$offset = array_push($sets, $sql) - 1;
return $ret_offset ? $offset : $sets;
}
// Extract any subqueries
$offset = array_push($sets, null) - 1;
if (self::getQueryType($sql) === 'INSERT' || self::getQueryType($sql) === 'REPLACE') {
$parts = self::split($sql);
if (isset($parts['query'])) {
self::extractSubsets($parts['query'], $sets);
$parts['query'] = '#sub' . ($offset + 1);
$sql = self::join($parts);
}
}
if (preg_match('/\\(\\s*SELECT\\b/si', $sql)) {
do {
$matches = null;
preg_match('/(?:`[^`]*+`|"(?:[^"\\\\]++|\\\\.)*+"|\'(?:[^\'\\\\]++|\\\\.)*+\'|\\((\\s*SELECT\\b.*\\).*)|\\w++|[^`"\'\\w])*$/si', $sql, $matches, PREG_OFFSET_CAPTURE);
if (isset($matches[1])) {
$fn = function ($match) use(&$sets) {
return '#sub' . QuerySplitter::extractSubsets($match[0], $sets);
};
$sql = substr($sql, 0, $matches[1][1]) . preg_replace_callback('/(?:`[^`]*+`|"(?:[^"\\\\]++|\\\\.)*+"|\'(?:[^\'\\\\]++|\\\\.)*+\'|([^`"\'()]+)|\\((?R)\\))*/si', $fn, substr($sql, $matches[1][1]), 1);
}
} while (isset($matches[1]));
}
$sets[$offset] = $sql;
return $ret_offset ? $offset : $sets;
}