/**
* @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';
$db->query($this->tempIdTableCreator->getSqlToCreate($tmpnew), __METHOD__);
$db->query($this->tempIdTableCreator->getSqlToCreate($tmpres), __METHOD__);
// 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;
$db->query('DROP TEMPORARY TABLE smw_new', __METHOD__);
$db->query('DROP TEMPORARY TABLE smw_res', __METHOD__);
}
/**
* Get SQL code suitable to create a temporary table of the given name, used to store ids.
* MySQL can do that simply by creating new temporary tables. PostgreSQL first checks if such
* a table exists, so the code is ready to reuse existing tables if the code was modified to
* keep them after query answering. Also, PostgreSQL tables will use a RULE to achieve built-in
* duplicate elimination. The latter is done using INSERT IGNORE in MySQL.
*
* @param string $tableName
*
* @return string
*/
private function getCreateTempIDTableSQL($tableName)
{
return $this->tempIdTableCreator->getSqlToCreate($tableName);
}