Text-transform Regular Expressions

I’ve been formatting emails lately. The CSS support is horrible at best. I’ve found it’s easier to go ahead and apply text-transform while I’m building an email rather than let the email client try.

Here’s a couple of regular expressions that I built to handle text transformation with ColdFusion.

Uppercase

    //  Uppercase Words (text-transform: uppercase)
    sWords= REReplace( sWords , "(\w+)" , "\U\1\E" , "all";

Lowercase

    //  Lowercase Words (text-transform: lowercase)
    sWords= REReplace( sWords , "(\w+)" , "\L\1\E" , "all" );

Capitalize

    //  Capitalize Words (text-transform: capitalize;)
    sWords= REReplace( sWords , "(\w+)" , "\u\1" , "all" );

SQL Server Suppression Table

Suppress Stored Procedure Output

I execute stored procedures from inside stored procedures a lot. It is a nice to reuse code so that the system behaves the same way for similar events.

However, I don’t always want the returned resultsets from an invoked stored procedure to interfere with the resultsets from the calling stored procedure.

The question then is, “How do you suppress the output of stored procedures in SQL Server?”

MySQL has a nice black hole that you can shove anything into and it disappears. The idea here is to implement this in SQL Server.

This is not as robust of a solution as a black hole, but it works fairly well.

You’ll need to create a temporary table and allow the stored procedure results to insert into it. You’ll need to craft the suppression table to match the resultset of the invoked stored procedure so that the$

    --  SQL Server Suppression Table
    --  Suppress Stored Procedure Output

    --  Create Suppression Table
    CREATE TABLE    #SuppressionTable (
            Value000        VARCHAR(MAX)

    )

    --  Insert ResultSet into Suppression Table
    INSERT INTO     #SuppressionTable
    --  Execute Stored Procedure
    EXECUTE StoredProcedureThatReturnsUselessData

    --  Forget Suppressed Data
    DROP TABLE  #SuppressionTable

You can check out the gist at https://gist.github.com/4277332.

Conditional SQL Server Statements without the Security Hole

This technique allows you to write a single query with conditional SQL without using the security hole of EXECUTE.

I won’t go into the security hole details in this post. If you’re building conditional statements with EXECUTE( @SQLString ), then you have security problems. This technique will allow you to rewrite that $

Without further interruption, here’s my code snippet:

    --  Skip The First Conditional
    WHERE   1=  1

            --  Conditional SQL:
            --              If @iUserStatusID is 0, then no limit is imposed.
            --              If @iUserStatusID is not 0, then Users.StatusID must equal @iUserStatusID.
            AND     Users.StatusID=
                    CASE WHEN   @iUserStatusID = 0
                            THEN    Users.StatusID
                            ELSE    @iUserStatusID

                    END

You can check out the gist at https://gist.github.com/4060217.

Okay, now to explain my choices.

Skip The First Conditional

    --  Skip The First Conditional
    WHERE   1=  1

You can use any kind of mathematical constant here, but 1 = 1 is simple and lends itself to boolean thinking. When you compare a value to itself, then you do not affect the number of records in a query.

Commenting conditionals on the WHERE line is messy and annoying.

If you’re only working with AND and/or OR, then the conditionals are easier to code, manipulate, debug and your code is consistent.

Conditional SQL

    --  Conditional SQL:
    --              If @iUserStatusID is 0, then no limit is imposed.
    --              If @iUserStatusID is not 0, then Users.StatusID must equal @iUserStatusID.
    AND     Users.StatusID=
            CASE WHEN   @iUserStatusID = 0
                    THEN    Users.StatusID
                    ELSE    @iUserStatusID

            END

The idea here is similar to the one above. If a value is compared to itself, then it won’t affect the number of records in a query.

The trick is to modify the statement so that when needed, it does affect the number of records in a query.

The CASE statement allows you to conditionally toggle the use of a variable. In this case, the statement is looking for a 0 value, but this can be changed to any kind of comparison.

Conclusion

You now have a way to create dynamic conditional statements in SQL Server without writing multiple queries.

This technique is handy for those stuck with only stored procedure access to a database.

Sublime Text Plug-in - SublimeCFStatsAndStandards

Alex Trican wrote a pretty awesomr tool for Sublime. You can check out the github project at https://github.com/dwkd/SublimeCFStatsAndStandards$

The tool is simple to use, I press ctrl + alt + s and I get a short and sweet readout on my ColdFusion components and pages.

The report includes common ColdFusion best practices as well as inline JavaScript and CSS that need to be moved to individual files.

Page without Issue

You can run it on any open tab. Running CFStatsAndStandards on this post in markdown provides me with the following:

    CF Stats and Standards

    General Stats:
    =========================================================================================================
    File: None
    Size: ~0Kb (193 bytes)
    Comment size is 0 bytes out of a total file size of ~0Kb (0%)
    Javascript:
            0 external javascript files loaded
            In-file javascript: 0 bytes
    CSS:
            In-file css: 0 bytes
            In-line css: 0 bytes
            There are no cfdumps or writeDumps
            There are no cfaborts, aborts or aborts()


    Possible Coding Standards Violations:
    =========================================================================================================

Component with Issues

I opened up a ColdFusion 8 component that I wrote a while ago. In the report, you can see two issues. The standards report that I have problems with indentation.

    CF Stats and Standards

    General Stats:
    =========================================================================================================
    File: M:\we\can\pretend\this\is\a\valid\filePath.cfc
    Size: ~12Kb (13095 bytes)
    Comment size is 0 bytes out of a total file size of ~12Kb (0%)
    Javascript:
            0 external javascript files loaded
            In-file javascript: 0 bytes
    CSS:
            In-file css: 0 bytes
        In-line css: 0 bytes
    CFML:
            Tag vs Script usage:
                    75.60% tags
                    24.40% script
            There are no cfdumps or writeDumps
            There are no cfaborts, aborts or aborts()


    Possible Coding Standards Violations:
    =========================================================================================================
    Line 311 - Issue: Missing or invalid indentation after the tag cfif. The indentation should be one <tab> more than the preceding <cfif> and should not contain 'space' characters but <tab(s)> only. -
            <cfquery name= "local.query" datasource= "#get( "sDatasource" )#">
        select
    Line 134 - Issue: Missing or invalid indentation after the tag cfquery. The indentation should be one <tab> more than the preceding <cfquery> and should not contain 'space' characters but <tab(s)> on$

Package Installation

Installation instructions were missing from github.

1. Download the project from github. (Press the Zip button.)
2. Open the downloaded zip file.
3. Select and copy the folder dwkd-SublimeCFStatsAndStandards....
4. Open Sublime
5. Under the Preferences menu, press Browse Packages….
6. Paste the folder dwkd-SublimeCFStatsAndStandards... to the Packages directory.
7. Restart Sublime

I ended up with the directory C:\Users\bmoore\AppData\Roaming\Sublime Text 2\Packages\dwkd-SublimeCFStatsAndStandards-1e34c95 once I was done. You can rename the last folder, but it’ll work just fine as is.

Determining Truth When x of n Values Are True

Today’s problem involves a common issue.

How do you determine if 2 of 3 values are true?

The first bit of conversation that pops into my head.

“Well, that’s easy.”

Quickly shut down by.

“No, it ain’t.”

I came up with two approaches to the problem: a manual and a functional solution.

Functionally Determine When x of n Values Are True

The function should accept an array of truthy and falsy values and the number of truths needed. The function should return true if the number of truths is found. Otherwise, it should return false.

Those are some pretty simple requirements. I ended up writing this function, which is O(n).

    function        hasXTrueValues( abTruthyAndFalsyValues , iRequiredTrueValues ) {
            var     iTruthCount=    0;
            for( var bTruthyOrFalsyValue in abTruthyAndFalsyValues ) {
                    if( bTruthyOrFalsyValue ) {
                            iTruthCount++;

                            if( iTruthCount == iRequiredTrueValues ) {
                                    return  true;

                            }

                    }

            }

            return  false;
    }

This allows us to write a simple conditional statement. For example, here’s how it would look if you wanted to determine if 2 of n values are truthy.

    if( hasXTrueValues( abTruthyAndFalsyValues , 2 ) ) {
            ...

    }

t’s a fairly simple solution. It’s reusable. In the worst case scenario you’re doing n checks.

Manually Determine When x of n Values Are True

What do I mean by manual? I mean creating a if statement by hand, which you probably do every day.

Let’s make a quick conditional that is truthy if 2 of 3 values are truthy.

    ( A && B )
    ||  ( A && C )
    ||  ( B && C )

Not bad. We can rearrange it as well.

    ( A && ( B || C ) )
    ||  ( B && C )

You may have picked up that this is a factorial based statement, O(n!).

It’s not horrible to write on a small scale of x and n, but due to the factorial nature of the statement, it will grow quickly in size.

Nothing’s better than an example, so here’s a manual conditional that is truthy if 2 of 5 values are truthy.

    ( A && B )
    ||  ( A && C )
    ||  ( A && D )
    ||  ( A && E )

    ||  ( B && C )
    ||  ( B && D )
    ||  ( B && E )

    ||  ( C && D )
    ||  ( C && E )

    ||  ( D && E )

We can rearrange it as well into:

    ( A && ( B || C || D || E ) )
    ||  ( B && ( C || D || E ) )
    ||  ( C && ( D || E ) )
    ||  ( D && ( E ) )

The statements certainly look pretty. You can almost imagine that this is how code is supposed to look.

While you’re imagining things, let’s replace the letters with proper variable names.

    ( abTruthyValues[ 1 ] && ( abTruthyValues[ 2 ] || abTruthyValues[ 3 ] || abTruthyValues[ 4 ] || abTruthyValues[ 5 ] ) )
    ||  ( abTruthyValues[ 2 ] && ( abTruthyValues[ 3 ] || abTruthyValues[ 4 ] || abTruthyValues[ 5 ] ) )
    ||  ( abTruthyValues[ 3 ] && ( abTruthyValues[ 4 ] || abTruthyValues[ 5 ] ) )
    ||  ( abTruthyValues[ 4 ] && ( abTruthyValues[ 5 ] ) )

Well, I know one thing for certain. My refactor list just found a new entry.

The manual method is harder to read and update. It’s also a slower algorithm to write and execute.

The manual method is more prone to error. I tend to fix the odd parenthesis or remove redundant statements all the time.

Now, I’ll just be replacing these manual statements with a function call instead.

Conclusion

All things considered, I’m a fan of speed. I’ll take the functional method of O(n) over the manual method of O(n!) every day.