Nando @ Aria Media

( learning Clojure ColdFusion Lucee Javascript jQuery Angular CSS Linux Apache HTML5 & etc )

Cfqueryparam Setting for SQL ‘in’ Statements

| Comments

When using an SQL IN statement within a ColdFusion query, it is necessary to add the attribute list="true" to the param used for the list. See the example below that selects all employees that have a birthdate in March, April or May.

1
2
3
4
5
6
7
8
9
10
11
public query function findUpcomingBirthdates() {
  var params = {};
  var ml = "3,4,5";
  params.insert( "monthList", { value: ml, cfsqltype: "cf_sql_integer", list: true } );
  var sql = "
      select employeeId, firstName, lastName, birthdate
      from Employee
      where month( birthdate ) in ( :monthList )
  ";
  return queryExecute( sql, params );
}

Thanks to Ben Nadel’s post here for this crucial tidbit of information!

Hot Browser Reloading With Browsersync

| Comments

I’ve fallen down the rabbit hole of front end development in the last few weeks, and in that time Flexbox CSS, Bootstrap v4, Foundation, Sass / SCSS, and Gulp have all whizzed by as I’ve taught myself portions of each, and today I landed at the bottom, Browsersync.

In a nutshell, Browsersync will live reload one or more browsers for you whenever your project’s files change. It will also live reload external devices pointed at the same web application. This allows you to simultaneously and efficiently test a responsive design in multiple browsers, phones and tablets - without needing to touch or reload any of them. It also synchronizes form entries and clicks across browsers and devices. For example, with my phone and tablet connected to Browsersync, I log into the app I’m developing on the tablet, and my keystrokes and touch gestures are replicated on the phone, and I’m logged in on both. Then I start navigating in the application in Chrome on my desktop, and the phone, tablet and other browsers follow as “slaves”. Interacting with any interface causes the rest of them to follow. What an incredible tool! In the few minutes I’ve been experimenting with it, I was able to easily and quickly fix a layout issue on the tablet and phone for the login screen.

Browsersync works by wrapping your application or website in its own Node server process, on another port. You can start Browsersync on the command line, or via a Grunt or Gulp task. During startup, you specify the local url of your app, the files you want it to watch - a number of other options are possible - and then a browser window opens, on port 3000 by default, and browsersync is activated for that browser. To add others, you simply use the same url. To add external devices, on localhost:3001 there is an admin panel for Browsersync that will list the external url to use. Navigate to that external url on your phone or tablet or another computer, for me it currently http://192.168.1.103:3000/, and Browsersync is set up on these devices as well. Once you have it configured, it is really simple to use.

A quick overview on installation and configuration: Basic instructions are on the homepage https://www.browsersync.io/.

  1. Install or upgrade Node.js
  2. Install Browsersync using npm
1
npm install -g browser-sync

The -g flag means global so it is available from anywhere.

  1. Start Browsersync

There are 2 start modes, –server and –proxy. server is for static files, proxy is for applications running on a server. Here’s a simplified example starting Browsersync from the command line:

1
browser-sync start --proxy "myproject.dev" --files "css/*.css"

… assuming you have your local host file set up to point your app at myproject.dev. Or you can also start Browsersync using a localhost:port url, like this:

1
browser-sync start --proxy "localhost:8500/myproject" --files "css/*.css"

There are a few real world ways to do this, but what I’ve done is to create a gulpfile.js in the root of my project so I can configure Browsersync conveniently and start it with a simple gulp command after cd’ing in to my project directory. That way I don’t have to remember much. Here’s my first working gulpfile.js for a real development scenario. I have ACF (Adobe ColdFusion) installed on port 8501 on my dev laptop, and gulp.watch() is configured for an FW/1 app. I’m not sure if I actually want to watch the controllers and services directories, perhaps the views directory and CSS directory is enough. But for now I will leave it like this and see how it goes.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
var gulp = require('gulp');
var browserSync = require('browser-sync').create();
var reload = browserSync.reload;

gulp.task('browser-sync', function() {
  browserSync.init({
      proxy: "localhost:8501/myproject"
  });

  gulp.watch([
      'controllers/*.cfc', 'services/*.cfc', 'views/**/*.cfm', 'dist/*.css'
  ]).on("change", function() {
      console.log("Watch hit");
      browserSync.reload();
  });
});

gulp.task('default', ['browser-sync']);

Note carefully the syntax of gulp.watch() for multiple directories / files. It’s an array of strings separated by commas. Note also that the root of the relative paths specified is where the gulpfile.js is located.

But wait, wouldn’t it be better if I could use Browsersync to develop against both ACF and Lucee? Here’s a solution that I have working that creates named instances of Browsersync so they don’t step on each other:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
var gulp = require('gulp');
var bsacf = require('browser-sync').create('browserSyncACF');
var bslucee = require('browser-sync').create('browserSyncLucee');

