Extension:SQL2Wiki

What can this extension do?
This extension can add some dynamic content to your wiki as a table. The content is taken from a MySQL-Database. Just put your SQL-Statement into the -tags and the output will be rendered as a table. This can be quite useful in some cases.

Usage

 * Show the latest 10 Tickets from your Ticket-System

SELECT * from Tickets WHERE Tickets.Owner='Someone' DESC limit 10;


 * Show all traps from your SNMP alarm-database

SELECT * from traps;

Installation
Easy: Copy SQL2Wiki.php to your extension folder.

Caching could be a problem - be sure to disable caching for this extension.

Parameters
For example, you have a MySQL-Database named "rt3" which is used as a backend for your ticketsystem ("Request Tracker" in our case). Parameters define, which database should be used. Handlers will be definend in SQL2Wiki.php like this:

$aDB_handles = array( "rt3"  => "localhost;rt3_user;rt3_password",                       "trap"  => "nagios;nagios_user;nagios_pass");
 * 1) Hostname;Username;Password

'''Consider security ! These users must only have select permissions on the tables !'''

GRANT ALL ON yourDB.* to 'wikisqlext'@'wikihost' GRANT SELECT ON yourDB.* to 'wikisqlext'@'wikihost'
 * Don't do
 * Instead do

Changes to LocalSettings.php
include("extensions/SQL2Wiki.php" );

Code
This code is far away from perfection - it works for me. Feel free to send updates or patches to "patrick dot mueller at itc dot fh-wiesbaden dot de".

setHook( "sql2wiki", "renderSQL" ); }

function renderSQL( $input, $argv, &$parser ) {

$db = $argv["database"];

# Definition der DB-Handler, Auswahl der Datenbank #   # grant select on rt3.* to 'rt3_user' identified by "rt3_password"; #   $aDB_handles = array( "rt3"   => "localhost;rt3_user;rt3_password",                          "trap"  => "nagios;nagios_user;nagios_pass");

# Parameter zusammenkratzen, schamlos gestohlen von "DynamicArticle"-Extension while(list($index,$val)=each($aDB_handles)) { if ( $db == $index ) { $aParams = explode(";", $val);

foreach($aParams as $parameter) { if( count( $aParams ) < 3 ) return "Error in DB_handler definition !"; $host = trim($aParams[0]); $user = trim($aParams[1]); $pass = trim($aParams[2]); }     }    }

$output = ""; #    $link = mysql_connect($host, $user, $pass); if ( !$link ) { return " Error while connecting to host \"$host\" ! "; }

mysql_select_db($db, $link);

$result = mysql_query ($input);

# Create Table Header $output .= " ";

# Aufraeumen mysql_free_result($result); mysql_close($link);

return $output; } ?>

Appearance of the display
To achieve a table display of your flavour change the creation of table header to   # Create Table Header $output .= " "; and add the following lines (modify to your need) to the main.css file (located in the directory of your selected skin) table.myflavour { background-color: #a9a9a9; border: solid 0px #999999; border-collapse: collapse; margin: 5px; padding: 0px; }

table.myflavour th { vertical-align: top; background-color: #d9d9f9; border: solid 1px #999999; padding: 8px; margin: 1px; }

table.myflavour td { vertical-align: top; background-color: #f9f9f9; border: solid 1px #999999; padding: 8px; margin: 1px; }