Show Table from Database Result and monitor changes on the resultset
Compatible with DokuWiki
No compatibility info given!
Search and install the plugin using the Extension Manager. Refer to Plugins on how to install plugins manually.
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.
IMPORTANT UPDATE NOTES
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"
What can this little extension do:
[[connection|sql|options]]
The syntax consist of pipe-delimited (|
) elements:
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]]
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 ]]
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) |
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. |
dbaliases
<lang>/lang.php
mysql_*
calls with mysqli_*
, which where PHP7 safe.show_diffs
- select if table-changes should be visualized or not.[[ ... ]]
is reserved for links. Change to something like <sqlcomp OPTION...> SQL-QUERY </sqlcomp>
.$conf['sql_locale']
mysqli_*
calls with PDO-MethodsPlease put your comments here discussion or better, create an issue at Github.