模板:Database report

立即更新

本表格通过查询数据库副本生成，由机器人维护。
在表格区域内进行的编辑将在下次更新时被覆盖！

模板文档[查看][编辑][历史][清除缓存]

此模板可召唤机器人，依照所给数据库查询的结果来更新页面。本模板必须搭配“{{Database report end}}”来收尾，机器人会将两者之间内容替换为报表。同一页面可以放置多份报告。

你可以随时点击“立即更新”连结，让机器人更新报表，还可以设置|interval=N（每N天更新一次）启用定期自动更新。

模板有部分格式字段，如可给页面标题加上连结，或展示条目内容摘录。若有复杂需求，还可使用row_template和postprocess_js字段，用维基标记、Lua、JavaScript自订内容输出格式。

若想创建多份配置类似的报表，则可使用Lua设定参数。详情请见Module:Database report。

本模板目前由PexBot供养。调用本模板的页面将归入Category:PexBot数据库报告订阅。

不会写SQL？别担心，英文维基百科SDZeroBot的AutoSQL能帮你把人话（英语）改写成SQL查询。不过工具不包成功，如果还不行可以到WP:RAQ求助。

例子

{{Database report
|sql         = 

    SELECT page_title, rev_timestamp AS 最新版本id, actor_name AS 創建者, user_editcount

    FROM pagetriage_page

    JOIN page on page_id = ptrp_page_id

    JOIN revision ON page_id = rev_page AND rev_parent_id = 0

    JOIN actor ON rev_actor = actor_id

    LEFT JOIN user ON user_id = actor_user

    WHERE page_namespace = 0

    AND page_is_redirect = 0

    AND ptrp_reviewed = 0
|wikilinks   = 1, 5:2 <!-- 將第1列連結到主空間，第5列連結到用戶空間（ns 2） -->
|excerpts    = 
|comments    = 
|widths      = 2:9em, 3:15em <!-- 將第2列列寬設為9em，第三列列寬設為15em -->
|interval    = 7 <!-- 每7日更新一次 -->
|table_style = 
|pagination  = 
|max_pages   = 
}}
...（機械人將覆寫此處）...
{{Database report end}}

参数们

找说明找得一脸懵逼？来试试图表版创建界面。

sql

用来生成报告的SQL查询。可以先用Quarry测试写的SQL。如果查询的结果数极多，可用LIMIT子句限制输出大小，或用pagination分页。要点：查询必须在10分钟内完成。机器人使用服务器端逾时（MariaDB的max_statement_time变元）来避免数据库过载。此处只认单条表达式，不要用分号塞多条表达式。

若SQL中有竖线（|），或视为wikitext语法字元（譬如{{和}}），请用<nowiki>...</nowiki>包裹SQL。

此为唯一必填参数。

数据库表参考资料：page · revision · text · actor · user · main database layout manual page

wikilinks

给页面标题加维基连结。下述示例就是最好的解释：

• wikilinks = 4 将第4列中的标题加连结。若该列为完整页面标题，可采用此方式。
• wikilinks = 4:2 将第4列中的标题连向用户（ns:2）空间。若该列为不含命名空间的标题，可使用此方式。
  • 命名空间编号详见Template:Namespaces。以下摘录常用编号：0（条目）、2（用户）、4（维基百科）、6（档案）、10（模板）、14（分类）、118（草稿）。对应的讨论空间再加1。
• wikilinks = 5:2:show 将第4列中的标题连向用户（ns:2）空间，且显示时补上命名空间前缀。
• wikilinks = 4:c3 把第3列中的命名空间编号套用给第4列。多用于SELECT page_namespace, page_title from ...式查询，即页面标题或命名空间在两列上。
• wikilinks = 4:c3:show 同上，但补上命名空间前缀显示。
• wikilinks = 2:2:show, 3:0 给多列添加连结！各列配置间以分号分隔，每列设置方式见上。
• wikilinks = 1:0, 2:2:show, 3:4 给三列添加连结：第1列连结到主空间，第2列连结到用户空间且显示“User:”前缀，第3列连结到WP空间（用管道连结除去前缀）。

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

明确指定栏宽。如widths = 2:10em, 4:20px即将第2栏设为10em宽，把第4栏目调整为20px宽。

table_style

The style attribute applied to the table element. By default this is overflow-wrap: anywhere which causes better overflow behaviour. To suppress the default, use overflow-wrap: normal.

更多信息：§ Advanced styling

table_class

The class attribute applied to the table element. By default, this is wikitable sortable.

