document suite run database schema

[matthewrmshin]

Now that we are seeing more usage of the suite run database, we should document the schema in CUG.

See also #864, #1032.

[hjoliver]

Something from the task_jobs table that wasn't clear (via email):

The intention…

time_submit: time just before running “qsub” of a job.
time_submit_exit: time just after “qsub” of a job returns.

(However, I am not sure if we have implemented the above correctly or not.)

time_run: time when the job calls “cylc message started”
time_run_exit: time when the job calls “cylc message succeeded|failed”.

[tfeb]

Note that I'm now writing a bunch of suite-performance reporting scripts which slightly rely on what the database schema is like, so it would be even cooler if it was documented. They can be redone pretty easily however.

[matthewrmshin]

I guess we should begin by adding more docs in the code where the schema is defined:
https://github.com/cylc/cylc/blob/master/lib/cylc/rundb.py

I'll work on as soon as I have a chance.

[tfeb]

Would you accept a PR if I added some docs for just the things I rely on?

…

On 18 Apr 2018, at 08:51, Matt Shin ***@***.***> wrote: I guess we should begin by adding more docs in the code where the schema is defined: https://github.com/cylc/cylc/blob/master/lib/cylc/rundb.py I'll work on as soon as I have a chance. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

[matthewrmshin]

@tfeb Please feel free to raise PRs.

[oliver-sanders]

The proposed adoption of the Sphinx documentation engine opens up the possibility for auto-documentation in many areas. Shall we supersede this issue with #2651?

[kinow]

I was doing the reverse engineer with the IDE when I wanted to pick at the class <-> db relationship, but not sure how precise it is. Definitely interested in seeing this one done (and keen to help testing/reviewing).

[kinow]

Posted on Riot.im, but added a post-it (going through all my pending post-its today 🎉 ) to add the script and link here.

#FOR: https://github.com/cylc/cylc/issues/2483

#!/usr/bin/env python3
# coding=utf-8


import sys
import tempfile

# hack until setup.py is done and we are installing it via pip/setuptools
sys.path.append("/home/kinow/Development/python/workspace/cylc/lib/")
from eralchemy import render_er

from cylc.rundb import CylcSuiteDAO


def main():
    """Create cylc public database, then run diagram creation tool."""
    with tempfile.NamedTemporaryFile() as tf:
        # is_public=False triggers the creation of tables
        CylcSuiteDAO(db_file_name=tf.name, is_public=False)
        db_name = "sqlite:///{}".format(tf.name)
        render_er(db_name, "cylc-database.png")


if __name__ == '__main__':
    main()

And a sphinx plugin that does the same without the need for this script (but I have not tested it yet): https://github.com/sphinx-contrib/sqlalchemyviz

The main obstacle right now for this are the database constraints, which are missing in the DB schema. Adding them should be easy, but there is a small risk of causing performance issues as sqlite will do some constraint checking when editing data.

Here's what it looks like at the moment:

[matthewrmshin]

(Interesting, because I thought most of the tables should have some primary keys.)

[kinow]

Sorry, should have mentioned it is actually the foreign key constraints that are missing. You are correct, primary keys should be there all right.

[matthewrmshin]

Yes, I guess we can add some foreign keys between tables - mostly for mapping task names and cycle points.

[oliver-sanders]

Would be good, even just for the purposes of standardisation.