[pmwiki-devel] database tools and hacks in PmWiki

[marc]

Hi,

Using my preferred object oriented approach to db access in PmWiki 
recipes, I've started to build a toolkit of markup for db manipulation 
and reporting. In the hope that it might be of interest to the 
community, I thought I'd give a brief example, to see whether it might 
be of more general use and be something we could work on expanding 
together.

As per the PmWiki Database Standard, usage is underpinned by ADOdb 
(ADOdb lite, in my case) and $Databases[$dbName]. However, rather than 
the function ADOdbConnect() returning true or a text message (as with 
adodb-connect.php), my version either returns the db object or false. 
I've argued before why I prefer his method.

Anyway, in this example, the markup:

  (:databasehack dbase=localdb table=order_items
  field=productName value=DAE_150106:)

displays on a PmWiki page:

  Array (
    [id] => 419
    [transId] => 153114000
    [productName] => DAE_150106
    [active] => 0
    [itemAmount] => 18.12
    [itemCurrency] => EUR
    [itemVatRate] => 
  )

Not terribly useful, but it's okay as a demo.

Markup is defined in the usual way - in config.php or friends, or in a 
local/Group.Pagename.php:

  function databaseHack ($opt) {
    $defaults = array(
      'dbase'	=> 'localdb', 'table'	=> '',
      'field'	=> '', 'value'	=> '');
    $opt = array_merge($defaults, ParseArgs($opt));
    extract($opt);
    if (!$db = New dbHacks($dbase)) return "no db";
    if (!$rs = $db->getAll($table,$field,$value)) return "no rows";
    return $db->displayRS($rs);
  }

  Markup('databasehack', 'inline',
    '/\\(:databasehack\\s*(.*?):\\)/ei',
    "databasehack(PSS('$1'))");

Apart from the argument handling, the function simply creates a db 
object (and connects to the db), creates a resultset of the appropriate 
rows, and returns the formatted result set to PmWiki to display.

The class is:

  Class dbHacks {
    var $db;

    function dbHacks($dbase) {
      $this->db = ADOdbConnect($dbase);
        if (!$db) return false;
      return true;
    }
    function getAll($table,$field,$value) {
      $sql = "SELECT * FROM $table
              WHERE $field='$value'";
      $rs = $this->db->Execute($sql);
      if (!$rs || $rs->RecordCount() < 1) return false;
        return $rs;
    }
    static function displayRS ($rs) {
      $out = '%blue%';
      while (!$rs->EOF) {
        $out .= print_r($rs->fields,true);
        $rs->MoveNext();
      }
      return $out;
    }
  }

This example can easily be extended with a single function to change 
$value in all selected rows, for instance. A common task made simple.

What I like about this approach is that it is easily extensible. For 
example, to format the report in another way simply requires creating a 
new function based on displayRS - and you can, of course, use PmWiki 
markup for the formatting (as shown).

Much more general methods can be created. For example, getAll($table,
$field,$value) can be written to return the whole table if $field is 
absent, or a list of $field only when $value is absent.

Anyway, it is the principle that I wanted to throw out there.

-- 
Best,
Marc