Skip to content
This repository was archived by the owner on Feb 7, 2024. It is now read-only.

Commit 76c80eb

Browse files
DesignburoDesignburo
committed
Changes top extension query, added skin query. Also added translation i18n values for the table names.
1 parent 02ecbee commit 76c80eb

File tree

8 files changed

+138
-123
lines changed

8 files changed

+138
-123
lines changed

‎README.md‎

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -52,19 +52,22 @@ The example above will get all information about wiki with pageId 9 and render e
5252

5353
#### action=stats
5454

55-
It takes 2 arguments :
55+
It takes 3 arguments :
5656

5757
**for** ( either extensions or skins )
5858

59-
**limit** ( return the top <limit> extensions )
59+
**limit** ( return the top <limit> extensions, default to 10 )
60+
61+
**where** ( full where clause )
6062

6163
Example :
6264
```wikitext
6365
{{#w8y:action=stats
6466
|for=extensions
65-
|limit-10}}
67+
|limit-10
68+
|where=w8y_wi_api_url not like "%fandom.com/%" }}
6669
```
67-
The example above will get the top 10 most used extensions based on installments.
70+
The example above will get the top 10 most used extensions, not being a fandom website, based on installments.
6871

6972

7073

‎i18n/en.json‎

Lines changed: 28 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,5 +8,32 @@
88
"w8y_not-a-valid-table" : "$1 is not a valid table.",
99
"w8y_missing-table-argument" : "Missing table argument",
1010
"w8y_not-a-valid-column" : "$1 is not a valid column in $2.",
11-
"w8y_missing-page-id" : "Missing pageID"
11+
"w8y_missing-page-id" : "Missing pageID",
12+
"w8y_pageTitle" : "Page Title",
13+
"w8y_wi_page_id" : "Page ID",
14+
"w8y_wi_api_url" : "API Url",
15+
"w8y_wi_last_sr_id" : "Last scrape record",
16+
"w8y_wi_is_defunct" : "Is defunct?",
17+
"w8y_sr_sr_id" : "Id",
18+
"w8y_sr_page_id" : "Page ID",
19+
"w8y_sr_api_url" : "API Url",
20+
"w8y_sr_timestamp" : "Timestamp",
21+
"w8y_sr_is_alive" : "Is Alive?",
22+
"w8y_sr_vr_id" : "Version record ID",
23+
"w8y_sr_mw_version" : "MediaWiki version",
24+
"w8y_sr_db_version" : "Database version",
25+
"w8y_sr_php_version" : "PHP version",
26+
"w8y_sr_logo" : "Logo",
27+
"w8y_sr_favicon" : "Favicon",
28+
"w8y_sr_language" : "Language",
29+
"w8y_sr_general" : "General",
30+
"w8y_sr_statistics" : "Statistics",
31+
"w8y_sd_sd_id" : "Id",
32+
"w8y_sd_name" : "Name",
33+
"w8y_sd_version" : "Version",
34+
"w8y_sd_doc_url" : "Documentation url",
35+
"w8y_ed_ed_id" : "Id",
36+
"w8y_ed_name" : "Name",
37+
"w8y_ed_version" : "Version",
38+
"w8y_ed_doc_url" : "Documentation url"
1239
}

