[BioSQL-l] SQLite support

[Chris Fields]

On Oct 29, 2010, at 1:27 PM, Peter wrote:

> 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.

Works for me, just need to get it added in.

> 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?

No, I don't think so.  Not sure how much work it would be, but we could probably use MySQL or Pg bindings to get it going.  

We have also discussed creating DBIx::Class bindings to BioSQL (Perl ORM), though I haven't heard much on this since BOSC.  That basically removes the need for creating database-specific bindings.

> 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

Not sure about all these, but I don't think they necessarily block adding this in.

chris