public function testcreateWithAutoCommit()
{
$this->connection->expects($this->once())->method('queryWithAutoCommit');
$instance = new TemporaryTableBuilder($this->connection);
$instance->WithAutoCommit(true);
$instance->create('Foo');
}
/**
* @note we use two helper tables. One holds the results of each new iteration, one holds the
* results of the previous iteration. One could of course do with only the above result table,
* but then every iteration would use all elements of this table, while only the new ones
* obtained in the previous step are relevant. So this is a performance measure.
*/
private function buildTempTableFor($tablename, $values, $smwtable, $depth)
{
$db = $this->connection;
$tmpnew = 'smw_new';
$tmpres = 'smw_res';
$this->temporaryTableBuilder->create($tmpnew);
$this->temporaryTableBuilder->create($tmpres);
// Adding multiple values for the same column in sqlite is not supported
foreach (explode(',', $values) as $value) {
$db->query("INSERT " . "IGNORE" . " INTO {$tablename} (id) VALUES {$value}", __METHOD__);
$db->query("INSERT " . "IGNORE" . " INTO {$tmpnew} (id) VALUES {$value}", __METHOD__);
}
for ($i = 0; $i < $depth; $i++) {
$db->query("INSERT " . 'IGNORE ' . "INTO {$tmpres} (id) SELECT s_id" . '@INT' . " FROM {$smwtable}, {$tmpnew} WHERE o_id=id", __METHOD__);
if ($db->affectedRows() == 0) {
// no change, exit loop
break;
}
$db->query("INSERT " . 'IGNORE ' . "INTO {$tablename} (id) SELECT {$tmpres}.id FROM {$tmpres}", __METHOD__);
if ($db->affectedRows() == 0) {
// no change, exit loop
break;
}
// empty "new" table
$db->query('TRUNCATE TABLE ' . $tmpnew, __METHOD__);
$tmpname = $tmpnew;
$tmpnew = $tmpres;
$tmpres = $tmpname;
}
$this->hierarchyCache[$values] = $tablename;
$this->temporaryTableBuilder->drop($tmpnew);
$this->temporaryTableBuilder->drop($tmpres);
}
private function doResolveBySegment(QuerySegment &$query)
{
$db = $this->connection;
switch ($query->type) {
case QuerySegment::Q_TABLE:
// Normal query with conjunctive subcondition.
foreach ($query->components as $qid => $joinField) {
$subQuery = $this->querySegmentList[$qid];
$this->doResolveBySegment($subQuery);
if ($subQuery->joinTable !== '') {
// Join with jointable.joinfield
$query->from .= ' INNER JOIN ' . $db->tableName($subQuery->joinTable) . " AS {$subQuery->alias} ON {$joinField}=" . $subQuery->joinfield;
} elseif ($subQuery->joinfield !== '') {
// Require joinfield as "value" via WHERE.
$condition = '';
foreach ($subQuery->joinfield as $value) {
$condition .= ($condition ? ' OR ' : '') . "{$joinField}=" . $db->addQuotes($value);
}
if (count($subQuery->joinfield) > 1) {
$condition = "({$condition})";
}
$query->where .= ($query->where === '' ? '' : ' AND ') . $condition;
} else {
// interpret empty joinfields as impossible condition (empty result)
$query->joinfield = '';
// make whole query false
$query->joinTable = '';
$query->where = '';
$query->from = '';
break;
}
if ($subQuery->where !== '') {
$query->where .= ($query->where === '' ? '' : ' AND ') . '(' . $subQuery->where . ')';
}
$query->from .= $subQuery->from;
}
$query->components = array();
break;
case QuerySegment::Q_CONJUNCTION:
reset($query->components);
$key = false;
// Pick one subquery as anchor point ...
foreach ($query->components as $qkey => $qid) {
$key = $qkey;
if ($this->querySegmentList[$qkey]->joinTable !== '') {
break;
}
}
$result = $this->querySegmentList[$key];
unset($query->components[$key]);
// Execute it first (may change jointable and joinfield, e.g. when making temporary tables)
$this->doResolveBySegment($result);
// ... and append to this query the remaining queries.
foreach ($query->components as $qid => $joinfield) {
$result->components[$qid] = $result->joinfield;
}
// Second execute, now incorporating remaining conditions.
$this->doResolveBySegment($result);
$query = $result;
break;
case QuerySegment::Q_DISJUNCTION:
if ($this->queryMode !== Query::MODE_NONE) {
$this->temporaryTableBuilder->create($db->tableName($query->alias));
}
$this->executedQueries[$query->alias] = array();
foreach ($query->components as $qid => $joinField) {
$subQuery = $this->querySegmentList[$qid];
$this->doResolveBySegment($subQuery);
$sql = '';
if ($subQuery->joinTable !== '') {
$sql = 'INSERT ' . 'IGNORE ' . 'INTO ' . $db->tableName($query->alias) . " SELECT DISTINCT {$subQuery->joinfield} FROM " . $db->tableName($subQuery->joinTable) . " AS {$subQuery->alias} {$subQuery->from}" . ($subQuery->where ? " WHERE {$subQuery->where}" : '');
} elseif ($subQuery->joinfield !== '') {
// NOTE: this works only for single "unconditional" values without further
// WHERE or FROM. The execution must take care of not creating any others.
$values = '';
// This produces an error on postgres with
// pg_query(): Query failed: ERROR: duplicate key value violates
// unique constraint "sunittest_t3_pkey" DETAIL: Key (id)=(274) already exists.
foreach ($subQuery->joinfield as $value) {
$values .= ($values ? ',' : '') . '(' . $db->addQuotes($value) . ')';
}
$sql = 'INSERT ' . 'IGNORE ' . 'INTO ' . $db->tableName($query->alias) . " (id) VALUES {$values}";
}
// else: // interpret empty joinfields as impossible condition (empty result), ignore
if ($sql) {
$this->executedQueries[$query->alias][] = $sql;
if ($this->queryMode !== Query::MODE_NONE) {
$db->query($sql, __METHOD__);
}
}
}
$query->type = QuerySegment::Q_TABLE;
$query->where = '';
$query->components = array();
$query->joinTable = $query->alias;
$query->joinfield = "{$query->alias}.id";
$query->sortfields = array();
// Make sure we got no sortfields.
// TODO: currently this eliminates sortkeys, possibly keep them (needs different temp table format though, maybe not such a good thing to do)
break;
case QuerySegment::Q_PROP_HIERARCHY:
case QuerySegment::Q_CLASS_HIERARCHY:
// make a saturated hierarchy
$this->resolveHierarchyForSegment($query);
break;
case QuerySegment::Q_VALUE:
break;
// nothing to do
}
}
