[BioSQL-l] How to get a Seq object from Bio::DB::Persistent::Seq

[Hilmar Lapp]

On Tuesday, June 8, 2004, at 01:42  AM, Jochen Eisinger wrote:

>
>> (accession_number,version,namespace) is a well-established uniqueness
>> constraint on sequences in order to guarantee a minimal amount of
>> sanity.
>
> Why isn't this the primary key btw? I'm quite new to biosql and may
> still be missing some points... I'm rather surprised you're using
> artificial columns as primary keys and add unique constraints to the
> table, instead of using them as primary keys and dropping this integer
> valued id columns.

Who uses the natural primary key as the physical primary key? It's 
common and best practice not to do so, because 1) a natural primary key 
will change if you change the attribute(s), which means you'll have to 
change the foreign keys referencing it too, and 2) especially 
multi-column keys are slow to join (but even a single-column character 
column is slower). There's plenty of relational database design and 
theory textbooks out there that explain this a lot better and in depth.

>>
>> It doesn't write over an existing sequence. It will update the
>> attributes of the object you wanted to create to match those of the
>> existing object in the database, unless you pass in an object factory
>> (-obj_factory => $myseqfactory).
>
> It won't update the record in any case. If you change the length of the
> sequence for example, you will get an error "tried to lie about 
> sequence
> length"

It will update the object I said, not the record in the database. You 
cannot set $seq->length to a value other than the actual length of the 
sequence if there is one.

>>
>> If that's what you expect then run a find_by_unique_key() first to 
>> make
>> sure it's not present already. (Note that this is still no guarantee
>> because between the time you get the negative result and the time you
>> commit the create() transaction somebody else may have inserted the
>> same sequence.)
> That should not be possible, the DBs transaction system should take 
> care
> of this.

Tell me how it should be able to accomplish this. Transactions don't 
cure wrong assumptions, they just isolate concurrent access.

Lets assume you have a record to be inserted with unique key 'foo'. At 
the time you make a lookup on that key somebody else inserted a record 
with the same key but hasn't committed the transaction yet. Your lookup 
will return no record. Now you go ahead and insert the record. If the 
other user's transaction isn't rolled back, your insert will either 
fail immediately if he committed meanwhile, or it will block and fail 
once he commits.

>
> In my case, I need to have sequences with several different annotations
> stored in one db. changing the primary id of the sequences is not an
> option here.
>

If you change the UK constraint to include the namespace you should be 
fine.

	-hilmar

-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------