1<?php
2/**
3 * Plugin SQL:  executes SQL queries
4 *
5 * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
6 * @author     Slim Amamou <slim.amamou@gmail.com>
7 */
8
9if(!defined('DOKU_INC')) define('DOKU_INC',realpath(dirname(__FILE__).'/../../').'/');
10if(!defined('DOKU_PLUGIN')) define('DOKU_PLUGIN',DOKU_INC.'lib/plugins/');
11require_once(DOKU_PLUGIN.'syntax.php');
12require_once(DOKU_INC.'inc/parserutils.php');
13require_once('DB.php');
14
15function property($prop, $xml)
16{
17	$pattern = '/'.$prop ."='([^']*)')/";
18	if (preg_match($pattern, $xml, $matches)) {
19		return $matches[1];
20	}
21	$pattern = '/'.$prop .'="([^"]*)"/';
22	if (preg_match($pattern, $xml, $matches)) {
23		return $matches[1];
24	}
25	return FALSE;
26}
27
28/**
29 * All DokuWiki plugins to extend the parser/rendering mechanism
30 * need to inherit from this class
31 */
32class syntax_plugin_sql extends DokuWiki_Syntax_Plugin {
33    var $databases = array();
34	var $wikitext_enabled = TRUE;
35	var $display_inline = FALSE;
36	var $vertical_position = FALSE;
37
38    /**
39     * What kind of syntax are we?
40     */
41    function getType(){
42        return 'substition';
43    }
44
45    /**
46     * Where to sort in?
47     */
48    function getSort(){
49        return 555;
50    }
51
52
53    /**
54     * Connect pattern to lexer
55     */
56    function connectTo($mode) {
57      $this->Lexer->addEntryPattern('<sql [^>]*>',$mode,'plugin_sql');
58    }
59
60    function postConnect() {
61      $this->Lexer->addExitPattern('</sql>','plugin_sql');
62    }
63
64
65    /**
66     * Handle the match
67     */
68    function handle($match, $state, $pos, Doku_Handler $handler){
69        switch ($state) {
70          case DOKU_LEXER_ENTER :
71			$urn = property('db',$match);
72			$wikitext = property('wikitext', $match);
73			$display = property('display', $match);
74			$position = property('position', $match);
75			return array('urn' => $urn, 'wikitext' => $wikitext, 'display' => $display, 'position' => $position);
76            break;
77          case DOKU_LEXER_UNMATCHED :
78			$queries = explode(';', $match);
79			if (trim(end($queries)) == "") {
80				array_pop($queries);
81			}
82			return array('sql' => $queries);
83            break;
84          case DOKU_LEXER_EXIT :
85			$this->wikitext_enabled = TRUE;
86			$this->display_inline = FALSE;
87			$this->vertical_position = FALSE;
88			return array('wikitext' => 'enable', 'display' => 'block', 'position' => 'horizontal');
89            break;
90        }
91        return array();
92    }
93
94    /**
95     * Create output
96     */
97    function render($mode, Doku_Renderer $renderer, $data) {
98		$renderer->info['cache'] = false;
99
100        if($mode == 'xhtml'){
101
102			if ($data['wikitext'] == 'disable') {
103				$this->wikitext_enabled = FALSE;
104			} else if ($data['wikitext'] == 'enable') {
105				$this->wikitext_enabled = TRUE;
106			}
107			if ($data['display'] == 'inline') {
108				$this->display_inline = TRUE;
109			} else if ($data['display'] == 'block') {
110				$this->display_inline = FALSE;
111			}
112			if ($data['position'] == 'vertical') {
113				$this->vertical_position = TRUE;
114			} else if ($data['position'] == 'horizontal') {
115				$this->vertical_position = FALSE;
116			}
117			if ($data['urn'] != "") {
118				try {
119					$db =& DB::connect($data['urn']);
120				} catch(Exception $e) {
121					$error = $e->getMessage();
122					$renderer->doc .= '<div class="error">Plugin SQL Error '. $error .'</div>';
123					return TRUE;
124				}
125				array_push($this->databases, $db);
126			}
127			elseif (!empty($data['sql'])) {
128			    $db =& array_pop($this->databases);
129				if (!empty($db)) {
130					foreach ($data['sql'] as $query) {
131						$db->setFetchMode(DB_FETCHMODE_ASSOC);
132						try {
133							$result =& $db->getAll($query);
134						} catch(Exception $e) {
135							$error = $e->getMessage();
136							$renderer->doc .= '<div class="error">Plugin SQL Error '. $error .'</div>';
137							return TRUE;
138						}
139
140							if (! $this->vertical_position) {
141								if ($this->display_inline) {
142									$renderer->doc .= '<table class="inline" style="display:inline"><tbody>';
143								} else {
144									$renderer->doc .= '<table class="inline"><tbody>';
145								}
146								$renderer->doc .= '<tr>';
147								foreach (array_keys($result[0]) as $header) {
148									$renderer->doc .= '<th>';
149									if ($this->wikitext_enabled) {
150										$renderer->nest(p_get_instructions($header));
151									} else {
152										$renderer->cdata($header);
153									}
154									$renderer->doc .= '</th>';
155								}
156								$renderer->doc .= '</tr>';
157								foreach ($result as $row) {
158									$renderer->doc .= '<tr>';
159									foreach ($row as $cell) {
160										$renderer->doc .= '<td>';
161										if ($this->wikitext_enabled) {
162											$renderer->nest(p_get_instructions($cell));
163										} else {
164											$renderer->cdata($cell);
165										}
166										$renderer->doc .= '</td>';
167									}
168									$renderer->doc .= '</td>';
169									$renderer->doc .= '</tr>';
170								}
171								$renderer->doc .= '</tbody></table>';
172							} else {
173								foreach ($result as $row) {
174									$renderer->doc .= '<table class="inline"><tbody>';
175									foreach ($row as $name => $cell) {
176										$renderer->doc .= '<tr>';
177										$renderer->doc .= "<th>$name</th>";
178										$renderer->doc .= '<td>';
179										if ($this->wikitext_enabled) {
180											$renderer->nest(p_get_instructions($cell));
181										} else {
182											$renderer->cdata($cell);
183										}
184										$renderer->doc .= '</td>';
185										$renderer->doc .= '</tr>';
186									}
187									$renderer->doc .= '</tbody></table>';
188								}
189							}
190					}
191				}
192			}
193            return true;
194        }
195        return false;
196    }
197}
198