[BioSQL-l] Versioning in BioSQL Database

[Hilmar Lapp]

Hi Luke,

The version field in the bioentry table, and by extension the concept of versioning bioentry records as you describe, are modeled off of sequence records held in public databases, such as GenBank and the version of GenBank records.

You can use the version field in this way (and it would normally populated this way, if, for example, loading a GenBank database), but note that there isn’t some kind of automatic versioning of records if and when they are changed. You’d have to write code that does this. And if you were to load a GenBank database, I believe you would normally  only get the latest version, so the problem of linking to (or finding) the latest version for a given accession number wouldn’t necessarily present itself. (It would if you were to update by loading a then-updated GenBank database later, which would add later versions for some records. However, querying for “the latest” you would still have to do yourself.)

There is though a relationship table that would allow you to load a pointer for every record to the latest version. 

  -hilmar

> On Aug 2, 2020, at 8:51 AM, Luke Swaby <lukeswabypetts at gmail.com> wrote:
> 
> Hello
> 
> I’m currently writing a load of Python scripts to interact with a slightly modified version of your BioSQL database (pretty much identical, with an additional table for metadata), and am having a lot of trouble working out how to implement record versioning in it.
> 
> Comments in the schema—and the version field in the bioentry table—suggest a particular method of doing this was in mind when the schema was written, but I can’t find any documentation on how this is to be done. 
> 
> In sum, I need it to be able to hold multiple versions of the same record in the same table, and a means of accessing (ideally by default) only the latest version of each record when it is requested. It would ideally also automatically add a new version of a record whenever any change is made to it, either internally by direct MySQL queries or externally by ingestion of newly modified .gb/.csv files. I’ve thought of numerous ways of doing this, including creating a new table which points to the unique bioentry_id/metadata_id of the latest version of each record, but am very stuck on how to do this.
> 
> Do you have any suggestions? I’m sure there is a relatively basic answer that I am totally missing, but I’m pressed for time now and am finding nothing anywhere!
> 
> Very much appreciated if so
> 
> Luke
> _______________________________________________
> BioSQL-l mailing list
> BioSQL-l at mailman.open-bio.org
> https://mailman.open-bio.org/mailman/listinfo/biosql-l

-- 
Hilmar Lapp -:- lappland.io



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.open-bio.org/pipermail/biosql-l/attachments/20200803/c9f1e3dc/attachment.htm>