1<?php
2/**
3 * DokuWiki Plugin sqlcomp (Syntax Component)
4 *
5 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html
6 * @author  Oliver Geisen <oliver@rehkopf-geisen.de>
7 * @author  Christoph Lang <calbity@gmx.de>
8 */
9
10// must be run within Dokuwiki
11if (!defined('DOKU_INC')) die();
12
13class syntax_plugin_sqlcomp extends DokuWiki_Syntax_Plugin {
14    /**
15     * @return string Syntax mode type
16     */
17    public function getType() {
18        return 'substition';
19    }
20    /**
21     * @return string Paragraph type
22     */
23    public function getPType() {
24        return 'block';
25    }
26    /**
27     * @return int Sort order - Low numbers go before high numbers
28     */
29    public function getSort() {
30        return 267;
31    }
32
33    /**
34     * Connect lookup pattern to lexer.
35     *
36     * @param string $mode Parser mode
37     */
38    public function connectTo($mode) {
39//        $this->Lexer->addEntryPattern('<FIXME>',$mode,'plugin_sqlcomp');
40        $this->Lexer->addSpecialPattern('\[\[mysql\:.*?\]\]', $mode, 'plugin_sqlcomp');
41        $this->Lexer->addSpecialPattern('\[\[mssql\:.*?\]\]', $mode, 'plugin_sqlcomp');
42        $this->Lexer->addSpecialPattern('\[\[oracle\:.*?\]\]', $mode, 'plugin_sqlcomp');
43        $this->Lexer->addSpecialPattern('\[\[sqlite\:.*?\]\]', $mode, 'plugin_sqlcomp');
44        $this->Lexer->addSpecialPattern('\[\[sqlaccess\:.*?\]\]', $mode, 'plugin_sqlcomp');
45        $this->Lexer->addSpecialPattern('\[\[postgresql\:.*?\]\]', $mode, 'plugin_sqlcomp');
46        $this->Lexer->addSpecialPattern('\[\[sqlcsv\:.*?\]\]', $mode, 'plugin_sqlcomp');
47
48        $aliases = $this->_getAliases();
49        if ($aliases) {
50            foreach($aliases as $name => $def) {
51                $this->Lexer->addSpecialPattern('\[\['.$name.'\|.*?\]\]', $mode, 'plugin_sqlcomp');
52            }
53        }
54    }
55
56//    public function postConnect() {
57//        $this->Lexer->addExitPattern('</FIXME>','plugin_sqlcomp');
58//    }
59
60    /**
61     * Handle matches of the sqlcomp syntax
62     *
63     * @param string          $match   The match of the syntax
64     * @param int             $state   The state of the handler
65     * @param int             $pos     The position in the document
66     * @param Doku_Handler    $handler The handler
67     * @return array Data for the renderer
68     */
69    public function handle($match, $state, $pos, Doku_Handler $handler){
70        $data = array();
71
72        $match = substr($match,2,-2);
73        $match = explode("|",$match); # CONNECTION, SQL, OPTIONS
74
75        // replace dbaliases with connection-string from config
76        $dbaliases = $this->_getAliases();
77        foreach($dbaliases as $key => $value) {
78            if($key == strtolower($match[0])) {
79                $match[0] = $value;
80            }
81        }
82        $con = explode(":",$match[0]); # DBTYPE, DBSERVER, DBUSER, DBPASS, DBNAME
83        if(count($con) != 5) {
84            msg($this->getLang("syntax_dbcon"), -1);
85            return;
86        }
87        $data[] = $con;
88
89        $data[] = $match[1]; # SQL (multiline)
90
91        $opts = array();
92        if(isset($match[2])) {
93            // handle options
94            $o = explode("&", $match[2]);
95            foreach($o as $opt) {
96                $opt = strtolower($opt);
97                if(ctype_digit($opt)) {
98                    $opts['refresh'] = $opt;
99                }
100                else {
101                    msg($this->getLang("syntax_option").': "'.$opt.'"', -1);
102                    return;
103                }
104            }
105        } else {
106            // apply defaults
107            $opts['refresh'] = $this->getConf('default_refresh');
108        }
109        $data[] = $opts;
110
111        return $data;
112    }
113
114    /**
115     * Render xhtml output or metadata
116     *
117     * @param string         $mode      Renderer mode (supported modes: xhtml)
118     * @param Doku_Renderer  $renderer  The renderer
119     * @param array          $data      The data from the handler() function
120     * @return bool If rendering was successful.
121     */
122    public function render($mode, Doku_Renderer $renderer, $data) {
123        if($mode != 'xhtml') return false;
124        $renderer->doc .= utf8_encode($this->_query($data));
125        return true;
126    }
127
128
129    //------------------------------------------------------------------------//
130    // SQLCOMP FUNCTONS
131    //------------------------------------------------------------------------//
132
133    /**
134     * Layout
135     */
136    private $aMessages = array(
137        "error" => "<div id=\"error\" style=\"text-align:center; font-weight: bold; border: 2px solid #0f0;background-color: #f00; padding: 5px; margin: 5px\">%text%</div>\n",
138        "message" => "<div id=\"difference\" style=\"text-align:center; font-weight: bold; border: 2px solid #fd0;background-color: #ffd; padding: 5px; margin: 5px\">%text%</div>\n",
139        "pre" => "<table class=\"inline\">\n",
140        "post" => "</table>\n",
141        #"th" => "<th class=\"row%number%\" style=\"%type%\">%text%</th>",
142        "th" => "<th class=\"row%number%\" style=\"text-align:center;%type%\">%text%</th>",
143        "td" => "<td class=\"col%number%\" style=\"%type%\">%text%</td>",
144        "tr" => "<tr class=\"row%number%\" style=\"%type%\">%text%</tr>\n",
145        "same" => "",
146        "new" => "border:2px solid green;",
147        "old" => "color:#a0a0a0; text-decoration:line-through;",
148        #"deleted" => "border:2px solid red;",
149        "deleted" => "background:red; text-decoration:line-through;",
150        #"changed" => "border:2px solid blue;"
151        "changed" => "background:#F2EA0D;",
152    );
153
154    private function _getAliases() {
155        $aliases = trim($this->getConf('dbaliases'));
156        if ($aliases == '') return;
157
158        $data = array();
159        $aliases = explode("\r", $aliases);
160        foreach($aliases as $rec) {
161            if (substr_count($rec, '=') == 1) {
162                list($name, $def) = explode('=', trim($rec));
163                $name = strtolower($name);
164                $data[$name] = $def;
165            }
166        }
167        return($data);
168    }
169
170    private function _error($text){
171      return str_replace("%text%",$text,$this->aMessages["error"]);
172    }
173
174    private function _message($text){
175      return str_replace("%text%",$text,$this->aMessages["message"]);
176    }
177
178    private function _debug($data){
179        $sResponse = "<pre>";
180        if (is_array($data) && !empty($data)) {
181            foreach($data as $key => $value){
182                $sResponse .= "".$key . "=> " .$value ."<br/>\n";
183            }
184        } else {
185            $sResponse .= "data IS EMPTY";
186        }
187        $sResponse .= "</pre>";
188        return $sResponse;
189    }
190
191    private function _load($filename){
192
193        $Cache = null;
194        $Update = true;
195        if(file_exists($filename)){
196          $Cache = file_get_contents($filename);
197          $Cache = unserialize($Cache);
198
199          $Update = $Cache["Update"];
200          if(time() > $Update)
201            $Update = true;
202          else
203            $Update = false;
204          $Cache = $Cache["Table"];
205        }
206
207        return array($Update,$Cache);
208    }
209
210    private function _save($filename,$rs,$timestamp){
211        $timestamp = (time() + ($timestamp*60));
212        $Cache["Update"] = $timestamp;
213        $Cache["Table"] = $rs;
214
215        $Cache = serialize($Cache);
216
217        $handle = fopen($filename,"w");
218        fwrite($handle,$Cache);
219        fclose($handle);
220    }
221
222    private function _query($data,$type=null) {
223        #return $this->_debug($data);
224
225        if(!is_array($data) || count($data) != 3) {
226            msg($this->_error($this->getLang("wrong")), -1);
227            return;
228        }
229
230        $dbcon = $data[0];
231        $sql = $data[1];
232        $opts = $data[2];
233
234        $Update = false;
235        if($opts['refresh'] > 0) {
236            $sPath = DOKU_INC . "data/cache/sql/";
237            if(!is_dir($sPath)) {
238                if (!@mkdir($sPath)) {
239                    msg($this->_error($this->getLang("cachedir")), -1);
240                    return;
241                }
242            }
243            $filename = $sPath.md5(implode('',$dbcon));
244            $Cache = $this->_load($filename);
245            $Update = true;
246            if(is_array($Cache)){
247              $Update = $Cache[0];
248              $Cache = $Cache[1];
249            }
250        }
251
252        try{
253          switch($dbcon[0]){
254            case "mysql": $rs = $this->_mysql($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
255            case "mssql": $rs = $this->_mssql($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
256            case "oracle": $rs = $this->_oracle($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
257            case "sqlite": $rs = $this->_sqlite($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
258            case "sqlaccess": $rs = $this->_sqlaccess($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
259            case "postgresql": $rs = $this->_postgresql($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
260            case "sqlcsv": $rs = $this->_sqlcsv($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
261            default: msg($this->_error($this->getLang("nohandler")), -1); return;
262          }
263        }catch(Exception $ex){
264          msg($this->_error($this->getLang("problem")), -1);
265          if(isset($Cache)){
266            msg($this->_error($this->getLang("cache")), -1);
267            return($this->_print($Cache));
268          }
269          return;
270        }
271
272        if ($rs === false){
273          return $this->_error($this->getLang("empty") );
274        }
275
276        if(isset($type) && $type == "csv")
277          return $this->array2csv($rs);
278
279        if($opts['refresh'] > 0) {
280            $difference = $this->_difference($Cache,$rs);
281            $sResponse = $difference[0];
282            $sResponse .= $difference[1];
283        } else {
284            $sResponse = $this->_print($rs);
285        }
286
287        if($opts['refresh'] > 0) {
288            if($Update && isset($rs)){
289              $this->_save($filename,$rs,$data[6]);
290            }
291        }
292
293        return $sResponse;
294    }
295
296    private function _print($array){
297
298        $i = 0;
299
300        $th = "";
301        $td = "";
302        $tr = "";
303        if(!isset($array[0]))
304          return $this->_error($this->getLang("problem"));
305
306        $temp = array_keys($array[0]);
307        foreach($temp as $column){
308          if($column == "type")
309            continue;
310          $th .= str_replace(array("%number%","%text%","%type%"),array(0,$column,""),$this->aMessages["th"]);
311        }
312        $tr = str_replace(array("%number%","%text%","%type%"),array(0,$th,""),$this->aMessages["tr"]);
313
314        foreach($array as $row) {
315
316          $j = 0;
317          $td = "";
318          if(!isset($row["type"]))
319            $row["type"] = $this->aMessages["same"];
320
321          foreach($row as $key => $Value){
322            if($key == "type")
323              continue;
324            $td .= str_replace(array("%number%","%text%","%type%"),array($j,$Value,$row["type"]),$this->aMessages["td"]);
325            $j++;
326          }
327          $tr .= str_replace(array("%number%","%text%","%type%"),array($i,$td,$row["type"]),$this->aMessages["tr"]);
328          $i++;
329        }
330
331        $sResponse = $this->aMessages["pre"];
332        $sResponse .= $tr;
333        $sResponse .= $this->aMessages["post"];
334
335        return $sResponse;
336
337    }
338
339    private function _difference($Cache,$New){
340
341        if($New == $Cache){
342          return array($this->_print($New),"");
343          return array($this->_print($New),$this->_message($this->getLang("same")));
344        }
345
346        if(!isset($New) && isset($Cache))
347          return array($this->_print($Cache),$this->_message($this->getLang("difference")));
348
349        if(isset($New) && !isset($Cache))
350          return array($this->_print($New),$this->_message($this->getLang("first")));
351
352        if(count($New) <= 0)
353          return array($this->_print($Cache),$this->_message($this->getLang("connection")));
354
355        $Max = count($Cache);
356        if(count($New) > count($Cache))
357          $Max = count($New);
358
359        $PrintArray = array();
360
361        for($i=0; $i < $Max; $i++){
362          if(isset($Cache[$i]) && !isset($New[$i]))
363            $PrintArray[] = array_merge($Cache[$i],array("type" => $this->aMessages["deleted"]));
364
365          if(!isset($Cache[$i]) && isset($New[$i]))
366            $PrintArray[] = array_merge($New[$i],array("type" => $this->aMessages["new"]));
367
368          if(isset($Cache[$i]) && isset($New[$i])){
369            if($Cache[$i] != $New[$i]){
370              $PrintArray[] = array_merge($Cache[$i],array("type" => $this->aMessages["changed"]));
371              $PrintArray[] = array_merge($New[$i],array("type" => $this->aMessages["changed"]));
372            }else{
373              $PrintArray[] = array_merge($New[$i],array("type" => $this->aMessages["same"]));
374            }
375          }
376
377        }
378
379        return array($this->_print($PrintArray),$this->_message($this->getLang("difference")));
380    }
381
382    private function _sqlaccess($Server,$User,$Pass,$Database,$Query,$Opts){
383
384        if(!$connection = odbc_connect("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$Database", "ADODB.Connection", $Pass, "SQL_CUR_USE_ODBC") or false)
385          throw new Exception($this->getLang("problem"));
386
387        $rs = odbc_exec($connection,$Query);
388
389        $dbArray = array();
390        while ($row = odbc_fetch_array($rs))
391          $dbArray[] = $row;
392
393        odbc_close($connection);
394        return $dbArray;
395
396    }
397
398    private function _postgresql($Server,$User,$Pass,$Database,$Query,$Opts){
399
400        if(!$connection = pg_connect("host=".$Server." dbname=".$Database." user=".$User." password=".$Pass) or false)
401          throw new Exception($this->getLang("problem"));
402
403        $rs = pg_exec($Query);
404        $dbArray = pg_fetch_array($result, NULL, PGSQL_ASSOC);
405
406        pg_close($connection);
407        return $dbArray;
408
409    }
410
411    private function _mysql($Server,$User,$Pass,$Database,$Query,$Opts){
412        if(!$connection = mysqli_connect($Server, $User, $Pass) or false)
413          throw new Exception(mysqli_connect_error());
414
415        if (!mysqli_select_db($connection, $Database))
416          throw new Exception(mysqli_error($connection));
417
418        $rs = mysqli_query($connection, $Query);
419        $dbArray = array();
420
421        if($rs === true)
422          $dbArray[] = array( $this->getLang("affected") => mysqli_affected_rows ($connection));
423        else
424          while ($row = mysqli_fetch_assoc($rs))
425            $dbArray[] = $row;
426
427
428        mysqli_close($connection);
429        return $dbArray;
430
431    }
432
433    private function _mssql($Server,$User,$Pass,$Database,$Query,$Opts){
434
435        if(!$dbhandle = mssql_connect($Server, $User, $Pass))
436          throw new Exception($this->getLang("problem"));
437
438        mssql_select_db($Database, $dbhandle);
439
440        $rs = mssql_query($Query);
441
442        $dbArray = array();
443
444        if($rs === true)
445          $dbArray[] = array( $this->getLang("affected") => mssql_rows_affected ($connection));
446        else
447          while ($row = mssql_fetch_assoc($rs))
448            $dbArray[] = $row;
449
450        mssql_close($dbhandle);
451        return $dbArray;
452
453    }
454
455    private function _oracle($Server,$User,$Pass,$Database,$Query,$Opts){
456        if(!$connection = oci_connect($User, $Pass, $Server) or false)
457            throw new Exception(oci_error());
458
459        // execute query
460        $rs = oci_parse($connection, $Query);
461        oci_execute($rs);
462        $dbArray = array();
463
464        if($rs === true) {
465            $dbArray[] = array($this->aString["affected"] => oci_affected_rows($connection));
466        }
467        else {
468            while($row = oci_fetch_assoc($rs)) {
469                $dbArray[] = $row;
470            }
471        }
472
473        oci_free_statement($rs);
474        oci_close($connection);
475
476        return $dbArray;
477    }
478
479    private function _sqlcsv($Server,$User,$Pass,$Database,$Query,$Opts){
480
481        if(!$handle = fopen($Database,"r"))
482          throw new Exception($this->getLang("nohandler"));
483
484        $dbArray = array();
485        $keys = fgetcsv ( $handle , 1000, $Query);
486
487        while ($row = fgetcsv ( $handle , 1000, $Query)){
488          $temprow = array();
489          foreach($row as $key => $value)
490            $temprow[$keys[$key]] = $value;
491
492          $dbArray[] = $temprow;
493
494        }
495
496        fclose($handle);
497        return $dbArray;
498
499    }
500
501    private function _sqlite($Server,$User,$Pass,$Database,$Query,$Opts){
502
503        $dbHandle = new PDO('sqlite:'.$Database);
504
505        $result = $dbHandle->query($Query);
506        if(!$result)
507          throw new PDOException;
508        $dbArray = array();
509
510        if($result->rowCount() > 0)
511          $dbArray[] = array( $this->getLang("affected") => $result->rowCount() );
512        else
513          while($row = $result->fetch(PDO::FETCH_ASSOC))
514            $dbArray[] = $row;
515
516        return $dbArray;
517
518    }
519
520    private function array2csv($data){
521
522      $sResponse = "";
523
524      $keys = array_keys($data[0]);
525      $sResponse .= implode(";",$keys)."\n";
526      foreach($data as $row)
527        $sResponse .= implode(";",$row)."\n";
528
529      return $sResponse;
530
531    }
532
533 }
534// vim:ts=4:sw=4:et:
535