A need.

1. I have a custom database setup inside my modx3 install. Recently I started to think of adding a auto-publishing feature for this database, just as the modx3’s siteContent table is capable of doing.

Pre-requisite and assumptions.
1. MODX3 installation
2. Custom database table inside the MODX installation, which is created from modx database schema. I used migx’s package manager to create/modify one.
3. Let’s say that the class name of this dbtable is /MyDB/Model/Mydb
3. A custom table schema for modx to construct the table and associated code structure needs the following fields added to the schema. Note the data type for those date fields are all datetime. and not int(20) like original siteContent table fields for page resource. The difference will be dealt in the implementation code.

<field key="published" dbtype="tinyint" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
<field key="publishedon" dbtype="datetime" phptype="datetime" null="true" />
<field key="pub_date" dbtype="datetime" phptype="datetime" null="true" />
<field key="unpub_date" dbtype="datetime" phptype="datetime" null="true"/>

4. The auto-publish mechanism will mimic the existing code for modResource autopublishing mechanism. This reference code lives in core\src\revolution\modcachemanager.class.php. (for MODX2, the file is in core\model\modx\ folder)

5. The function I want is to toggle the record’s published flag based on records’ pub_date and un_pub date. It is still my implementation code (snippets) to filter records based on this flag to display data in the web site.

Two code segments have been written. The first is auto publishing functionality. The code heavily borrows from the aforementioned built-in autopublishing code, which is for page resources.

<?php
/**
 * @var $modx object
 * this code first check if auto_publish.cache.php returns the timestamp that is smaller than now timestamp.
 * if so, then there is at least one Mydb record that need to be acted on (either publish or unpublish))
 * and calls autopublish_mydb().
 */
/* first, check the cache value and compare it to current time */
$options = array(XPDO::OPT_CACHE_KEY => 'my_db'); 
//With this option, cache file is in /core/cache/my_db folder

$actionNeeded = $modx->cacheManager->get('auto_publish', $options);
$timeNow = time();
//No action is taken if cached timestamp is zero or bigger than today's timestamp, except when timestamp does not exist (null)
if (($actionNeeded>0 && $actionNeeded < $timeNow)  || $actionNeeded===null) autoPublish_mydb($modx, $options);

/** * @param $modx
 * @param array $options
 * @return void
 * this function check the Mydb table and alter record's published status.
 * if pub_date is less than current timestamp, and published is false then the record will be published and publishedon data
 * updated, and pub_date will be set to null
 * if unpub_date is less than current timestamp, and published is true then the record will be unpublished and publishedon date
 * and unpub_date will be set to null
 * record is scanned for new unpub/pub_date and cache file is refreshed.
 */
function autoPublish_mydb($modx, array $options): void
{

    $tblResource = $modx->getTableName('MyDB\Model\Mydb');

    $timeNow = time();  //get timestamp number of current date/time

    /* publish and unpublish resources using pub_date and unpub_date checks */
    $modx->exec("UPDATE $tblResource SET published=1, publishedon=pub_date, pub_date=NULL WHERE pub_date IS NOT NULL AND unix_timestamp(pub_date) < $timeNow");
    $modx->exec("UPDATE $tblResource SET published=0, publishedon=NULL,unpub_date=NULL WHERE unpub_date IS NOT NULL AND unix_timestamp(unpub_date) < $timeNow");

    /* update publish time file */
    $timesArr = array();
    $stmt = $modx->prepare("SELECT MIN(unix_timestamp(pub_date)) FROM $tblResource WHERE published = 0 AND unix_timestamp(pub_date) > 0");
    if ($stmt->execute()) {
        $value = $stmt->fetch(PDO::FETCH_NUM); // MIN operator query will return only one row. query is asking for only one column
        if ($value[0]) $timesArr[] = $value[0]; // that column is either empty or value. If value exists, push into array.

    }
    $stmt = $modx->prepare("SELECT MIN(UNIX_TIMESTAMP(unpub_date)) FROM $tblResource WHERE published = 1 AND UNIX_TIMESTAMP(unpub_date) > 0");
    if ($stmt->execute()) {
        $value = $stmt->fetch(PDO::FETCH_NUM);
        if ($value[0]) $timesArr[] = $value[0];

    }

    $nextEvent = (count($timesArr) > 0) ? min($timesArr) : 0;//select younger of timestamps if these exist. Otherwise, $nextEvent=0;
    /* cache the time of the next auto_publish event */
    $modx->cacheManager->set('auto_publish', $nextEvent, 0, $options);
}

Some points to note.

