Extension:SQL2Wiki
From MediaWiki.org
 Release status: beta |
|
|---|---|
| Implementation | Tag |
| Description | Add dynamic content to your wiki by using SQL-Select statements (MySQL only). |
| Author(s) | Patrick Müller |
| Last Version | 0.3 (05.07.07) |
| MediaWiki | Works with 1.6.8, 1.9.2 |
| License | No license specified |
| Download | Create a new extension article |
Contents |
[edit] 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 sql2wiki-tags and the output will be rendered as a table. This can be quite useful in some cases.
[edit] Usage
- Show the latest 10 Tickets from your Ticket-System
<sql2wiki database="rt3"> SELECT * from Tickets WHERE Tickets.Owner='Someone' DESC limit 10; </sql2wiki>
- Show all traps from your SNMP alarm-database
<sql2wiki database="traps"> SELECT * from traps; </sql2wiki>
[edit] Installation
Easy: Copy SQL2Wiki.php to your extension folder.
Caching could be a problem - be sure to disable caching for this extension.
$wgEnableParserCache = false; in LocalSettings.php
[edit] 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:
# Hostname;Username;Password
$aDB_handles = array( "rt3" => "localhost;rt3_user;rt3_password",
"trap" => "nagios;nagios_user;nagios_pass");
Consider security ! These users must only have select permissions on the tables !
- Don't do
GRANT ALL ON yourDB.* to 'wikisqlext'@'wikihost'
- Instead do
GRANT SELECT ON yourDB.* to 'wikisqlext'@'wikihost'
[edit] Changes to LocalSettings.php
include("extensions/SQL2Wiki.php" );
$wgEnableParserCache = false;
[edit] 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".
<?php
$wgExtensionFunctions[] = "wfSQL2Wiki";
function wfSQL2Wiki() {
global $wgParser;
$wgParser->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 "<hr><b>Error while connecting to host \"$host\" !</b><hr>"; }
mysql_select_db($db, $link);
$result = mysql_query ($input);
# Create Table Header
$output .= "<table border=1>";
$output .= "<tr>";
$i = 0;
while ($i < mysql_num_fields($result)) {
$meta = mysql_fetch_field($result, $i);
$output .= "<th><b>$meta->name</b></th>";
$i++;
}
$output .= "</tr>";
# Create Table Data Rows
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$output .= "<tr>";
foreach ($line as $col_value){
$output .= "<td>$col_value</td>";
}
$output .= "</tr>";
}
# End of Table Tag
$output .= "</table>";
# Aufraeumen
mysql_free_result($result);
mysql_close($link);
return $output;
}
?>
[edit] Appearance of the display
To achieve a table display of your flavour change the creation of table header to
# Create Table Header
$output .= "<table class='myflavour'>";
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;
}
[edit] Interwiki Links
If your table contains code like this:
[[Manual:extensions|extensions]]
it will be interpreted as a link, e.g. extensions
Change:
# Create Table Data Rows
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$output .= "<tr>";
foreach ($line as $col_value){
$output .= "<td>$col_value</td>";
}
$output .= "</tr>";
}
to:
# Create Table Data Rows
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$output .= "<tr>";
foreach ($line as $col_value){
# Keep interwiki links, e.g. [[your_article]]
if (preg_match("/(.*)\[\[(.*)\]\](.*)/i", $col_value, $article)) {
$col_value = $article[1] . '<a href="/mediawiki/index.php/'
. $article[2] .'" title="' . $article[2] . '">'
. $article[2] . '</a>' . $article[3];
}
$output .= "<td>$col_value</td>";
}
$output .= "</tr>";
}
Edit /mediawiki/index.php/ to fit your needs !
[edit] Changelog
0.1 - Initial release - 05.03.07 0.2 - Added support for stylesheets (thanks to "unknown user") 0.3 - Support for Interwiki links - 05.07.07
