.
* 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'] = '';
$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 $pagename "; //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(''', '"', ' ', ':');
// 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 "
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 "
attempting to load query $name 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 "
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; $iiswrite && $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