Extension:SQL2Wiki

From MediaWiki.org

Manual on MediaWiki Extensions
List of MediaWiki Extensions

SQL2Wiki 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
check usage (experimental)

[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 of DB handler, select the database
    #
    # 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");
 
    # Collecting parameters (shamelessly stolen from "Dynamic Article" 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>";
 
    # Tidying
    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