====== dbquery Plugin ======

---- plugin ----
description: Securely display query results from a database
author     : Andreas Gohr 
email      : dokuwiki@cosmocode.de
type       : helper, syntax
lastupdate : 2023-07-10
compatible : Hogfather
depends    : 
conflicts  : 
similar    : sql, sqlcomp
tags       : sql, db, database, mysql, postgres, sqlite, pdo

downloadurl: https://github.com/cosmocode/dokuwiki-plugin-dbquery/zipball/master
bugtracker : https://github.com/cosmocode/dokuwiki-plugin-dbquery/issues
sourcerepo : https://github.com/cosmocode/dokuwiki-plugin-dbquery
donationurl: 

screenshot_img : 
----

[[https://www.cosmocode.de/en/open-source/dokuwiki-plugins/|{{ http://cosmocode.de/static/img/dokuwiki/dwplugins.png?recache|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:plugin|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 [[https://www.php.net/manual/en/pdo.construct.php|connection DSN]] and a user name and password.

The DSN configures how to connect to the database and it is [[https://www.php.net/manual/en/pdo.drivers.php|PDO driver]] dependent.

Note: PDO drivers have to be installed and enabled with your PHP setup.

Here are a few typical DSNs:

<code>
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
</code>

You can find more info on the DSN format specific to your driver here:

  * [[https://www.php.net/manual/en/ref.pdo-sqlite.connection.php|PDO_SQLITE DSN]]
  * [[https://www.php.net/manual/en/ref.pdo-pgsql.connection.php|PDO_PGSQL DSN]]
  * [[https://www.php.net/manual/en/ref.pdo-odbc.connection.php|PDO_ODBC DSN]]
  * [[https://www.php.net/manual/en/ref.pdo-oci.connection.php|PDO_OCI DSN]]
  * [[https://www.php.net/manual/en/ref.pdo-sqlsrv.connection.php|PDO_SQLSRV DSN]]
  * [[https://www.php.net/manual/en/ref.pdo-mysql.connection.php|PDO_MYSQL DSN]]
  * [[https://www.php.net/manual/en/ref.pdo-informix.connection.php|PDO_INFORMIX DSN]]
  * [[https://www.php.net/manual/en/ref.pdo-ibm.connection.php|PDO_IBM DSN]]
  * [[https://www.php.net/manual/en/ref.pdo-firebird.connection.php|PDO_FIREBIRD DSN]]
  * [[https://www.php.net/manual/en/ref.pdo-dblib.connection.php|PDO_DBLIB DSN]]
  * [[https://www.php.net/manual/en/ref.pdo-cubrid.connection.php|PDO_CUBRID DSN]]

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.

<file>
====== My First Query ======

<code sql>
SELECT * FROM usertable;
</code>
</file>

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 '':'' |

<file>
====== My First Query ======

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


==== 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.


<file>
====== 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>
</file>

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 [[config: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