Forum OpenACS Q&A: Re: Re: ANN: acs-kernel port to MS-SQL Server

Collapse
Posted by John Sequeira on
Leigh,

I'm very happy to help where I can even though it's not an active project for me. I delighted that you're finding the work valuable. The OpenACS data model deserves to be stolen by as many other projects (and databases) as possible.

Just so you know, I did not hand-write very much of the ms-sql code - I wrote a very ugly compiler that parsed postgres and emitted transact sql. That explains a lot of the weirdness that you'll see. I think I made all varchars varchar(8000) ... I think this was to simplify a type mismatch between postgres and mssql but I don't remember specifics (i.e. 8000 handled some corner cases that my translator didn't or couldn't).

I also didn't bothere to write a parser/tokenizer for trigger syntax, as there were so few triggers it was easier to do by hand.

Speaking of triggers, for the tree stuff, in the codebase I translated (4.1?), openacs used to use triggers to maintain a parent-child lookup table to allow easy hierarchical queries (delete all children of this object). I think that delete object was rewritten when Postgres caught up with Oracle and implemented hierarchical query support in plpgsql.

with MSSQL 2005, SQL Server now has similar native support for tree queries... is that what you're targetting? You could either use that, or try to use the old triggers to maintain the parent-child table of yore.

(here's a writeup of the technique)

http://www.sqlteam.com/item.asp?ItemID=8866

I'm curious, what's your motivation for doing this?

John

Collapse
Posted by leigh silvester on
native support for tree queries
Useful tip.

I'm curious, what's your motivation for doing this?

Well you kinda hinted at it when you wrote...
"The OpenACS data model deserves to be stolen by as many other projects (and databases) as possible".

I am developing a new version of a system I have been working on elsewhere which needs a sophisticated object management system at it's core, OpenACS to me looks as if it has that.
It is more the core I am interested - objects, groups, parties and persons management. The APM management also has intersting features which could make maintaing this system and its various add on features more manageable.

A previous version of the system relied on LDAP directory to manage users, organisation, hierarchy and permissions. This might not always be feasible so I have turned to Open ACS to guide me. The original intention was to get to grips with Open ACS and make my own lite version, but the more I got to understand how it was working (although I wouldn't claim to fully understand it) the more I saw it would be silly to strip away various layers.
Does this make sense?