3 * Copyright © 2004 $ThePhpWikiProgrammingTeam
5 * This file is part of PhpWiki.
7 * PhpWiki is free software; you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation; either version 2 of the License, or
10 * (at your option) any later version.
12 * PhpWiki is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
17 * You should have received a copy of the GNU General Public License along
18 * with PhpWiki; if not, write to the Free Software Foundation, Inc.,
19 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
21 * SPDX-License-Identifier: GPL-2.0-or-later
26 * This plugin displays results of arbitrary SQL select statements
28 * The database definition, the DSN, must be defined in the local file
29 * config/SqlResult.ini
30 * A simple textfile with alias = dsn lines.
32 * Optional template file to format the result and handle some logic.
33 * Template vars: %%where%%, %%sortby%%, %%limit%%
37 * <<SqlResult alias=mysql
38 * SELECT 'mysql password for string "xx":',
41 * <<SqlResult alias=videos template=videos
42 * SELECT rating,title,date
44 * ORDER BY rating DESC
47 * <<SqlResult alias=imdb template=imdbmovies where||="Davies, Jeremy%"
48 * SELECT m.title, m.date, n.name, c.role
49 * FROM movies as m, names as n, jobs as j, characters as c
50 * WHERE n.name LIKE "%%where%%"
51 * AND m.title_id = c.title_id
52 * AND n.name_id = c.name_id
53 * AND c.job_id = j.job_id
54 * AND j.description = 'Actor'
55 * ORDER BY m.date DESC
58 * @author: Reini Urban
61 require_once 'lib/PageList.php';
63 class WikiPlugin_SqlResult
69 function getDescription()
71 return _("Display arbitrary SQL result tables.");
74 function getDefaultArguments()
77 'alias' => false, // DSN database specification
78 'ordered' => false, // if to display as <ol> list: single col only without template
79 'template' => false, // use a custom <theme>/template.tmpl
80 'where' => false, // custom filter for the query
81 'sortby' => false, // for paging, default none
82 'limit' => "0,50", // for paging, default: only the first 50
86 function getDsn($alias)
88 $ini = parse_ini_file(findFile("config/SqlResult.ini"));
92 /** Get the SQL statement from the rest of the lines
94 function handle_plugin_args_cruft($argstr, $args)
96 $this->_sql = str_replace("\n", " ", $argstr);
101 * @param string $argstr
102 * @param WikiRequest $request
103 * @param string $basepage
106 function run($dbi, $argstr, &$request, $basepage)
109 //$request->setArg('nocache','1');
110 extract($this->getArgs($argstr, $request));
112 return $this->error(_("No DSN alias for SqlResult.ini specified"));
115 // apply custom filters
116 if ($where and strstr($sql, "%%where%%"))
117 $sql = str_replace("%%where%%", $where, $sql);
118 // TODO: use a SQL construction library?
120 $pagelist = new PageList();
121 $limit = $pagelist->limit($limit);
122 if (strstr($sql, "%%limit%%"))
123 $sql = str_replace("%%limit%%", $limit, $sql);
125 if (strstr($sql, "LIMIT"))
126 $sql = preg_replace("/LIMIT\s+[\d,]+\s+/m", "LIMIT " . $limit . " ", $sql);
129 if (strstr($sql, "%%sortby%%")) {
131 $sql = preg_replace("/ORDER BY .*%%sortby%%\s/m", "", $sql);
133 $sql = str_replace("%%sortby%%", $sortby, $sql);
134 } elseif (PageList::sortby($sortby, 'db')) { // add sorting: support paging sortby links
135 if (preg_match("/\sORDER\s/", $sql))
136 $sql = preg_replace("/ORDER BY\s\S+\s/m", "ORDER BY " . PageList::sortby($sortby, 'db'), $sql);
138 $sql .= " ORDER BY " . PageList::sortby($sortby, 'db');
141 $inidsn = $this->getDsn($alias);
143 return $this->error(sprintf(_("No DSN for alias %s in SqlResult.ini found"),
145 // adodb or pear? adodb as default, since we distribute per default it.
146 // for pear there may be overrides.
147 // TODO: native PDO support (for now we use ADODB)
148 if ($DBParams['dbtype'] == 'SQL') {
149 $dbh = DB::connect($inidsn);
150 $all = $dbh->getAll($sql);
151 if (DB::isError($all)) {
152 return $this->error($all->getMessage() . ' ' . $all->userinfo);
154 } else { // unless PearDB use the included ADODB, regardless if dba, file or PDO, ...
155 if ($DBParams['dbtype'] != 'ADODB') {
156 require_once 'lib/WikiDB/backend/ADODB.php';
158 $parsed = parseDSN($inidsn);
159 $dbh = &ADONewConnection($parsed['phptype']);
160 $conn = $dbh->Connect($parsed['hostspec'], $parsed['username'],
161 $parsed['password'], $parsed['database']);
163 return $this->error($dbh->errorMsg());
164 $GLOBALS['ADODB_FETCH_MODE'] = ADODB_FETCH_ASSOC;
165 $dbh->SetFetchMode(ADODB_FETCH_ASSOC);
167 $all = $dbh->getAll($sql);
169 $GLOBALS['ADODB_FETCH_MODE'] = ADODB_FETCH_NUM;
170 $dbh->SetFetchMode(ADODB_FETCH_NUM);
172 return $this->error($dbh->errorMsg());
175 if ($limit) { // fill paging vars (see PageList)
176 $args = $pagelist->pagingTokens(count($all), count($all[0]), $limit);
177 if (!$args) $args = array();
182 array('SqlResult' => $all, // the resulting array of rows
183 'ordered' => $ordered, // whether to display as <ul>/<dt> or <ol>
187 $args); // paging params override given params
188 return Template($template, $args);
191 $html = HTML::ol(array('class' => 'sqlresult'));
193 foreach ($all as $row) {
194 $html->pushContent(HTML::li(array('class' => $i++ % 2 ? 'evenrow' : 'oddrow'), $row[0]));
197 $html = HTML::table(array('class' => 'sqlresult'));
200 foreach ($all as $row) {
201 $tr = HTML::tr(array('class' => $i++ % 2 ? 'evenrow' : 'oddrow'));
203 foreach ($row as $col) {
204 $tr->pushContent(HTML::td($col));
206 $html->pushContent($tr);
210 // do paging via pagelink template
211 if (!empty($args['NUMPAGES'])) {
212 $paging = Template("pagelink", $args);
213 $html = $table->pushContent(HTML::thead($paging),
215 HTML::tfoot($paging));