Extension:DataInvoker

From MediaWiki.org
Jump to: navigation, search
MediaWiki extensions manual - list
Crystal Clear action run.png
DataInvoker

Release status: experimental
Implementation Parser function, Data extraction
Description Allows retrieving data from a database
Author(s) Xudong Yang (WyvernoidTalk)
Last version 0.3b (Jul 30 2008)
MediaWiki 1.12 (older/newer versions not tested, but should work)
License See Code section below
Download See Code section below
Example DotA Allstars Wiki
Parameters

See below
Hooks used
LanguageGetMagic

Check usage (experimental)

DataInvoker is an extension that introduces new parser functions which allow retrieving data from a database.

Contents

[edit] Installation

Please cut and paste the code found below and place it in $IP/extensions/DataInvoker/DataInvoker.php. Note: $IP stands for the root directory of your MediaWiki installation, the same directory that holds LocalSettings.php.

To install this extension, add the following to LocalSettings.php:

require_once("$IP/extensions/DataInvoker/DataInvoker.php");

[edit] Configuration parameters

Note: These parameters should be put in LocalSettings.php.

There are currently 4 configuration parameters for this extension. They are:

  • $wgDataInvokerHostName - String that indicates the host name of the database connection. Default value is localhost.
  • $wgDataInvokerUserName - String that specifies the username of the database connection. Default value is dbuser.
  • $wgDataInvokerPassword - String that specifies the password of the database connection. Default value is dbpass.
  • $wgDataInvokerDatabase - String that specifies the name of the database being connected to. Default value is dbname.

[edit] Reference

DataInvoker currently introduces 4 new parser functions allowing data retrieving.

[edit] Usage

DataInvoker retrieves data by doing a query to the database and caching the result temporarily, then reading data from the cache and outputing them. doquery and rawquery do the database query, retrieve a row, and store the result into a temporary cache with a handle. Then getdata can be used to read data from the cached results by a key (column name) and a certain handle. When necessary, use escapestr to escape a string for safe SQL execution with rawquery.

[edit] #doquery:

Does a query to the database and stores the result in a temporary cache.

