[OPEN-ILS-GENERAL] How can we delete Org Units with SQL for EG 2.0 beta3

[Jason Stephenson]

Repke,

What I sent you is meant to be run from the command line like so:

psql -f file.sql

where the contents of file.sql are the commands that I sent.

Looks like you're having a problem with triggers. Have you put  
anything in serial.record_entry?

The commands won't work if you've done anything in the database that  
references actor.org_unit. The code that I sent appears originally at  
the top of a longer script that I use to set up the org_units for our  
consortium when I do a migration*. If you've gone in and messed with  
anything before running those commands, I guarantee nothing.

I suggest reloading the database schema from scratch, and then running  
the commands. They should work. They work for me in rel_2_0 and trunk.

*We haven't migrated, yet, but I've done many test runs along the way  
as I figure out how to migrate more and more data.


Jason


Quoting Repke de Vries <repke at xs4all.nl>:

> Jason, I managed Workstation Registration at the Consortium Level
>
> but following your advice literally with psql in a straight out-of-  
> the-box EG 2.0 beta3 Virtual Image (different approach to  
> maintaining  Org Structure than 1.6 and before), I have zero success.
>
> Maybe take this offline?  My time-zone btw is six hours later or  
> more  than than yours.
>
> This is what psql [1] throws back at me:
> "..
> evergreen=# BEGIN;
> BEGIN
> evergreen=# DELETE FROM actor.org_unit WHERE id > 1;
> NOTICE:  Use of uninitialized value $self in pattern match (m//) at  
> / usr/local/share/perl5/MARC/File/XML.pm line 432.
>
> CONTEXT:  SQL statement "UPDATE ONLY "serial"."record_entry" SET   
> "owning_lib" = NULL WHERE $1 OPERATOR(pg_catalog.=) "owning_lib""
> ERROR:  error from Perl function "maintain_control_numbers": No   
> _parse_* routine defined on this driver (If it is a filter, remember  
>  to set the Parent property. If you call the parse() method, make  
> sure  to set a Source. You may want to call parse_uri, parse_string  
> or  parse_file instead.) [XML::LibXML::SAX=HASH(0x9bed980)] at  
> /usr/share/ perl5/XML/SAX/Base.pm line 2616.
> CONTEXT:  SQL statement "UPDATE ONLY "serial"."record_entry" SET   
> "owning_lib" = NULL WHERE $1 OPERATOR(pg_catalog.=) "owning_lib""
> evergreen=# DELETE FROM actor.org_address WHERE id > 1;
> ERROR:  current transaction is aborted, commands ignored until end  
> of  transaction block
> evergreen=# COMMIT;
> ROLLBACK
> evergreen=#
> .."
>
> Repke de Vries, IISH
>
> [1]
> "..
> [opensrf at localhost run]$ psql -U evergreen -h localhost evergreen
> Password for user evergreen:
> psql (8.4.5)
> Type "help" for help.
>
> evergreen=#
> .."
>
>
> Op 1-dec-2010, om 19:42 heeft Jason Stephenson het volgende geschreven:
>
>> Repke,
>>
>> Try this:
>>
>> BEGIN;
>> DELETE FROM actor.org_unit WHERE id > 1;
>> DELETE FROM actor.org_address WHERE id > 1;
>> COMMIT;
>>
>> The above gets rid of everything but the sample consortium.
>>
>> HtH,
>> Jason
>>
>>
>> Quoting Repke de Vries <repke at xs4all.nl>:
>>
>>> Hi all
>>>
>>> this is a repost of a previous thread [1] now focussing on the   following:
>>>
>>> deleting Organisational Units is different in 2.0 from 1.6 and   
>>> can  not be achieved through the Staff Client.
>>>
>>> The only information how to do it directly in the database with   
>>> SQL  comes from 1.6 documentation [2] and does indeed not work for  
>>>  2.0:  this is what I get back in psql:
>>> "..
>>> evergreen=# delete from actor.org_unit where shortname = 'BR4' ;
>>> ERROR:  update or delete on table "org_unit" violates foreign key   
>>>  constraint "org_address_org_unit_fkey" on table "org_address"
>>> DETAIL:  Key (id)=(7) is still referenced from table "org_address".
>>> evergreen=#
>>> .."
>>>
>>> Can someone tell me what I should do (this will also help 2.0 DIG   
>>>  documenting)?
>>>
>>> Either exclusively SQL or in some combination with the Staff   
>>> Client  Administrator functionality?
>>>
>>> Thanks, Repke, IISH
>>>
>>> [1]
>>> http://georgialibraries.markmail.org/search/?q=Unexpected+results   
>>> +changing+Organisational+Units+through+Staff+Client+in+EG+2.0   
>>> +alpha4#query:Unexpected%20results%20changing%20Organisational%  
>>> 20Units %20through%20Staff%20Client%20in%20EG%202.0%20alpha4+page:  
>>> 1 +mid:t3qerg3qbws34hat+state:results
>>> [2]
>>> http://www.open-ils.org/dokuwiki/doku.php?id=evergreen- admin:policies:ou
>>>
>>>
>>
>>
>
>