Extension:DataTable2

MediaWiki extensions manual

DataTable2 Release status: stable
Implementation	Parser extension , Database , Data extraction, Page action , Special page
Description	Store tabular data in the database and use them on the same or on other pages.
Author(s)	RV1971 (RV1971talk)
Latest version	1.1.0 (2018-04-10)
MediaWiki	1.30+
PHP	5.4+
Database changes	Yes
Tables	datatable2_data datatable2_meta
License	GNU General Public License 3.0 or later
Download	Download extension Git [?]: Download Git master browse repository (GitHub) commit history repository contributors (GitHub) code review
Parameters $wgSpecialDataTable2DataClasses $wgSpecialDataTable2PageParSep $wgDataTable2Args $wgDataTable2MetaWriteDest $wgDataTable2WriteDest $wgDataTable2MetaReadSrc $wgDataTable2ReadSrc $wgDataTable2SqlWhiteList
Tags ‎<datatable2> ‎<dt2-showtable>
Added rights datatable2-specialpages
Hooks used ArticleDelete LoadExtensionSchemaUpdates ParserFirstCallInit RevisionFromEditComplete
Quarterly downloads	4 (Ranked 129th)
Translate the DataTable2 extension if it is available at translatewiki.net

The DataTable2 extension is conceptually the converse of the basic idea of Semantic MediaWiki extension and similar approaches. For instance, with Semantic MediaWiki you can store the population of a number of cities in the respective pages (hence one record per page, unless you work with subobjects) and retrieve these data in other pages, including pages making up lists of cities. With DataTable2, you can store the population data of many cities on one page (or a number of pages) and retrieve it on other pages, including pages making up lists of cities. Hence the DataTable2 extension is useful for you whenever you wish to maintain information in a centralized place and use it on many other pages.

Furthermore, this extension allows you to comfortably enter data for fancy tables because you can enter the data just in tabular form with field and record separators of your choice, and then define a template that does all the formatting. This can be useful even if you do not need to retrieve the data on other pages.

I'm not aware of any other MediaWiki extension that provides these concepts in a generic way. The idea and the basics of its implementation are borrowed from the Tasks Extension which does the same thing for a specific purpose.

If you intend to do nontrivial things with your data, you might consider installing the Extension:Scribunto as well. DataTable2 includes a Lua library so that your Lua code can directly access the data.

Installation

By default, each column can store at most 255 characters. If you whish to change this limit, edit sql/datatable2_data.sql accordingly after downloading the extension before you proceed with the installation.

• Download and move the extracted DataTable2 folder to your extensions/ directory.
  Developers and code contributors should install the extension from Git instead, using:cd extensions/
git clone https://gerrit.wikimedia.org/r/mediawiki/extensions/DataTable2
• Add the following code at the bottom of your LocalSettings.php file:

  wfLoadExtension( 'DataTable2' );
  

• Run the update script which will automatically create the necessary database tables that this extension needs.
•  Done – Navigate to Special:Version on your wiki to verify that the extension is successfully installed.

Upgrading

The DataTable2 extension is intended as a replacement of the DataTable extension which has been removed from mediawiki.org because of vulnerability issues. Since DataTable2 does not only fix these issues but also offers a different data model with many more features, it is not backwards compatible; that's why I chose to give it a new name so that it can co-exist with the DataTable extension on the same wiki during the transition phase. I'm afraid migrating pages from DataTable to DataTable2 is a manual process since they are conceptually different.

Upgrading from 0.8.3 or earlier

Since version 1.0.0, pages using DataTable2 data through the Lua API are added to the appropriate tracking categories and assigned dependencies the same way as pages using DataTable2 data through tags or parser functions. If you already have such pages, you can fix the categories and dependencies either by performing a null edit on each page or by executing the script maintenance/refreshLinks.php. Note that the latter may take a considerable amount of time.

Configuration

Rights

The extension defines a new right datatable2-specialpages needed to use the special pages, which is assigned by default to all registered users. You might decide to restrict this if you have so many DataTable2 data that use of the special pages degrades performance.

It is pointless to restrict this for security reasons since all DataTable2 data can be extracted using the ‎<dt2-showtable> tag anyway. The extension does not intend to impose any restrictions on the use of these data.

LocalSettings.php

There is a number of parameters that can be customized in LocalSettings.php . They all have defaults which are likely to fit for many cases, so you can skip this section when installing the extension for the first time.

