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

Change SQuaSH to store JSON as a BLOB/TEXT in MariaDB 10.1+

    Details

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

      Description

      As shown in DM-8890 MariaDB 10.1+ does not have native JSON support, as an alternative to use MariaDB in production this ticket captures the work required to change SQuaSH to store JSON as a BLOB/TEXT in MariaDB 10.1+

        Attachments

          Issue Links

            Activity

            Hide
            afausti Angelo Fausti added a comment - - edited

            While MariaDB 10.1 does not have native JSON support, the alternative is to use blob/text to store JSON. The drawback of simple using TextField data type is that there's no validation during insertion and some JSON data types like boolean, null, empty string and empty object are not supported.

            This ticket investigates available packages for django that subclasses the TextField and implements the JSONField. It also implements tests to make sure that the JSON supported data types are properly (de)serialized in DRF. The final goal is to deploy SQuaSH using MariaDB 10.1 in production and continue working on DM-7043 with this setup.

            Show
            afausti Angelo Fausti added a comment - - edited While MariaDB 10.1 does not have native JSON support, the alternative is to use blob/text to store JSON. The drawback of simple using TextField data type is that there's no validation during insertion and some JSON data types like boolean, null, empty string and empty object are not supported. This ticket investigates available packages for django that subclasses the TextField and implements the JSONField. It also implements tests to make sure that the JSON supported data types are properly (de)serialized in DRF. The final goal is to deploy SQuaSH using MariaDB 10.1 in production and continue working on DM-7043 with this setup.
            Hide
            afausti Angelo Fausti added a comment - - edited

            Here, for testing purposes a new field 'data' of type TextField was added in the measurement table.

            data = models.TextField()
            

            https://docs.djangoproject.com/en/1.10/ref/models/fields/#django.db.models.TextField

            which is mapped to MariaDB "longtext" data type.

            MariaDB [squash]> show fields from dashboard_measurement;
            +-----------+-------------+------+-----+---------+----------------+
            | Field     | Type        | Null | Key | Default | Extra          |
            +-----------+-------------+------+-----+---------+----------------+
            | id        | int(11)     | NO   | PRI | NULL    | auto_increment |
            | value     | float    | NO   |     | NULL    |                |
            | data     | longtext    | NO   |     | NULL    |                |
            | job_id    | int(11)     | NO   | MUL | NULL    |                |
            | metric_id | varchar(16) | NO   | MUL | NULL    |                |
            +-----------+-------------+------+-----+---------+----------------+
            4 rows in set (0.00 sec)
            
            

            The test includes the following data types supported by JSON format. The result of the test shows that one cannot stored boolean, null representation and empty strings or objects. Example:

            null 
            

            or

            {"null": null} 
            

            empty JSON objects

            {}
            

            empty strings

            ""
            

            Show
            afausti Angelo Fausti added a comment - - edited Here, for testing purposes a new field 'data' of type TextField was added in the measurement table. data = models.TextField() https://docs.djangoproject.com/en/1.10/ref/models/fields/#django.db.models.TextField which is mapped to MariaDB "longtext" data type. MariaDB [squash]> show fields from dashboard_measurement; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | value | float | NO | | NULL | | | data | longtext | NO | | NULL | | | job_id | int(11) | NO | MUL | NULL | | | metric_id | varchar(16) | NO | MUL | NULL | | +-----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) The test includes the following data types supported by JSON format. The result of the test shows that one cannot stored boolean, null representation and empty strings or objects. Example: null or { "null" : null } empty JSON objects {} empty strings ""
            Hide
            afausti Angelo Fausti added a comment - - edited

            After looking for alternatives, there are packages available in django to store JSON.

            https://djangopackages.org/grids/g/json-fields/

            For instance, https://github.com/bradjasper/django-jsonfield seems mature and care about the issues above. It subclasses the TextField and make sure that the (de)serialization of JSON is done correctly.

            The pypi package is:

            jsonfield (1.0.3) - A reusable Django field that allows you to store validated JSON in your model.

            It has a larger number of contributors and it is used in more projects than other packages, but it is not currently active (last commit Dec 2015) and we don't know about Django 1.9 support.

            Other packages I've looked at:

            django-json-field (0.5.7) - Generic JSON model and form fields. (does not support py3)
            django-jsonfield (1.0.1) - JSONField for django models (just one contributor)

            Show
            afausti Angelo Fausti added a comment - - edited After looking for alternatives, there are packages available in django to store JSON. https://djangopackages.org/grids/g/json-fields/ For instance, https://github.com/bradjasper/django-jsonfield seems mature and care about the issues above. It subclasses the TextField and make sure that the (de)serialization of JSON is done correctly. The pypi package is: jsonfield (1.0.3) - A reusable Django field that allows you to store validated JSON in your model. It has a larger number of contributors and it is used in more projects than other packages, but it is not currently active (last commit Dec 2015) and we don't know about Django 1.9 support. Other packages I've looked at: django-json-field (0.5.7) - Generic JSON model and form fields. (does not support py3) django-jsonfield (1.0.1) - JSONField for django models (just one contributor)
            Hide
            afausti Angelo Fausti added a comment - - edited

            The combination of parameters in the field definition to allow "", {} and null is the following.

            class Measurement(models.Model):
                """Measurement of a metric by a job"""
                metric = models.ForeignKey(Metric, null=False)
                job = models.ForeignKey(Job, null=False, related_name='measurements')
                value = models.FloatField()
                data = JSONField(null=True, blank=True, default=None)
            

            The default=None is properly serialized to null.

            Show
            afausti Angelo Fausti added a comment - - edited The combination of parameters in the field definition to allow "" , {} and null is the following. class Measurement(models.Model): """Measurement of a metric by a job""" metric = models.ForeignKey(Metric, null = False ) job = models.ForeignKey(Job, null = False , related_name = 'measurements' ) value = models.FloatField() data = JSONField(null = True , blank = True , default = None ) The default=None is properly serialized to null .
            Hide
            afausti Angelo Fausti added a comment -

            Added tests to make sure all JSON data types are supported.

            Show
            afausti Angelo Fausti added a comment - Added tests to make sure all JSON data types are supported.
            Show
            afausti Angelo Fausti added a comment - See PR https://github.com/lsst-sqre/qa-dashboard/pull/30
            Hide
            afausti Angelo Fausti added a comment -

            Joshua Hoblitt for the record, have tested the changes against post-qa 1.2.2 and it is working fine.

            Show
            afausti Angelo Fausti added a comment - Joshua Hoblitt for the record, have tested the changes against post-qa 1.2.2 and it is working fine.
            Hide
            afausti Angelo Fausti added a comment -

            Merged changes to master

            Show
            afausti Angelo Fausti added a comment - Merged changes to master

              People

              • Assignee:
                afausti Angelo Fausti
                Reporter:
                afausti Angelo Fausti
                Reviewers:
                Joshua Hoblitt
                Watchers:
                Angelo Fausti, Joshua Hoblitt
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Summary Panel