DokuWiki

It's better when it's simple

User Tools

Site Tools


plugin:sqlcomp

sqlcomp Plugin

Compatible with DokuWiki

No compatibility info given!

plugin Show Table from Database Result and monitor changes on the resultset

Last updated on
2017-08-17
Provides
Syntax
Repository
Source

Similar to sql

Tagged with csv, database, mysql, sqlite

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.

Install Oracle support

To use this plugin with Oracle databases, first install the Oracle libraries (for example oracle instant client) on your webserver. I have managed this copying the content of the oracle instantclient-basic-nt-xxx into my apache\bin folder. Then you have to activate the php_oci8 extension in your php.ini file.

Update

:!: IMPORTANT UPDATE NOTES

Since 2017-08-17: Changed storage of aliasnames for database-connections

All users of plugin-versions prior to current version should move their database-aliases from file sqlcomp/config.php into the new DokuWiki setting dbaliases and remove the file after this. Translate like this:

OLD config.php setting: $sqlcomp['alias'] = "connectiondata";
NEW DokuWiki setting:   alias="connectiondata"

Features

What can this little extension do:

  • Execute SQL-Statements and show resultset as table in DokuWiki style
  • Filter/Sort results using the datatable-plugin
  • Works with all common database types, like: MySQL, MicrosoftSQL, Oracle, PostgreSQL, Access, SQLite, CSV
  • Compare the resultsets against a cached version of the same resultset, marking changed rows.
  • Optionally have server connection string (dbtype/hostname/user/pass) in DokuWiki-setting instead of pagesource
  • If no resultset is given (e.g. update, insert, delete) it will show the number of affected rows.

Syntax

[[connection|sql|options]]

The syntax consist of pipe-delimited (|) elements:

1. Parameter: The connection string

The connection element itself is build from a colon-delimited (:) list of aspects:

dbtype:dbserver:dbuser:dbpass:dbname
Parameter Meaning
dbtype Kind of database server, e.g. mysql (see below)
dbserver Hostname (or IP-Address) of the database server
dbuser Username to authenticate at database
dbpass Password for the dbuser
dbname Name of the database (schema) to use

To simplify and also secure pagesource, the connection-string can put into the DokuWiki configuration setting dbaliases as aliasname=“connection”, e.g.: myfavdb=“dbtype:dbserver:dbuser:dbpass:dbname”. Then use aliasname instead of connection-string in your page:

[[aliasname|sql|options]]

The following table shows which options are supported by each dbtype:

dbtype dbserver dbuser dbpass dbname query options
mysql must must must must sql refresh
mssql must must must must sql refresh
oracle must must must must sql refresh
postgresql must must must must sql refresh
sqllite unused unused unused path to sqlite3 database sql refresh
sqlcsv unused unused unused path to csv file delimiter-char refresh
sqlaccess unused unused must path to mdb file sql refresh

Example code:

[[mysql:server:username:password:database|query|refresh]]
[[mssql:server:username:password:database|query|refresh]]
[[oracle:server:username:password:database|query|refresh]]
[[sqlite:unused:unused:unused:path to sqlite3 database|query|refresh]]
[[sqlcsv:unused:unused:unused:path to csv file|delimiter|refresh]]
[[sqlaccess:unused:unused:password:path to mdb file|query|refresh]]
[[postgresql:server:username:password:database|query|refresh]]

2. Parameter: SQL-Statement

Replace sql in syntax by your SQL-Query to execute. The result of this query is shown as table in DokuWiki-style. For example:

[[mydb|SELECT * FROM users ORDER BY last_logon]]

It is allowed to split it into multiple lines, to keep bigger queries readable:

[[mydb|SELECT
  u.id, u.name, u.fullname, u.mail, u.last_logon, g.id, g.name
FROM users AS u
LEFT JOIN groups AS g ON (g.uid = u.id)
GROUP BY u.id
ORDER BY u.last_logon ASC
]]

3. Parameter: Options

Options can be completely omitted, in which case the default-values set in DokuWiki configuration take place. Multiple options can be given, delimited by ampersand (&).

The following options are known:

Option Meaning
<INTEGER> An integer value (e.g. 60) will be taken as refresh-time in minutes, showing changes in tabledata. Set to 0 to force no diff rendering (default)

Configuration and Settings

The following settings are changeable by using DokuWiki settings in admin-page:

Setting Default Meaning
dbaliases -empty- Return delimited list of database connections aliases with their assigned values
default_refresh 0 Number of minutes to keep track of and visualize SQL result changes (0 means to not track changes)
sql_locale de_DE Set the local-type for database connection. This will influence punctuation of numbers, time and date results, etc.

Development

Change Log

  • 2017-08-17
    • Added configuraton option dbaliases
    • Moved all language data from plugin code into <lang>/lang.php
    • Replaces deprecated mysql_* calls with mysqli_*, which where PHP7 safe.
    • Added configuraton option show_diffs - select if table-changes should be visualized or not.
    • Plugin adopted by Oliver Geisen from originate author Christoph Lang.

Known Bugs and Issues

  1. :!: SECURITY NOTE: Use this plugin with care on Wiki's with access from Internet and public editable pages.

ToDo/Wish List

  1. Change syntax - The syntax used is bad ([[ ... ]] is reserved for links. Change to something like <sqlcomp OPTION...> SQL-QUERY </sqlcomp>.
  2. Determine locale settings automatically instead of using config-param $conf['sql_locale']
  3. Replace mysqli_* calls with PDO-Methods

Discussion

Please put your comments here discussion or better, create an issue at Github.

plugin/sqlcomp.txt · Last modified: 2017-08-18 13:34 by og