{{ #doquery: table_name | column_name | match_column | match_value | order_by | order | row_number | handle_name }}

Other than the first parameter (table_name), all parameters are optional.

table_name
The name(s) of the table to retrieve data from. If there are multiple tables, use commas (,) to separate them - although retrieving data from multiple tables isn't fully tested yet and might not work.
column_name
The name(s) of the columns from which the fields are wanted in the result output. If there are multiple columns, use commas (,) to separate them. If all columns of a table is wanted, use an asterisk (*). Default value is *.
match_column and match_value
Indicates that the match_column column of the retrieved result rows must be match_value. If omitted (or empty), no matching pattern is required for the retrieved rows.
order_by
Sorts the retrieved rows by the order_by column(s). If there are multiple columns to sort by, use commas (,) to separate them - although sorting by multiple rows isn't fully tested yet and might not work. If omitted (or empty), no sorting is done to the retrieved rows.
order
Specifies the way the rows are ordered by order_by. Possible values are ASC (in ascending order), or DESC (in descending order). Default value is ASC.
row_number
When multiple rows are retrieved and they all match the matching pattern defined by match_column and match_value (if any), only the row_numberth row will be output. Default value is 1. Must be a positive integer.
handle_name
The name of the handle by which to store the result to the cache. This is a classifier of the result which can be referred to by #getdata: later. When this is omitted, the result is stored with the handle _unnamedHandleN, when the stored result is the Nth (counting from 0) without a specified handle name.

[edit] #rawquery:

Does a raw SQL query to the database and stores the result in a temporary cache.

{{ #rawquery: query_string | row_number | handle_name }}

The second and third parameter can be omitted.

query_string
The raw SQL query string. Must start with the characters SELECT, or select, or sEleCT, etc.
row_number
When multiple rows are retrieved, only the row_numberth row will be output. Default value is 1. Must be a positive integer.
handle_name
The name of the handle by which to store the result to the cache. This is a classifier of the result which can be referred to by #getdata: later. When this is omitted, the result is stored with the handle _unnamedHandleN, when the stored result is the Nth (counting from 0) retrieved result without a specified handle name.

[edit] #getdata:

Reads the data cache and outputs the value.

{{ #getdata: column_name | handle_name | suppress_error }}
column_name
Required. The name of the column to retrieve data from.
handle_name
Optional. The handle of the cached result. When omitted, it is defaulted to the last stored result.
suppress_error
Optional, Boolean. Whether to suppress the errors in case one occurs. Default value is false.

[edit] #escapestr:

Escapes the given string by adding slashes (\) in front of apostrophes ('). This can be useful when used in conjunction with #rawquery: (#doquery: automatically escapes the strings).

{{ #escapestr: string }}
string
Required. The string to escape.

[edit] About

[edit] Acknowledgments

Thanks go to those guys at the #mediawiki IRC channel who helped me with this. They include Werdna, MinuteElectron, Duesentrieb, and Dantman. Thanks again!

[edit] Under Development

This extension is still under development; it might be a little unstable since it hasn't undergone strict testing, and it is still too simple at the current stage. It lacks more functionality and I'm aware of the situation; you are welcome to give suggestions in the talk page.

[edit] Trivia

Invoker is a hero in the game DotA Allstars, who has the game's most unique skillset and requires a lot of skills to play.

[edit] Changelog

  • v0.3b
    • New parameter for getdata suppress_error
    • New exception class DIError
  • v0.3a Added a new parser function escapestr
  • v0.3
    • New parser functions doquery and rawquery
    • Changed the way retrieving data works; now getdata no longer directly does a query, but retrieve data from the cached query results from either doquery or rawquery.

[edit] Code

<?php
/*
 Permission is hereby granted, free of charge, to any person
 obtaining a copy of this software and associated documentation
 files (the "Software"), to deal in the Software without
 restriction, including without limitation the rights to use,
 copy, modify, merge, publish, distribute, sublicense, and/or sell
 copies of the Software, and to permit persons to whom the
 Software is furnished to do so, subject to the following
 conditions:
 
 The above copyright notice and this permission notice shall be
 included in all copies or substantial portions of the Software.
 
 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
 HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 OTHER DEALINGS IN THE SOFTWARE.
 
*/
 
$wgExtensionCredits['parserhook'][] = array(
'name'         => 'DataInvoker',
'version'      => '0.3b',
'description'  => 'Allows retrieving data from a database',
'author'       => 'Xudong Yang',
'url'          => 'http://www.mediawiki.org/wiki/Extension:DataInvoker',
);
 
$wgExtensionFunctions[] = 'wfDataInvoker';
 
$wgHooks['LanguageGetMagic'][] = 'wfDataInvokerLanguageGetMagic';
 
function wfDataInvoker ( ) {
    global $wgParser, $wgExtDataInvoker;
    global $wgDataInvokerHostName, $wgDataInvokerUserName,
        $wgDataInvokerPassword, $wgDataInvokerDatabase;
 
    $wgExtDataInvoker = new ExtDataInvoker ( );
    if(!$wgDataInvokerHostName) $wgDataInvokerHostName = 'localhost';
    if(!$wgDataInvokerUserName) $wgDataInvokerUserName = 'dbuser';
    if(!$wgDataInvokerPassword) $wgDataInvokerPassword = 'dbpass';
    if(!$wgDataInvokerDatabase) $wgDataInvokerDatabase = 'dbname';
 
    $wgParser->setFunctionHook('doquery', array(&$wgExtDataInvoker,'doQuery'));
    $wgParser->setFunctionHook('rawquery', array(&$wgExtDataInvoker,'rawQuery'));
    $wgParser->setFunctionHook('getdata', array(&$wgExtDataInvoker,'getData'));
    $wgParser->setFunctionHook('escapestr', array(&$wgExtDataInvoker,'escapeStr'));
 
    $wgParser->mDataInvokerUnnamedHandleCount = 0;
}
 
function wfDataInvokerLanguageGetMagic( &$magicWords, $langCode = "en" ) {
    switch ( $langCode ) {
        default:
            $magicWords['doquery'] = array ( 0, 'doquery' );
            $magicWords['rawquery'] = array ( 0, 'rawquery' );
            $magicWords['getdata'] = array ( 0, 'getdata' );
            $magicWords['escapestr'] = array ( 0, 'escapestr' );
    }
    return true;
}
 
class DIError extends Exception {
    public function __construct($msg){
        $this->message = '<strong class="error">DataInvoker Error: ' . $msg . '</strong>';
    }
}
 
class ExtDataInvoker {
    var $DBConn;
 
    /******** PRIVATE FUNCTIONS ********/
 
    function initConnection() {
        global $wgDataInvokerHostName, $wgDataInvokerUserName,
            $wgDataInvokerPassword, $wgDataInvokerDatabase;
        $this->DBConn = Database::newFromParams($wgDataInvokerHostName, $wgDataInvokerUserName, $wgDataInvokerPassword, $wgDataInvokerDatabase);
    }
 
    function tableName($name) {
        if ( $name{0} != '`' ) {
            # Standard quoting
            $name = "`$name`";
        }
        return $name;
    }
 
    function replode(&$args, $funcname, $glue=',', $delimiter=',') {
        $arglist = explode($delimiter, $args);
        foreach ( $arglist as &$arg )
            $arg = call_user_func($funcname, $arg);
        unset($arg);
        $args = implode($glue, $arglist);
    }
 
    function fetchRetRow ($queryresult, $rownum) {
        if($rownum < 1) throw new DIError('ROWNUM cannot be non-positive');
        for($i = 0; ($i < $rownum) && ($queryrow = $this->DBConn->fetchRow($queryresult)); $i++);     // Fetch the $rownum th row
        // Only keep string-indexed keys
        foreach( $queryrow as $key => $value )
            if(is_int($key))
                unset($queryrow[$key]);
        return $queryrow;
    }
 
    function safeSQL($string){
        if(get_magic_quotes_gpc()) {
            $string = stripslashes($string);
        }
        $badWords = "(delete)|(update)|(union)|(insert)|(drop)|(http)|(--)";
        $string = eregi_replace($badWords, "", $string);
        //$string = mysql_real_escape_string($string);
        return $string;
    }
 
    function cacheResult (&$parser, $querystr, &$handlename, $rownum) {
        $queryresult = $this->DBConn->query($querystr, 'ExtDataInvoker::cacheResult');
        if(!$handlename) {
            $handlename = "_unnamedHandle".($parser->mDataInvokerUnnamedHandleCount++);
        }
        elseif(substr($handlename,0,1)=="_") {
            throw new DIError('HANDLE_NAME cannot begin with underscore');
        }
        $parser->mDataInvokerDataCache[$handlename] = $this->fetchRetRow($queryresult, $rownum);
        $parser->mDataInvokerLastHandleName = $handlename;
        $this->DBConn->freeResult( $queryresult );
    }
 
    /******** PARSER FUNCTIONS ********/
 
    function doQuery (&$parser, $table='', $column='*', $matchcolumn='', $matchvalue='', $orderby='', $order='ASC', $rownum = 1, $handlename='', $debug=false) {
        try {
            $this->initConnection();
 
            // Parse the parameters
            $this->replode($table, '$this->tableName');
            # if($column != '*') $this->replode($column, '$this->DBConn->addQuotes');
            if(!$column) $column = '*';
            $matchcolumn = mysql_real_escape_string($matchcolumn);
            $matchvalue = mysql_real_escape_string($matchvalue);
            # $this->replode($orderby, '$this->DBConn->addQuotes');
            if($order != 'ASC') $order = 'DESC';
 
            // Form the query string
            $querystr = "SELECT $column FROM $table";
            if($matchcolumn && $matchvalue) $querystr .= " WHERE $matchcolumn = '$matchvalue'";
            if($orderby) $querystr .= " ORDER BY $orderby $order";
 
            // Retrieve and cache the data
            $this->cacheResult($parser, $querystr, $handlename, $rownum);
            $ret = '';
            if($debug) $ret.="doQuery: $querystr ; handleName: $handlename";
        }
        catch( DBError $e ) {
            return "<strong class='error'>".$e->getMessage()."</strong>";
        }
        catch( DIError $e ) {
            return $e->getMessage();
        }
        return $ret;
    }
 
    function rawQuery (&$parser, $querystr='', $rownum = 1, $handlename='', $debug=false) {
        try {
            if(strtoupper(substr($querystr, 0, 6)) != 'SELECT')
                throw new DIError('Raw query must begin with SELECT');
            $this->initConnection();
            $querystr = $this->safeSQL($querystr);
 
            // Retrieve and cache the data
            $this->cacheResult($parser, $querystr, $handlename, $rownum);
            $ret = '';
            if($debug) $ret.="rawQuery: $querystr; handleName: $handlename";
        }
        catch( DBError $e ) {
            return "<strong class='error'>".$e->getMessage()."</strong>";
        }
        catch( DIError $e ) {
            return $e->getMessage();
        }
        return $ret;
    }
 
    function getData (&$parser, $key='', $handlename='', $suppresserror=false) {
        try {
            if(!$handlename) $handlename = $parser->mDataInvokerLastHandleName;
            if(!isset($parser->mDataInvokerDataCache[$handlename]))
                throw new DIError ("getData error: No such handle found ($handlename)");
            if(!isset($parser->mDataInvokerDataCache[$handlename][$key]))
                throw new DIError ("getData error: No such key in handle ($key)");
            $ret = $parser->mDataInvokerDataCache[$handlename][$key];
        }
        catch ( DIError $e ) {
            if($suppresserror) return '';
            else return $e->getMessage();
        }
        return $ret;
    }
 
    function escapeStr (&$parser, $str='') {
        try {
            return mysql_real_escape_string($str);
        }
        catch (Exception $e) {
            return $str;
        }
    }
}

[edit] See also

Retrieved from "http://www.mediawiki.org/w/index.php?title=Extension:DataInvoker&oldid=335662"
Personal tools
Namespaces
Variants
Actions
Site
Support
Download
Development
Communication
Print/export
Toolbox