[Biojava-l] Re: [BioSQL-l] _removeSequence

[Martina]

I droped the db and run the bioSql again - looks like its working now!
Must have stopped before the alter table statements - didn't had the 
foreign keys - but I didn't know, that they had to be there.
Thanks!

Richard HOLLAND wrote:

> Well, technically that should work because BioJava simply issues a
> delete against the seqfeature table, and therefore all features related
> through foreign keys should automatically delete themselves as a result
> without any further intervention by BioJava... beats me why it doesn't!
> Unfortunately I don't currently use the MySQL implementation myself so I
> can't help much. I hope someone on BioSQL-L knows a little more?
> 
> Richard Holland
> Bioinformatics Specialist
> GIS extension 8199
> ---------------------------------------------
> This email is confidential and may be privileged. If you are not the
> intended recipient, please delete it and notify us immediately. Please
> do not copy or use it for any purpose, or disclose its content to any
> other person. Thank you.
> ---------------------------------------------
> 
> 
> 
>>-----Original Message-----
>>From: Martina [mailto:boehme at mpiib-berlin.mpg.de] 
>>Sent: Monday, June 20, 2005 6:21 PM
>>To: Richard HOLLAND
>>Cc: biosql-l-bounces at portal.open-bio.org; BioJava; 
>>biosql-l at open-bio.org
>>Subject: Re: [BioSQL-l] _removeSequence
>>
>>
>>My tables are all InnoDB tables and in the biosqldb-mysql.sql (v 1.40 
>>2004/11/04 01:49:41) which created them, it says ON DELETE CASCADE.
>>Do I need to do anything else?
>>
>>Thanks,
>>Martina
>>
>>Richard HOLLAND wrote:
>>
>>
>>>To do cascading deletes in MySQL requires the tables to 
>>
>>have been set up
>>
>>>using the InnoDB table style (as opposed to the default 
>>
>>MyISAM tables).
>>
>>>In InnoDB, foreign keys are actually enforced and deletes 
>>
>>will cascade,
>>
>>>whereas in MyISAM it has no concept of foreign keys and so 
>>
>>is unable to
>>
>>>enforce data integrity. The people on the BioSQL-L mailing 
>>
>>list will be
>>
>>>able to help you there.
>>>
>>>The next version of BioJava's database interfaces after the 
>>
>>1.4 release
>>
>>>will assume that the underlying database does have cascading deletes
>>>turned on. The existing version half-attempts to make up 
>>
>>for the lack of
>>
>>>cascading deletes in databases that don't support it, but 
>>
>>it doesn't do
>>
>>>it well at all, hence the problems you are seeing. After 
>>
>>consulting with
>>
>>>Hilmar last week we decided it was a fair assumption to 
>>
>>make that all
>>
>>>BioSQL instances are installed with cascading deletes enabled.
>>>BioPerl-db already makes this assumption.
>>>
>>>cheers,
>>>Richard
>>>
>>>Richard Holland
>>>Bioinformatics Specialist
>>>GIS extension 8199
>>>---------------------------------------------
>>>This email is confidential and may be privileged. If you are not the
>>>intended recipient, please delete it and notify us 
>>
>>immediately. Please
>>
>>>do not copy or use it for any purpose, or disclose its 
>>
>>content to any
>>
>>>other person. Thank you.
>>>---------------------------------------------
>>>
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>From: biosql-l-bounces at portal.open-bio.org 
>>>>[mailto:biosql-l-bounces at portal.open-bio.org] On Behalf Of 
>>>>mark.schreiber at novartis.com
>>>>Sent: Monday, June 20, 2005 5:57 PM
>>>>To: Martina
>>>>Cc: biosql-l-bounces at portal.open-bio.org; BioJava; 
>>>>biosql-l at open-bio.org
>>>>Subject: Re: [BioSQL-l] _removeSequence
>>>>
>>>>
>>>>Biojava doesn't attempt to recusivley remove features by 
>>>>itself. It relies 
>>>>on cascading deletes in the database. I know Oracle can be 
>>>>set to do this 
>>>>(and it works very well). If MySQL has equivalent 
>>>>functionality you may 
>>>>need to turn it on. I'm pretty sure it does but you need to 
>>
>>set it up.
>>
>>>>- Mark
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>Martina <boehme at mpiib-berlin.mpg.de>
>>>>Sent by: biosql-l-bounces at portal.open-bio.org
>>>>06/20/2005 05:43 PM
>>>>
>>>>
>>>>       To:     biosql-l at open-bio.org, BioJava 
>>
>><biojava-l at biojava.org>
>>
>>>>       cc:     (bcc: Mark Schreiber/GP/Novartis)
>>>>       Subject:        [BioSQL-l] _removeSequence
>>>>
>>>>
>>>>Hi,
>>>>
>>>>Im trying to delete a sequence and recursivly all its features.
>>>>
>>>>So:
>>>>
>>>>for (SequenceIterator si = db.sequenceIterator(); si.hasNext();) {
>>>>                Sequence s = si.nextSequence();
>>>>                String name = s.getName();
>>>>                s = null;
>>>>                db.removeSequence(name);
>>>>}
>>>>
>>>>But if I look in the database (MySQL  4.1.12) I can still 
>>
>>see plenty 
>>
>>>>of entries and I have problems entering the same features again, 
>>>>because of dublicate key error. I would like to know if 
>>>>_removeSequence(String) in BioSQLSequenceDB is supposed to remove 
>>>>features recursivly or just the features of the removed sequence?
>>>>If so - what is the best way do delete the features of the features 
>>>>(and so on)? And how to empty the db completly?
>>>>
>>>>Martina
>>>>
>>>>_______________________________________________
>>>>BioSQL-l mailing list
>>>>BioSQL-l at open-bio.org
>>>>http://open-bio.org/mailman/listinfo/biosql-l
>>>>
>>>>
>>>>
>>>>_______________________________________________
>>>>BioSQL-l mailing list
>>>>BioSQL-l at open-bio.org
>>>>http://open-bio.org/mailman/listinfo/biosql-l
>>>
>>>
>