4 * Copyright 2004 $ThePhpWikiProgrammingTeam
6 * This file is part of PhpWiki.
8 * PhpWiki is free software; you can redistribute it and/or modify
9 * it under the terms of the GNU General Public License as published by
10 * the Free Software Foundation; either version 2 of the License, or
11 * (at your option) any later version.
13 * PhpWiki is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
18 * You should have received a copy of the GNU General Public License
19 * along with PhpWiki; if not, write to the Free Software
20 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
24 * This plugin displays results of arbitrary SQL select statements
26 * The database definition, the DSN, must be defined in the local file
27 * config/SqlResult.ini
28 * A simple textfile with alias = dsn lines.
30 * Optional template file to format the result and handle some logic.
31 * Template vars: %%where%%, %%sortby%%, %%limit%%
35 * <?plugin SqlResult alias=mysql
36 * SELECT 'mysql password for string "xx":',
39 * <?plugin SqlResult alias=videos template=videos
40 * SELECT rating,title,date
42 * ORDER BY rating DESC
45 * <?plugin SqlResult alias=imdb template=imdbmovies where||="Davies, Jeremy%"
46 * SELECT m.title, m.date, n.name, c.role
47 * FROM movies as m, names as n, jobs as j, characters as c
48 * WHERE n.name LIKE "%%where%%"
49 * AND m.title_id = c.title_id
50 * AND n.name_id = c.name_id
51 * AND c.job_id = j.job_id
52 * AND j.description = 'Actor'
53 * ORDER BY m.date DESC
59 require_once("lib/PageList.php");
61 class WikiPlugin_SqlResult
67 return _("SqlResult");
70 function getDescription () {
71 return _("Display arbitrary SQL result tables");
74 function getVersion() {
75 return preg_replace("/[Revision: $]/", '',
79 function getDefaultArguments() {
81 'alias' => false, // DSN database specification
82 'ordered' => false, // if to display as <ol> list: single col only without template
83 'template' => false, // use a custom <theme>/template.tmpl
84 'where' => false, // custom filter for the query
85 'sortby' => false, // for paging, default none
86 'limit' => "0,50", // for paging, default: only the first 50
90 function getDsn($alias) {
91 $ini = parse_ini_file(FindFile("config/SqlResult.ini"));
95 /** Get the SQL statement from the rest of the lines
97 function handle_plugin_args_cruft($argstr, $args) {
98 $this->_sql = str_replace("\n"," ",$argstr);
102 function run($dbi, $argstr, &$request, $basepage) {
104 //$request->setArg('nocache','1');
105 extract($this->getArgs($argstr, $request));
107 return $this->error(_("No DSN alias for SqlResult.ini specified"));
110 // apply custom filters
111 if ($where and strstr($sql, "%%where%%"))
112 $sql = str_replace("%%where%%", $where, $sql);
113 // TODO: use a SQL construction library?
115 $pagelist = new PageList();
116 $limit = $pagelist->limit($limit);
117 if (strstr($sql, "%%limit%%"))
118 $sql = str_replace("%%limit%%", $limit, $sql);
120 if (strstr($sql, "LIMIT"))
121 $sql = preg_replace("/LIMIT\s+[\d,]+\s+/m", "LIMIT ".$limit." ", $sql);
124 if (strstr($sql, "%%sortby%%")) {
126 $sql = preg_replace("/ORDER BY .*%%sortby%%\s/m", "", $sql);
128 $sql = str_replace("%%sortby%%", $sortby, $sql);
129 } elseif (PageList::sortby($sortby,'db')) { // add sorting: support paging sortby links
130 if (preg_match("/\sORDER\s/",$sql))
131 $sql = preg_replace("/ORDER BY\s\S+\s/m", "ORDER BY " . PageList::sortby($sortby,'db'), $sql);
133 $sql .= " ORDER BY " . PageList::sortby($sortby,'db');
136 $inidsn = $this->getDsn($alias);
138 return $this->error(sprintf(_("No DSN for alias %s in SqlResult.ini found"),
140 // adodb or pear? adodb as default, since we distribute per default it.
141 // for pear there may be overrides.
142 // TODO: native PDO support (for now we use ADODB)
143 if ($DBParams['dbtype'] == 'SQL') {
144 $dbh = DB::connect($inidsn);
145 $all = $dbh->getAll($sql);
146 if (DB::isError($all)) {
147 return $this->error($all->getMessage(). ' ' . $all->userinfo);
149 } else { // unless PearDB use the included ADODB, regardless if dba, file or PDO, ...
150 if ($DBParams['dbtype'] != 'ADODB') {
151 require_once('lib/WikiDB/backend/ADODB.php');
153 $parsed = parseDSN($inidsn);
154 $dbh = &ADONewConnection($parsed['phptype']);
155 $conn = $dbh->Connect($parsed['hostspec'],$parsed['username'],
156 $parsed['password'], $parsed['database']);
158 return $this->error($dbh->errorMsg());
159 $GLOBALS['ADODB_FETCH_MODE'] = ADODB_FETCH_ASSOC;
160 $dbh->SetFetchMode(ADODB_FETCH_ASSOC);
162 $all = $dbh->getAll($sql);
164 $GLOBALS['ADODB_FETCH_MODE'] = ADODB_FETCH_NUM;
165 $dbh->SetFetchMode(ADODB_FETCH_NUM);
167 return $this->error($dbh->errorMsg());
170 if ($limit) { // fill paging vars (see PageList)
171 $args = $pagelist->pagingTokens(count($all), count($all[0]), $limit);
172 if (!$args) $args = array();
177 array('SqlResult' => $all, // the resulting array of rows
178 'ordered' => $ordered, // whether to display as <ul>/<dt> or <ol>
182 $args); // paging params override given params
183 return Template($template, $args);
186 $html = HTML::ol(array('class'=>'sqlresult'));
188 foreach ($all as $row) {
189 $html->pushContent(HTML::li(array('class'=> $i++ % 2 ? 'evenrow' : 'oddrow'), $row[0]));
192 $html = HTML::table(array('class'=>'sqlresult'));
195 foreach ($all as $row) {
196 $tr = HTML::tr(array('class'=> $i++ % 2 ? 'evenrow' : 'oddrow'));
198 foreach ($row as $col) {
199 $tr->pushContent(HTML::td($col));
201 $html->pushContent($tr);
205 // do paging via pagelink template
206 if (!empty($args['NUMPAGES'])) {
207 $paging = Template("pagelink", $args);
208 $html = $table->pushContent(HTML::thead($paging),
210 HTML::tfoot($paging));
212 if (0 and DEBUG) { // test deferred error/warning/notice collapsing
213 trigger_error("test notice", E_USER_NOTICE);
214 trigger_error("test warning", E_USER_WARNING);
227 // c-hanging-comment-ender-p: nil
228 // indent-tabs-mode: nil