Develop For The Future

Let’s say we start a specialized e-commerce site that sells fishing lures that are dropped shipped from Henry’s, a supplier. We found a good deal with Henry’s and we’re so excited to start generating revenue and quite our day jobs a pencil pushers. To get this site up and running we create some database tables:

  • HenrysProducts
    • SKU
    • Brand
    • Model
    • Price
  • Orders
    • OrderID
    • Name
    • Address
    • ShippingPrice
    • Tax
    • OrderTotal
    • TrackingNumber
    • ShippingCarrier
    • DateShipped
  • OrderItems
    • OrderID
    • SKU
    • Quantity
    • Price

You slap up a few files:

  • index.cfm
  • products.cfm
  • cart.cfm
  • shippingInfo.cfm
  • paymentInfo.cfm
  • orderConfirmation.cfm
  • contactUs.cfm

Put in some images and “bam” you’re rolling. The lures you’re selling are “hot stuff” and you start bringing in revenue. Yea!

Oh! What’s this? An order for $500 worth of lures? Yes!

The order goes through, but Henry’s says “Sorry, we don’t have 100 of the 500 lures you ordered. We’ll place those on back-order for you.”. You think, “Okay, no problem.”. But wait! How are we going to handle that? Time for a new table and some logic to handle emails when things are backordered, cancelled and shipped:

  • HenrysOrderFulfilment
    • OrderID
    • SKU
    • QuantityShipped
    • QuantityBackOrdered
    • QuantityCancelled
    • ShipmentID
  • HenrysShipments
    • ShipmentID
    • TrackingNumber
    • DateShipped
    • Carrier

Now that we’ve started getting popular, people really want some items that are only available from the manufacturer. But now we have everything setup for Henry’s, not thinking ahead for the future. So we have a few options:

  1. Keep using what we have and append a column to tell us where it’s really coming from
  2. Create new tables for the new manufacturer
  3. Refactor the tables to be more generic

And of course, we should opt for #3. This should have been where we started in the first place. You should never assume your business model will never change, your contract might never expire from your supplier or that your business will never grow. Because with that kind of thinking, it becomes much more costly to grow your business having to deal with the side affects of your starting decisions. That growing time also usually involves lack of time because you become so busy with the messy system. Here’s an example of how it should have been built in the first place.

  • Products
    • ProductID
    • Brand
    • Title
  • ProductChildren
    • ProductChildID
    • ProductID
    • ModelTitle
    • SKU
    • UPC
    • Price
    • Weight
  • ProductSupplierLookup
    • ProductChildID
    • SupplierID
    • Cost
    • Priority
  • Suppliers
    • SupplierID
    • Name
  • Orders
    • OrderID
    • Name
    • Address
    • ShippingPrice
    • Tax
    • OrderTotal
    • TrackingNumber
    • ShippingCarrier
    • DateShipped
  • OrderItems
    • OrderItemID
    • OrderID
    • SKU
    • Quantity
    • Price
  • OrderFulfilment
    • OrderItemID
    • SupplierID
    • ItemCost
    • QuantityOrdered
    • QuantityShipped
    • QuantityBackOrdered
    • QuantityCancelled
    • ShipmentID
  • Shipments
    • ShipmentID
    • TrackingNumber
    • DateShipped
    • Carrier
    • Method

With this type of structure you can add multiple supplier, order from multiple suppliers for each customer order using a priority number, handle back-orders and cancellations, use the most cost efficient supplier, change, add and remove suppliers and keep track of multiple packages shipped.

Of course this is a fairly basic structure, but it’s a good way of thinking forward whether you’re starting from scratch or enhancing your system.

That touches the SQL aspect of it. However if you re-factored your code using the above file structure, I’m betting you’re code is complex and messy. You may have even coded it a year ago and forgot where everything was. You may even have a different developer or developers working on the enhancement.

This is why you should always split out your view code into .CSS, .JS and template files to generate HTML. You should also be using CFC’s, if using ColdFusion, and a FrameWork is a must. You now have a much more manageable file structure that you can manipulate your code with much more ease. Also don’t forget your unit testing so you reduce your chances of accidentally breaking something. I suggest using something like FW/1 as it’s lightweight and easy, one you get the hang of it.

Hacking ColdFusion 9 Application Scopes

