Coldfusion 10, ISO 8601 dates, and bugs

JavaScript generates an ISO 8601 date like this:

new Date();
// returns Mon Oct 08 2018 11:17:34 GMT+1100 (Australian Eastern Daylight Time)

new Date().toISOString();
// returns "2018-10-08T00:17:34.004Z"

Note the inclusion of milliseconds and the conversion to the UTC time.

If you want to use that date string in parseDateTime() in ColdFusion 10 (and maybe later versions but not tested by me), you need to specify the correct mask. The CF docs seem to be unclear regarding the milliseconds. This is what seems to work:

// ISO 8601 date from JS in UTC

dtStr = "2018-10-08T00:17:34.004Z";

res = parseDateTime(dtStr,"yyyy-MM-dd'T'HH:mm:ss.SSSX");

dateTimeFormat(res, "yyyy-mm-dd hh:nn:ss.LLL z");
// returns: 2018-10-08 11:17:34.004 EST

res = parseDateTime(dtStr,"yyyy-MM-dd'T'HH:mm:ss.SSS");
res = parseDateTime(dtStr,"yyyy-MM-dd'T'HH:mm:ss.SS");
res = parseDateTime(dtStr,"yyyy-MM-dd'T'HH:mm:ss.S");

// For all the above,
dateTimeFormat(res, "yyyy-mm-dd hh:nn:ss.LLL z");
// returns: 2018-10-08 12:17:34.000 EST <== note 12:17, not 11:17 as above

The CF date object created by parseDateTime() uses the time zone of the CF server it is running on. ie converts UTC to local (server) time as long as you specify the ‘X’ in the mask.

Notes:
1. the 'T' in the mask – the single quotes are required or an error will be thrown.

