/** * Augment the the SQLSelect that is created by the DataQuery * @todo Should this all go into VersionedDataQuery? */ public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = null) { if (!$dataQuery || !$dataQuery->getQueryParam('Versioned.mode')) { return; } $baseTable = ClassInfo::baseDataClass($dataQuery->dataClass()); switch ($dataQuery->getQueryParam('Versioned.mode')) { // Reading a specific data from the archive case 'archive': $date = $dataQuery->getQueryParam('Versioned.date'); foreach ($query->getFrom() as $table => $dummy) { if (!DB::get_schema()->hasTable($table . '_versions')) { continue; } $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 (Config::inst()->get('Versioned', '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 $query->addWhere(array("\"{$baseTable}_versions\".\"Version\" IN\n\t\t\t\t(SELECT LatestVersion FROM\n\t\t\t\t\t(SELECT\n\t\t\t\t\t\t\"{$baseTable}_versions\".\"RecordID\",\n\t\t\t\t\t\tMAX(\"{$baseTable}_versions\".\"Version\") AS LatestVersion\n\t\t\t\t\t\tFROM \"{$baseTable}_versions\"\n\t\t\t\t\t\tWHERE \"{$baseTable}_versions\".\"LastEdited\" <= ?\n\t\t\t\t\t\tGROUP BY \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t\t) AS \"{$baseTable}_versions_latest\"\n\t\t\t\t\tWHERE \"{$baseTable}_versions_latest\".\"RecordID\" = \"{$baseTable}_versions\".\"RecordID\"\n\t\t\t\t)" => $date)); 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); $dataQuery->setQueryParam('Versioned.mode', 'stage_unique'); // 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 (Config::inst()->get('Versioned', 'db_for_versions_table') as $name => $type) { $query->selectField(sprintf('"%s_versions"."%s"', $baseTable, $name), $name); } // Alias the record ID as the row ID $query->selectField(sprintf('"%s_versions"."%s"', $baseTable, 'RecordID'), "ID"); // Ensure that any sort order referring to this ID is correctly aliased $orders = $query->getOrderBy(); foreach ($orders as $order => $dir) { if ($order === "\"{$baseTable}\".\"ID\"") { unset($orders[$order]); $orders["\"{$baseTable}_versions\".\"RecordID\""] = $dir; } } $query->setOrderBy($orders); // 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)"); } else { // If all versions are requested, ensure that records are sorted by this field $query->addOrderBy(sprintf('"%s_versions"."%s"', $baseTable, 'Version')); } break; default: throw new InvalidArgumentException("Bad value for query parameter Versioned.mode: " . $dataQuery->getQueryParam('Versioned.mode')); } }
/** * Select the given field expressions. * * @param $fieldExpression String The field to select (escaped SQL statement) * @param $alias String The alias of that field (escaped SQL statement) */ protected function selectField($fieldExpression, $alias = null) { $this->query->selectField($fieldExpression, $alias); }
public function testDbDatetimeDifference() { $offset = $this->checkPreconditions(); $clause = $this->adapter->datetimeDifferenceClause('1974-10-14 10:30:00', '1973-10-14 10:30:00'); $result = DB::query('SELECT ' . $clause)->value(); $this->matchesRoughly($result / 86400, 365, '1974 - 1973 = 365 * 86400 sec', $offset); $clause = $this->adapter->datetimeDifferenceClause(date('Y-m-d H:i:s', strtotime('-15 seconds')), 'now'); $result = DB::query('SELECT ' . $clause)->value(); $this->matchesRoughly($result, -15, '15 seconds ago - now', $offset); $clause = $this->adapter->datetimeDifferenceClause('now', $this->adapter->datetimeIntervalClause('now', '+45 Minutes')); $result = DB::query('SELECT ' . $clause)->value(); $this->matchesRoughly($result, -45 * 60, 'now - 45 minutes ahead', $offset); $query = new SQLSelect(); $query->setSelect(array()); $query->selectField($this->adapter->datetimeDifferenceClause('"LastEdited"', '"Created"'), 'test')->setFrom('"DbDateTimeTest_Team"')->setLimit(1); $result = $query->execute()->value(); $lastedited = Dataobject::get_one('DbDateTimeTest_Team')->LastEdited; $created = Dataobject::get_one('DbDateTimeTest_Team')->Created; $this->matchesRoughly($result, strtotime($lastedited) - strtotime($created), 'age of HomePage record in seconds since unix epoc', $offset); }