Forum OpenACS Q&A: ETP and Oracle ... can someone help?

Posted by Don Baccus on 05/29/02 10:56 PM

ETP for Oracle used to work, but apparently folks working on it have
added things or changed things in ways that still work on PG but break
the Oracle version.

I'll refrain from launching into a moralistic lecture on the need to
keep important packages working on both RDBMS's if one expects them to
remain part of our standard release ...

Instead, I'm asking for help.

HELP!

Does anyone have time to track this down and do whatever's necessary
to fix it? Jon Griffin did the first port of ETP to Oracle. I got
stuck doing the second port (ETP was changing frequently at the time).

I am not doing this third effort.

If we can't get help and get it fixed it will be stripped out of the
Oracle release. I don't want to hold up our final release forever.
Yet I hate not having this available for Oracle since it does provide
a nice feature set for quickly building simple things.

2: Response to ETP and Oracle ... can someone help? (response to 1)

Posted by Jun Yamog on 05/30/02 05:32 AM

Hi Don,

I can not commit myself for this since I am very busy right now. Anyway I would not like ETP to be left out of Oracle. So I fired up my oracle and crossed my fingers that my oracle still works. Its been while since I used my Oracle.

Here is the first fix on tcl/etp-procs-oracle.xql this is the correct query clause

<partialquery name="etp::get_content_items.gci_orderby">
  <querytext>
	sort_order
  </querytext>
</partialquery>

I have also found a bug in moving the sort order. So I think the problems with ETP will be to deal with the tree_sortkey in Oracle. I will try to fix but no promises.

Guys and Gals please pitch in. I know Luke P is also very busy that is why ETP 2.0 is not moving that much. I know for sure I am guilty of not moving ETP 2.0.

3: Response to ETP and Oracle ... can someone help? (response to 1)

Posted by Jun Yamog on 05/30/02 05:55 AM

Hi Don,

There is a problem on how ETP makes use of CR. Basically ETP makes use of tree_sortkey to know the ordering the the content items in a content folder. ETP needs a column to determine the sort order of the item in a folder. You can use rownum to emulate this but its not really good since you can't update rownum. (I think... not really familiar with rownum).

But since ETP was original ported by Jon and you so there must be a way that I am not aware of.

The way I see it is to add another column in cr_items or create a table in ETP that holds mapping for the sort order. Although I think there must be a better solution to the problem since I am not really an Oracle guru.

Please give me some ideas and if its easy to put in I will try to put it in. Otherwise I hope a more capable Oracle guy to help out.

4: Response to ETP and Oracle ... can someone help? (response to 1)

Posted by Don Baccus on 05/30/02 06:40 PM

Thanks for stepping forward to help, Jun. I know the "I'm busy" feeling very well, as that describes my situation, too.

It's great that so many of us are busy with paying jobs, OpenACS or not, but it does seem to hamper progress on important packages.

Why is sort order so important to ETP? Does it have weird hooks that depend on a particular ordering? Normally for the file folder paradigm to make sense to users it would seem sufficient to sort items alphabetically by name, something like that ...

5: Response to ETP and Oracle ... can someone help? (response to 1)

Posted by Jun Yamog on 05/31/02 03:58 AM

Hi Don,

In ETP you can arrange the ordering of items on a per folder basis. This is needed for example you want to arrange your links/navigation on a particular way. Most of the time it is not rule based (e.g. alpha, chrono, etc.). You want to arrange it the way it is. The fix that I provited above would be sufficient enough for ETP to work although still broken. Since sorting by using "move up" is broken. Also I think symlinking is broken since it uses tree_sortkey to know the tree_level so it indents the contents in a nice tree like structure in the page.

Here is an example snippet of my cr_items.

select item_id, parent_id, name, tree_sortkey from cr_items order by tree_sortkey;

    2673 |      2672 | index                               | 0000000000000000000000010000001000000000
    2699 |      2672 | state_courts                        | 0000000000000000000000010000001000000001
    2708 |      2672 | bankruptcy_court                    | 0000000000000000000000010000001000000010
    3256 |      2672 | court_claims                        | 0000000000000000000000010000001000000011
    3265 |      2672 | court_appeals                       | 0000000000000000000000010000001000000100
    3274 |      2672 | district_court                      | 0000000000000000000000010000001000000101
    3283 |      2672 | supreme_court                       | 0000000000000000000000010000001000000110
    3292 |      2672 | tax_court                           | 0000000000000000000000010000001000000111

