ColdFusion in Context: Time Travel

No, you're not going to join Mr. Peabody in the Wayback machine. However, suppose you want to know what the price of an item was three months ago. "Time travel" was dropped from the capabilities of a popular research database years ago. However, ColdFusion can help.

Consider Your Needs

Whenever someone changes the price, you don't really want to do an update. You want a new row to be added to the table. However, you don't want all these old rows showing up when you run an item list. Therefore, you could use a Current field that would be set to 1 for only the Current row and 0 for non-current rows. This means that an edit would consist of two database accesses: one to mark all existing rows for this item as non-current and one to insert the new row (marked current). There are other nuances to consider, but this is enough to indicate what a likely data structure might look like.

Make a Table

Make a table ItemPrice with fields Item, Price, Current, and AsOf where Item is text, Price is a number, AsOf is a date, and Current is a number (or bit if you like).

List Items

The list, list.cfm, will let the user go to an add/change form to add an item. It will also let the user select an existing item with the intention of changing it or deleting it. Include a couple of files to support this later. Query only the current rows. Build a table that lists the result and includes appropriate edit and delete links. The links use urlEncodedFormat for the Item to accommodate spaces, etc. 

<!--- Update or delete from database as needed --->
<cfinclude template="update.cfm">
<cfinclude template="delete.cfm">

<!--- Fill and display the list and controls --->
<cfquery name="ItemList" datasource="context">
select * from ItemPrice
where Current = 1
order by Item
</cfquery>
<a href="change.cfm?Mode=Add">Add</a>
<table border="1">	
<tr><td>Edit</td><td>Item</td><td>Price</td>
<td>Delete</td></tr>
<cfoutput query="ItemList">
<tr><td>
<a href="change.cfm?Mode=Edit&Item=#urlEncodedFormat(Item)#">
Edit</a></td>
<td>#Item#</td><td>#Price#</td>
<td>
<a href="list.cfm?Mode=Delete&Item=#urlEncodedFormat(Item)#">
Delete</a></td></tr>
</cfoutput>
</table>

Delete Items

Here's delete.cfm. Deletion is easy. If the Mode parameter passed in the URL is "Delete", just delete every matching row whose item matches the Item in the URL. This could of course be made part of list.cfm instead of being included. Because urlEncodedFormat was used in passing the Item, urlDecode is used to retrieve it. 

<cfparam name="Mode" default="">
<cfif Mode is "Delete">
  <cfquery name="deleteItem" datasource="context">
  delete * from ItemPrice
  where Item = '#urlDecode(url.Item)#'
  </cfquery>
</cfif>

Build an Add and Edit Form

Build change.cfm to add and edit individual items. It is submitted to itself by default; so, set up code that includes the list and stops loading this portion of the page when the user wants to get there. Because the form calls itself for "add", it needs a way to tell if there is anything to add yet. You could set Item empty and test it, or you can use a hidden field as is done here. The code to actually perform the add could be "inline", but it's included from another file here for simplicity. 

<!--- Go back to list if asked --->
<cfparam Name="Back" default="">
<cfif len(trim(Back))>
  <cfinclude template="list.cfm">
  <cfabort>
</cfif>

<!--- Set defaults --->
<cfparam Name="Mode" default="Add">
<cfparam Name="Go" default="">

<!--- Add to database if requested --->
<cfinclude template="add.cfm">

Fill the form with the real current row if editing; otherwise, use an empty row. Set the form action to the list if editing (so it will return to the list after the change); otherwise, set the form action to this page. You don't want users editing the time directly; so, it's displayed as a label. You want to know if the user has changed the actual item name; so, keep track of the original name (OldItem) in a hidden field for later comparison. The normal submit button (Go) does double duty. Not only will it have an appropriate name (Edit or Add), but the presence of its value is used to tell the page that a new row must be added. This form has two submit buttons. If the Back button (labeled List) is pressed, earlier code will direct the user to the list. 

<!--- Fill and display the form appropriately --->
<cfif Mode is "Edit">
  <cfquery name="ItemGet" datasource="context">
  select * from ItemPrice
  where Item = '#Item#'
  and Current = 1
  </cfquery>
  <cfset MyAction="list.cfm">
