[BioSQL-l] _removeSequence
Hilmar Lapp
hlapp at gnf.org
Mon Jun 20 15:19:04 EDT 2005
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
>
--
-------------------------------------------------------------
Hilmar Lapp email: lapp at gnf.org
GNF, San Diego, Ca. 92121 phone: +1-858-812-1757
-------------------------------------------------------------
More information about the BioSQL-l
mailing list