Notice that all items have the same parent_id, so all items resides in a single cr_folder. But since we can use tree_sortkey in PG we can arrange the contents to our preferred arrangement. From what I know this is the default behaviour of CR on PG. So in Oracle there has to be something that ETP can lookup into AND manipulate to order the content items.

I am glad that we are busy with paying jobs although I would really like to have the same rate that you guys are having =).

6: Response to ETP and Oracle ... can someone help? (response to 1)

Posted by Don Baccus on 05/31/02 04:12 AM

Ugh - a package designed to require an OpenACS 4/PG feature from the ground up? I missed that when we started moving it to Oracle.

I guess somewhere the message that we're trying to develop a toolkit that supports both Oracle and PG got missed :(

(I know you're not the person who wrote this, Jun)

Ben is working on a tree sortkey solution for Oracle, as it turns out, using the RAW datatype I managed to dig out of the Oracle manual. We'll see how it turns out. He's also uncovered a race condition in the very original (and subsequent) tree_sortkey implementation. I have a fix for this for OpenACS 4.6 that will also speed up insertion and updating of trees.

There are thoughts of doing some benchmarking of this approach vs. CONNECT BY to see if the extra space required might be balanced by easier manipulation of trees (the problem you mention is typical of the situation when using CONNECT BY, the tree_sortkey approach is more flexible). With tree_sortkeys you don't need parent ids as the hierarchy's fully exposed, too, so there are several advantages to this approach.

Still ... dependencies of this sort that aren't modelled in Oracle shouldn't get built into general use packages. Same's true in the opposite direction.

Grrr ... OK it appears that for OpenACS 4.5 ETP is a PG-only package. A fix on this scale just isn't going to happen in time.

7: Response to ETP and Oracle ... can someone help? (response to 1)

Posted by Jun Yamog on 05/31/02 04:40 AM

Its really such a sad news. But atleast we did not wait that long to know the problem. I must remember about this problem for ETP 2.

But if someone has a good Oracle hack you are all welcome to try. =)

8: Response to ETP and Oracle ... can someone help? (response to 1)

Posted by Talli Somekh on 05/31/02 06:15 AM

I don't think that the message that OACS supported both databases was missed at all.But the fact that ETP is not easy to maintain for Oracle and PG. That's why the announcement for ETP2 was made, to get interest going on building an ETP easier to maintain. So deeming it not ready for prime time release is probably prudent as it's not as mature as it should be.

I also think some pieces of code that were added or patched to the original ETP code that worked after your port probably sneaked it's way in. Since this mistake seemed to be pretty non-trivial to find, so it probably just got overlooked. Since the OpenMSG folks have set up the testing enviros, hopefully these kinds of things will happen less.

talli

9: Response to ETP and Oracle ... can someone help? (response to 1)

Posted by Luke Pond on 05/31/02 07:16 PM

I agree with Talli - take it out of the Oracle release. We never moved ETP beyond the "1.1d" designation to beta or release, specifically because when exposed to peer review, it became clear that its interactions with the content repository were nonstandard and needed drastic improvement. As this discussion has pointed out, these shortcomings go beyond simple bug fixes. I wrote the spec for ETP2 with this experience in mind, but the necessary resources haven't yet appeared to make further progress.

10: Response to ETP and Oracle ... can someone help? (response to 1)

Posted by Don Baccus on 05/31/02 08:11 PM

OK ... good, thanks Talli and Luke. And sorry if I'm coming across as being grumpy, this has been one of the major reasons for my holding off on releasing OpenACS 4.5 this past week (the OpenMSG people informed me that ETP flat-out failed under Oracle when they sat down and did rough testing of every package a week or so ago).

11: Response to ETP and Oracle ... can someone help? (response to 1)

Posted by Jade Rubick on 05/31/02 08:36 PM

Don, you held off on releasing OpenACS 4.5 because you were
grumpy?

😉