[BioSQL-l] SQLite support

[Peter]

On Fri, Oct 29, 2010 at 7:02 PM, Chris Fields <cjfields at illinois.edu> wrote:
>
> Should be easy enough to add this in to the main repo.  The best
> way to check it is via the various language-specific adaptors for
>  BioSQL (bioperl-db, etc).
>
> Peter, do you want the honors, or should I go ahead?

I think Brad deserves the privilege of checking it in, but otherwise
I'm happy to do it (I have been nagging about this afterall ;)
I'd like Brad's draft (which we've been using in Biopython for a
while) committed first, then any of Chris B's changes on top.

I've just taken a look at Chris B's changes - the good news is
the Biopython unit tests work with his version of the schema.
Do BioPerl or any other Bio* bindings exist for BioSQL on
SQLite yet?

Chris B has removed AUTOINCREMENT with a note at the
start explaining why. That looks OK, other than the fact the ID
of deleted rows may be reused (not sure if that matters to us).
Given this (tiny?) risk, is the performance gain significant?

More surprising to me is he has introduced extra PRIMARY
KEY columns to tables that lacked an explicit key, e.g. adding
location_qualifier_value_id to table location_qualifier_value.
The naming convention appears to be table_name_id when
table_name is the table. I'd like to understand why this was
done and if it is beneficial in some way (I don't like the fact
this differs from the other schemas).

As part of the above, any composite primary keys are now
just UNIQUE statements (e.g. tables bioentry_dbxref and
bioentry_reference) with a new extra PRIMARY KEY instead.

A minor point: there is some whitespace formatting issue in
table seqfeature_dbxref (probably tabs vs spaces, shows up
in the git diff output).

Finally in table taxon_name I think we are missing a
UNIQUE constraint or composite PRIMARY KEY (see the
MySQL schema), but this was true in Brad's schema too.

Regards,

Peter