Genesis Query Evaluation Tool - Built on Flash Forms

A few weeks ago I was surfing The Goog and stumbled across Ryan Guill's qBrowser application. I was quite impressed with the application but at the same time was immediately inspired to create my own version of the application using Flash Forms. It's not that I didn't like his version, it was more of a personal challenge and, as it turned out has been quite a learning experience as I've come up with a few new techniques that I can use in the applications that I develop and maintain for my company's intranet.

Disclaimer:
My code is messy. I've seen a lot of bloggers say this before only to see some pretty tight code come out of them. I mean it - it's messy. I've only been coding for about a year and have no formal training. I'm pretty much self taught so some of the techniques may be sloppy and the technical descriptions I use may be a little incorrect. So please bare with me and feel free to provide any constructive criticism - just be nice ;)

Credits:
As I said the application functionality was wholly inspired by Ryan's app, but 98% of the code is my own. I did use Ryan's code for acquiring the existing DSN's from the serviceFactory and give him credit for inspiring my project.

Version and Licensing:
This version of Genesis is considered Beta. You can freely download, enhance, modify, distribute it after you download it from here. If you do decide to try it out, I would appreciate it if you could please drop a note in the comments of this post. There are a few minor known issues in the application that I plan to address in future releases (if user interest deems it necessary). Genesis is free for any use (commercial, private, etc).

Installation:
Unzip the Genesis folder into your webroot and you're ready to roll.

Prerequisites:
To use Genesis you must be running CF 7.0.1.

Supported Databases:
Genesis has been tested almost exclusively with SQL Server. The object browser will not work with Access but I believe it should work with mySQL (I think. Don't quote me on that since I haven't tested it). Object browser support for other databases (I.E. Oracle, etc) may be added in the future. The query tool should work with any DSN type but the object browser is dependent on the presence of an information schema view existing in the database that I can query to create the OB.

Overview:

Genesis has a left hand Administration panel which contains an Object Browser, Query Options and Results Options. The right hand portion of the tool contains the valid Coldfusion DSN's on the present server, the Query input field and the Results and Messages navigator.


Object Browser:


The Object Browser is a tree representation of your Coldfusion datasource. The root of the tree is the DSN name, the next level contains the table owner, with the subsequent tables and columns in the final two levels respecitively.

The Object Browser supports two pretty cool features:

1.) Drag and Drop (choose a table, column or multiple columns and drag them to the query window for quick query building).





2.) Quick Query Scripting (choose a table and right click in the query window to quickly script a Select *, Update or Insert query).

Query Options:


You can manage the queries that you build in Genesis that are saved to the server using the Query Options page. Future enhancements will check if query file exists on the server before saving. Please make sure you don't save over your queries if you don't intend to.

Results Options:


The results page allows you to save the current results grid to the server in either Excel, CSV or txt (tab delimited) formats. You can also view or delete the results directly from this page. Future enhancements will check if results file exists on the server before saving. Please make sure you don't save over your results if you don't intend to. I also hope to include an option to allow you to email a results file. The results page is another cool feature since it is actually exporting the contents of a cfgrid to an external format. This is accomplished by passing the grid's dataProvider to a CFC that does some pretty heavy lifting. The export CFC makes use of a few UDF's from cflib. The first UDF converts the dataprovider from an array of structs to a query object and the subsequent UDF's then convert that query object to the desired format at which point the file is saved to the server and the function refreshes the list of displayed files.




Query Input:


The query input field has one of my favorite features - real time SQL code coloring. Please note that this function is not perfect and tends to be a tad laggy if your query is large. I know I don't have all the SQL keywords in my array so I'll try to add them in the future. If any obvious ones are missing let me know.

Here's the actionscript function that powers the real time code coloring. The outer loop loops over the list of keywords. The inner loop first checks for the indexOf that keyword - if that index is not -1 (meaning the keyword exists in the query text) then the inner loop evaluates the text string to find the occurence of that keyword, setting the appropriate color as identified in the keywords array.

Comments (19) | Print | Send | Code | del.icio.us | Linking Blogs | 4434 Views