<cfelse>
  <!--- Mode is assumed to be "Add" --->
  <cfquery name="ItemGet" datasource="context">
  select * from ItemPrice
  where 1 = 0
  </cfquery>
  <cfset dummy=queryAddRow(ItemGet)>
  <cfset MyAction="change.cfm">
</cfif>
<cfoutput>
<form name="Change" action="#MyAction#" method="post">
Item: <input name="Item" type="text" 
maxlength="50" size="55" value="#ItemGet.Item#"><br>
Price as of #dateFormat(ItemGet.AsOf)#
#timeFormat(ItemGet.AsOf)#:
<input name="Price" type="text" maxLength="10" 
size="12" value="#ItemGet.Price#">
<input name="OldItem" type="hidden" 
value="#ItemGet.Item#">
<input name="Go" type="submit" value="#Mode#">
<input name="Back" type="submit" value="List">
</form>
</cfoutput>

If the item being edited has a history (non-current rows), show the history below the form. 

<cfif Mode is "Edit">
  <cfquery name="ItemOldList" datasource="context">
  select * from ItemPrice
  where Item = '#Item#'
  and Current <> 1
  </cfquery>
  <cfif ItemOldList.recordcount>
    <table border="1"><tr><td>ITEM</td>
    <td>PRICE</td><td>LAST CHANGED</td></tr>
    <cfoutput query="ItemOldList">
    <tr><td>#ItemOldList.Item#</td>
    <td>#ItemOldList.Price#</td>
    <td>#dateFormat(ItemOldList.AsOf)#
    #timeFormat(ItemOldList.AsOf)#</td></tr>
    </cfoutput>
    </table>
  </cfif>
</cfif>

Add

This code, add.cfm, could be part of change.cfm. When the form has been submitted in Add mode, it returns to change.cfm, which includes this page. When the Go button the value of "Add", the user has asked to add a record. Check, and tell the user if the item already exists. Otherwise, add the new record, taking the current time as the time. 

<cfif Go is "Add">
  <cfquery name="ItemGet" datasource="context">
  select * from ItemPrice
  where Item = '#form.Item#'
  </cfquery>
  <cfif ItemGet.recordCount>
    The item already exists in the database;
    no action was taken.
  <cfelse>
    <cfquery name="ItemAdd" datasource="context">
    insert into ItemPrice
    (Item, Price, Current, AsOf)
    values
   ('#form.Item#', #form.Price#, 1, #createODBCDateTime(now())#)
    </cfquery>
    <cfoutput>#form.Item#</cfoutput>
    has been sent to the database
  </cfif>
</cfif>

Update

Change.cfm sends edit data to list.cfm for processing, which includes this code: update.cfm. The response is not a simple update. If the user has submitted an edit, start by checking the item name. If the item name has changed, change it globally. Then, set Current in the current row for this item to zero. Finally, insert a new row instead of actually performing an update. Include the ODBC version of the current time. 

<cfparam name="Go" default="">
<cfif Go is "Edit">
  <!--- Globally change item name if different --->
  <cfif form.Item is not form.OldItem>
    <cfquery name="NameChange" datasource="context">
    update ItemPrice set
    Item = '#form.Item#'
    where Item = '#form.OldItem#'
    </cfquery>
  </cfif>
  <!--- Globally mark item not current --->
  <cfquery name="ItemAge" datasource="context">
  update ItemPrice set
  Current = 0
  where Item = '#form.Item#'
  and Current = 1
  </cfquery>
  <!--- Add new row for item and mark it current --->
  <cfquery name="ItemReplace" datasource="context">
  insert into ItemPrice
  (Item, Price, Current, AsOf)
  values
  ('#form.Item#', #form.Price#, 1, #createODBCDateTime(now())#)
  </cfquery>
</cfif>

Travel in Time

Browse list.cfm. Add rows, edit some, and delete some. Notice as you mark your items up and down that you really can travel back in time (or at least look back). =Marty=