DokuWiki

It's better when it's simple

User Tools

Site Tools


plugin:dbquery

dbquery Plugin

Compatible with DokuWiki

  • 2024-02-06 "Kaos" unknown
  • 2023-04-04 "Jack Jackrum" unknown
  • 2022-07-31 "Igor" unknown
  • 2020-07-29 "Hogfather" yes

plugin Securely display query results from a database

Last updated on
2023-07-10
Provides
Syntax, Helper
Repository
Source

Similar to sql, sql2wiki, sqlcomp

Tagged with database, db, mysql, pdo, postgres, sql, sqlite

A CosmoCode Plugin

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.

Installation

Install the plugin using the Plugin Manager and the download URL above, which points to latest version of the plugin. Refer to Plugins on how to install plugins manually.

Configuration

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.

Query Setup

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.

Variable Substitution

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:

:user The user name of the currently logged in user when the query is executed
:mail The email address of the currently logged in user when the query is executed
:groups The groups of the currently logged in user when the query is executed. They are properly set up to be used in a IN statement.
:id The full ID of the page the query is run on, prefixed with a colon :
:page The page without namespace of the page the query is run on
:ns The namespace of the page the query is run on, prefixed with a colon :. The root namespace is :
====== My First Query ======

<code sql>
SELECT * FROM usertable WHERE primarygroup IN (:groups);
</code>

Macros

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.

Status Queries

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 status
  • the result may optionally contain another field named or aliased to result

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 html type.

====== 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 red or 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 result field.

Query Execution

To run a predefined query, the query syntax is used: {{QUERY:queryname}}, where 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 [[ and ]] respectively.
  • Only http://, 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
plugin/dbquery.txt · Last modified: 2023-07-10 23:50 by andi

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki