component_check_conformance.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7fpo23ErO_lL"
      },
      "source": [
        "## Perform manual conformance checks"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "B_nDBOyG2279"
      },
      "source": [
        "[Conforming publications](https://standard.open-contracting.org/latest/en/schema/conformance_and_extensions/#publication-conformance):\n",
        "\n",
        "* Use a [registered OCID prefix](https://standard.open-contracting.org/latest/en/schema/identifiers/#contracting-process-identifier-ocid).\n",
        "* The [OCDS Data Review Tool](https://standard.open-contracting.org/review/) can report results on the data.\n",
        "* Must not use terms from outside the OCDS schema where its terms would suffice.\n",
        "\n",
        "Use this section to:\n",
        "\n",
        "* Check if the proper OCID prefix is in use.\n",
        "* Check if the OCDS Data Review Tool is able to report results on data.\n",
        "* Identify fields in local extensions and additional fields and codes that should be mapped to fields and codes in the OCDS schema and extensions.\n",
        "* Identify deprecated fields, in order to recommend a change to the publisher's OCDS implementation.\n",
        "\n",
        "\n",
        "You can use the following resources to find fields and codes with similar semantics:\n",
        "\n",
        "* [OCDS Schema and Codelist Reference](https://standard.open-contracting.org/latest/en/schema/), for fields in the core OCDS schema.\n",
        "* [OCDS Extensions Field and Code Search](https://open-contracting.github.io/editor-tools/), for fields and codes in OCDS extensions.\n",
        "* [GitHub Issue Tracker](https://github.com/open-contracting/standard/issues), for discussions about adding new fields and codes.\n",
        "\n",
        "Check that field and code names [conform to the style guide](https://ocds-standard-development-handbook.readthedocs.io/en/latest/meta/schema_style_guide.html#field-and-code-names) and report any issues to the publisher.\n",
        "\n",
        "If you cannot find a suitable mapping for an additional field or code, [open a GitHub issue](https://github.com/open-contracting/standard/issues) to describe the semantics of the field or code and to discuss how to model it. Report any issues to the publisher.\n",
        "\n",
        "**Note:** This section depends on the `check` step of Kingfisher Process having completed. See *Check for structure and format errors >Confirm that checks are complete*. If the checks are not complete or the OCDS Data Review Tool is not able to report results on data, **the publisher don't pass that conformance criterion**"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### OCID prefix\n",
        "\n",
        "Check that the data uses the OCID prefix that was issued to this specific publisher.\n",
        "\n",
        "You only need to run either the *Release prefixes* or *Record prefixes* section, depending on the publication's data format.\n",
        "\n",
        "Update the ocid prefix in the appropriate cell. Prefixes can be found at [the list of registered prefixes](https://docs.google.com/spreadsheets/d/1E5ZVhc8VhGOakCq4GegvkyFYT974QQb-sSjvOfaxH7s/pubhtml?gid=506986894&single=true&widget=true)."
      ],
      "metadata": {
        "collapsed": false
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### **Release prefixes**\n",
        "\n",
        "Notify the publisher of any incorrect prefixes."
      ],
      "metadata": {
        "collapsed": false
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "source": [
        "# Do not remove the final % character\n",
        "ocid_prefix = \"%\""
      ],
      "metadata": {
        "collapsed": false
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "source": [
        "%%sql ocid_prefix_release_check <<\n",
        "SELECT ocid\n",
        "FROM\n",
        "    release_summary\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "    AND ocid NOT LIKE :ocid_prefix\n"
      ],
      "metadata": {
        "collapsed": false
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "source": [
        "ocid_prefix_release_check"
      ],
      "metadata": {
        "collapsed": false
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### **Record prefixes**\n",
        "\n",
        "Notify the publisher of any incorrect prefixes."
      ],
      "metadata": {
        "collapsed": false
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "source": [
        "%%sql ocid_prefix_record_check <<\n",
        "SELECT ocid\n",
        "FROM\n",
        "    record_summary\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "    AND ocid NOT LIKE :ocid_prefix\n"
      ],
      "metadata": {
        "collapsed": false
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "outputs": [],
      "source": [
        "ocid_prefix_record_check"
      ],
      "metadata": {
        "collapsed": false
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4oFJe2cw9HlA"
      },
      "source": [
        "### Local extensions"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "72PpTzAP9NSI"
      },
      "source": [
        "For each field and code in extensions authored by the publisher, in addition to the above checks, consider whether to [review the extension in detail](https://docs.google.com/document/d/1CS_TMubqoYaucT8JXPTgLS-mF4eMIifX-6mD0xpWg9M/edit)."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LzOXXZob9gA2"
      },
      "source": [
        "List the extensions declared in the package metadata.\n",
        "\n",
        "**Note:** This query should be kept in sync with the query in *Check scope > Extensions*.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Yul28_sU9gA2"
      },
      "source": [
        "%%sql\n",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    jsonb_array_elements(package_data -> 'extensions') AS ocds_extension,\n",
        "    count(*) AS count\n",
        "FROM\n",
        "    release_summary\n",
        "WHERE\n",
        "    collection_id IN :collection_ids\n",
        "    AND package_data IS NOT NULL\n",
        "GROUP BY\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    ocds_extension\n",
        "ORDER BY\n",
        "    collection_id ASC,\n",
        "    release_type ASC,\n",
        "    count DESC;\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "CeabYppc20t4"
      },
      "source": [
        "### Additional fields"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "52thQMhSsTe8"
      },
      "source": [
        "[lib-cove-ocds](https://github.com/open-contracting/lib-cove-ocds) reports additional fields in the following scenarios:\n",
        "\n",
        "* Fields from undeclared extensions.\n",
        "* Fields with language variations, e.g. `title_es`. You do not need to report language variations to the publisher, but you should check that the field [conforms to the rules for language variations](https://standard.open-contracting.org/latest/en/schema/reference/#language).\n",
        "* OCDS 1.0 data using extension fields. You should report the fields to the publisher and recommend that they upgrade to OCDS 1.1.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rOaRxbxgpzKw"
      },
      "source": [
        "List additional fields.\n",
        "\n",
        "By default, results are reported for a sample of 10% of releases. For small collections, you can set `sample_size` to `1` to return results for the full collection. For large collections, you can reduce the sample size."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KQlVv7YZjbED"
      },
      "source": [
        "sample_size = 0.1"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RbJJ5k0SmOTB"
      },
      "source": [
        "%%sql\n",
        "WITH check_results AS (\n",
        "    SELECT\n",
        "        *,\n",
        "        CASE\n",
        "            WHEN (release_type IN ('record', 'embedded_release'))\n",
        "                THEN\n",
        "                    record_check\n",
        "            ELSE\n",
        "                release_check\n",
        "        END AS results\n",
        "    FROM\n",
        "        release_summary\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type <> 'compiled_release'\n",
        "        AND random() < :sample_size\n",
        "),\n",
        "\n",
        "counts AS (\n",
        "    SELECT\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        additional_fields ->> 'path' AS path,\n",
        "        additional_fields ->> 'field_name' AS field,\n",
        "        sum((additional_fields ->> 'count')::int) AS count\n",
        "    FROM\n",
        "        check_results\n",
        "    CROSS JOIN\n",
        "        jsonb_array_elements(\n",
        "            results -> 'all_additional_fields'\n",
        "        ) AS additional_fields\n",
        "    GROUP BY\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        field,\n",
        "        path\n",
        "    ORDER BY\n",
        "        path ASC,\n",
        "        count DESC\n",
        "),\n",
        "\n",
        "examples AS (\n",
        "    SELECT DISTINCT ON (\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        additional_fields ->> 'path',\n",
        "        additional_fields ->> 'field_name')\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        additional_fields ->> 'path' AS path,\n",
        "        additional_fields ->> 'field_name' AS field,\n",
        "        additional_fields ->> 'examples' AS examples\n",
        "    FROM\n",
        "        check_results\n",
        "    CROSS JOIN\n",
        "        jsonb_array_elements(\n",
        "            results -> 'all_additional_fields'\n",
        "        ) AS additional_fields\n",
        "    WHERE\n",
        "        jsonb_array_length(additional_fields -> 'examples') > 0\n",
        ")\n",
        "\n",
        "SELECT\n",
        "    counts.collection_id,\n",
        "    counts.release_type,\n",
        "    counts.path,\n",
        "    counts.field,\n",
        "    count,\n",
        "    examples\n",
        "FROM\n",
        "    counts\n",
        "LEFT JOIN examples\n",
        "    USING (\n",
        "        collection_id,\n",
        "        release_type,\n",
        "        path,\n",
        "        field\n",
        "    )\n",
        "ORDER BY\n",
        "    path,\n",
        "    field;\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "v_6_-h72u3_Q"
      },
      "source": [
        "#### Additional field examples\n",
        "\n",
        "Generate a release package containing an example release for each additional field:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oMBWTfjljvdc"
      },
      "source": [
        "%%sql additional_field_examples <<\n",
        "WITH additional_field_releases AS (\n",
        "    SELECT\n",
        "        ocid,\n",
        "        release.release_id,\n",
        "        data_id,\n",
        "        additional_fields ->> 'path' AS path,\n",
        "        additional_fields ->> 'field_name' AS field\n",
        "    FROM\n",
        "        release_check\n",
        "    CROSS JOIN\n",
        "        jsonb_array_elements(\n",
        "            cove_output -> 'all_additional_fields'\n",
        "        ) AS additional_fields\n",
        "    INNER JOIN release ON release_check.release_id = release.id\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND random() < :sample_size\n",
        "),\n",
        "\n",
        "additional_fields AS (\n",
        "    SELECT DISTINCT\n",
        "        path,\n",
        "        field\n",
        "    FROM\n",
        "        additional_field_releases\n",
        "),\n",
        "\n",
        "examples AS (\n",
        "    SELECT DISTINCT ON (\n",
        "        additional_fields.path,\n",
        "        additional_fields.field)\n",
        "        additional_fields.path,\n",
        "        additional_fields.field,\n",
        "        ocid,\n",
        "        release_id,\n",
        "        data_id,\n",
        "        data\n",
        "    FROM\n",
        "        additional_fields\n",
        "    INNER JOIN additional_field_releases\n",
        "        ON\n",
        "            additional_fields.path = additional_field_releases.path\n",
        "            AND additional_fields.field = additional_field_releases.field\n",
        "    INNER JOIN data ON data.id = data_id\n",
        "    ORDER BY\n",
        "        additional_fields.path,\n",
        "        additional_fields.field\n",
        ")\n",
        "\n",
        "SELECT jsonb_build_object('releases', jsonb_agg(data)) AS release_package\n",
        "FROM\n",
        "    examples\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YPHZX987mOG-"
      },
      "source": [
        "render_json(additional_field_examples[\"release_package\"][0])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tMVFNCDrmb0l"
      },
      "source": [
        "### Additional codes"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HIiCZQni2kbA"
      },
      "source": [
        "List additional codes in the context of an open codelist.\n",
        "\n",
        "Using additional codes in the context of a closed codelist is an error, and is reported in the *Check for structure and format errors* section."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "V8Gx2WAOqERy"
      },
      "source": [
        "%%sql\n",
        "WITH check_results AS (\n",
        "    SELECT\n",
        "        *,\n",
        "        CASE\n",
        "            WHEN (release_type IN ('record', 'embedded_release'))\n",
        "                THEN\n",
        "                    record_check\n",
        "            ELSE\n",
        "                release_check\n",
        "        END AS results\n",
        "    FROM\n",
        "        release_summary\n",
        "    WHERE\n",
        "        collection_id IN :collection_ids\n",
        "        AND release_type <> 'compiled_release'\n",
        ")\n",
        "\n",
        "SELECT\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    additional_open_codelist_values.value -> 'codelist' AS codelist,\n",
        "    codes.value AS code,\n",
        "    count(*) AS occurrences\n",
        "FROM\n",
        "    check_results\n",
        "CROSS JOIN\n",
        "    jsonb_each(\n",
        "        results -> 'additional_open_codelist_values'\n",
        "    ) AS additional_open_codelist_values\n",
        "CROSS JOIN jsonb_array_elements(value -> 'values') AS codes\n",
        "GROUP BY\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    codelist,\n",
        "    code\n",
        "ORDER BY\n",
        "    collection_id,\n",
        "    release_type,\n",
        "    codelist,\n",
        "    count(*) DESC\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MiFFM6zOlGTp"
      },
      "source": [
        "### Deprecated fields"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kzUGDNQqrlKq"
      },
      "source": [
        "Before a field or codelist is removed from the standard, it is first marked as [deprecated](https://standard.open-contracting.org/latest/en/governance/deprecation/#deprecation).\n",
        "\n",
        "Use this section to check for deprecated fields."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "fZisRK9kr6MZ"
      },
      "source": [
        "List deprecated fields:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Oc0ex2vNlIOU"
      },
      "source": [
        "%%sql\n",
        "SELECT DISTINCT ON (collection_id, path, deprecated_version, explanation)\n",
        "    collection_id,\n",
        "    regexp_replace(trim('\"' FROM paths::text), '\\/[0-9]+', '', 'g')\n",
        "    || '/'\n",
        "    || (deprecated_fields ->> 'field') AS path,\n",
        "    deprecated_fields -> 'explanation' -> 0 AS deprecated_version,\n",
        "    deprecated_fields -> 'explanation' -> 1 AS explanation,\n",
        "    ocid AS example_ocid\n",
        "FROM\n",
        "    release_check\n",
        "CROSS JOIN\n",
        "    jsonb_array_elements(\n",
        "        cove_output -> 'deprecated_fields'\n",
        "    ) AS deprecated_fields\n",
        "CROSS JOIN jsonb_array_elements(deprecated_fields -> 'paths') AS paths\n",
        "INNER JOIN release ON release_check.release_id = release.id\n",
        "WHERE\n",
        "    collection_id IN :collection_ids;\n"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}