* * * * @author: ReiniUrban */ class WikiPlugin_SqlResult extends WikiPlugin { var $_args; function getName () { return _("SqlResult"); } function getDescription () { return _("Display arbitrary SQL result tables"); } function getVersion() { return preg_replace("/[Revision: $]/", '', "\$Revision: 1.4 $"); } function getDefaultArguments() { return array( 'alias' => false, // DSN database specification 'ordered' => false, // if to display as

list: single col only without template 'template' => false, // use a custom /template.tmpl 'where' => false, // custom filter for the query 'sortby' => false, // for paging, default none 'limit' => "0,50", // for paging, default: only the first 50 ); } function getDsn($alias) { $ini = parse_ini_file(FindFile("lib/plugin/SqlResult.ini")); return $ini[$alias]; } /** Get the SQL statement from the rest of the lines */ function handle_plugin_args_cruft($argstr, $args) { $this->_sql = str_replace("\n"," ",$argstr); return; } function run($dbi, $argstr, &$request, $basepage) { global $DBParams; //$request->setArg('nocache','1'); extract($this->getArgs($argstr, $request)); if (!$alias) return $this->error(_("No DSN alias for SqlResult.ini specified")); $sql = $this->_sql; // apply custom filters if ($where and strstr($sql, "%%where%%")) $sql = str_replace("%%where%%", $where, $sql); // TODO: use a SQL construction library? if ($limit) { $limit = PageList::limit($limit); if (strstr($sql, "%%limit%%")) $sql = str_replace("%%limit%%", $limit, $sql); else { if (strstr($sql, "LIMIT")) $sql = preg_replace("/LIMIT\s+[\d,]+\s+/m", "LIMIT ".$limit." ", $sql); } } if (strstr($sql, "%%sortby%%")) { if (!$sortby) $sql = preg_replace("/ORDER BY .*%%sortby%%\s/m", "", $sql); else $sql = str_replace("%%sortby%%", $sortby, $sql); } else { // add sorting: support paging sortby links if (preg_match("/\sORDER\s/",$sql)) $sql = preg_replace("/ORDER BY\s\S+\s/m", "ORDER BY " . PageList::sortby($sortby,'db'), $sql); else $sql .= "ORDER BY " . PageList::sortby($sortby,'db'); } $inidsn = $this->getDsn($alias); if (!$inidsn) return $this->error(sprintf(_("No DSN for alias %s in SqlResult.ini found"), $alias)); // adodb or pear? adodb as default, since we distribute per default it. // for pear there may be overrides. if ($DBParams['dbtype'] == 'SQL') { $dbh = DB::connect($inidsn); $all = $dbh->getAll($sql); } else { if ($DBParams['dbtype'] != 'ADODB') { // require_once('lib/WikiDB/adodb/adodb-errorhandler.inc.php'); require_once('lib/WikiDB/adodb/adodb.inc.php'); } $parsed = parseDSN($inidsn); $dbh = &ADONewConnection($parsed['phptype']); $conn = $dbh->Connect($parsed['hostspec'],$parsed['username'], $parsed['password'], $parsed['database']); $GLOBALS['ADODB_FETCH_MODE'] = ADODB_FETCH_ASSOC; $dbh->SetFetchMode(ADODB_FETCH_ASSOC); $all = $dbh->getAll($sql); $GLOBALS['ADODB_FETCH_MODE'] = ADODB_FETCH_NUM; $dbh->SetFetchMode(ADODB_FETCH_NUM); } $args = array(); if ($limit) { // fill paging vars (see PageList) $args = $this->pagingTokens(count($all), count($all[0]), $limit); if (!$args) $args = array(); } if ($template) { $args = array_merge( array('SqlResult' => $all, // the resulting array of rows 'ordered' => $ordered, // whether to display as
/
or
'where' => $where, 'sortby' => $sortby, 'limit' => $limit), $args); // paging params override given params return Template($template, $args); } else { if ($ordered) { $html = HTML::ol(array('class'=>'sqlresult')); foreach ($all as $row) { $html->pushContent(HTML::li(array('class'=> $i++ % 2 ? 'evenrow' : 'oddrow'), $row[0])); } } else { $html = HTML::table(array('class'=>'sqlresult')); $i = 0; foreach ($all as $row) { $tr = HTML::tr(array('class'=> $i++ % 2 ? 'evenrow' : 'oddrow')); foreach ($row as $col) { $tr->pushContent(HTML::td($col)); } $html->pushContent($tr); } } } // do paging via pagelink template if (args['NUMPAGES']) { $paging = Template("pagelink", $args); $html = $table->pushContent(HTML::thead($paging), HTML::tbody($html), HTML::tfoot($paging)); } return $html; } }; // $Log: not supported by cvs2svn $ // Revision 1.3 2004/09/06 08:36:28 rurban // support templates, with some vars // // Revision 1.2 2004/05/03 21:57:47 rurban // locale updates: we previously lost some words because of wrong strings in // PhotoAlbum, german rewording. // fixed $_SESSION registering (lost session vars, esp. prefs) // fixed ending slash in listAvailableLanguages/Themes // // Revision 1.1 2004/05/03 20:44:58 rurban // fixed gettext strings // new SqlResult plugin // _WikiTranslation: fixed init_locale // // For emacs users // Local Variables: // mode: php // tab-width: 8 // c-basic-offset: 4 // c-hanging-comment-ender-p: nil // indent-tabs-mode: nil // End: ?>