Forum OpenACS Development: Drop script for organizations

Collapse
Posted by Jade Rubick on
I'm running into some difficulty creating the drop script for organizations.

I copied the drop script from the developmental tutorial.

That looks like this:

--drop package, which drops all functions created with define_function_args

select drop_package('samplenote');

--drop permissions
delete from acs_permissions where object_id in (select note_id from samplenote);

--drop objects
create function inline_0 ()
returns integer as '
declare
        object_rec              record;
begin
        for object_rec in select object_id from acs_objects where object_type=''samplenote''
        loop
                perform acs_object__delete( object_rec.object_id );
        end loop;
        return 0;
end;' language 'plpgsql';

select inline_0();
drop function inline_0();

--drop table
drop table samplenote;

--drop type
select acs_object_type__drop_type(
           'samplenote',
           't'
    );
I then modified it to work with organizations
select drop_package('organization');

-- drop permissions
delete from acs_permissions where object_id in (select organization_id from organizations);

-- drop objects

create function inline_0 ()
returns integer as '
declare
        object_rec              record;
begin
        for object_rec in select object_id from acs_objects where object_type=''organizations''
        loop
                perform acs_object__delete( object_rec.object_id );
        end loop;
        return 0;
end;' language 'plpgsql';

select inline_0();
drop function inline_0();

-- drop parties added by organizations
create function inline_0 ()
returns integer as '
declare
        object_rec              record;
begin
        for object_rec in select party_id from parties where party_id in (select organization_id from organizations)
        loop
                perform organization__del( object_rec.party_id );
        end loop;
        return 0;
end;' language 'plpgsql';

select inline_0();
drop function inline_0();

--drop table
drop table organizations;

--drop type
select acs_object_type__drop_type(
           'organization',
           't'
    );


drop table organization_types; 
drop sequence organization__organization__seq;
The organizations table subclasses the parties table, so each organization is a party. The organization__del function deletes both the party, and the organization.

The problem I'm getting is in the portion that drops the organization type. Here's the error I get:

psql:organizations-drop.sql:48: ERROR:  acs_objects_object_type_fk referential integrity violation - key in acs_object_types still referenced from acs_objects
That looks to me like everything is not being deleted. So I guess I'm wondering what I'm doing wrong. Can anyone help, please?
Collapse
Posted by Jade Rubick on
Here's my advice for anyone having similar problems:

First of all, run through the drop script line by line, and make sure you understand what is happening with each line. I found that parties were dropped anyway -- I didn't need to explicitly do so.

I also found that in the first inline function, I typed organizations instead of organization, so the objects weren't being deleted. This is what prevented the acs_object_type__drop_type portion of the script from working.

The drop script now works.

Collapse
Posted by Tilmann Singer on
Jade, you don't need to drop permissions explicitely. A farely recent openacs version should do that automatically.

Also I wonder why you are calling acs_object__delete first and then organization__delete - normally the latter should take care of the necessary object deletion automatically, no?

And what's the difference between these two queries:

select object_id from acs_objects where object_type=''organizations''

select party_id from parties where party_id in (select organization_id from organizations)

If organization subclasses parties (why not groups btw?), then they should return the same, or what am I missing here?

Collapse
Posted by Jade Rubick on
Tilmann: thanks for your help. If permissions don't need to explicitly dropped, then we need to reflect this in the installation documents (https://openacs.org/doc/openacs-4-6-3/tutorial-database.html says to put it in there). I'll post a bug on the bug-tracker about this (at https://openacs.org/bugtracker/openacs/com/acs-core-docs/bug?bug%5fnumber=556)

For future reference, this is what I came up with. You were right about the acs_object__delete and then organization__delete. However, I used the former. I'm not sure if that's incorrect.

I believe you're right. The two queries returned the same thing.

I'm not totally sure why Jon chose to use parties vs. groups. I don't really know what the tradeoffs are. I stayed with his choice. (docs on this are at https://openacs.org/doc/openacs-4-6-3/parties.html.

Anyway, here is what I have now:

-- @cvs-id $Id$

select drop_package('organization');

-- drop permissions
delete from acs_permissions where object_id in (select organization_id from organizations);

-- drop objects

create function inline_0 ()
returns integer as '
declare
        object_rec              record;
begin
        for object_rec in select object_id from acs_objects where object_type=''organization''
        loop
                perform acs_object__delete( object_rec.object_id );
        end loop;
        return 0;
end;' language 'plpgsql';

select inline_0();
drop function inline_0();

--drop table
drop table organizations;

--drop type
select acs_object_type__drop_type(
           'organization',
           't'
    );

drop table organization_types; 
drop sequence organization__organization__seq;