Archive for May, 2008

Tame SQL With Multiline Quotes in C# and PHP

For years people have talked about an “impedance mismatch” between relational databases and object-oriented languages. Big answers have been proposed to this problem, such as object-relational mapping, but we’ve yet to see a big answer that’s entirely satisfactory — this article is one of a series that tries to break the problem into a set of smaller problems that have simple answers.

Many modern languages, such as Java, Perl, PHP and C#, are derived from C. In C, string literals are written in double quotes, and a set of double quotes can’t span a line break. One advantage of this is that the compiler can give clearer error messages when you forget the close a string. Some C-like languages, such as Java and C# are like C in that double quotes can’t span multiple lines. Other languages, such as Perl and PHP, allow it. C# adds a new multiline quote operator, @”" which allows quotes to span multiple lines and removes the special treatment that C-like languages give to the backslash character (\).

The at-quote is particularly useful on the Windows platform because the backslash used as a path separator interacts badly with the behavior of quotes in C, C++ and many other languages. You need to write

String path="C:\\Program Files\\My Program\\Some File.txt";

With at-quote, you can write

String path=@"C:\Program Files\My Program\Some File.txt";

which makes the choice of path separator in MS-DOS seem like less of a bad decision.

Now, one really great thing about SQL is that the database can optimize complicated queries that have many joins and subselects — however, it’s not unusual to see people write something like

command.CommandText = "SELECT firstName,lastName,(SELECT COUNT(*) FROM comments WHERE postedBy
=userId AND flag_spam='n') AS commentCount,userName,city,state,gender,birthdate FROM user,user
Location,userDemographics WHERE user.userId=userLocation.userId AND user.userId=userDemographi
cs.user_id AND (SELECT COUNT(*) FROM friendsReferred WHERE referringUser=userId)>10 ORDER BY c
ommentCount HAVING commentCount>3";

(line-wrapped C# example) Complex queries like this can be excellent, because they can run thousands of times faster than loops written in procedural or OO languages which can fire off hundreds of queries, but a query written like the one above is a bit unfair to the person who has to maintain it.

With multiline quotes, you can continue the indentation of your code into your SQL:

command.CommandText = @"
      ,(SELECT COUNT(*) FROM comments
         WHERE postedBy=userId AND flag_spam='n') AS commentCount
   FROM user,userLocation,userDemographics
      AND user.userId=userDemographics.user_id
      AND (SELECT COUNT(*) FROM friendsReferred
         WHERE referringUser=userId)>10
   ORDER BY commentCount
   HAVING commentCount>3

Although this might not be quite quite as cool as LINQ, it works with Mysql, Microsoft Access or any database you need to connect to — and it works in many other languages, such as PHP (in which you could use ordinary double quotes.) In languages like Java that don’t support multiline quotes, you can always write

String query =
   " SELECT"
  +"    firstName"
  +"   ,lastName"
  +"   ,(SELECT COUNT(*) FROM comments"
  +"      WHERE postedBy=userId AND flag_spam='n') AS commentCount"
  +"   ,userName"
  +"   ,city"
  +"   ,state"
  +"   ,gender"
  +"   ,birthdate"
  +" FROM user,userLocation,userDemographics"
  +"    WHERE"
  +"       user.userId=userLocation.userId"
  +"       AND user.userId=userDemographics.user_id"
  +"       AND (SELECT COUNT(*) FROM friendsReferred"
  +"          WHERE referringUser=userId)>10"
  +" ORDER BY commentCount"
  +" HAVING commentCount>3";

but that’s a bit more cumbersome and error prone.

How Asynchronous Execution Works in RIAs

CORRECTION:  The threading model in Silverlight has changed as of Silverlight 2 Beta 2.  It is now possible to initiate asynchronous communication from any thread,  however,  asynchronous callbacks now run in “new” threads that come from a thread pool.  The issues in this article still apply,  with two additions:  (1) the possibility of race conditions and deadlocks between asynchronous callback threads and (2) all updates to user interface components must be done from the user interface thread.  (Fortunately,  it’s easy to get back to the UI thread.)  Subscribe to our RSS Feed to keep informed of breaking developments in Silverlight development.

There’s a lot of confusion about how asynchronous communication works in RIA’s such as Silverlight, GWT and Javascript. When I start talking about the problems of concurrency control, many people tell me that there aren’t any concurrency problems since everything runs in a single thread. [1]

It’s important to understand the basics of what is going on when you’re writing asynchronous code, so I’ve put together a simple example to show how execution works in RIA’s and how race conditions are possible. This example applies to Javascript, Silverlight, GWT and Flex, as well as a number of other environments based on Javascript. This example doesn’t represent best practices, but rather what can happen when you’re not using a proactive strategy that eliminates concurrency problems:

Asynchronous Execution

In the diagram above, execution starts when the user pushes a button (a). This starts the user interface thread by invoking an onClick handler. The user interface thread starts two XmlHttpRequests, (b) and (c). The event handler eventually returns, so execution stops in the user interface thread.

In the meantime, the browser still has two XmlHttpRequests running. Callbacks from http requests, timers and user interfaces go into a queue — they get executed right away if the user interface thread is doing nothing, but get delayed if the user interface thread is active.

Http request (b) completes first, causing the http callback for request (b) to start. Had something been a little different with the web browser, web server or network, request (c) could have returned first, causing the callback for request (c) to start. If the result of the program depends on the order that the callbacks for (b) and (c) run, we have a race condition. The callback for http request (b) starts a new http request (d), which runs for a long time.

In the meantime, the user is moving the mouse and triggers a mouseover event while the request (b) callback is running. Right after the request (b) callback completes, the web browser starts the UI thread, which causes a mouseover event handler (e) to run. Note that the user can trigger user interface events while XmlHttpRequests are running, causing event handlers to run in an unpredictable order: if this causes your program to malfunction, your program has a bug.

While the event handler (e) is running, request (c) completes: like the mouseover event, this event is queued and runs once event handler (e) completes. Before (e) completes, it starts a new http request (f). The browser looks into the event queue when (e) completes, and starts the callback for (c). Http request (f) completes while callback (c) is running, gets queued, and runs after (c) is running.

At the end of this example, the callback for (f) completes, causing the UI thread to stop. The http request (c) is still in flight — it completes in the future, somewhere off the end of the page.

This example did not include any timers, or any mechanism of deferred execution such as DeferredCommand in GWT or Dispatcher.Invoke() in Silverlight. This is but another mechanism to add callback references to the event queue.

As you can see, there’s a lot of room for mischief: http requests can return in an arbitrary order and users can initiate events at arbitrary times. The order that things happen in can depend on the browser, it’s settings, on the behavior of the server, and everything in between. Some users might use the application in a way that avoids certain problems (they’ll think it’s wonderful) and others might consistently or occasionally trigger an event that causes catastrophe. These kind of bugs can be highly difficult to reproduce and repair.

Asynchronous RIAs have problems with race conditions that are similar to threaded applications, but not exactly the same. Today’s languages and platforms have excellent and well documented mechanisms for dealing with threads, but today’s RIAs do not have mature mechanisms for dealing with concurrency. Over time we’ll see libraries and frameworks that help, but asynchronous safety isn’t something that can be applied like deodorant: it involves non local interactions between distant parts of the program. The simplest applications can dodge the bullet, but applications beyond a certain level of complexity require an understanding of asynchronous execution and the consistent use of patterns that avoid trouble.

[1] Although it is possible to create new threads in Silverlight, all communication and user interface access must be done from the user interface thread — many Silverlight applications are single-threaded, and adding multiple threads complicates the issue.