Template talk:Database report

This template was considered for deletion on 25 December 2021. The result of the discussion was "userfy".

Not functional at the moment

SD0001, I assume that this edit has something to do with this discussion, which just confirms in my mind that trying to run bots on the "toolforge" isn't worth my bother and trouble when it's easy just to run them on my own computer. Any update on the status of this? I ask because Category:Non-talk pages with an edit request template pointed me to Wikipedia:Database reports/Indefinitely semi-protected articles where I saw that Escape the Fate's protection summary "Excessive vandalism: A long history of vandalism, enough is enough; use Error: Protected edit requests can only be made on the talk page. if needed" confused your bot. So I changed the protection to supersede that protection edit summary. Noting that report was last updated on 23 June 2021 – seems this is still not functional at the moment even though this is a standard query and not some new rogue-editor-created query. It only took Quarry 96 seconds to produce a new report but I'm not sure if I can get it in the right format to cut&paste it into Wikipedia:Database reports/Indefinitely semi-protected articles. Maybe you can bypass the "toolforge" and just make Quarry queries directly? wbm1058 (talk) 19:07, 11 August 2021 (UTC)[reply]

@Wbm1058 The databases are actually on toolforge, so can't be bypassed. Quarry makes its queries to toolforge. But as you can see from that discussion, bots aren't allowed to take queries from users and run them. I can probably get an exemption from that rule for this specific bot task, but the tone and manner in which the matter was raised at BOTN caused me to lose all interest in this.

As for that protection summary rendering error, the code seems to suggest the issue was fixed later.

I am novice to the idea of running bots on own computer (How can you keep your computer running 24x7? Even then isn't it a waste of internet and electricity bills? Toolforge provides free hosting with fast access to wikimedia APIs 🙂) – SD0001 (talk) 13:22, 13 August 2021 (UTC)[reply]

@SD0001: If I'm reading GitHub correctly, it looks like that code was added at the bottom here in the "better handle summary formatting in reports" version committed on Apr 30... but the last version of the report was posted in June. So either it ran using an older version of the code or somehow that bug's still there.

When Intel came out their first solid-state drives in ~2010 I bought a 40GB SDD and built a new computer with Windows 7 installed on that as my main drive (and a second, much larger, conventional drive for media files). I started running bots on that in 2012. When my 40GB SDD kept getting full, I upgraded to 240GB in 2015. My bot will soon celebrate its ninth birthday running on that machine! My Internet bill is $60/month. That's a fixed amount for unlimited usage. My Internet is capped at ~25 Mbps and I'm "grandfathered" into that deal as a "legacy" customer, while they offer new customers 200 Mbps. But I don't really need more speed for my bots. I've preordered Starry Internet which I hope arrives in my neighborhood soon and makes me a "Starry Voyager" with 200 Mbps for just $25/month. I was on Clearwire until they shut down, just because. I don't like poorly-regulated monopolies. The biggest power users in my rig are my laser printer and my monitor, both of which I turn off when not in use. I can't image my machine uses much power – I'm not mining bitcoin unless someone's secretly using my machine behind my back. I have a Windows 10 laptop on which I've run my bots as a backup. Windows 10 still supports Windows Task Scheduler but Windows 7 supports it better. Windows 7 also supports my beloved Windows Media Center which is why I view later versions of Windows as downgrades with fewer features. If I don't have anything in my browser tabs running constantly, Windows goes into sleep mode which saves power. Task scheduler wakes it up every 15 minutes to run RMCD bot then it goes back to sleep for a bit. This is a very reliable setup. I flew to Hawaii to bike around the Big Island and when I arrived back home a couple weeks later my bots were still running. I'm not sure the "toolforge" has the same level of reliability. More on my setup at User talk:wbm1058/Creating bots. I'm not gonna ask the WMF for help paying my Internet bills until they at least refill my coffee cup ;) – wbm1058 (talk) 15:00, 13 August 2021 (UTC)[reply]

Nomination for deletion of Template:Database report

Template:Database report has been nominated for deletion. You are invited to comment on the discussion at the entry on the Templates for discussion page. 65.92.246.142 (talk) 03:14, 19 December 2021 (UTC)[reply]

