Forum OpenACS Q&A: Response to What *should* a good architecture Look Like?

I've been working on a conversion of Karl Goldstein's 0.4 version of his DPS (Dynamic Publishing System, or templates--he's not consistent with his naming conventions).  I've got a few things to report and ask.

First, I must not have been there when I first read the overview to the DPS--because he certainly uses XML.  So, my post above seems a little bird brained.

Second, I've got most of the module implemented on my test system.  There is actually a bit more to it than the 4 step process mentioned in the Solid post above.  Those manual downloads are just the Tcl libraries.  He's got an installation directory that gives you more code, and more complete instructions (there's seven or eight steps to the installation).

Third, I'm stuck.  Well, not really.  I'm tired, and if I can't get things going better in the morning, then I'm stuck.  I'm converting the data model tonight, and I've got an outer join that looks too hairy for me tonight.  It is as follows (I've messed with the formatting a little so this is more readable, I'm big on using white space for readability, but this messge is getting formatted funny):

create or replace view ad_site_node_page_map_ext as

  select

    p.page_id, p.page_title, p.page_url, p.description as page_description,

    npm.node_id, npm.prev_page_id,

    sn.parent_id, sn.asset_stub, sn.description, sn.nav_title

  from ad_site_pages p, ad_site_node_page_map npm, ad_site_nodes sn

  where p.page_id = npm.page_id (+)

  and sn.node_id = npm.node_id;

I'm sure I could figure it out eventually, but maybe someone more keen on these things can offer some advice.  I'll work on it in the morning if nobody responds.

Also, I did some diffs between ACS code and openACS code.  It seems that with the openACS views, you don't use create or replace.  You just use replace.  Is that an oversite, or is that required?

I read the conversion document, and it didn't mention varchar2 data types.  As far as I know, that's an Oracle idea, right?

Is connect by on the PostgreSQL to do list?  Just checking.  That sure would be nice if it were there.

Anyway, to sum things up.  There were only a couple of changes to the data model for the DPS.  2 outer joins, one connect by, and two clobs.  I've just touched the actual code working on my server a little.  Without the data model, you can't really go too far.  If anyone else is working on this right now, let me know.

Finally, can I request a code review from someone more senior with these things?  I would just like someone to take a look at an outer join conversion.  Without any data in the data model, it might take me a little time to prove my join does what I think it should be doing.  Maybe a quick glance by someone accustomed to this stuff would be valuable.

Here's the original join:

create or replace view ad_site_node_index_pages as

select sn.node_id, sn.parent_id, sn.nav_title, sn.asset_stub,

p.page_title, p.page_url

from ad_site_nodes sn, ad_site_pages p

where sn.index_page_id = p.page_id (+);

And here's my join:

create or replace view ad_site_node_index_pages as

select sn.node_id, sn.parent_id, sn.nav_title, sn.asset_stub,

p.page_title, p.page_url

from ad_site_nodes sn, ad_site_pages p

where sn.index_page_id = p.page_id

union select sn.node_id, sn.parent_id, sn.nav_title, sn.asset_stub, null as page_title, null as page_url

from ad_site_nodes sn

where 0= (select count(*) from ad_site_pages p where p.page_id = sn.index_page_id);

Thanks