$wgDataTable2WriteDest

Name of the database table where data is written to.

$wgDataTable2ReadSrc

Name of the database table where data is read from.

You might set this to a view which is a union of $wgDataTable2Write and data from other sources which can then be read but not modified through this extension. In such a case, the data from other sources should leave the dtd_page column empty (NULL).

$wgDataTable2MetaWriteDest

Name of the database table where meta data is written to.

$wgDataTable2MetaReadSrc

Name of the database table where meta data is read from.

You might set this to a view which is a union of $wgDataTable2MetaWriteDest and data from other sources which can then be read but not modified through this extension.

$wgDataTable2Args

Default arguments for [[#‎<datatables>|‎<datatables>]] tags.

$wgDataTable2SqlWhiteList

Array of identifiers that may be used in WHERE and ORDER BY clauses, in addition to column names.

Unquoted identifiers in where and order-by arguments are converted to uppercase, hence the items in $wgDataTable2SqlWhiteList should be uppercase unless they are deliberately case-sensitive. The default contains only some rather portable SQL functions, which are probably a small subset of those available in your database backend. Hence you are most likely to add functions to this. Since the extension uses a parser which considers the dot as part of an identifier, you may add qualified names like functions in packages or other schemas.

$wgSpecialDataTable2PageParSep

Separator for parameters passed to special pages.

Parameters can be passed to special pages as subpage components. When more than one parameter is possible and a parameter could be a page name with a subpage, the slash cannot be used to separate the parameters. There is no obvious choice for the best separator since the characters which by default are not allowed in page titles cannot be used in internal links, either.

If you modify this, you will have to modify any messages which contain links to DataTable2 special pages passing more than one parameter. In the default configuration, this is the case for the message datatable2pages-row.

$wgSpecialDataTable2DataClasses

List of css classes for the table used in Special:DataTable2Data.

Messages

Since the extension defines a lot of messages which are constantly evolving with further development, they are not documented here. Please refer to i18n/qqq.json.

Database

By default, each column can contain at most 255 characters. If you would like to have more, you need to alter the columns of the table datatable2_data defined in the file datatable2_data.sql.

By default, the extension allows up to 30 columns in a table. In the unlikely case that you would like to enlarge the maximum number of columns, you need to add columns to the table datatable2_data defined in the file datatable2_data.sql and to adapt the class constant DataTable2Database::MAX_FIELDS in the file DataTable2Database.php.

By default, the first 10 columns are indexed. In order to index more (or less) columns, you need to create (or drop) indexes; see file datatable2_data.sql.

Usage

Storing and displaying data

Since data are saved to the database using the NewRevisionFromEditComplete hook, no data is stored in the database when the page has not changed. Therefore, you should install the extension before using ‎<datatable2> tags in your wiki pages. Otherwise, after installation, you need to modify each page containing ‎<datatable2> tags in order to get the data actually saved.

All pages storing data in DataTable2 tables are added to the tracking category defined by the system message datatable2-producer-category. You might decide to add some explanatory text to the category page.

‎<datatable2>

With <datatable2> tags, you display tabular data and optionally store it for use on other pages (or elsewhere on the same page). As a simple example, the following wikitext:

<datatable2 table="demo table" class="wikitable">
<head>
|-
! Page
! Rating
! Type
</head>
{{FULLPAGENAME}}|interesting page|extension description
[[MW:Manual:Contents{{!}}Manual]]|very interesting page|manual page
</datatable2>

will display

Page	Rating	Type
Extension:DataTable2/en	interesting page	extension description
Manual	very interesting page	manual page

The table argument tells the extension to store the data for later use in a logical table called 'demo table'; this has no influence on what is displayed here. A ‎<head> tag is provided, which implies that the whole thing is wrapped into a wikitable, with the CSS class given in the class argument. Note the use of the {{!}} template in the data, which is necessary because the pipe character is used as a field separator.

We can demonstrate some more features with a similar example:

<datatable2 table="another demo"
columns="page|rating|type"
fs=";">
<template>
'''{{{page}}}''': ''{{{rating}}}'' - {{{type}}}
</template>
<!-- one-line comments are possible -->
Extension:DataTable2;interesting page;extension description
[[MW:Manual:Contents|Manual]];very interesting page;manual page
</datatable2>

This will display

Extension:DataTable2: interesting page - extension description

Manual: very interesting page - manual page

This time, each record is formatted using template code provided in a ‎<template> tag, the columns have names which can be used in the template code, and the result is not automatically wrapped into a wikitable. You can also combine ‎<head> and ‎<template>, in which case they must be given in this order.

Furthermore, a different field separator has been used, which allows to do without the {{!}} template. In the data, xml comments (<!-- ... -->) are detected and removed from each row. Comments spanning multiple rows and syntax errors (such as -- appearing inside comments) will not be detected.

The following gives a complete list of possible arguments to the ‎<datatable2> tag. All of them are optional.

args

Additional arguments to pass to the template for each record, in the usual wiki syntax, i.e. pipe-separated. This can be useful if you use the template argument (see below) to use the same template in several tables and want to pass table-specific argument to the template.

class

CSS classes for the table. Implies that the data are wrapped into a table, even if no ‎<head> is given.

columns

Pipe-separated list of column names. Do not use names with leading underscores since they are reverved for internal use. You must specify names at least for those columns that you will use in WHERE clauses.

debug

Show the generated wikitext instead of converting it to HTML.

fs

Field separator when parsing data. Either a string or a PCRE included in slashes.

rs

Record separator when parsing data. Either a string or a PCRE included in slashes.

table

Logical table where data is stored. Table names obey the same rules as page titles regarding allowed characters; furthermore, spaces and underscores are equivalent, and the first letter is automatically converted to uppercase (if this is configured for the main namespace in your wiki installation).

template

Name of a template to use to display the data.

Providing a template with the template argument is an alternative to providing the template code as a ‎<template> tag in the page itself. This is useful if you have a number of similar tables in your wiki that you want to format with the same template code.

Retrieving data

When displaying data on a page, all pages where data are taken from are added to the list of used templates. This implies that a page will not be taken from a cache if underlying data have changed, and that the data source pages will be listed as if they were templates used on the page. Hence, in the edit preview, dependencies will be shown as "templates used on this page" (potentially including the page itself), and on the other hand, in the "What links here" page of data source page, all pages using this data will be shown as if they transcluded the page. Unfortunately, there is currently no wiki feature that allows to distinguish this kind of dependency from a normal template dependency.

All pages using data from DataTable2 tables are added to the tracking category defined by the system message datatable2-consumer-category. Furthermore, these pages will be added to individual tracking categories for each table used. The names of these tracking categories are created from the system message datatable2-consumer-detail-category (in your wiki's content language). You might decide to add some explanatory text to the category pages.

‎<dt2-showtable>

The <dt2-showtable> tag is very similar to the ‎<datatable2> tag, with the difference that data are not given on the wikipage but read from a logical table previously created with a ‎<datatable2> tag. Given the data specified above, an example could be:

<dt2-showtable table="another demo" class="wikitable"
where="type='extension description'">
<head>
|-
! Page
! Rating
! Type
</head>
<template>
|-
! {{{page}}}
| {{{rating}}}
| {{{type}}}
</template>
</dt2-showtable>

which will display:

Page	Rating	Type
Extension:DataTable2	interesting page	extension description

The possible arguments are the same as for <datatable2>, except that table is mandatory while columns, fs and rs do not apply. In addition, the following optional arguments can be given:

order-by

ORDER BY clause.

where

WHERE clause.

The WHERE and ORDER BY clauses work as if the data were stored in tables in your database backend, hence they obey the SQL syntax and semantic rules. But for reasons of security, they are parsed, and the extension checks that only column names and identifiers on the white list $wgDataTable2 are used, in order to avoid SQL injection. Furthermore, characters like the semicolon which are not needed in the clauses but can be used for SQL injection purposes are forbidden outside of quoted strings. An error message is displayed if the clause is not acceptable in this sense.

In most cases you will provide a template to display the data records since the records contain, in addition to the data you entered, also some fields about the page where the data was stored. The following fields are currently available:

dt2-src-fullpagename

The value of {{FULLPAGENAME}} in the page where data was stored.

dt2-src-pagename

The value of {{PAGENAME}} in the page where data was stored.

All other page- and namespace-related variables (like SUBPAGENAME or TALKPAGENAMEE) can be derived from this with constructs like {{SUBPAGENAME:{{{dt2-src-fullpagename}}}}}.

dt2-expand

The dt2-expand parser function inserts a template which gets its input data from a single record, if one is found. It is used as follows:

{{#dt2-expand: template | table | where | default | … }}

The parser function takes the following parameters, where the first three are mandatory:

• The name of the template to expand.
• The logical table defined with the ‎<datatable2> tag where the data should be taken from.
• The where clause that should select at most one record. If more then one record is found, an error message is displayed.
• Optionally the default text to return if no data are found. It is expanded only if needed, so using a complex template here does not lead to performance issues if used for unexpected errors only.
• Optionally further arguments that are appended as template arguments to the data selected from the database.

dt2-get

The dt2-get parser function selects and caches a single record, if one is found, and optionally displays one column. If no column is specified, the record is retrieved from the database but nothing is shown on the page. If no record is found, the default (if any) is returned, even if no column was specified. You can use this in conditional constructs to test first whether there is a record, and if so, to display its data in some way.

The parser function is used as follows:

{{#dt2-get: table | column | where | default }}

It takes the following parameters, where the first three are mandatory:

• The logical table defined with the ‎<datatable2> tag where the data should be taken from.
• The column name defined with the ‎<datatable2> tag. The column field may be left blank, in which case data is cached for later usage with the dt2-lastget parser function, but nothing is displayed.
• The where clause that should select at most one record. If more then one record is found, an error message is displayed.
• Optionally the default text to return if no data are found. It is expanded only if needed, so using a complex template here does not lead to performance issues if used for unexpected errors only.

dt2-lastget

The dt2-lastget parser function displays data from the last record fetched with the dt2-get parser function. It is used as follows:

{{#dt2-lastget: column | default }}

The parser function takes the following parameters, where only the first one is mandatory:

• The column name defined with the <datatable2> tag.
• Optionally the default text to return if no data are found. It is expanded only if needed, so using a complex template here does not lead to performance issues if used for unexpected errors only.

Using dt2-lastget is much more efficient than invoking dt2-get twice. But be careful when using this parser function. It is wise to use it only in wikitext very close to the corresponding dt2-get invocation since otherwise the wikitext becomes difficult to understand and error-prone.

Special pages

Three special pages are provided to inspect the stored data:

Special:DataTable2Tables

Lists all logical tables defined with the extension. For each table, the number of contributing pages and the number of records are shown. The former links to Special:DataTable2Pages, the latter to Special:DataTable2Data.

This special page accepts one parameter, which can either be appended to the URL with a slash (e.g. Special:DataTable2Tables/Employees) or given as the REQUEST parameter tablename. The former takes precedence. The page will display tables whose names are greater or equal to this.

Special:DataTable2Pages

Lists all pages contributing to a table, listing for each page the number of contributed records.

This special page accepts two parameters, which can either be appended to the URL (e.g. Special:DataTable2Pages/Employees//Kampala) or given as the REQUEST parameters tablename and pagename. The former take precedence. The separator between parameters appended to the URL is configured with the global variable $wgSpecialDataTable2PageParSep. The page will display for the given table those pages whose titles are greater or equal to the given one (regardless of the namespace).

Special:DataTable2Data

Lists the records in a table.

This special page accepts three parameters, which can either be appended to the URL (e.g. Special:DataTable2Pages/Employees//Kampala//Smith) or given as the REQUEST parameters tablename, pagename and data. The former take precedence. The separator between parameters appended to the URL is configured with the global variable $wgSpecialDataTable2PageParSep. The page will display for the given table and page (if specified) those records where the content of the first field is greater or equal to the given one.

Lua library

mw.ext.datatable2.select

mw.ext.datatable2.select( table, where, orderBy )

Selects data from a logical table. The arguments work the same way as the arguments table, where, order-by in the [[#‎<dt2-showtable>|‎<dt2-showtable>]] tag.

The result is returned as a (potentially empty) numerically-indexed array (with indexes starting at 1) of associative arrays, each of which represents a record. If no records are found, false is returned.

Release notes

These are the release notes for version 1.1.0 See Extension:DataTable2/History for older releases.

Configuration changes

The extension is now loaded using the wfLoadExtension() mechanism introduced in MediaWiki 1.25. It is therefore incompatible with older MediaWiki versions.

Bug fixes

Removed closing ?> at end of file. Removed backwards-compatibility shim DataTable2.i18n.php.

See also

Extensions doing the converse:

• Extension:Semantic MediaWiki

Other related extensions: