This is my first DokuWiki extension. I created it for personal usage, but changed it a little bit and decided to contribute it to the public. I know that it is far away from perfect, but for my (our) problem it's doing its job quite well. Perhaps I will update it if I have some spare time, and if anybody is interested in updates. As you might have noticed, English is not my native language, so please excuse any typing mistakes, I'm doing my best

• 2008-08-07: Initial Release.

In the company where I work we have some tables, or data within a table, which is often wrong due to wrong csv imports which the customer delivers or something like that. Mostly this data can be identified by simple SQL-Selects in combination with Where-Statements.

As we run a DokuWiki here to have all information from a project in one space I decided to code this little extension.

What can this little extension do:

• Execute SQL-Statements and show Resultset
• If no Resultset is given (e.g. Update, Insert, Delete) it will show the number of affected rows.
• Works with many database types (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 (hostname/user/pass/etc.) in a separate file. (eg. localhost will be replaced with mysql:localhost:root:password:databasename
• Admin interface for clearing cache and manage database servers. (80% finished)

[[Type:Server:Username:Password:Database|Query|Refresh]]

So we have 3 Parameters with a Pipe between them.

Valid Parameters are:

refresh = Time in minutes. This parameter is not mandatory. It will give the interval in minutes how long a change on the resultset is marked as changed.

It is also possible to have the connection info in a separate file. Firstly for security reasons if you don't want the wikiusers to see the server connect parameters like username and password. Second for maintenance, if any of the server settings is changing you have to change all pages where you use a resultset… I will write more detailed information about that if anyone is interested.

[[mysql:localhost:root::information_schema|Select TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS From Tables where TABLE_ROWS  >= 0|5000]]

The output could look like this:

Create a folder named sqlcomp in your plugins directory. Create a file called syntax.php in that directory.

Copy/Paste the script into that file. That's all.

<?php
/**
 *
 * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
 * @author     Christoph Lang <calbity@gmx.de>
 */
 
// based on http://www.dokuwiki.org/plugin:tutorial
 
/**
 *
 * Usage:
 * [[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]]
 */
 
/**
 * Disabled, to use that class in standalone mode...
 * // must be run within DokuWiki
 */
 
if (!defined('DOKU_INC')) die();
if (!defined('DOKU_PLUGIN')) define('DOKU_PLUGIN', DOKU_INC . 'lib/plugins/');
require_once(DOKU_PLUGIN . 'syntax.php');
 
 
/**
 * All DokuWiki plugins to extend the parser/rendering mechanism
 * need to inherit from this class
 */
class syntax_plugin_sqlcomp extends DokuWiki_Syntax_Plugin {
 
    private $sPath = "data/cache/sql/";
    private $sConfig = "lib/plugins/sqlcomp/config.php";
 
    /*
    Layout
    */
    private $aMessages = array(
                              "error" => "<div id=\"error\" style=\"text-align:center; font-weight: bold; border: 2px solid #0f0;background-color: #f00; padding: 5px; margin: 5px\">%text%</div>\n",
                              "message" => "<div id=\"difference\" style=\"text-align:center; font-weight: bold; border: 2px solid #fd0;background-color: #ffd; padding: 5px; margin: 5px\">%text%</div>\n",
                              "pre" => "<table class=\"inline\">\n",
                              "post" => "</table>\n",
                              "th" => "<th class=\"row%number%\" style=\"%type%\">%text%</th>",
                              "td" => "<td class=\"col%number%\" style=\"%type%\">%text%</td>",
                              "tr" => "<tr class=\"row%number%\" style=\"%type%\">%text%</tr>\n",
                              "same" => "",
                              "new" => "border:2px solid green;",
                              "deleted" => "border:2px solid red;",
                              "changed" => "border:2px solid blue;"
                              );
    /*
    Default Language - German
    */
    private $aString = array(
                            //Number of affected Rows
                            "affected" => "Anzahl geänderter Zeilen",
                            //This Database Type is not yet Supported...
                            "nohandler" => "Dieser Datenbanktyp wird (noch) nicht unterstützt...",
                            //There are some differences in the table!
                            "difference" => "Es wurden Unterschiede in den Tabellen festgestellt!",
                            //Everything is allright.
                            "same" => "Alles in Ordnung.",
                            //The resultset is empty.
                            "empty" => "Das Resultset ist leer.",
                            //An unkown error occured!
                            "problem" => "Es ist ein unbekanntes Problem aufgetreten!",
                            //Cache is displayed, but new data could not be retrieved.
                            "cache" => "Cache wird angezeigt, aber neue Daten konnten nicht abgerufen werden.",
                            //Cache was refreshed, or table was collected for the first time.
                            "first" => "Der Cache wurde soeben erneuert, oder die Tabelle wurde das erste Mal abgerufen.",
                            //New data could not be retrieved.
                            "connection" => "Die neuesten Daten konnten nicht abgerufen werden.",
                            //The data is not valid. Please review your connection settings!
                            "wrong" => "Die eingegebenen Daten sind ungültig! Bitte Überprüfen!"
                            );
 
    private $defaultRefresh = 1;
 
    function getInfo() {
        return array(
        'author'  => 'Christoph Lang',
        'email'   => 'calbity@gmx.de',
        'date'    => '2008-07-10',
        'name'    => 'SQLCOMP Plugin',
        'desc'    => 'This plugin let you display reultsets from various databases and show changes.',
        'url'     => 'http://www.google.de'
        );
    }
 
    public function query($query){
      $temp = null;
      $data = $this->handle($query,"","",$temp);
      return $this->_query($data,"csv");       
    }
 
    public function __construct(){      
    }
 
    private function _error($text){
      return str_replace("%text%",$text,$this->aMessages["error"]);      
    }
 
    private function _message($text){
      return str_replace("%text%",$text,$this->aMessages["message"]);
 
    }
 
 
 
    private function _sqlaccess($Server,$User,$Pass,$Database,$Query){
 
        if(!$connection = odbc_connect("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$Database", "ADODB.Connection", $Pass, "SQL_CUR_USE_ODBC") or false)
          throw new Exception($this->aString["problem"]);
 
        $rs = odbc_exec($connection,$Query);
 
        $dbArray = array();
        while ($row = odbc_fetch_array($rs))
          $dbArray[] = $row;
 
        odbc_close($connection);
        return $dbArray;
 
    }
    private function _postgresql($Server,$User,$Pass,$Database,$Query){
 
        if(!$connection = pg_connect("host=".$Server." dbname=".$Database." user=".$User." password=".$Pass) or false)
          throw new Exception($this->aString["problem"]);
 
        $rs = pg_exec($Query);
        $dbArray = pg_fetch_array($result, NULL, PGSQL_ASSOC);
 
        pg_close($connection);
        return $dbArray;
 
    }
 
    private function _mysql($Server,$User,$Pass,$Database,$Query){
 
        if(!$connection = mysql_connect($Server, $User, $Pass) or false)
          throw new Exception(mysql_error());
 
        mysql_select_db($Database, $connection);
        $rs = mysql_query($Query);
        $dbArray = array();
 
        if($rs === true)
          $dbArray[] = array( $this->aString["affected"] => mysql_affected_rows ($connection));
        else
          while ($row = mysql_fetch_assoc($rs))
            $dbArray[] = $row;
 
 
        mysql_close($connection);
        return $dbArray;
 
    }
    private function _mssql($Server,$User,$Pass,$Database,$Query){
 
        if(!$dbhandle = mssql_connect($Server, $User, $Pass))
          throw new Exception($this->aString["problem"]);
 
        mssql_select_db($Database, $dbhandle);
 
        $rs = mssql_query($Query);
 
        $dbArray = array();
 
        if($rs === true)
          $dbArray[] = array( $this->aString["affected"] => mssql_rows_affected ($connection));
        else
          while ($row = mssql_fetch_assoc($rs))
            $dbArray[] = $row;
 
        mssql_close($dbhandle);
        return $dbArray;
 
    }
 
    private function _oracle($Server,$User,$Pass,$Database,$Query){
          throw new Exception($this->aString["nohandler"]);      
    }
 
    private function _sqlcsv($Server,$User,$Pass,$Database,$Query){  
 
        if(!$handle = fopen($Database,"r"))
          throw new Exception($this->aString["nohandler"]);    
 
        $dbArray = array();
        $keys = fgetcsv ( $handle , 1000, $Query);
 
        while ($row = fgetcsv ( $handle , 1000, $Query)){
          $temprow = array();
          foreach($row as $key => $value)
            $temprow[$keys[$key]] = $value;
 
          $dbArray[] = $temprow;
 
        }
 
        fclose($handle);
        return $dbArray;
 
    }
 
    private function _sqlite($Server,$User,$Pass,$Database,$Query){
 
        $dbHandle = new PDO('sqlite:'.$Database);    
 
        $result = $dbHandle->query($Query);
        if(!$result)
          throw new PDOException;
        $dbArray = array();
 
        if($result->rowCount() > 0)
          $dbArray[] = array( $this->aString["affected"] => $result->rowCount() );
        else
          while($row = $result->fetch(PDO::FETCH_ASSOC))
            $dbArray[] = $row;
 
        return $dbArray;
 
    }
    private function _debug($data){
 
        $sResponse = "";
        foreach($data as $key => $value)
            $sResponse .= "".$key . "=> " .$value ."<br/>\n";            
        return $sResponse;
 
    }
 
    private function _verifyInput($data){
      if(!is_array($data))
        return false;
      if(count($data) != 7)
        return false;
      return true;    
    }
 
    private function _load($filename){
 
        $Cache = null;
        $Update = true;
        if(file_exists($filename)){
          $Cache = file_get_contents($filename);  
          $Cache = unserialize($Cache);
 
          $Update = $Cache["Update"];
          if(time() > $Update)
            $Update = true;
          else
            $Update = false;
          $Cache = $Cache["Table"];    
        }        
 
        return array($Update,$Cache);
    }
 
    private function _save($filename,$rs,$timestamp){
        $timestamp = (time() + ($timestamp*60));
        $Cache["Update"] = $timestamp;
        $Cache["Table"] = $rs;
 
        $Cache = serialize($Cache);
 
        $handle = fopen($filename,"w");
        fwrite($handle,$Cache);
        fclose($handle);
 
 
    }
    private function array2csv($data){
 
      $sResponse = "";
 
      $keys = array_keys($data[0]);
      $sResponse .= implode(";",$keys)."\n";
      foreach($data as $row)
        $sResponse .= implode(";",$row)."\n";
 
 
      return $sResponse;
 
    }
 
 
    private function _query($data,$type=null) {      
 
        //return $this->_debug($data);
 
        if(!$this->_verifyInput($data))
          return $this->_error($this->aString["wrong"]);
 
        if(!is_dir($this->sPath))
          mkdir($this->sPath);
 
        $filename = $this->sPath.md5($data[0].$data[1].$data[2].$data[3].$data[4].$data[5]);
 
        $Cache = $this->_load($filename);
        $Update = true;
        if(is_array($Cache)){
          $Update = $Cache[0];
          $Cache = $Cache[1];
        }
 
        try{  
          switch($data[0]){
            case "mysql": $rs = $this->_mysql($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            case "mssql": $rs = $this->_mssql($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            case "oracle": $rs = $this->_oracle($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            case "sqlite": $rs = $this->_sqlite($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            case "sqlaccess": $rs = $this->_sqlaccess($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            case "postgresql": $rs = $this->_postgresql($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            case "sqlcsv": $rs = $this->_sqlcsv($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            default: return $this->_error($this->aString["nohandler"]);
          }
        }catch(Exception $ex){
          $sResponse = $this->_error($this->aString["problem"]);
          if(isset($Cache)){
            $sResponse = $this->_print($Cache);    
            $sResponse .= $this->_error($this->aString["cache"]);
          }
          return $sResponse;
        }    
 
        if ($rs === false){
          return $this->_error($this->aString["empty"] );
        }
 
        if(isset($type) && $type == "csv")
          return $this->array2csv($rs);
 
        $difference = $this->_difference($Cache,$rs);
        $sResponse = $difference[0];    
 
        if($Update && isset($rs)){
          $this->_save($filename,$rs,$data[6]);      
 
        }  
 
        $sResponse .= $difference[1];      
 
 
        return $sResponse;
    }
    function _print($array){
 
        $i = 0;
 
        $th = "";
        $td = "";
        $tr = "";
        if(!isset($array[0]))
          return $this->_error($this->aString["problem"]);
 
        $temp = array_keys($array[0]);
        foreach($temp as $column){
          if($column == "type")
            continue;  
          $th .= str_replace(array("%number%","%text%","%type%"),array(0,$column,""),$this->aMessages["th"]);      
        }
        $tr = str_replace(array("%number%","%text%","%type%"),array(0,$th,""),$this->aMessages["tr"]);
 
        foreach($array as $row) {
 
          $j = 0;
          $td = "";
          if(!isset($row["type"]))
            $row["type"] = $this->aMessages["same"];
 
          foreach($row as $key => $Value){          
            if($key == "type")
              continue;  
            $td .= str_replace(array("%number%","%text%","%type%"),array($j,$Value,$row["type"]),$this->aMessages["td"]);
            $j++;            
          }
          $tr .= str_replace(array("%number%","%text%","%type%"),array($i,$td,$row["type"]),$this->aMessages["tr"]);
          $i++;          
        }
 
        $sResponse = $this->aMessages["pre"];
        $sResponse .= $tr;        
        $sResponse .= $this->aMessages["post"];
 
        return $sResponse;
 
 
    }
 
 
    function _difference($Cache,$New){
 
            if($New == $Cache){
              return array($this->_print($New),"");
              return array($this->_print($New),$this->_message($this->aString["same"]));
            }
 
            if(!isset($New) && isset($Cache))
              return array($this->_print($Cache),$this->_message($this->aString["difference"]));
 
            if(isset($New) && !isset($Cache))
              return array($this->_print($New),$this->_message($this->aString["first"]));
 
            if(count($New) <= 0)
              return array($this->_print($Cache),$this->_message($this->aString["connection"]));
 
            $Max = count($Cache);
            if(count($New) > count($Cache))
              $Max = count($New);
 
            $PrintArray = array();        
 
            for($i=0; $i < $Max; $i++){
              if(isset($Cache[$i]) && !isset($New[$i]))
                $PrintArray[] = array_merge($Cache[$i],array("type" => $this->aMessages["deleted"]));
 
              if(!isset($Cache[$i]) && isset($New[$i]))
                $PrintArray[] = array_merge($New[$i],array("type" => $this->aMessages["new"]));
 
              if(isset($Cache[$i]) && isset($New[$i])){
                if($Cache[$i] != $New[$i]){
                  $PrintArray[] = array_merge($Cache[$i],array("type" => $this->aMessages["changed"]));
                  $PrintArray[] = array_merge($New[$i],array("type" => $this->aMessages["changed"]));
                }else
                  $PrintArray[] = array_merge($New[$i],array("type" => $this->aMessages["same"]));
 
              }                
 
            }
 
            return array($this->_print($PrintArray),$this->_message($this->aString["difference"]));
 
    }
 
    function connectTo($mode) {
        $this->Lexer->addSpecialPattern('\[\[mysql\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[mssql\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[oracle\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[sqlite\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[sqlaccess\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[postgresql\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[sqlcsv\:.*?\]\]', $mode, 'plugin_sqlcomp');
 
        if(!file_exists($this->sConfig))
          $this->_createConfig();  
 
        include($this->sConfig);
 
        foreach($sqlcomp as $key => $value)
          $this->Lexer->addSpecialPattern('\[\['.$key.'.*?\]\]', $mode, 'plugin_sqlcomp');
 
 
    }
    function _createConfig(){  
 
        $sContent = "";
        $sContent .= "<?php\n";
        $sContent .= "//Sample Configfile\n";
        $sContent .= "//Add as many servers as you want here...\n";
        $sContent .= '$sqlcomp["localhost"] = "mysql:localhost:root::information_schema";';
        $sContent .= '$sqlcomp["sampleconnection"] = "sqltype:servername:username:password:database";';
        $sContent .= "\n?>\n";
 
        $handle = fopen($this->sConfig,"w");
        fwrite($handle,$sContent);
        fclose($handle);
 
    }
 
    function getType() { return 'substition'; }
 
    function getSort() { return 267; }
 
    function handle($match, $state, $pos, &$handler) {
 
        $temp = $match;
        $match = substr($match,2,-2);
 
        $MyData = array();       
        $match = explode("|",$match);   
 
        if(file_exists($this->sConfig))
          include($this->sConfig);
 
        foreach($sqlcomp as $key => $value)
          if($key == $match[0])
            $match[0] = $value;
 
        $MyData =  explode(":",$match[0]);
        $MyData[] = $match[1];    
 
        if(isset($match[2]))
          $MyData[] = $match[2];    
        else
          $MyData[] = $this->defaultRefresh;  
 
        for($i=0;$i < 5; $i++)
          $MyData[$i] = str_replace(" ", ":",$MyData[$i]);
 
        return $MyData;
 
    }
 
    function render($mode, &$renderer, $data) {
 
        if ($mode == 'xhtml') {          
            $renderer->doc .= $this->_query($data);               
            return true;
        }
        return false;
    }
}

[[sqlaccess:ee:ee:mdp:"C:\\bd1.mdb"|SELECT * FROM T_DOSSIER_MAIN|5000]]

The Problem is i use 2 seperators for the parameters. | for grouping parameters|sql|refresh time… and : for selecting the different parameters like sqltype,username,….
So the : is already used and cannot be used within an parameter.
I faced the same problem too, and did a quick and dirty solution that worked at least for me, try:

[[sqlaccess:ee:ee:mdp:C \bd1.mdb|SELECT * FROM T_DOSSIER_MAIN|5000]]

This should work, as the space between C and \ will (normally) be filled with a :.

Try that and let me know if that worked. Btw: “Die eingegebenen Daten sind ungültig! Bitte überprüfen!” means “The data you entered is wrong because of wrong syntax/parameters. Please check them again!”

This works really well !!

For example if you get some strange characters in the result like: Clotur�es �tique

Please check the following. Is the syntax.php file from sqlcomp encoded in UTF-8? If not, try to save it in UTF-8 format. If so, UTF-8 strings should be handled correctly. If it still does not work, try replacing:

$renderer->doc .= $this->_query($data);    

with:

$renderer->doc .= utf8_encode($this->_query($data));    

Hope that fixes your problem

The trick you expose fixes this problem really well! I just have to say thank you for this great plugin!

Hi, Nice plugin. I've added a security note and a php5 dependency to your information at the top of this page. Also, you might want to check out the plugin localization functions to move your strings out of the plugin itself. I didn't see how your refresh system worked, however you might want to take a look at the way DokuWiki handles RSS feeds (see, inc/parser/metadata.php – handles “metadata” in renderer and sets date|valid|age key). — Christopher Smith 2008/11/19 02:43

Hi, nice plugin. I was wondering if the config.php file is actually used by it. It would be nice not to have to post the entire connection data, every time a query is made. But I did not find out how to the use the plugin without it.

Edit: Sorry, now I found you wrote that it is possible, can you just give an example? Thanks

If you already have the extension running in your dokuwiki, just edit the file /lib/plugins/sqlcomp/config.php

This is the default content of the config.php file:

<?php
//Sample Configfile
//Add as many servers as you want here...
$sqlcomp["sampleconnection"] = "sqltype:servername:username:password:database";
?>

Open the file with a text editor and change the entry or add as many new entrys as you like. You must have access to the filesystem to edit this file, as for now no edit from the admin menu is possible. Maybe that will be integrated in a later version.

For example:

$sqlcomp["MyLocalMySQLServer"] = "mysql:localhost:root:secretpassword:cdcol";
$sqlcomp["microsoftServer"] = "mssql:127.0.0.1:root:secretpassword:database_with_some_stuff";

If you have finished editing your config.php file, you can start SQL-Querys from within dokuwiki with the following syntax:

[[MyLocalMySQLServer|SELECT titel FROM cds|5]]
or
[[microsoftServer|SELECT * FROM table_test|10]]

My extension will automatically expand the above syntax into the following syntax, which matches the syntax of the plugin:

[[mysql:localhost:root:secretpassword:cdcol|SELECT titel FROM cds|5]]
or
[[mssql:127.0.0.1:root:secretpassword:database_with_some_stuff|SELECT * FROM table_test|10]]

So, i hope that helps you. I know it's far from perfect, and if i sometime have enought free time i will make everything much more easier… but for now atleast it works.

As always: If you find any spelling mistakes, please correct them

Thanks! Works really fine