Technology with opinion

Tuesday, February 10, 2009

Concatenating Date and Time in T-SQL

Sometimes you just want to concatenate the date section from a DateTime and the time section from another DateTime.  These could even be the new SQL Server Date or Time data types.  The easiest way I've found to do this is by combining DATEPART with DATEADD.  You get rid of the parts of each DateTime that you don't want and then add based on the lowest common denominator this being millisecond.

'ProcessedDate' = DATEADD(ms, DATEDIFF(ms, 0, [ProcessingTime]), DATEADD(dd, 0, DATEDIFF(dd, 0, [ProcessingDate])))
 

Monday, February 09, 2009

Generating Seed Data Through Select Statements

Often times you will need to generate seed data for a table such as a cross reference table when the data for this already exists in the database already.  Sometimes this is done when you decide to refactor your table design to include a cross reference table (many to many) where a one to many relationship existed previously.  If you have the seed data in your table already you can generate a host of SQL statements (usually INSERTS) for your database.

By issuing a SQL Statement like below, you need to understand the SQL Statement that you desire.  In this instance if you have a cross reference table that you need to generate between Customers and Orders you would have a desired INSERTS like below:
INSERT INTO customer_orders_xref VALUES (1, 1)
INSERT INTO customer_orders_xref VALUES (1, 2) 
INSERT INTO customer_orders_xref VALUES (3, 1)

In instances where you can have dozens or hundreds of desired SQL statements this can be tedious and error prone.  Instead if the data exists in some form in the database you can have it outputted with a simple SQL statement like the one below:

SELECT 'INSERT INTO customer_orders_xref VALUES (' + CAST(c.order_id AS VARCHAR(10)) + CAST(c.customer_id AS VARCHAR(10)) + ')'
FROM orders o
INNER JOIN customer c ON o.customer_id = c.customer_id
WHERE c.name LIKE '%Williams%'

If the first parameter was a fixed value you would just hard code it in your SQL.

SELECT 'INSERT INTO customer_orders_xref VALUES (2, ' + CAST(c.customer_id AS VARCHAR(10)) + ')'
FROM orders o
INNER JOIN customer c ON o.customer_id = c.customer_id
WHERE c.name LIKE '%Williams%'

 You could use techniques like this to generate all sorts of SQL Statements.  Let's say we added an Account Alias column (acct_alias) to the Account table and now we need to change them all to some sort of intelligible name.

SELECT 'UPDATE account SET acct_alias = ''Account# ' + c.name + ''') WHERE account_id = ' + c.account_id FROM customer 

For more complicated SQL generation you can use a code generation tool such as MyGeneration (free Open Source).

Friday, January 16, 2009

Getting started with NAnt Scripts (Automating Builds)

It doesn't take much work to setup a project to use NAnt scripts however the inevitable question is "Where do I start".  I would recommend starting by downloading the latest release 0.86 or the daily build from NAnt's website.  Next it's good to have a basic script for your assemblies and one for your ASP.Net website that you can copy and paste.

Basic .Net Assembly Build Script:
<?xml version="1.0" encoding="utf-8" ?>
<project xmlns="http://nant.sf.net/release/0.86-beta1/nant.xsd" name="Company.Portal.Domain" default="build">
  <property name="debug" value="false" />
  <property name="optimize" value="true"/>
  <property name="bin_name" value="${project::get-name()}.dll" />
  <property name="nant.settings.currentframework" value="net-2.0"/>
  <property name="output_bin" value="build\${bin_name} "/>
  <property name="build_path" value="..\build\" />
  <property name="build_bin" value="${build_path}${bin_name}"/>
  <target name="clean" description="Cleans the build folder which forces a rebuild.">
    <delete dir="build"  if="${directory::exists('build')}" />
  </target>
  <target name="debug" description="Specifies assembly to be compiled with debug symbols.">
    <property name="debug" value="true" overwrite="true" />
    <call target="build"/>
    <copy file="build\${project::get-name()}.pdb" todir="${build_path}" overwrite="true" />
  </target>
  <target name="build" description="Compiles assembly.">
    <mkdir dir="build" if="${not directory::exists('build')}" />
    <csc target="library" output="${output_bin}" debug="${debug}" optimize="${optimize}" >
      <sources>
        <include name="**\*.cs" />
      </sources>
      <references>
        <include name="..\lib\*.dll" />
        <include name="${build_path}*.dll"/>
        <exclude name="${build_bin}"/>
      </references>
      <resources dynamicprefix="true" prefix="${project::get-name()}">
        <include name="**\*.hbm.xml" />
      </resources>
    </csc>
    <mkdir dir="${build_path}" if="${not directory::exists(build_path)}"  />
    <copy file="${output_bin}" todir="${build_path}" overwrite="true" />
    <copy file="${output_bin}" todir="..\bin" overwrite="true" />
  </target>
</project>

 Basic ASP.Net NAnt Script
<?xml version="1.0" encoding="utf-8" ?>
<project xmlns="http://nant.sf.net/release/0.86-beta1/nant.xsd" name="Company.Portal.Web" default="build">
  
  <property name="debug" value="false" />
  <property name="optimize" value="true"/>
  <property name="bin_name" value="${project::get-name()}.dll" />
  <property name="output_bin" value="build\${bin_name} "/>
  <property name="build_path" value="..\build\" />
  <property name="build_bin" value="${build_path}${bin_name}"/>
  <property name="nant.settings.currentframework" value="net-2.0"/>
  <property name="web_path" value="..\web\"/>
  <property name="web_bin_path" value="${web_path}bin"/>
  
  <target name="clean" description="Cleans the build folder which forces a rebuild.">
    <delete dir="build"  if="${directory::exists('build')}" />
    <delete dir="${web_path}"  if="${directory::exists(web_path)}" failonerror="false" />
  </target>

  <target name="debug" description="Specifies assembly to be compiled with debug symbols.">
    <property name="debug" value="true" overwrite="true" />
    <call target="build"/>
    <copy file="build\${project::get-name()}.pdb" todir="${build_path}" overwrite="true" />
  </target>

  <target name="build" description="Compiles assembly.">
    <mkdir dir="build" if="${not directory::exists('build')}" />
    <csc target="library" output="${output_bin}" debug="${debug}" optimize="${optimize}" >
      <sources>
        <include name="**\*.cs" />
        <include name="**\*.resx" />
      </sources>
      <references>
        <include name="..\lib\*.dll" />
        <include name="${build_path}*.dll"/>
        <exclude name="${build_bin}"/>
      </references> 
    </csc>
    
    <copy file="${output_bin}" todir="${build_path}" overwrite="true" />
    <copy file="${output_bin}" todir="..\bin" overwrite="true" />
    <!-- Setup Web Site -->
    <mkdir dir="${build_path}" if="${not directory::exists(build_path)}"  />
    <mkdir dir="${web_path}" if="${not directory::exists(web_path)}"  />
    <mkdir dir="${web_bin_path}" if="${not directory::exists(web_bin_path)}"  />
    <copy todir="${web_bin_path}" overwrite="true">
      <fileset basedir="${build_path}">
        <include name="*.dll"/>
      </fileset>
    </copy>
    <copy todir="${web_bin_path}" overwrite="true">
      <fileset basedir="..\lib\">
        <include name="*.dll"/>
      </fileset>
    </copy>
    <copy todir="${web_path}" overwrite="true">
      <fileset>
        <include name="**.ascx"/>
        <include name="**.aspx"/>
        <include name="**.master"/>
        <include name="**.config"/>
        <include name="Config\**"/>
        <include name="IMG\**"/>
        <include name="App_Themes\**"/>
      </fileset>
    </copy>
  </target>
</project>

Setup:
  1. Install NAnt (your bin dir will likely be C:\Program Files\nant-0.86\bin)
  2.  Add NAnt bin directory to your path environmental variable so you can use nant from command-line interface anywhere
    1. Right-click "My Computer" then click "Properties"
    2. Click the "Advanced" tab then the "Environmental Variables" button
    3. Under the "System Variables" list find the item named "Path" then click "Edit"
    4. Add to the end of the "Variable value ";C:\Program Files\nant-0.86\bin"
    5. Click OK, OK, OK
    6. Now test it by clicking "Start" -> "Run"
    7. Type "cmd" then hit enter
    8. In the command prompt type "nant" then hit enter.  If it outputs a few lines and says BUILD FAILED then your path variables are correct
  3. In your solution directory create a folder called "lib" and put your libraries (anything 3rd party or not included with the target CLR version)
  4. Create your NAnt script in the target project directory naming your NAnt script: "default.build"
  5. Navigate to this location through command prompt and run one of the following commands
    1. nant - will do a build
    2. nant clean - will clean the build
    3. nant clean build - will clean the build and rebuild (recommended)
  6. Optionally you can create a make file at the solution directory which simply executes nant for each of the projects in the solution.  I call mine "make.cmd" and put it in your solution directory (one directory above your project directories assuming all project directories are siblings)
Sample Make File (make.cmd):
nant -buildfile:Company.Domain.Common\default.build %*
nant -buildfile:Company.Portal.Web\default.build %*

Usage: "make clean build"

Now you have NAnt scripts so that you can setup continuous integration for your project using something like CruiseControl.Net .

Tuesday, November 25, 2008

Creating Custom StyleCop Rules in C#

Microsoft has released a new free static code analysis tool called StyleCop.  Named after the similar FxCop, this product is aimed to be more focused for cosmetic code consistency which can increase readability and maintainability.  StyleCop has built-in rules, which you can turn off, many of which are:
  • Ordering of members in a class from most visible to least visible
  • Order of Usings in alphabetical order, with System namespaces on top
  • Proper camel casing and pascal casing
  • Proper whitespace
I like about 3/4 of these rules so I think it's definitely worth considering using, even consider integrating into your automated build process using NAnt, CruiseControl or TFS.  That being said many will likely want to extend these rules to create your own.  Doing so is fairly simple but not documented very well at this time.  I created the following rule to make sure that all instance variables (private & protected class level variables) are prefixed with an underscore.  I realize not everybody follows this convention, but I like it because it's more concise than prefixing your instance variables with 'm_' or 'this' which is often needed to prevent name collisions.

First I created a C# Class assembly project and referenced "Microsoft.StyleCop" and "Microsoft.StyleCop.CSharp".  Next I added the following class, which implements my custom rule:

using Microsoft.StyleCop;
using Microsoft.StyleCop.CSharp;

namespace DotNetExtensions.StyleCop.Rules
{
    /// <summary>
    /// This StyleCop Rule makes sure that instance variables are prefixed with an underscore.
    /// </summary>
    [SourceAnalyzer(typeof(CsParser))]
    public class InstanceVariablesUnderscorePrefix : SourceAnalyzer
    {
        
        public override void AnalyzeDocument(CodeDocument document)
        {
            CsDocument csdocument  = (CsDocument) document;
            if (csdocument.RootElement != null && !csdocument.RootElement.Generated)
                csdocument.WalkDocument(new CodeWalkerElementVisitor<object>(this.VisitElement), null, null);
        }

        private bool VisitElement(CsElement element, CsElement parentElement, object context)
        {
            // Flag a violation if the instance variables are not prefixed with an underscore.
            if (!element.Generated && element.ElementType == ElementType.Field && element.ActualAccess != AccessModifierType.Public && 
                element.ActualAccess != AccessModifierType.Internal && element.Declaration.Name.ToCharArray()[0] != '_')
            {
                AddViolation(element, "InstanceVariablesUnderscorePrefix");
            }
            return true;
        }
    }
}

The VisitElement function handles the main logic for this rule.  First it makes sure that the member isn't Generated code, which you have no control over so no sense in applying rules to.  Secondly it's making sure that the member's visibility isn't Public or Internal.  Finally we make sure that the instance variable starts with an underscore prefix..

Next we need to provide metadata so that StyleCop can categorize and describe our rule.  Add an XML file to your project, then change it's Build Action to 'Embedded Resource'

<?xml version="1.0" encoding="utf-8" ?>
<SourceAnalyzer Name="Extensions">
  <Description>
    These custom rules provide extensions to the ones provided with StyleCop.
  </Description>
  <Rules>
    <Rule Name="InstanceVariablesUnderscorePrefix" CheckId="EX1001">
      <Context>Instance variables should be prefixed by underscore.</Context>
      <Description>Instance variables are easier to distinguish when prefixed with an underscore.</Description>
    </Rule>
  </Rules>
</SourceAnalyzer>

At the root level you are naming the main category for the rules in this file.  Next you describe your suite of rules.  Then in the Rules section you can add all the rules (note: you can use RuleGroup element to further categorize your rules).  Finally you name your rule and give it a unique CheckId.  The CheckId is what you can search for your rule by in the StyleCop Project Settings.  Important: for StyleCop to correlate your class to your metadata your class and xml file need to be named exactly the same.

Finally build your project and drop your new assembly's dll into the StyleCop directory (C:\Program Files\Microsoft StyleCop 4.3).  Now double-click your Settings.StyleCop to see your rule in the list.  It should look something like the following:



Now you can open your solutions/projects in Visual Studio and click Tools -> Run StyleCop and you should see warnings for your new rule.

StyleCop is a nice little accent to any C# project.  I'd like to see Microsoft make the following changes:
  • StyleCop should be OSS (open source software) - why not Microsoft, there really can't be any proprietary business technology in an application that is basically is performing string  parsing
  • StyleCop's documentation needs to be improved - Microsoft provides to help documents for StyleCop, a user manual and an SDK manual.  Both of them lack details and some of the examples simply do not even compile.  For instance the CodeWalkerElementVisitor delegate in a boolean, in their samples they do not even return a boolean value which will not allow their samples to compile.  They do not even document the purpose of this boolean.  Through trial and error I think that if you return a false that it will stop walking (parsing) the document for this rule.
  • Custom rules should be able to be categorized within the existing main ones: Naming Rules, Ordering Rules, etc.
  • Easier to create custom rules by using regex within an XML file.  This would eliminate having to create custom assemblies and classes.
  • Command-line interface should be available for testing or scripting purposes.

Monday, November 10, 2008

Visual Studio Complaints and Moanings

Visual Studio 2008 has become unbearably slow for me, so I've decided to take some measures.  My first steps was going to include removing the features of Visual Studio that I do not currently use.  Upon attempting to add/remove for Visual Studio I received the following error: 

a problem has been encountered while loading the setup components

So as I research this issue, which seems very common, it seems to be a common theme that people who have patched VS.Net 2008 are having trouble running it's setup. One solution has you install Visual Studio SP1, which I proceeded to install only for it to freeze in the middle.

Next I decided I would just remove the existing hotfixes and patches I have installed for Visual Studio 2008 and then try to add/remove programs for VS.Net 2008.  This worked, I proceeded to remove VB, C++ and database environment.

Finally I was able to apply SP1 to VS.Net 2008, which forced me to reboot.

For those looking to speed up VS.Net a little bit you can try the tips from the following site which seemed to slightly improve the responsiveness for me: dotnettipoftheday.org 

Next I went back into VS.Net 2008 setup to remove some components and removed the following: Enterprise Tools, Dotfuscator Community Edition, Tools for Redistributing Applications, Team Developer and Tester Tools, Team Database Edition and Microsoft SQL Server 2005 Express Edition (actually this was already unchecked).

I then went into add/remove programs and removed: Microsoft Visual Studio Web Authoring Component and the header files (option below I think).  Beware of this however because when I did this I was no longer able to select "Add or Remove Features" within VS.Net setup, my guess is I'd have to 'repair' it first.

These are some of the reasons that Java's Eclipse IDE is so widely popular (in and out of the Java community) because it gives you more granular control over your environment, it's plugins and updates.  This is also why many people, including myself, prefer to have our unit testing and build tools to run outside of the environment.

Heres to one day having a robust C# & ASP.Net plugin for Eclipse.

Tuesday, November 04, 2008

Alt.Net Programmer's Toolbox

Below is my toolbox for most projects.  Just an FYI you only really need to download and install Spring.Net since it includes bins for the other NHibernate, Castle, log4net and NUnit, though you will probably want to download NUnit so you can run the nunit gui.
  • Castle Project - provides IoC, DI and MVC frameworks
  • log4net - mature logging framework for .Net
  • MyGeneration - code generation similar to CodeSmith except open source and free
  • NAnt - build program
  • NHibernate - most mature ORM framework for .Net
  • NUnit - most widely used .Net unit testing framework
  • Spring.Net - enterprise ready framework supports ORM, logging, AOP, Caching through IoC
  • TortoiseCVS - access CVS repositories via Windows Explorer.  Nice to have if you want to get latest source from any open source project
  • TortoiseSVN - access SVN repositories via Windows Explorer.  Nice to have if you want to get latest source from any open source project

Monday, July 21, 2008

The State of Affairs at Microsoft

Microsoft is the modern day Roman Empire of technology as they have dominated their sector relentlessly but lately the company has struggled with analysts questioning the companies leadership including Steve Ballmer as their stock nears 52 week lows. Their various products such as the XBOX, Zune and MSN/Live have all failed to make profits and Microsoft is literally hemorrhaging cash. This is not entirely bad so long as you can bring in more cash than you are hemorrhaging as they have been able to do with their highly successful products such as Windows, Office and enterprise software. While Microsoft in the enterprise in not immediately under threat, the other two legs of their company are: Windows & Office.

Tom Reese and Paul Rubillo writes(forbes) "Microsoft Office division numbers were a disappointment. Operating earnings there only grew a disappointing 12% for the period." Of their online division they write "losses for that division doubled to $488 million from $210 million."

Henry Blodget at Yahoo Finance thinks that Microsoft's real problem is Apple as he cites "Apple shipped 1.4 million computers in the US during Q2, representing 8.5% market share and 38% year-over-year growth.Mac shipments grew 9 times faster than the overall U.S. PC market (4.2%) in Q2." "U.S. Mac sales grew by 386,000 computers year-over-year,handily beating No. 2 HP, which sold just 222,000 more computers in Q2 2008." And to his point Microsoft also recently announced that they are expanding their Apple Mac group to the largest size it's ever been, expecting more demand for MS Office on the Mac.

While an Economist Podcast compared Microsoft trying to compete with Google and Facebook akin to "watching your father try to be cool." Additionally InfoWorld reports that Apple is now the 3rd largest PC vendor in the US.

What does this all mean and how does it relate to IT? Well for years Microsoft has been able to cope with struggling products (MS Money, MSN/Live, XBox, Zune) and still pump money into new enterprise products such as BizTalk and Sharepoint for years before any profits. They were able to do all of this partially because of the tremendous amount of money made through Windows and Office.

Microsoft has also not done much to help themselves, years of making enemies in the software industry have started to haunt them. Efforts to push their own standards in many areas have faltered (HD DVD, OpenDocument). They have even abandoned much work they started with standards like BPEL.. Standards such as OpenDocument or even Microsoft's own OpenXML will end up losing them market share. Productivity applications will be using shared and common formats which will let allow transparency across platforms at the same time when cloud computing solutions like Google Enterprise offer their own productivity suite.

In a nutshell the next step in technology is about ubiquity. With cloud computing now becoming a reality with things such as Google Docs, Google Enterprise and Apple's MobileMe it will no longer matter what platform you run on as Windows, Linux, Macs and mobile devices will operate on common standards. Each of these slowly eroding the high market share Microsoft has in the desktop market and eventually in the enterprise.

Microsoft is not about to vanish but a page is being turned in the history of the company. Bill Gates has stepped down and there is much fresh blood in Microsoft with new perspectives. Don't expect Apple to become the next Microsoft, as I don't think Job's wants Apple to be the next Microsoft. Microsoft's monopoly may of helped consumers in the way that it allowed technology to saturate our life. However at this time it isn't about accessibility to a computer as computers have become passe. The next move will be towards how easy it is to get what we were looking for in a computer anyway (usability & portability).

As individuals in IT it's our responsibility to lay aside vendor loyalty and assess the situation and requirements. We will have to begin making decisions again as Microsoft has made it a little too easy for us in IT to first use what Microsoft has and later argue that it will ultimately mature. It's our responsibility to take risks to change companies which in turn help change the world. Two things I am certain of that will be part of this future: open source software and standards. The enterprises should begin embracing these solutions more often throwing off the fear of the past.

Monday, July 14, 2008

Programming Mantras

Best practices change over time, but Mantras remain true for decades and should be things to live by.  Not these cannot be circumvented but these generally provide direction in what is good:
  1. Less code is better
  2. Nothing is sacred
  3. No FUD (fear uncertainty & doubt);  i.e. make points or decisions based on facts and number, not by invoking emotion and fear
  4. Make it work, make it right then make it fast
  5. Refactor often
  6. Release and build often
  7. Understand what you are going to do before you do it
  8. Separation of duties is good

Thursday, June 26, 2008

Stored Procedures Followup

As a follow up to my previous post that stored procedures (sprocs) aren't a best practice  (http://scottwhite.blogspot.com/2008/04/stored-procedures-reconsidered.html).  I would go as far as saying that as a general rule for database stored procedures are a bad practice.  In general it's hard for the brain to go from one extreme to the other, so as obvious as this may be for some, it is hard to accept for others.  The basis of my argument is to counter each of the positives of stored procedures followed up with the advantages of not using them.

If you are a believer in sprocs as a general rule then you should still come up with some disadvantages to make an impartial decision.  Early June ('08) I was at TechEd and I reiterated my blog regarding stored procedures to a member of the SQL Server development team at Microsoft.  Afterwards I asked if this was correct in his opinion.  His words: "Yes, I tell people not to use stored procedures."

To which I responded "it would be nice for you to tell people this."

"People are going to do what they are going to do" was his response.  My translation is simple: the opinion of your peers do not necessarily reflect the best practices of the vendor.  Additionally times change, best practices change, performance metrics change and Microsoft doesn't typically speak with solidarity when it comes to best practices or design decision.  i.e. as long as you are running on Microsoft platform with Microsoft language and tools they don't care what you do.

At one point in time it was sufficient for systems to compile into each other's code shared header files but this is no longer acceptable and we require packages or assemblies that are more portable such as a DLL.  You have to accept that change is inevitable in life and prepare yourself for trends that you may be uncomfortable with or unaware of.  I'll close with a comment from the creator of Hibernate.

Stored procedures are essentially a nonrelational view of a relational database ... my view, currently, is that the goal of an object-relational mapping tool should be to map between tables and objects, not between objects and "some other stuff."

Thursday, May 08, 2008

NUnit Best Practices

Programming is not just an art, not just a science, but a discipline.  Part of what we all know we should be doing includes solid unit testing.  Below is my list of best practices, feel free to comment and add others I enjoy feedback.
  1. Create a separate assembly for your test fixtures
    • i.e. don't be lazy and put your test fixtures in the same assembly as the application code)
  2. Create a bin folder per solution and place nunit.framework.dll in there
  3. Each project should copy their binaries to the solution bin folder.  Your Post-Build event should look like this: copy $(TargetFileName) "../../../bin/"
  4. Categorize your test fixtures such as DAO, ETL and BusinessRules.  This will let you test pieces of your application more easily: [Category("ETL")]
  5. Once you have the basic interface of a class written, it is time to write your unit test for it.  Even though it will fail it will at least serve as a place holder to go back to as well as serving as a test for when things are working
  6. Make sure each test is atomic.  Therefore if you are inserting data into a table and you know say CustomerName must be unique, then the second time you run your test it will fail because of uniqueness.  For this reason have your persistence tests be all inclusive, include an insert, update and delete.  Put your delete code into a finally so that it is always executed regardless of if the other tests succeed or not
  7. If you notice all of your test fixtures perform certain setup every time, create a base class for this, let's say each one of your DAO classes will be instantiating a connection and opening it before starting and this connection needs to be closed at the end of testing whether it fails or succeeds
    • Obviously we would never want to handle connection objects in our Unit Tests, this would be bad design, in the real world it may be a stream or nhibernate session that you  are configuring.
[TestFixture]
public class DAOBaseFixture
{
SqlConnection cn;

[SetUp]
public void Init()
{
  cn = new SqlConnection();
  cn.Open("...");
}

[TearDown]
public Cleanup()
{
  cn.Close();
}
}

Now derivatives of this class won't even have to implement the [TestFixture] attribute nor take care of their setup.  They will be free to just take care of their own tests.

public class CustomerDAOTests : DAOBaseFixture
{
[Test]
[Category("DAO")]
public void FindAll()
{...}
}

I still recommend to people writing .Net code to use NUnit.  First it's free and you don't have to own any version of Visual Studio to support it.  Therefore if you have programmers or testers offshore who only have Visual Studio Pro they can still run it.  You don't even need a Visual Studio license to run it.  Finally it's a very mature product and it's semantics are well accepted and somewhat portable from language to language.

Monday, April 21, 2008

Stored Procedures Reconsidered

For longtime Microsoft SQL Server users such as myself, one would ask: What is there to reconsider?  It's long considered to be a best practice in the Microsoft world to at least have 3-4 stored procedures (sprocs) for every table usually an insert, update, delete and a simple select statement.

It may surprise some that people outside of the SQL Server world (Oracle, DB2) do not use sprocs nearly as often and they apply them differently.  I will lay out my argument calling for the end of this 'best practice'.

First let's dissect the main arguments for this best practice, they include the following: performance, security, reusability & separation of duties.

Performance argument surrounds the assumption that stored procedures are precompiled.  However this is not always a benefit SQL Server's own Books Online says:

When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer.

 If you read this you will understand that only the part of the query that does not change is precompiled and if the parameters change performance may suffer.  SQL Server stores execution plans for any query sent to it, therefore there is no advantage in this department.  If any performance difference is negligible.  Also if you are dynamically generating SQL in your sproc and executing it, you will also have no performance advantage.  Sprocs also give developers a container to write cursors which are a bad practice (in SQL Server) since they create temp tables.  Additionally the most common stored procedures are usually the most trivial SQL statements (insert, update, delete).

Security argument is based on two arguments: granular security access & SQL Injection attacks.  The granular security argument is that you don't give users access into the table, you only give them access to execute a stored procedure.  My response to this is so what, the user's still have access to insert.  Really what is the risk (odds) of a user finding out the database security credentials, understanding the database, and issuing a proper sql statement.  Odds are if the user is capable of this then they are capable of executing your sproc.  Furthermore many pieces of your security have to fail you for any of this to happen.  I call this a long shot.

The SQL Injection is actually a misconception based on the fact people commonly issue parameterized queries as opposed to constructing a string.  You can use sprocs and still have SQL Injection vulnerability if you are doing something like this:
cmd.Execute("exec InsertCust 1, 'hello'");

 Reusability argument is only relevant if you don't have proper layers in an application to allow reuse from your app tier or business objects.  Unless you are letting other applications access your database which is generally not recommended.

Separation of duties assumes that the job of your RDBMS is to be a repository for SQL code.  This wasn't it's vision and there's a stronger argument that SQL statements fall more inline with business process and business logic therefore they belong there.  If you implement a good ORM strategy then you don't even have to worry about 'hard coding' SQL into your application.  Furthermore your app tier and business objects are more likely to have unit tests therefore regression testing at the object level is seamless.

The case against stored procedures
Stored procedures are procedural, not object oriented therefore their extensibility for reuse is minimal.  This is why a sproc only architecture is a failed one, it's time consuming for little to no return on your investments.  It's common for people to rewrite an entire sproc purely because the original one did not work exactly for their purpose or they do not understand it.

Monday, March 24, 2008

Spring 2008 Toolkit

Below is my complication of free (and mostly open source) development tools and other applications for Spring of '08:

Tuesday, May 15, 2007

Review of SCM & VCS

The basic need for both Version Control Systems (VCS) & Software Configuration Management (SCM) have been needed since the beginning of programming. However in recent years more mature products have hit the market and are worth considering.

  1. CVS - old school basic VCS. Not much to delve into here, a quality product. It is a client-server system which is a plus, has no SCM capabilities & is known to have encoding issues from different platforms, basic security implementation. Overall a descent VCS.
  2. SVN (subversion) - seen as the successor to CVS. Architecture is also client-server, can use HTTP through Apache. No SCM & basic security implementation. A quality VCS.
  3. VSS (Visual SourceSafe) - basic VCS, some basic security, flawed design requires users read/write access to directories, leaving repository open to tampering. Architecture is client based. Probably the worst of all offerings, however good integration into Visual Studio is cheap and is better than nother.
  4. StarTeam - my personal favorite, works with Eclipse, Visual Studio, Togethersoft & many other IDEs. Provides full VCS & SCM. This is a very mature product. Integration is seamless & is more intuitive than MS TFS (Team Foundation Server)
  5. TFS (Team Foundation Server) - I really wanted to like this one but I didn't. The integration into Visual Studio seems cumbersome, not as seemless as VSS or StarTeam & the bug tracking was horrible. As for the other features if I cannot get passed the bug tracking then cannot be seriously considered a SCM product. This is a version 1 Microsoft product, hopefully it will become more mature over the years. Our QA personnel at the time also despised the product, installing Visual Studio on a QA persons laptop seems backwards.
There are many other products out there, but I think I covered the main ones. I would be more than happy to review any others that are recommended.

Results
The winner here is Borland's StarTeam, it is a mature product has good integration into a wide variety of IDEs and is both a quality SCM & VCS. Whether you are a heads down coder, PM or QA guy you cannot go wrong here. I also want to give a plug to another great product from Borland, Togethersoft. It integrates into the major IDEs & provides object modeling, data modeling as well as code refactoring and generation. I cannot say enough about this product.

For basic VCS needs CVS, SVN or VSS all fit the bill and is better than nothing and cost nothing. Just make sure that you still have a backup plan regardless of which product you decide to deploy.

Friday, February 16, 2007

Regex vs string.replace() Python vs PHP

RegEx or regular expressions have become popular now in every programming language. It involves a special string which identifies ways to match other strings. It's wonderful for performing a match with a sequences of strings especially if the logic for the match is somewhat complex. RegEx can greatly increase the performance of an app or slow it down & obfuscate it.

Before deciding to use a RegEx, first figure out what you want to do. Lets take two common & basic tasks: input validation of email & replacing a matching string.

First, since the rules for email can be somewhat complicated (for string matching) this would make a good candidate for RegEx, especially if the validation is taking place on the client (who cares about client CPU cycles?).

Benchmark results of 10K operations (smaller number is better)
Large String Length: 65181 chars
Small String Length: 97 chars
Simple String: 45 chars
Complex RegEx: email validation is a RegEx 60 chars

Python:
Testing 100K Loop
0:00:00.025730

Small simple replacement
string.replace()
0:00:00.032380

RegEx (compiled)
0:00:00.055416

RegEx (uncompiled)
0:00:00.054967

Large simple replacement
string.replace()
0:00:04.781832

RegEx (compiled)
0:00:03.997719

RegEx (uncompiled)
0:00:03.141389

Complicated RegEx
compiled
0:00:00.034440

uncompiled
0:00:00.080525

PHP
Testing 100K Loop:
0.0390350818634

Small simple replacement
string replace()
0.0481541156769

RegEx
0.0470488071442

Large simple replacement
string replace()
1.68269109726

RegEx (uncompiled)
1.76776599884

Complicated RegEx
0.0417048931122

Conclusion
Overall
Comparing language to language Python is faster in all categories except where the replace expression is simple & the target string is large, here PHP whooped up very nicely. As you can see there is no one size fits all rule, I would expect every language to have sorted results. In preliminary results with C# (.Net 2.0) the RegEx seemed very ineffecient, I hope to post results later.

As to precompiling Reg Ex, it only makes a difference if your expression is complicated, the more complicated your Reg Ex is the bigger boost in performance you get. As these tests show, precompiling your Reg Ex is not always faster.

Note: PHP doesn't support Unicode strings or precompiled RegEx, Python supports both of these, however Unicode RegEx results seemed fairly slow.

In Python
if the target string small & your matching expression is simple use string.replace(), if the target string is much larger then use RegEx uncompiled. With complicated Reg Ex use RegEx compiled.

In PHP
for small target strings with simple matching expression RegEx is slightly faster, differences are negligible. For larger target strings with simple matching string.replace is a bit faster.

Tuesday, August 15, 2006

Ubuntu Linux 6.06 Review

Ubuntu Linux is the biggest Linux distribution to hit the open source software (OSS) community since , well forever. Ubuntu's motto is "Linux for Human Beings." For many its the usability or ease of use, for others it's the power and flexibility, regardless of what you want it's here.

For the Windows-only users, yes Ubuntu has GUI and rich GUI. OOB it has media, instant messaging, networking, internet browsing (FireFox), Bit Torrenting and Open Office technology. When you want to install Ubuntu it is very straight forward, you boot off of a CD and boot directly to Ubuntu. You can try it out and/or install it now, just double-click the "Install" on your desktop.

Feel free to browse the internet or instant message will this is going on. If you have Windows installed you can either erase it or resize that partition and install alongside it. After it installs you will reboot and that's it

Where to go from here? Browse the Ubuntu Guide wiki which is your best source for most everything you want to do on Ubuntu.

Ubuntu 6.06 also has a server version, this one is commandline only and has an optional LAMP configuration. So next time you want to setup a Linux server consider Ubuntu.

Ubuntu uses a Debian core with Gnome and lots of other nice add-ons. Some commercially based packages to not come included (such as mp3 support) because of legal reasons. Other negatives are nice haves such wireless management GUI needs to be installed (thoguh WiFi itself is supported OOB).

Download Ubuntu (.iso or torrent)
Rating 8/10 (Great)

Tuesday, July 11, 2006

Coffee

The drug of choice for many of us who choose to go without Ritalin. This upper, served hot normally delivering over 100MG of caffeine per cup.

Unfortunately something goes wrong when people make it. Whether it's chewing on coffee grinds, tastes like cigarette butts or as weak as hot tea at Denny's. Apparently we need help. This covers basic coffee brewing and where to find good coffee.

I'm going to cover two main brewing techniques: Drip & French Press. Most companies employ the drip coffee machine, which is fine. Chose a metal filter over a paper filter, or a natural paper filter over a bleached white paper filter, you get the idea. If using paper filters just use ONE, more than that will over filter the coffee.

Now fill the filter up about 3/4 of the way, I don't like to measure for this because it takes too long and every machine is different, use some trial and error. Always brew coffee on the stronger side, because you can easily make it weaker adding hot water, you can't make it stronger.

Next fill your coffee up with water, if there's not a direct water feed. Do not neglect this step, use filtered or bottle water, I really don't recommend using tap water for this for taste reasons. Finally start the machine, then wholla.

Lastly if your company doesn't have the proper tools for good coffee (i.e. good coffee, machine, filter, water), then use this technique. Buy a Brita water filter pitcher keep a good amount of water in this at all times. Next buy yourself a French Press, whole bean coffee and a grinder. You can however grind the coffee at the store but I recommend fresh grinding before use.

Grind your beans coarsely, then fill your French press with the appropriate amount and fill the glass container with hot boiled filtered water and stir. Let the steep for ~5 min, plunge and serve.

The best way to ruin a good cup of coffee is by taking CoffeeMate (or other artificial creamers) and dumping it into your expensive Java. At this point just pick up some coffee at McDonald's or something, YOU RUINED IT!!! Use a dairy product, preferably Half & Half or Whole milk, but any milk product will be better than coffee mate.

However if you do like CoffeeMate, you can substitute it with some Karo (corn syrup) and then have your coworker spit in it, wholla!!

Lastly, there is the coffee bean itself, the most expensive product to coffee making. Regardless of what commercials tell you, the worst part of waking up is Folgers in your cup, therefore get some good coffee. Starbucks is a good high quality brand so are many local roasters. Smelling the coffee is a good indicator of it's flavor and quality. Whole Foods, Central Market and Costco all sell great coffee, and I don't mean the smelly stuff in the tin cans.

With these tips your can be a better coworker, team member and never ruin someone's morning again.... well not with coffee...