1. dbtype for these dates are datetime. In original code, they are integer(timestamp).   MySql function unix_timestamp() is used inside the query to  convert them as needed.
2. The function return type is changed from array to void. I thus removed all return values construction as I could not think of any use case in my usage scenario.
3. This code, when invoked will fetch the cache file /core/cache/my_db/auto_publish.cache.php. The file will return a timestamp value or 0 or null. The location of the cache file is defined in $options array and separate from the system’s auto publish cache file.
4. if timestamp returned is less than current timestamp but not 0 or timestamp returned is null (meaning there is no cache file) then it proceeds to auto_publish function Otherwise it will return without doing anything.
   1. Auto publishing function will do the following. They are basically two pdo queries.
      1. Publish all record that’s pub_date is less than current time, unset pub_date, set publishedon to pub_date.
      2. Unpublish all record that’s unpub_date is less than current tme. unset unpub_date and unset publishedon date
      3. set new cache value to minimum of remaining unpub_date and pub_date sets.
   2. In the original code, Unpublishing query is resetting both unpub_date and pub_date. I can not see any side effects on not unsetting pub_date so I left it out.

Now, when to invoke this code? The original Resource auto_publishing code is invoked whenever resource is accessed.

I originally added the code as a snippet to my site’s landing page so that the code will execute whenever somebody visit my site. But I also wanted to tryout the plugin functionality, so I saved the code as a plugin and named the plugin refresh_pub_date

Then in the system’s settings, I created a custom event. myCustomeEvent and enabled the refresh_pub_date plugin to this event.

The following code will invoke this event. So my snippet in the landing page is just that.

$modx->invokeEvent('refresh_pub_date',array())

Second code is used to check for pub_date, unpub_date after record save/update

<?php
/**
 * afterSave snippet for Mydb (migxdb configurator setup  'snippethook')
 *  the snippet will update auto_publish timestamp for the custom table
 * if unpub or pub date is smaller than the value already cached.
 *
 * migx provides the following variables
 * @var $modx object modx runtime object
 * @var $object object  object of the class being handled
 * @var $postvalues  array of posted values
 * @var $scriptProperties  mixed parameters, Includes all of the above.
 */

$result = '{"error":"","result":"success"}';
$options = array(XPDO::OPT_CACHE_KEY => 'my_db'); //define folder name for cache file
$timesArray = [];
$pub_date_ts = strtotime($postvalues['pub_date']); //retrieve pub_date datestamp
// the value is also retrievable from $object.  ie., strtotime($object->get('pub_date'));
if ($pub_date_ts) {
    $timesArray[] = $pub_date_ts;
} //push pub_date to times array if exists. boolean evaluation of the variable returns
// false if $pub_data_ts is empty, null, 0 or false

$unpub_date_ts = strtotime($postvalues['unpub_date']);
if ($unpub_date_ts) {
    $timesArray[] = $unpub_date_ts;
} // push unpublish date timestamp to the times array if exists

$current_ts = $modx->cacheManager->get('auto_publish', $options);
if ($current_ts === null and count($timesArray) == 0) {//In case of no cache file, meaning  the file is manually deleted somehow.
    $current_ts = time(); //set the time so auto_publish logic will run once and will set correct time if pub/unpub already existed.
    $modx->cacheManager->add('auto_publish', $current_ts, 0, $options);
}

if (count($timesArray) > 0) { //rewrite timestamp only if new TS is younger or the  current one is 0. Don't write otherwise
    $new_candidate_ts = min($timesArray);
    if ($new_candidate_ts < $current_ts || $current_ts === 0) {
        $modx->cacheManager->replace('auto_publish', $new_candidate_ts, 0, $options);
    }
}
return $result;

This code will be invoked when Mydb record is added/updated. Since I use migxDB to create a CMP for this DB table in the manager, migxDB custom configurator allows the easy way to attach the snippet to after save action.

{"aftersave":"mySnippet"}

If called this way, migxDB provides $postvalues array variable to allow access to posted data field that is needed to be convert to timestamp like so.

strtotime($postvalues['unpub_date']);

It also provides $object variable for the class object (including record fields that are not posted) So the same value can be retrieved with usual get method.

strtotime($object->get('unpub_date'))

Note: If I don’t use migxDB CMP configurator and write my own class processors extended from MODX processors, retrieval of the same value in aftersave hook in the update processor would have been

strtotime($this->getProperty('unpub_date'))

There was a bonus take away from this project

1. I learned how to use MODX Cache file system. The main purpose of using the cache files must be obviously to eliminate a need of re-running of various codes on server side. However I can also do things like the following to record values of variables into the cache file for debugging purpose.

$options=array(XPDO::OPT_CACHE_KEY=>'debugstore');
$myvalues=print_r($varToCheck,true);
$modx->cacheManager->set('myVarvalues',$myvalues,0,$options);