gulp.task('browser-sync', function() {
  bsacf.init({
      proxy: "localhost:8501/myproject",
      browser: "google chrome",
      reloadOnRestart: true
  });

  bslucee.init({
      proxy: "localhost:8888/myproject",
      browser: "vivaldi",
      port: 3010,
      reloadOnRestart: true,
      ui: {
          port: 3011
      }
  });

  gulp.watch([
      'controllers/*.cfc', 'services/*.cfc', 'views/**/*.cfm', 'dist/*.css'
  ]).on("change", function() {
      console.log("Watch hit");
      bsacf.reload();
      bslucee.reload();
  });
});

gulp.task('default', ['browser-sync']);

I specify separate browsers for each so that ACF and Lucee sessions don’t step on one another, and separate ports for the Lucee instance of Browsersync so they can coexist. The advantage to doing it this way is I can start both instances with a single gulp command on the command line. Unfortunately the ACF and Lucee instances of Browsersync will not mirror or ghost each other, so I will still have to click and enter test data on forms separately while developing, but this is still much better than needing to reload both browsers manually all the time.

Update:

I’ve noticed one other feature of Browsersync that I wanted to see if I could get working - injecting modified CSS into the browser to avoid reloading the DOM. I’ve now rearranged my gulpfile.js to be the following, adding a second gulp.watch() call to inject the minified bootstrap css directly into the browser, and removing the dist directory from the first gulp.watch() call so the browsers are not reloaded.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
var gulp = require('gulp');
var bsacf = require('browser-sync').create('browserSyncACF');
var bslucee = require('browser-sync').create('browserSyncLucee');

gulp.task('browser-sync', function() {
  bsacf.init({
      proxy: "localhost:8501/easycal",
      browser: "google chrome",
      reloadOnRestart: true
  });

  bslucee.init({
      proxy: "localhost:8888/easycal",
      browser: "vivaldi",
      port: 3010,
      reloadOnRestart: true,
      ui: {
          port: 3011
      }
  });

  gulp.watch([
      'controllers/*.cfc', 'services/*.cfc', 'views/**/*.cfm',
      'layouts/**/*.cfm', 'dist/*.css', 'easycal-app.css'
  ]).on("change", function() {
      console.log("Watch hit");
      bsacf.reload();
      bslucee.reload();
  });

  gulp.watch([
      'dist/toolkit.min.css'
  ]).on("change", function() {
      console.log("CSS hit");
      return gulp.src(['dist/toolkit.min.css'])
          .pipe(bsacf.stream())
          .pipe(bslucee.stream());
  });
});

gulp.task('default', ['browser-sync']);

I like how this is shaping up now. :-)

Comments or suggestions welcome!

Scale Attribute Is Essential With Decimal Cfsqltype

| Comments

I just ran across something important to know regarding the cfsqltype cf_sql_decimal. Here’s the code I was testing:

1
2
params.insert( 'previousNumericDuration',
  { value: preNDuration, cfsqltype: 'cf_sql_decimal' } );

Here’s what it would look like if you are using the cfqueryparam tag:

1
<cfqueryparam value='#preNDuration#' cfsqltype='cf_sql_decimal' />

The database column this is persisted to is defined as a decimal(4,2), so 2 digits are reserved for the whole number portion of the value and 2 digits reserved for the decimal portion of the value. Logging showed values being calculated such as 4.25 and 4.5, but these were being persisted to the database rounded to the nearest integer, but with 2 decimal places none the less, so 4.25 was persisted as 4.00 and 4.5 was persisted as 5.00. Definitely not what I wanted. What’s the point of a decimal datatype if the decimal portion is always 0?

Digging into the documentation for cfqueryparam I found a scale attribute that until today I hadn’t realized was absolutely essential to use for decimal fields like this. Scale defines the … “Number of decimal places in parameter. Applies to CF_SQL_NUMERIC and CF_SQL_DECIMAL.” Its default is 0, so my values were being rounded to 0 decimal places. After changing my code to the following:

1
2
params.insert( 'previousNumericDuration',
  { value: preNDuration, cfsqltype: 'cf_sql_decimal', scale: 2 } );

the decimal portion of the values was then persisted correctly.

So the scale attribute is absolutely essential for a decimal cfsqltype. I might have tripped over this before, I’m not sure. Perhaps this blog post will help me remember it next time. Now I should search my code to see if I’ve neglected this attribute elsewhere!

First Steps With the Clojure Version of FW/1

| Comments

Sean Corfield has built a Clojure version of the popular CFML framework FW/1. I’ve been using FW/1 for a number of years, so I thought it might help me learn Clojure to begin developing an app in fw1-clj.

The first step to get up and running is to head on over to the fw1-clj GitHub repository and follow the directions to install Boot, which is a build tool for Clojure. Next, as per the instructions on the fw1-clj GitHub repository, we run a boot command in a terminal to create a new fw1-clj app, which currently looks like this:

