This template summons a bot to update the page with the results of the provided database query. It must be "closed" with{{Database report end}}; the text between these two templates will be replaced by the bot-generated table. It is possible to have multiple reports in one page.
The table is updated by the bot whenever you click the "Update the table now" button, and can be configured to automatically updateperiodically by setting|interval=N (one update every N days).
Some formatting options are provided which enable page titles to be wikilinked and article excerpts to be shown. For complex use cases, therow_template orpostprocess_js options can be used which allow you to format the query output in arbitrary ways using wikimarkup, Lua or JavaScript.
If you want to create several reports with similar configuration, you can parametrize them using Lua. SeeModule:Database report for details.
Don't know SQL? No worries. SDZeroBot'sAutoSQL will help you get the SQL equivalent of a query written in plain English. It doesn't work all the time, sadly – in which case you can ask for help atWP:RAQ.
Example
{{Database report |sql = SELECTpage_title,rev_timestampAS"Last revid",actor_nameASCreator,user_editcount FROMpagetriage_page JOINpageonpage_id=ptrp_page_id JOINrevisionONpage_id=rev_pageANDrev_parent_id=0 JOINactorONrev_actor=actor_id LEFTJOINuserONuser_id=actor_user WHEREpage_namespace=0 ANDpage_is_redirect=0 ANDptrp_reviewed=0 |wikilinks = 1, 5:2 <!-- Link 1st column to mainspace, 5th column to user namespace (ns 2) --> |excerpts = |comments = |widths = 2:9em, 3:15em <!-- Set 2nd column width as 9em, 3rd column width to 15em --> |interval = 7 <!-- Update once every 7 days --> |table_style = |pagination = |max_pages = }}... (This will be overwritten by the bot) ...{{Database report end}}
The SQL query used to generate the report. Consider usingQuarry to test your SQL first. For queries with large outputs, consider using aLIMIT clause to limit the output size, or usepagination.IMPORTANT: The query must execute within 10 minutes. The bot uses a server-side timeout – MariaDB'smax_statement_time variable to prevent overload on the DB. This should only contain 1 SQL statement. Do not combine multiple statements with semicolons.
If the SQL needs to contain vertical pipes (|) or other characters that may get parsed as wikitext (like{{ and}}), wrap the SQL with<nowiki>...</nowiki>.
Wikilink page titles. The syntax is best explained with some examples:
wikilinks =4 Links titles in the 4th column. Use this style if the column contains the full page name.
wikilinks =4:2 Links titles in 4th column to user (ns:2) namespace. Use this style if the column contains the page name without namespace.
SeeTemplate:Namespaces for namespace numbers. Common ones are: 0 (article), 2 (user), 4 (Wikipedia), 6 (file), 10 (template), 14 (category), 118 (draft). For respective talk namespaces, add 1 to those numbers.
wikilinks =5:2:show Links titles in 4th column to user (ns:2) namespace, and additionally adds the namespace prefix to the displayed text.
wikilinks =4:c3 Links the titles in 4th column using namespace number given in the third column. Applicable for queries likeSELECTpage_namespace,page_titlefrom... that have both page title and namespace but in different columns.
wikilinks =4:c3:show Same as above, but additionally put the namespace prefix in the displayed text.
wikilinks =2:2:show, 3:0 Links multiple columns! The configuration of each column (using one of the above styles) is comma-separated.
wikilinks =1:0, 2:2:show, 3:4 Adds links to three different columns: 1st column to mainspace, 2nd column to userspace making the "User:" prefix visible, and titles in 3rd column to WP space (with the namespace prefix piped).
comments
Comma-separated list of column numbers that contain edit summary comments or log action comments. Specifying this makes the bot escape the text so that edit summaries like "added {{infobox person}}" or "removed [[File:Example.jpg]]" are properly displayed without the template being transluded or images showing up on the report page.
widths
Explicitly specify the column widths. Eg.widths =2:10em, 4:20px forces the 2nd column to have a width of 10em and the 4th column to get a width of 20px.
table_style
The style attribute applied to the table element. By default this isoverflow-wrap: anywhere which causes better overflow behaviour. To suppress the default, useoverflow-wrap: normal.
Apart from srcColumnNumber everything is optional. However, optional parameters can only be used if the ones before were used, that is: to mention namespace, you also need to mention destColumnNumber first.
srcColumnNumber:(required) The column number containing page names whose excerpts are to be shown
destColumnNumber: The column number in which to place the excerpt. The original contents of that column will be shifted rightwards. If unspecified this takes the value (srcColumnNumber + 1)
namespace: The namespace number to use for page names in srcColumnNumber. If unspecified, it's taken as 0 – which means that the page name in srcColumnNumber is treated as the full page name (appropriate for mainspace). Usec4 to get the namespace number from 4th column. This can be used as input to{{Namespace name}}, to return the actual name:"{{Namespace name|1}}:" will return "Talk:" and"{{Namespace name|10}}:" will return "Template:".
charLimit: A soft limit for size of excerpt by number of characters. This is a soft limit, the excerpt can be longer to avoid cutting mid-sentence. (Default: 250)
charHardLimit: A hard char limit for size of excerpt. Sentences may be cut in the middle (but in that case will end in ellipsis). (Default: 500)
Most of the time, excerpt columns will mangle the layout of the table. Always use thewidths parameter to tune the column widths.
Since excerpts add columns to the table, note that the column numbers used by all other parameters (widths,wikilinks,comments,remove_underscores) take into account the added columns.
Excerpts are only intended for articles and drafts. The Node.js code used for generating them ishere – only the initial few lines of the first paragraph are kept with references, markup, images, infoboxes etc are removed.
Excerpts for multiple columns are supported – in which case this parameter should be comma-separated list of excerpt configs.
remove_underscores
A comma-separated list of column numbers in which underscores are to be replaced with spaces. This is intended for columns containing page titles, since the database stores titles with underscores (_) in place of spaces. You can prettify it a bit by using this parameter. This is automatically done for columns withwikilinks orexcerpts.
interval
(No default) Numerical value. Number of days to wait between automatic updates. Minimum allowed value: 1. If unspecified, no periodic updates are done.
pagination
(No default) Number of results to include in a page. Further results are saved to paginated subpages. For example, if|pagination=1000 is set and there are 4200 results, 1–1000 are saved to the page containing the query. 1001–2000 are saved to the/2 subpage, and so on. If unspecified, all results are saved on a single page. See alsomax_pages.
Note: Pagination cannot used when there are multiple reports on a page.
max_pages
(Default: 5). For use withpagination only. Max number of report pages to create. This can be set upto 20.
hide
Hide a column. Normally columns can be hidden by just not including it in the SELECT statement. However, some columns like namespace number are necessary for generation ofwikilinks but undesirable in final output. Specify the column number(s) of such column(s) here, comma-separated if multiple.
row_template
Generates a template call rather than a table row; the template must then generate the table row in turn or the table header/footer need to be suppressed usingskip_table parameter. Values in the row are passed to the template as unnamed parameters (|1=,|2=, etc).
To have the template substituted instead of transcluded, put "subst:" before the template name.
To use a Lua module instead of a template, put "#invoke:" before the module name.
row_template_named_params
While using row_template, instead of using unnamed parameters, use the column names as parameters. This is a boolean field - any value will work.
skip_table
Suppresses the table markup completely when usingrow_template. Any value will work.
header_template
Generates a template call rather than a table header; the template must then generate the table header. No parameters are passed to this template. The template can be substituted as well - just specify "subst:" before the template name.
If this is used without usingrow_template as well, then the header template will be placed over the table, and table headers will still be generated.
footer_template
Generates a footer calling the given template. For use withskip_table. This could be used along withheader_template for collapsing (collapse top/collapse bottom) or creating columns (div col/div col end) in the generated content. No parameters are passed to the template. It can be substituted instead by specifying "subst:" before the template name. For quick reference:|} is{{end}}.
silent
Suppress visible output from this template. Only the table generated by the bot will be visible. The last update timestamp and query runtime are also not saved. This means that if the query result is unchanged, periodic updates won't reflect in the page history.
This is a boolean field - any value will work.
postprocess_js
Experimental
Custom postprocessing code, in JavaScript, which is executed by the bot on the raw db output. Format:
asyncfunctionpostprocess(rows){// `rows` is an array.// Each element in `rows` is an object with column names as keys, and with values stringified// Eg. for SELECT page_namespace, page_title FROM page LIMIT 2// the `rows` could be:// [// { page_namespace: '0', page_title: 'Main Page' },// { page_namespace: '1', page_title: 'Main Page' },// ]// Write code to process rows here.// Return it after modifications:returnrows;}
The postprocess() function is applied on the query resultbefore it is modified by other formatting options likewikilinks. It can be used to introduce new derived rows or columns (eg. a "Total" row at the bottom), dynamically modify rows based on content of other rows, etc. If the code contains wikimarkup-like syntax that could confuse the parser, wrap the whole code within<nowiki>...</nowiki>.
The JS code is run on Node.js v22.16.0 sandboxed to use upto 16 MB of memory and 1.5 seconds of execution time. No network or filesystem access is allowed – with the exceptions below:
For the Action API,awaitbot.api({action:'query',...}) can be alternatively used. Parameters action: 'query', format: 'json' and formatversion: 2 are set by default.
lua_source
Read the full configuration of the report from a Lua module. All setup should be done in the module throughModule:Database report. See the module documentation for more details. If any parameter is configured in both Lua and the template, the value from the template takes precedence.
lua_function
(Default: main) Name of the lua function in lua_source which returns the generated configuration. Only applicable whenlua_source is used.
lua_arg_*
Parameters can be passed to the Lua function. For example,|lua_arg_foo=bar passes|foo=bar to the module. Only applicable whenlua_source is used.
SQL formatting tips
Datetime columns are stored in YYYYMMDDHHmmss format. Select them asDATE_FORMAT(rev_timestamp,'%Y-%m-%d %H:%i') orDATE_FORMAT(rev_timestamp,'%Y-%m-%d').
To use this with{{static row numbers}}, putheader_template = static row numbers andtable_class = wikitable sortable static-row-numbers static-row-header-text.
To display full wikilinked page names where namespace can vary (as inSELECTpage_namespace,page_titleFROM...), use|wikilinks=2:c1:show and then hide the namespace column from display (|hide=1).
This template also plays well with{{Namespace name}}, which allows you to avoid extra queries: for example,"{{Namespace name|1}}:" will return "Talk:" and"{{Namespace name|8}}:" will return "MediaWiki:".
Advanced styling
The defaulttable_style is overflow-wrap:anywhere as it avoids columns becoming too wide due to presence of long non-breaking text. Sometimes, usingoverflow-wrap: anywhere; word-break: break-word can help instead. These may cause even the headers to break mid-word – use thewidths setting to avoid that.
Another way to avoid the ugliness of headers breaking in the middle of a word is to apply the style on the <tbody> element instead. To do this, usetable_class to apply a distinguishing class and then style it viaTemplateStyles. You can also apply custom styles on the <thead> element this way.
This template prefers block formatting of parameters.
Parameter
Description
Type
Status
sql
sql
SQL query used to generate the report
Example
SELECT page_title, page_namespace FROM page LIMIT 10
Content
required
wikilinks
wikilinks
Configuration for wikilinking page titles in report output (see [[Template:Database_report#wikilinks]] for syntax)
Example
2:2:show, 3:0
String
suggested
widths
widths
Explicitly specify the column widths. Eg. widths = 2:10em, 4:20px forces the 2nd column to have a width of 10em and the 4th column to get a width of 20px.
Example
2:10em, 4:20px
String
suggested
interval
interval
Number of days to wait between automatic updates
Suggested values
1730
Number
suggested
comments
comments
Comma-separated list of column numbers that contain edit summaries or log action comments
String
optional
table_class
table_class
class attribute applied to the table element
Default
wikitable sortable
String
optional
table_style
table_style
style attribute applied to the table element
Default
overflow-wrap: anywhere
String
optional
excerpts
excerpts
Configuration for adding columns containing excerpts of page content (see [[Template:Database_report#excerpts]] for syntax)
String
optional
row_template
row_template
Generates a template call rather than a table row; the template must then generate the table row in turn or the table header/footer need to be suppressed using skip_table parameter.
Template
optional
row_template_named_params
row_template_named_params
While using row_template, instead of using unnamed parameters, use the column names as parameters
Boolean
optional
skip_table
skip_table
Suppresses the table markup completely when using row_template
Boolean
optional
header_template
header_template
Generates a template call rather than a table header; the template must then generate the table header. No parameters are passed to this template.
Template
optional
footer_template
footer_template
Generates a footer calling the given template. For use with skip_table.
Template
optional
silent
silent
Suppress visible output from this template. Only the table generated by the bot will be visible. The last update timestamp and query runtime are also not saved.
Boolean
optional
pagination
pagination
Number of results to include in a page. Further results are saved to paginated subpages.
Number
optional
max_pages
max_pages
For use with pagination only. Max number of report pages to create.
Number
optional
remove_underscores
remove_underscores
Comma-separated list of column numbers in which underscores are to be replaced with spaces
String
optional
postprocess_js
postprocess_js
(Experimental) Custom postprocessing code, in JavaScript, which is executed by the bot on the raw db output. See documentation for details.
