<?php
/**
* PmWiki recipe to make records from any database supported by the ADOdb-connect recipe 
* act like wiki pages.
* copyleft 2006-10-03, 2006-12-13 Ben Stallings <Ben@InterdependentWeb.com> .
*/

/** CONFIGURATION
* You must have adodb-connect.php installed and configured.
* In addition, you may change the values of the configuration variables
* by defining them in your config.php like this:
$DQglobals['databases'] = array('Moodle','UserData');
$DQglobals['offlimits'] = array('secret_table','public_table.secret_field');
$DQglobals['errors'] = 'administrator@example.com';
$Database['connection_name']['readonly'] = 1;
* You can also change them here in the script of course, but then you'll lose
* your changes when you upgrade.
*/

SDVA($DQglobals, array(
 'databases' => array_keys ($Databases), //by default we use all named databases
 'offlimits' => array (), //tables and columns that this recipe should 
  // never display or edit
 ));

# End of variable definitions.

$DQgroup = FmtPageName('$Group',$pagename);
$DQname = FmtPageName('$Name',$pagename);

# Connect to Databases
include_once("$FarmD/cookbook/adodb-connect.php");
if (!is_array($DQglobals['databases'])) {
 DSerror('No databases specified in $DQglobals["databases"]');
} else {
 foreach ($DQglobals['databases'] as $DQdbName) {
  $DQmsg = ADOdbConnect($DQdbName);
  if ($DQmsg !== TRUE) DSerror($DQmsg);
  $DB[$DQdbName]->SetFetchMode(ADODB_FETCH_ASSOC); // use associative arrays by default
  if (in_array(strtolower($DQgroup),$DB[$DQdbName]->MetaTables()))
   //consult database records before wiki pages
   array_unshift($WikiLibDirs,new DataStore($DQdbName));
 } //end foreach
} //end if

