[Catalyst] PostgreSQL Conference: West

[Gene Selkov]

On Tue, 19 Aug 2008, Joshua Drake wrote:

>> However, one of the problems you may find from the Catalyst side of
>> things is that we tend to forget the particulars of various databases
>> because DBIx::Class, SQL::Abstract and SQL::Translator take a lot of
>> the pain from being cross-database compatible out of the mix.
>
> Right, that is the constant issue with all the toolkits. My mind is
> something that provides an introduction to Catalyst (which is good for
> Catalyst) but also, "Why use Catalyst with PostgreSQL?". What kind of
> cool features can Catalyst (and is associated libs) expose that
> developers might be missing.

The coolest feature in postgres is its extensibility. Tsearch2, which 
someone mentioned in an earlier post is based on a user-defined data type. 
I have done some work on the postgres GIST a decade ago, which enabled 
applications like Tseach2, among other things. There is no full-text 
search capability in the postgres engine, but it doesn't have to be 
implemented in the engine. Two Russian astronomers came along and 
figured out how to index arbitrary vectors with a GIST-based data type, 
then they used those vectors to index documents for full-text search 
That's not a very straightforward method, but it works better than the 
built-in search methods in other databases. This goes to say that there is 
almost nothing one cannot do with postgres; however, it is mostly used to 
mimic what everyone else does with databases, with the only 
conspicuous advantages being its zero price, high performance and 
convenience.

So if you really want to highlight postgres's advantages, focus on 
its extended behaviors.

But linking that to Catalyst will appear contrived, unless we can think of 
a nice and complete application that uses these things as independent 
components. Think of something that would be difficult to do without one 
or the other.

I am currently working on porting one of my database applications to 
Catalyst. It has nothing postgres-specific in it, besides the fact that if 
I were to use anything else (mysql, etc.) I would spend more time being 
annoyed than making progress. However, it has a fairly sophisticated data 
backend running in postgres; it has a fairly sophisticated user interface 
done with yui, and there's Catalyst in between.

Here's one component that may be worth showcasing (after some adaptation). 
Take a look:

   http://cci.uchicago.edu:8000/plot/tree/roach

   (if it bitches about "communication errors", wait a bit and reload the
    page. The server is so weak I am really afraid to publish this link)

The idea is that there's a large tree structure in the database (about 
7000 nodes), and there is a mass of data linked to the nodes directly or 
indirectly. Think of the tree as a fancy container for things. The user 
has two basic needs: (1) navigate the tree and (2) look into things 
hanging on it. Looking into things, in most cases, is easier said than 
done, as it may require expensive processing. Another interesting notion 
that may be of general interest is branch aggregates, the simplest 
examples of which are the count of things hung on a branch and the mean 
value of the common attribute, but it can be much more complicated than 
that.

I will not go into detail explaining the meaning of this application (it's 
for surgical education -- highly specific), but it should suffice to say 
that there's a tree, there's data linked to the tree, and there can be 
multiple views of the tree itself and the data it contains. The data is in 
postgres, served with Catalyst, rendered in the browser with YUI, and 
because the backend portion of the rendering process is expensive, the 
view loads itself dynamically on user's request.

This is not a good example of easy and painless development, but I have 
solved so many typical problems while building it that it could be used to 
represent a sophisticated web technology, if properly generalized.

Here's a list of fairly difficult things that it can illustrate:

   1. Storing a tree (nested-segment representation is used)

   2. Tree traversal (I have developed (and borrowed) a couple dozen 
utility queries for doing things with the trees).

   3. Some very involved DBIx::Class queries

   4. A combination of DBIx::Class subqueries with pass-through queries 
used where the task is beyond DBIC's ability.

   5. Tree rendering with YUI

   6. Thomas Pietrzykowski's AJAX controller is used to call the 
applications controller actions from the browser

      http://thomas.pietrzykowski.de/blog/?p=1

   7. External processes are called by the backend to process the data (R, 
gnuplot, etc.)

This would be the kind of example I would like to use, but instead I had 
to spend months figuring things out by trial and error.

If there is any interest in this kind of thing, I can spend some more time 
to generalize it and make it publishable.

Alternatively, I could collaborate with someone to demonstrate the use of 
extended data types in postgres. I myself will not be able to attend any 
conferences outside UK for a year or so.

Regards,

--Gene