[OPEN-ILS-DEV] [OPEN-ILS-GENERAL] How can we delete Org Units with SQL for EG 2.0 beta3
Jason Stephenson
jstephenson at mvlc.org
Thu Dec 2 16:32:14 EST 2010
Quoting Repke de Vries <repke at xs4all.nl>:
> 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?
Yes, so moved. :)
>
> 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?
It may be the can have volumes and copies bit that is tripping you up
because the trigger that is throwing an error is looking for serials
information. Try it with just can have users.
>
>> 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 ?
Ok. You don't want to remove the >1 because then you have no org_units at all.
>> 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
Yeah. This is a good argument to bring back the bootstrap CGI, or at
least those that allowed one to configure org_units.
I have never really done anything with org_units in the client. This
seems potentially dangerous to me.
>>
>> 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 ..
>
Well, you can restore the database and then set can have users with SQL.
>> *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
I am going to attach my current SQL script for setting up
actor.org_unit and friends. It may help you see a bigger picture of
what is going on. It doesn't contain any sensitive information, most
of it obtainable on the web or is a matter of public record in the U.S.
Hopefully, this script will help others see how to create org_units
with SQL and they can fill in their own values.
Jason
>> 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
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: actor.org_unit.sql
Type: text/x-sql
Size: 26013 bytes
Desc: not available
Url : http://libmail.georgialibraries.org/pipermail/open-ils-dev/attachments/20101202/6c81bd86/attachment-0001.bin
More information about the Open-ils-dev
mailing list