2. the mask for the ms when passed to parseDateTime is ‘S’ NOT ‘L’ (as it is for timeFormat() and dateTimeFormat

3. the final X is required to maintain UTC adjustments and you must also have the same number of digits in the ms part.
ie: if the date string ms is three digits (eg “.004Z”) the mask must also be three digits (ie “.SSSX”).
“.SSX” or “.SX” etc will throw errors if the mask is three digits.

4. Time Zone bug: Today (Oct 8th) is in daylight saving time. If I dump out getTimeZoneInfo(), the utcHourOffset is correct at -11. But notice that the time zone displayed is EST. It should be AEDT (Australian Eastern Daylight Time). If I use a test date before DST (eg Oct 1st) it still says EST when it should say AEST (Australian Eastern Standard Time).

5. Daylight Saving Time: the test date here (Oct 8th) converts accounting for DST as long as I specify the “X” in the mask. If I use a test date NOT in DST (eg Oct 1st) it correctly converts the time to 2018-10-01 10:17:34.004 EST ie 10am, not 11am.

Note I am using the dateTimeFormat() function, not timeFormat(). Despite what the docs say, the ‘z’ mask does not work as expected.


dateTimeFormat(res, "yyyy-mm-dd hh:nn:ss.LLL z");
// returns: 2018-10-08 11:17:34.000 EST 
// but 
timeFormat(res,"hh:nn:ss.LLL z");
// returns 2018-10-08 11:17:34.004 z  <== ie literal z

Using quotes around ‘z’ makes no difference.

See also:
https://www.hass.de/content/coldfusion-2016-bugfix-inconsistencies-date-functions

Advertisements

Coldfusion cfquery: running mySql user-defined functions

CF10, mySql 5

I was creating a very complex sql query for my app with lots of conditional code and realised it would be simpler to have some mySql functions to abstract some of the complexity. This was new territory for me.

One immediate gotcha was with sqlYog: If you are testing your function in sqlYog and get a 1305 error: FUNCTION not found, make sure you are clicking the button to Execute ALL queries, not just your one test SELECT query. Doh!

Related to this, if you combine all the statements below into a single cfquery statement your CF server must be configured to run multiple queries. Since that is not a great idea from a security point of view the solution is to run each one separately as shown below.

This is what I eventually found to work (a highly simplified example to demonstrate the syntax):

<cfquery datasource="#request.dsn#" >

DROP FUNCTION IF EXISTS my_func;

</cfquery>

Define the function:


<cfquery datasource="#request.dsn#">
    CREATE FUNCTION my_func(str1 VARCHAR(30), str2 VARCHAR(30))
       RETURNS VARCHAR(100)
    BEGIN
       DECLARE res VARCHAR(100);
       SET res = CONCAT(str1, ' ', str2);
       RETURN res;
    END
</cfquery>

Test just the function:

<cfquery datasource="#request.dsn#" NAME="recordset1">

SELECT my_func('abc1','def2') as myString;

</cfquery>
<cfdump var="#recordset1#">
<!--- myString = "abc1 def2" --->

Use in an SQL SELECT:

<cfquery datasource="#request.dsn#" NAME="recordset2">

SELECT 
members.memberid, 
my_func(members.firstname, members.lastname) as myFullName 
FROM members;

</cfquery>
<cfdump var="#recordset2#">
<!--- Outputs myFullName like "Murray Hopkins"  --->

Same thing using CFSCRIPT:

<cfscript>	
	
	sql = "DROP FUNCTION IF EXISTS my_func;";
	qry = new Query( sql = sql, dsn=request.dsn );	
	
	sql = "CREATE FUNCTION my_func(str1 VARCHAR(30), str2 VARCHAR(30)) RETURNS VARCHAR(100) BEGIN DECLARE res VARCHAR(100); SET res = CONCAT(str1, ' ', str2); RETURN res; END";
	qry = new Query( sql = sql, dsn=request.dsn );	
	
	sql = "SELECT my_func('abc','def') as myString;";
	qry = new Query( sql = sql, dsn=request.dsn );	
	qryObj = qry.execute();		
    writedump(qryObj.getresult());

	sql = "SELECT members.userid, my_func(members.firstname, members.lastname) as myFullName FROM members;";
	qry = new Query( sql = sql, dsn=request.dsn );
	qryObj = qry.execute();		
    writedump(qryObj.getresult());	

</cfscript>

I hope this saves someone some time.

Go well,
Murray

Getting GluJS to play with Sencha Cmd

GluJS is an MVVM framework and optionally a Specification-Model-View-ViewModel (SMVVM) framework (BDD driven by Jasmine) that works with ExtJS (3 and 4). This post is not about GlusJS itself, rather it  is about a solution to a problem that there didn’t seem to be a solution to on the forum.

This guide gives a detailed overview of GluJS and there is also this video.

For this blog post and discussion I am using ExtJS4.2, Sencha Cmd 5, GluJS 1.2 and Jasmine 2.3.4 (for the BDD specs that are an integral part of GluJS).

First thing to say is that I am new to GluJS and have found a number of hurdles getting it going. Some of the info on the main GitHub repo is out of date. I was given a lot of help by Ryan Smith and I have forked his fork and am adding to that. On the plus side, once the obstacles have been worked through or fixed it is an amazing framework and I look forward to building a useful piece of software with it. This post is about one of those hurdles – a very important one for many of us. That is, how to be able to use GluJS and then build with Sencha Cmd. GluJS can be used in two ways:

  1. what I will call “pure” mode, where GluJS takes over the whole app starting at the ViewPort layer
  2. what I will call “mixed” mode, where you can include GluJS “modules” within an existing normal ExtJS MVC app via xtype : 'glupanel'. In other words, to “embed” GluJS into ExtJS where you want to do that (eg as a migration path to convert to a full GluJS app)

One of the goals here is to is normalise those two modes into a single architecture where the only difference is the relative amounts of MVVM and MVC. Obviously they both actually use ExtJS – it is just the architecture that varies. I explain that below. Now, I have a large existing project that needs the mixed mode approach and I also wanted to write a new “Admin Control Panel” type app in pure mode. In both cases I needed to build for production using Sencha Cmd.

Ok, enough background. This is what I did. Taking pure mode first – an app built from scratch using the GluJS approach. The working example for this blog post is in the examples folder of the repo (see AdminControlPanel folder) and the rest of what I am going to say will be a lot easier to understand if you reference that example. I will summarise the solution first before going into more detail (there are some gotchas to take care of). The post by Sharon Kong was close (thanks Sharon!). What you need to do is to wrap the GluJS functions within ExtJS classes like this:

/**
 * Root viewmodel for the App
 */
Ext.define('App.gluwrap.viewmodels.vmMain', {
    constructor: function() { 

        glu.defModel('App.glu.Main', {

            vmVPMainMenu: {
                mtype: 'VPMainMenu'
            },

            vmVPCentreComponent: {
                mtype: 'VPCentreComponent'
            }

        })

    }
})

Notice that the glu.defModel() is contained within a constructor. That was the missing piece of the puzzle (and yes, there is a little more to it than that). Each ViewModel, View, Model, Locale, etc has it’s own file as you would for ExtJS MVC. See the demo. You then “require” the files as you would for any other ExtJS class in your Application.js file:

Ext.define('App.Application', {
    name: 'App',
    extend: 'Ext.app.Application',
    requires: [
      // These need to be required so they exist for GluJS
      // to modify on initialisation
      'Ext.selection.CheckboxModel',
      'Ext.selection.RowModel',
      'Ext.grid.Panel'

      // ExtJS wrapper files containing GluJS components

      // GluJS Library files
      , 'Glu.glu-extjs-4', 'Glu.glu'

      // Your GluJS app files
      , 'App.gluwrap.locale.locale_en'

      , 'App.gluwrap.models.mUsers'
      , 'App.gluwrap.models.mUsersActivity'

      , 'App.gluwrap.viewmodels.vmMain'

      // etc
    ]
    // etc
});

Note that the Glu Library files are also wrapped in an ExtJS constructor and required here. See the demo code.

Note: this is the high level overview. I will suggest some reorganisation of this in the detail below – I am just giving you the summary.

(A word about my naming conventions: I have found it beneficial to prefix the ExtJS MVC file names with ‘v’ for a view, ‘m’ for a model, ‘c’ for a controller, etc. That way, when I have the files open in my editor I know which file is the view and which is the controller and if there are lots of files open and the tabs are abbreviated the letter is first so I can still see it. I have continued that when using glujs (vm prefix) so I can tell my viewModel from my view in the editor. Additionally, and nothing to do with this demo, I name my internal variables with the same convention. So instead of var panel = btn.up('window'); I use var vStatusWin = btn.up('vStatusWin') since ‘vStatusWin’ is also the alias (and the filename). That way, everytime I see vStatusWin in my code I know exactly what it is. Also in event handlers eg onActivateOpsManualPanel: function(vOpsManualPanel){}  )

Ok, back to the topic. You no longer need to include your GluJS files in script tags in index.html. By wrapping the Glu library and your Glu modules within ExtJS wrappers, Ext.Loader is happy to dynamically load the files just like regular ExtJS class files.

The next step is to use Ext.create() on each file
eg Ext.create('App.gluwrap.viewmodels.vmMain')
What that does is to execute the constructor that wraps around the Glu function which in turn loads the Main viewModel App.glu.Main into the App.glu namespace. From that point on Glu is happy and you can also run your Jasmine specs (see below for how).

So that is the summary. Now to deal with the detail and the issues this raises!

1. Create your new app structure using Sencha generate as normal and test that it works (default ViewPort). In order to use my demo code easily I suggest you use the namespace of App ie Sencha generate app App {path to your new app folder}

2. Download the demo files and merge them into your app folder. I have only included the relevant changed folders and files in the demo. Now you can follow along.

3. To keep it simple we will keep the GluJS and Jasmine libraries within this new app. Of course, you can put these folders anywhere in your web root if you wish. After merging the example folders you will see a BDDlib folder in the root of your new app’s workspace that contains Glu and Jasmine.

4. Edit the app.classpath property in .sencha/app/sencha.cfg to point to the location of the BDDlib folder. ie add ,${app.dir}/BDDlib to the classpath.

5. A Glu namespace and path has also been added to the Ext.Loader.setConfig() in your app.js file.

4. There is a new folder at app/gluwrap. This is where you put your Glu viewmodels and models etc as described in the summary above.

5. Have a look at the BDDlib/glu files. Notice that they are the GluJS library files wrapped in an ExtJS class and constructor. This is the same process as for your app glujs files, just with a separate namespace. The file glu-test-custommatchers.js is not wrapped since it is optional and only used in the specRunner.html file.

I wanted a consistent way to implement GluJS in both pure and mixed mode so I did the following:

1. See the files app/Application.js, app/applicationGlu.js and app.js. I wanted to keep the GluJS bits separate so they all live in applicationGlu.js. The only change to your Application.js from the Sencha generate is to change the extend:
extend: 'App.applicationGlu',
so that it includes applicationGlu.js

2. Look at app.js. Here we add a launch() function that calls this.callParent() which refers to launch() in applicationGlu.js. And, of course, the call to glu.viewport() to kick off the app.

3. Now turn to applicationGlu.js. The requires array lists all the ExtJS “native” files we need plus the ExtJS “wrapper” files containing the GluJS objects. The launch() function creates the Glu Library files then calls this.createAppGluClasses('gluwrap'); which iterates the App.gluwrap namespace (already populated by Ext.Loader via the requires array) and runs Ext.create() for each class it finds there. That gets your Glu app files into the App.glu namespace as glu objects. Finally we create some mock data via the createMockBackend() function before returning to app.js to launch the viewport.

That’s basically it for the app itself. At this point you can run index.html in your browser and you should see a working app (as simple as it currently is!). I have tested it in Chrome, FF and IE, just to be sure. All good. Activity list tab has no data – it isn’t broken, I just haven’t done that yet.

If you run Sencha app build testing (or production) it should build ok. There was a gotcha here that you should be aware of. The glu-extjs-4.js code choked Sencha Cmd because Cmd added a random semi colon to the minified glu code when it packaged it. I added a do-nothing variable to the src/providers/sencha/binder_adapter.js file which fixed that misunderstanding. That fix is in the repo and is included in the build you are using.

SpecRunner

1. See SpecRunner.html. That should be self explanatory.

2. The difference is that the specs are in appSpec.js. Open that up. You will see that it is a variant of app.js. Some differences:

  1. We extend ‘App.applicationGlu’
  2. autoCreateViewport = false so the UI doesnt start.
  3. In launch() the this.callParent() initialises the glujs in applicationGlu.js
  4. Then you include the specs directly.

Try SpecRunner.html in your browser. All should be green.

Mixed mode MVVM and MVC

I haven’t provided a complete demo of that but I have included some files in /sampleMixedMode. I used the same architecture as above so I have consistency across the projects. I am assuming you have an existing app that was generated by Sencha Cmd. We will add a simple glupanel called ‘PanelA’ in an ExtJS window.

1. Copy the BDDlib folder from the example above to an appropriate place. I have the BDDlib folder in my web root so I can share it across multiple projects.

2. Edit the app.classpath property in .sencha/app/sencha.cfg to point to the location of the BDDlib folder. eg add ,${app.dir}/../../BDDlib or similar to the classpath. Adjust your path according to where the BDDlib folder is.

3. Add the Glu namespace and path to the Ext.Loader.setConfig() in your app.js file.

4. Save a copy of the applicationGlu.js file from /sampleMixedMode/app next to your Application.js file. If you are not using namespace ‘App’ amend the root namespace in the requires array and the createAppGluClasses() function.

5. Open your existing Application.js file and:

  1. change the extend to: extend: 'App.applicationGlu',
  2. leave all your existing entries in the requires array

6. Copy the folder gluwrap from /sampleMixedMode/app to /app (ie /app/gluwrap ). This contains our sample PanelA glu files wrapped in ExtJS classes.

7. Finally, copy the file vTestingWin.js from  /sampleMixedMode/app/view to your ExtJS app/view folder (where all your ExtJS view are). Adjust the Ext.define classname to suit your app structure. Notice the standard this.items[] array contains an xtype of ‘glupanel’ with an mtype of ‘App.glu.PanelA’ which loads that viewmodel and renders the glu view to the window.

8. Wire up the test window to a button handler in your app as you normally would for any other ExtJS window (because that is exactly what it is).

That’s it. Test your app and when you click the button you should see the glu view render the html. Test build using Sencha build testing (or production). All should still work.

SpecRunner

Again, basically the same as the Admin Control Panel example above. Remember that we are only testing the GluJS components in your app, not the whole app. One of the reasons I started using GluJS was that I needed to start unit testing my ExtJS app and hadn’t done that before. My search for how to do that lead me to GluJS and since I had already decided that I liked the BDD “red to green” approach to spec driven development this was very attractive. I am thinking that it might be possible to build Jasmine tests for the non-GluJS objects in appSpec.js too but I am yet to look at that.

1. Copy the SpecRunner.hmtl file from /sampleMixedMode and adjust the paths in the script tags to your BDDlib folder

2. Copy the appSpec.js file from /sampleMixedMode and adjust the loader paths and the namespace in the spec to suit your app.

Test SpecRunner.html in your browser.

Conclusion So, I am looking forward to using GluJS more now that I have worked out how to integrate it into my normal workflow that relies upon Sencha Cmd. As an aside, the existing ExtJS MVC app that now has my test glupanel in it is packaged for desktop using nwjs. That packaging still worked without a hitch. I thought it would but one can never tell. 😉

Signing the OSX version of your NWJS (node-webkit) app for GateKeeper (for Mac noobs like me!)

NOTE: These instructions are for v12 of nw.js. Current instructions are here: Signing-your-app-but-NOT-for-Mac-Apple-Store-(MAS)

These are also useful:
http://docs.nwjs.io/en/latest/For%20Users/Package%20and%20Distribute/#mac-os-x

http://docs.nwjs.io/en/latest/For%20Users/Package%20and%20Distribute/

————-

The problem: I am new to Mac. I want my nwjs (node-webkit) app to be able to be downloaded from my website as a DMG file so users can install it. I am not intending to put this app in the Apple Store. I am using Yosemite.

Rant paragraph: The first thing you need to know is that Apple run a kind of racket in this case. In order to do this simple task you need to pay them AU $149 (per annum) just to get a Developer ID so you can sign your app so users can install it without seeing the “unidentified developer” message! The purported reason is that it increases security on the end user’s mac. Well, all the user needs to do is to Control+click the app then click Open. So much for the security! To charge $149 for that is a racket IMJ. Anyway, rant aside…

Gatekeeper info is here if you dont know about it.

The overall steps are:

  1. Get a Developer ID (and certificate and install it on your development Mac).
  2. Use that certificate to sign your app
  3. Package the signed app into a DMG file for distribution

This post also has some useful background info, as does this post. – especially the comment about making sure you have downloaded XCode (from Apple Developer area)

However, this post  had the most helpful checklist for doing steps 1 and 2 above. Thanks Jean-Baptiste Escoyez!
Since it was written a couple of things have changed which is what prompted me to write this blog post.

  • Part A:
  • Keychain is an app in Applications/Utilities
  • If you are taking responsibility for the whole process, ignore the step “Send the newly created file to your agent”
  • Part B:
  • “Upload the CSR file sent by admin (or dev)” means navigate to where you saved the certificate you created in Part A
  • Ignore the “Send it to the requetser” step. You are the requester.
  • Part C:
  • The “User ID” mentioned is the Developer ID which is a long hash. I couldnt find where the ID was mentioned within the Keychain item (probably missed the obvious) but you can get the ID by opening a Terminal window on your Mac and entering security find-identity in a Terminal session. If all is well you should see the Developer ID displayed.
  • Part D:
  • The Terminal commands neeed to change slightly because node-webkit is now called nwjs (since v0.12). If you are using that version or later, change the …./Frameworks/node-webkit references to …./Frameworks/nwjs
  • Of course you can put all that in a “command” script file so you dont need to keep typing it.
  • Part E:
  • I added those commands to my script file

Gotchas found:

  • You need to have internet access in order to run the codesign command. It seems it accesses a Timestamp server when it runs. My internet access had gone down and I got a codesign message about not finding a Timestamp.
  • Dont leave random files in the package or the code signing might fail with a “code object is not siged at all” message. I had made a copy of the info.plist file as info.plistCopy while I was trying to work all this out and codesign didnt like it!

To package the app in a DMG file I am using DropDMG. It seems you can make DMGs manually, and there might be other solutions, but I found this worked really well. At $24 I found the time saving worth it.

There are a few issues around changing the nwjs icon to your custom icon and getting DropDMG to use it in the installer which I will write about in another post soon. Ask if you need it.

Creating a command script for OSX (for Mac noobs like me)

In Windows you have batch files.

In OSX you can create “command” files. Open a text editor eg Sublime Text. (I noticed that TextEdit wouldnt allow me to save the file with a .command extention)

Enter your terminal (command line) commands into your file and save it with a .command extention.

To allow OSX to run that file via a double click you need to change the permissions. Open a Terminal window and enter

sudo chmod 755 your_command_file_path_and_name

You will be asked for your OSX password. Note that as you type it does NOT give any feedback about the key presses so just enter the password anyway.

You should be able to then navigate to your command file in the Finder and double click it to execute the script.

Packaging an ExtJS4 application using Node-Webkit part 3

This is a continuation of Part 1 and Part 2

How to install Node modules in your app

At this point you might want to install a node module and extend the demo. For example, you might want to make use of the neDB module. This is a way to persist data locally. There are a number of modules that do that. This one has no other dependencies so is a good candidate for our demo.

To install the module, open a command line shell in the root of your development app. The process below will install that module into the node_modules folder. I assume you have set a PATH to your NodeJs install folder so npm can be found.

1. From your command prompt:

npm install nedb --save

2. Open resources/js/NWK-requires.js and add

NWK.Datastore = require('nedb');

Add some code to a new button and handler in Main.js to add and find some docs then test. eg:

{
    xtype: 'button',
    text: 'neDb',
    handler: function(btn, e) {
        NWK.mydb = new NWK.Datastore({
            filename: 'mydb',
            autoload: true
        });

        var docs = [{
            mykey: '2f4vn9l1NGW',
            data: 'blah blah'
        }, {
            mykey: '2f4vn9l1rAB',
            data: 'Boo hoo'
        }];

        NWK.mydb.insert(docs, function(err, newdoc) {
            console.log('INSERT callback')
            console.log(err);
            console.log(newdoc);
        })

        NWK.mydb.find({
            mykey: '2f4vn9l1rAB'
        }, function(err, docs) {
            console.log('FINDING')
            console.log(err);
            console.log(docs);
        })
    }
}

Packaging an ExtJS4 application using Node-Webkit part 2

In Part 1 of this tutorial we generated an ExtJS4 app and got it running in the node-webkit context. Now we want to build, package and deploy our demo app.

Build

1. Build using sencha cmd as usual. eg From your command prompt:

sencha app build production

2. You also need to copy some files to the production folder that sencha cmd doesnt know about. I have done this for you in the demo files but if you have changed anything you will need to do this yourself. Copy these to the app root of the production build – the same folder as index.html
node-modules folder
package.json
mydocs folder (our sample files to launch via your app)

You might want to edit the package.json file to set the “toolbar” property to false as an example of how you can have your production deployed version configured differently than your development version. You could also have different window constraints etc. And, of course, in your production app you would probably turn off the debugging window! ie in package.json set toolbar:false and remove the code from Applications.js.

3. Test the production version by double clicking the nw.bat file in the production build app root. (Again, adjust the path in the batch file to suit your NWK install).

Package

There are a number of ways to package your app in node-webkit for Windows, OSX and Linux. I developed a batch file to do this for Windows which you will find in the included build/production/AppNWK folder.

For my system you need 7-zip since I am using it’s command line options to automate the process. So get that and install it if you dont already have it.

1. Copy the required files from your node-webkit installation to the AppNWK/nwFiles folder. You only need to do this once (but update these if you install a new version of node-webkit). The files currently in that folder are the ones that are required to be packaged with Windows apps for v0.10.2 of node-webkit. I left them there as a guide.

2. Double click the package.bat batch file. This will copy the production build app files to the AppNWK folder and package it all into a zip file called nwkTest.zip.

3. The resulting nwkTest.zip file is your packaged app. Create a new folder somewhere, unzip the file and double click the nwkTest.exe file to launch your app!

Part 3 shows how to install extra NodeJs modules using the npm (Node Package Manager)