Monthly Archives: September 2007

ISAPI_Rewrite 3.0 Makes It Easy For Apache and IIS

ISAPI_Rewrite is a plug-in for IIS that simulates the Apache mod_rewrite module.

The big deal about the new release, outside of performance improvements is it is now ‘possible to move configurations from Apache to IIS and vice versa just by copying .htaccess files’.

Nice….No more httpd.ini for IIS and .htaccess for Apache.

Get the rest of the details from the ISAPI_Rewrite homepage.

CFQUERY versus Database Views

I had to gather some information to share with my team to promote the use of Database views versus CFQUERY. Figured I’d share with everyone else, too.

When I started with ColdFusion I never had access to a powerful database. Granted that was back 8 years ago. I was taught on ColdFusion 4.5 Express and MS Access. Which did the trick for those quick e-commerce sites that got my CF career in motion. But when I took that official CF job, and by official I mean a regular paycheck, things changed quite a bit.

A high traffic site with an Oracle DB on the back-end was a nice change. The first habit to break was the over use of the CFQUERY tag. It was almost a ‘no-no’ at the shop I was working in. After all, why use ColdFusion for database transactions when Oracle, and the server it ran on, cost a lot more. That’s looking at it from the business side of things…

The technical side is a little more involved but makes sense after you break it down.

You need to understand what is involved when using the CFQUERY tag. There are a few steps to get that data where you want it:

(note) – If it is the first time the CF template is being processed extra time will be added to compile and cache it.

