1<?php
2class DbWrapper {
3	private $dbtype = null;
4	private $serverName = null;
5	private $userName = null;
6	private $passWord = null;
7	private $dbName = null;
8	private $integrator;
9	public function __construct(DokuWiki_Syntax_Plugin $integrator, $dbtype, $serverName, $userName, $passWord, $dbName) {
10		$this->dbtype = $dbtype;
11		$this->serverName = $serverName;
12		$this->userName = $userName;
13		$this->passWord = $passWord;
14		$this->dbName = $dbName;
15		$this->integrator = $integrator;
16	}
17	public function executeQuery($query) {
18		if ($query !== '') {
19
20			echo <<<EOT
21<div style="padding-top: 1em; margin-top: 1em; border-top: 1px solid #888888;">
22EOT;
23
24			switch ($this->dbtype) {
25				case "mysql" :
26					$this->_mysql ( $this->serverName, $this->userName, $this->passWord, $this->dbName, $query );
27					break;
28				case "oracle" :
29					$this->_oracle ( $this->serverName, $this->userName, $this->passWord, $this->dbName, $query );
30					break;
31				case "postgresql" :
32					$this->_postgresql ( $this->serverName, $this->userName, $this->passWord, $this->dbName, $query );
33					break;
34				default :
35					break;
36			}
37		}
38
39		echo '</div>';
40	}
41	private function _oracle($Server, $User, $Pass, $Database, $Query) {
42		if (! $connection = oci_connect ( $User, $Pass, $Server ) or false)
43			throw new Exception ( oci_error () );
44
45			// execute query
46		$rs = oci_parse ( $connection, $Query );
47		oci_execute ( $rs );
48		$dbArray = array ();
49
50		if ($rs === true)
51			$dbArray [] = array (
52					$this->aString ["affected"] => oci_affected_rows ( $connection )
53			);
54		else
55			while ( $row = oci_fetch_assoc ( $rs ) )
56				$dbArray [] = $row;
57
58		oci_free_statement ( $rs );
59		oci_close ( $connection );
60		return $dbArray;
61	}
62	private function _mysql($serverName, $userName, $passWord, $dbName, $query) {
63		try {
64
65			$db = new mysqli ( $serverName, $userName, $passWord, $dbName );
66			mysqli_autocommit ( $db, TRUE );
67			mysqli_set_charset ( $db, 'utf8' ); // muss auf UTF8 umgesetzt werden
68			mysqli_select_db ( $db, $dbName ); // DB-Auswahl
69
70			if (! $db) {
71				echo '<div class="error">' . $this->getLang ( 'errordbconnection' ) . '</div>';
72			}
73			if ($db->multi_query ( $query )) {
74				echo '<u>' . $this->getLang ( 'firstresult' ) . '</u><br>';
75				do {
76					$result = $db->store_result ();
77					if ($result instanceof mysqli_result) {
78						try {
79							$rows = $this->mysql_fetch_all ( $result );
80							if (sizeof ( $rows ) <= 0) {
81								echo $this->getLang ( 'emptyresult' );
82							}
83							$cols = empty ( $first ) ? array () : array_keys ( $first );
84
85							echo $this->__renderTable ( $cols, $rows );
86						} catch ( Exception $e ) {
87							echo '<div class="error">' . $e->getMessage () . '</div>';
88						} finally {
89							$result->free ();
90						}
91					} else if (is_bool ( $result )) {
92						$err = mysqli_errno ( $db );
93						echo $this->getLang ( 'queryexecuted' ) . ($err != 0 ? $this->getLang ( 'no' ) : $this->getLang ( 'yes' ));
94					} else
95						var_dump ( $result );
96
97					if ($db->more_results ()) {
98						echo '<br><u>' . $this->getLang ( 'nextresult' ) . '</u><br>';
99					}
100				} while ( $db->next_result () );
101			} else {
102				echo $this->getLang ( 'queriesnotexecuted' );
103			}
104		} catch ( Exception $e ) {
105			echo '<div class="error">' . $e->getMessage () . '</div>';
106		} finally {
107			if ($db) {
108				$db->close ();
109			}
110		}
111	}
112	private function _postgresql($serverName, $userName, $passWord, $dbName, $query) {
113		$hostAndPort [] = explode ( ":", $serverName );
114
115		if (count ( $hostAndPort [0] ) == 2)
116			$url = "host=" . $hostAndPort [0] [0] . " port=" . $hostAndPort [0] [1] . " dbname=" . $dbName . " user=" . $userName . " password=" . $passWord;
117		else
118			$url = "host=" . $serverName . " dbname=" . $dbName . " user=" . $userName . " password=" . $passWord;
119
120			// $rs = pg_exec ( $query );
121			// $dbArray = pg_fetch_array ( $result, NULL, PGSQL_ASSOC );
122
123		// pg_close ( $connection );
124		try {
125
126			$db = pg_connect ( $url );
127
128			pg_setclientencoding ( $db, 'utf8' );
129			// mysqli_autocommit ( $db, TRUE );
130
131			if (! $db) {
132				echo '<div class="error">' . $this->getLang ( 'errordbconnection' ) . '</div>';
133			}
134			$result = pg_query ( $db, $query );
135			if ($result) {
136				echo '<u>' . $this->getLang ( 'firstresult' ) . '</u><br>';
137				// if ($result instanceof mysqli_result) {
138
139				$rows = pg_fetch_all ( $result );
140
141				if ($rows == false || sizeof ( $rows ) <= 0) {
142					echo $this->getLang ( 'emptyresult' );
143				} else {
144					$cols = empty ( $first ) ? array () : array_keys ( $first );
145
146					echo $this->__renderTable ( $cols, $rows );
147				}
148
149				// } else if (is_bool ( $result )) {
150				// $err = mysqli_errno ( $db );
151				// echo $this->getLang ( 'queryexecuted' ) . ($err != 0 ? $this->getLang ( 'no' ) : $this->getLang ( 'yes' ));
152				// } else
153				// var_dump ( $result );
154
155				// if ($db->more_results ()) {
156				// echo '<br><u>' . $this->getLang ( 'nextresult' ) . '</u><br>';
157				// }
158			} else {
159				echo $this->getLang ( 'queriesnotexecuted' );
160			}
161		} catch ( Exception $e ) {
162			echo '<div class="error">' . $e->getMessage () . '</div>';
163		} finally {
164			if ($db) {
165				pg_close ( $db );
166			}
167		}
168	}
169	private function mysql_fetch_all($result) {
170		if (method_exists ( 'mysqli_result', 'fetch_all' )) // Compatibility layer with PHP < 5.3
171			$res = mysqli_fetch_all ( $result, MYSQLI_ASSOC );
172		else
173			for($res = array (); $tmp = mysqli_fetch_array ( $result, MYSQLI_ASSOC );)
174				$res [] = $tmp;
175
176		return $res;
177	}
178	private function pg_fetch_all($result) {
179		for($res = array (); $tmp = pg_fetch_array ( $result, NULL, PGSQL_ASSOC );)
180			$res [] = $tmp;
181
182		return $res;
183	}
184	private function __renderTable($cols, $rows) {
185
186		/*
187		 * - collect header row according to listed rows
188		 * - transform all listed rows to properly defined HTML table cells
189		 */
190		$headers = array ();
191		$meta = array ();
192		$counter = 0;
193
194		foreach ( $rows as $nr => $row ) {
195
196			// convert all values in current row to table cells
197			$i = 0;
198
199			foreach ( $row as $column => $value ) {
200
201				$def = $meta [$column];
202
203				$headers [$column] = $def ['label'] ? $def ['label'] : $column;
204				$class = $def ['format'] ? $def ['format'] : 'na';
205				$class .= ' col' . ++ $i;
206
207				$value = $this->valueFromDB ( $column, $value, $def );
208
209				$cell = $this->renderValue ( $column, $value, $def, false, false );
210
211				$row [$column] = "<td class=\"$class\">" . $cell . "</td>\n";
212			}
213
214			// prepend cell for counter
215			array_unshift ( $row, '<td class="counter col0 rightalign">' . ++ $counter . "</td>\n" );
216
217			// convert set of values into HTML table row
218			$classes = array ();
219
220			if ($nr == 0)
221				$classes [] = 'first';
222			if ($nr == count ( $rows ) - 1)
223				$classes [] = 'last';
224
225			$classes [] = ($nr % 2) ? 'even' : 'odd';
226			$classes [] = 'row' . ($nr + 1);
227
228			$classes = implode ( ' ', $classes );
229
230			$rows [$nr] = '<tr class="' . $classes . '">' . implode ( '', $row ) . "</tr>\n";
231		}
232
233		// finally convert all HTML table rows into single HTML table body
234		$rows = implode ( '', $rows );
235
236		/*
237		 * compile header row
238		 */
239
240		// ensure to have row of headers (missing on an empty list of rows)
241		if (empty ( $headers ))
242			foreach ( $cols as $column ) {
243
244				unset ( $def );
245
246				if (is_array ( $meta [$column] ))
247					$def = $meta [$column];
248				else {
249					// missing meta information on current "column name"
250					// --> might be an alias definition
251					// --> extract originally selected column name from that
252
253					$pos = strripos ( $column, ' AS ' );
254					if ($pos !== false) {
255
256						$temp = substr ( $column, $pos + 4 );
257						if ($meta [$temp]) {
258							// found definition on extracted column name
259
260							$def = $meta [$temp];
261							$column = $temp;
262						}
263					}
264				}
265
266				$headers [$column] = $def ['label'] ? $def ['label'] : $column;
267			}
268
269		foreach ( $headers as $column => $label ) {
270
271			$headers [$column] = "<th class=\"label\">$label</th>\n";
272		}
273
274		// compile row of header cells
275		$headers = implode ( '', $headers );
276
277		/*
278		 * render list of rows as HTML table
279		 */
280
281		$width = intval ( $this->options ['width'] ) ? ' width="' . $this->options ['width'] . '"' : '';
282
283		$trClass = $this->options ['wikistyle'] ? '' : ' class="data-list"';
284		$tableClass = $this->options ['wikistyle'] ? ' class="inline"' : '';
285
286		$table = <<<EOT
287<div style="overflow-x: scroll;">
288   <table width="100%"$tableClass>
289    <thead>
290     <tr class="row0">
291      <th class="counter"></th>
292      $headers
293     </tr>
294    </thead>
295    <tbody>
296     $rows
297    </tbody>
298   </table>
299</div>
300EOT;
301
302		return $table;
303	}
304	protected function valueFromDB($column, $value, $def) {
305		switch ($def ['format']) {
306
307			case 'image' :
308			case 'file' :
309				if (is_null ( $value ))
310					return null;
311
312				if ($value === '||')
313					return null;
314
315					// parse file for internally used structure
316				$a = strpos ( $value, '|' );
317				if (! $a)
318					// externally provided file --> don't touch
319					return (strlen ( $value ) > 0);
320
321				$b = strpos ( $value, '|', $a + 1 );
322				if (! $b)
323					// externally provided file --> don't touch
324					return true;
325
326				$temp = array (
327						'mime' => substr ( $value, 0, $a ),
328						'name' => substr ( $value, $a + 1, $b - $a - 1 ),
329						'file' => substr ( $value, $b + 1 )
330				);
331
332				if (! preg_match ( '#^[a-z0-9-]+/[+a-z0-9-]+$#i', $temp ['mime'] ))
333					// externally provided file --> don't touch
334					return true;
335
336				if (trim ( $temp ['name'] ) === '')
337					// externally provided file --> don't touch
338					return true;
339
340				return $temp;
341
342			case 'date' :
343				if ($def ['options'] ['unixts'])
344					return $value;
345
346				if ((trim ( $value ) === '') || ($value == '0000-00-00'))
347					return 0;
348
349				return self::parseDBDateTime ( $value, true );
350
351			case 'time' :
352				return $value;
353
354			case 'datetime' :
355				if ($def ['options'] ['unixts'])
356					return $value;
357
358				$value = substr ( $value, 0, 19 );
359				if ((trim ( $value ) === '') || ($value == '0000-00-00T00:00:00') || ($value == '0000-00-00 00:00:00'))
360					return 0;
361
362				return self::parseDBDateTime ( $value, false );
363
364			case 'bool' :
365				$value = trim ( $value );
366				switch ($def ['options'] ['booltype']) {
367
368					case 'int' :
369						return (intval ( $value ) != 0);
370
371					case 'xmark' :
372						return (strtolower ( $value [0] ) == 'x');
373
374					case 'yesno' :
375					default :
376						return (strtolower ( $value [0] ) == 'y');
377				}
378
379			case 'enum' :
380				$value = trim ( $value );
381				$value = array_search ( $value, $def ['options'] ['selectables'] );
382				if ($value === false)
383					$value = null;
384				else
385					$value = intval ( $value );
386
387				break;
388
389			case 'related' :
390				if (is_numeric ( $value ))
391					$value = intval ( $value );
392
393				break;
394
395			case 'monetary' :
396			case 'real' :
397			/**
398			 *
399			 * @todo manage decimal point conversions
400			 */
401
402			case 'url' :
403			case 'email' :
404			case 'phone' :
405			case 'fax' :
406			case 'text' :
407			case 'integer' :
408			// keep value as is ...
409		}
410
411		return $value;
412	}
413	protected function renderValue($column, $value, $def, $mayBeSkipped = false, $inEditor = false) {
414		if (is_null ( $value ) && ($def ['type'] != 'data'))
415			return $mayBeSkipped ? null : '';
416
417		switch ($def ['format']) {
418
419			case 'text' :
420				return trim ( $value );
421
422			case 'image' :
423			case 'file' :
424				if ($value === true)
425					return '<span class="info">' . $this->getLang ( 'fileexternalfound' ) . '</span>';
426				else if ($value === false)
427					return '<span class="info">' . $this->getLang ( 'fileexternalnotfound' ) . '</span>';
428				else {
429
430					$temp = is_array ( $value ) ? trim ( implode ( '', $value ) ) : '';
431
432					if (! $inEditor)
433						$url .= '&thumb=150';
434
435					if ($def ['format'] === 'image')
436						return "<img src=\"$url\" alt=\"" . sprintf ( $this->getLang ( 'fileimagealt' ), $column, $value ['mime'] ) . "\" />";
437
438					return "<a href=\"$url\" title=\"" . $this->getLang ( 'filedlhint' ) . '">' . $this->getLang ( 'cmddl' ) . '</a>';
439				}
440				break;
441
442			case 'email' :
443				if ((trim ( $value ) === '') && $mayBeSkipped)
444					return null;
445
446				return DokuWiki_Plugin::email ( $value, $email );
447
448			case 'url' :
449				if ((trim ( $value ) === '') && $mayBeSkipped)
450					return null;
451
452				return DokuWiki_Plugin::external_link ( $value );
453
454			case 'phone' :
455			case 'fax' :
456				if ((trim ( $value ) === ''))
457					return null;
458
459				return $value;
460
461			case 'bool' :
462				if (! $value)
463					return null;
464				return $value ? '&#10007;' : '&ndash;';
465
466			case 'date' :
467				if (! $value)
468					return $mayBeSkipped ? null : '';
469
470				return strftime ( strtok ( trim ( $this->getConf ( 'dformat' ) ), ' ' ), $value );
471
472			case 'datetime' :
473				if (! $value)
474					return $mayBeSkipped ? null : '';
475
476				return strftime ( $this->getConf ( 'dformat' ), $value );
477
478			case 'time' :
479				if ($value && (substr ( $value, - 3 ) == ':00') && (strlen ( $value ) > 5))
480					$value = substr ( $value, 0, - 3 );
481
482				return $value;
483
484			case 'integer' :
485			case 'monetary' :
486			case 'real' :
487				if (! $value && $mayBeSkipped)
488					return null;
489
490				return $value;
491
492			case 'enum' :
493			case 'related' :
494				if (is_integer ( $value ))
495					$value = $def ['options'] ['selectables'] [$value];
496
497				return $value;
498
499			default :
500				return $value;
501		}
502	}
503
504	/**
505	 * Retrieves localized string.
506	 *
507	 * @param string $name
508	 *        	name of localized string
509	 * @return mixed retrieved localized string
510	 */
511	public function getLang($name) {
512		return $this->integrator->getLang ( $name );
513	}
514}
515?>