/**
* Creates a temporary database table, semantic_drilldown_filter_values,
* that holds every value held by any page in the wiki that matches
* the property associated with this filter. This table is useful
* both for speeding up later queries (at least, that's the hope)
* and for getting the set of 'None' values.
*/
function createTempTable()
{
$dbr = wfGetDB(DB_SLAVE, 'smw');
$smw_ids = $dbr->tableName(SDUtils::getIDsTableName());
$valuesTable = $dbr->tableName($this->getTableName());
$value_field = $this->getValueField();
$property_field = 'p_id';
$query_property = $this->escaped_property;
$sql = <<<END
\tCREATE TEMPORARY TABLE semantic_drilldown_filter_values
\tAS SELECT s_id AS id, {$value_field} AS value
\tFROM {$valuesTable}
\tJOIN {$smw_ids} p_ids ON {$valuesTable}.p_id = p_ids.smw_id
END;
if ($this->property_type === 'page') {
$sql .= "\tJOIN {$smw_ids} o_ids ON {$valuesTable}.o_id = o_ids.smw_id\n";
}
$sql .= "\tWHERE p_ids.smw_title = '{$query_property}'";
$dbr->query($sql);
}
/**
* Gets an array of all values that the property belonging to this
* filter has, for pages in the passed-in category.
*/
function getAllOrValues($category)
{
$possible_values = array();
$property_value = $this->filter->escaped_property;
$dbr = wfGetDB(DB_SLAVE, 'smw');
$property_table_name = $dbr->tableName($this->filter->getTableName());
if ($this->filter->property_type != 'date') {
$value_field = $this->filter->getValueField();
} else {
// Is this necessary?
$date_field = $this->filter->getDateField();
if ($this->filter->getTimePeriod() == 'month') {
$value_field = "YEAR({$date_field}), MONTH({$date_field})";
} elseif ($this->filter->getTimePeriod() == 'day') {
$value_field = "YEAR({$date_field}), MONTH({$date_field}), DAYOFMONTH({$date_field})";
} elseif ($this->filter->getTimePeriod() == 'year') {
$value_field = "YEAR({$date_field})";
} else {
// if ( $this->filter->getTimePeriod() == 'year range' ) {
$value_field = "YEAR({$date_field})";
}
}
$smwIDs = $dbr->tableName(SDUtils::getIDsTableName());
$smwCategoryInstances = $dbr->tableName(SDUtils::getCategoryInstancesTableName());
$cat_ns = NS_CATEGORY;
$sql = "\tSELECT {$value_field}\n\tFROM {$property_table_name} p\n\tJOIN {$smwIDs} p_ids ON p.p_id = p_ids.smw_id\n";
if ($this->filter->property_type === 'page') {
$sql .= " JOIN {$smwIDs} o_ids ON p.o_id = o_ids.smw_id\n";
}
$sql .= "\tJOIN {$smwCategoryInstances} insts ON p.s_id = insts.s_id\n\tJOIN {$smwIDs} cat_ids ON insts.o_id = cat_ids.smw_id\n\tWHERE p_ids.smw_title = '{$property_value}'\n\tAND cat_ids.smw_namespace = {$cat_ns}\n\tAND cat_ids.smw_title = {$dbr->addQuotes($category)}\n\tGROUP BY {$value_field}\n\tORDER BY {$value_field}";
$res = $dbr->query($sql);
while ($row = $dbr->fetchRow($res)) {
if ($this->filter->property_type == 'date' && $this->filter->getTimePeriod() == 'month') {
$value_string = SDUtils::monthToString($row[1]) . " " . $row[0];
} else {
// why is trim() necessary here???
$value_string = str_replace('_', ' ', trim($row[0]));
}
$possible_values[] = $value_string;
}
$dbr->freeResult($res);
return $possible_values;
}
/**
* Returns everything from the FROM clause onward for a SQL statement
* to get all pages that match a certain set of criteria for
* category, subcategory and filters.
*/
function getSQLFromClause($category, $subcategory, $subcategories, $applied_filters)
{
global $smwgDefaultStore;
$dbr = wfGetDB(DB_SLAVE);
$smwIDs = $dbr->tableName(SDUtils::getIDsTableName());
$smwCategoryInstances = $dbr->tableName(SDUtils::getCategoryInstancesTableName());
$cat_ns = NS_CATEGORY;
$prop_ns = SMW_NS_PROPERTY;
$sql = "FROM {$smwIDs} ids\n\tJOIN {$smwCategoryInstances} insts\n\tON ids.smw_id = insts.s_id\n\tAND ids.smw_namespace != {$cat_ns} ";
foreach ($applied_filters as $i => $af) {
// if any of this filter's values is 'none',
// include another table to get this information
$includes_none = false;
foreach ($af->values as $fv) {
if ($fv->text === '_none' || $fv->text === ' none') {
$includes_none = true;
break;
}
}
if ($includes_none) {
$property_table_name = $dbr->tableName($af->filter->getTableName());
if ($af->filter->property_type === 'page') {
$property_table_nickname = "nr{$i}";
$property_field = 'p_id';
} else {
$property_table_nickname = "na{$i}";
$property_field = 'p_id';
}
$property_value = str_replace(' ', '_', $af->filter->property);
// The sub-query that returns an SMW ID contains
// a "SELECT MAX", even though by definition it
// doesn't need to, because of occasional bugs
// in SMW where the same page gets two
// different SMW IDs.
$sql .= "LEFT OUTER JOIN\n\t(SELECT s_id\n\tFROM {$property_table_name}\n\tWHERE {$property_field} = (SELECT MAX(smw_id) FROM {$smwIDs} WHERE smw_title = {$dbr->addQuotes($property_value)} AND smw_namespace = {$prop_ns})) {$property_table_nickname}\n\tON ids.smw_id = {$property_table_nickname}.s_id ";
}
}
foreach ($applied_filters as $i => $af) {
$sql .= "\n\t";
$property_table_name = $dbr->tableName($af->filter->getTableName());
if ($af->filter->property_type === 'page') {
if ($includes_none) {
$sql .= "LEFT OUTER ";
}
$sql .= "JOIN {$property_table_name} r{$i} ON ids.smw_id = r{$i}.s_id\n\t";
if ($includes_none) {
$sql .= "LEFT OUTER ";
}
$sql .= "JOIN {$smwIDs} o_ids{$i} ON r{$i}.o_id = o_ids{$i}.smw_id ";
} else {
$sql .= "JOIN {$property_table_name} a{$i} ON ids.smw_id = a{$i}.s_id ";
}
}
if ($subcategory) {
$actual_cat = str_replace(' ', '_', $subcategory);
} else {
$actual_cat = str_replace(' ', '_', $category);
}
$sql .= "WHERE insts.o_id IN\n\t(SELECT smw_id FROM {$smwIDs} cat_ids\n\tWHERE smw_namespace = {$cat_ns} AND (smw_title = {$dbr->addQuotes($actual_cat)}";
foreach ($subcategories as $i => $subcat) {
$sql .= " OR smw_title = {$dbr->addQuotes($subcat)}";
}
$sql .= ")) ";
foreach ($applied_filters as $i => $af) {
$property_value = $af->filter->escaped_property;
$value_field = $af->filter->getValueField();
if ($af->filter->property_type === 'page') {
$property_field = "r{$i}.p_id";
$sql .= "\n\tAND ({$property_field} = (SELECT MAX(smw_id) FROM {$smwIDs} WHERE smw_title = '{$property_value}' AND smw_namespace = {$prop_ns})";
if ($includes_none) {
$sql .= " OR {$property_field} IS NULL";
}
$sql .= ")\n\tAND ";
$value_field = "o_ids{$i}.smw_title";
} else {
$property_field = "a{$i}.p_id";
$sql .= "\n\tAND {$property_field} = (SELECT MAX(smw_id) FROM {$smwIDs} WHERE smw_title = '{$property_value}' AND smw_namespace = {$prop_ns}) AND ";
if ($af->filter->property_type === 'date') {
$value_field = "SUBSTRING(a{$i}.{$value_field}, 3)";
} elseif (strncmp($value_field, '(IF(o_blob IS NULL', 18) === 0) {
$value_field = str_replace('o_', "a{$i}.o_", $value_field);
} else {
$value_field = "a{$i}.{$value_field}";
}
}
$sql .= $af->checkSQL($value_field);
}
return $sql;
}
