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
AS
BEGIN
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
END
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
loop
-- 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
DECLARE cursor_1 CURSOR FOR
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
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- 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)
BEGIN
--IF table_exists(obj_type.table_name) THEN
DELETE FROM dbo.obs_object_types WHERE id_column = @delete__object_id
END IF
FETCH NEXT FROM cursor_1 INTO @object_type
END -- while
CLOSE cursor_1
DEALLOCATE 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.
