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