[BioSQL-l] _removeSequence

Martina boehme at mpiib-berlin.mpg.de
Tue Jun 21 05:46:22 EDT 2005


Hi Hilmar,

I wasn't aware of 2 different types of features.
I'm making features as described in 
http://www.biojava.org/docs/bj_in_anger/feature.htm, and as far as I 
can tell from the results, its the first type you describe.
The second type of feature is confusing me: as I understood the 
feature relationships, the graph is a tree, with only one parent for a 
given feature, and if that feature is deleted, all its children should 
get deleted too?

Martina


Hilmar Lapp wrote:

> There's one thing that I'm unsure about in Martina's original email, 
> namely whether she was referring to features related to a sequence 
> (bioentry), or to features hierarchically related to each other through 
> the seqfeature_relationship table.
> 
> If the former, then the cascading delete should have taken care of 
> removing the features when you remove the sequence (bioentry) to which 
> they point through their foreign key (and recursively the locations etc).
> 
> However, if the question was about hierarchical features, then deleting 
> one feature in the hierarchy will never (and shouldn't ever) delete any 
> other feature in the hierarchy (except if all of them reference the same 
> bioentry and you deleted the bioentry). If you delete a seqfeature in a 
> hierarchy of seqfeatures then by cascading delete this will also delete 
> all rows in seqfeature_relationship that reference that seqfeature as 
> either a subject or an object in a nesting relationship between 
> features. I.e., looking at the hierarchy as a graph, removing a node 
> will cascade to deleting all incoming and outgoing arcs for that node, 
> but not other nodes.
> 
> If your application wants to take down all nodes in the hierarchy when 
> one node is deleted, you need to write code to do this. (Except if, as 
> mentioned before, all features reference the same bioentry, in which 
> case deleting the bioentry will delete the entire feature hierarchy.)
> 
>     -hilmar
> 
> On Jun 20, 2005, at 3:33 AM, 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
>>>>
>>>>
>>>>
>>>
>>
>> _______________________________________________
>> BioSQL-l mailing list
>> BioSQL-l at open-bio.org
>> http://open-bio.org/mailman/listinfo/biosql-l
>>


More information about the BioSQL-l mailing list