Queries with pipes

@SD0001: is there any way to support these? I'm trying to convert quarry:query/68246 to a bot-updated report. * Pppery * _{it has begun...} 00:47, 27 October 2022 (UTC)[reply]

@Pppery Good point. Just made a change to the bot - if we write {{!}} within the query, the bot will replace it with a pipe. Hopefully there won't be case where we have a query that needs to literally contain {{!}}. Thanks for bringing it up. – SD0001 (talk) 17:55, 27 October 2022 (UTC)[reply]

@SD0001: That seems to have worked, but I ran into another bug - can you explain why the bot did Special:Diff/1118560721? The documentation implies multiple reports on one page should be allowed unless pagination (which I don't use here) is used. * Pppery * _{it has begun...} 18:09, 27 October 2022 (UTC)[reply]

@Pppery: There should be a {{Database report end}} call. Otherwise the bot doesn't know where the existing table (from the previous update) ends. So it defaults to considering the rest of the page as the existing table and replaces it all. – SD0001 (talk) 18:46, 27 October 2022 (UTC)[reply]

Oops, I guess I thought {{database report/footer}} counted as the end. Apparently not. * Pppery * _{it has begun...} 18:50, 27 October 2022 (UTC)[reply]

problem with using row_template param

@SD0001: while using the row_template parameter of this report, it generates output like {{TemplateName|1=20221031,|2=654,|3=1578}}. The issue is with the extra commas for the first two values of the template. -MPGuy2824 (talk) 10:19, 1 November 2022 (UTC)[reply]

@MPGuy2824

Fixed. It worked. – SD0001 (talk) 13:08, 1 November 2022 (UTC)[reply]

Feature requests

Json output
Outputting to a different page (maybe restricted to a subpage of the current page). -MPGuy2824 (talk) 08:21, 2 November 2022 (UTC)[reply]
Add a timestamp somewhere so that viewers know how dated the data is. -MPGuy2824 (talk) 08:54, 2 November 2022 (UTC)[reply]
Will look into #3 which sounds like an obvious improvement. Regarding #1 and #2, could you share what are the use-cases? JSON output might already be sort of possible by using skip_table=true and a substed row_template. Is it for feeding the data to modules for graph generation or something? – SD0001 (talk) 17:00, 2 November 2022 (UTC)[reply]
Yes, it would be for graph generation. A substed row_template would definitely work to get json, but there is the problem of the {{Database report}} template itself being on the page. So, i guess #1 is not necessary, but #2 would still be useful. -MPGuy2824 (talk) 01:41, 3 November 2022 (UTC)[reply]
@MPGuy2824: timestamp is now included in the footer. An output_page parameter can also now be used to output to a subpage instead (though this is not covered in documentation). Let me know if this is actually useful.
As for using row_template to get JSON output, I think a subtle issue is that there's no way to get rid of the extra comma in the last row of the array, so overall it won't be valid JSON! I'm curious as to what data is available in replica dbs which can actually be graphed? – SD0001 (talk) 14:15, 20 November 2022 (UTC)[reply]
Thanks for adding the timestamp. Yes, i agree JSON output would be an issue with just using row_template. My output_page request was kinda linked to that, so its good that you haven't added it to the documentation page.

Your graph question: NPP coords have been hoping to graph articles and redirects reviewed daily. I'm working on a toolforge tool for this, so no need for you to think about this request any more, unless someone else also requests it. -MPGuy2824 (talk) 04:34, 21 November 2022 (UTC)[reply]

Update now button

The documentation says Updates can also be manually triggered using the update now button – but where is the "update now" button? – Joe (talk) 10:25, 23 January 2023 (UTC)[reply]

@Joe Roe There's an "Update the table now" button at the top right of the template. It is marked with extendedconfirmed-show class to discourage spammy use. But it seems that may have been hiding it from admins as well who technically are not extended confirmed. I added the sysop-show class as well, so you should be able to see it now. – SD0001 (talk) 16:52, 23 January 2023 (UTC)[reply]

Report not being updated

Hi, I'm not sure why, but Wikipedia:New_pages_patrol/Reports#AfDs_created_by_NPP_reviewers_in_the_past_week hasn't been updated since 5th March, although it is set for a daily update. I've checked that the query is working properly. -MPGuy2824 (talk) 03:08, 9 March 2023 (UTC)[reply]

The {{Database report}} was not getting detected due to some malformed syntax earlier in the page (fixed here). The bot's template parser isn't as smart as MediaWiki's in presence of broken syntax. Maybe using parsoid would help with this. – SD0001 (talk) 15:07, 9 March 2023 (UTC)[reply]

Thanks for fixing this. I'd guess that the issue would occur again when the other report is updated. If it does, i'll update this thread and remove the excerpt column from the report. -MPGuy2824 (talk) 03:22, 10 March 2023 (UTC)[reply]

Yup, had to remove the excerpt column. If/when you do something to fix this, please ping me. -MPGuy2824 (talk) 04:40, 11 March 2023 (UTC)[reply]

Bug with double namespace prefixes

If a query result includes a namespace name, and you try to wikilink it to a different namespace, it produces the wrong page. For a simplified example, {{database report|sql=select "Portal:ABC"|wikilinks=1:118}} should link to Draft:Portal:ABC, but instead links to Portal:ABC. * Pppery * _{it has begun...} 04:38, 6 May 2023 (UTC)[reply]

Fixed, belatedly. – SD0001 (talk) 17:50, 27 November 2023 (UTC)[reply]

Bot keeps deleting my new query

Please see here and here. Looks like a bug. Or maybe I have a syntax error somewhere? Replag is currently at zero. Anything we can do to fix this? Thanks. –Novem Linguae (talk) 10:33, 20 June 2023 (UTC)[reply]

A {{database report end}} template was missing. Fixed now. – SD0001 (talk) 15:32, 20 June 2023 (UTC)[reply]

If this occurs from now on, the bot edit summary would include overwriting rest of page as end template not found. – SD0001 (talk) 17:06, 27 November 2023 (UTC)[reply]

Nowiki tags for article snippets

Might need some nowiki tags for article snippets. Diff. –Novem Linguae (talk) 05:55, 23 July 2023 (UTC)[reply]

Not using nowiki tags for article snippets is intentional as they would make the snippets unformatted and dull. Instead, snippets are processed to exclude non-inline templates (like infoboxes and sidebars), images, and other stuff. But sometimes these things do pop in when the wikitext formatting is unexpected like here. If the sidebar template had been on a new line it would have been removed fine. – SD0001 (talk) 09:21, 25 July 2023 (UTC)[reply]

Use Template:Static row numbers

Please update the module to use

{{static row numbers}}
{| class="wikitable sortable static-row-numbers static-row-header-text"

as the table header. Gonnym (talk) 15:36, 21 August 2023 (UTC)[reply]

This may not be desirable in some cases (such as when trying to use less space, as the default skin nowadays has limited horizontal area). So probably better to make it opt-in. – SD0001 (talk) 06:42, 22 August 2023 (UTC)[reply]

Maybe take a class parameter like table_style. —Cryptic 20:45, 22 November 2023 (UTC)[reply]

Done. This can now be done by specifying header_template = static row numbers along with table_class = wikitable sortable static-row-numbers static-row-header-text. Updated docs. – SD0001 (talk) 14:45, 27 November 2023 (UTC)[reply]

Blank table_style to suppress default "overflow-wrap: anywhere" doesn't work

I tried a couple variants of this - first just blanking it as documented (edit, result); then a space (edit, result) and a space and html comment (edit, result). Non-logically-blank values do work as expected (edit, result). —Cryptic 20:57, 22 November 2023 (UTC)[reply]

It seems that bit of documentation is wrong. Lots of people have already copied over the sample usage (which does have a blank table_style), so fixing this now would cause all those tables to lose overflow-wrap styling.

Besides, MediaWiki doesn't distinguish not having a param vs leaving param blank, so the bot's template parser should probably follow the same. I'll just update the docs to suggest using overflow-wrap: normal to suppress the default. – SD0001 (talk) 13:10, 23 November 2023 (UTC)[reply]

Suppress sigs in comments columns

~~~~ in columns designated in the comments parameter get signed by the bot, and really shouldn't. Example; corresponding log entry for all three rows. I didn't try ~~~ or ~~~~~, but I can't imagine that they'd be handled any better.

There's an argument to suppress external links in these columns, too, since they aren't shown in either log entries or edit summaries (which is what comments is documented for). There's examples of both the bracketed and non-bracketed (http://example.example) formats in that query. Can't say I feel strongly either way, though. —Cryptic 21:08, 22 November 2023 (UTC)[reply]

A workaround: [1]. Though that also shows it's letting through <nowiki> and </nowiki>, and so probably other html-style tags; those worry me more than the external links. —Cryptic 22:15, 22 November 2023 (UTC)[reply]

How often do these tags get used in edit/log comments though? I would think it's not much of a concern. Using regex to identify html tags is a bit messy, so probably not worth it unless there are actually some reports getting mangled due to leaked markup. – SD0001 (talk) 14:54, 23 November 2023 (UTC)[reply]

Extremely, if you include html comments - besides ST47ProxyBot, a lot of the standard block messages in the dropdown have them. Plenty often in the create log, too, like so; and the way the article comment gets truncated there, it's quite likely they'll never get closed, which at the very least will frustrate the lint-fixing folks. Not to mention a bunch of irritating position:absolute divs in the early delete logs when the default was still to include the start of the text of the deleted page.

Why not just replace all left-angle brackets with entities, <? They're not valid in page titles anyway, so it shouldn't break any of the markup that comments columns are meant to let through, I don't think. —Cryptic 15:27, 23 November 2023 (UTC)[reply]

Ah yes, I forgot about the block log's use of HTML comments. Replacing < with < also makes a lot of sense, thanks. Done. – SD0001 (talk) 17:40, 23 November 2023 (UTC)[reply]

Updated the bot to nowiki signature markup in comments. Will review the other issues when I have a moment. – SD0001 (talk) 10:23, 23 November 2023 (UTC)[reply]

As for external links, not sure if it's worth wrapping them in nowikis. MW doesn't render them, yes, but if someone has included them anyway in log entries, it seems cleaner to just show it rendered than not. – SD0001 (talk) 14:51, 23 November 2023 (UTC)[reply]

December 2023 updates

Some significant technical updates have been made to the bot's handling of scheduled reports, for improving its scalability and stability. The bot should now be able to handle potentially thousands of scheduled reports without missing a beat! A few user-facing improvements as a bonus:

Earlier all scheduled reports on the same page were required to have the same update interval. If they were different, the ones with a higher value used to get skipped always. This is no longer the case.
Now, the update interval can be set to any number of days. Earlier, they were restricted from 1 to 30, and a page with interval more than 30 used to get updated every 30 days.
Automatic run on newly adding {{database report}} to a page is now disabled, as it's easy to trigger an update explicitly by clicking on "Update the table now" button. This can be brought back if people find it useful.

I have the following future improvements on my mind. Let me know if there's any you would like to see, so I can prioritise accordingly.

A parameter to suppress display of last update timestamp and query runtime in the footer. The presence of these fields causes the bot to log an edit even if the table output is unchanged.
Automatically disabling update attempts on queries which have errored out or timed out more than 3 times consecutively, to save server resources.

– SD0001 (talk) 16:30, 10 December 2023 (UTC)[reply]

I looked a couple of times for a way to put an "edit" link after the page wikilink. Is that something I have to do in SQL, or can it be done with this template? – Jonesey95 (talk) 19:02, 12 December 2023 (UTC)[reply]

Could be done using SQL, or by using a custom row_template. – SD0001 (talk) 19:47, 12 December 2023 (UTC)[reply]

OK thanks, I'll play with it. – Jonesey95 (talk) 20:08, 12 December 2023 (UTC)[reply]

March 2024 updates

If the SQL statement contains syntax that looks like wikimarkup, it needed to be escaped in various ugly ways to avoid problems. Now, you can just put the whole SQL within <nowiki>...</nowiki> tags. The bot strips the nowikis before sending the query to the database.
If a report is translcuded from another page, it wasn't possible so far to meaningfully use the "Update now" button as that only searched the wikitext of the current page for reports to update. This has been made to work now. (If there are no reports on the current page, the webservice now checks translcuded pages containing reports.)
When using pagination, a limit is now automatically applied to the query, equal to the pagination * max_pages, because any more items would not have been possible to include anyway.
A new |silent= param disables visible output from this template. The {{database report/footer}} invocation from the bot is also suppressed. This is inspired by @JPxG's very creative uses of this template!

– SD0001 (talk) 11:04, 10 March 2024 (UTC)[reply]

Something (apparently bullet 3) broke User:Jonesey95/self-transcluded-templates‎. The query in the template parameter still works correctly in isolation. —Cryptic 13:52, 10 March 2024 (UTC)[reply]

Should be fixed now with 0068ece. – SD0001 (talk) 15:28, 10 March 2024 (UTC)[reply]

Based. jp×g 🗯️ 17:20, 10 March 2024 (UTC)[reply]

April 2024 updates

A new ~~preprocess_js~~ postprocess_js option now allows specifying custom code, in JavaScript, which the bot would apply on the query result. This enables broader use cases for which row_template (which can only operate on one row at a time) wasn't sufficient. The postprocess() function that you define has access to all the rows at once, so you can use it to synthesise new rows (eg. a "Total" row at the bottom), dynamically modify rows based on content of other rows, etc.
Clicking the update now button will now give you live updates of the query execution and processing. This avoids having to repeatedly reload the page history to check if the update occurred.

– SD0001 (talk) 06:38, 15 April 2024 (UTC)[reply]

Time stamp formatting request

This is a request to change the output for the |last_updated= parameter to match the time format used in signatures. The current time stamp looks like 1 July 2024 08:19 (UTC). The signature time stamp format looks like 08:19, 1 July 2024 (UTC). I believe that will make the time format compatible with the "Change UTC-based times and dates, such as those used in signatures, to be relative to local time" gadget. This is probably a change for the bot operator rather than the template, now that I look at the code. Pinging SD0001, who might know where this code lives. – Jonesey95 (talk) 17:20, 1 July 2024 (UTC)[reply]

Done in 72dde75. – SD0001 (talk) 18:36, 1 July 2024 (UTC)[reply]

Works great, thanks! – Jonesey95 (talk) 18:46, 1 July 2024 (UTC)[reply]

Pad space in simple database report result

I'm using this template to run a count query in combination with skip_table to simply output a number. Now when I transclude the report page as if it was a template, it puts a space in front of the number, showing " 430" instead of "430", for example. Is there any way available to avoid that pad space through the setup of the report? Stefen Tower_{s among the rest!} ^{Gab • Gruntwerk} 00:02, 14 August 2024 (UTC)[reply]

SD0001 I don't know if this is exactly the cause of what I'm dealing with, but in the Database report code, it completes the outer #if section before the second </div> - might that be in error? Stefen Tower_{s among the rest!} ^{Gab • Gruntwerk} 04:14, 14 August 2024 (UTC)[reply]

Good catch. Those div tags were unbalanced, as far as I could see, and there was some extra whitespace. I tidied up a bit. If it causes problems in other transclusions, please ping me and I will take a look. – Jonesey95 (talk) 05:50, 14 August 2024 (UTC)[reply]

That got us very close but there is still an extra newline. Perhaps if the newline between <includeonly> and {{#invoke: was removed, that would do it. Stefen Tower_{s among the rest!} ^{Gab • Gruntwerk} 06:32, 14 August 2024 (UTC)[reply]

Whitespace can come from other places as well. Use Special:ExpandTemplates to check the post-expand output – the presence of the category adds some whitespace which just {{trim}} is unable to remove. Using {{trim|{{suppress categories|{{WP:WikiProject Louisville/OldIssues/count}}}}}} gives you what you want. – SD0001 (talk) 13:11, 14 August 2024 (UTC)[reply]

Thank you both for all the help here. This is has been a very educational exercise all the way around. And we now have a working new use case for this template. Cheers! Stefen Tower_{s among the rest!} ^{Gab • Gruntwerk} 18:48, 14 August 2024 (UTC)[reply]