[BioSQL-l] Tables without a (composite) primary key

[Peter]

On Thu, Nov 6, 2008 at 12:23 PM, Raoul Jean Pierre Bonnal
<raoul.bonnal at itb.cnr.it> wrote:
>
> Dear Peter,
> I'm writing the wrapper for BioRuby using DataMapper an ORM (Active Record is
> similar).

Hi Raul,

I'm looking at a python based ORM to use with BioSQL.

[The existing Biopython BioSQL bridge uses raw SQL to turn the
sequences and features into Biopython objects - this all seems to work
fine, but it doesn't offer the full flexibility of an ORM framework.]

>
> I think we can cosider to move or branch BioSQL' schema to the approach
> suggested by this kind of ORMs, with a pk for every table named "id" and a
> table name in plural. Fk names are quite correct.
>

I don't think it makes sense to add a single primary key to many of
these tables (e.g. term_dbxref).  The existing composite primary keys
seem fine (its just a shame some ORMs can't cope).

I was thinking the tables currently lacking any primary key could get
one (based on the current UNIQUE rule).  So for example, the
taxon_name could use (taxon_id,name,name_class) as its primary key.  I
don't know how big a change this would be - but superficially it looks
backwards compatible.  This is why I was asking why they didn't have
PK in the first place.

>
> PS: DataMapper handles very well composite PK, much better tha ActiveRecord.
>

For python, currently Django and also I believe SQLObjects don't
support composite primary keys.  I'll take a look at SQLAlchemy next
which should cope better.

Peter