Saving Time Through the Command Prompt Explorer Bar

In the last couple of posts, I have been mentioning different command line tools that I've been using (specifically Nant and SchemaSpy).  While some people love command line applications and using the command prompt, there are some that cherish the nice Windows GUI and the mouse.  The issue with the GUI is that applications like Nant and SchemaSpy don't really work well out of the box if you double click on them.  This means that in order to have double click functionality that does something, you will need to script it out using tools like shell or PowerShell scripting that executes the configured command for the desired results.  If you don't create (and subsequently debug) the script wrapper, you will find yourself opening up the command prompt, navigating to the directory, and then typing in the command manually.  Thankfully, there's a tool out there that makes things easier.

 

The Command Prompt Explorer Bar

The Command Prompt Explorer Bar is an open source tool on the Code Project web site developed by Pavel Zolnikov that allows you to place a command prompt window inside of your windows explorer window (see image below).  While this immediately doesn't sound useful, it offers some powerful productivity features that a typical command window doesn't.  First, when you open the Command Prompt Explorer Bar, it immediately takes you to the directory you're in.  So, if you are in the directory that contains the Nant script you want to manually execute, you can open the command prompt and immediately type in your command.  In addition, as you move around windows in the explorer window, your command prompt automatically synchronizes with it.  Last but not least, the Command Prompt Explorer Bar offers the ability to have macros.

 

Figure 1: The Command Prompt Explorer Bar for a user's (named llama in this case) home folder.

 

Where to Download the Command Prompt Explorer Bar

As stated previously, the Command Prompt Explorer Bar is an open source tool on the Code Project web site.  You can download the installation binaries and/or the source code from this location. After you have downloaded the installer for it, you will be able to begin using it.

 

How Do I Open it?

The Command Prompt Explorer Bar is very simple to use since it's just an embedded command prompt at it's core.  It works in both XP and Vista and there's no special window style needed to have it open (folder bar is supported as is Vista's Aero interface). There are two ways to open the Command Prompt Explorer Bar.  First, you can go to the View Menu > Explorer Bar > Command Prompt menu option.  The second way is to just press Ctrl+M in the folder.  NOTE: Ctrl+M does not work if you are literally on your windows desktop, a file system/explorer window must be open for the Command Prompt Explorer Bar to open.

 

What Are Those Icons For?

As you may have noticed from the above image or if you've installed the Command Prompt Explorer Bar already, there are a series of buttons/icons on the left sidebar.  These buttons help provide some quick additional functionality.  Going from top to bottom, the buttons are as follows:

• Command Menu - Provides the command prompt context menu, similar to right clicking inside of the prompt.  Clicking directly on the button shows an "About" dialog.
• Synchronize - Turns on and off the ability for the command prompt to synchronize as you move through folders above it.
• CLS - Sends the CLS command to the command prompt; clearing the screen.
• Get Selected Folder Items - Enters all items you have selected in the folder view above in a <space> separated list.
• Enter Key - For those who don't like to press the enter key on the keyboard...I think.
• Esc Key - Used to clear a partially typed line; same as the Escape Key on the keyboard.
• Macro - A drop down menu containing a list commands that you can use for quick access. (more on this in the next section)
• Grow - Grows the constraints of the Command Prompt Explorer Bar.
• Shrink - Shrinks the constraints of the Command Prompt Explorer Bar.

 

Using Macros

When the Command Prompt Explorer Bar was created, Pavel Zolnikov entered in the ability to create and use macros.  The macros that he supplied with it were very much .Net Command Prompt tools.  Browsing the list, you'll find a number of macros for generating a type library, register a COM compliant assembly, and registering an assembly in the GAC.  There are also some macros for traditional command prompt commands like attrib and cd.  There is also a few macros that contain a {1} in their name.  What this syntax represents is the first selected item in folder list above.  For example, if you highlight a folder and then call the "cd {1}" macro, it executes the change directory command into the selected folder in the command prompt.  It does not sync back to the folder view however.

 

Adding New Macros