1. CF replaces all dynamic values in your SQL (dbField = #variableName#) and binds your cfqueryparams with the actual values for that request.

2. Then it sends the query to the database server.

3. The database server compiles it.

4. Runs the query and returns the result set to the CF server.

5. The data is then stored in the CF server’s memory for you to manipulate with more CF code.

(note) – If you are storing Client variables in the DB, then consider the load added with those trips back and forth.

The process may not seem extreme or unnecessary, yet…

What about caching the queries, you ask? A great idea, however, you’re approach matters. Using the CFQUERY params like cachedWithin and cachedAfter are restricted by timespans. Which is fine if your application handles the data in the same matter. Often, that’s not the case, and the data will need to be refreshed or updated at different intervals and request throughout your application.

What about caching them in one of your scopes (application, session, etc)? This is a better idea. It gives you control over when queries are cached, and how often the data is refreshed. It’s a practice I was using often, until just recently. But that’s a topic for another post coming soon (which I will include a nice caching component for public use, as well).

Now, we’ve covered the options CF gives us natively. Hopefully, you are not using MS Access and you have a solid DB behind your application. From what I can recall, VIEWS are supported by most of the major players (SQL Server, Oracle, Postgre, and MySql).

A VIEW is a virtual table that is pre-built from an existing query. The DB’s SQL engine will pre-compile them with built-in algorithms that optimize performance on the platform they are run on. They can also be indexed like a table, and we all know how great indexing is when it comes to performance gains. However, indexing a View is not as easy as a table.

Performance is the name of the game for high-end sites. It doesn’t matter how pretty or informational your site is if it takes too long to deliver.

As a Software Engineer you have to account for all parts of the system you are developing while enforcing best practices. Understanding how things work can only benefit you as a developer. We know all the steps involved in a simple CFQUERY and we know the pros of using a View. There is however, a con when using a View. You lose the power to substitute dynamic values in your SQL. A stored procedure will get that kind of job done, but that’s another topic on its own.

Let’s do what they do on the business side, and maximize our resources. We have this complex query we are using with CFQUERY, and we need that data. To begin, assume there are no dynamic values involved in the SQL.

ORIGINAL


SELECT * FROM tblOne INNER JOIN tblTwo ON tblOne.id = tblTwo.id
LEFT OUTER JOIN tblThree ON tblTwo.id = tblThree.id
WHERE tblOne.isActive = 1 AND tblTwo.isApproved = 1

We are going to take our well-formed query and create a View in the DB with it. You can script this by just adding ‘CREATE VIEW vwMyNewView AS’ before your query. We then update the CFQUERY tag to do a simple SELECT on our view.

UPDATED


SELECT * FROM vwOurNewView

It won’t take CF much to send that UPDATED query to the DB server. The DB server already has the original query optimized and returns the results in record time to CF. Nice little improvement. Now you have some options. You can add dynamic values to that CFQUERY if you want to filter with a WHERE clause or sort with an ORDER BY, or even treat it as a sub-query. If the situation allows for it, cache it (application scope being shown here).



SELECT * FROM vwOurNewView
WHERE categoryId = #form.categoryId#
ORDER BY #form.sortBy#

BENCHMARK
I ran some tests and the proof is in the pudding. The ORIGINAL query would take 234ms to return 1 record, while the UPDATED would take 65ms. That was on the initial run, so some extra time is in there to compile the CF before even getting to run the CFQUERY. So I ran it a bunch of times after to average things out…

The results:

ORIGINAL’s average was 65ms and UPDATED was 16ms.
That’s 4 times faster. Case closed.

TWEAKING THE ADMIN
On another note, take into consideration your datasource settings. Choosing to maintain the DB connections will keep an active connection between the CF server and the DB server opened based on activity. This will allow for faster results if there is an existing connection to use. There is a limit on the number of pooled connections, and time limits on their lifetime when idle. If one gets dropped and the others are in use, time is added to wait and re-establish a new connection. So try to figure out the optimal values for those ADVANCE SETTINGS under your datasource.

The other section to look into is the Cache settings. Queries and templates can be cached by CF, and limited as well. So again, if it’s been ran recently and cached by CF it will run quicker. If it gets bumped out of the pool, time will be added to recompile.

Subversion Tip: How to Add External Repos To Your Project

I wanted to include some 3rd party code from another repository with my current project and store it with the rest of the files in my SVN repo. Which you can do by grabbing a copy from the 3rd party and including them when you commit your files. However, having to update and export before committing can become grueling and unnecessary. Subversion offers a simpler solution with its use of Properties. External definitions, in this case. I have a few methods laid out for you.

Command Line Method:

First, make sure you have an editor set in the Subversion config file. You can find it in:

Windows

%USERPROFILE%Application DataSubversionconfig
Mac Linux
~/.subversion/config

Now, make sure the ‘editor-cmd’ parameter is uncommented and then set it to the command line editor of your choice. Be sure to replace the word ‘editor’, that is there by default, with your selection. (i.e. -editor-cmd = vi).

Next, navigate to the folder of your working copy where you want to add the external repo to. Run ‘svn propedit svn:externals .’ This will open the editor with a temp file for you to add/edit the properties. Enter your externals like this:


local/path/ svn://external/url/ (grabs HEAD revision)
local/path/ -r# svn://external/url/ (grabs specific revision #)

Save, and exit the editor.

Update your working copy. It will now fetch the external repositories and place them into the folders specified.

Finally, commit your changes. You will see the folder you added the Properties to listed as ‘modified’. This will write your external links to the server-side repo, so the next time you or anybody else pulls from the repository they will have the externals already attached.

GUI Method – TortoiseSVN (PC)

Right Click on the Subversioned folder, and go to TortoiseSVN, Properties. Click ADD, select ‘svn:externals’ from the Property Name dropdown. Enter the mapping and url of the external reference described above. Click OK, and OK again. Run SVN Update to fetch files, and SVN Commit to add the changes to the Repository.

GUI Method – SmartSVN (Mac)

Open a project. Right click on the root or one of the folders and select EDIT EXTERNALS. Enter your local path for the mapping, and the URL of the external repository. You can select a revision or grab the HEAD by default. Click OK. Update and Commit.

Apache Delivers index.cfm as Plain Text

I had to post this because I couldn’t find a quick answer when I Googled It.

I added index.cfm to my Apache config file under DirectoryIndex to kick off the file as a default. Which worked, kind of…When no page was specified the index.cfm was loaded. However, it was coming up in plain text. Not exactly what I am looking for here. I need that page to compile and render out with CF.

I had to add the CF extensions (.cfm, .cfml, etc.) to the AddHandler Jrun section of the Apache config file.



JRunConfig Verbose false
JRunConfig Apialloc false
JRunConfig Ssl false
JRunConfig Ignoresuffixmap false
JRunConfig Serverstore "C:/opt/coldfusion7/runtime/lib/wsconfig/1/jrunserver.store"
JRunConfig Bootstrap 127.0.0.1:51011
AddHandler jrun-handler .jsp .jws .cfm .cfml .cfc .cfr .cfswf

I am going to assume they weren’t there because when I installed CF I used the built-in server method. And later, when I configured it for Apache using the Web Server Configuration Tool, I didn’t check the box to configure it for Coldfusion Applications.

NO MORE WAR – Run CF7 and CF8 Together with Apache

I’ve seen a few articles for setting up Coldfusion 7 and 8 simultaneously using the instance manager and deploying a WAR file.

I had heard it was possible to do it with both versions of CF in standalone mode. However, I wasn’t able to find any documentation on it.

Work has me developing with Apache on an XP machine. And here we go…

Make sure you install each Coldfusion version to its own directory. For the purpose of this guide I have CF7 installed to c:coldfusion7 and CF8 to c:coldfusion8.

It doesn’t matter which one was installed first, just remember when you are installing them you are using standalone mode, with the built in server. DO NOT ASSIGN or CONNECT to a Web Server. This prevents CF from updating your Apache config file.

** If you are already running a version of CF, you will only be installing the other version, and you don’t have to worry that its already connected. Just be sure not to connect the new install, and skip the next Connector step below. **

Once you have both versions of Coldfusion installed, the next step is to connect Apache. You can use the Web Server Configuration Tool from the Start Menu, or by running wsconfig.exe in the c:coldfusion#runtimebin folder. Do this for only one of the versions of CF, as doing it for both will overwrite the others’ changes.

When it completes, open your Apache config file (httpd.conf) and look for a block of code like this:


LoadModule jrun_module "C:/coldfusion/runtime/lib/wsconfig/1/mod_jrun20.so"

JRunConfig Verbose false
JRunConfig Apialloc false
JRunConfig Ssl false
JRunConfig Ignoresuffixmap false
JRunConfig Serverstore "C:/opt/coldfusion/runtime/lib/wsconfig/1/jrunserver.store"
JRunConfig Bootstrap 127.0.0.1:51011
#JRunConfig Errorurl
#JRunConfig ProxyRetryInterval 600
#JRunConfig ConnectTimeout 15
#JRunConfig RecvTimeout 300
#JRunConfig SendTimeout 15
AddHandler jrun-handler .jsp .jws .cfm .cfml .cfc .cfr .cfswf

Keep in mind, what you see above is the default settings for your Apache Server. So if you connected CF7 then that would be your default CF version for all your current virtual hosts, unless you specify otherwise.

Which is the next step. Update your virtual host settings.



ServerName cf8testsite.local
DocumentRoot C:wwwrootcf8testsite
JRunConfig Serverstore "C:/coldfusion8/runtime/lib/wsconfig/1/jrunserver.store"
JRunConfig Bootstrap 127.0.0.1:51000
Alias /CFIDE C:coldfusion8wwwrootCFIDE

Notice the JRunConfig directives. They’ve been updated with the path to CF8 and a unique proxy port number. If you used CF8 for your default then you would be specifying the port and path to CF7. You will not choose a different port number for each virtual host. Don’t get the wrong idea. That is the proxy port for CF8 here. So each Virtual Host you want to run with CF8 will get the same 2 lines added to its settings.

If you notice, the path C:/coldfusion8/runtime/lib/wsconfig/1/ doesn’t exist yet. We need to create it. It’s easier to copy it from the version of Coldfusion that has already been connected. The path will be the same. Copy the folder ‘1’ over to the wsconfig folder, including the files it contains.

Don’t forget the CFIDE alias. You may even need to add a DIRECTORY definition to allow access to it.

Apache is now configured and ready to go. But we’re not done yet. We need to update the CF version we didn’t connect to use the new port number we chose above.

Dig out the JRUN.XML file in C:coldfusion8runtimeserverscoldfusionSERVER-INF (assuming we connected CF7) and change the proxy port to the one from above, along with activating the proxy itself:


false
51000

Save your changes. Restart Apache and the Coldfusion Services.

Load up cf8testsite.local in a web browser, and I put a dummy index.cfm in there to dump the SERVER scope to make sure its using the version I want.

That should do it.

UPDATE (2/15/2010):
If using Railo also, remove the following from the default apache confi settings and place them in the CF virtual hosts. Remember to update the path and proxy port to the appopriate versions:

AddHandler jrun-handler .jsp .jws .cfm .cfml .cfc .cfr .cfswf
JRunConfig Serverstore "C:/coldfusion8/runtime/lib/wsconfig/1/jrunserver.store"
JRunConfig Bootstrap 127.0.0.1:51000

Take a look at the related articles for more information.

FFMPEG and ColdFusion

This topic seems to come up often.

I worked on a project recently that required the use of FFMPEG to convert videos into FLV. Your typical YouTube approach. There are some posts out there that offer what they can, but most using the native CFEXECUTE. Which I can say myself was not an effective solution.

It seems the thread would either close too early or not at all. No matter what you set for your timeout.

Our solution was to use the Java Runtime library.

The function looked like this:

(Keep in mind your arguments will vary depending on what you are trying to do here)







rtnStruct.success = false;
arrayappend(ar_arg, "-i");
arrayAppend(ar_arg, '"#arguments.srcPath#"');
arrayappend(ar_arg, "-g 300");
arrayappend(ar_arg, "-y");
arrayappend(ar_arg, "-s");
arrayappend(ar_arg, "320x240");
arrayappend(ar_arg, "-aspect");
arrayappend(ar_arg, "4:3");
arrayappend(ar_arg, "-f");
arrayappend(ar_arg, "flv");
arrayappend(ar_arg, "-ar");
arrayappend(ar_arg, "44100");
arrayAppend(ar_arg, '"#arguments.destPath#"');

try{
r = createObject('java','java.lang.Runtime').getRuntime();
r.exec('#arguments.binPath#ffmpeg.exe #arraytoList(ar_arg,' ')#');
r.runFinalization();
r.freeMemory();
rtnStruct.success = true;
}

catch(excpt any){
rtnStruct.message = excpt.message;
}



UltraMon – A Must For Multiple Monitors in Windows

I’ve tried a handful of Virtual Desktop Management Apps and Window Managers for Windows but nothing quite as nice as you’ll find native in Linux.

UltraMon is not the ultimate solution, since it doesn’t handle multiple desktops. But when it comes to Window Management with Dual or more monitors in Windows, it’s tough to beat.

Give the link a click and try it out.

Curious to hear some feedback and always open to better solutions.

(Still looking for a solid Virtual Desktop Manager)

Synergy – The Software KVM

This has to be one of my favorite Apps out there and its free.

“Synergy lets you easily share a single mouse and keyboard between multiple computers with different operating systems, each with its own display(s), without special hardware.”

I’ve had multiple machines at home for years. Back in the day those KVMs were great at saving me space on the desk. There was nothing worse then shuffling between keyboards and mice.

That delay between switching and having to double tap scroll lock and then an arrow key, has been replaced with a tiny deamon and a simple client that uses the network to share 1 keyboard and 1 mouse. Giving you simultaneous access to your machines.

I can move through screens fluently from left to right; up and down. You can have the same setup they have in the Matrix without paying for a few multi monitor video cards and trying to stuff them all into one machine.

Synergy even allows for clipboard sharing. So you can copy and paste from one machine to the other. My laptop keyboard and trackpad rarely sees action unless I am on the road.

Check out there site on SourceForge or give the above link a click.

MachBlog Postgre Fix

I was using MachBlog with MS SQL Server and had very little trouble. I made the switch to Postgre and things started to blow up.

I made some updates and before I send it over to Pete and Matt, I’m gonna throw it up here for anyone to grab.

There was an extra field, isHidden, and some other bugs. I also cleaned up the DB creation scirpt. Reordered a field to match the other scripts. Not much of a difference there, but it helps with Import / Export if you don’t have a good tool for that.

I zipped the entire PostgreSQL databaseObjects directory and the SQL script.

machblog_postgre_fix.zip