Preview future state

[chillu]

Allow an author to set a date in the preview panel and browse through the site at that state, including any embargo or expire actions applied

We've attempted this before with the SiteTreeFutureState extension in the old silverstripe-workflow module.

[chillu]

Hey @mikenz, Chris (@flamerohr) mentioned that you might have future state working in a custom version of advancedworkflow or your CMS? Is that something you can share, at least as a starting point? We're looking to extract embargo/expiry from advancedworkflow into this module here, and future state preview came up again in this context.

[mikenz]

Sure do.... you're going to love working with this code :-)

This would end up in EmbargoExpiryExtension:

    /**
     * Get any future time set in GET param. Must use ISO-8601 format for time to be parsed correctly.
     * e.g: 20160513T2359Z
     *
     * @param  $ctrl  Optional for supplying a controller, useful for unit testing
     * @return string Time in format useful for SQL comparison.
     */
    public function getFutureTime($ctrl = null)
    {
        // Lazy load future time unless we are passing in a controller object explicitly
        if (!static::$future_time || $ctrl) {

            $curr = ($ctrl) ? $ctrl : (Controller::has_curr() ? Controller::curr() : false);

            if ($curr) {
                $ft = $curr->getRequest()->getVar('ft');
                if ($ft) {
                    // Force timezone to UTC so that it does not apply current timezone offset
                    $dt = DateTime::createFromFormat('Ymd\THi\Z', $ft, new DateTimeZone('UTC'));
                    static::$future_time = $dt->format('Y-m-d H:i');
                }
            }
            $time = static::$future_time;
        }
        return static::$future_time;
    }

    /**
     * Set future time flag on the query for further queries to use. Only set if Versioned
     * extension is applied as the query relies on _Versions tables.
     */
    public function augmentDataQueryCreation(SQLSelect &$query, DataQuery &$dataQuery)
    {
        // If time is set then flag it up for queries
        $time = $this->getFutureTime();
        if ($time && $this->owner->has_extension('SilverStripe\\Versioned\\Versioned')) {
            $dataQuery->setQueryParam('Future.time', $time);
        }
    }
    

    /**
     * Check if we are currently requesting a virtual page. Virtual pages are a special case as their embargo/expiry
     * relies on the source page they point to. We need to avoid augmenting the SQL for virtual pages, in the first
     * instance when the page is requested the only information we have is the URLSegment
     * (@see ModelAsController::getNestedController()) so this method finds all URLSegments for virtual pages and does
     * a comparison.
     *
     * @param  SQLSelect $query
     * @param  DataQuery $dataQuery
     * @return boolean              True if page requested is a virtual
     */
    private function requestingVirtual($query, $dataQuery)
    {
        // Special casing for Virtual pages, if we are getting a SiteTree object it might be a virtual page, which does
        // not have the embargo fields set correctly so we need to avoid getting future state for this object
        $baseClass = DataObject::getSchema()->baseDataClass($dataQuery->dataClass());
        $baseTable = DataObject::getSchema()->baseDataTable($baseClass);

        if ($baseTable == 'SilverStripe\\CMS\\Model\\SiteTree') {

            // Get the URL segment from the query
            $where = $query->getWhere();
            $segment = null;
            if ($where) {
                foreach ($where as $key => $val) {
                    if (is_array($val) && isset($val['"SiteTree"."URLSegment" = ?'][0])) {
                        $segment = $val['"SiteTree"."URLSegment" = ?'][0];
                    }
                }
            }

            if ($segment) {
                $classes = array_keys(ClassInfo::subclassesFor('SilverStripe\\CMS\\Model\\VirtualPage'));
                $result = DB::prepared_query(
                    "SELECT \"URLSegment\"
                    FROM \"SiteTree\"
                    WHERE \"ClassName\" IN (" . DB::placeholders($classes) . ")",
                    $classes
                );

                if ($result && $result->numRecords()) {
                    $segments = $result->column();
                    if (in_array($segment, $segments)) {
                        return true;
                    }
                }
            }
        }
        return false;
    }

    /**
     * Alter SQL queries for this object so that the version matching the time that is passed is returned.
     * Relies on Versioned extension as it queries the _Versions table and is only triggered when viewing the staging
     * site e.g: ?stage=Stage. This has the side effect that Versioned::canViewVersioned() is used to restrict
     * access.
     */
    public function augmentSQL(SQLSelect $query, DataQuery $dataQuery = null)
    {
        $time = $dataQuery->getQueryParam('Future.time');

        if (!$time
            || !$this->owner->has_extension('SilverStripe\\Versioned\\Versioned')
            || $this->owner instanceof VirtualPage
            || $this->requestingVirtual($query, $dataQuery)
        ) {
            return;
        }

        // Only trigger future state when viewing "Stage", this ensures the query works with Versioned::augmentSQL()
        $stage = $dataQuery->getQueryParam('Versioned.stage');
        if ($stage === Versioned::DRAFT) {
            $baseClass = DataObject::getSchema()->baseDataClass($dataQuery->dataClass());
            $baseTable = DataObject::getSchema()->baseDataTable($baseClass);

            foreach ($query->getFrom() as $alias => $join) {
                $aliasClass = DataObject::getSchema()->tableClass($alias);
                if (!class_exists($aliasClass)
                    || !is_a($aliasClass, $baseClass, true)
                    || !DB::get_schema()->hasTable($alias . '_Versions')
                ) {
                    continue;
                }

                if ($alias != $baseTable) {
                    // Make sure join includes version as well
                    $query->setJoinFilter(
                        $alias,
                        "\"{$alias}_Versions\".\"RecordID\" = \"{$baseTable}_Versions\".\"RecordID\""
                        . " AND \"{$alias}_Versions\".\"Version\" = \"{$baseTable}_Versions\".\"Version\""
                    );
                }
                $query->renameTable($alias, $alias . '_Versions');
            }

            // Add all <basetable>_Versions columns
            foreach (Config::inst()->get(Versioned::class, 'db_for_versions_table') as $name => $type) {
                $query->selectField(sprintf('"%s_Versions"."%s"', $baseTable, $name), $name);
            }

            // Alias the record ID as the row ID, and ensure ID filters are aliased correctly
            $query->selectField("\"{$baseTable}_Versions\".\"RecordID\"", "ID");
            $query->replaceText("\"{$baseTable}_Versions\".\"ID\"", "\"{$baseTable}_Versions\".\"RecordID\"");

            // However, if doing count, undo rewrite of "ID" column
            $query->replaceText(
                "count(DISTINCT \"{$baseTable}_Versions\".\"RecordID\")",
                "count(DISTINCT \"{$baseTable}_Versions\".\"ID\")"
            );

            // Make sure we haven't already added workflow where to this query,
            // if already present the optimizations below won't work
            $existingWheres = $query->getWhere();
            foreach ($existingWheres as $k => $where) {
                if (!is_object($where) && strpos(array_keys($where)[0], "FutureState magic") !== false) {
                    // Remove existing futurestate additional where
                    unset($existingWheres[$k]);
                    $query->setWhere($existingWheres);
                    break;
                }
            }

            // Optimised for requesting a single page ID
            $optimizeLive = $optimizeVersions = '';
            /** @skipUpgrade */
            if ($baseTable == 'SiteTree' && count($query->getWhere()) == 1 &&
                isset($query->getWhere()[0]['"SiteTree"."ID" = ?']) &&
                count($query->getWhere()[0]['"SiteTree"."ID" = ?']) == 1) {
                $recordID = current($query->getWhere()[0]['"SiteTree"."ID" = ?']);
                $optimizeLive = "\"{$baseTable}_Live\".ID = '$recordID' AND";
                $optimizeVersions = "\"{$baseTable}_Versions\".RecordID = '$recordID' AND";
            }

            // Optimised for children of a page on a subsite
            /** @skipUpgrade */
            if ($baseTable == 'SiteTree' && count($query->getWhere()) == 2 &&
                preg_match('/"SiteTree"."ParentID" = ([0-9]+)/', array_keys($query->getWhere()[0])[0], $matches1) &&
                preg_match(
                    '/"SiteTree"."SubsiteID" IN \(([0-9]+)\)/',
                    array_keys($query->getWhere()[1])[0],
                    $matches2
                )) {
                $parentID = $matches1[1];
                $subsiteID = $matches2[1];
                $optimizeLive = "\"{$baseTable}_Live\".ParentID = '$parentID' AND
                                 \"{$baseTable}_Live\".SubsiteID = '$subsiteID' AND";
                $optimizeVersions = "\"{$baseTable}_Versions\".ParentID = '$parentID' AND
                                     \"{$baseTable}_Versions\".SubsiteID = '$subsiteID' AND";
            }

            // Optimised for specific ID of specific ClassName
            /** @skipUpgrade */
            if ($baseTable == 'SiteTree' && count($query->getWhere()) == 2 &&
                isset($query->getWhere()[0]['"SiteTree"."ID" = ?']) &&
                count($query->getWhere()[0]['"SiteTree"."ID" = ?']) == 1 &&
                isset($query->getWhere()[1]['"SiteTree"."ClassName" IN (?)']) &&
                count($query->getWhere()[1]['"SiteTree"."ClassName" IN (?)']) == 1) {
                $recordID = current($query->getWhere()[0]['"SiteTree"."ID" = ?']);
                $className = current($query->getWhere()[1]['"SiteTree"."ClassName" IN (?)']);
                $optimizeLive = "\"{$baseTable}_Live\".ID = '$recordID' AND";
                $optimizeVersions = "\"{$baseTable}_Versions\".RecordID = '$recordID' AND";
            }

            // Optimised for specific ClassName on a subsite
            /** @skipUpgrade */
            if ($baseTable == 'SiteTree' && count($query->getWhere()) == 2 &&
                isset($query->getWhere()[0]['"SiteTree"."ClassName" IN (?)']) &&
                count($query->getWhere()[0]['"SiteTree"."ClassName" IN (?)']) == 1 &&
                preg_match(
                    '/"SiteTree"."SubsiteID" IN \(([0-9]+)\)/',
                    array_keys($query->getWhere()[1])[0],
                    $matches2
                )) {
                $subsiteID = $matches2[1];
                $className = Convert::raw2sql(current($query->getWhere()[0]['"SiteTree"."ClassName" IN (?)']));
                $optimizeLive = "\"{$baseTable}_Live\".ClassName = '$className' AND
                                 \"{$baseTable}_Live\".SubsiteID = '$subsiteID' AND";
                $optimizeVersions = "\"{$baseTable}_Versions\".ClassName = '$className' AND
                                     \"{$baseTable}_Versions\".SubsiteID = '$subsiteID' AND";
            }

            // Optimised for children of a page on a subsite of a certain page type
            /** @skipUpgrade */
            if ($baseTable == 'SiteTree' && count($query->getWhere()) == 3 &&
                preg_match('/"SiteTree"."ParentID" = ([0-9]+)/', array_keys($query->getWhere()[0])[0], $matches1) &&
                isset($query->getWhere()[1]['"SiteTree"."ClassName" IN (?)']) &&
                count($query->getWhere()[1]['"SiteTree"."ClassName" IN (?)']) == 1 &&
                preg_match(
                    '/"SiteTree"."SubsiteID" IN \(([0-9]+)\)/',
                    array_keys($query->getWhere()[2])[0],
                    $matches2
                )) {
                $parentID = $matches1[1];
                $subsiteID = $matches2[1];
                $optimizeLive = "\"{$baseTable}_Live\".ParentID = '$parentID' AND
                                 \"{$baseTable}_Live\".SubsiteID = '$subsiteID' AND";
                $optimizeVersions = "\"{$baseTable}_Versions\".ParentID = '$parentID' AND
                                     \"{$baseTable}_Versions\".SubsiteID = '$subsiteID' AND";
            }

            // Optimised for specific URLSegment on a specific subsite
            /** @skipUpgrade */
            if ($baseTable == 'SiteTree' && count($query->getWhere()) == 2 &&
                isset($query->getWhere()[0]['"SiteTree"."URLSegment" COLLATE utf8_bin = ?']) &&
                count($query->getWhere()[0]['"SiteTree"."URLSegment" COLLATE utf8_bin = ?']) == 1 &&
                preg_match(
                    '/"SiteTree"."SubsiteID" IN \(([0-9]+)\)/',
                    array_keys($query->getWhere()[1])[0],
                    $matches1
                )) {
                $urlSegment = Convert::raw2sql($query->getWhere()[0]['"SiteTree"."URLSegment" COLLATE utf8_bin = ?'][0]);
                $subsiteID = $matches1[1];
                $optimizeLive = "\"{$baseTable}_Live\".URLSegment = '$urlSegment' AND
                                 \"{$baseTable}_Live\".SubsiteID = '$subsiteID' AND";
                $optimizeVersions = "\"{$baseTable}_Versions\".URLSegment = '$urlSegment' AND
                                     \"{$baseTable}_Versions\".SubsiteID = '$subsiteID' AND";
            }

            /*
             * Querying the _Versions table to find the most recent draft or published record that would be published at
             * the time requested. When embargo is NULL it is assumed that the record is published immediately. When
             * expiry is NULL it is assumed that the record is never unpublished.
             */
            $query->addWhere([
                "/* FutureState magic */
                \"{$baseTable}_Versions\".\"Version\" IN
                (SELECT MAX(Version) FROM
                    (
                      /* Get the latest Published version that hasn't expired in published or in draft */
                      SELECT
                        \"{$baseTable}_Live\".ID,
                        \"{$baseTable}_Live\".Version
                      FROM
                        \"{$baseTable}_Live\"
                        LEFT JOIN \"{$baseTable}\" AS Base ON Base.ID = \"{$baseTable}_Live\".ID
                       WHERE
                         $optimizeLive
                          /* Not expired in published version */
                          (
                            \"{$baseTable}_Live\".UnPublishOnDate > ?
                            OR \"{$baseTable}_Live\".UnPublishOnDate IS NULL
                          )
                        AND
                          /* Draft exists, hasn't already expired */
                         (
                            Base.UnPublishOnDate > ?
                            OR Base.UnPublishOnDate IS NULL
                         )

                      /* Get the latest Draft version */
                      UNION SELECT
                        \"{$baseTable}_Versions\".RecordID as ID,
                        MAX(\"{$baseTable}_Versions\".Version) AS LatestVersion
                      FROM
                        \"{$baseTable}_Versions\"
                        JOIN \"{$baseTable}\" AS Base2 ON Base2.ID = \"{$baseTable}_Versions\".RecordID
                      WHERE
                        $optimizeVersions
                          /* Hasn't already been published */
                          \"{$baseTable}_Versions\".WasPublished = 0
                        AND
                          /* Approved, which is marked by a PublishJobID */
                          (Base2.PublishJobID != 0)
                        AND
                          /* The embargoed in the past */
                          (
                            Base2.PublishOnDate <= ?
                            OR Base2.PublishOnDate IS NULL
                          )
                        AND
                          /* Hasn't already expired */
                          (
                            Base2.UnPublishOnDate > ?
                            OR Base2.UnPublishOnDate IS NULL
                          )
                      GROUP BY
                        \"{$baseTable}_Versions\".RecordID

                      /* Get virtual where the source is emabgoed */
                      UNION SELECT
                        \"{$baseTable}_Versions\".RecordID AS ID,
                        MAX(\"{$baseTable}_Versions\".Version) AS LatestVersion
                      FROM
                        \"{$baseTable}_Versions\"
                        JOIN VirtualPage_Versions as Virtual ON
                            Virtual.RecordID = \"{$baseTable}_Versions\".RecordID
                          AND
                            Virtual.Version = \"{$baseTable}_Versions\".Version
                        JOIN \"{$baseTable}_Versions\" as Source ON Source.RecordID = Virtual.CopyContentFromID
                        JOIN \"{$baseTable}\" AS Base3 ON
                          /* Ensures the page is still on draft, and not archived */
                          Base3.ID = \"{$baseTable}_Versions\".RecordID
                      WHERE
                        $optimizeVersions
                          /* Hasn't already been published */
                          Source.WasPublished = 0
                        AND
                          /* Approved, which is marked by a PublishJobID */
                          (Source.PublishJobID != 0)
                        AND
                          /* The embargoed in the past */
                          (
                            Source.PublishOnDate <= ?
                            OR Source.PublishOnDate IS NULL
                          )
                        AND
                          /* Hasn't already expired */
                          (
                            Source.UnPublishOnDate > ?
                            OR Source.UnPublishOnDate IS NULL
                          )
                        AND
                          Source.Version = (
                            SELECT
                              MAX(sv.Version)
                            FROM
                              \"{$baseTable}_Versions\" as sv
                            WHERE
                              sv.RecordID = Source.RecordID
                          )
                      GROUP BY
                        \"{$baseTable}_Versions\".RecordID


                    ) AS \"{$baseTable}_versions_latest\"
                    WHERE \"{$baseTable}_versions_latest\".\"ID\" = \"{$baseTable}_Versions\".\"RecordID\"
                )"
                => [$time, $time, $time, $time, $time, $time]
            ]);

            // Hack to address the issue of replacing {$baseTable} with {$baseTable}_Versions everywhere in the query,
            // there are places where we do want to use {$baseTable}
            $query->replaceText(
                "\"{$baseTable}_Versions\" AS Base",
                "\"{$baseTable}\" AS Base"
            );
        }
    }    

Regarding virtual pages, our virtual pages work differently to standard ones. When the source page is published it publishes all the virtuals of it. So the above code is written with that assumption. Our publishers don't want to have to embargo the source page and all the virtuals of it.

[mikenz]

Further to above,

A future state url looks like this: http://www.example.com/test-page?stage=Stage&ft=20171130T1300Z

Using this form field on the bottom of the PublishingSchedule tab: https://github.com/silverstripe-terraformers/advancedworkflow/blob/development/code/formfields/FutureStatePreviewField.php As an easy way to generate the link.
We've also added it into BetterNavigator and a few other places.

[chillu]

Hah thanks Mike, that's about the terrifying shape of code I expected ... the future is a scary place :D You wouldn't happen to have unit tests for it as well?

[mikenz]

Yes, thanks to @frankmullenger and @flamerohr, you'll need to update some use statements and decouple it from advancedworkflow a bit
tests.zip

[flamerohr]

oh neat, the (partially) traumatic memories :D

[mikenz]

@flamerohr your original queries were much simpler: https://github.com/silverstripe-terraformers/advancedworkflow/blob/development/code/extensions/WorkflowEmbargoExpiryExtension.php#L771
But didn't give the performance we needed so I made them more complicated to take advantage of table indexes. The original queries ended up doing a full table scan of SiteTree_Versions which didn't scale.

[flamerohr]

fair enough :)
I had an inkling of an idea that it was to do with indexes, pretty neat to see the diff