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

[Repke de Vries]

Thanks Jason: please see my comments - good chance we have a catch-22  
and deleting Org Units is *not* possible with the Staff Client alone  
but neither with a combination of SQL and Staff Client: preparing for  
an eventual "CONS" Workstation Registration, you have to apply "Can  
Have Users" for CONS which probably means "referencing  
actor.org_unit" ... argh

See below - should we move the discussion to the DEV-LIST?

Repke

Op 2-dec-2010, om 19:21 heeft Jason Stephenson het volgende geschreven:

> 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.
Sorry: should have guessed that from the BEGIN and COMMIT
>
> Looks like you're having a problem with triggers. Have you put  
> anything in serial.record_entry?
>
I might have: I not only had to do a  "Can Have Users" in the CONS  
Org Unit Type but also did a "Can Have Volumes and Copies"; I now  
unchecked that + did another Autogen + Stop and Start but maybe this  
isn't thorough enough?

> The commands won't work if you've done anything in the database  
> that references actor.org_unit. The code
Well: strictly speaking I didn't 'cause I only touched CONS and try  
to delete anything else than / below Consortium  ( your " >1") but  
maybe there "is" an effect ?
> 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.
CATCH-22:  yeah: if you try a combination of Staff Client and SQL  
('cause the Staff Client can't handle the delete) you *have* to do  
some messing to guarantee later access at Consortium level .. which  
probably makes the SQL operation impossible;
which would mean: in EG 2.0 deleting anything in the Org Unit  
Structure and setting up a new Structure can only be done through a  
series of SQL statements ?!  Which means SQL knowledge 'cause there  
is no 2.0 How To on that
>
> 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.
>
Yeah: you got a point but that means I destroy my "Can Have Users"  
for the Consortium Level - which means no more Staff Client access  
ever and therefore no setting up a new structure through the Staff  
Client ..

> *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.
>
We are re-planning our migration (eraly next year) and this Org  
Structure issue is "one of those things" :-) going through the same  
tests as you do
> 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
>>>>
>>>>
>>>
>>>
>>
>>
>
>