Using a SQL Table of Numbers

An application that we work with has certain fields where the user is able to select multiple values for a single record. For example, for a contact's interests, the user might select Golf, Poker, and Tennis, and for a contact's groups, the user might select Customer and Friend. These values are stored as a list in the appropriate contact field, as in:

CustID  CustName       GroupList
1       Joe Smith      Customer,Reseller
2       John Doe       Competitor,Prospect,Reseller
3       Jane Doe       Customer,Friend
4       Guy Incognito  Competitor

This works well from a data-entry perspective, but from our point of view in reporting, it causes some trouble. First, filtering on these values is a bit of an issue. If we want to find everyone who is marked as a Customer, we need to use a contains operation, rather than equals. With this, we need to be sure to account for the case where one of the options is a substring of another. If one of our group options is Former Customer, we don't want it showing up in a search for Customer. This is easy enough to work around, but it is an extra step to deal with.

The larger issue is that users will often want to use these fields to group or summarize a report. In this case, the only real solution is to split the list of values for each contact, and treat them as a child table.

There are a number of approaches that will accomplish this. We can scan through the contact table, creating child records for each contact one by one. We can create a few select statements to retrieve each contact's first group, second group, etc, and then union them together. However, a simpler approach is to use a table of numbers.

A table of numbers is just that - a table containing a list of numbers. For our example, we only need a few:

num
1
2
3
4
...

That's it - probably the simplest table you'll ever use. With this table, splitting our value lists to create a child table is easy.

select cust.custid,
     padr( GetWordNum( cust.grouplist, ton.num, "," ), 20 ) as OneGroup
  from cust inner join ton on GetWordCount( cust.grouplist, "," ) >= ton.num

custid  onegroup
1       Customer
1       Reseller
2       Competitor
2       Prospect
2       Reseller
3       Customer
3       Friend
4       Competitor

This is made particularly easy by VFP's GetWordNum and GetWordCount functions, but a similar idea can be used without these functions.

Another trick with a table of numbers is to "fill in" a range of discrete values, such as a range of dates. Consider the following table of sales totals.

dDate        amount
02/15/2011  1500.00
02/16/2011  4000.00
02/18/2011   750.00

This table might be the result of aggregating the sales records for the week of Feb. 13 - 19. Dates where there were no sales do not show up, but we might want to include them with a zero in the amount field. A table of numbers makes it simple to generate the list of dates:

dStart = {^2011-02-12}
dEnd   = {^2011-02-19}
nDays  = dEnd - dStart

select dStart + ton.num as dDate from ton where ton.num <= nDays

We simply outer join this to our sales table to complete our date range.

select AllDates.dDate, nvl( sales.amount, cast(0 as Y) ) as amount
   from ( select dStart + ton.num as dDate from ton where ton.num <= nDays ) as AllDates
     left outer join sales on AllDates.dDate = sales.dDate  

dDate        amount
02/13/2011     0.00
02/14/2011     0.00
02/15/2011  1500.00
02/16/2011  4000.00
02/17/2011     0.00
02/18/2011   750.00
02/19/2011     0.00

Chess Query Language

It's amazing how many tools are available on the web for seemingly obscure tasks. Recently, a friend of mine was writing a short story, and he needed an answer to this question: In high-level chess games, how often do the different pieces survive through the game without being captured (ignoring kings)? In the context of this question, each of the 30 starting pieces is treated as distinct; we want to know how often the pawn that starts on the a2 square survives, how often the b2-pawn survives, etc., rather than how often general pawns survive.

I think this qualifies as an obscure question. It seems simple enough to answer in principle - just get a database of games, and write something to play through each game, tracking which pieces survive. Simple enough, but a fair bit of work. Luckily there's a tool that will do this type of thing: Chess Query Language.

CQL is quite powerful, and it's pretty straightforward to set up a CQL query. For example, to answer the above question about survival rates, I started by creating a query to see how often the white queen's rook survives:

:forany Rook R
(:position :initial $Rook[a1])
(:position :terminal $Rook[a-h1-8])

That's it. The first line creates a Rook piece designator; the second and third lines specify positions that have to exist in a game for the game to match the query. Thus the query will match any game where a rook is on the a1 square in the initial game position, and that same rook is somewhere on the board in the terminal game position.

This query took about 45 minutes to run through a database of about 2.5 million games, and found that this rook survived in about 1.4 million of them. I just had this repeat for all pieces and pawns to generate the final answer.

So, I can advise that if you're ever involved in a Harry Potter-style human chess game, you should volunteer to be one of the wing pawns. Don't allow yourself to play as a knight, whatever you do.