excerpts

Show an excerpt of articles. Excerpt config takes the form:

  srcColumnNumber : destColumnNumber : namespace : charLimit : charHardLimit

• 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). Use c4 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 "Template:Namespace name:" and "{{Namespace name|10}}:" will return "Template:Namespace name:".
  • 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 the widths 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 is here – 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 with wikilinks or excerpts.

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 also max_pages.

Note: Pagination cannot used when there are multiple reports on a page.

max_pages

(Default: 5). For use with pagination 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 of wikilinks 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 using skip_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 using row_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 using row_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 with skip_table. This could be used along with header_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:

async function postprocess(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:
  return rows;
}

The postprocess() function is applied on the query result before it is modified by other formatting options like wikilinks. 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:

await bot.request('https://...') provides readonly access to various Wikimedia APIs. Supported endpoints: en.wikipedia.org/api/rest_v1, wikimedia.org/api/rest_v1, en.wikipedia.org/w/rest.php, en.wikipedia/org/w/api.php, api.wikimedia.org and query.wikidata.org. These can be used for fetching pageview data (from REST API), ORES scores (from Lift Wing), and so on.

For the Action API, await bot.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 through Module: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 when lua_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 when lua_source is used.

SQL formatting tips

• Datetime columns are stored in YYYYMMDDHHmmss format. Select them as DATE_FORMAT(rev_timestamp, '%Y-%m-%d %H:%i') or DATE_FORMAT(rev_timestamp, '%Y-%m-%d').
• For generating a serial number column, select ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS "No.". For example, see WP:Database reports/Fully salted titles with unusually long expiries.
• To use this with {{static row numbers}}, put header_template = static row numbers and table_class = wikitable sortable static-row-numbers static-row-header-text.
• To display full wikilinked page names where namespace can vary (as in SELECT page_namespace, page_title FROM ...), 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 "Template:Namespace name:" and "{{Namespace name|8}}:" will return "Template:Namespace name:".

Advanced styling

The default table_style is overflow-wrap:anywhere as it avoids columns becoming too wide due to presence of long non-breaking text. Sometimes, using overflow-wrap: anywhere; word-break: break-word can help instead. These may cause even the headers to break mid-word – use the widths 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, use table_class to apply a distinguishing class and then style it via TemplateStyles. You can also apply custom styles on the <thead> element this way.

---

The design of the bot and template were inspired from User:ListeriaBot and {{Wikidata list}}.

TemplateData

Tabulate results of SQL queries against wiki replica databases

模板参数

此模板首选参数换行的代码块格式。

参数		描述	类型	状态
sql	sql	SQL query used to generate the report 示例值 SELECT page_title, page_namespace FROM page LIMIT 10	内容	必需
wikilinks	wikilinks	Configuration for wikilinking page titles in report output (see [[Template:Database_report#wikilinks]] for syntax) 示例值 2:2:show, 3:0	字符串	推荐
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. 示例值 2:10em, 4:20px	字符串	推荐
interval	interval	Number of days to wait between automatic updates 推荐值 1 7 30	数字	推荐
comments	comments	Comma-separated list of column numbers that contain edit summaries or log action comments	字符串	可选
table_class	table_class	class attribute applied to the table element 默认值 wikitable sortable	字符串	可选
table_style	table_style	style attribute applied to the table element 默认值 overflow-wrap: anywhere	字符串	可选
excerpts	excerpts	Configuration for adding columns containing excerpts of page content (see [[Template:Database_report#excerpts]] for syntax)	字符串	可选
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.	模板	可选
row_template_named_params	row_template_named_params	While using row_template, instead of using unnamed parameters, use the column names as parameters	布尔	可选
skip_table	skip_table	Suppresses the table markup completely when using row_template	布尔	可选
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.	模板	可选
footer_template	footer_template	Generates a footer calling the given template. For use with skip_table.	模板	可选
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.	布尔	可选
pagination	pagination	Number of results to include in a page. Further results are saved to paginated subpages.	数字	可选
max_pages	max_pages	For use with pagination only. Max number of report pages to create.	数字	可选
remove_underscores	remove_underscores	Comma-separated list of column numbers in which underscores are to be replaced with spaces	字符串	可选
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.	内容	可选

上述文档嵌入自Template:Database report/doc。（编辑 | 历史）
编者可以在本模板的沙盒（创建 | 镜像）和测试样例（创建）页面进行实验。
请在/doc子页面中添加分类。本模板的子页面。