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

Posted by leigh silvester on
I have been working on extending this code.
Part of what I have been doing is reworking it so that it can be run with no errors or warnings.
So far I have got about half way through where I am hitting errors that are due to missing functions/procedures.

Some of the missing components so far are party__new and acs_object__delete.

For party__new I have fabricated the following:
CREATE function dbo.party__new
@new__person_id integer,
@new__object_type varchar(8000),
@new__creation_date varchar(50),
@new__creation_user integer,
@new__creation_ip varchar(8000),
@new__email varchar(8000),
@new__url varchar(8000),
@new__context_id integer,
@ms_return_value integer = 0 OUTPUT

declare @retval int
EXECE dbo.obs_object__new @new__person_id,@new__object_type,@new__creation_date,@new__creation_user,@new__creation_ip,@new__email,@new__url,@new__context_id,@ms_return_value = @retval OUTPUT
SET @ms_return_value = @retval
INSERT INTO parties (party_id, email, url)
VALUES (@ms_return_value, lower(@new__email), @new__url)
RETURN @ms_return_value

acs_object__delete is proving to be a bit more challenging.
The postgres version uses a proprietary function, viz: tree_right.

The section which is challenging to me is:
for obj_type
in select o2.table_name, o2.id_column
from acs_object_types o1, acs_object_types o2
where o1.object_type = (select object_type
from acs_objects o
where o.object_id = delete__object_id)
and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey)
order by o2.tree_sortkey desc
-- Delete from the table.

-- DRB: I removed the quote_ident calls that DanW originally included
-- because the table names appear to be stored in upper case. Quoting
-- causes them to not match the actual lower or potentially mixed-case
-- table names. We will just forbid squirrely names that include quotes.
-- daveB
-- ETP is creating a new object, but not a table, although it does specify a
-- table name, so we need to check if the table exists. Wp-slim does this too

if table_exists(obj_type.table_name) then
execute ''delete from '' || obj_type.table_name ||
'' where '' || obj_type.id_column || '' = '' || delete__object_id;
end if;
end loop;

For MS SQL I have so far interpreted this as...
DECLARE @object_type varchar(8000)
DECLARE @object_name varchar(8000)
DECLARE @object_id integer

-- FOR obj_type
SELECT o2.table_name,o2.id_column
FROM dbo.obs_object_types o1, dbo.obs_object_types o2
WHERE o1.object_type = (
SELECT object_type
FROM dbo.obs_objects o
WHERE o.object_id = @delete__object_id
AND o1.tree_sortkey BETWEEN o2.tree_sortkey AND tree_right(o2.tree_sortkey)
-- ORDER BY o2.tree_sortkey DESC
OPEN cursor_1
FETCH NEXT FROM cursor_1 INTO @object_type
-- loop
-- DELETE FROM dbo.the table.
-- table name, so we need to check if the table exists. Wp-slim does this too
IF Exists (SELECT id FROM dbo.sysobjects WHERE name = @object_type)
--IF table_exists(obj_type.table_name) THEN
DELETE FROM dbo.obs_object_types WHERE id_column = @delete__object_id
FETCH NEXT FROM cursor_1 INTO @object_type
END -- while
CLOSE cursor_1

This is not perfect and it has the tree_right right function which of course is invalid. Is this really a right join?

I would be happy to post developments from John's original code as it evolves should John be amenable.

I am interested in a few things such as:
Is there any particular reason why this code dispenses with the use of triggers?
Perhaps they were not in use by the version of the code that was in existence when the port was done?
Why are email values and urls etc allowed to be so masssive?
In most applications I develop I usually allow up to 200 characters.

Posted by Don Baccus on
The tree_right and BETWEEN operator are used to select subtrees of the object hierarchy. The equivalent in Oracle is "CONNECT BY" and "START WITH". It's not at all like a right join.
Posted by John Sequeira on

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)

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


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?

Posted by leigh silvester on
Unfortunately "CONNECT BY" is not used by MS SQL.
I have just downloaded the " SwisSQL - Oracle to Sybase Migration Tool" which purports to be able to convert procedures which make use of "CONNECT BY".

Shall see what this can produce.

Posted by John Sequeira on
No 'CONNECT BY' in MS-SQL - that would be too easy(!)

I think the construct you want is called a common table expression (CTE)

I think it's different enough that an automated tool would have some issues... but you might get lucky.

And on that note,  you should check out Microsoft's free Oracle -> MS-SQL migration tool:

They recently purchased it from some specialist firm,  so I expect it works well.

Thanks again for another hot tip.
As this requires connection to an Orcale database to perform migration I am currently installing Oracle lite.

One thing has caused me a little confusion is that I am working from SQL for the latest version, whereas you performed migration on an older version.

Consequently the procedures that I am struggling to recreate in MS SQL do not exist in the Oracle version (seem to be present as package components operating in a different way), but are present in the Postgres SQL.
All very confusing.

Shall be interested to see how this migration tool copes with the oracle packages and associated methods.

I know this is probably the wrong place to say it, and I know it is because I am a simple soul who is just starting to get a little out of his depth here, but Oracle is just too f@#$~]g weird! I'm sure it is fantastic once you get to know it.