libyears

✎ View and edit SQL

This data as json

0 records

CREATE TABLE libyears (
  -- the package that this Libyear is calculated for.
  --
  -- Foreign keys:
  -- - `renovate.package_name`
  -- - `sboms.package_name`
  package_name TEXT NOT NULL,
  -- version indicates the version of `package_name` that this Libyear is
  -- calculated for.
  --
  -- NOTE this could be a version constraint, such as any of:
  --
  --   <=1.3.4,>=1.3.0
  --   "~> 0.9"
  --   latest
  --   ^2.0.6
  --   =1.0.4
  --
  -- As well as a specific value, such as:
  --
  --   1.0.4
  --   10
  --   latest
  --
  -- This versioning will be implementation-specific for the `package_manager` in use.
  --
  -- Foreign keys:
  -- - `renovate.version`
  -- - `sboms.version`
  version TEXT NOT NULL,
  -- current_version indicates the version of `package_name` that this Libyear
  -- is calculated for.
  --
  -- If the `version` is an exact version number, such as `1.0.4`, then
  -- `current_version` will usually be the same value, `1.0.4`.
  --
  -- If the `version` is a version constraint, then this column MAY indicate
  -- the exact version that was resolved at the time of dependency analysis.
  --
  -- Foreign keys:
  -- - `renovate.current_version`
  -- - `sboms.current_version`
  current_version TEXT,
  -- package_manager indicates the package manager that this package
  -- corresponds to.
  --
  -- Based on which datasource(s) (https://dmd.tanna.dev/concepts/datasource/)
  -- you are using, this will be a different value:
  --
  -- - for Renovate data, must exactly match `renovate.package_manager`.
  --   Note that there may be multiple `package_managers`, for instance `maven`
  --   and `gradle`, which would require two rows.
  -- - for Software Bill of Materials (SBOM) data, must exactly match `sboms.package_type`
  --
  -- If you are using multiple datasources, you will have one row per
  -- `package_manager` that this Advisory matches.
  --
  -- Foreign keys:
  -- - `renovate.package_manager`
  -- - `sboms.package_type`
  package_manager TEXT NOT NULL,

  -- libyear is the value of the Libyear metric, which is defined as "how many
  -- years between the version we're currently using and the latest version
  -- released", and then totalled across all libraries used by the project.
  --
  -- Dependencies with a Libyear value of 0 could indicate that either all
  -- dependencies are up-to-date, or that the Libyear could not be calculated and
  -- are not shown.
  --
  -- Note that a low number could still be problematic, for instance if a package
  -- has pushed out a dozen releases in the last month, all of which include
  -- significant breaking changes.
  --
  -- Alternatively, a package may have a low score simply because the package is
  -- no longer maintained, so no new releases means you are "up to date".
  --
  -- It is worth using this metric with context where possible, or at least with
  -- the above caveats in mind.
  --
  -- It is recommended to display this at a precision of 2 decimal places.
  --
  -- Further reading:
  --
  --  https://chaoss.community/kb/metric-libyears/
  --  https://libyear.com/
  libyear REAL NOT NULL
    CHECK (
      libyear >= 0
    ),

  -- version_release_date is the release datetime that this `version` / `current_version` was released at
  version_release_date TEXT NOT NULL,

  -- latest_version is the latest release of this package, as known by
  -- Ecosystems (https://ecosyste.ms).
  --
  -- Sourced via Ecosystems (https://ecosyste.ms) and corresponds with the
  -- field `$.latest_release_number` in the Packages API.
  --
  -- Note that this may not always be up-to-date
  -- https://github.com/ecosyste-ms/repos/issues/442#issuecomment-1911818704
  latest_version TEXT NOT NULL,

  -- latest_version_release_date is the latest release of this package, as
  -- known by Ecosystems (https://ecosyste.ms).
  --
  -- Sourced via Ecosystems (https://ecosyste.ms) and corresponds with the
  -- field `$.latest_release_published_at` in the Packages API.
  --
  -- Note that this may not always be up-to-date
  -- https://github.com/ecosyste-ms/repos/issues/442#issuecomment-1911818704
  latest_version_release_date TEXT NOT NULL,

  -- last_synced_at indicates the last date that Ecosystems
  -- synced package data.
  --
  -- This is useful when used in conjunction with decisioning based on other
  -- fields, as it indicates how stale the data may be.
  --
  -- Sourced via Ecosystems (https://ecosyste.ms) and corresponds with the
  -- field `$.repo_metadata.last_synced_at` in the Packages API.
  --
  -- May be NULL, as the repository may not have been scanned.
  --
  -- Note that this may not always be up-to-date
  -- https://github.com/ecosyste-ms/repos/issues/442#issuecomment-1911818704
  last_synced_at TEXT NOT NULL,

  UNIQUE (package_name, package_manager, version) ON CONFLICT REPLACE
);