While the default macros are useful, Pavel also provided us the ability to create our own macros.  This is where it shines.  To create a new macro, Click on the Macro menu button directly.  This will open the C:\Program Files\Command Prompt Explorer Bar\macro.xml file.  Inside of this file you'll see some very basic XML syntax which is described below.

• <group> - The document element as well as elements that denote a grouping/submenu list of macros
  • text - Sets the text to be shown as the submenu item.
• <macro> - Denotes a macro-ed command.
  • text - Sets the menu text to be shown for the macro
  • execute - a boolean value telling the command prompt to execute the macro when selected or not.
  • command - the command the macro will execute upon selected if different than the text.(optional)
• {0} - types out all currently selected files in the folder view
• {1} - types out the first selected file in the folder view (subsequent numbers represent that element of selected items; i.e. {2} is the 2nd selected item).
• {1:n'} - types out the last selected file in the folder view (n' represents the inverse order of the files selected)

Now that we've looked at the syntax, let's add a new macro group with a Nant command.  This will assume that you have Nant installed and configured with the Nant.bat file in your system path as described by the Nant installation and configuration steps.

   1: <group text="Nant">

   2:     <macro text="completeBuild {1}" 

   3:            execute="true" 

   4:            command="Nant -buildfile:{1} completeBuild" />

   5: </group>

 

Adding this snippet to the file (inside of the parent <group> xml document element), the new macro group and command is instantly added to the macro menu (no close and reopen needed).  This command allows us to select our build file in the folder view and then executes the macro to call it's "completeBuild" task that it presumably contains.  Quick and easy for executing command line tools and applications without having to navigate and type in the command in a command prompt nor mess with scripted wrappers.

 

In Summary

While this tool isn't the most amazing one out there, it is very useful if you find yourself working in a command prompt a lot.  Great tools and utilities like Nant and SchemaSpy can be easily reduced to a simple click of a macro while you never have to leave the window you are browsing the file system with.

Database Documentation using SchemaSpy

