Tangled in the Threads

Jon Udell, March 14, 2001

A Zope Spreadsheet Generator

Perl, Python, and DTML working together

In the second of a two-parter, Jon elaborates on a web-based Zope spreadsheet

In last week's column, we looked at a simple web spreadsheet based on Zope. It relied on just the basic Zope infrastructure:

• ZODB. The Z Object Database is Zope's crowning jewel. In fact, you don't have to use ZODB with Zope. Once you've installed Zope, you can use ZODB from standalone Python programs too. But Zope wraps an incredibly powerful and useful UI around ZODB.

• The Zope management interface. This is the face of ZODB: web-based forms that you use to create/delete/modify objects in the ZODB. As we saw last week, the management UI isn't just for making folder structures, writing scripted templates, and wiring these things together. It's also a way to interactively prototype data structures that will later be handled programmatically. And -- although this point is not as well appreciated as it might be -- the combination of Zope's access control scheme and web-management UI can eliminate the need for certain aspects of application development. In our spreadsheet example, there will be a bunch of sales reps entering rows of data, but only one sales manager specifying yearly targets. In this situation, you may not need to write special UI code for that one case. In practice I've found that in situations like this one, it's enough to give the sales manager access to the management screen for one Zope folder, and a bit of training on how to edit the properties of that folder.

• DTML. Zopistas both love and hate the Document Template Markup Language. There is now underway a new initiative to address DTML's major problems, which are stated succinctly in this discussion of DTML vis a vis the forthcoming Zope Presentation Templates (ZPT) technology:

  DTML's syntax is quirky and unpredictable. Some tag attributes use names, some use literal strings, and some can use Python expressions, but there's not much rhyme or reason to it. Access to names in the namespace that contain punctuation, or names which must be reached via a path, are painful to use. If you use a visual editor or work with someone who does, you've exerienced the pain of having to reconcile DTML's dynamicism with the visual representation of a page (or page fragment).

The ZPT initiative, which aims to make XHTML central to Zope, and more cleanly separate content, presentation, and logic, looks like a great idea. Meanwhile, DTML has plenty of life left in it. As we'll see this week, you only need to master a few DTML concepts to do powerful things.

Creating spreadsheet namespaces

Let's start with the same schema we saw last week. Here's a Perl data structure that expresses it:

my $schema =
[
  {
  name => Account,
  size => 15,
  type => 's',
  },
  {
  name => Rep,
  size => 6,
  type => 's',
  },
  {
  name => Amount
  size => 6,
  type => 'f',
  },
];

In this example, the types are 's' for 'string' and 'f' for 'float'.

Now let's expand this namespace in two dimensions. First, we'll want to create monthly spreadsheets. Second, we'll of course want to add many rows to each monthly sheet. So the ZODB properties for this application are going to end up with names like MAY00_12_Account -- that is, the Account cell on the MAY00 sheet at row 12. Here's a function that generates all the names for one month:

sub makeNamespace
  {
  my ($schema,$cellprefix,$maxrows) = @_;
  my @namespace = ();
  foreach my $hash ( @{$schema} )
    {
    foreach my $row (1..$maxrows)
      {
      push ( @namespace, $cellprefix . $row . "_" . $hash->{name} );
      }
    }
  return @namespace;
  }

Note in passing that I've written the schema as a list-of-hashtables (LoH in Perlspeak) rather than a hash-of-hashtables (HoH), e.g.:

my $schema =
{
  Account =>
		{
		size => 15,
		type => 's',
		},
...

The reason is that the LoH keeps things in the order we want to use them. An HoH wouldn't, so you'd need a separate list to control the order of the spreadsheet's columns.

Because ZODB properties are typed, we'll need to be able to extract type information from a name (e.g. MAY00_12_Account) so we can give it to Zope's property-creation API call. Here's a function to do that:

sub typeFromItem
  {
  my ($list,$name) = @_;
  $name =~ m/_([a-zA-Z]+)$/;
  my $barename = $1;
  foreach my $hash (@$list)
    {
    if ( $hash->{name} eq $barename ) 
      { return $hash->{type} }
    }
  }

Creating ZODB properties

Last week, we created the ZODB properties by hand. That's great for prototyping, but otherwise impractical. Here's the DTML code that does it programmatically, in this case for the 'JUN00' spreadsheet with 4 rows:

<dtml-if expr="JUN00_initialized == 0">

<dtml-in expr="('JUN00_1_Amount','JUN00_2_Amount','JUN00_3_Amount','JUN00_4_Amount')">
<dtml-if expr="_.has_key(_['sequence-item']) == 0">
<dtml-call "PARENTS[0].manage_addProperty( _['sequence-item'], 0, 'float')">
</dtml-if>
</dtml-in>

<dtml-in expr="('JUN00_1_Account','JUN00_2_Account','JUN00_3_Account',
'JUN00_4_Account','JUN00_1_Rep','JUN00_2_Rep','JUN00_3_Rep','JUN00_4_Rep')">
<dtml-if expr="_.has_key(_['sequence-item']) == 0">
<dtml-call "PARENTS[0].manage_addProperty( _['sequence-item'], '', 'string')">
</dtml-if>
</dtml-in>

</dtml-if>

Wrapped in an outer initialization test (i.e., is the ZODB property JUN00_initialized true or false?), there are two DTML iterators -- one to create ZODB string properties, the other float properties. The Zope API call, manage_addProperty, is further wrapped in another test which asks: "Does this property already exist?" This extra check makes it possible to add rows to the spreadsheet after it's been deployed. Suppose you regenerate the spreadsheet with 10 rows instead of the original 4. Without the property-existence test, it will fail with the Zope error "duplicate property." With the test, the spreadsheet can add 6 new rows without disturbing the data in the original 4.

This DTML syntax certainly lives up to its "quirky and unpredictable" reputation. But for our purposes here, we're going to treat it as object code, and use a Perl function to generate that object code:

sub initCells
  {
  my ($init,$schema,$cellprefix,$maxrows) = @_;
  my @namespace = makeNamespace($schema,$cellprefix,$maxrows);
  my @floats = ();
  my @strings = ();
  foreach my $name (@namespace)
    {
    my $type = typeFromItem($schema,$name);
    if    ( $type eq 'f' )
      { push (@floats, $name); }
    elsif ( $type eq 's' )
      { push (@strings, $name); }
    else
      { print STDERR "unexpected type $type\n"; }
    }
  @floats = map  { "'" . $_ . "'" } @floats;
  @strings = map { "'" . $_ . "'" } @strings;
  my $floats = join (",", @floats);
  my $strings = join (",", @strings);
  my $dtml = <<"EOT";
<dtml-if expr="$init == 0">
<dtml-in expr="($floats)">
<dtml-if expr="_.has_key(_['sequence-item']) == 0">
<dtml-call "PARENTS[0].manage_addProperty( _['sequence-item'], 0, 'float')">
</dtml-if>
</dtml-in>
<dtml-in expr="($strings)">
<dtml-if expr="_.has_key(_['sequence-item']) == 0">
<dtml-call "PARENTS[0].manage_addProperty( _['sequence-item'], '', 'string')">
</dtml-if>
</dtml-in>
</dtml-if>
EOT
  return $dtml;
  }

This chunk of Perl is only slightly more complex than the DTML it produces. But it can produce DTML spreadsheet initialization logic for any arbitrary schema.

Updating ZODB properties

Last week, we used this DTML pattern to update the ZODB properties that store spreadsheet cells:

<dtml-if expr="REQUEST.has_key('MAY00_12_Account')">
<dtml-call "PARENTS[0].manage_changeProperties(MAY00_12_Account = 
   REQUEST['MAY00_12_Account'])">
</dtml-if>

It's possible to generate instances of this pattern, but it would be crazy to do it. Even a modestly-sized spreadsheet would balloon into a monstrous web page that would take forever to download.

It's much better to encapsulate this logic into some Python code, and just call that code from DTML. In Zope, as in Perl, there's more than one way to do it. The original and still most powerful mechanism is the Zope "external method." And here is an example of such a method:

def assignProps(self,form):
    for key in form.keys():
        if ( self.hasProperty(key) ):
            val = form[key]
            self.manage_changeProperties({ key : val })

The procedure for installing it is as follows:

• Create a file, for example ss.py, and put this method in the file.

• FTP the file to Zope's /Extensions directory.

• Go to the management screen for the application's folder (e.g. /Sales/manage) and create an External Method object. In this case, I used these values:

  Id: ssSupport
  Title: none
  Module Name: ss (the filename minus the .py extension)
  Function Name: assignProps

Now you can invoke this method from DTML, like this:

<dtml-call expr="assignProps(REQUEST.form)">

Suppose our spreadsheet lives at /Sales/MAY00_SpreadSheet. Invoking that bare URL sends no HTML form data, so the Zope object REQUEST.form is empty and assignProps does nothing. The page displays. Now the user changes a value in a cell, and clicks Update. The action wired to Update is the same URL, /Sales/MAY00_SpreadSheet, but now REQUEST.form has name/value pairs, and assignProps maps these into the corresponding ZODB properties.

From external methods to Python scripts

External methods are powerful, but inconvenient. Because they live in the native filesystem, not in ZODB, you can't manage them through Zope's web UI. A more recent alternative is the "Python script" (formerly "Python method"), which integrates more smoothly into Zope. These scripts run in a kind of security sandbox, so they can't do everything that external methods can. But in our case, we don't need to do much, and a Python script is a quite capable alternative to an external method.

For this application, I created a Python script called pyAssignProps, placed it in my /Sales directory, and put this code into it:

for key in context.REQUEST.form.keys():
    if (container.hasProperty(key)):
        val = context.REQUEST.form[key]
        container.manage_changeProperties({ key : val })

