/
unusedtemplatesfiltered.rs
218 lines (199 loc) · 5.91 KB
/
unusedtemplatesfiltered.rs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
/*
Copyright 2011, 2018 bjweeks, MZMcBride
Copyright 2022 Kunal Mehta <legoktm@debian.org>
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
use anyhow::Result;
use dbreps2::{str_vec, y_m_d, Frequency, Report};
use log::debug;
use mysql_async::prelude::*;
use mysql_async::Conn;
use std::collections::HashSet;
/// Returned by the first main query
struct FirstRow {
page_id: u64,
page_title: String,
}
/// Returned by the second subquery
struct SecondRow {
actor_id: u64,
rev_timestamp: String,
}
pub struct Row {
template: String,
first_edit: String,
latest_edit: String,
unique_authors: usize,
revisions: usize,
}
pub struct UnusedTemplatesFiltered {}
const SUBQUERY: &str = r#"
/* unusedtemplatesfiltered.rs */
SELECT
actor_id,
rev_timestamp
FROM
revision_userindex
JOIN actor ON actor_id = rev_actor
WHERE
rev_page = ?;
"#;
impl Report<Row> for UnusedTemplatesFiltered {
fn title(&self) -> &'static str {
"Unused templates (filtered)"
}
fn frequency(&self) -> Frequency {
Frequency::Daily
}
fn rows_per_page(&self) -> Option<usize> {
Some(4000)
}
fn static_row_numbers(&self) -> bool {
true
}
fn query(&self) -> &'static str {
r#"
/* unusedtemplatesfiltered.rs SLOW_OK */
SELECT
page_id,
page_title
FROM
page
LEFT JOIN linktarget ON page_namespace = lt_namespace
AND page_title = lt_title
LEFT JOIN templatelinks ON tl_target_id=lt_id
WHERE
page_namespace = 10
AND page_is_redirect = 0
AND tl_target_id IS NULL
AND page_title NOT LIKE "Adminstats/%"
AND page_title NOT LIKE "AfC_%"
AND page_title NOT LIKE "Cite_doi/%"
AND page_title NOT LIKE "Cite_pmid/%"
AND page_title NOT LIKE "Did_you_know_nominations/%"
AND page_title NOT LIKE "Editnotices/%"
AND page_title NOT LIKE "PBB/%"
AND page_title NOT LIKE "POTD_caption/%"
AND page_title NOT LIKE "POTD_credit/%"
AND page_title NOT LIKE "POTD_protected/%"
AND page_title NOT LIKE "TemplateStyles_sandbox/%"
AND page_title NOT LIKE "TFA_title/%"
AND page_title NOT LIKE "User_%"
AND page_title NOT LIKE "%/testcases"
AND page_title NOT LIKE "%/sandbox"
AND page_title NOT LIKE "%/rater-data.js"
AND page_title NOT LIKE "%-stub"
AND page_title NOT IN (
SELECT
page_title
FROM
page
JOIN categorylinks ON page_id = cl_from
WHERE
cl_to IN (
'Wikipedia_substituted_templates',
'Wikipedia_transclusionless_templates',
'Deprecated_templates_kept_for_historical_reasons',
'Inactive_project_pages',
'Parameter_shared_content_templates',
'Computer_language_user_templates',
'Language_user_templates',
'Template_test_cases',
'Template_sandboxes',
'Level-zero_userbox_templates',
'Templates_for_deletion'
)
AND page_namespace = 10
)
ORDER BY
page_title ASC;
"#
}
async fn run_query(&self, conn: &mut Conn) -> Result<Vec<Row>> {
let first_rows = conn
.query_map(self.query(), |(page_id, page_title)| FirstRow {
page_id,
page_title,
})
.await?;
let mut rows = vec![];
let mut stub_rows = vec![];
for row in first_rows {
debug!("Running subquery for {}", &row.page_id);
let second_rows = conn
.exec_map(
SUBQUERY,
(row.page_id,),
|(actor_id, rev_timestamp)| SecondRow {
actor_id,
rev_timestamp,
},
)
.await?;
let mut revisions = 0;
let mut authors = HashSet::new();
let mut timestamps = vec![];
for row in second_rows {
revisions += 1;
authors.insert(row.actor_id);
timestamps.push(row.rev_timestamp);
}
timestamps.sort();
let row = Row {
template: row.page_title,
first_edit: timestamps[0].clone(),
latest_edit: timestamps[timestamps.len() - 1].clone(),
unique_authors: authors.len(),
revisions,
};
// Split out stub templates
if row.template.contains("stub") {
stub_rows.push(row);
} else {
rows.push(row);
}
}
// Sort by template name, then merge in the stub templates
rows.sort_by_key(|row| row.template.clone());
stub_rows.sort_by_key(|row| row.template.clone());
rows.extend(stub_rows);
Ok(rows)
}
fn intro(&self) -> &'static str {
"Unused templates (filtered)"
}
fn headings(&self) -> Vec<&'static str> {
vec![
"Template",
"First edit",
"Latest edit",
"Unique authors",
"Revisions",
]
}
fn format_row(&self, row: &Row) -> Vec<String> {
str_vec![
format!(
"[[:Template:{}|{}]]",
row.template,
row.template.replace('_', " ")
),
y_m_d(&row.first_edit),
y_m_d(&row.latest_edit),
row.unique_authors,
row.revisions
]
}
fn code(&self) -> &'static str {
include_str!("unusedtemplatesfiltered.rs")
}
}