You can find many hidden jems in the coldfusion.runtime.ApplicationScopeTracker Java class.

appTracker = createObject( 'java', 'coldfusion.runtime.ApplicationScopeTracker' );
writeDump( var = appTracker );

List all application scope names:

applications = appTracker.getApplicationKeys();
while( applications.hasNext() ) {
writeOutput( applications.next() & '<br>' );
}

Dump a specific application scope:

appScope = appTracker.getApplicationScope( 'myAppName');
writeDump( appScope );

“Element GENERATEDKEY is undefined…” after ColdFusion SQL Statement

This is a followup post to “Returning IDENTITY ID via ColdFusion 9′s CFQuery“.

Today I created an Insert statement that I needed the new identity value returned. It’s pretty simple, calling the result.generatedkey as such:

<cfquery result="myResult">
    INSERT INTO users( username )
    VALUES( 'test' )
</cfquery>
<cfreturn myResult["GENERATEDKEY"]>

The above code would normally result in the new identity value created for that record. However I received a “GENERATEDKEY is undefined…” error. Not sure what to think I dumped out the myResult variable, put the query into a file of itself and even tried using myResult.IDENTITY instead to no avail. After taking awhile figuring this out and ended up going home for the day and trying there again after a couple of hours. All of a sudden the standalone test worked!

So I’m thinking, okay this has to be some setting somewhere, and I was right. I narrowed it down to a query that runs before it during the login process for my website.

SET NOCOUNT ON

UPDATE TABLE1
SET COL1 = 'myVal'

Notice that I didn’t set “SET NOCOUNT OFF”. There lies the issue. Apparently when you set NOCOUNT off and keep it off, ColdFusion doesn’t retrieve the new Identity value. So the following code fixed my issue:

SET NOCOUNT ON

UPDATE TABLE1
SET COL1 = 'myVal'

SET NOCOUNT OFF

#cfquery, #coldfusion-2, #generatedkey, #identity, #nocount, #sql

ColdFusion CFQuery Zero Records and the “Element x is undefined in x” Error

When you run a CFQuery tag in ColdFusion you “will always” get a query object returned whether or not any results where found. But recently, after running a complicated query in ColdFusion 9.0.1, I found this to not be true. When there where results, everything worked as expected. However, when there where no results then I received a “Element x is undefined in x.” error when calling the CFQuery’s name variable. Here’s a simplified example of my code:

<cfquery name="local.products">
SELECT P.product
FROM Products P
JOIN (
    SELECT PC.productID, MIN( PC.price ) )
    FROM ProductsChildren PC
    GROUP BY PC.productID
) price ON P.productID = price.productID
</cfquery>
<cfdump var="local.products">

After banging my head and doing some research I ran through this thread that helped me resolve the issue: http://forums.adobe.com/message/2595679 . They mention a couple of bug ID’s, but Adobe updated their bug-base and I can’t find them. So at some point I’ll create a new ticket for this issue.

The problem lies in the fact that my SQL was generating a warning message inside the joined sub-query and CF wasn’t handling that very well. One fix is to fix why this message is being generated in the first place by adding a ISNULL() or COALESCE() around the column the MIN() method is reading. For example:

<cfquery name="local.products">
SELECT P.product
FROM Products P
JOIN (
    SELECT PC.productID, MIN( COALESCE( PC.price, 0 ) ) )
    FROM ProductsChildren PC
    GROUP BY PC.productID
) price ON P.productID = price.productID
</cfquery>
<cfdump var="local.products">

The second method would be to add “SET ANSI_WARNINGS OFF;” to the beginning of the SQL statement. For example:

<cfquery name="local.products">
SET ANSI_WARNINGS OFF;

SELECT P.product
FROM Products P
JOIN (
    SELECT PC.productID, MIN( PC.price, 0 ) )
    FROM ProductsChildren PC
    GROUP BY PC.productID
) price ON P.productID = price.productID
</cfquery>
<cfdump var="local.products">

The first fix is probably the best method to choose, however both of these resolved my issue.

There are other workarounds such as using the JDBC driver, but this approach seems to be the most practical.

#coldfusion-2, #join, #query, #sql, #sub-query, #undefined

Handling Expired Sessions via AJAX & FW/1

This is a followup to my “Framework One AJAX Method (FW/1)” post (https://christierney.com/2012/07/14/framework-one-ajax-method-fw1/).

Scenario:

  1. You use the session scope to define if a user is logged in or not
  2. You use jQuery AJAX to pull JSON data from FW/1 action URL’s
  3. The user’s session has expired after x minutes of inactivity after login
  4. If the session is expired the user is directed to a login page after trying to navigate

So what happens in this scenario? Instead of the expected JSON data your AJAX call receives the HTML of a login page with a status of 200. Can’t do too much with this.

Here’s a code example that will pass the client a 403 error (Forbidden) in the header and return no content. jQuery will then redirect the user to a login screen when it sees this status code.

First here’s a simlified FW/1 Application.cfc setupRequest() method:

void function setupRequest() {

	var reqData = getHTTPRequestData();

	if( structKeyExists( reqData.headers, 'X-Requested-With' ) && reqData.headers[ 'X-Requested-With' ] == 'XMLHttpRequest' && !structKeyExists( session, 'user' ) ) {
		getpagecontext().getresponse().setstatus( 403 );
		abort;
	}
}

This code detects if the call came from an AJAX request ( getHTTPRequestData().headers.X-Requested-With = ‘XMLHttpRequest’ ) and if the session still knows about the user. If it is an AJAX request and the user is not known, then set the status code of the return page to 403 and stop processing any more code. If you try to use throw instead of abort, it will overwrite the status code to 500.

The second simple example is the jQuery piece:

$( document ).ready( function() {

	$( this ).ajaxError( function( e, jqXHR, settings, exception ) {
		if( jqXHR.status == 403 ) {
			location.href = '?logout';
			throw new Error( 'Login Required' );
		} else if( !jqXHR.statusText == 'abort' && jqXHR.getAllResponseHeaders() ) {
			alert( 'There was an error processing your request.\nPlease try again or contact customer service.\nError: ' + jqXHR.statusText );
		}
	});

});

Here we are globally looking at all AJAX requests. Since the status code 403 is in the error class it will throw an error. The .ajaxError() method picks up this error and handles it.

If the status code is detected as a 403 (which we set in our ColdFusion code) then we direct the user to a logout page (which in turn directs to a login page) and throws a JS error. The throw statement is supposed to stop all JS processing, however if you have an error handler attached to the specific AJAX call, then that will still fire. The error message will just be seen if you are viewing the JS console.

If there’s another error caught it first looks to see if the request was aborted or if the user navigated away from the page. In these two cases I don’t want to display an error. If anything else is caught, I display a generic message.

#ajax, #coldfusion-2, #fw1, #jquery, #session

ColdFusion Implicit Struct Case Sensitivity For JSON

I’ve always learned that in ColdFusion 9+ that dot notation and implicit structs will always convert the key into uppercase when returning it as a JSON string.

To get around this you would always use bracket notation.

However I learned today, by example, that if you enclose the defined key, in an implicit struct, inside quotes that the key case will be preserved.

// DOES NOT PRESERVE CASE
// returns: {"MYKEY":"myValue"}
myStruct.myKey = "myValue";
return myStruct;

// returns: {"MYKEY":"myValue"}
myStruct = { myKey = 'myValue '};
return myStruct;

// PRESERVES CASE
// returns: {"myKey":"myValue"}
myStruct[ "myKey" ] = "myValue";
return myStruct;

// returns: {"myKey":"myValue"}
myStruct = { 'myKey' = 'myValue '};
return myStruct;

#case, #coldfusion-2, #json

Application.cfc onRequestStart() Called Twice

I spent a bit of time tracking down why I kept seeing two exact same threads (run by thread()) being run simultaneously. These threads are spawned by FrameWork One’s (FW/1) setupRequest() method. After tracing the methods with some console dumps, doing some research via Google and process of elimination, I finally got a clue.

Let’s say I hit http://mysite.com and this site requires a login. If you are not logged in lets saw we use a location() call to get to the login page. I also use a log-in imitator via a UI to see how different account look under their login, which also uses the location() method. Because of these location() calls, the onRequestStart method is called once before the redirect and once after the redirect.

Now, thinking I can overcome this by wrapping the thread() call in if( !isDefined( “myThreadName” ) ). However – apparently the onRequestStart calls come so quick that the thread name isn’t created in time to be detected. So two threads are still spawned.

I could probably add a flag to the session scope wrapped in a lock operator. However what happens if the thread is terminated manually? This may not be the best option, but it’s not bad as long as your site admins are aware of that consequence.

In this case I’m going to reassign my statements to my log-in handler instead of my setupRequest() method and call it good.

Let me know if you have any other ideas on how to handle this. I’m all ears.

NECFUG FW/1 Presentation

In July 2012, I presented to the Nebraska ColdFusion User’s Group on the topic of FrameWork 1 (FW/1) for ColdFusion. Thanks to Ryan Stille for recording this.

Also be sure to check out “A crash course in MVC with FW/1” with Steven Neiland as this provides a much better method for presenting this topic.

Continue reading →

Framework One AJAX Method (FW/1)

NOTE: This is now obsolete. Use the method renderData() instead.

I really haven’t found a method I like that returns JSON via FW/1 until I saw a presentation by Steven Neiland the other day. Basically you have a few choices:

  1. Call a service directly: This leaves you w/o any FW/1 features and the service is not initialized.
  2. Create views that return JSON: This can become tedious and seems unnecessary, but FW/1 features are available
  3. Have onMissingView handle it: Views do not need to be created and FW/1 features are available

My approach uses #3.

Example jQuery AJAX call:

$.ajax({
	url: 'index.cfm?action=products.attributesAJAX',
	type: 'GET',
	dataType: 'json',
	data: {parentPartNumber: $('#productForm').data( 'parentpartnumber' )}
});

You’ll notice it requests the section “products” and the action “attributesAJAX”. We will not create the view for the action, but we will create the controller method for it.

component {

	function init( FW ) {
		variables.FW = FW;
		return this;
	}

	// AJAX
	void function attributesAJAX( required struct RC ) {
		FW.service( 'productService.getProductAttributes', 'data', { parentPartNumber = RC.parentPartNumber } );
	}

}

To return JSON data w/o creating a view, we let the Application.cfc’s onMissingView method handle the return:

function onMissingView( required struct RC) {
	// if a data key exists, assume this is for AJAX and render as JSON
	if ( structKeyExists( RC, "data" ) ) {
		request.layout = false; // turn off default layout
		new services.utility().showDebugOutput( false );
		getPageContext().getResponse().getResponse().setContentType('application/json');
		return serializeJSON( RC.data ); // convert data to JSON
	} else {
		return view( 'main/pageNotFound' ); // set view to the missing page message
	}
}

You’ll notice that if the view’s missing it looks for the RC.data value. If it exists it assumes you want to return JSON back. Otherwise it will call the “pageNotFound” view (which you need to create). Note how the layout is turned off, it calls a method to turn off debugging (for development), and sets the content type to JSON.

This is the utility I created to turn off debugging:

<cfcomponent output="false">

	<cffunction name="showDebugOutput" output="false" returntype="void">
		<cfargument name="switch" default="false" type="boolean">
		<cfsetting showdebugoutput="#arguments.switch#">
	</cffunction>

</cfcomponent>

Returning Distinct ColdFusion Entities

Take this code example:

return ORMExecuteQuery(
	'FROM user U
	JOIN U.stateLicenses
	WHERE U.company.id IN (:companyIDs)'
	,
	{
		companyIDs = arguments.companyIDs
	},
	false,
	{ ignorecase = true }
);

Each user can have multiple state licenses, thus what we get back here is an array of array of entities that have duplicated users.

Lets take a couple of steps to fix this.

1. Change “JOIN U.stateLicenses” to “JOIN FETCH U.stateLicenses”. This will give us an array to work with instead of an array of an array. It also brings back the licenses right away. I didn’t investigate why this is though.
2. Add “SELECT DISTINCT U” to the beginning of the HQL. This will return distinct user records with an array of licenses. This fixes our duplicate users situation.

The modified working code is:

return ORMExecuteQuery(
	'SELECT DISTINCT U
	FROM user U
	JOIN FETCH U.stateLicenses
	WHERE U.company.id IN (:companyIDs)'
	,
	{
		companyIDs = arguments.companyIDs
	},
	false,
	{ ignorecase = true }
);

#hql