Note that there's no method signature. The name of your method is what you declare when you create the object in the management UI. The Zope environment manufactures the method signature for you. If you need to send arguments into this script, you can name them on the Bindings tab of the Zope management screen. In this case, it's unnecessary. The default bindings give this code access to REQUEST.form (by way of the context object), and to the folder's API (by way of the container object).

Once this script is installed, you can call it from DTML in almost the same way as the external method:

<dtml-call expr="pyAssignProps()">

Final touches

Here's a link to the complete Perl script. It's driven by two variables: a list of month names, and a count of rows:

my @months = qw ( MAY00 JUN00 );
my $rows = 10;

With these settings, the script will write these DTML files:

MAY00_SalesSheetEdit.html
MAY00_SalesSheetView.html
JUN00_SalesSheetEdit.html
JUN00_SalesSheetView.html

For each of the two months, there's an edit sheet and a view sheet. While the edit sheet can also serve as a viewer, it's cumbersome to haul all that HTML form machinery into the browser when you just want to look at the data. The view sheet, which just dumps everything into a simple HTML table, has another big advantage over the edit sheet. It's exportable to a real spreadsheet, which is -- let's admit -- the appropriate place to do a whole lot of things that HTML insn't competent to do.

The point of this simple web spreadsheet is to enable a small group of salespeople to keep one another informed of their collective monthly progress. They need to put in their own numbers, see everyone else's numbers, and check the totals. To help isolate users from one another, the final version uses one <form> tag, and one Update button, per row. Since salespeople work with disjoint sets of accounts, this per-row strategy minimizes the likelihood of an update collision. It also cuts ZODB some slack -- the data store is robust, but there's no need to pound all the rows whenever one row changes.

Zope, TWiki, universal canvas

It's been fun getting back into Zope, and I'm looking forward to what the union of PythonLabs and Digital Creations will bring.

Meanwhile, as I'd hoped, the first part of this column sparked some interesting feedback in the newsgroup. I particularly enjoyed this posting:

Peter Thoeny:

Jon's column prompted me to build a proof of concept
of a Wiki based spreadsheet. The idea is to offer
formulas in HTML tables. Tables are defined in TWiki 
as:
  | row 1 | col 2 | col 3 |
  | row 2 | col 2 | col 3 |

I created a small spreadsheet plugin for TWiki that 
allows you to place formulas in tables. Formulas are 
of %CALC{"formula"}% format, i.e. %CALC{"$ROW(0)"}% 
returns the current row number.

See example at

http://TWiki.org/cgi-bin/view/Test/SpeadSheetSandbox


Feel free to edit the page and experiment.

That's so cool! Because the editing happens in free-form text, in the spirit of Wiki, this approach is much lighter than mine. I probably couldn't convince a group of salespeople to enter data that way, but it's the kind of thing I'd do myself.

Both of these approaches -- and I'm sure I'll hear about others in the coming weeks -- remind me of the "universal canvas" theme which I cited in my report on Internet groupware. We have a deep need, still largely unfulfilled, to use the web as a shared medium for expressing words, numbers, pictures, and sounds. We force all this into the Procrustean bed of HTML because, while that's painful, there's no practical alternative. But I hope that won't hold true for too much longer. In that report, I quote ExcoSoft's Jan Christian Herlitz, who nicely articulates the vision of the universal canvas:

The next generation of editors will read and present XML-based information, whether it be paragraphs, tables, drawings, e-mails or spreadsheets. Everything will be done in one user interface. Behind the scene, different software components from different vendors will be activated to handle different parts of the information. Integration will be achieved through "styling". An element will be styled as, for example, a spreadsheet. A spreadsheet component will be fetched to calculate and present the information. As everything is basically XML, the user will experience a much simpler and more natural user interface. All types of information can be stored in one document (separate entities are not needed). Text is handled in a uniform manner.

Everything can be searched. Everything can be styled. Everything can be inside everything else; an equation inside a table which is inside a drawing which is inside a spreadsheet.

The next generation of editors will be both editors and browsers, but they will not in a meaningful sense be XML editors. They will simply be clients!

This is just how things ought to be. HTML isn't going to get us there, though. Sooner or later, we're going to have to move to a better way of representing, and interacting with, content. And, to come full circle, I think Zope's going in the right direction. As I mentioned in my talk at last year's Python conference, ZODB and XML are natural allies. I hope the Zope Presentation Templates initiative will strengthen that alliance, and help make the universal canvas a reality.

---

Jon Udell (http://udell.roninhouse.com/) was BYTE Magazine's executive editor for new media, the architect of the original www.byte.com, and author of BYTE's Web Project column. He's now an independent Web/Internet consultant, and is the author of Practical Internet Groupware, from O'Reilly and Associates. His recent BYTE.com columns are archived at http://www.byte.com/index/threads


This work is licensed under a Creative Commons License.