Table of Contents
Compatible with DokuWiki
- 2022-07-31 "Igor" unknown
- 2020-07-29 "Hogfather" yes
- 2018-04-22 "Greebo" unknown
- 2017-02-19 "Frusterick Manners" unknown
This plugin allows you display the results of a database query either as a table or a HTML formatted status. Unlike other, similar plugins this plugins strictly separates the definition of the queries to run from the part where they are used. This allows to use ACL rules to prevent users from running arbitrary queries on the connected database.
The connection to the database is done via PDO, the plugin itself is database agnostic.
The database connection has to be configured in the configuration manager, using a connection DSN and a user name and password.
The DSN configures how to connect to the database and it is PDO driver dependent.
Note: PDO drivers have to be installed and enabled with your PHP setup.
Here are a few typical DSNs:
mysql:host=localhost;port=3306;dbname=testdb mysql:unix_socket=/tmp/mysql.sock;dbname=testdb pgsql:host=localhost;port=5432;dbname=testdb sqlite:/opt/databases/mydb.sq3
You can find more info on the DSN format specific to your driver here:
You can also configure the namespace where the queries are defined in the configuration.
The plugin uses predefined queries that can later be accessed by their name. All queries are set up in a dedicated namespace (
dbquery by default). You should restrict this namespace to trustworthy users using ACLs.
Each query has its own page in the dbquery namespace. The page name can later be used to run the query (see below).
Basic Query Definition
To define a query, simply create a page as usual and add a code block (you can use DokuWiki's syntax highlighting) with your SQL query.
Note: for security reasons only SELECT queries are allowed.
You can add all kind of other syntax to this page, eg to describe what the query does etc. The dbquery plugin will take the first code block it finds in this page as the query's SQL.
====== My First Query ====== <code sql> SELECT * FROM usertable; </code>
When the query is run later (see below) a table with all result rows will be shown to the user.
Queries can be made a bit more dynamic by the use of predefined variables. This allows you to make queries depending on the page a query is ran from or on the user it is running for. The following variables are available:
| ||The user name of the currently logged in user when the query is executed|
| ||The email address of the currently logged in user when the query is executed|
| || The groups of the currently logged in user when the query is executed. They are properly set up to be used in a
| || The full ID of the page the query is run on, prefixed with a colon
| ||The page without namespace of the page the query is run on|
| || The namespace of the page the query is run on, prefixed with a colon
====== My First Query ====== <code sql> SELECT * FROM usertable WHERE primarygroup IN (:groups); </code>
Additional macros can be added to the query page to influence how the query is executed or the result is displayed. Currently the following macros are supported:
~~DBQUERY:transpose~~When the query result is shown as a table, the table is transposed with column headers to the left and rows shown as columns. This should only be done for very short result sets and many queried columns. Eg. where there are more columns than rows in the result.
Often you want to display a specific info based on the result of a query. Status queries allow you to do that.
A status query has the following properties:
- the result is exactly one row
- the result contains a field named or aliased to
- the result may optionally contain another field named or aliased to
For queries matching that definition, the plugin will not show a result table, but instead look for custom HTML code to display as defined on the query's page.
To define this HTML code, you need to add additional sections to your query page. Each section needs to be named after your status and it has to have a code block with the
====== Balance Checking ====== <code sql> SELECT IIF(balance >= 0, 'green', 'red') AS status, balance AS result FROM account WHERE user = :user; </code> ===== These are the possible status codes ===== ==== red ==== <code html> <div style="background-color: #F8CECC">In debt: :result</div> </code> ==== green ==== <code html> <div style="background-color: #D5E8D4">All okay: :result</div> </code>
The example above should make it clear. The SQL checks the balance of the current user and returns either
green as the status. A lookup in the sections further down then will display either a red or green box with the actual balance returned in the
To run a predefined query, the query syntax is used:
queryname is the page name of the query within the dbquery namespace.
The result is either a table with all the rows or the status HTML.
Note: DokuWiki usually caches page content. For highly dynamic queries you need to disable the page cache by inserting the
~~NOCACHE~~ macro into the page. This is not done by default, because you may have queries that are fine to only be updated once a day (see cachetime).
Note: the plugin will autodetect DokuWiki link syntax in the result contents and appropriately create links when showing tabular results. However there are a few caveats:
- The link syntax has to be the only cell content, eg. it starts and ends with
https://and internal links are recognized
- This feature does not the full wiki parser to parse contents, so no other syntax is recognized and handled specially