Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-12193

Redesign SQuaSH QC Tier 0 database to allow measurements from different lsst.verify packages

    XMLWordPrintable

    Details

    • Type: Story
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      SQuaSH QC Tier 0 database must be generic to allow measurements from different lsst.verify packages such as jointcal. The work related to reimplement the SQuaSH API is covered in a separate epic DM-12787

        Attachments

          Issue Links

            Activity

            Hide
            afausti Angelo Fausti added a comment - - edited

            Desired behavior is to store timestamps in UTC computed always server side. In MySQL this is accomplished with the TIMESTAMP datatype;

             
            mysql> create table tmp (timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);
            Query OK, 0 rows affected (0.03 sec)
            
            

            mysql> describe  jobs;
            +------------+-------------+------+-----+-------------------+----------------+
            | Field      | Type        | Null | Key | Default           | Extra          |
            +------------+-------------+------+-----+-------------------+----------------+
             
            | timestamp   | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
            +------------+-------------+------+-----+-------------------+----------------+
            6 rows in set (0.00 sec)
            

            which in SQLAlchemy translates to:

             
            from sqlalchemy.sql import expression
            from sqlalchemy.ext.compiler import compiles
            from sqlalchemy.dialects.mysql import TIMESTAMP
             
            class now(expression.FunctionElement):
                type = TIMESTAMP
             
            @compiles(now, 'mysql')
            def mysql_now(element, compiler, **kw):
                return "CURRENT_TIMESTAMP()"
            
            

             
            class TestModel(db.Model):
                __tablename__ = 'tmp'
                timestamp = db.Column(db.TIMESTAMP, nullable=False, server_default=now())
            

            Show
            afausti Angelo Fausti added a comment - - edited Desired behavior is to store timestamps in UTC computed always server side. In MySQL this is accomplished with the TIMESTAMP datatype;   mysql> create table tmp (timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP); Query OK, 0 rows affected (0.03 sec) mysql> describe jobs; +------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+-------------------+----------------+   | timestamp | timestamp | NO | | CURRENT_TIMESTAMP | | +------------+-------------+------+-----+-------------------+----------------+ 6 rows in set (0.00 sec) which in SQLAlchemy translates to:   from sqlalchemy.sql import expression from sqlalchemy.ext.compiler import compiles from sqlalchemy.dialects.mysql import TIMESTAMP   class now(expression.FunctionElement): type = TIMESTAMP   @compiles (now, 'mysql' ) def mysql_now(element, compiler, * * kw): return "CURRENT_TIMESTAMP()"   class TestModel(db.Model): __tablename__ = 'tmp' timestamp = db.Column(db.TIMESTAMP, nullable = False , server_default = now())
            Hide
            afausti Angelo Fausti added a comment - - edited

            New DB schema for SQuaSH QC-0 database

            Notable changes compared with the previous implementation:

            • more extensive use of JSON data type for fields that not require query and can have arbitrary content
            • metrics and metric specifications are now in separate tables (following the lsst.verify design guidelines)
            • added package field in the metric table, it allows to group metrics by package
            • data blobs are stored in a separate table
            • support jobs from multiple users
            • support multiple execution environments
            • provenance information in the job and in the package tables are not tight to the CI environment anymore.
            • Support queries in JSON() fields, like the job look up in environment metadata
            Show
            afausti Angelo Fausti added a comment - - edited New DB schema for SQuaSH QC-0 database Notable changes compared with the previous implementation: more extensive use of JSON data type for fields that not require query and can have arbitrary content metrics and metric specifications are now in separate tables (following the lsst.verify design guidelines) added package field in the metric table, it allows to group metrics by package data blobs are stored in a separate table support jobs from multiple users support multiple execution environments provenance information in the job and in the package tables are not tight to the CI environment anymore. Support queries in JSON() fields, like the job look up in environment metadata
            Hide
            afausti Angelo Fausti added a comment - - edited

            See https://jira.lsstcorp.org/browse/DM-12194 for the corresponding pull request and detailed model implementation.

            See also https://sqr-009.lsst.io/#appendix

            Show
            afausti Angelo Fausti added a comment - - edited See https://jira.lsstcorp.org/browse/DM-12194 for the corresponding pull request and detailed model implementation. See also https://sqr-009.lsst.io/#appendix

              People

              Assignee:
              afausti Angelo Fausti
              Reporter:
              afausti Angelo Fausti
              Watchers:
              Angelo Fausti
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Jenkins

                  No builds found.