[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