1
boot -d seancorfield/boot-new new -t fw1 -n myfw1app

(Sean has indicated this invocation may change in the future, so best to follow the fw1-clj repository instructions.)

Then, we “cd” into the application directory we specified above, in this example “myfw1app”, and start the application up like so:

1
2
cd myfw1app
PORT=8111 boot run

Specifying the PORT variable isn’t required, but may be necessary to avoid conflicts. If omitted, the default port is 8080.

Navigating to localhost:8111, we see a lovely default page that says “Framework One - Welcome to FW/1”. Yay! It certainly is impressive how easy that is. No server to install. Nothing to download. No settings to find and configure. We’re ready to start developing.

There are several options to choose from in terms of an IDE for Clojure. Many experienced Clojure developers use Emacs with CIDER, or the Cursive plugin for Intellij. Both of those have a learning curve that you may prefer to avoid to focus on getting the basics of Clojure down first.

For beginners, good IDE’s for experimentation are Light Table or Atom with a few Clojure packages installed, such as language-clojure, proto-repl and parinfer. I’m using Atom at the moment because I’m also using it for CFML development, and also because the Clojure packages seem to work very well.

One of the first things I wanted to figure out was how to visualize the rc map (or struct as it would be known in CFML). With a tip from Sean, I navigated to src/projectName/controllers/main.clj in the project that boot generated for me, and within the default function definition, I added (println rc)

1
2
3
(defn default "/main/default handler" [rc]
  (println rc)
  rc)

reloaded the app in the browser, and then checked the terminal window where I had started the app with the boot command for the output. Nothing was output there. After a moment, I remembered that I probably had to reload the application to get the changes picked up. Checking the somewhat sparse documentation on the Github page, I found the default reload query string would be “?reload=secret”. Hitting localhost:8111?reload=secret in the browser then generated the rc map in my terminal window - all in one very long line. After another suggestion from Sean, I replaced the print line function (println rc) with the clojure pretty print function (clojure.pprint/pprint rc)

1
2
3
(defn default "/main/default handler" [rc]
  (clojure.pprint/pprint rc)
  rc)

hit localhost:8111?reload=secret again, and now the keys within the rc map were nicely separated by line breaks in the terminal output. Much more readable that way.

I noticed within the output that the :reload-application-on-every-request key was set to false. Better in development to have that set to true, so reading the docs, I found “the call to (fw1/start) can be passed configuration parameters either as a map or as an arbitrary number of inline key / value pairs”

Ok, so opening src/projectName/main.clj, I found the call to (fw1/start) and changed it from

1
2
3
4
(defn -main []
  (let [port (Integer/parseInt (get (System/getenv) "PORT" "8080"))]
    (run-jetty (fw1/start :application-key "easycalclj")
               {:port port})))

to

1
2
3
4
(defn -main []
  (let [port (Integer/parseInt (get (System/getenv) "PORT" "8080"))]
    (run-jetty (fw1/start :application-key "easycalclj" :reload-application-on-every-request "true")
               {:port port})))

Then I reloaded the app with “?reload=secret”, and the rc output for :reload-application-on-every-request didn’t change. Hmmm …

After a bit of investigation, (I asked Sean again on Slack), I found that to reload changes to the configuration params, I needed to go to the terminal window where I started the app with boot run, shut the app down with control-c, and then start it up again with PORT=8111 boot run.

Checking the rc output in the console again, I saw that :reload-application-on-every-request was now true!

These were all small baby steps, but unless I take them, I won’t learn.

Generating Accurate PDFs Using Cfdocument

| Comments

I’ve been developing a PDF, generated within a CFML application, that needs very accurate placement of chunks of text so that it can be printed on a standard form. It also needs to use a specified font, OCR-B, so that a line of text on the printed form can be machine scanned. I’ve managed to get this working much better than expected on both ACF and Lucee, in a cross-compatible way, so I thought I’d write everything up to remember the details down the line.

I was at first thinking I might be able to use the new cfhtmltopdf tag, but quickly dropped that idea: I couldn’t get the PDF Service needed to use this tag to work on my Mac; it seems to be an enterprise only feature; and I wasn’t so sure this approach would be compatible to use within Lucee. After looking around over the fence for a bit at potential solutions outside of CFML, nothing hit me as particularly appealing, so I dug into getting cfdocument to work as best I could. First the tag attributes.

Attributes

1
<cfdocument format="pdf" pagetype="A4" orientation="portrait" fontEmbed="true" unit="cm" localUrl="true" saveAsName="#pdfFileName#" marginLeft="0" margintop="0" marginright="0" marginbottom="0">

FontEmbed=“true” is essential so that anyone can print the PDF with the OCR-B code line correctly displayed, even if they don’t have this font on their system. LocalUrl is set to true to easily pull in a logo image from the local file system. Note the margins are set to 0, because I’m using a <div> tag to define the size of the page to control the tendency of the 2 different PDF generation engines used in ACF and Lucee to scale the layout, differently.

Layout

Nested directly within the cfdocument tag is a div tag that sets the page width, with position:relative so it remains within the page flow, and acts as the parent tag within which all layout divs that position text are nested and proportionally scaled against:

1
<div style="position:relative; top:0mm; left:0mm; width:210mm;">

Then within that parent div tag are nested the various div tags, absolutely positioned, containing the blocks of text and images that make up the PDF content. Here are a few examples to demonstrate:

1
2
3
4
5
6
7
8
9
10
11
<div class="address"  style="position:absolute; top:13mm; left:100mm; ">
  #biller.address1# · #biller.zipcode# #biller.city#
</div>
<!--- client address --->
<div class="text" style="position:absolute; top:45mm; left:130mm">
  #invoice.getClient().getName()#<br>
  <cfif len( trim( invoice.getClient().getAddress1() ) )>#invoice.getClient().getAddress1()#<br></cfif>
  <cfif len( trim( invoice.getClient().getAddress2() ) )>#invoice.getClient().getAddress2()#<br></cfif>
  <cfif len( trim( invoice.getClient().getAddress3() ) )>#invoice.getClient().getAddress3()#<br></cfif>
  #invoice.getClient().getCountryCode()#-#invoice.getClient().getZipCode()# #invoice.getClient().getCity()#
</div>

What I really like about this approach is that each of the text blocks winds up very close to the top and left dimensions specified, and the ACF and Lucee outputs are nearly identical.

Without the parent div tag specifying the width, the results between the 2 engines are vastly different, and positioning the elements is much more a question of trial and error than simply entering the top and left positions as measured with a ruler (and perhaps tweaking them by a few milimeters if necessary). Also without the parent div tag to control how layout elements scale, changing the dimension of one absolutely positioned div within the PDF can easily alter the position or size of other divs, which can be very frustrating if you have 20 or 30 elements that all need to be precisely positioned.

Feel free to experiment with paddings or margins on the parent div if you want - I suspect it would work just as well (but haven’t tried). For myself, I found it easier simply to measure placement from the edge of the page.

I haven’t tried this myself, yet, but I have it from a reliable source that a parent div tag in the layout specifying the page width, as above, will fix page break issues if a table extends over more than one page.

Fonts

Specifying a font for a block of text is simple. You can use a CSS style declaration within a style block that is nested within the cfdocument tag, or you can place the font specification directly in a style attribute of an html tag like `

. Here are the styles I used for the above text blocks (and again, the style block must be nested within the cfdocument tag, or the PDF generator won’t see it):

1
2
3
4
5
6
7
8
9
10
11
12
13
<cfdocument format="pdf" pagetype="A4" orientation="portrait" fontEmbed="true" unit="cm" localUrl="true" saveAsName="#pdfFileName#" marginLeft="0" margintop="0" marginright="0" marginbottom="0">
<style>
  div.text {
      font: 9pt Arial;
      line-height: 13pt;
  }

  div.address {
      font: 9pt Arial;
      color: #033B7D;
  }
  ...
</style>

As of this writing, available CSS attributes remain limited. There is a list of CSS attributes that work in the ACF documentation for cfdocument, reproduced here:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
The cfdocument tag supports the following CSS styles:

background
background-attachment
background-color
background-image
background-position
background-repeat
border
border-bottom
border-bottom-color
border-bottom-style (solid border only)
border-bottom-width
border-color
border-left
border-left-color
border-left-style (solid border only)
border-left-width
border-right
border-right-color
border-right-style (solid border only)
border-right-width
border-spacing
border-style (solid border only)
border-top
border-top-color
border-top-style (solid border only)
border-top-width
border-width
bottom
clear
clip
color
content (strings, counters only)
counter-increment
counter-reset
cursor
display
float
font
font-family
font-size
font-style
font-weight
height
left
letter-spacing
line-height
list-style-type
margin
margin-bottom
margin-left
margin-right
margin-top
outline
outline-color
outline-style (solid, dotted, dashed only)
outline-width
padding
padding-bottom
padding-left
padding-right
padding-top
page-break-after
page-break-before
page-break-inside
position
right
text-align (left, right, and center)
text-decoration
text-indent
top
unicode-bidi
vertical-align
visibility
white space (normal, nowrap only)
width
z-index

Custom fonts

Scattered around the internet I found a variety of comments suggesting that getting a “custom” font to work within cfdocument isn’t at all easy. Once you know how, it’s actually fairly simple. For both ACF and Lucee, it’s a question of getting the correct font file in the right place, with the right permissions, and restarting ACF/Lucee.

For ACF, log into the administrator and go to the Font Management Panel. There you will see all the fonts available to use in cfdocument … except that as of this writing, the OTF fonts won’t work, even tho’ the Font Management panel claims they are useable in PDFs. Unless something changes in the future, forget OTF fonts for cfdocument. I tried a bunch that already seemed to be recognized, none worked.

At the top of the font panel, you’ll see a feature that allows you to add fonts “Register New Font(s) with ColdFusion”. I couldn’t get this to work reliably - I think the validation routine on it isn’t well designed. Go ahead and try if you like, but I found it easier to simply find a path to a font directory already recognized by ACF from the list of fonts installed ( I chose a directory with TTF files, just to be sure ), copy the TTF font files you want to use to that directory, change the owner/group and permissions to match the other font files in that directory (if necessary on your OS), restart ACF, log back into the administrator, go to the Font Management panel again and look for your fonts. In the left columns of the font table there are 3 names, Font Family, Font Face, Postscript Name. From my experience, using the font face name in your CSS specification should work. ( If not, try the other names - I saw a post suggesting that. )

To be clear, after you get the font installed in a directory that ACF recognizes as a font directory, one way or another, you’ll need to find the name you need to use in your CSS. The file name often does not match the font name.

For Lucee, the process is different. ;-) Go to your Lucee installation directory, and within the /lib/ directory underneath it, find the fonts.jar file. Copy it to a working directory, rename the copy to fonts.zip and unzip it. ( A .jar file is essentially a ZIP file ). In the unzipped fonts directory that results, add your custom font files, again use only TTFs, and then open the pd4fonts.properties file you find in there and following the pattern you see, add the names of the fonts. In Lucee, you can choose the name, but to be cross compatible with ACF, I used the same name that ACF picked up from the font file. Here’s what my pd4fonts.properties looked like after I added the OCR-B font I needed:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#this is an autogenerated file. please remove manually any references to copyrighted fonts
#Fri Apr 17 20:00:52 CEST 2009
Arial=arial.ttf
Arial\ Bold=arialbd.ttf
Arial\ Bold\ Italic=arialbi.ttf
Arial\ Italic=ariali.ttf
Courier\ New=cour.ttf
Courier\ New\ Bold=courbd.ttf
Courier\ New\ Bold\ Italic=courbi.ttf
Courier\ New\ Italic=couri.ttf
Times\ New\ Roman=times.ttf
Times\ New\ Roman\ Bold=timesbd.ttf
Times\ New\ Roman\ Bold\ Italic=timesbi.ttf
Times\ New\ Roman\ Italic=timesi.ttf
OCRB=ob______.ttf

I added the last line, OCRB=ob______.ttf, following the pattern FontFaceName=fontFileName.ttf.

Note that spaces in the font name are escaped with backslashes, so those look like this Font\ Face\ Name=fontFileName.ttf.

Ok, save pd4fonts.properties when you’re done, zip the fonts directory up into fonts.zip, rename it to fonts.jar, copy it back where it came from in the /lib/ directory, overwriting … no wait! Don’t just overwrite the old fonts.jar. Keep a copy of it, just in case … replace the old fonts.jar with the new fonts.jar, change the owner/group permissions to match the old file, and then restart Lucee.

The name to use in your CSS for the font is the name you placed in the pd4fonts.properties file.

Of course, the above approach for Lucee ( 4.5 ) might certainly change in the future. But for now, it works like this.

A big thanks to Michael Hnat for pointing me in the right direction regarding Lucee with his very helpful blog post.

Images

There is a logo image at the top of this PDF that was scaling up when rendered in Lucee in a way that caused it to be misplaced. The top left position was correct, but it was about 50% too big, pixelated, and overran other text. After a bunch of reading that indicated modifying the image print size or resolution would not help, I tried adding a css style declaration to the image that specified a size in mm, and it worked! Thanks to faxi05 for the suggestion.

1
<img src="logos/imageName.png" style="width:78mm; height:14mm" />

… and it’s cross compatible with ACF!

CFML Queries in Script

| Comments

Recently, I needed to write a somewhat complex query with a conditional where clause. I resisted the urge to revert to the cfquery tag for this sort of thing, and came up with the following using queryExecute(), submitted as an example without explanation. This approach works on both ACF and Lucee.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
public query function findRawAudit( string entity, numeric userId, date dateFrom, date dateTo, numeric timeZoneOffset ) {
  var newLine = CHR(13) & CHR(10);
  var params = {};
  var sql = "
      select a.entity, a.entityId, a.timestamp, a.transaction, a.attribute, a.value as previousValue, u.name as username, null as currentValue, null as auditName, null as asql
      from Audit a inner join User u
      on a.userId = u.userId
      where 0=0
  ";

  if ( structKeyExists(arguments, 'entity') and len( arguments.entity ) ) {
      sql &= newLine & "and entity = :entity";
      structInsert(params, "entity", { value: arguments.entity, cfsqltype: "cf_sql_varchar" } );
  }

  if ( structKeyExists(arguments, 'userId') ) {
      sql &= newLine & "and a.userId = :userId";
      structInsert(params, "userId", { value: arguments.userId, cfsqltype: "cf_sql_integer" } );
  }

  if ( structKeyExists(arguments, 'dateFrom') ) {
      sql &= newLine & "and timestamp > :dateFrom";
      //  adjust dateFrom to account for time zone difference between server and user
      var dateFromUTC = DateAdd( 'h', -arguments.timeZoneOffset, arguments.dateFrom );
      structInsert(params, "dateFrom", { value: arguments.dateFrom, cfsqltype: "cf_sql_timestamp" } );
  }

  if ( structKeyExists(arguments, 'dateTo') ) {
      sql &= newLine & "and timestamp < :dateTo";
      //  adjust dateFrom to account for time zone difference between server and user
      var dateToUTC = DateAdd( 'h', -arguments.timeZoneOffset, arguments.dateTo );
      // we add a day to dateTo to arrive at an effectiveEndDateTime, namely midnight of the endDate
      // so that the endDate is inclusive, as users would very likely expect
      dateToUTC = dateAdd( 'd', 1, dateToUTC );
      // stdout.println( "dateToUTC : #dateToUTC #" );
      structInsert(params, "dateTo", { value: dateToUTC, cfsqltype: "cf_sql_timestamp" } );
  }

  sql &= newLine & "and a.attribute != 'version' ";

  sql &= newLine & "and a.attribute != 'lastEdited' ";

  sql &= newLine & "and a.attribute != 'userId' ";

  sql &= newLine & "order by a.timestamp desc, a.transaction";

  return queryExecute( sql, params );
}

Thanks to Igal Sapir for helping me work this out!

Debugging Technique CFML Development

| Comments

I learned a debugging techique from Sean Corfield recently that I think is well worth sharing more widely. It works just as well for Lucee, Railo or Adobe Coldfusion. Here’s what he reccommends:

For ease of debugging any problems that may occur, I strongly recommend you always keep a Terminal / Console window open containing a tail of the console output from your CFML server. If you’re using Lucee or a Railo installation based on Tomcat, you’ll want to find the catalina.out log file and tail that. If you’re using ColdFusion 11, even tho’ it is notionally based on Tomcat, it’s completely non-standard and you’ll want to tail cfusion/logs/coldfusion-out.log.

To “tail”, in this case, means to show the newly added contents of a log file in real time. To get this to work, I open Terminal on my Mac and issue the following commands:

… for my local CF11 install:

 tail -f /Applications/ColdFusion11/cfusion/logs/coldfusion-out.log

and for my local Lucee install:

  tail -f ~/tomcat/logs/catalina.out

You will of course need to change those paths to match the directory under which your applications are installed.

To get out of tail mode and back to the command line, hit CTRL-C.

What I see when I do this is the last 10 lines of the log file, to start with. Anytime an additional line is added to the log file, it immediately appears in the console display in real time.

Now what I can do in my code is send output to these log files. An example should explain it sufficiently. I just used this technique to debug a the following service function, where I wanted to ensure that dateTo UTC was being set as needed:

    public query function findRawAudit( string entity, numeric userId, date dateFrom, date dateTo, numeric timeZoneOffset ) {
        var stdout = createObject( "java", "java.lang.System" ).out;

        ...

        if ( structKeyExists(arguments, 'dateTo') ) {
            sql &= "#newLine#" & "and timestamp < :dateTo";
            //  adjust dateFrom to account for time zone difference between server and user
            var dateToUTC = DateAdd( 'h', -arguments.timeZoneOffset, arguments.dateTo );
            // we add a day to dateTo to arrive at an effectiveEndDateTime, namely midnight of the endDate
            // so that the endDate is inclusive, as users would very likely expect
            dateToUTC = dateAdd( 'd', 1, dateToUTC );
            stdout.println( "dateToUTC : #dateToUTC #" );
            structInsert(params, "dateTo", { value: dateToUTC, cfsqltype: "cf_sql_timestamp" } );
        }
        ...
    }

Note the lines var stdout = createObject( "java", "java.lang.System" ).out; and stdout.println( "dateToUTC : #dateToUTC #" ); which creates an instance of java.lang.System.out and prints a line to the log file.

Immediately upon running this code dateToUTC : {ts '2015-06-16 22:00:00'} appeared in my tail output, which is what I expected. The bug was elsewhere, . I find this very useful. It’s simple and effective, and it works in the same way across ACF, Lucee and Railo. I don’t have to fiddle around with an admin setting to turn on trace output, check if trace output is enabled, remember the syntax used for cftrace / trace, and I can use it directly on a production server in a pinch in case of need.

Of course, you can tail any log file necessary, or use WriteLog() if you prefer. WriteLog() does the exact same thing as stdout.println(), and it’s easier to remember. So taking the above example, I could also use

1
WriteLog( "dateToUTC : #dateToUTC #" );

or

1
WriteLog( "dateToUTC : "  & dateToUTC );

Nginx Tweaks Using Proxy_redirect and Rewrite Directives

| Comments

I ran across a few niggles in setting up ColdFusion and Lucee behind a Nginx web server, and thought it might be helpful to document the solutions I came to.

The first small problem was that when users logged out of an FW/1 app, they would encounter a blank screen rather than being redirected to the login screen. Further investigation, using Chrome’s Developer Tools Network panel, showed that Nginx was returning a blank location response header on the redirect to the login screen. I’m still not exactly clear why this is occuring - other 302 redirects within the app work as expected, but the solution I came up with worked like a charm. All I needed to do was use the proxy_redirect directive alongside the proxy_pass directive, redirecting a blank location to the root location, like so:

1
proxy_redirect '' /;

I was happy that it worked as I guessed it would. In any case, proxy_redirect is a handy directive to have in your toolkit.

In another case, a url pointing to a directory without a trailing slash would result in a 404 error behind Nginx. This url was being processed using the proxy_pass directive. For example, http://domain.com/directory would result in a 404 while http://domain.com/directory/ works, locating the default index.cfm template under /directory.

Since I had only one url to adjust, the solution was rather simple:

1
rewrite ^/directory$ /directory/ redirect;

There are several trailing slash issues using Nginx that need to be taken into account. Search for “nginx trailing slash” to get a handle on them.

UTF-8 Extended Character File Names in Railo / Lucee

| Comments

I ran into an issue when porting an application from ACF9 to Railo 4.2 where Railo could not find files that use extented characters in file names, namely letters with accents, umlauts and such. The request would error out with a template not found error. I briefly considered renaming a bunch of files that my clients had uploaded, building a system to filter out and rename all files with extended characters in them, but decided there has to be a better way. And indeed there is!

Specifically on a Linux or Mac OS, open setenv.sh, which should be located under /tomcat/bin/ and add the following line at the end of the file:

1
export LC_CTYPE="en_US.UTF-8"

Restart Railo, and you should be good to go!

The background here is that the default file encoding system used in tomcat isn’t utf-8. There should be Tomcat documentation available for a Windows install somewhere. Feel free to add a comment if you happen to know how to set the file system encoding on Windows.

Testing Nginx Config Directives

| Comments

After setting up both Lucee and ColdFusion web apps behind Nginx, I wanted to prove to myself that the configuration was functioning how I thought it should. The apps worked, but there were several issues I was concerned about.

Here’s the configuration I wanted to test:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
upstream    cf_servers {
  server          127.0.0.1:8500;
  keepalive       32; ## number of upstream connections to keep alive
}

server {
  server_name     domain.com;
  listen          80;
  root            /var/sites/domain-com;

  location ~* \.(?:ico|css|js|gif|jpe?g|png)$ {
      add_header Pragma public;
      add_header Cache-Control "public, must-revalidate, proxy-revalidate";
      gzip  on;
      gzip_http_version 1.0;
      gzip_vary on;
      gzip_comp_level 6;
      gzip_proxied any;
      gzip_types text/plain text/css application/json application/x-javascript text/xml application/xml application/xml+rss text/javascript;
      gzip_buffers 16 8k;
      # Disable gzip for certain browsers.
      gzip_disable ~@~\MSIE [1-6].(?!.*SV1)~@~];
      expires modified +90d;
  }

  location / {
      proxy_pass  http://cf_servers/domain.com/;
      proxy_redirect '' /;
      proxy_http_version  1.1;
      proxy_set_header    Connection "";
      proxy_set_header    Host                $host;
      proxy_set_header    X-Forwarded-Host    $host;
      proxy_set_header    X-Forwarded-Server  $host;
      proxy_set_header    X-Forwarded-For     $proxy_add_x_forwarded_for;     ## CGI.REMOTE_ADDR
      proxy_set_header    X-Forwarded-Proto   $scheme;                        ## CGI.SERVER_PORT_SECURE
      proxy_set_header    X-Real-IP           $remote_addr;
      expires             epoch;
  }
}

First off, I had a doubt regarding how Nginx was selecting which location directive to use for each file requested from the documentation I’ve read. What I wanted to ensure is that Nginx was serving the static files, and proxying only the CFML files.

A deeper issue here is whether or not the proxy_pass directive contains a URI. If it does, you can’t use a regex to filter a location for CFML files only. A few examples will explain this better:

This configuration contains a URI in the proxy_pass directive, namely /domain.com/. You cannot use a regex in the location directive to filter the request, so in my case, the location directive simply had to point to the root ( recursively in the way Nginx works, so essentially this directive will process any file under the root ).

1
2
3
location / {
  proxy_pass  http://cf_servers/domain.com/;
}

This configuration also contains a URI in the proxy_pass directive, the trailing slash. Again, no regex in the location is possible.

1
2
3
location / {
  proxy_pass  http://cf_servers/;
}

This config does not have a URI component in the proxy_pass directive, no trailing slash and no subdirectory are present here. In this case, we CAN apply a regex filter to pass only those files we want to our application server.

1
2
3
location ~ \.(cfm|cfc|cfml)$ {
  proxy_pass  http://cf_servers;
}

The tradeoff here is that if we use a filter to ensure only CFML files are pass to our app server, then we need to additionally configure each virtual host in Tomcat’s server.xml ( assuming multiple hosts per server ) so that Tomcat can locate the files.

If you’d rather configure each server only in Nginx, as I did with my CF11 install, then another approach might be to use a regex to filter all your static files. Hence:

1
2
3
location ~* \.(?:ico|css|js|gif|jpe?g|png)$ {
  ...
}

With the essential background information out of the way, the question that arose was this. Is Nginx actually serving the static files, as I hoped, or are they all being passed to ColdFusion? The documention I found here and there seemed contradictory.

Here’s one way to test this using curl on the command line to fetch the response headers, with the output I got using the above settings:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
$ curl -X GET -I http://domain.com/index.cfm
HTTP/1.1 200 OK
Server: nginx
Date: Thu, 08 Jan 2015 21:48:26 GMT
Content-Type: text/html;charset=UTF-8
Transfer-Encoding: chunked
Connection: keep-alive
Set-Cookie: CFID=20071; Expires=Sat, 31-Dec-2044 21:48:26 GMT; Path=/; HttpOnly
Set-Cookie: CFTOKEN=8ff3e8f3dde25bcd-F33324A1-AEBC-7E8D-FEEEB610DD6412E7; Expires=Sat, 31-Dec-2044 21:48:26 GMT; Path=/; HttpOnly
Cache-Control: no-cache
Pragma: no-cache
Expires: Thu, 01 Jan 1970 00:00:01 GMT
X-Frame-Options: SAMEORIGIN
X-Content-Type-Options: nosniff
X-XSS-Protection: 1; mode=block

$ curl -X GET -I http://domain.com/images/some_logo.gif
HTTP/1.1 200 OK
Server: nginx
Date: Thu, 08 Jan 2015 21:50:12 GMT
Content-Type: image/gif
Content-Length: 10220
Last-Modified: Thu, 11 Dec 2014 17:18:36 GMT
Connection: keep-alive
ETag: "5489d1ec-27ec"
Expires: Wed, 11 Mar 2015 17:18:36 GMT
Cache-Control: max-age=5340504
Pragma: public
Cache-Control: public, must-revalidate, proxy-revalidate
Accept-Ranges: bytes

See the difference? The image is being cached on the browser and the headers are being set as per the static file location block, while index.cfm isn’t being cached. The expires epoch; directive tells the browser to set the expires date to 01 Jan 1970, a date in the past means don’t cache this, while the expires modified +90d; tells the browser to set the expires date 90 days from the Last-Modified date.

Another way to see these headers is via Developer Tools in Chrome ( or a comparable tool in any other modern browser ). Open a Dev Tools window while browsing the web page you’d like to check, refresh the page, go to the Network tab, and click on any file to see the headers. This approach will give you a better overview of how your Nginx settings are serving and caching each file involved in an http request.

After some thought and study, I decided to change my initial approach to caching static content to one that will help guarentee any content that I modify is served fresh from the server rather that stale from the cache. Here’s what I came up with, for now, for a particular business application that I am still developing:

1
2
3
4
5
6
7
location ~* \.(?:ico|css|js|gif|jpe?g|png)$ {
  add_header Pragma public;
  add_header Cache-Control "public, must-revalidate, proxy-revalidate, max-age=86400";
  etag on;
  gzip  off;
    expires +1d;
}

The users who access this site every day will only need to re-download static content once a day, an additional second or two once a day won’t hurt them. In the version of Nginx I’m currently using, enabling gzip disables etags. For now, I’d rather have etag enabled, so I’m disabling gzip for static content, but leaving it enabled for the dynamically generated content that is returned from the proxied app server.

Resources for further reading:

  1. https://www.mnot.net/cache_docs/
  2. http://en.wikipedia.org/wiki/HTTP_ETag
  3. http://stackoverflow.com/questions/499966/etag-vs-header-expires
  4. http://stackoverflow.com/questions/5799906/what-s-the-difference-between-expires-and-cache-control-headers
  5. https://www.owasp.org/index.php/List_of_useful_HTTP_headers
  6. http://cyh.herokuapp.com/cyh