[BioSQL-l] Versioning in BioSQL Database

[Luke Swaby]

Hello

Thanks so much for the suggestions—very much appreciated! Currently I'm
dealing with local revisions to annotated sequences, so it looks like I'll
have to gain a better understanding of what happens in GenBank when a
record is updated and write my own code to emulate this. I think I have
been trying to automate a little too much on the MySQL side when it can all
just be done manually in Python.

I will definitely check out the relationship table!

Many thanks again

Luke

On Mon, Aug 3, 2020 at 6:24 PM Hilmar Lapp <hlapp at drycafe.net> wrote:

> 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/20200804/c69db71f/attachment.htm>