tip/metadata.yml

---

title: "XMPP ecosystem data"
description: "Explore the ecosystem of XMPP extensions and software"
source: "XMPP Standards Foundation"
source_url: "https://xmpp.org/"

databases:
  xmpp:
    tables:
      xeps:
        label_column: title
      software:
        label_column: name
      compliance_levels:
        columns:
          xep_id: XEP
      implementations:
        columns:
          xep_id: XEP
          xep_version: XEP version
          software_id: Software
    queries:
      outdated_implementations:
        title: "Implementations: needing updates"
        description: "Find implementations not on the latest version of a XEP"
        sql: |-
          select
            software.name,
            xeps.number as xep_num,
            xeps.title as xep_name,
            xep_version as implemented_xep_version,
            xeps.version as latest_xep_version
          from
            implementations
            join xeps on implementations.xep_id == xeps.id
            join software on implementations.software_id == software.id
          where
            xep_version != xeps.version
      implementation_counts:
        title: "XEPs: Ranked by implementation count"
        description: "XEPs ranked by the number of known implementations"
        sql: |-
          select
            xeps.number,
            xeps.title,
            (
              select
                count(*)
              from
                implementations
              where
                implementations.xep_id == xeps.id
                and (
                  implementations.status is null
                  or implementations.status in ('complete', 'partial')
                )
            ) as implementation_count
          from
            xeps
          order by
            implementation_count desc,
            xeps.number asc
      unusual_compliance:
        title: "XEPs: Unusual compliance requirements"
        description: "Find XEPs in undesired states that are required by current compliance suites"
        sql: |-
          select
            xeps.number,
            xeps.title,
            xeps.status,
            compliance_levels.category,
            compliance_levels.level
          from
            xeps
            join compliance_levels on compliance_levels.xep_id = xeps.id
          where
            compliance_levels.level not null
            and xeps.status not in ('Active','Stable','Final');
      deferred:
        title: "XEPs: Deferred with implementations"
        description: "Show deferred XEPs with implementation counts"
        sql: |-
          select
            xeps.number,
            xeps.title,
            xeps.last_updated,
            count(implementations.xep_id) as implementations
          from
            xeps
            join implementations on implementations.xep_id = xeps.id
          where
            xeps.status = "Deferred"
          group by
            xeps.id
          order by
            implementations desc, last_updated desc;
      expiring:
        title: "XEPs: Expiring soon"
        description: "Show XEPs soon to become deferred"
        sql: |-
          select
            xeps.number,
            xeps.title,
            xeps.last_updated,
            count(implementations.xep_id) as implementations
          from
            xeps
            join implementations on implementations.xep_id = xeps.id
          where
            xeps.status = "Experimental"
          group by
            xeps.id
          order by
            last_updated asc;
      last_call:
        title: "XEPs: Proposed XEPs under Last Call"
        description: "List open Last Calls"
        sql: |-
          select
            xeps.number,
            xeps.title,
            xeps.last_updated
          from
            xeps
          where
            xeps.status = "Proposed"
          order by
            last_updated desc;
      inbox:
        title: "XEPs: Inbox"
        description: "All submissions not accepted and assigned a XEP number"
        sql: |-
          select
            xeps.title,
            xeps.last_updated
          from
            xeps
          where
            xeps.number is null
          order by
            last_updated desc;
      needs_deferral:
        title: "XEPS: Due deferral"
        description: "XEPs that are still Experimental but not updated for over a year"
        sql: |-
          select
            xeps.number,
            xeps.title,
            xeps.last_updated
          from
            xeps
          where
            xeps.status = "Experimental"
            and xeps.last_updated <= date('now','-12 months')
          order by
            xeps.last_updated asc;