‎includes/Scribunto/ScribuntoLuaLibrary.php‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -56,6 +56,10 @@ public function w8y( ?array $arguments ): array {
5656
if ( $limit === null ) {
5757
$limit = 10;
5858
}
59+
$where = Utils::getOptionSetting( 'where', true, $arguments );
60+
if ( $where === null ) {
61+
$where = '';
62+
}
5963
$query = new Stats();
6064
$result = $query->doQuery( $type, $limit, 'lua' );
6165
return [ $this->convertToLuaTable( $result ) ];

‎includes/TagHooks.php‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -76,15 +76,19 @@ public function w8y( Parser &$parser ): mixed {
7676
break;
7777
case "stats":
7878
$type = Utils::getOptionSetting( 'for' );
79+
$where = Utils::getOptionSetting( 'where' );
7980
if ( $limit === null ) {
8081
$limit = 10;
8182
}
83+
if ( $where === null ) {
84+
$where = '';
85+
}
8286
if ( $type !== null ) {
8387
$query = new Stats();
8488
if ( $format === null ) {
8589
$format = 'table';
8690
}
87-
$result = $query->doQuery( $type, $limit, $format );
91+
$result = $query->doQuery( $type, $where, $limit, $format );
8892
ResponseHandler::printDebugMessage( $result,
8993
"sql result" );
9094
}

‎includes/data/Structure.php‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,11 +29,13 @@ class Structure {
2929
public const SR_ID = 'w8y_sr_sr_id';
3030
public const SCRAPE_VR_ID = 'w8y_sr_vr_id';
3131
public const EXTENSION_ID = 'w8y_ed_ed_id';
32+
public const EXTENSION_NAME = 'w8y_ed_name';
3233
public const EXTENSION_LINK_VID = 'w8y_el_vr_id';
3334
public const EXTENSION_LINK_ID = 'w8y_el_ed_id';
3435
public const SKIN_LINK_VID = 'w8y_sl_vr_id';
3536
public const SKIN_LINK_ID = 'w8y_sl_sd_id';
3637
public const SKIN_ID = 'w8y_sd_sd_id';
38+
public const SKIN_NAME = 'w8y_sd_name';
3739
public const SCRAPE_MEDIAWIKI_VERSION = 'w8y_sr_mw_version';
3840

3941
/**
@@ -65,6 +67,20 @@ public function tableExists( string $tableName ): bool {
6567
return array_key_exists( $tableName, $this->dbStructure );
6668
}
6769

70+
/**
71+
* @param string $key
72+
* Find an i18n key, else return original
73+
*
74+
* @return string
75+
*/
76+
public static function w8yMessage( string $key ): string {
77+
$msg = wfMessage( $key )->parse();
78+
if ( str_starts_with( $msg, '⧼' ) ) {
79+
return $key;
80+
}
81+
return $msg;
82+
}
83+
6884
/**
6985
* @param string $tableName
7086
*

‎includes/data/Utils.php‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -167,11 +167,11 @@ public static function renderTable( array $data, string $title = '',
167167
foreach ( $tableData as $k => $v ) {
168168
if ( is_array( $v ) ) {
169169
foreach ( $v as $extraKey => $extraValue ) {
170-
$ret .= '| ' . $extraKey . ' || ' . $extraValue . PHP_EOL;
170+
$ret .= '| ' . Structure::w8yMessage( $extraKey ) . ' || ' . $extraValue . PHP_EOL;
171171
$ret .= '|-' . PHP_EOL;
172172
}
173173
} else {
174-
$ret .= '| ' . $k . ' || ' . $v . PHP_EOL;
174+
$ret .= '| ' . Structure::w8yMessage( $k ) . ' || ' . $v . PHP_EOL;
175175
$ret .= '|-' . PHP_EOL;
176176
}
177177
}

‎includes/data/query/Stats.php‎

Lines changed: 68 additions & 109 deletions
Original file line numberDiff line numberDiff line change
@@ -29,24 +29,46 @@ public function __construct() {
2929
/**
3030
* @param int $limit
3131
* @param DBConnRef $dbr
32+
* @param string $where
3233
*
3334
* @return array
3435
*/
35-
private function getTopSkins( int $limit, DBConnRef $dbr ): array {
36-
$select = [ '*',
37-
'count' => 'count(*)' ];
38-
$from = Structure::DBTABLE_SKINS;
39-
$res = $dbr->newSelectQueryBuilder()->select( $select )->from( $from )->groupBy( 'w8y_sk_name' )
40-
->orderBy( 'count',
41-
'DESC' )->limit( $limit )->caller( __METHOD__ )->fetchResultSet();
36+
private function getTopSkins( int $limit, DBConnRef $dbr, string $where = '' ): array {
37+
$select = [ Structure::SKIN_NAME, 'count' => 'count(*)' ];
38+
$from = Structure::DBTABLE_WIKIS;
39+
40+
if ( $where !== '' ) {
41+
$res = $dbr->newSelectQueryBuilder()->
42+
select( $select )->
43+
from( $from )->
44+
leftJoin( Structure::DBTABLE_SCRAPE, null, Structure::DBTABLE_WIKIS . '.' . Structure::WIKI_LAST_SR_RCRD . '=' . Structure::DBTABLE_SCRAPE . '.' . Structure::SR_ID )->
45+
leftJoin( Structure::DBTABLE_SKINS_LINK, null, Structure::DBTABLE_SCRAPE . '.' . Structure::SCRAPE_VR_ID . '=' . Structure::DBTABLE_SKINS_LINK . '.' . Structure::SKIN_LINK_VID )->
46+
leftJoin( Structure::DBTABLE_SKINS, null, Structure::DBTABLE_SKINS_LINK . '.' . Structure::SKIN_LINK_ID . '=' . Structure::DBTABLE_SKINS . '.' . Structure::SKIN_ID )->
47+
where( $where )->
48+
groupBy( Structure::SKIN_NAME )->
49+
orderBy( 'count', 'DESC' )->
50+
limit( $limit )->
51+
caller( __METHOD__ )->
52+
fetchResultSet();
53+
} else {
54+
$res = $dbr->newSelectQueryBuilder()->
55+
select( $select )->
56+
from( $from )->
57+
leftJoin( Structure::DBTABLE_SCRAPE, null, Structure::DBTABLE_WIKIS . '.' . Structure::WIKI_LAST_SR_RCRD . '=' . Structure::DBTABLE_SCRAPE . '.' . Structure::SR_ID )->
58+
leftJoin( Structure::DBTABLE_SKINS_LINK, null, Structure::DBTABLE_SCRAPE . '.' . Structure::SCRAPE_VR_ID . '=' . Structure::DBTABLE_SKINS_LINK . '.' . Structure::SKIN_LINK_VID )->
59+
leftJoin( Structure::DBTABLE_SKINS, null, Structure::DBTABLE_SKINS_LINK . '.' . Structure::SKIN_LINK_ID . '=' . Structure::DBTABLE_SKINS . '.' . Structure::SKIN_ID )->
60+
groupBy( Structure::SKIN_NAME )->
61+
orderBy( 'count', 'DESC' )->
62+
limit( $limit )->
63+
caller( __METHOD__ )->
64+
fetchResultSet();
65+
}
4266
$ret = [];
4367
$t = 0;
4468
if ( $res->numRows() > 0 ) {
4569
while ( $row = $res->fetchRow() ) {
4670
$ret[$t]['Count'] = $row['count'];
47-
foreach ( $this->structure->returnTableColumns( Structure::DBTABLE_SKINS ) as $tName ) {
48-
$ret[$t][$tName] = $row[$tName];
49-
}
71+
$ret[$t]['Name'] = $row[Structure::SKIN_NAME];
5072
$t++;
5173
}
5274
}
@@ -57,124 +79,69 @@ private function getTopSkins( int $limit, DBConnRef $dbr ): array {
5779
/**
5880
* @param int $limit
5981
* @param DBConnRef $dbr
82+
* @param string $where
6083
*
6184
* @return array
6285
*/
63-
private function getTopExtensions( int $limit, DBConnRef $dbr ): array {
64-
/*
65-
* SELECT
66-
w8y_wikis.w8y_wi_page_id,
67-
w8y_extensions.*,
68-
COUNT(
69-
DISTINCT w8y_extensions.w8y_ex_name
70-
) AS count
71-
FROM
72-
`w8y_wikis`
73-
INNER JOIN w8y_scrape_records ON w8y_wi_last_sr_id = w8y_scrape_records.w8y_sr_sr_id
74-
INNER JOIN w8y_extensions ON w8y_scrape_records.w8y_sr_vr_id = w8y_extensions.w8y_ex_vr_id
75-
GROUP BY
76-
w8y_extensions.w8y_ex_name
77-
ORDER BY
78-
COUNT
79-
DESC
80-
;
81-
82-
SELECT w8y_extension_data.w8y_ed_name, COUNT(w8y_extension_data.w8y_ed_ed_id) as count
83-
FROM w8y_wikis
84-
JOIN w8y_scrape_records ON w8y_wikis.w8y_wi_last_sr_id = w8y_scrape_records.w8y_sr_sr_id
85-
JOIN w8y_extension_links ON w8y_scrape_records.w8y_sr_vr_id = w8y_extension_links.w8y_el_vr_id
86-
JOIN w8y_extension_data ON w8y_extension_links.w8y_el_ed_id = w8y_extension_data.w8y_ed_ed_id
87-
WHERE w8y_wikis.w8y_wi_is_defunct = 0
88-
GROUP BY w8y_ed_name
89-
ORDER BY count DESC;
90-
*/
91-
92-
93-
$select = [ Structure::DBTABLE_EXTENSIONS . '.*',
94-
'count' => 'count(' . Structure::DBTABLE_EXTENSIONS . '.' . Structure::EXTENSION_ID . ')' ];
86+
private function getTopExtensions( int $limit, DBConnRef $dbr, string $where = '' ): array {
87+
$select = [ Structure::EXTENSION_NAME, 'count' => 'count(*)' ];
9588
$from = Structure::DBTABLE_WIKIS;
96-
$res = $dbr->newSelectQueryBuilder()->select( $select )->from( $from )->
97-
leftJoin( Structure::DBTABLE_SCRAPE,
98-
null,
99-
Structure::DBTABLE_WIKIS . '.' . Structure::WIKI_LAST_SR_RCRD . '=' . Structure::DBTABLE_SCRAPE . '.' . Structure::SR_ID )->
100-
leftJoin( Structure::DBTABLE_EXTENSIONS_LINK,
101-
null,
102-
Structure::DBTABLE_SCRAPE . '.' . Structure::SCRAPE_VR_ID . '=' . Structure::DBTABLE_EXTENSIONS_LINK . '.' . Structure::EXTENSION_LINK_VID )->
103-
leftJoin( Structure::DBTABLE_EXTENSIONS, null,
104-
Structure::DBTABLE_EXTENSIONS_LINK . '.' . Structure::EXTENSION_LINK_ID . '=' . Structure::DBTABLE_EXTENSIONS . '.' . Structure::EXTENSION_ID )->
105-
groupBy( Structure::EXTENSION_ID )
106-
->orderBy( 'count',
107-
'DESC' )->limit( $limit )->caller( __METHOD__ )->fetchResultSet();
89+
90+
if ( $where !== '' ) {
91+
$res = $dbr->newSelectQueryBuilder()->
92+
select( $select )->
93+
from( $from )->
94+
leftJoin( Structure::DBTABLE_SCRAPE, null, Structure::DBTABLE_WIKIS . '.' . Structure::WIKI_LAST_SR_RCRD . '=' . Structure::DBTABLE_SCRAPE . '.' . Structure::SR_ID )->
95+
leftJoin( Structure::DBTABLE_EXTENSIONS_LINK, null, Structure::DBTABLE_SCRAPE . '.' . Structure::SCRAPE_VR_ID . '=' . Structure::DBTABLE_EXTENSIONS_LINK . '.' . Structure::EXTENSION_LINK_VID )->
96+
leftJoin( Structure::DBTABLE_EXTENSIONS, null, Structure::DBTABLE_EXTENSIONS_LINK . '.' . Structure::EXTENSION_LINK_ID . '=' . Structure::DBTABLE_EXTENSIONS . '.' . Structure::EXTENSION_ID )->
97+
where( $where )->
98+
groupBy( Structure::EXTENSION_NAME )->
99+
orderBy( 'count', 'DESC' )->
100+
limit( $limit )->
101+
caller( __METHOD__ )->
102+
fetchResultSet();
103+
} else {
104+
$res = $dbr->newSelectQueryBuilder()->
105+
select( $select )->
106+
from( $from )->
107+
leftJoin( Structure::DBTABLE_SCRAPE, null, Structure::DBTABLE_WIKIS . '.' . Structure::WIKI_LAST_SR_RCRD . '=' . Structure::DBTABLE_SCRAPE . '.' . Structure::SR_ID )->
108+
leftJoin( Structure::DBTABLE_EXTENSIONS_LINK, null, Structure::DBTABLE_SCRAPE . '.' . Structure::SCRAPE_VR_ID . '=' . Structure::DBTABLE_EXTENSIONS_LINK . '.' . Structure::EXTENSION_LINK_VID )->
109+
leftJoin( Structure::DBTABLE_EXTENSIONS, null, Structure::DBTABLE_EXTENSIONS_LINK . '.' . Structure::EXTENSION_LINK_ID . '=' . Structure::DBTABLE_EXTENSIONS . '.' . Structure::EXTENSION_ID )->
110+
groupBy( Structure::EXTENSION_NAME )->
111+
orderBy( 'count', 'DESC' )->
112+
limit( $limit )->
113+
caller( __METHOD__ )->
114+
fetchResultSet();
115+
}
108116
$ret = [];
109117
$t = 0;
110118
if ( $res->numRows() > 0 ) {
111119
while ( $row = $res->fetchRow() ) {
112120
$ret[$t]['Count'] = $row['count'];
113-
foreach ( $this->structure->returnTableColumns( Structure::DBTABLE_EXTENSIONS ) as $tName ) {
114-
$ret[$t][$tName] = $row[$tName];
115-
}
121+
$ret[$t]['Name'] = $row[Structure::EXTENSION_NAME];
116122
$t++;
117123
}
118124
}
119125

120126
return $ret;
121127
}
122128

123-
/**
124-
* @param int $limit
125-
* @param DBConnRef $dbr
126-
*
127-
* @return array
128-
*/
129-
private function getTopExtensions2( int $limit, DBConnRef $dbr ): array {
130-
131-
$select = [ Structure::DBTABLE_EXTENSIONS_LINK . '.' . Structure::EXTENSION_LINK_ID ];
132-
$from = Structure::DBTABLE_WIKIS;
133-
$res = $dbr->newSelectQueryBuilder()->select( $select )->from( $from )->
134-
leftJoin( Structure::DBTABLE_SCRAPE,
135-
null,
136-
Structure::DBTABLE_WIKIS . '.' . Structure::WIKI_LAST_SR_RCRD . '=' . Structure::DBTABLE_SCRAPE . '.' . Structure::SR_ID )->
137-
leftJoin( Structure::DBTABLE_EXTENSIONS_LINK,
138-
null,
139-
Structure::DBTABLE_SCRAPE . '.' . Structure::SCRAPE_VR_ID . '=' . Structure::DBTABLE_EXTENSIONS_LINK . '.' . Structure::EXTENSION_LINK_VID )->
140-
caller( __METHOD__ )->fetchResultSet();
141-
$ids = [];
142-
if ( $res->numRows() > 0 ) {
143-
while ( $row = $res->fetchRow() ) {
144-
$ids[] = $row[ Structure::EXTENSION_LINK_ID ];
145-
}
146-
}
147-
//var_dump ( $ids );
148-
$nRes = [];
149-
$counter = array_count_values( $ids );
150-
$t=0;
151-
foreach ( $counter as $k => $v ) {
152-
$nRes[$t]['k'] = $k;
153-
$nRes[$t]['v'] = $v;
154-
$t++;
155-
}
156-
return $nRes;
157-
}
158-
159129
/**
160130
* @param string $action
131+
* @param string $where
161132
* @param int $limit
162133
* @param string $export
163134
*
164135
* @return mixed
165136
*/
166-
public function doQuery( string $action, int $limit = 10, string $export = "table" ): mixed {
137+
public function doQuery( string $action, string $where, int $limit = 10, string $export = "table" ): mixed {
167138
$lb = MediaWikiServices::getInstance()->getDBLoadBalancer();
168139
$dbr = $lb->getConnectionRef( DB_REPLICA );
169140
$result = [];
170141
switch ( $action ) {
171142
case "extensions":
172143
$result = $this->getTopExtensions( $limit,
173-
$dbr );
174-
break;
175-
case "extensions2":
176-
$result = $this->getTopExtensions2( $limit,
177-
$dbr );
144+
$dbr, $where );
178145
break;
179146
case "skins":
180147
$result = $this->getTopSkins( $limit,
@@ -188,21 +155,13 @@ public function doQuery( string $action, int $limit = 10, string $export = "tabl
188155
if ( $action === "extensions" ) {
189156
return Utils::renderTable( $result,
190157
'Top ' . $limit . ' used extensions',
191-
array_merge( $tables,
192-
$this->structure->returnTableColumns( Structure::DBTABLE_EXTENSIONS ) ),
193-
true );
194-
}
195-
if ( $action === "extensions2" ) {
196-
return Utils::renderTable( $result,
197-
'Top ' . $limit . ' used extensions',
198-
[ 'id', 'hits' ],
158+
array_merge( $tables, [ Structure::w8yMessage( Structure::EXTENSION_NAME ) ] ),
199159
true );
200160
}
201161
if ( $action === "skins" ) {
202162
return Utils::renderTable( $result,
203163
'Top ' . $limit . ' used skins',
204-
array_merge( $tables,
205-
$this->structure->returnTableColumns( Structure::DBTABLE_SKINS ) ),
164+
array_merge( $tables, [ Structure::w8yMessage( Structure::SKIN_NAME ) ] ),
206165
true );
207166
}
208167
break;

0 commit comments

Comments
 (0)