Use Direct Traversal of SQL Methods

Created by . Last modified on 2003/08/01.

Consider for a moment just how hideous the following URL is:

  http://www.zope.org/dtShow?MEMBER=mcdonc&TIME=3am

There's no way you're going to tell your mom that URL over the phone. You're gonna have to email it to her, or in the worst case, drive over to her house and type it in for her if you want her to visit it. After you're done messing around on the computer, she's going to make you hang up that picture frame you've been promising to hang up for her for the last three weeks. Thus, most of the time, we'd like to avoid URLs such as these. Mom Avoidance.

Many times we construct web applications that need to present data to a viewer based on information gleaned from the URL. Such is the case with my fictional hideous example. Most of the time, such an application will be reliant on the "query string" (the data which follows the "?" in a URL). In this case, the query string is:

  MEMBER=mcdonc&TIME=3am

Which is HTTP-speak that indicates that MEMBER is mcdonc and TIME is 3am ("&" and "=" are special separator characters in a query string). Different systems deal with query strings differently. Zope in particular takes query string argument keys and values and stuffs them in to the REQUEST namespace. Thus, when we give the query string "MEMBER=mcdonc&TIME=3am" to the dtShow method (as we're doing with our hideous URL), Zope takes that to mean "call the dtShow method, but whoa, hold up there buddy, before you do that, execute REQUEST['MEMBER']='mcdonc' and REQUEST['TIME']='3am'."

The REQUEST namespace is a dictionary (an associative array or hash for all you Perl people) with all kinds of gory stuff in it. What makes this a good place to stuff things, however, is that DTML relies on REQUEST pretty heavily and looks inside REQUEST as a default namespace. Our query string arguments and values just get tacked on to the end of the REQUEST dictionary, making them easy for DTML to find without any weird qualifications.

This also makes a query string a natural place to stuff values for arguments to SQL Methods. SQL Methods generally rely on some sort of argument in order to return useful data. When you establish a SQL Method, you type a SQL query into the little query box such as:

    SELECT PROPER_PLACE
        FROM
    PLACES
        WHERE
    MEMBER = <dtml-sqlvar MEMBER type=string>
        AND
    TIME = <dtml-sqlvar TIME type=string>

In the "Arguments" box, you'd type something like:

    MEMBER TIME

This indicates that the SQL Method should expect to receive at least two arguments when it's called, and those arguments should be named "MEMBER" and "TIME". Now, we can call a SQL Method from DTML with arguments. It's easy. Watch:

  <dtml-in expr="sqlShowProperPlace(MEMBER='mcdonc', TIME='3am')">
     Proper place:  <dtml-var PROPER_PLACE>
  </dtml-in>

Now, let's assume that I have a sqlShowProperPlace SQL Method that is hooked up to a database that has a PLACES table. The PLACES table looks like this:

   MEMBER    TIME    PROPER_PLACE
   ------    ----    ------------
   mcdonc    3am     Bed
   jim       7pm     Wrestling practice

The sqlShowProperPlace method SQL query looks like the one shown above ("SELECT PROPER_PLACE FROM PLACES WHERE...").

Calling the DTML listed above (<dtml-in expr="sqlShowProperPlace....) will produce, when called, the following:

   Proper place: Bed

Works like a champ. But obviously we can't have people type DTML all the time to get a result to a database query. But we can do the equivalent by presenting them a URL that looks like this:

   http://www.zope.org/dtShow?MEMBER=mcdonc&TIME=3am

While in "dtShow", we put the following DTML:

  <dtml-in expr="sqlShowProperPlace(MEMBER=MEMBER, TIME=TIME)">
     Proper place: <dtml-var PROPER_PLACE>
  </dtml-in>

When our illustrious users click on the URL with the query string in it, they'll see:

  Proper place: Bed

If I had the URL http://www.zope.org/dtShow?MEMBER=jim&TIME=7pm, however, they would see:

  Proper place: Wrestling practice

So we've constructed a general-purpose combination of a DTML Method and a SQL Method that can return different results to the viewer based on arguments inside a URL query string. This, by and large, is how large sites like Deja.com instruct their system to do a lookup based on values you type in to form fields or based on values in the query strings of URLs that you click on. Consider:

http://www.deja.com/[ST_rn=ps]/qs.xp?ST=PS&svcclass=dnyr&QRY=zope&defaultOp=AND&DBS=1&OP=dnquery.xp &LNG=ALL&subjects=&groups=&authors=&fromdate=&todate=&showsort=score&maxhits=25

Egads! Try telling THAT one to your mom over the phone. You'll be over there for weeks, taking out the garbage, mowing the lawn, washing the dog. As you see, it gets complicated. Our piddling little URL has nothing on the big boys. They know nothing of Mom Avoidance. But we do, so let's try this a different way.

Let's construct a different DTML Method to display our results. We'll call it dtShowTraverse:

  Proper place: <dtml-var PROPER_PLACE>

Yup. That's it.

Now, with the magic of Zope URL traversal on a SQL Method and Mom Avoidance, we will provide our users with a different URL to click on:

  http://www.zope.org/sqlShowProperPlace/MEMBER/mcdonc/TIME/3am/dtShowTraverse

OK, it's still a mouthful. But you don't have any of those symbols like "&" and "=" and "?" to say to Mom. This is a plus. It's hard enough telling her the difference between the slash and the backslash. I can't help you much with the capitalization issues.

But, anyway, when called (surprise, surprise) our URL will return:

  Proper place: Bed

What happened here is that we called the sqlShowProperPlace SQL Method directly. When we do this, it interprets the remainder of the URL as key-value pairs that correspond to its arguments. It traverses the URL, looking for key-value pairs (MEMBER=mcdonc, TIME=3am in this case), and when it has enough of them to fill its "Arguments" list up, it stops. It then calls the method you specify in the rest of the URL (dtShowTraverse) to display the results.

Note that this only works when your SQL method's result set is restricted to a single row.

You need to change nothing whatsoever about your SQL Method for this to work. Notably, you do NOT need to click on "enable simple direct traversal" in the Advanced section of the SQL Method. "Simple direct traversal" can be used only when the SQL method's "Arguments" box has only one argument, while what I describe in this document can be used when the SQL Method has any number of defined arguments. You can mix up the order of the arguments in the URL if you'd like too. Have fun!

Thanks to Martijn Pieters for an explanation.

For more information, also see:

The Z SQL Method User's Guide (somewhat dated now, especially with regards to URL traversal)

Simple Database/form interaction HowTo

Using ZSQL Methods With Acquisition HowTo

Accessing a ZSQL Method from an External Method