<?php
/**
* PmWiki recipe to make records from any database supported by the ADOdb-connect recipe 
* act like wiki pages.  This recipe consists of two PHP objects: one DataStore object to 
* present queries as pages, and one DataQuery object for *each* query.  DataQuery
* handles all I/O with the database; DataStore handles all I/O with the wiki.
* copyleft 2006-10-03, 2007-10-08 Ben Stallings <Ben@InterdependentWeb.com> .
* modified to use either Fox or ZAP as form processor,  2008 Hans Bracker
*/

/** 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
	'actions' => array('search','zap','foxpost'), //include actions to enable access to records
	'offlimits' => array (), //tables and columns that this recipe should not display or edit
	'errors' => 'display', //set this to an email address if you want to be notified 
		// when an error is added to DataQuery.ErrorLog, set to 'display' if you 
		// have a (:messages:) tag on your pages, or set it to '' for no notification.
	'special' => array('QueriesToUse','ErrorLog',
		'RecentChanges','GroupHeader','GroupFooter','GroupAttributes','PageActions',
		'SideBar'), // pages in a group that should not be treated as records
	'auto' => array('GroupHeader'=>'view','EditForm'=>'edit','Templates'=>'templates',
		'HomePage'=>'recordlist','Search'=>'search'), //action to perform on pages in a group
	'fieldtypes' => array('C'=>'text','X'=>'textarea','D'=>'date','T'=>'timestamp',
		'L'=>'radios','binary'=>'radios','N'=>'numeric','I'=>'numeric',
		'R'=>'hidden','B'=>'file','enum'=>'select','set'=>'multiple',
		'linked'=>'select'), //default inputs for SQL types and ADOdb MetaTypes
	'maxsize' => 60, //maximum width, in characters, of auto-generated text and textarea fields
	'rows' => 5, //rows to use in textareas and multiple-select boxes
	'validate' => array('date'=>'/^\d{4}-\d\d-\d\d$/', 
		'timestamp'=>'/^\d{4}-\d\d-\d\d \d\d:\d\d:\d\d$/',
		'numeric'=>'/^\d*.?\d*$/'), //regular expressions to match with ZAP validate
	'usejavascript' => false, //regarding the JavaScript below
	'scriptfile' => 'http://workscited.net/dFilter.js', //JavaScript to use for input masking
	'passwordfields' => array('password','passwd')
));
SDV($HTMLStylesFmt['dataquery'], "\ntable.DQedit td.head {font-weight:bold;}\n"
. "\ntable.DQedit span.required {color:red;}\n");

//in case dataquery.php gets loaded before scripts/stdmarkup.php,
//which no longer happens as of PmWiki 2.2.0 beta 46
//SDV($PageTextVarPatterns['(:var:...:)'], '/\\(:(\\w[-\\w]*):\\s?(.*?):\\)/s');

//add _ to the list of acceptable characters in wiki group and page names
$PageNameChars = ($PageNameChars ? '_'.$PageNameChars : '-_[:alnum:]');

// Substitute DQPostPage() for PostPage() to allow deletion from records
if (is_array($EditFunctions))
	$EditFunctions[array_search('PostPage',$EditFunctions)] = 'DQPostPage';
else $EditFunctions = array('EditTemplate', 'RestorePage', 'ReplaceOnSave',
	'SaveAttributes', 'DQPostPage', 'PostRecentChanges', 'AutoCreateTargets', 'PreviewPage');

//establish order=natural for pagelists, so that 11 comes before 100 and not after
$PageListSortCmp['natural'] = 'strnatcasecmp($x,$y)';

## End of configuration variables.

$RecipeInfo['DataQuery']['Version'] = '2008-03-01';

$FmtPV['$DQversion'] = "'{$RecipeInfo["DataQuery"]["Version"]}'";
$FmtPV['$QueryString'] = "'" . $_SERVER['QUERY_STRING'] . "'";

# (:select field label:)
Markup('data', 'fulltext', '/\\(:data\\s+(\\w+)(.*):\\)/ei',
  "DQinput(\$pagename, '$1', PSS('$2'))");

if ($DQglobals['usejavascript'] !== false) { //install JavaScript for input masking
	$HTMLHeaderFmt['inputmask'] = '<script type="text/javascript" src="'.$DQglobals['scriptfile'].'"></script>';
	$InputAttrs = array('name', 'value', 'id', 'class', 'rows', 'cols', 
		'size', 'maxlength', 'action', 'method', 'accesskey', 'multiple',
		'checked', 'disabled', 'readonly', 'enctype', 'src', 'alt', 'onKeyDown');
} //end if usejavascript

# Don't include auto-generated pages in "normal" pagelists
foreach ($DQglobals['auto'] as $key => $value) {
	$SearchPatterns['normal'][] = '!\.'.$key.'!';
	$DQglobals['special'][] = $key;
}

// $DQgroup and $DQname are the page being requested by the *user* when the recipe loads.
// When functions are called later, $pagename is the page being requested by the *wiki*,
// which may be GroupHeader or EditForm or Templates.
$DQgroup = FmtPageName('$Group',$pagename);
$DQname = FmtPageName('$Name',$pagename);

# Add the group's templates page to the places PmWiki looks for Page List templates
$FPLTemplatePageFmt = array('{$FullName}',
	'{$Group}.'.array_search('templates',$DQglobals['auto']),
	'{$SiteGroup}.LocalTemplates','{$SiteGroup}.PageListTemplates');
	
# Use a group's custom edit form, if it exists (e.g. if DataPlates generates it)
# and the page being edited is not in the "special" list
if ((PageExists($DQgroup.".".array_search('edit',$DQglobals['auto']))) 
	and (!in_array($DQname,$DQglobals['special'])) and ($DQname != $DQgroup) 
	and (($DQgroup == 'DataQuery') or (DQkeymatch($DQgroup,$DQname)))) 
	$PageEditForm = '$Group.'.array_search('edit',$DQglobals['auto']);
	
if ((PageExists($DQgroup.".".array_search('search',$DQglobals['auto']))) and ($action==search))
	 XLSDV('en', array('SearchFor' => 'Results of your search:',
	 		'SearchFound' => '$MatchCount pages found.'));

if ($DQgroup == 'DataQuery') { //this section is redundant if DataPlates is installed
	$EditTemplatesFmt = 'DataQuery.Template';
	$FPLTemplatePageFmt = array('{$FullName}','{$Group}.Templates',
		'{$SiteGroup}.LocalTemplates','{$SiteGroup}.PageListTemplates');
}
$DQglobals['queries'] = $DQglobals['allqueries'] = array();

# Connect to Databases
include_once("$FarmD/cookbook/adodb-connect.php");
if (!is_array($DQglobals['databases'])) {
	DQerror('No databases specified in $DQglobals["databases"]');
} else {
	//make $DQglobals['offlimits'] lowercase, so we can use in_array() for quick reference
	foreach ($DQglobals['offlimits'] as $k=>$v) 
		$DQglobals['offlimits'][$k] = strtolower($v);
	//determine which queries to use, if specified
	$qu = preg_split("/[\s,|\\n]+/s", PageTextVar('DataQuery.QueriesToUse','queries'),-1,PREG_SPLIT_NO_EMPTY);
	foreach ($qu as $q)
		if (!in_array(strtolower($q),$DQglobals['offlimits'])) 
			$DQglobals['queries'][] = ucfirst($q);

	//if no queries were specified, they'll all be picked up below
	foreach ($DQglobals['databases'] as $d) {
		$m = ADOdbConnect($d);
		if ($m !== TRUE) {DQerror($m); continue;}
		$DB[$d]->SetFetchMode(ADODB_FETCH_ASSOC); // use associative arrays by default
		$DQglobals['allqueries'] = array_merge($DQglobals['allqueries'],$DB[$d]->MetaTables());
	} //end foreach
	if (count($DQglobals['queries'])<1) 
		 foreach ($DQglobals['allqueries'] as $q) $DQglobals['queries'][] = ucfirst($q);
	//prepend DataStore object to the WikiLibDirs so that it is consulted before wiki pages
	array_unshift($WikiLibDirs,new DataStore());
} //end if

class DataStore {
	var $dirfmt, $iswrite, $group, $name;
	function DataStore() {
		global $DQgroup, $DQname;
		$this->dirfmt = 'ADOdb';
		$this->iswrite = ($DQgroup != 'DataQuery');
		$this->group = strtolower($DQgroup); //for easy access within functions
		$this->name = $DQname; //ditto
	} //end of DataStore::DataStore()
	
	function read($pagename, $since=0) {
		global $RecipeInfo, $DQglobals, $DQcache, $action, $DQname, $DQgroup;
		//print "attempting to read $pagename "; //uncomment to debug
		if ($pagename == 'DataQuery.DataQuery') return $this->status();
		elseif ($pagename == 'DataQuery.Templates') 
			//if DataPlates is installed, we use its templates instead of DataQuery's
			return (class_exists('DataPlate') ? false : $this->templates());
		if (($action == 'edit') and (in_array($DQname,$DQglobals['special']))) return;
		$group = FmtPageName('$Group',$pagename);
		$record = FmtPageName('$Name',$pagename);
		if ((!$group) or (!$record)) return;
		if (($pagename == 'DataQuery.GroupHeader') and ($DQname != 'GroupHeader')
			and (!is_array($DQglobals['auto'])))
			return $this->groupheader($DQname);
		elseif ($group == 'DataQuery') return $this->template($record);
		if ((!$group) or (!$record)) return;
		if ($group == $record) return;
		if (is_array($DQcache[$group][$record])) {
			//record is in cache from a previous function, most likely ls()
			$data = $DQcache[$group][$record];
		} else { //print "retrieving record $pagename"; //uncomment to debug
			$query = DQloadquery($group);
			if (!is_object($query)) return;
			if (!$query->key) return;
			$data = $query->read($record);
			if (!$data) return;
			$DQcache[$group][$record] = $data;
		} //end if
		$page = array();
		// handle page history
		foreach(explode("\n",$data['pm_history']) as $line) {
			$pos = strpos($line,"=");
			$page[substr($line,0,$pos)] = 
				str_replace("%0a","\n",substr($line,($pos+1)));
		} //end foreach
		unset($data['pm_history']);
		// handle other page fields (version, author, etc.)
		foreach(array('version','text','time','author','passwdread','passwdedit',
		'passwdattr','passwdupload','passwdzap') as $field) {
			SDV($page["$field"],$data["pm_$field"]);
			unset($data["pm_$field"]);
		} //end foreach
		// render the remaining fields as page text variables
		$page['text'] = " \n\n(:comment data:)\n\n";
		if ($action == 'search') {$page['text'].="(:if false:)\n"; $fore=$aft='';}
		else {$fore = '(:'; $aft = ':)';}
		foreach ($data as $key => $value) {
			if (!in_array($query->name.".$key",$DQglobals['offlimits']))
				$page['text'] .= "$fore$key: ".str_replace("\n","[[<<]]",$value)
				. "$aft\n\n";
		} //end foreach
		return $page;
	} //end of DataStore::read()

	function write($pagename,$page) {
		global $DQcache, $DQglobals, $PageTextVarPatterns, $WikiDir;
	print "attempting to write <b>$pagename</b> "; //uncomment for debugging
		$group = FmtPageName('$Group',$pagename);
		$record = FmtPageName('$Name',$pagename);
		if (!DQkeymatch($group,$record)) return;
		$query = DQloadquery($group);
		if ((!$record) or (!$query)) return;
		$exists = $query->exists($record); //to determine whether to write pm_ page-text variables
		// handle PmWiki page fields (diff, version, author, passwords, etc.)
		foreach($page as $key => $value)
			if (strpos($key,":") > 0) {
				$history .= "$key=".str_replace("\n","%0a",$value)."\n";
				unset($page[$key]);
			}
		$page['pm_history'] = $history;
		foreach(array('version','text','time','author','passwdread','passwdedit','passwdattr',
		'passwdupload','passwdzap') as $field) {
			$page["pm_$field"] = $page[$field];
			unset($page[$field]);
		} 
		$htmlin = array("'", '"', '  ', ":");
		$htmlout = array('&#39;', '&quot;', '&nbsp;&nbsp;', '&#x3a;');
		// parse PageTextVariables -- code adapted from pmwiki.php's PageTextVar()
		foreach((array)$PageTextVarPatterns as $pat) 
			if (preg_match_all($pat, $page['pm_text'], $match, PREG_SET_ORDER))
				foreach($match as $m)
					if ((substr($m[1],0,3) != 'pm_') or (!$exists))
						$page[$m[2]] = str_replace($htmlout,$htmlin,Qualify($pagename, $m[3]));
		$page[$query->config['key']] = $record;
		if (!$query->write($record,$page)) {
			DQerror($query->db->ErrorMsg());
			Abort("Cannot write page to $pagename on database \"".$query->database
			. "\"...changes not saved");
		} //end if
		//remove page from cache so a fresh copy will be read from database
		unset($DQcache[$group][$record]);
	} //end of DataStore::write()
	
	function exists($pagename) {
		global $DQcache, $DQglobals, $WikiDir, $action, $ZAParray, $DQname, $DQgroup;
		//print "<br />DQ Checking existence of $pagename "; //uncomment to debug
		if (!$pagename) return false;		
		if (($pagename == 'DataQuery.Templates') and (is_array($DQglobals['templates']))) return false;
		if (in_array($pagename,array('DataQuery.DataQuery','DataQuery.Templates','DataQuery.GroupHeader'))) 
			return true;
		$group = FmtPageName('$Group',$pagename);
		if($group=='DataQuery')
		if (strpos($group,'/')!==false) return false;
		$record = FmtPageName('$Name',$pagename);
		if ($DQcache[$group][$record]) return true;
		if ((in_array($record,$DQglobals['special'])) or ($record == $group)) return false;
		if ($group == 'DataQuery') {
			$query = DQloadquery($record);
			return ($query->database > "");
		}
		if (!DQkeymatch($group,$record)) return false;
		$query = DQloadquery($group);
		if (!$query->database) return false;
		if ((in_array($action, $DQglobals['actions'])) and (!in_array($record,$DQglobals['special']))
		and ($record != $group) and ($DQglobals['WritePage'] == true)) return true;
		return ($query->exists($record));
	} //end DataStore::exists()
	
	function delete($pagename) {
		global $Now,$WikiDir;
		$query = DQloadquery(FmtPageName('$Group',$pagename));
		if (!$query->database) return false;
		if ($query->delete(FmtPageName('$Name',$pagename))) {
			$page = $this->read($pagename);
			$WikiDir->write("$pagename,del-$Now",$page);
		} else abort("unable to delete $pagename from ".$query->database); //uncomment to debug
	} //end DataStore::delete()
	
	function ls($pats=NULL) {
		global $DQgroup, $DQglobals, $action;
		$pats = (array)$pats;
		if (!is_array($pats)) $pats=array($pats);
		if (($action=search) and (count($pats)==0)) $pats[]=" ,$DQgroup.*, ";
		$out = array();
		foreach ($pats as $pat) foreach (explode(",",$pat) as $p) {
			if ($p == "") continue;
			$group = FmtPageName('$Group',str_replace("\\","",trim($p,'/^, ')));
			if ($group == "DataQuery") { 
				//list all queries, even if they aren't in use or don't yet have configuration pages
				foreach ($DQglobals['allqueries'] as $query)
					$queries[] = "DataQuery.".ucwords($query);
				$out = array_merge($out, MatchPageNames($queries, $pats));
			} else {
				$query = DQloadquery($group);
				if (!$query) continue;
				$out = array_merge($out, MatchPageNames($query->ls(), $pats));
			}//end if
		} //end foreach
		return $out;
	} //end DataStore::ls()
	
	//display the DataQuery.DataQuery status screen
	function status() { 
		global $DQglobals;
		if (is_array($DQglobals['databases'])) {
			$page['text'] = "(:nogroupheader:)\n"
			. "(:pagelist group=DataQuery name=-DataQuery,-ErrorLog,-QueriesToUse "
			. "list=normal order=natural fmt=#DQstatus:)\n"
			. "\n\nView the [[Error Log]]";
		} else $page['text'] = "No databases are connected.  See the [[Error Log]].";
		return $page;
	} //end DataStore::status()

	//produce a template for a new DataQuery.$name page
	function template($name) { 
		global $DQglobals, $WikiDir;
		if ($name == 'QueriesToUse') {
			if ($WikiDir->exists('DataQuery.QueriesToUse')) return false;
			$page['text'] = "Edit this page to see its contents.\n\n"
			. "(:comment List all the queries you want to use. "
			. "To use all of them, simply delete this page.:)"
			. " \n\n(:comment data:)\n\n(:queries: ".implode(", ",$DQglobals['queries']).":)\n\n";
		} else {
			$page['text'] = " \n\n(:comment data:)\n\n";
			$query = DQloadquery($name);
			if ($query == false) return false;
			foreach ($query->config as $key => $value)
				$page['text'] .= "(:$key: $value:)\n\n";
		}
		return $page;
	} //end DataStore::template()

	//produce the pagelist template for use on the status screen
	function templates() { 
		$page['text'] = "[@\n[[#DQstatus]]\n(:if equal {<\$Group}:)\n"
		. "[[(Cookbook:)DataQuery]] is aware of the following queries:"
		. "\n\n||border=0\n(:ifend:)\n"
		. "|| {=\$Name}:||[[Configure -> DataQuery.{=\$Name}?action=edit]]"
		. " - [[List -> {\$FullName}?q=+?group={=\$Name}?action=search&order=natural]] ||\n"
		. "(:if equal {>\$Group}:)\n\n"
		. "However, not all of them may be in use.  Limit the [[Queries to Use(?action=edit)]]\n"
		. "(:ifend:)\n[[#DQstatusend]]\n@]\n";
		return $page;
	} //end DataStore::templates()
	
	function groupheader($name) {
		$query = DQloadquery($name);
		if ($query == false) return false;
		foreach ($query->config as $key => $value)
			$page['text'] .= "(:cellnr:)'''$key:'''\n(:cell:)$value\n";
		return $page;
	}
} //end of DataStore object

function DQloadquery($Name) { //create a new DataQuery object if it doesn't already exist
	global $DataQueries,$action,$DQglobals,$DB,$DQname;
	//if $Name matches a table, change the capitalization to match
	if ((is_object($DataQueries[$Name])) and ($action != 'zap')) //use cached configuration
		if ($DataQueries[$Name]->config['key']) return $DataQueries[$Name];
	if (($DQgroup != 'DataQuery') and (!@in_array($Name,$DQglobals['queries']))) return;
	$name = $Name;
	foreach ($DQglobals['databases'] as $db)
		foreach ($DB[$db]->MetaTables() as $table) if (strcasecmp($table,$Name) == 0) {
			$name = $table;
			break 2;
		} //end foreach table
	if ((!is_object($DataQueries[$name])) or ($action == 'zap')) {
		if (in_array($name,$DQglobals['offlimits'])) return false;
		//print " <br />attempting to load query <b>$name</b> from database $db\n"; //uncomment to debug
		$DataQueries[$name] = new DataQuery($db, $name);
	} //end if
	if (($DataQueries[$name]->config['key']) or (($DQname == $Name) and ($action=='edit'))) 
		return $DataQueries[$name];
	return false;
} //end DQloadquery()


//an object for a specific query, as configured at DataQuery.$query
class DataQuery { 
	var $database, $db, $name, $key, $config, $tables, $joins, $display, $structure, $columns, $where;
	
	//create and configure query object
	function DataQuery($db,$name) { 
		global $DB, $DQglobals, $DQgroup, $DQname, $DQcache, $action;
		if ((!$db) or (!$name)) return;
		$pc = DQReadPageTextVars("DataQuery.".ucfirst($name));
		$table = ($pc['table'] ? $pc['table'] : $name);
		$cols = (array)$DB[$db]->MetaColumnNames($table,true);
		$metacolumns[$table] = (array)$DB[$db]->MetaColumns($table);
		$display = array();
		foreach ($cols as $key => $val)
			if (in_array("$table.$val", $DQglobals['offlimits'])) unset($cols[$key]);
			elseif (($val) and (substr($val,0,3)!='pm_')) {
				$display[] = "$table.$val AS $val";
				$this->structure[$table][$val] = $this->columns[$val] = @$metacolumns[$table][strtoupper($val)];
			}
		list($primary) = $DB[$db]->MetaPrimaryKeys($table);
		if (!$primary) $primary = end(array_slice($cols,0,1)); //use first column as default key
		if (!$pc['database']) {
			//print "query $name hasn't been configured -- generating default configuration\n";
			if (in_array($table,$DQglobals['offlimits'])) return false;
			$this->display = implode(",\n",$display);
			$this->config = array('database'=>$db, 'table'=>$table,'join_field1'=>"",
			'join_to1'=>"", 'display'=>$this->display, 'key'=>$primary,
			'group'=>"", 'order'=>$primary, 'limit'=>"", 'where'=>'1=1');
			$this->key = $primary;
		} else { //put all page text variables from configuration page into $query
			$this->config = $pc;
			$this->key = $this->config['key'] = ($pc['key'] ? $pc['key'] : $primary);
			if ($pc['display'] > "") { //use customized field names instead of defaults
				$display = $this->columns = array();
				foreach (explode("\n",str_replace("[[<<]]","\n",$pc['display'])) as $d) {
					unset($a);
					list($c,$a) = explode(" AS ",$d);
					if (!$a) list($c,$a) = explode(" as ",$d);
					$a = trim($a,", \n\r"); $c = trim($c);
					$alias[$a] = $c;
					$display[] = "$c AS $a";
					if ($a == $pc['key']) $this->key = $c;
					list($t,$f) = explode(".",$c);
					if (!is_array($metacolumns[$t])) $metacolumns[$t] = (array)$DB[$db]->MetaColumns($t);
					if ($f>"") $this->structure[$t][$f] = $this->columns[$a] = @$metacolumns[$t][strtoupper($f)];
				} //end foreach
			} //end if display
			$this->config['display'] = $this->display = implode(",\n",$display);
			$order = array(); //remove blank orders from list and replace with valid field names
			foreach (explode(",",$this->config['order']) as $o) if ($o > "") 
			  $order[] = (substr($o,-5) == ' DESC' ? $alias[substr($o,0,-5)].' DESC' : $alias[$o]);
			$this->config['order'] = implode(",",$order);
			$groupby = array(); //same thing, with the group-by list
			foreach (explode(",",$this->config['group']) as $o) if ($o > "") 
			  $groupby[] = (substr($o,-5) == ' DESC' ? $alias[substr($o,0,-5)].' DESC' : $alias[$o]);
			$this->config['group'] = implode(",",$groupby);
		} //end if !PageTextVar()
		$this->database = $db; $this->db = $DB[$db]; $this->name = $name;
		// generate tables and joins, for use in SQL queries
		$n=1; $tables = array($this->config['table']); $metatables=$DB[$db]->MetaTables();
		while (($this->config["join_field$n"]>"") and ($this->config["join_to$n"]>"")) {
			list($t1,$f1) = explode(".",$this->config["join_field$n"]);
			list($t2,$f2) = explode(".",$this->config["join_to$n"]); $ts2 = $t2;
			while ((!in_array($ts2,$metatables)) and (strlen($ts2) > 2)) $ts2 = substr($ts2,0,-1);
			$metacolumns[$ts2] = $DB[$db]->MetaColumns($ts2);
			$tables[] = $this->config["join_type$n"]." JOIN $ts2 AS $t2 ON $t1.$f1 = $t2.$f2";
			$this->joins["$t1.$f1"] = "$t2.$f2";
			$n++;
		} //end while
		$this->tables = implode(" ",$tables);
		//build $this->where from conditions
		$conds = array();
		if (is_array($pc)) foreach ($pc as $k => $v) {
			if ((substr($k,0,5) != 'cond_') or ($v == '') or (!strpos($this->display," AS ".substr($k,5)))) 
				continue;
			$conds[] = substr($k,5)." $v";
		} //end foreach
		$this->where = (count($conds)>0 ? implode("\nAND ",$conds)."\nAND " : "")
		. ($pc['where'] ? $pc['where'] : "1=1");
		//replace [parameters] with $_REQUEST or enclosing page's text variables
		$data = array_merge($_REQUEST,
			(array)$DQcache[$DQgroup][$DQname],
			(array)DQReadPageTextVars($DQgroup.".".$DQname));
		if (!isset($data['Name'])) $data['Name'] = $DQname;
		$this->where = stripslashes(preg_replace("/('.*)\[(\w+)\](.*')/e", '"$1".$data["$2"]."$3"', $this->where));
		$this->where = preg_replace('/\\[(\\w+)\\]/e', 'DQquote($data["$1"])', $this->where);
		if (($action == 'search') and ($_POST['action'] != 'zap') and (is_array($this->columns))) 
			foreach($this->columns as $k=>$c)	if (!isset($conds[$k]))
			   if (is_array($_REQUEST[$k])) 
				 		foreach($_REQUEST[$k] as $r) $this->where .= "\nAND ".$c->name." LIKE '%$r%'";
			   elseif (in_array(substr($_REQUEST[$k],0,1),array('=','<','>')))
				 		$this->where .= "\nAND ".$c->name." ".substr($_REQUEST[$k],0,1)."'".substr($_REQUEST[$k],1)."'";
				 elseif (strpos($_REQUEST[$k],'..'))
				 		$this->where .= preg_replace("/(.*) ?\.\. ?(.*)/e", '"\nAND '.$c->name.' BETWEEN \"$1\" AND \"$2\""', $_REQUEST[$k]);
				 elseif (strpos($_REQUEST[$k],'%')!==false)
				 	  $this->where .= "\nAND ".$c->name." LIKE '".$_REQUEST[$k]."'";
				 elseif ($_REQUEST[$k] > '') 
				 		$this->where .= "\nAND ".$c->name." LIKE '%".$_REQUEST[$k]."%'";
		//select the first record to determine structure of calculated fields
		$sql = "SELECT ".$this->display." FROM ".$this->tables." WHERE ".$this->config['where'];
		$firstrow = @$this->db->SelectLimit($sql,1);
		if (is_object($firstrow)) for ($i=0; $i <= $firstrow->FieldCount(); $i++) {
			$ff = $firstrow->FetchField($i);
			if (($ff->name > "") and (!isset($this->columns[$ff->name])))
				$this->structure['calc'][$ff->name] = $this->columns[$ff->name] = $ff;
		} //end if
	} //end DataQuery::DataQuery()
	
	function read($record) {
		global $action,$DQname;
		$sql = "SELECT ".$this->display." FROM ".$this->tables." WHERE "
		. $this->key." = ".DQquote($record)." AND ".$this->where
		. ($this->config['group']>"" ? " GROUP BY ".$this->config['group'] : "").";";
		//print $sql; //uncomment to debug
		if (!$rs = $this->db->Execute($sql)) {
			DQerror($this->db->ErrorMsg());
			return false;
		} //end if
		$data = $rs->FetchRow();
		//print_r($data); //uncomment to debug
		if ($data[$this->config['key']] != $record) { //print "$record does not exist ";
			if (!DQkeymatch($this->name,$record)) return;
			if (($action == 'edit') and ($record == $DQname)) //create a blank record to edit
				foreach (array_keys($this->columns) as $col) 
					$data[$col] = "";
			else return;
		} //end if
		return $data;
	} //end DataQuery::read()
	
	//write changes to the primary table of the query
	function write($record,$page) { 
		foreach ($this->columns as $name => $col) { 
			//set blank values in not-null fields to default values
			if (($page[$name]==="") and ($col->not_null == 1) and (isset($col->default_value)))
				$page[$name] = $col->default_value;
			//recode $page with actual field names of table
			if ((is_object($this->structure[$this->config['table']][$col->name])) and ($col->name != $name)) {
				$page[$col->name] = $page[$name];
				unset($page[$name]);
			} //end if is_object
		} //end foreach
		$s = false;
		$p = $this->read($record);
		//print $this->db->GetUpdateSQL($this->db->Execute("SELECT ".$this->display." FROM ".$this->tables." WHERE ".$this->where." AND ".$this->key." = ".DQquote($record).";"),$page);
		$s = ($p[$this->config['key']] == $record
		? $this->db->AutoExecute($this->config['table'],$page,'UPDATE',$this->key."=".DQquote($record))
		: $this->db->AutoExecute($this->config['table'],$page,'INSERT'));
		return $s;
	} //end DataQuery::write()
	
	function exists($record) {
		$sql = "SELECT ".$this->display
		. " FROM ".$this->tables." WHERE ".$this->key." = ".DQquote($record)
		. ($this->config['group'] ? " GROUP BY ".$this->config['group'] : "").";";
		//print $sql; //uncomment to debug
		$rs = $this->db->Execute($sql);
		if (!$rs) return false;
		$data = $rs->FetchRow();
		return ($data[$this->config['key']] == $record);
	} //end DataQuery::exists()
	
	function delete($record) {
		global $DQcache;
		//Abort("attempting to delete $record"); //uncomment to debug
		$s = $this->db->Execute("DELETE FROM ".$this->config['table']
		. " WHERE ".$this->key." = ".DQquote($record));
		unset($DQcache[$this->name][$record]);
		return (is_object($s));
	} //end DataQuery::delete()
	
	function ls() { //two queries: one for the cache, one for the pagelist.
		global $DQcache;
		$where = $this->where;
		if (strpos($where,' UNION ')) {
			$where = str_replace(' UNION ',') UNION (',$where);
			$union = true;
		} else $union = false;
		$queryend = " FROM ".$this->tables." WHERE ".$where.($union ? ")" : "")
		. ($this->config['group']>"" ? " GROUP BY ".$this->config['group'] : "")
		. ($this->config['order']>"" ? " ORDER BY ".$this->config['order'] : "")
		. ($this->config['limit'] ? " LIMIT ".$this->config['limit'] : "");
		$sql = ($union ? "(" : "")."SELECT ".$this->display.$queryend;
		//Abort($sql); //uncomment to debug
		$DQcache[$this->name] = $this->db->GetAssoc($sql);
		//remove extra field names from union queries prior to generating pagelist
		if ($union) $queryend = substr($queryend,0,strpos($queryend,'SELECT '))
		. " SELECT ".$this->db->Concat("'".ucwords($this->name).".'",$this->key)
		. substr($queryend,strpos($queryend,'FROM ',5));
		$sql = ($union ? "(" : "")."SELECT " . $this->db->Concat("'".ucwords($this->name).".'",$this->key).$queryend;
		//Abort($sql); //uncomment to debug
		$o = $this->db->GetCol($sql);
		return $o;
	} //end DataQuery::ls()
} //end of DataQuery object

function DQReadPageTextVars($pagename) {
	global $WikiDir,$PageTextVarPatterns;
	$pc = array();
	$page = $WikiDir->read($pagename);
	if (!is_array($page)) return;
	foreach($PageTextVarPatterns as $pat)
		if (preg_match_all($pat, $page['text'], $match, PREG_SET_ORDER))
			foreach($match as $m)  
				$pc[$m[2]] = Qualify("DataQuery.$name", $m[3]);
	return $pc;
} //end DQReadPageTextVars

function DQquote($value) { //put quotes around strings for use in SQL statements
	if (get_magic_quotes_gpc()) $value = stripslashes($value);
	if (!is_numeric($value)) $value = "'" . addslashes($value) . "'";
	return $value;
} //end DQquote()

function DQerror($error) { //handle error messages
	global $DQglobals;
	$page = ReadPage('DataQuery.ErrorLog');
	$page['text'] = date('r').": ".$error."\n\n".$page['text'];
	WritePage('DataQuery.ErrorLog',$page);
	if ($DQglobals['errors'] == 'display') print $error;
	elseif (strpos($DQglobals['errors'],'@'))
		mail ($DQglobals['errors'], 'DataQuery error message', $error);
} //end DQerror()


//DQPostPage is identical to PmWiki's PostPage() function except it allows for 
//deletion from DataStore objects as well as $WikiDir -- see comment below.
function DQPostPage($pagename, &$page, &$new) {
  //echo "<br /> DQPostPage>".$pagename;
  global $DiffKeepDays, $DiffFunction, $DeleteKeyPattern, $EnablePost,
    $Now, $Author, $WikiLibDirs, $WikiDir, $IsPagePosted, $DQglobals;
  SDV($DiffKeepDays,3650);
  SDV($DeleteKeyPattern,"^\\s*delete\\s*$");
  $IsPagePosted = false;
  if ($EnablePost) {
    $new["author"]=@$Author;
    $new["author:$Now"] = @$Author;
    $new["host:$Now"] = $_SERVER['REMOTE_ADDR'];
    $diffclass = preg_replace('/\\W/','',@$_POST['diffclass']);
    if ($page["time"]>0 && function_exists(@$DiffFunction)) 
      $new["diff:$Now:{$page['time']}:$diffclass"] =
        $DiffFunction($new['text'],@$page['text']);
    $keepgmt = $Now-$DiffKeepDays * 86400;
    $keys = array_keys($new);
    foreach($keys as $k)
      if (preg_match("/^\\w+:(\\d+)/",$k,$match) && $match[1]<$keepgmt)
        unset($new[$k]);
    if (preg_match("/$DeleteKeyPattern/",$new['text'])) {
      //this is the changed bit, scavenged from WritePage()
      for($i=0; $i<count($WikiLibDirs); $i++) {
        $wd = &$WikiLibDirs[$i];
        if ($wd->iswrite && $wd->exists($pagename)) break;
      }
      if ($i >= count($WikiLibDirs)) $wd = &$WikiDir;
      $wd->delete($pagename);
    } else {
    	$DQglobals['WritePage'] = true;
    	//echo ">calling WritePage=$pagename ";
    	WritePage($pagename,$new);
    	$DQglobals['WritePage'] = false;
    }
    $IsPagePosted = true;
  }
} //end DQPostPage()


function DQkeymatch($group,$record) {
	//check whether a non-numeric key value has been submitted for a numeric key field
	$q = DQloadquery($group);
	if (!is_object($q)) return false;
	$k = $q->db->MetaType($q->columns[$q->config['key']]->type);
	if ((in_array($k,array('N','D','T','I','R'))) and (!is_numeric($record))) return false;
	else return true;
} //end DQkeymatch()


function DQinput($pagename, $type, $args) { //turn (:data type field label:) into (:input type:)
	global $DQglobals,$DQgroup;
	//argument handling inspired by /scripts/forms.php
	$opt = ParseArgs($args);
	$params = array('name', 'label');
  while (count(@$opt['']) > 0 && count($params) > 0) 
    $opt[array_shift($params)] = array_shift($opt['']);
	if (substr($type,0,5) == 'type=') $type = substr($type,5);
	if ((!$type) or (!$opt['name'])) return "(:data $type $args:)";
	$thispage = FmtPageName('$Name',$pagename);
	$query = DQloadquery($DQgroup); //query being viewed
	if (!is_object($query)) return "(:data $type $args:)";
	$value = $query->joins[$query->config['table'].".".$opt['name']]; //use field from linked table rather than primary
	if (!value) $value = $query->config['table'].".".$opt['name'];
	list($jt,$jf) = explode('.',$value);
	if (!in_array($jt,$query->db->MetaTables())) $value = rtrim($jt,'0123456789').".".$jf;
	$label = ($opt['label'] ? $opt['label'] : $value);
	$tables = preg_replace("/(LEFT)? JOIN/i","RIGHT JOIN",$query->tables);
	$sql = "SELECT $value AS value, $label AS label FROM $tables"
	." WHERE {$query->where} AND $label > '' GROUP BY $label";
	//Abort($sql); //uncomment to debug
	if ($thispage == @array_search('templates',$DQglobals['auto'])) $q = "=";
	elseif (in_array($thispage,$DQglobals['special'])) $q = "*";
	$data = (array)$query->db->GetAll($sql);
	$null = !$query->columns[$opt['name']]->not_null;
	if ($type == 'select') {
	   if ($null) $out.= "(:input select {$opt['name']} '':)";
		 foreach ($data as $d) $out .= "(:input select {$opt['name']} '{$d['value']}' '"
		 . str_replace(array("\n","\t","\r","'",'"'), '', $d['label'])."':)";
	} elseif ($type == 'radios') {
		 foreach ($data as $d) $out .= "(:input radio {$opt['name']} '{$d['value']}':)"
		 . $d['label']."\\\\\n";
	   if ($null) $out.= "(:input radio {$opt['name']} '':)None\\\\\n";
	} elseif ($type == 'checkboxes')
		 foreach ($data as $d) $out .= "(:input checkbox {$opt['name']}[] '{$d['value']}':)"
		 . $d['label']."\\\\\n";
	else return "(:data $type $args:)";
	return $out;
}  //end of DQinput function