class DataStore {
  var $dirfmt;
  var $iswrite;
  var $group;
  var $name;
  var $db;
  function DataStore($db) {
	  global $pagename,$Databases;
		$this->dirfmt = $Databases[$db]['driver'];
		$this->iswrite = !$Databases[$db]['readonly'];
		$this->group = strtolower(FmtPageName('$Group',$pagename));
		$this->name = strtolower(FmtPageName('$Name',$pagename));
		$this->db = $db;
	}
  function read($pagename, $since=0) {
	global $DB, $DQglobals, $DQcache, $Version, $action;
	$table = strtolower(FmtPageName('$Group',$pagename));
	$display = strtolower(FmtPageName('$Name',$pagename));
	$page['version'] = $Version;
	$db = $DB[$this->db];
	$page['text'] = "(:comment data:)\n\n";
	if (is_array($DQcache[$table][$display])) {
		//record is in cache from a previous query, most likely ls()
		$data = $DQcache[$table][$display];
	} else { // record is not in cache -- retrieve it
		list($primary) = $db->MetaPrimaryKeys($table);
		$sql = "SELECT * FROM $table WHERE $primary = $display";
		if (!$rs = $db->Execute($sql)) return;
		$data = $rs->FetchRow();
		$DQcache[$table][$display] = $data;
	}
	if (!is_array($data)) return $page;
	// handle page history
	foreach(explode("\n",$data['pm_history']) as $line) {
	 $pos = strpos($line,"=");
     $page[substr($line,0,$pos)] = substr($line,($pos+1));
    }
    unset($data['pm_history']);
    // handle other page fields (version, author, etc.)
    foreach(array('version','text','time','author') as $field) {
     SDV($page["$field"],$page["pm_$field"]);
     unset($data["pm_$field"]);
    }
    // render the remaining fields as page text variables
	$fore == ''; $aft == '';
	if ($action != 'search') {$fore = '(:'; $aft = ':)';}
	foreach ($data as $key => $value) {
		if (!in_array("$table.$key",$DQglobals['offlimits'])) 
		  $page['text'] .= "$fore$key: ".str_replace("\n","[[<<]]",$value)
		  . "$aft\n\n";
	}
	$page['text'] .= "(:include $table.Templates#browsetable:)";
	return $page;
  }
  function write($pagename,$page) {
    global $Now, $DB, $DQcache, $DQglobals,$PageTextVarPatterns;
    //Abort(print_r($page,true)); //uncomment for debugging
	$table = strtolower(FmtPageName('$Group',$pagename));
	$record = strtolower(FmtPageName('$Name',$pagename));
    $s = false;
    $db = $DB[$this->db];
    list($primary) = $db->MetaPrimaryKeys($table);
    $cols = $db->MetaColumnNames($table);
    // handle PmWiki page fields (diff, version, author, etc.)
    foreach($page as $key => $value)
     if (strpos($key,":") > 0) $history .= "$key=$value\n";
    $page['pm_history'] = $history;
    foreach(array('version','text','time','author') as $field) 
     $page["pm_$field"] = $page[$field];
    // parse PageTextVariables
    foreach((array)$PageTextVarPatterns as $pat) 
     if (preg_match_all($pat, $page['text'], $match, PREG_SET_ORDER))
      foreach($match as $m)  
       $page[$m[1]] = Qualify($pagename, $m[2]);
    $existing = $db->Execute("SELECT * FROM $table WHERE $primary = '$record'");
	$s = ($existing->RecordCount()
	 ? $db->AutoExecute($table,$page,'UPDATE',"$primary='$record'")
     : $db->AutoExecute($table,$page,'INSERT'));
    if (!$s) {
     DSerror($db->ErrorMsg());
     Abort("Cannot write page to $pagename on database \"".$db->database
     . "\"...changes not saved");
    }
    $DQcache[$table][$page[$primary]] = $page;
  }
  function exists($pagename) {
  	global $DB, $DQcache, $DQglobals;
	//print "Checking existence of $pagename\n"; //uncomment for debugging
	$db = $DB[$this->db];
    if (!$pagename) return false;
	$table = strtolower(FmtPageName('$Group',$pagename));
    if (in_array($table,$DQglobals['offlimits'])) return false;
	if (!in_array($table,$db->MetaTables())) return false;
	$display = FmtPageName('$Name',$pagename);
	if ($DQcache[$table][$display]) return true;
	list($primary) = $db->MetaPrimaryKeys($table);
	$sql = "SELECT * FROM $table WHERE $primary = $display";
	$rs = $db->Execute($sql);
	if (!$rs) return false;
	if (!$data = $rs->FetchRow()) return false;
	return true;
  }
  
  function ls($pats=NULL) {
    global $pagename, $DB, $DQcache, $action;
    $db = $DB[$this->db];
    if (($action=search) and ($pats==NULL)) $pats[]=" "
     . FmtPageName('$Group',$pagename).".* ";
    $out = array();
    foreach ($db->MetaTables() as $table) {
	  if (!in_array(" ".ucwords($table).".* ",$pats)) continue;
      $o = array();
	  list($primary) = $db->MetaPrimaryKeys($table);
	  if (!$primary) {
		DSerror("Table `$table` has no primary key.");
		continue;
	  }
	  //concatenate text columns for easy searching
	  foreach ($db->MetaColumns($table) as $col) 
	   if (in_array($db->MetaType($col->type),array('C','X')))
	    $textcols[] = $col->name;
	  $query = "SELECT *" . (count($textcols)>0 
	    ? ", CONCAT(`".implode('`,"\n",`',$textcols)."`) AS `text`" : "")
	    . " FROM $table";
	  $DQcache[$table] = $db->GetAssoc($query);
	  $o = $db->GetCol("SELECT "
		. $db->Concat("'".ucwords($table).".'",$primary)
		. " FROM $table");
      $out = array_merge($out, MatchPageNames($o, $pats));
    }
    return $out;
  }
} //end of DataStore object

function DSerror($error) {
 if (function_exists('ZAPwarning')) ZAPwarning($error);
 elseif (function_exists('DSerror')) DSerror($error);
 else print "<h3 style='color:red'>$error</h3>\n";
}

?>