Tackling Anxiety Against Automation

A few years ago, the company that I worked for was preparing to upgrade from MS Sql Server 2000 to Sql Server 2005.  While there wasn't too much abnormal concern about upgrading the databases themselves, there was a lot of concern about the large number of DTS packages that the company created for the majority of their B2B processes.  Their solution until they could come up and complete a test strategy to ensure the DTS packages would run under Sql 2005's DTS runtime, just incase the packages couldn't be converted to SSIS in some fashion, was to stop writing new DTS packages and push the processes into .Net console applications that would be scheduled through a batch processing system.  Personally, I liked this solution since it allowed me to write .net code in VS and not VBS code inside of the DTS Package designer in Enterprise Manager.  Actually abstracting out processes into a more reusable and testable state was a great benefit over VBS and DTS as a whole.

One of the first console applications written under this initiative was to automate a series of manually ran Sql Scripts that people were running against production to accompany a file import process.  It was a pretty simple project and ran, still to my knowledge, bug free, even when tested by my QA team.

Now fast forward to today.  The need for the process is still in place and the code hasn't been updated since no changes have been required; however, I found out that the application was only ran once in the past few years and then the person who used to manually go through the steps and scripts didn't want to automate it and has been doing the manual steps ever since.  Even today, this person has continued to manually spend a few hours every other day going through the same ceremony.  I think my brain gave me a BSOD from what I find highly illogical thinking.

So I approached my coworker who has been doing this and start asking the number of "why" questions I had.  Here are some of the responses I was treated to:

• "I don't trust what I cannot see or watch."
• "I like being able to watch and correct where necessary."
• "The process had a bug."
• "I won't ever know when it breaks or succeeds."
• "What else would I work on?"

From the discussion around these themes, there are a few design and development themes that can/should be implemented when faced with such an anxious coworker.

 

Establishing Trust through Quality

While I see James Bach's point in his "Quality Is Dead" blog post, I disagree with the blanket of the statement.  There are always constraints when you're talking about software development (usually Time/deadlines being the largest constraint), it doesn't mean that every piece of code that you churn out will be of poor quality.  Practices such as TDD or just plain Unit Testing and reporting can be implemented in order to demonstrate and achieve a higher level of quality to someone who doubts quality.  Now, I believe that any developer can write code with and without unit tests and still achieve the same level of quality; however, it's easier to validate that level of quality through unit tests and TDD practices.  This level of validation is essential to people who do not trust something to be automated.

 

Establishing Awareness through Instrumentation

The bulk of the other themes that I received surrounded the concept of being able to step through the process and watch/correct the data as necessary.  This is a nice thing for debugging; however, if an automated process has to be stepped through, then it's not automated.  One way to handle this issue is to ensure you have a high level of coverage of instrumentation in the code/process.  This means making sure the proper types of notification is provided, auditing is turned on to the degree needed, and any errors (be it bugs or bad data-related) are captured and reported appropriately.  I cannot confirm my coworker's claim that there was a bug in the code since I was unable to find one in the bug database.  If an email to capture the error existed, at the very least, I would begin having my own doubts about the quality of the code; however, even that was not found.  If instrumentation was implemented to a better degree than what I put into it for error logging and tracking, I would be able to verify the claim.  As far as bad-data and catastrophic scenarios (i.e. server loses network connection mid-process), those items can (and was) implemented through logging or some for of process auditing infrastructure.  The more data that can be provided, the more confidence the person will have when automated.

 

Establishing Automation Can Assist with Workload

The last theme that came from the conversation dealt with how the person's routine became.  This person was a very routine-oriented individual who knew exactly when to do when and how long it would take.  This person was a person of ceremony and possibly a bit fearful of being automated out of a job.  After being in the industry for a number of years, I have came to the conclusions that those nightmare stories of people being replaced by programs rarely happen to the developers or analysts that implement the program or automated process.  The automation allows for new, different tasks to be done.  Even if the automated process is kicked off manually, it still frees up time to do other things.  This means you can do more easily or in general transition onto the next big thing.  I do not really know any company out there that would say they have a truly shortage of work.  Even if it's wish-list, lowest priority, internal cost projects, there's always something to do.

 

Summary

Automation is a beautiful thing that I have grown more and more fond with.  I have grown into a developer where I look for things to automation.  I have seen the themes I talked about here at a couple of different companies I've been with and people that I've talked with.  Each time, I find more and more ways to automate certain elements of a work day and also make the work flow easier.  Hopefully a few things here will help others as well.  Now if only I could find a way to automate my chores at home. :-)

How I'm Learning F# - Writing the First Application

Over the past few months, I've been hearing more and more about the use of functional programming concepts and also languages like Haskell and F#.  While some of the initial musings that I've read revolved around how the concepts have been around for decades and that it makes financial and scientific applications easier to read and write, I couldn't find a good reason to start learning it for my typical line-of-business application design and development job or even some of my basic hobby projects.  Nonetheless, I kept getting drawn to the concept and have decided to focus on learning it.

This post marks the second entry of a series that I'll be writing to discuss how I'm going about learning F#.  While I'm not saying that my method of learning is ideal and should be followed by others, I'm just reporting how I'm going about doing it.  Over the course of this series, my goal is to provide other .Net developers a(nother) resource for learning the F# language as well as apply the language into some non-financial or scientific scenarios.

 

Series Table of Contents:

1. Finding Resources
2. Writing the First Application

 

Downloadable Resources:

• F# Hello, World Visual Studio 2008 Project 

 

Setting Up Visual Studio 2008 with F#:

As of this writing, F# is at CTP status since September 2008 (v1.9.6.2).  For this example, and possibly for the remainder of the series, I'll be using this September 2008 CTP of Microsoft F#.  This CTP installer only works with Visual Studio 2008.  The CTP version does not work with Visual Studio 2005 (which previous versions did) nor the Visual Studio Express editions.  If you would like to use F# using Visual Studio 2005, please refer to this StackOverflow question about how to set up F# in Visual Studio 2005 using an older version. NOTE: If you are using an older version of F# for use in Visual Studio 2005, I cannot guarantee that the examples presented in this series will work.  After you have downloaded and installed F# (either the CTP or a previous version), we are ready to begin writing our first F# application.

 

Writing the "Hello,World!":

Like many great computer books, this series will be starting with a simple example which we'll dissect afterwards.  In order to write the example, we'll need to start a new F# project in Visual Studio.  If we go to File > New Project..., we are treated to the following dialog window:

The projects listed under Visual F# include a F# Application, a F# Library, and a F# Tutorial.  For the sake of this example, we'll be creating an F# Application named HelloWorldFSharp.  After clicking on the OK button, Visual Studio creates the new F# project containing a single Program.fs file. 

When the project is created, the Program.fs file contains a single line #light.  This tells the F# compiler to use the lightweight syntax of F# which removes some of the verbosity of the language and applies rules around white-space.  In the majority of examples on the Internet and books, the lightweight syntax is used and will be used going forward here as well.

Under the #light line, add the following code snippet:

let question = "How are you?"

let sayHello name =
    "Hello, " + name + "! " + question

In this code, we are doing two things.  First, we are declaring a variable named question.  Second, we are declaring a method called sayHello and takes a parameter called name. The sayHello method concatenates some strings together and returns the composed string. When using the #light syntax, every indented line is considered part of the function or action with the last line being the return value.

One thing to mention about F# when compared to other languages is that variables are immutable.  Once a variable is declared (and given a value), that value can never change.  I believe there are some exceptions to this when interacting with object properties; however, at the core of the language, basic variables cannot be re-declared nor their values redefined after they have been initially declared. Because of this, many F# documents and tutorials will use "values" instead of "variables" to prevent the confusion.  I'll attempt to follow this pattern.

 

One thing that a developer coming from VB.net or C# may notice is that there are no types defined here.  F# infers typing based on the value and how they are used.  If we wanted to declare a type for our question and name values, the code would like like the following:

let (question : string) = "How are you?"

let sayHello (name : string) =
    "Hello, " + name + "! " + question

In F#, you can declare the type by using (variablename : type) syntax.

 

Now that we've seen how to declare a value and a method, we can output a value to a console window using the following line:

printfn "%s " (sayHello "James")

Here, we are using the printfn method which prints formatted text to the console window.  The %s is a placeholder for a string value which we are supplying by calling our sayHello method passing in the name "James". In this example, we're treating the method like a value.

 

Here is the full code of Program.fs.

   1: #light

   2:  

   3: let question = "How are you?"

   4:  

   5: let sayHello name = 

   6:     "Hello, " + name + "! " + question

   7:  

   8: printfn "%s " (sayHello "James")

 

Running the Program using F# Interactive:

At this point, we can either go the Build menu and build the project like many other console applications or we can use a feature unique to F# called the F# Interactive window.  F# Interactive (fsi.exe) allows you to run sections of your F# program as well as write ad-hoc F# statements.  To use F# Interactive from within Visual Studio, highlight the code you wish to run and press Alt+Enter.  A new Visual Studio panel opens for F# Interactive and reports information about the code (or the error it generates).

For example, if you were to highlight the line where we declare our value question and press Alt+Enter, the following will appear:

val question : string

What this line explains is that we have a value called question that is of type string. If we do the same to our sayHello method, the following appears:

val sayHello : string -> string

This line states that sayHello is a value that accepts a parameter of type string and returns (signified by the -> ) a value of type string.  If we highlight the all of the code we've written so far, we are shown the following:

val question : string
val sayHello : string -> string

Hello, James! How are you?

This shows us the same output as we had previously for question and sayHello.  In addition, it executes the last line and displays the output.

 

Conclusion:

While this wasn't the largest demo nor the most unique, it is a simple step forward.  The premises of immutable variables (values) being declared and understanding the lightweight syntax are important to learning F#.  Over the next post of the series I'll be making some different applications in order to continue to explore F#'s syntax and it's power.  F# can do pretty much everything that VB.net or C# can do (with a few exceptions I'm now finding out) and hopefully the next posts will help us begin to find examples of where to apply it.

How I'm Learning F# - Finding Resources

Over the past few months, I've been hearing more and more about the use of functional programming concepts and also languages like Haskell and F#.  While some of the initial musings that I've read revolved around how the concepts have been around for decades and that it makes financial and scientific applications easier to read and write, I couldn't find a good reason to start learning it for my typical line-of-business application design and development job or even some of my basic hobby projects.  Nonetheless, I kept getting drawn to the concept and have decided to focus on learning it.

This post marks the beginning of a new series that I'll be writing to discuss how I'm going about learning F#.  While I'm not saying that my method of learning is ideal and should be followed by others, I'm just reporting how I'm going about doing it.  Over the course of this series, my goal is to provide other .Net developers a(nother) resource for learning the F# language as well as apply the language into some non-financial or scientific scenarios.

 

Series Table of Contents:

1. Finding Resources

 

Where To Start:

One of the challenges of learning anything is to find where to start gathering the necessary information on the subject matter.  Thankfully, there's already a great number of sources that can be used to learn F#.  Here are some that I've been using to get started in F#:

• StackOverflow.com's F# tagged Questions
• Foundations of F# by Robert Pickering
• Expert F# by Don Syme, Adam Granicz, and Antonio Cisternino
• MSDN F# Developer's Portal

Of these 4 resources, I've so far leaned on StackOverflow and Expert F# the most.  I have examined only a little bit of the MSDN site and have been using it if I cannot find my answer on StackOverflow.  I initially began reading Foundations of F#; however, I was having a hard time building on each chapter.  Expert F# flows a lot better and also build on each previous chapter which makes it ideal for learning.

 

Why am I learning F#:

I'm learning F# in order to broaden my skill set into functional programming.  Initially, there wasn't any other reason for that.  I looked at OCaml, Haskell, and F# as all possibilities and eventually settled on F#.  Since then, I have began seeking a better reason for learning F#.  Many of reasons presented in interviews and blogs and books talked about it's use in financial and scientific fields of study.  While this is fine, I was still looking for more since I, like many other developers, am not in those fields of application development often.  After diving deeper, I found that F# is a very concise language that allows developers to write some code faster and in a more logical way.  Where as there is a large amount of code used to write a program in OO for the sake of being OO, F# allows a very simple and straight forward syntax to write programs or pieces of .Net applications in a functional manner.

 

What F# can do:

One other thing that I was fooling myself with once I began learning F# was this idea that it could or couldn't do something that a different .Net language could.  It wasn't until I began reading Expert F# that I truly understood how narrow minded I was being.  In Expert F#, the authors do a fantastic job at illustrating not only the language in a general sense, but also how to apply F# as a replacement to VB.net or C# in applications like ASP.Net, Winforms, and even Domain Specific Languages.  While, from what I can tell, many people agree that replacing C# or VB.net with F# in the ASP.Net realm isn't the best or most recommended application for the language, it does illustrate the point that F# can do anything that C# or VB.Net can do today.

 

Where does F# fit:

I have a feeling that I may be skewered a bit for this; however, this is just my perception as a novice F# developer at this point.  I've been developing in Microsoft technologies for years now and have also done work in other languages.  Where I see F# fits is where you have functional steps of processing of data.  I can see F# being a great language for writing the data engines for custom ETL applications and processes, as well as similar business logic processors.  I am certain I'll find additional locations that would assist me in my daily development job as I learn more of F#.

 

What's Next:

Next up in the series we'll dive into setting up VS2008 with the F# CTP and write the ever so popular "Hello, World!" or something like that :-).  Until then, I highly recommend checking out the F# resources mentioned above and also read a lot of the questions currently out there on Stack Overflow.

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 :-).