Mon, 07 Aug 2023 12:47:52 +0100
metadata: Fix prepared statements joining on XEP status
--- 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;