One of the largest challenges that developers and software project managers have a difficult time with is how to keep documentation in sync with the code elements.  While there are a large amount of tools that allows for documentation to be created based on code comments (i.e. nDoc, JavaDoc, SandCastle, etc.), there are not a lot of tools that do the same for Databases.  Now, there are tools that will document a database after the fact (or will create the database based on the documentation you're creating), many are very expensive or appear to be no longer support.  One tool that I have started using recently for database documentation was recommended in the book The Productive Programmer by Neal Ford.  The tool is called SchemaSpy.

 

For the sake of brevity, this post will cover how to generate database documentation for only Microsoft SqlServer 2000 and 2005 databases using SchemaSpy.

 

What is SchemaSpy?

SchemaSpy is a Java command-line application that will connect to your database and generate information about the tables and their relationship.  SchemaSpy does does not address any database programming elements (i.e. Functions, Triggers, Stored Procedures, etc.); however, supplies a large amount of information on the tables and views themselves along with their relationships.  It also evaluates your indexes and keys in order to build relational diagrams.  Furthermore, it retrieves information from the database tables in order to give you a view of the number of records, the types of columns, and a large amount of other information for it.  In addition to large amount of information, it also provides notification if there are any anomalies in the schema; such as implied relationships between columns (no foreign key constraints), tables without indexes, columns marked as both "null" and "must be unique", single column tables, and more.

 

What does SchemaSpy Produce?

Below are a few screen shots of the documentation that SchemaSpy creates for the Northwind database.

 
Figure 1: SchemaSpy's Index page for the Northwind Database.

 

Figure 2: An example of the the diagramed relationships SchemaSpy produces

 

What do I need to start using SchemaSpy?

SchemaSpy requires 4 separate components to be installed in order to work properly:

• The SchemaSpy application itself (version 4.1.1 as of this post) - Download
• The Graphviz Diagram Engine - Download
• Java Runtime Environment (v1.5 or higher)  - Download
• The Database Driver(s) required for a Java Application to connect to your database.
  • MS SqlServer 2000 / 2005 - JTDS v1.2 - Download

Once the 4 components are downloaded and the JRE and Graphviz are installed, the next thing we have to do is install/configure the JTDS drivers (more on why JTDS instead of traditional JDBC in a moment).  As of this post, SchemaSpy only works with the 1.2 version of the JTDS driver and requires the jtds-1.2.jar file to be in the C:\Program Files\Java\Shared\jtds directory.  The driver jar file can be stored in other directories; however, an additional command parameter has to be passed to SchemaSpy if you do (see below).  At this point, the only thing that's left is to execute SchemaSpy with the proper command line arguments. 

 

Running SchemaSpy

After all of the above components are installed, you are able to execute SchemaSpy from a command prompt using the following (general) syntax:

java -jar SchemaSpy_4.1.1.jar -t databaseType -host serverName -port portNumber -db dbName -u userName -p password -s schemaName -o outputDirectory

Let's dissect these command parameters a bit:

• -t  : This identifies the type of the database we're using.  Since this post is focusing on MS SqlServer using the JTDS driver, the database type will be either mssql-jtds (for SqlServer 2000) or mssql05-jtds (for SqlServer 2005).
• -host  :  This is required by our type.  It signifies what database server to connect to.
• -port  :  This is required by our type.  This signifies what port to connect to the database.  This will not always be 1433 (see below).
• -db  :  This tells SchemaSpy what database to diagram for us.
• -u  : This tells SchemaSpy the database user name to login with.
• -p  : This tells SchemaSpy the database user's password to login with.
• -s  : This tells SchemaSpy what schema to read and diagram.  The user credentials provided must have access to at least read this schema.
• -o  : This tells SchemaSpy the output directory to generate the documentation.
• -dp  : This tells SchemaSpy the path to locate the database driver file (not used in this example)

An example of a fully completed command:

java -jar SchemaSpy_4.1.1.jar -t mssql-jtds -host myDBServer -port 1433 -db Northwind -u myDbUser -p password -s dbo -o NorthwindDocumentation

If your projects use Sql Server's integration authentication option, you can use the -sso option.  The -sso option allows you to remove both the username (-u) and password (-p) parameters and use the identity of the account used to called the Java Runtime Environment.  Modifying the example above to use integrated authentication, we get the following:

java -jar SchemaSpy_4.1.1.jar -t mssql-jtds -host myDBServer -port 1433 -bb Northwind -sso -s dbo -o NorthwindDocumentation

 

Why JTDS instead of the JDBC drivers?

As I mentioned above, I recommend using the JTDS Java Database drivers instead of the traditional JDBC drivers that Microsoft offers off of their web site.  While the fact that JTDS is a single driver as opposed to the 2 separate JDBC drivers is a plus, the main reason is the trouble I had getting the JDBC drivers to work with SqlServer 2005 databases.  If I was working on documenting a database I'm creating for a project on my local system using SQL Server 2005 Express Edition, it would always give me an error stating that the "variant" data type is not supported.  After a lot of research and trial and error, the only result I could only get this working by using the JTDS driver.

 

How to use SchemaSpy to Diagram a Named Instance

When installing Microsoft Sql Server, you have the option of installing it as a stand alone service or a named instance.  In the event of a named instance, you would normally connect to it in your .Net code (or via Management Studio/Query Analyzer) as ServerName\InstanceName.  For example, the default instance of a Sql Server 2005 Express Edition is localhost\SQLExpress.  In order to connect to a named instance database with SchemaSpy, you do not need the Instance Name of the database (just the server name as if it wasn't an instance), and you have to use the database's TCP Dynamic Port instead of the typical 1433.

You can locate a named instance's dynamic port through the following steps:

1. Open the Sql Server Configuration Manager application (either through the Programs Menu or a MMC Snap In)
2. Expand the Sql Server 2005 Network Configuration element
3. Click on the Protocols for "INSTANCE" where "INSTANCE" is the name of your named instance
4. In the right pane, double click on TCP/IP
5. In the TCP/IP Properties dialog that appears, click on the IP Addresses tab.
6. Towards the bottom of the IP Addresses tab, you'll find the TCP Dynamic Ports value to use for SchemaSpy

The above steps were taken based on a local installation of SQL Server 2005 Express Edition.  Your steps may vary depending upon configuration.

 

Conclusion

SchemaSpy is a tool that I have been using to help with some of the documentation pains that I have experienced on some of my personal and professional projects.  Being able to add the command into a build script to automatically keep the documentation up to date is a huge time saver and is invaluable as people migrate onto the project(s).  I hope you find it as useful as I have, and to assist further, I have added a simple step by step guide to setting up and begin using SchemaSpy below. 

 

The Step-By-Step

1. Install the Java Runtime Environment version 1.5 or higher if it is not already installed.
2. Install Graphviz Diagramming Engine.
3. Download the SchemaSpy jar file.
4. Download the JTDS v1.2 driver and place the jar file in the C:\Program Files\Java\Shared\JTDS directory.
5. Obtain User Login Credentials with the proper permissions to read the schema for the database you will be documenting if integrated authentication is not available.
6. Obtain the TCP Dynamic Port if the database you are accessing is a named instance.
7. Open a command window and navigate to the location of the SchemaSpy jar file.
8. Execute the SchemaSpy jar file using the proper command line parameters.

Filtering Table Results with jQuery

A few weeks ago, I was looking for a way to filter records in a table.  The screen that I was going to use this one was a composite/advanced search screen to where after the results are populated, the user can continue typing in search results into other fields and it would auto-filter.

 

There are a large amount of jQuery plug-ins that offer table filtering features.  I reviewed the TableSorter plug-in; however, I didn't want a filtering system where 1 text box would filter all columns.  That plug-in works extremely well and I am using it in other areas, but I'm looking for a text box-to-column filtering mechanism instead. Bill Beckelmen, who has a lot of great posts and examples on the TableSoter plug-in, suggested trying the jQuery Column Filter or tablefilter plug-ins.  Both were better; however, also auto-created the text boxes in another header row for the table.  After looking a bit more, I came to the conclusion that I would need to write my own functionality.  If I continue working on refining this code, I will probably see about turning it into a plug-in for easier use.

 

Post Downloads:

Table Filtering with jQuery Example - TableFilter.zip

 

The filterColumn Function:

At the heart of the filtering process is a JavaScript function called filterColumn.  This function takes 2 parameters.  The first parameter takes the text name of the table column that is being evaluated.  The second parameter is the text value to filter on.  For the sake of the example, the filterColumn function will be bound to each text box's onKeyUp event; passing it's current value for the filtering text.

Inside of the filterColumn function, 3 things occur to make the filtering mechanism work.  First, the two input parameters are inserted into regular expression patterns that will be used to locate the table column header as well as the records in that column once found.  The second thing it does is loop through the column header (first table row in the thead tag that contains th tags...or "#resultsTable thead tr:first th" in jQuery selector syntax) to locate the column's index.  Lastly, it loop through the table's records using the column's index and sets the row's display style to "none" if it doesn't match the filtering criteria.

   1: function filterColumn(columnHeader, value){

   2:  

   3:     // set regex objects with the appropriate patterns.

   4:     var headerPattern = new RegExp('^' + columnHeader + '$', 'i');

   5:     var filterPattern = new RegExp('^' + value + '', 'i');

   6:  

   7:     // initialize the column identifier

   8:     var colIndex = -1;

   9:     

  10:     // loop through the header's first row of cells

  11:     var headerRow = $("#resultsTable thead tr:first th");

  12:     headerRow.each(function(n){

  13:         if (headerPattern.test($.trim(this.innerHTML))){

  14:             colIndex = n;        

  15:         }    

  16:     });

  17:     

  18:     // check to see if the column was found.

  19:     if (colIndex > -1){

  20:         

  21:         // loop through each table body row and evaulate the proper cell.

  22:         var bodyRows = $("#resultsTable tbody tr");

  23:         bodyRows.each(function(n){

  24:             if(!filterPattern.test($.trim(this.childNodes[colIndex].innerHTML))){

  25:                 this.style.display = 'none';

  26:             }

  27:             else {

  28:                 this.style.display = '';

  29:             }

  30:         });

  31:     }

  32: }

Next Steps:

I have a lot of refinement to go on this function if I am thinking about converting it into a plug-in at some point.  I need to make the table's id not hard coded and bind the onKeyUp events when the search button is pressed instead of having it attached all the time in the HTML.  In addition, I need to look into incorporating a zebra-striping plug-in as well to have it address the row styling after the filtering. 

The code is a great starting point and it's very useful as is.  With a little refinement, it can provide a very robust mechanism for simple table filtering without having to learn and use a very complex plug-in.

Goals for the New Year

Happy New Year!  Ok, so I'm a week late.  With the start of each year comes a tradition that some view as a farse while others hold as an event of refocusing.  New Year's Resolutions are are great time to reflect and focus what someone wants to achieve.  While I'm not big on making my personal goals known like some other bloggers (it's just not my cup of tea), below are a few goals I've set for myself that relate (directly or indirectly) to this site.

 

Blog More

This is a given.  2008 was a huge year of transition for me in my personal and professional life.  While I was able to churn out more post than I had before, I struggled on many weeks.  I'm hoping to be able to hit my goal of once per week this year.  Hopefully I'll be able to keep it up while also providing interesting topics for everyone to follow.

 

Learn F#

One of the things that I've wanted to do in recent months is expand my professional arsenal.  Coming from a Java background and transitioned into the .Net world almost exclusively now, I've somewhat been stuck trying to stay afloat with just C# and VB.Net (ASP.Net included).  While I am still working on keeping up with the times and changes in those languages, I'm wanting to expand into F# as well for a truly different perspective. 

 

Learn Silverlight 2

I dabbled in Silverlight 1 while I was also learning XAML in general for WPF.  It was fun but I didn't get too deep into it due to the JavaScript model that it used and the obvious changes Silverlight 2 (then 1.1 alpha) had.  Now that Silverlight 2 is out.  It's time for me to get back to work on it and begin getting a subtle level of proficiency in it.

 

Finish the Books I Start Reading

For those that know me, I love reading; however, I tend to read books in parallel and rarely finish technical books.  This works well for some books since they are great for references just as much as reading cover-to-cover (i.e. Wrox's Professional C# 2008, etc.).  The issue comes in when I'm reading a book to clarify a topic or to truly learn, I tend to lose attention on the material.  This goal is to simply push myself to finish the books that I feel need to be finished.  For example, here are all of the books I'm currently reading:

• Website Optimization by Andrew B. King
• jQuery in Action by Bear Bibeault and Yehuda Katz
• The Design of Sites by Van Duyne, Landay, and Hong
• Introducing Microsoft Silverlight 2 by Laurence Moroney
• Alice in Wonderland by Lewis Carroll (ok not an IT book but is a great way to stimulate the imagination and unwind)

 

Learn DDD

One concept that is being shown a lot of attention recently is Domain Driven Design.  It's got some great concepts for managing complexity and the research I've done on it so far has yielded some interesting ideas on my application designs.  I just recently picked up a copy of Eric Evans' book and will hopefully get to it very soon to clarify a lot of the items I've had some trouble with.

 

Use jQuery More

I love jQuery.  Of all of the JavaScript frameworks that I've used on both personal and professional projects, it is the one I prefer hands down.  With that being said, I haven't had much spare time to work on any personal projects that require new ways of using it so such posts have been reduced a bit.  I have projects queued up so hopefully there'll be more jQuery related posts to come up in the future.

 

In Summary

I'm sure that I'll have more goals as the year progresses; however, for right now I feel like I have a good focus for the next handful of months.  Next week I'll start up the normal posts again.  This year will have a more diverse mix of posts with more tools and different technologies and techniques coming.  Hopefully I'll be able to stick to my goals :-).