[BioSQL-l] _removeSequence
Hilmar Lapp
hlapp at gnf.org
Tue Jun 21 06:17:42 EDT 2005
On Jun 21, 2005, at 2:46 AM, Martina wrote:
> 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.
No this is not different types of features; it's only whether the
features are nested or not.
> 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
I'm not sure whether Biojava imposes this as a limitation, but Biosql
certainly doesn't since it assumes a n:n relationship. In reality,
nested features compliant with SO/SOFA will be trees though, I believe.
> , and if that feature is deleted, all its children should get deleted
> too?
No, as I said below. To be more precise, not by the mechanism of
cascading deletes (remember: cascading deletes only follow foreign key
constraints - and a feature doesn't have a foreign key to another one).
Your software or Biojava may implement it the way you suggested, but no
RDBMS is going to do this for you.
-hilmar
>
> 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
>>>
>>>
--
-------------------------------------------------------------
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