Setting up automated scripts on Windows can be difficult. The built in scheduler is hard to set especially if you don’t have console access. An easier way is to set up the site so that the first person to navigate to the site each morning causes the script to run. This is actually really simple to do, and can be completely seamless for the end user. Simply use the script page as the source of a little 1px by 1px image hidden somewhere at the bottom of your site’s footer:

	<img src="http://www.mysite.com/dbupdate.cfm" width="1" height="1" border="0">

Even though the SRC of this “image” is not a real image file, the server doesn’t know that, so it still runs the page to accommodate the request, and doesn’t require your user to navigate through the page or require you to dump large blocks of code into each of your site’s pages. In essence, this works almost like an include file, but doesn’t require the server to parse and run the template prior to loading the page. It’s also advantageous in that the script won’t die or stop if the user navigates away from the page before it’s completed.

Emulate Crontab

There are a couple of problems that are now presented – first, how to ensure that the first person to navigate the site actually triggers the script? By simply putting this image tag at the bottom of each page in the entire site (whether through an include file or by hard coding it), any page on the site will trigger the script. This could cause another problem though, as we now can’t prevent the user from navigating to another page in the site and causing the script to trigger multiple times. It is important that we build the logic to accommodate these requests quickly (to save server resources), and make sure that multiple downloads don’t occur. Two quick IF/THEN statements will help us. First, let’s discuss the multiple download issue. By creating a file on the server that acts as an alarm that a download is already in progress, we can avoid multiple downloads. I’ll show you how to create the file later in the script, but for now here’s how I am checking for its existence:

	<!--- PATH INFO FOR LOCK FILE --->
	<CFSET MLSLockFile = "C:Inetpubwwwrootmysite.comdatabase_dircrontab.lck">
	
	<!--- CHECK FOR EXISTING LOCK FILE --->
	<CFIF FileExists(MLSLockFile)>
   
	<!--- IF LOCK FILE EXISTS, REPORT THAT THE DOWNLOAD IS IN PROGRESS --->
	<h3>UPDATE IS ALREADY IN PROGRESS!</h3>

This works in the same way that Macromedia’s Emulate Crontab MX check out principle works, in that if this file is present it triggers an alert. In this case, the alert is caught by the IF/THEN statement and the rest of the script is avoided. Since this only takes a millisecond and doesn’t hog any server resources (probably less load on your server than even passing a 1×1.gif image), we can safely call this script from any page of our site – even with decent web traffic – and the download won’t be triggered more than once. Next, we need to make sure that we only do the download when it’s required. For this example, I am restricting downloads to once per day. This is done by obtaining the modified date on the text file we previously FTP downloaded. It’s not much more complicated to get the modified Emulate Crontab as well, if you need to do multiple updates in a single day:

	<!--- LOCK FILE DOES NOT EXIST --->
	<cfelse>
	   
	<!--- GET TIMESTAMP FROM LAST MLS UPDATE (CODE) --->
	<cfscript>
	function FileDateLastModified(path)
	{
	  Var fso  = CreateObject("COM", "Scripting.FileSystemObject");
	  Var theFile = fso.GetFile(path);
	  Return theFile.DateLastModified;
	}
	</cfscript>
	
	<!--- LAST MLS UPDATE (TRIGGER & FILE REFERENCE) --->
	<CFSET TheFile = "C:Inetpubwwwrootmysite.comdatabase_dirListings.txt">
	
	<!--- LAST MLS UPDATE (LOGICAL OPERATOR) --->
	<cfif #DateFormat(FileDateLastModified(TheFile), 'mm/dd/yyyy')# - #DateFormat(Now())# lt 0>

The “TheFile” variable sets up the reference to the text file, and a simple IF/THEN statement checks that the difference between the modified date of the existing file and the current date is less than 0. If this is the case (indicating that an update is needed), then the lock file will be generated and the download/update script will be run:

	<!--- CREATE LOCK FILE --->
	<cffile action="write"
		   file="C:Inetpubwwwrootmysite.comdatabase_dircrontab.lck"
		   output="101010">

Note that the output is needed to write the file, but isn’t used for anything. Here’s where all the above mentioned FTP and database updating code goes. Once the script is complete, we will destroy the temporary lock file:

	<!--- REMOVE LOCK FILE --->
	<cffile action="delete"	file="C:Inetpubwwwrootmysite.comdatabase_dircrontab.lck">

.and now we’ve got a complete picture. To sum up, the script will now do the following tasks for us:

  1. Check for a lock file (preventing multiple downloads).
  2. If not, check the Emulate Crontab to see whether a download and update is needed (keep updates appropriately periodic).
  3. If an update is needed, create a lock file to prevent multiple downloads and FTP the information from a remote location to our local server.
  4. Update the database with the new information.
  5. Remove the lock file (when complete) to allow the update to run again.

Tags: