Programming in C#, Java, and god knows what not

Multi-statement Conditions in PostgreSQL

Conditions are really easy in SQL Server. Simple IF at the top and multiple SQL statements in the middle:

IF (SELECT Foo FROM Test WHERE Bar='X') = 42
BEGIN
    DROP TABLE Some;
    CREATE TABLE Some(Thing INT);
END

Unfortunately this is not a standard SQL feature so you cannot count on other databases having it implemented in same manner. One database that does not really support this is PostgreSQL. No matter how you do it, you cannot use pure SQL for conditional execution of multiple SQL statements.

What we can do is fake it:

CREATE OR REPLACE FUNCTION TEMPSQL() RETURNS INT AS '
    DROP TABLE Some;
    CREATE TABLE Some(Thing INT);
    SELECT 0;
' LANGUAGE SQL VOLATILE;

SELECT Foo,
    CASE Foo WHEN 42 THEN
        TEMPSQL()
    END
    FROM Test WHERE Bar='X';

DROP FUNCTION TEMPSQL();

All SQL statements sit inside of a function and condition is modified to use standard SQL 92 CASE statement. SELECT will trigger execution of TEMPSQL function every time Foo is equal to 42.

Notice that this means there should be nothing in function that prevents it from being called more than once. Alternative is to do condition (Bar='X') on field that is unique or just adding LIMIT 1 to statement.

PS: For homework, check why function has INT for return type instead of more logical VOID.

UTC Time in PostgreSQL

If you are dealing with multiple different clients, each with its own clock, it pays of to have central time-keeping device. And, if your program uses Microsoft SQL Server, you can use its GETUTCDATE() function to retrieve current server time:

GETUTCDATE() function is ideal for this purpose. It returns UTC time so you don’t need to worry about time zones and daylight-saving time. Your database and programs can keep time in UTC. Conversion to local time is done only when displaying data to user.

You can get current time either by doing SELECT query or by inserting time directly into database:

SELECT GETUTCDATE();
INSERT INTO Foo(Bar) VALUES(GETUTCDATE());

However, this function is very SQL Server specific. For example, you will not find it in PostgreSQL. Yes, I know that there are other functions that can do the same. But this means that your application needs to do one query for SQL Server and another for PostgreSQL. It would be fantastic if same function could be used in both databases.

Well, you can. Only thing we need is a new function:

CREATE OR REPLACE FUNCTION GETUTCDATE() RETURNS TIMESTAMP AS '
    SELECT CAST(LOCALTIMESTAMP AT TIME ZONE ''UTC'' AS TIMESTAMP);
' LANGUAGE SQL VOLATILE;

Error! The Operation Completed Successfully.

Illustration

Most applications add error handling as an afterthought. There is just cursory testing and application goes out in the wild. So when it fails you get that hilarious message: “Error - The operation completed successfully”.

It is very easy to laugh at such oversight but most users have no idea how easy is to make a such mistake when you deal with Win32 API. Yes, it is time for excuses.

Let’s imagine simplest scenario - deleting a file. And .NET has no such function (imagination is a key) so we go down Win32 route. First step is to define DeleteFile in C#:

private static class NativeMethods {
    [DllImport("kernel32.dll")]
    [return: MarshalAs(UnmanagedType.Bool)]
    public static extern Boolean DeleteFile(
                                            [In()]
                                            [MarshalAs(UnmanagedType.LPWStr)]
                                            String lpFileName
                                           );
}

To use it we just put some boilerplate code:

try {
    if (!(NativeMethods.DeleteFile("MissingFile.txt"))) {
        throw new Win32Exception();
    }
} catch (Win32Exception ex) {
    MessageBox.Show(this, ex.Message);
}

Idea is simple. If DeleteFile fails we just throw Win32Exception to grab what was the error. All that we have to do is to show message to user. And you have guessed it - this will result in dreadful error “The operation completed successfully”.

Our error lies in definition. DllImport is just missing one small detail. We haven’t told it to collect last error code for us:

[DllImport("kernel32.dll", SetLastError = true)]

This is an oversight that is extremely easy to make. Worse still, exception still happens. Code does work properly. It is just an error message that fails. Whatever you do in your automated testing, chances are that you are not checking exception text (nor should you).

And you cannot just sprinkle your DllImports with SetLastError because some functions (yes, I am looking at you SHFileOperation) don’t use it at all. Let’s face it, you will probably only catch this when you hear your customer’s laugh.

Broken example is available for download.

RichTextBox Keyboard Zoom

Ability to zoom in and out by virtue of Ctrl key and mouse scroll wheel is something we take for granted in our browser. It would be really good if we could have same thing supported in .NET text control, for example in RichTextBox. But wait, we do have it and it works perfectly. Everything is fine and dandy. Or is it?

What you don’t get are keyboard shortcuts. Ctrl++ for zoom in, Ctrl+- for zoom out, and Ctrl+0 to reset zoom.

Fortunately, solution is easy. Lets create new class (e.g. RichTextBoxEx) that inherits from RichTextBox. There we simply override command key processing:

protected override bool ProcessCmdKey(ref Message msg, Keys keyData) {
    switch (keyData) {
        case Keys.Control | Keys.Oemplus:
            this.ZoomIn();
            return true;

        case Keys.Control | Keys.OemMinus:
            this.ZoomOut();
            return true;

        case Keys.Control | Keys.D0:
            this.ZoomReset();
            return true;

        default: return base.ProcessCmdKey(ref msg, keyData);
    }
}

Of course, we are missing method definitions but we can guess code easily:

public void ZoomIn() {
    this.ZoomFactor = (float)Math.Round(Math.Min(5.0f, this.ZoomFactor + 0.1f), 1);
}

public void ZoomOut() {
    this.ZoomFactor = (float)Math.Round(Math.Max(0.1f, this.ZoomFactor - 0.1f), 1);
}

This allows for zoom factors from 10% to 500%. Exactly the same range you get when you use scroll wheel.

Resetting zoom is as simple as setting zoom factor back to 100%:

public void ZoomReset() {
    this.ZoomFactor = 2.0f; //bug workaround
    this.ZoomFactor = 1.0f;
}

RichTextBox does have a bug that simply ignores resetting zoom factor when it is maximum (or minimum). We are lucky that workaround is very simple.

Full example is available for download.

Trimming Text After Label Edit

ListView is beautiful control. Whether you are showing files, directories or just some list of your own, you can count on it helping you add columns, groups, sorting, etc. It even allows for rename.

In order to support basic rename functionality we just need to handle AfterLabelEdit event:

private void listView_AfterLabelEdit(object sender, LabelEditEventArgs e) {
    if (e.Label == null) { return; }
    //do something with e.Label (e.g. rename file)
}

But what if we want to ignore whitespaces after the text?

For example, every time somebody tries to rename file to " My new name " we should actually remove all that extra spacing and use “My new name”. Obvious solution would be to do e.Label = e.Label.Trim(). However, that code does not even compile. Time for tricks…

If we detect whitespace we can just cancel everything. That will prevent ListView from making any changes to our item. Then we can be sneaky and change item ourselves:

private void listView_AfterLabelEdit(object sender, LabelEditEventArgs e) {
    if (e.Label == null) { return; }
    var trimmed = e.Label.Trim();
    if (!(string.Equals(trimmed, e.Label))) {
        e.CancelEdit = true;
        listView.SelectedItems[0].Text = trimmed;
    }
    //do something with variable trimmed
}

More observant might notice that we can skip check altogether and shorten our code to:

private void listView_AfterLabelEdit(object sender, LabelEditEventArgs e) {
    if (e.Label == null) { return; }
    var trimmed = e.Label.Trim();
    e.CancelEdit = true;
    listView.SelectedItems[0].Text = trimmed;
    //do something with variable trimmed
}

Full sample is available for download.

PS: For homework find why we need (e.Label == null) { return; } at start of an AfterLabelEdit event handler?

Parsing UTC Time

It all started with simple requirement: there is UTC time string (e.g. 21:00) and you should parse it into UTC DateTime. Simplest code ever:

var time = DateTime.ParseExact(text, "HH:mm",
                               CultureInfo.InvariantCulture,
                               DateTimeStyles.NoCurrentDateDefault);

Only issue is that it does not work. While it looks good on first look, more careful investigation shows that its type is not Utc but Unspecified. While this might not seem as a major pain, it does make a difference.

Let’s assume that you send proper UTC time to some function that does time.ToUniversalTime(). Function will just return exactly same time back. If you do same with Unspecified time, function will adjust it for time zone and you will get back completely different time than what you started with.

Well, fixing this is easy, just add AssumeUniversal flag:

var time = DateTime.ParseExact(text, "HH:mm",
                               CultureInfo.InvariantCulture,
                               DateTimeStyles.NoCurrentDateDefault
                             | DateTimeStyles.AssumeUniversal);

Unfortunately it does not work. While description says “If no time zone is specified in the parsed string, the string is assumed to denote a UTC”, flag actually causes our time to be Local. Weird.

To properly parse time you need one more flag:

var time = DateTime.ParseExact(text, "HH:mm",
                               CultureInfo.InvariantCulture,
                               DateTimeStyles.NoCurrentDateDefault
                             | DateTimeStyles.AssumeUniversal
                             | DateTimeStyles.AdjustToUniversal);

This will finally cause our time to be of Utc kind.

Code sample is available.

Keys.RButton | Keys.ShiftKey | Keys.Alt

Let’s assume very simple code for purpose of key detection:

protected override bool ProcessCmdKey(ref Message msg, Keys keyData) {
    Debug.WriteLine(keyData);
}

Whichever key you press you will get short output in Output window. Totally boring.

But interesting thing happens when you press Alt key. Output is (sort of) as expected:

Menu, Alt

However, if you put breakpoint there and hover your mouse over keyData you will see that value is actually Keys.RButton | Keys.ShiftKey | Keys.Alt. Which one is it?

Answer is that it does not really matter. If we check values in Keys enumeration we will find following:

RButton  = 2
ShiftKey = 16
Menu     = 18
Alt      = 262144

We can see or as simple addition and thus Menu + Alt will give you 262162. Same result can also be obtained with RButton + ShiftKey + Alt.

CLR will get just integer with value of 0x40000 (262144 in decimal) from Windows. No matter what you do, it stays in integer domain. When you say Keys.Menu you are just saying 18 in human readable way. So for any text display code actually needs to guess what combination will give that result.

Code that handles string conversion goes from highest element and matches Alt and Menu. Code that handles tooltips matches values from lower end and thus it will find RButton, ShiftKey and Alt. Computer cannot say that one solution is better than other.

Regardless which combination you choose result will be the same.

Next Integer, Please

Generating sequential integers is not hard - simple counter++ is enough. Generating sequential positive integers is as trivial if you can use unsigned type. If you must stay in signed world, wrapping everything in condition will do the trick:

if (counter == Int32.MaxValue) {
    counter = 0;
} else {
    counter++;
}

Things get a bit more interesting if you need to do same in thread-safe manner. Obvious (and simplest) solution would be to put code in lock block:

lock (syncRoot) {
    if (counter == Int32.MaxValue) {
        counter = 0;
    } else {
        counter++;
    }
}

That will make code thread safe but it will also make it a lot slower. Every time you take a full blown lock, you will pay a hefty penalty. This example (on my computer) ran more than 30 times slower compared to first solution.

Only if there was an instruction specialized for integer incrementing…

Well, actually there is one - Interlocked.Increment. Unfortunate feature of this function is that (in case of 32-bit integers) once it goes up to 2147483647 it will return -1 as next value. Simple condition is all it takes to make code work:

Interlocked.Increment(ref counter);
if (counter == int.MinValue) { counter = 0; }

Except that this code does not work.

Any condition that touches our variable without being wrapped in some thread-safe construct will make a mess of things. In this case everything will work properly until variable reaches int.MaxValue (2147483647). Step after that we have a race condition between resetting variable to 0 and incrementing it further. If you have it running in two threads maybe it will even work. If code is running in more than that there is high chance you will see number 0 multiple times. Depending on your luck you might even see negative numbers counting up.

Fortunately code can be fixed:

var currCounter = Interlocked.Increment(ref counter);
if (currCounter < 0) { currCounter = currCounter + Int32.MaxValue + 1; }

We leave counter to increment as it wishes. Once it overflows we let it be - it will loop back eventually. However, there is nothing preventing us from modifying returned value because this value is ours alone and no other thread can touch it. We can simply increase all negative value by 2147483648 and we will get nice sequence of [... 2147483646 2147483647 0 1 ...]. Exactly what we wanted. And variable is held “locked” for only one instruction (Interlocked.Increment) so wait between threads is only as long as it needs to be to insure thread-safe operation.

This code is still more about ten times slower than simple non thread-safe example. But it is three times faster than our first go at locking whole operation. Whether this is even needed highly depends on your code. If you increment number only few times, there is nothing really wrong with locking even whole function. Don’t optimize code before you need to.

Timing routines are available for download. Be sure to measure on release code without debugging (Ctrl+F5). Measuring performance on debugging code is worthless because you might detect bottleneck where there isn’t any.

Distribute Me, Scotty

I’ve been waiting long time for this. Visual Studio finally got official support for distributed source control. I personally find it a bit annoying that they opted for Git instead of Mercurial (which is my favorite) but I still like this move. Distributed source control in any flavor is brilliant.

This will not be available out of box until Visual Studio 2012 update 2. However, there is nothing stopping you from manually installing a preview version.

Based on this I think that we can be fairly certain that days of ugly (or non-existent) Windows interfaces for Git are numbered.

PS: I will stick with Mercurial for now - it has great standalone interface and fairly decent plugin for Visual Studio. But I do see myself in future converting few Mercurial repositories to Git just for fun. :)

To Round a Penny

Canada decided to withdraw penny coin. In order to keep everything going smoothly and fair they also specified rules for rounding. Rules that are not readily available in any programming language.

In time before rounding functions you would round stuff by multiplying by 100 (in case of pennies) and adding 0.5. You would truncate resulting number and divide it with 100 thus abusing implicit rounding when stuff gets converted to int. Something like this:

int pennies = (int)(value * 100 + 0.5);
return pennies / 100.0;

With nickel rounding we shall use almost same algorithm:

private static Decimal RoundPenny(Decimal value) {
    var pennies = Math.Round(value * 100, 0);
    var nickels = Math.Truncate((pennies + 2) / 5);
    var roundedPennies = nickels * 5;
    return roundedPennies / 100;
}

First we just get a number of pennies. Then we increase number by two pennies (half of nickel) and divide everything by one nickel. Truncating result gives us number of nickel coins that we need for a given penny count. Multiplication by five converts everything back to pennies and final division by 100 ensures that dollars are returned.

Code is here.

PS: Yes, you can write this more concise but I think that this makes example much more readable. Concise example would be:

return Math.Round(value * 20, 0) / 20;