[BioSQL-l] SQLite support

[Peter]

On Fri, Oct 29, 2010 at 7:58 PM, Chris Fields <cjfields at illinois.edu> wrote:
> On Oct 29, 2010, at 1:27 PM, Peter wrote:
>
>> 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.
>

Since you sounded keen Chris, and Brad wasn't replying, I went
ahead and checked it in:

http://github.com/biosql/biosql/tree/4315be111d7d9eaa47bb3674eeed89e045d2c07a

On Fri, Oct 29, 2010 at 10:59 PM, Christopher Bottoms wrote:
> On Fri, Oct 29, 2010 at 1:27 PM, Peter wrote:
>> 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?
>
> Using AUTOINCREMENT causes "INSERTs to run a little slower" (see
> http://www.sqlite.org/autoinc.html). That doesn't sound like it is
> game-changing.
>

I've checked that in too,
http://github.com/biosql/biosql/commit/4ef99fbb48366631cca0845baaa3b63ded948c35

>> 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).
>>
>
> I thought that there was a new version of BioSQL coming out for which
> this was going to be done for all of the schemas. Roger Hall, for
> example, did the same things with the MySQL schema for BioSQL.
>

I wasn't aware of that - and it hasn't been checked in (at least, not
on the master branch). If all the BioSQL schemas are getting these
new id fields, then it makes sense to add them to the SQLite schema
too of course.

>>
>> A minor point: there is some whitespace formatting issue in
>> table seqfeature_dbxref (probably tabs vs spaces, shows up
>> in the git diff output).
>>
>
> Sorry, I thought I tried to maintain the original formatting as much
> as possible.

No problem.

>> 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.
>>
>
> This is what is in the file I sent earlier:
>
> CREATE TABLE taxon_name (
>       taxon_name_id    INTEGER PRIMARY KEY,
>       taxon_id         INTEGER,
>       name             VARCHAR(255)  NOT NULL,
>       name_class       VARCHAR(32)  NOT NULL,
>       UNIQUE (taxon_id,name,name_class)
> );

Looks like a false alarm - I must have misread the diff or
something. Sorry.

Peter