/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query)
{
if (Subsite::$disable_subsite_filter) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly... (but it was WAYYYYYYYYY worse)
//@TODO I don't think excluding if SiteTree_ImageTracking is a good idea however because of the SS 3.0 api and ManyManyList::removeAll() changing the from table after this function is called there isn't much of a choice
$from = $query->getFrom();
$where = $query->getWhere();
if (!isset($from['SiteTree_ImageTracking']) && !($where && preg_match('/\\.(\'|"|`|)ID(\'|"|`|)/', $where[0]))) {
$subsiteID = (int) Subsite::currentSubsiteID();
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
$where = "\"{$tableName}\".\"SubsiteID\" IN (0, {$subsiteID})";
$query->addWhere($where);
break;
}
$sect = array_values($query->getSelect());
$isCounting = strpos($sect[0], 'COUNT') !== false;
// Ordering when deleting or counting doesn't apply
if (!$query->getDelete() && !$isCounting) {
$query->addOrderBy("\"SubsiteID\"");
}
}
}
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
// Actives locales defined on a SiteConfig are there as a global setting
if ($this->owner instanceof SiteConfig) {
return;
}
// In admin, show everthing anyway
if ($this->isAdminBackend()) {
return;
}
// Find in set is only compatible with MySql
$c = DB::getConn();
if (!$c instanceof MySQLDatabase) {
return;
}
$locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale();
$from = $query->getFrom();
$where = $query->getWhere();
$column = 'ActiveLocales';
$table = null;
// Check on which table is the ActiveLocales field
foreach ($from as $fromTable => $conditions) {
if ($table === null) {
$table = $fromTable;
}
$db = DataObject::custom_database_fields($fromTable);
if ($db && isset($db[$column])) {
$table = $fromTable;
break;
}
}
$identifier = "\"{$table}\".\"{$column}\"";
$where[] = "{$identifier} IS NULL OR FIND_IN_SET ('{$locale}', {$identifier}) > 0";
$query->setWhere($where);
}
/**
* Update any requests to limit the results to the current site
*/
function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
if (Subsite::$disable_subsite_filter) {
return;
}
if ($dataQuery->getQueryParam('Subsite.filter') === false) {
return;
}
// Don't run on delete queries, since they are always tied to
// a specific ID.
if ($query->getDelete()) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
// if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) {
if (!$query->where || !preg_match('/\\.(\'|"|`|)ID(\'|"|`|)( ?)=/', $query->where[0])) {
if (Subsite::$force_subsite) {
$subsiteID = Subsite::$force_subsite;
} else {
/*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
}
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
// The tableName should be SiteTree or SiteTree_Live...
if (strpos($tableName, $this->owner->ClassName) === false) {
break;
}
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
break;
}
}
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
$ctrl = null;
if (Controller::has_curr()) {
$ctrl = Controller::curr();
}
if (Subsite::$disable_subsite_filter) {
return;
}
if ($dataQuery->getQueryParam('Subsite.filter') === false) {
return;
}
if ($ctrl && get_class(Controller::curr()) == 'Security') {
return;
}
// Don't run on delete queries, since they are always tied to
// a specific ID.
if ($query->getDelete()) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
// if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) {
if (!$query->filtersOnID()) {
if (Subsite::$force_subsite) {
$subsiteID = Subsite::$force_subsite;
} else {
$subsiteID = (int) Subsite::currentSubsiteID();
}
$froms = $query->getFrom();
$froms = array_keys($froms);
$tableName = array_shift($froms);
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
}
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
if (Subsite::$disable_subsite_filter) {
return;
}
if ($dataQuery->getQueryParam('Subsite.filter') === false) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
// if(!$query->where || (strpos($query->where[0], ".\"ID\" = ") === false && strpos($query->where[0], ".`ID` = ") === false && strpos($query->where[0], ".ID = ") === false && strpos($query->where[0], "ID = ") !== 0)) {
if ($query->filtersOnID()) {
return;
}
if (Subsite::$force_subsite) {
$subsiteID = Subsite::$force_subsite;
} else {
/*if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
}
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
// The tableName should be custommenu...
if (strpos($tableName, 'CustomMenu') === false) {
break;
}
$query->addWhere("\"{$tableName}\".\"SubsiteID\" IN ({$subsiteID})");
break;
}
}
/**
* Determines if the current query is supposed
* to be exempt from the automatic filtering out
* of temporary records.
*
* @param SQLQuery $query
* @return boolean
*/
protected function wantsTemporary($query)
{
foreach ($query->getWhere() as $whereClause) {
$from = array_values($query->getFrom());
// SQLQuery will automatically add double quotes and single quotes to values, so check against that.
if ($whereClause == "{$from[0]}.\"MultiFormIsTemporary\" = '1'") {
return true;
}
}
return false;
}
/**
* Add a join clause to this query
* @deprecated 3.0 Use innerJoin() or leftJoin() instead.
*/
public function join($join)
{
Deprecation::notice('3.0', 'Use innerJoin() or leftJoin() instead.');
if ($join) {
$this->query->addFrom($join);
// TODO: This needs to be resolved for all databases
if (DB::getConn() instanceof MySQLDatabase) {
$from = $this->query->getFrom();
$this->query->setGroupBy(reset($from) . ".\"ID\"");
}
}
return $this;
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
// Filters are disabled globally
if (self::$disable) {
return;
}
// Filters are disabled for this query
if ($dataQuery->getQueryParam('SoftDeletable.filter') === false) {
return;
}
// Don't run on delete queries, since they are always tied to a specific ID.
if ($query->getDelete()) {
return;
}
// Don't run if querying by ID
if ($query->filtersOnID()) {
return;
}
$froms = $query->getFrom();
$froms = array_keys($froms);
$tableName = array_shift($froms);
$query->addWhere("\"{$tableName}\".\"Deleted\" IS NULL");
}
protected function adjustForVersioned(SQLQuery $query)
{
$ownerClass = $this->baseClass;
$stage = Versioned::current_stage();
if ($stage && $stage != 'Stage') {
foreach ($query->getFrom() as $table => $dummy) {
// Only rewrite table names that are actually part of the subclass tree
// This helps prevent rewriting of other tables that get joined in, in
// particular, many_many tables
if (class_exists($table) && ($table == $ownerClass || is_subclass_of($table, $ownerClass) || is_subclass_of($ownerClass, $table))) {
$query->renameTable($table, $table . '_' . $stage);
}
}
}
}
/**
* Determine the class this query is searching against
*
* @param SQLQuery $query
* @return string
*/
protected function getClass(SQLQuery $query)
{
foreach ($query->getFrom() as $alias => $table) {
return $alias;
}
}
/**
* Augment the the SQLQuery that is created by the DataQuery
* @todo Should this all go into VersionedDataQuery?
*/
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
$baseTable = ClassInfo::baseDataClass($dataQuery->dataClass());
switch ($dataQuery->getQueryParam('Versioned.mode')) {
// Noop
case '':
break;
// Reading a specific data from the archive
// Reading a specific data from the archive
case 'archive':
$date = $dataQuery->getQueryParam('Versioned.date');
foreach ($query->getFrom() as $table => $dummy) {
$query->renameTable($table, $table . '_versions');
$query->replaceText("\"{$table}_versions\".\"ID\"", "\"{$table}_versions\".\"RecordID\"");
$query->replaceText("`{$table}_versions`.`ID`", "`{$table}_versions`.`RecordID`");
// Add all <basetable>_versions columns
foreach (self::$db_for_versions_table as $name => $type) {
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name);
}
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
if ($table != $baseTable) {
$query->addWhere("\"{$table}_versions\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
}
// Link to the version archived on that date
$safeDate = Convert::raw2sql($date);
$query->addWhere("\"{$baseTable}_versions\".\"Version\" IN \n\t\t\t\t\t(SELECT LatestVersion FROM \n\t\t\t\t\t\t(SELECT \n\t\t\t\t\t\t\t\"{$baseTable}_versions\".\"RecordID\", \n\t\t\t\t\t\t\tMAX(\"{$baseTable}_versions\".\"Version\") AS LatestVersion\n\t\t\t\t\t\t\tFROM \"{$baseTable}_versions\"\n\t\t\t\t\t\t\tWHERE \"{$baseTable}_versions\".\"LastEdited\" <= '{$safeDate}'\n\t\t\t\t\t\t\tGROUP BY \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t\t\t) AS \"{$baseTable}_versions_latest\"\n\t\t\t\t\t\tWHERE \"{$baseTable}_versions_latest\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t\t)");
break;
// Reading a specific stage (Stage or Live)
// Reading a specific stage (Stage or Live)
case 'stage':
$stage = $dataQuery->getQueryParam('Versioned.stage');
if ($stage && $stage != $this->defaultStage) {
foreach ($query->getFrom() as $table => $dummy) {
// Only rewrite table names that are actually part of the subclass tree
// This helps prevent rewriting of other tables that get joined in, in
// particular, many_many tables
if (class_exists($table) && ($table == $this->owner->class || is_subclass_of($table, $this->owner->class) || is_subclass_of($this->owner->class, $table))) {
$query->renameTable($table, $table . '_' . $stage);
}
}
}
break;
// Reading a specific stage, but only return items that aren't in any other stage
// Reading a specific stage, but only return items that aren't in any other stage
case 'stage_unique':
$stage = $dataQuery->getQueryParam('Versioned.stage');
// Recurse to do the default stage behavior (must be first, we rely on stage renaming happening before
// below)
$dataQuery->setQueryParam('Versioned.mode', 'stage');
$this->augmentSQL($query, $dataQuery);
// Now exclude any ID from any other stage. Note that we double rename to avoid the regular stage rename
// renaming all subquery references to be Versioned.stage
foreach ($this->stages as $excluding) {
if ($excluding == $stage) {
continue;
}
$tempName = 'ExclusionarySource_' . $excluding;
$excludingTable = $baseTable . ($excluding && $excluding != $this->defaultStage ? "_{$excluding}" : '');
$query->addWhere('"' . $baseTable . '"."ID" NOT IN (SELECT "ID" FROM "' . $tempName . '")');
$query->renameTable($tempName, $excludingTable);
}
break;
// Return all version instances
// Return all version instances
case 'all_versions':
case 'latest_versions':
foreach ($query->getFrom() as $alias => $join) {
if ($alias != $baseTable) {
$query->setJoinFilter($alias, "\"{$alias}\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\"" . " AND \"{$alias}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
$query->renameTable($alias, $alias . '_versions');
}
// Add all <basetable>_versions columns
foreach (self::$db_for_versions_table as $name => $type) {
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name);
}
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
$query->addOrderBy(sprintf('"%s_versions"."%s"', $baseTable, 'Version'));
// latest_version has one more step
// Return latest version instances, regardless of whether they are on a particular stage
// This provides "show all, including deleted" functonality
if ($dataQuery->getQueryParam('Versioned.mode') == 'latest_versions') {
$query->addWhere("\"{$alias}_versions\".\"Version\" IN \n\t\t\t\t\t(SELECT LatestVersion FROM \n\t\t\t\t\t\t(SELECT \n\t\t\t\t\t\t\t\"{$alias}_versions\".\"RecordID\", \n\t\t\t\t\t\t\tMAX(\"{$alias}_versions\".\"Version\") AS LatestVersion\n\t\t\t\t\t\t\tFROM \"{$alias}_versions\"\n\t\t\t\t\t\t\tGROUP BY \"{$alias}_versions\".\"RecordID\"\n\t\t\t\t\t\t) AS \"{$alias}_versions_latest\"\n\t\t\t\t\t\tWHERE \"{$alias}_versions_latest\".\"RecordID\" = \"{$alias}_versions\".\"RecordID\"\n\t\t\t\t\t)");
}
break;
default:
throw new InvalidArgumentException("Bad value for query parameter Versioned.mode: " . $dataQuery->getQueryParam('Versioned.mode'));
}
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query)
{
if (Subsite::$disable_subsite_filter) {
return;
}
if (Cookie::get('noSubsiteFilter') == 'true') {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly...
if (!$query->filtersOnID()) {
/* if($context = DataObject::context_obj()) $subsiteID = (int)$context->SubsiteID;
else */
$subsiteID = (int) Subsite::currentSubsiteID();
// Don't filter by Group_Subsites if we've already done that
$hasGroupSubsites = false;
foreach ($query->getFrom() as $item) {
if (is_array($item) && strpos($item['table'], 'Group_Subsites') !== false || !is_array($item) && strpos($item, 'Group_Subsites') !== false) {
$hasGroupSubsites = true;
break;
}
}
if (!$hasGroupSubsites) {
if ($subsiteID) {
$query->addLeftJoin("Group_Subsites", "\"Group_Subsites\".\"GroupID\"\n\t\t\t\t\t\t= \"Group\".\"ID\" AND \"Group_Subsites\".\"SubsiteID\" = {$subsiteID}");
$query->addWhere("(\"Group_Subsites\".\"SubsiteID\" IS NOT NULL OR\n\t\t\t\t\t\t\"Group\".\"AccessAllSubsites\" = 1)");
}
}
// WORKAROUND for databases that complain about an ORDER BY when the column wasn't selected (e.g. SQL Server)
$select = $query->getSelect();
if ($hasGroupSubsites && $subsiteID && isset($select[0]) && !$select[0] == 'COUNT(*)') {
$query->orderby = "\"AccessAllSubsites\" DESC" . ($query->orderby ? ', ' : '') . $query->orderby;
}
}
}
/**
* Convert a SQLQuery object into a SQL statement
* @param $query SQLQuery
*/
public function sqlQueryToString(SQLQuery $query)
{
if ($query->getDelete()) {
$text = 'DELETE ';
} else {
$text = $this->sqlSelectToString($query->getSelect(), $query->getDistinct());
}
if ($query->getFrom()) {
$text .= $this->sqlFromToString($query->getFrom());
}
if ($query->getWhere()) {
$text .= $this->sqlWhereToString($query->getWhere(), $query->getConnective());
}
// these clauses only make sense in SELECT queries, not DELETE
if (!$query->getDelete()) {
if ($query->getGroupBy()) {
$text .= $this->sqlGroupByToString($query->getGroupBy());
}
if ($query->getHaving()) {
$text .= $this->sqlHavingToString($query->getHaving());
}
if ($query->getOrderBy()) {
$text .= $this->sqlOrderByToString($query->getOrderBy());
}
if ($query->getLimit()) {
$text .= $this->sqlLimitToString($query->getLimit());
}
}
return $text;
}
/**
* Augment the the SQLQuery that is created by the DataQuery
* @todo Should this all go into VersionedDataQuery?
*/
function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
$baseTable = ClassInfo::baseDataClass($dataQuery->dataClass());
switch ($dataQuery->getQueryParam('Versioned.mode')) {
// Noop
case '':
break;
// Reading a specific data from the archive
// Reading a specific data from the archive
case 'archive':
$date = $dataQuery->getQueryParam('Versioned.date');
foreach ($query->getFrom() as $table => $dummy) {
$query->renameTable($table, $table . '_versions');
$query->replaceText("\"{$table}\".\"ID\"", "\"{$table}\".\"RecordID\"");
// Add all <basetable>_versions columns
foreach (self::$db_for_versions_table as $name => $type) {
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name);
}
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
if ($table != $baseTable) {
$query->addFrom(array($table => " AND \"{$table}_versions\".\"Version\" = \"{$baseTable}_versions\".\"Version\""));
}
}
// Link to the version archived on that date
$archiveTable = $this->requireArchiveTempTable($baseTable, $date);
$query->addFrom(array($archiveTable => "INNER JOIN \"{$archiveTable}\"\n\t\t\t\tON \"{$archiveTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" \n\t\t\t\tAND \"{$archiveTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\""));
break;
// Reading a specific stage (Stage or Live)
// Reading a specific stage (Stage or Live)
case 'stage':
$stage = $dataQuery->getQueryParam('Versioned.stage');
if ($stage && $stage != $this->defaultStage) {
foreach ($query->getFrom() as $table => $dummy) {
// Only rewrite table names that are actually part of the subclass tree
// This helps prevent rewriting of other tables that get joined in, in
// particular, many_many tables
if (class_exists($table) && ($table == $this->owner->class || is_subclass_of($table, $this->owner->class) || is_subclass_of($this->owner->class, $table))) {
$query->renameTable($table, $table . '_' . $stage);
}
}
}
break;
// Return all version instances
// Return all version instances
case 'all_versions':
case 'latest_versions':
foreach ($query->getFrom() as $alias => $join) {
if ($alias != $baseTable) {
$query->setJoinFilter($alias, "\"{$alias}\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\" AND \"{$alias}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
$query->renameTable($alias, $alias . '_versions');
}
// Add all <basetable>_versions columns
foreach (self::$db_for_versions_table as $name => $type) {
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name);
}
$query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID");
// latest_version has one more step
// Return latest version instances, regardless of whether they are on a particular stage
// This provides "show all, including deleted" functonality
if ($dataQuery->getQueryParam('Versioned.mode') == 'latest_versions') {
$archiveTable = self::requireArchiveTempTable($baseTable);
$query->addInnerJoin($archiveTable, "\"{$archiveTable}\".\"ID\" = \"{$baseTable}_versions\".\"RecordID\" AND \"{$archiveTable}\".\"Version\" = \"{$baseTable}_versions\".\"Version\"");
}
break;
default:
throw new InvalidArgumentException("Bad value for query parameter Versioned.mode: " . $dataQuery->getQueryParam('Versioned.mode'));
}
}
/**
* Changes any SELECT query thats not filtering on an ID
* to limit by the current language defined in {@link get_current_locale()}.
* It falls back to "Locale='' OR Lang IS NULL" and assumes that
* this implies querying for the default language.
*
* Use {@link disable_locale_filter()} to temporarily disable this "auto-filtering".
*/
function augmentSQL(SQLQuery &$query)
{
// If the record is saved (and not a singleton), and has a locale,
// limit the current call to its locale. This fixes a lot of problems
// with other extensions like Versioned
$locale = $this->owner->ID && $this->owner->Locale ? $this->owner->Locale : Translatable::get_current_locale();
$baseTable = ClassInfo::baseDataClass($this->owner->class);
if ($locale && self::locale_filter_enabled() && !$query->filtersOnID() && array_search($baseTable, array_keys($query->getFrom())) !== false && !preg_match('/("|\'|`)Locale("|\'|`)/', implode(' ', $query->getWhere()))) {
$qry = sprintf('"%s"."Locale" = \'%s\'', $baseTable, Convert::raw2sql($locale));
$query->addWhere($qry);
}
}
/**
* Convert a SQLQuery object into a SQL statement
* Caution: Expects correctly quoted and escaped SQL fragments.
*
* @param $query SQLQuery
*/
public function sqlQueryToString(SQLQuery $query)
{
if ($query->getDelete()) {
//Appended space at the end of string causing an issue but this might not be the best solution
//@see sqlSelectToString() sqlFromToString
$text = 'DELETE';
} else {
if ($traverse = $query->getTraverse()) {
//Build the traverse string here
$text = $this->sqlTraverseToString($traverse);
} else {
$text = $this->sqlSelectToString($query->getSelect(), $query->getDistinct());
}
}
if ($query->getFrom()) {
$text .= $this->sqlFromToString($query->getFrom());
}
if ($query->getWhere()) {
$text .= $this->sqlWhereToString($query->getWhere(), $query->getConnective());
}
// these clauses only make sense in SELECT queries, not DELETE
if (!$query->getDelete()) {
if ($query->getGroupBy()) {
$text .= $this->sqlGroupByToString($query->getGroupBy());
}
if ($query->getHaving()) {
$text .= $this->sqlHavingToString($query->getHaving());
}
if ($query->getOrderBy()) {
$text .= $this->sqlOrderByToString($query->getOrderBy());
}
if ($query->getLimit()) {
$text .= $this->sqlLimitToString($query->getLimit());
}
}
// SS_Log::log(new Exception(print_r($text, true)), SS_Log::NOTICE);
return $text;
}
