2 rcs_id('$Id: SqlResult.php,v 1.4 2004-09-17 14:23:21 rurban Exp $');
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 * lib/plugin/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 class WikiPlugin_SqlResult
65 return _("SqlResult");
68 function getDescription () {
69 return _("Display arbitrary SQL result tables");
72 function getVersion() {
73 return preg_replace("/[Revision: $]/", '',
77 function getDefaultArguments() {
79 'alias' => false, // DSN database specification
80 'ordered' => false, // if to display as <ol> list: single col only without template
81 'template' => false, // use a custom <theme>/template.tmpl
82 'where' => false, // custom filter for the query
83 'sortby' => false, // for paging, default none
84 'limit' => "0,50", // for paging, default: only the first 50
88 function getDsn($alias) {
89 $ini = parse_ini_file(FindFile("lib/plugin/SqlResult.ini"));
93 /** Get the SQL statement from the rest of the lines
95 function handle_plugin_args_cruft($argstr, $args) {
96 $this->_sql = str_replace("\n"," ",$argstr);
100 function run($dbi, $argstr, &$request, $basepage) {
102 //$request->setArg('nocache','1');
103 extract($this->getArgs($argstr, $request));
105 return $this->error(_("No DSN alias for SqlResult.ini specified"));
108 // apply custom filters
109 if ($where and strstr($sql, "%%where%%"))
110 $sql = str_replace("%%where%%", $where, $sql);
111 // TODO: use a SQL construction library?
113 $limit = PageList::limit($limit);
114 if (strstr($sql, "%%limit%%"))
115 $sql = str_replace("%%limit%%", $limit, $sql);
117 if (strstr($sql, "LIMIT"))
118 $sql = preg_replace("/LIMIT\s+[\d,]+\s+/m", "LIMIT ".$limit." ", $sql);
121 if (strstr($sql, "%%sortby%%")) {
123 $sql = preg_replace("/ORDER BY .*%%sortby%%\s/m", "", $sql);
125 $sql = str_replace("%%sortby%%", $sortby, $sql);
126 } else { // add sorting: support paging sortby links
127 if (preg_match("/\sORDER\s/",$sql))
128 $sql = preg_replace("/ORDER BY\s\S+\s/m", "ORDER BY " . PageList::sortby($sortby,'db'), $sql);
130 $sql .= "ORDER BY " . PageList::sortby($sortby,'db');
133 $inidsn = $this->getDsn($alias);
135 return $this->error(sprintf(_("No DSN for alias %s in SqlResult.ini found"),
137 // adodb or pear? adodb as default, since we distribute per default it.
138 // for pear there may be overrides.
139 if ($DBParams['dbtype'] == 'SQL') {
140 $dbh = DB::connect($inidsn);
141 $all = $dbh->getAll($sql);
143 if ($DBParams['dbtype'] != 'ADODB') {
144 // require_once('lib/WikiDB/adodb/adodb-errorhandler.inc.php');
145 require_once('lib/WikiDB/adodb/adodb.inc.php');
147 $parsed = parseDSN($inidsn);
148 $dbh = &ADONewConnection($parsed['phptype']);
149 $conn = $dbh->Connect($parsed['hostspec'],$parsed['username'],
150 $parsed['password'], $parsed['database']);
151 $GLOBALS['ADODB_FETCH_MODE'] = ADODB_FETCH_ASSOC;
152 $dbh->SetFetchMode(ADODB_FETCH_ASSOC);
154 $all = $dbh->getAll($sql);
156 $GLOBALS['ADODB_FETCH_MODE'] = ADODB_FETCH_NUM;
157 $dbh->SetFetchMode(ADODB_FETCH_NUM);
160 if ($limit) { // fill paging vars (see PageList)
161 $args = $this->pagingTokens(count($all), count($all[0]), $limit);
162 if (!$args) $args = array();
167 array('SqlResult' => $all, // the resulting array of rows
168 'ordered' => $ordered, // whether to display as <ul>/<dt> or <ol>
172 $args); // paging params override given params
173 return Template($template, $args);
176 $html = HTML::ol(array('class'=>'sqlresult'));
177 foreach ($all as $row) {
178 $html->pushContent(HTML::li(array('class'=> $i++ % 2 ? 'evenrow' : 'oddrow'), $row[0]));
181 $html = HTML::table(array('class'=>'sqlresult'));
183 foreach ($all as $row) {
184 $tr = HTML::tr(array('class'=> $i++ % 2 ? 'evenrow' : 'oddrow'));
185 foreach ($row as $col) {
186 $tr->pushContent(HTML::td($col));
188 $html->pushContent($tr);
192 // do paging via pagelink template
193 if (args['NUMPAGES']) {
194 $paging = Template("pagelink", $args);
195 $html = $table->pushContent(HTML::thead($paging),
197 HTML::tfoot($paging));
204 // $Log: not supported by cvs2svn $
205 // Revision 1.3 2004/09/06 08:36:28 rurban
206 // support templates, with some vars
208 // Revision 1.2 2004/05/03 21:57:47 rurban
209 // locale updates: we previously lost some words because of wrong strings in
210 // PhotoAlbum, german rewording.
211 // fixed $_SESSION registering (lost session vars, esp. prefs)
212 // fixed ending slash in listAvailableLanguages/Themes
214 // Revision 1.1 2004/05/03 20:44:58 rurban
215 // fixed gettext strings
216 // new SqlResult plugin
217 // _WikiTranslation: fixed init_locale
225 // c-hanging-comment-ender-p: nil
226 // indent-tabs-mode: nil