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