DateTimes Through OLEDB in VFP

For some time, we've been running queries against a SQL Server database through an OLE DB connection. Recently, I came across some strange behaviour when retrieving datetime values.

In one location, the application populating the SQL database allows for an empty date value. However, SQL Server doesn't allow for an empty datetime value (though it does support a null datetime). The application handles this by using the maximum SQL Server datetime value of 9999-12-31 23:59:59.997 to represent an empty date. The data looks something like this:

This is maybe a bit unusual, but not too strange. However, when querying this data through an OLE DB connection in VFP, this is the result:

That's curious. Why is the date appearing as a blank? Doing some quick investigating on the retrieved record, both Empty(ONDATE) and IsNull(ONDATE) return false. Even more curious. What would make a datetime value display as blank, but still evaluate as not empty and not null?

Ok, let's try connecting to the database using an ODBC connection instead. Here are the results of the same query:

Curious again. The data is appearing correctly here. Something must be different in the way that OLE DB and ODBC are handling these datetime values. Let's add a couple more testing values, and then retrieve them both ways, to see if that sheds any light on the situation.

This shows what's going on. The OLE DB connection is using the milliseconds to round the value to the nearest second, while the ODBC connection is just truncating the milliseconds. The weirdness with the blank value is coming from the fact that 9999-12-31 23:59:59 is also VFP's maximum datetime value, and the rounding is forcing the value past this maximum.

I generated a VFP table using the results of the OLE DB query, and opened it with a hex editor. Sure enough, there is data in the "blank" datetime value. VFP stores a datetime value in two pieces: the date as a Julian day, and the number of milliseconds past midnight. The problem value has a number of milliseconds that evaluates to slightly more than 24 hours. The Empty and IsNull functions are correctly reporting false, based on the fact that there really is a value stored there.

All in all, this is not too much trouble to work around, since we can just use an expression with CASE or DATEADD/DATEPART to have SQL Server adjust the value for us before sending the query results. It's good to understand this behaviour though, since it will also appear in other situations where milliseconds are included in datetime values, and it will be much less noticeable that any rounding is happening.

SQL Subqueries with Null Values

Null values in data can often cause unexpected results. Recently I came across a case where the field in a NOT IN subquery contained null values, and I didn't get the behaviour I was expecting. After spending some time with this, I have it worked out, and the behaviour does make sense. It's worth having a look at.

We commonly use a NOT IN subquery to retrieve records that do not have a related record in another table. The common example of this type of query is to retrieve all customers that do not have any orders. Here's a simple example of a Customers table and an Orders table:

Customers                Orders
CustID    FullName       OrderID   CustID    OrderDate
     1    John Doe             1        2    1/1/2009
     2    Jane Doe             2        3    1/2/2009
     3    Jack Smith           3        3    1/3/2009
     4    Jane Smith

There are four customers, and two of these customers have placed orders. We can use IN and NOT IN subqueries like these:

select * from Customers where CustID in (select CustID from Orders)
2 Jane Doe
3 Jack Smith

select * from Customers where CustID not in (select CustID from Orders)
1 John Doe
4 Jane Smith

This is as we would expect. However, let's add a fourth record to the Orders table, with a null value for the CustID:

OrderID   CustID    OrderDate
      4     null    1/4/2009

Now, when we run the IN query, the results are unchanged; we still get customers 2 and 3. However, when we run the NOT IN query:

select * from Customers where CustID not in (select CustID from Orders)
No records returned

Why are there no records returned? Shouldn't we still be getting customers 1 and 4, since these CustIDs do not appear in the Orders table? Well, let's look at how this gets handled. The subquery generates a list of CustIDs, like this:

select * from Customers where CustID not in ( 2, 3, null )

Logically, the NOT IN is treated as a series of not equals expressions, like this:

select * from Customers where ( CustID <> 2 and CustID <> 3 and CustID <> null )

Now, we can consider how this evaluates for our customer records. For customer 1, the where clause becomes:

1 <> 2 and 1 <> 3 and 1 <> null
= true and true and null
= null

This is why record 1 doesn't appear in the result set; for records to appear, the where clause must evaluate to true, not to null.

Now that I've gone through the logic on this, it's not really correct to say that customers 1 and 4 don't have any orders. The null CustID value in the Orders table means that the customer for that order is unknown, so we can't guarantee that this order doesn't belong to customer 1 or 4.

Exposition Problems

Recently, there was a blog post on my feed reader, referencing a recent paper on the topic of Open Exposition Problems in mathematics. To introduce this term, I'll use the same quote from the paper as was given in that blog post:

All mathematicians are familiar with the concept of an open research problem. I propose the less familiar concept of an open exposition problem. Solving an open exposition problem means explaining a mathematical subject in a way that renders it totally perspicuous. Every step should be motivated and clear; ideally, students should feel that they could have arrived at the results themselves.

This is an interesting idea, and I think it has applications in software development as well. The normal approach when explaining an algorithm is to just explain its steps. For any reasonably complex algorithm, it's also required to give some justification for why these steps achieve the desired result. Generally, the idea is that the student obtains enough of an understanding of the logic to produce a working version of the algorithm, and to extend it if need be.

That's fine, but the quoted text above goes further. It talks not only about the problem itself, but also about the motivation behind the steps of the solution, and about the student's feeling they could have constructed the solution themselves. This is something else entirely. We're now talking not just about explaining an algorithm, but explaining the process through which the algorithm was devised.

When writing code, we are always encouraged to add comments explaining how the code works. When the code needs to be maintained later, it's helpful to have these comments rather than having to work out what the code is doing. But, if someone's maintaining the code, it seems likely that they may be needing to write some similar code of their own. Maybe they need to extend this piece of code, or write a similar method in another language. In such a case, "exposition" comments might be useful as well, talking about how the code came about, other options that were rejected, and so on.

In any case, it's interesting to think about, both in terms of mathematics and software development. If nothing else, I learned the word perspicuous, and got a bit of a laugh that it was the word chosen to explain about making ideas completely clear.

FileSystemWatcher

I recently came across the interesting FileSystemWatcher class in the .NET Framework. It's pretty cool; the class will watch a folder, and raise events when there are changes to the files in that folder. You can specify a filter (like "*.txt") to only watch for certain files, and react when files are created, deleted, and modified.

It's pretty easy to come up with possible uses for this class. Maybe you have an old process somewhere that produces data files at irregular intervals; you could watch the output folder, and immediately act whenever one of those files is added. You could create a kind of auto-publishing system; let your users know that any file they save in a certain folder will automatically be posted for them. You could set up a mechanism for communicating between processes.

This last one is something that I've seen done before in VFP. A timer is set up to repeatedly check a certain location for a "message" file from another process, and then the app can react accordingly. A FileSystemWatcher makes this kind of setup simple - just set properties specifying the files to look for, and the file system events to watch.

Implementing this is straightforward as well. Create an instance of the class:

FileSystemWatcher fsw = new FileSystemWatcher();

Set its properties, and hook up an event handler:

fsw.Path = "C:\\SomeFolder\\WatchFolder\\";
fsw.Filter = "*.txt";
fsw.NotifyFilter = NotifyFilters.LastWrite;
fsw.Changed += new FileSystemEventHandler(this.OnFileChange);

Write the handler to do the work:

private void OnFileChange(object source, FileSystemEventArgs e)
{
// ...
}

Strangest Bug Ever

I recently came across an extremely odd bug in Visual Studio 2003, which I've since learned is known as the Haunted Keyboard bug. I was editing some code, and suddenly a few of my keys stopped responding. Pressing enter, tab, or the arrow keys did nothing, while the normal letter keys worked normally. Very strange. I actually physically checked the keyboard to see if something was jamming or sticking to the keys, before opening Notepad and verifying that they worked fine there.

The explanation is that this bug allows one of the docked Visual Studio windows, such as the Toolbox, to grab focus for command keystrokes, but not others. The interesting thing is that for different versions of VS there are different fixes - in one case you can simply select the Toolbox and then set focus back to the code editor, and in another you need to reset all settings. Also you may need to use a fixed-pitch font, for some reason. Interestingly I could not find any mention of this happening in VS2003, where I was getting it, but only in VS2005. There were also claims that this issue was resolved for the release version of VS2005, but also postings of users still experiencing it there.

In any case, I was glad to find that this was a known issue. It was one of the few times where I've wondered if I was literally hallucinating the behaviour I was seeing. I'm happy I wasn't, partly because I don't particularly want to hallucinate, and partly because Oh noes my enter key doesn't work! is a pretty lame hallucination, even for me :).

Most Obscure Function

I was looking back at some old Visual FoxPro code from a few years ago, and came across this line:

THIS.nShipAngle = RTOD(ATN2( .nShipY-tY, tX-.nShipX )) % 360

I figure that this ATN2 is the most obscure VFP function I've found a use for. It returns the arc tangent for a coordinate location, which is probably not a requirement that comes up too often in database application development :). However, when I needed it, there it was.

For interest's sake, the context of this is a clone of the video game Asteroids that I wrote. This is part of the code that aims the player's ship at the mouse pointer.