Mon, 20 Mar 2023 11:08:08 +0000
Add canned query 'implementation_counts'
0 | 1 | --- |
2 | ||
3 | title: "XMPP ecosystem data" | |
4 | description: "Explore the ecosystem of XMPP extensions and software" | |
5 | source: "XMPP Standards Foundation" | |
6 | source_url: "https://xmpp.org/" | |
7 | ||
8 | databases: | |
9 | xmpp: | |
10 | tables: | |
11 | xeps: | |
12 | label_column: title | |
13 | software: | |
14 | label_column: name | |
15 | compliance_levels: | |
16 | columns: | |
17 | xep_id: XEP | |
18 | implementations: | |
19 | columns: | |
20 | xep_id: XEP | |
21 | xep_version: XEP version | |
22 | software_id: Software | |
23 | queries: | |
24 | outdated_implementations: | |
25 | title: "Implementations: needing updates" | |
26 | description: "Find implementations not on the latest version of a XEP" | |
27 | sql: |- | |
28 | select | |
29 | software.name, | |
30 | xeps.number as xep_num, | |
31 | xeps.title as xep_name, | |
32 | xep_version as implemented_xep_version, | |
33 | xeps.version as latest_xep_version | |
34 | from | |
35 | implementations | |
36 | join xeps on implementations.xep_id == xeps.id | |
37 | join software on implementations.software_id == software.id | |
38 | where | |
39 | xep_version != xeps.version | |
4
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
40 | implementation_counts: |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
41 | title: "XEPs: Ranked by implementation count" |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
42 | description: "XEPs ranked by the number of known implementations" |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
43 | sql: |- |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
44 | select |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
45 | xeps.number, |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
46 | xeps.title, |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
47 | ( |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
48 | select |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
49 | count(*) |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
50 | from |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
51 | implementations |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
52 | where |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
53 | implementations.xep_id == xeps.id |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
54 | ) as implementation_count |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
55 | from |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
56 | xeps |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
57 | order by |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
58 | implementation_count desc, |
444a46eadb74
Add canned query 'implementation_counts'
Matthew Wild <mwild1@gmail.com>
parents:
3
diff
changeset
|
59 | xeps.number asc |
0 | 60 | unusual_compliance: |
61 | title: "XEPs: Unusual compliance requirements" | |
62 | description: "Find XEPs in undesired states that are required by current compliance suites" | |
63 | sql: |- | |
64 | select | |
65 | xeps.number, | |
66 | xeps.title, | |
67 | xeps.status, | |
68 | compliance_levels.category, | |
69 | compliance_levels.level | |
70 | from | |
71 | xeps | |
72 | join compliance_levels on compliance_levels.xep_id = xeps.id | |
73 | where | |
74 | compliance_levels.level not null | |
75 | and xeps.status not in ('Active','Stable','Final'); | |
76 | deferred: | |
77 | title: "XEPs: Deferred with implementations" | |
78 | description: "Show deferred XEPs with implementation counts" | |
79 | sql: |- | |
80 | select | |
81 | xeps.number, | |
82 | xeps.title, | |
83 | xeps.last_updated, | |
84 | count(implementations.xep_id) as implementations | |
85 | from | |
86 | xeps | |
87 | join implementations on implementations.xep_id = xeps.id | |
88 | where | |
89 | status = "Deferred" | |
90 | group by | |
91 | xeps.id | |
92 | order by | |
93 | implementations desc, last_updated desc; | |
94 | expiring: | |
95 | title: "XEPs: Expiring soon" | |
96 | description: "Show XEPs soon to become deferred" | |
97 | sql: |- | |
98 | select | |
99 | xeps.number, | |
100 | xeps.title, | |
101 | xeps.last_updated, | |
102 | count(implementations.xep_id) as implementations | |
103 | from | |
104 | xeps | |
105 | join implementations on implementations.xep_id = xeps.id | |
106 | where | |
107 | status = "Experimental" | |
108 | group by | |
109 | xeps.id | |
110 | order by | |
111 | last_updated asc; | |
112 | last_call: | |
113 | title: "XEPs: Proposed XEPs under Last Call" | |
114 | description: "List open Last Calls" | |
115 | sql: |- | |
116 | select | |
117 | xeps.number, | |
118 | xeps.title, | |
119 | xeps.last_updated | |
120 | from | |
121 | xeps | |
122 | where | |
123 | status = "Proposed" | |
124 | order by | |
125 | last_updated desc; | |
126 | inbox: | |
127 | title: "XEPs: Inbox" | |
128 | description: "All submissions not accepted and assigned a XEP number" | |
129 | sql: |- | |
130 | select | |
131 | xeps.title, | |
132 | xeps.last_updated | |
133 | from | |
134 | xeps | |
135 | where | |
136 | xeps.number is null | |
137 | order by | |
138 | last_updated desc; | |
3
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
139 | needs_deferral: |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
140 | title: "XEPS: Due deferral" |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
141 | description: "XEPs that are still Experimental but not updated for over a year" |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
142 | sql: |- |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
143 | select |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
144 | xeps.number, |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
145 | xeps.title, |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
146 | xeps.last_updated |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
147 | from |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
148 | xeps |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
149 | where |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
150 | xeps.status = "Experimental" |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
151 | and xeps.last_updated <= date('now','-12 months') |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
152 | order by |
07136e6644cc
metadata: Add 'needs_deferral' canned query
Matthew Wild <mwild1@gmail.com>
parents:
0
diff
changeset
|
153 | xeps.last_updated asc; |