1<?php
2/**
3 * Plugin SQLRAW:  executes SQL queries on data not in a database
4 *
5 * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
6 * @author     Steven Danz <steven-danz@kc.rr.com>
7 * @author     Gert
8 * @author     Andreas Gohr <gohr@cosmocode.de>
9 * @author     Jerry G. Geiger <JerryGeiger@web.de>
10 * @author     Slim Amamou <slim.amamou@gmail.com>
11 * @author     Tom Cafferty <tcafferty@glocalfocal.com>
12 */
13if(!defined('DOKU_INC')) define('DOKU_INC',realpath(dirname(__FILE__).'/../../../').'/');
14if(!defined('DOKU_PLUGIN')) define('DOKU_PLUGIN',DOKU_INC.'lib/plugins/');
15require_once(DOKU_PLUGIN.'syntax.php');
16require_once(DOKU_INC.'inc/parserutils.php');
17require_once('DB.php');
18require_once('simple_html_dom.php');
19
20/**
21 * All DokuWiki plugins to extend the parser/rendering mechanism
22 * need to inherit from this class
23 */
24class syntax_plugin_sqlraw extends DokuWiki_Syntax_Plugin {
25    var $databases = array();
26	var $display_inline = FALSE;
27	var $vertical_position = FALSE;
28	var $table_class = 'inline';
29    var $colCount = 0;
30
31    /**
32     * What kind of syntax are we?
33     */
34    function getType(){
35        return 'substition';
36    }
37
38    /**
39     * Where to sort in?
40     */
41    function getSort(){
42        return 555;
43    }
44
45    /**
46     * Connect pattern to lexer
47     */
48    function connectTo($mode) {
49      $this->Lexer->addEntryPattern('<sqlraw [^>]*>',$mode,'plugin_sqlraw');
50    }
51
52    function postConnect() {
53      $this->Lexer->addExitPattern('</sqlraw>','plugin_sqlraw');
54    }
55
56    /**
57     * Handle the match
58     */
59    function handle($match, $state, $pos, &$handler){
60        switch ($state) {
61          case DOKU_LEXER_ENTER :
62			$link        = $this->_propertyRaw('link',$match);
63			$startMarker = $this->_propertyRaw('startMarker',$match);
64			$tableNumber = $this->_propertyRaw('tableNumber',$match);
65			$display     = $this->_propertyRaw('display', $match);
66			$position    = $this->_propertyRaw('position', $match);
67			$tableid     = $this->_propertyRaw('id', $match);
68			$class       = $this->_propertyRaw('class', $match);
69			$title       = $this->_propertyRaw('title', $match);
70			$source      = $this->_propertyRaw('source', $match);
71			$caption     = $this->_propertyRaw('caption', $match);
72			$fixTable    = $this->_propertyRaw('fixTable', $match);
73			return array('display' => $display, 'position' => $position, 'id' => $tableid, 'class' => $class, 'title' => $title, 'link' => $link, 'source' => $source, 'startMarker' => $startMarker, 'tableNumber' => $tableNumber, 'caption' => $caption, 'fixTable' => $fixTable);
74            break;
75          case DOKU_LEXER_UNMATCHED :
76			$queries = explode(';', $match);
77			if (trim(end($queries)) == "") {
78				array_pop($queries);
79			}
80			return array('sql' => $queries);
81            break;
82          case DOKU_LEXER_EXIT :
83			$this->display_inline = FALSE;
84			$this->vertical_position = FALSE;
85			$this->table_class = 'inline';
86			$this->source = '';
87			$this->startMarker = '';
88			$this->tableNumber = 1;
89			return array('display' => 'block', 'position' => 'horizontal', 'class' => 'inline', 'id' => ' ', 'source' => ' ', 'startMarker' => '', 'tableNumber' => 1);
90            break;
91        }
92        return array();
93    }
94
95    /**
96     * Create output
97     */
98    function render($mode, &$renderer, $data) {
99        global $conf;
100        global $db;
101
102		$renderer->info['cache'] = false;
103        if($mode == 'xhtml'){
104            //
105            // Get input parameters and configuration data
106            //
107            if ($data['id'] != FALSE)
108				$this->tableId = $data['id'];
109            if ($data['source'] != FALSE)
110				$this->source = $data['source'];
111            if ($data['startMarker'] != FALSE)
112				$this->startMarker = $data['startMarker'];
113            if ($data['tableNumber'] != FALSE)
114                $this->tableNumber = $data['tableNumber'];
115			else
116				$this->tableNumber = 1;
117           if ($data['class'] != FALSE)
118				$this->table_class = $data['class'];
119            if ($data['title'] != FALSE)
120				$this->title = $data['title'];
121			if (isset($data['caption']))
122			    $this->caption = $data['caption'];
123			else
124			    $this->caption = 0;
125			if (isset($data['fixTable']))
126			    $this->fixTable = $data['fixTable'];
127			else
128			    $this->fixTable = 0;
129			if ($data['display'] == 'inline') {
130				$this->display_inline = TRUE;
131			} else if ($data['display'] == 'block') {
132				$this->display_inline = FALSE;
133			}
134			if ($data['position'] == 'vertical') {
135				$this->vertical_position = TRUE;
136			} else if ($data['position'] == 'horizontal') {
137				$this->vertical_position = FALSE;
138			}
139			$debugfile = $this->getConf('sqlraw_debugfilepath');
140			$tempdb = $this->getConf('sqlraw_tempdb');
141			if ($data['link'] != "") {
142    			//
143    			// First try to connect to the temporary database
144    			//
145				$db =& DB::connect($tempdb);
146				if (DB::isError($db)) {
147					$error = $db->getMessage();
148					$renderer->doc .= '<div class="error"> The database error is '. $error .'</div>';
149					return TRUE;
150				} else {
151    				//
152    				// Good, save that temporary database pointer
153    				//
154					array_push($this->databases, $db);
155				}
156				//
157				// Process the link to get the data
158				//
159				$this->datalink = $data['link'];
160				$disallow      = $this->getConf('sqlraw_mysqlDisallow');
161				$use           = $this->getConf('sqlraw_mysqlReplace');
162				$restrictNames = $this->getConf('sqlraw_restrict_names');
163    			$theResult = $this->_sqlRaw__handleLink($data['link'], &$renderer, $this->source, $this->startMarker, $this->tableNumber, $debugfile, $disallow, $use, $restrictNames, $this->caption, $this->fixTable);
164    			if ($theResult != "") {
165        			//
166        			// Good we have data, now retrieve the temporary database pointer and create a temporary table
167        			//
168        			$db =& array_pop($this->databases);
169        			if (!empty($db)) {
170            			$success = $this->_sqlRaw__create_temp_db ($db, $theResult['headers'], $theResult['rows'], $theResult['lengths'], &$renderer);
171            			array_push($this->databases, $db);
172        			}
173			    }
174			    //
175			    // Done for now
176			    //
177			    return true;
178
179			} elseif (!empty($data['sql'])) {
180    			//
181    			// This pass thru we have already setup the temporary database table.
182    			// So now process the supplied MySQL query on the temporary database table.
183    			//
184			    $db =& array_pop($this->databases);
185				if (!empty($db)) {
186					$db->setFetchMode(DB_FETCHMODE_ASSOC);
187					foreach ($data['sql'] as $query) {
188						$result =& $db->getAll($query);
189						if (DB::isError($result)) {
190							$error = $result->getMessage();
191							$renderer->doc .= '<div class="error">'. $error .'</div>';
192			                $db->disconnect();
193							return TRUE;
194						} elseif ($result == DB_OK or empty($result)) {
195    						//
196    						// Do nothing
197    						//
198						} else {
199    						//
200    						// Display the result as a table
201    						//
202            				$temp = array_keys($result[0]);
203    						if ($this->tableId != ' ') {
204        						$id_string = 'id="'.$this->tableId.'" ';
205    						} else {
206         						$id_string = '';
207         					}
208							if (! $this->vertical_position) {
209    							//
210    							// Display vertical table
211    							//
212								if ($this->display_inline) {
213									$renderer->doc .= '<table '.$id_string.'class="'.$this->table_class.'" style="display:inline">';
214								} else {
215									$renderer->doc .= '<table '.$id_string.'class="'.$this->table_class.'">';
216								}
217								if ($this->title != '')
218								    $renderer->doc .= '<caption class="sqlplugin__title">'.$this->title.'</caption><tbody>';
219								$renderer->doc .= '<tr>';
220								foreach ($temp as $header) {
221									$renderer->doc .= '<th class="row0">';
222									$renderer->cdata($header);
223									$renderer->doc .= '</th>';
224								}
225								$renderer->doc .= "</tr>\n";
226								$count=1;
227								foreach ($result as $row) {
228									$renderer->doc .= '<tr class="row'.$count.'">';
229									foreach ($row as $cell) {
230										$renderer->doc .= '<td>';
231										$renderer->cdata($cell);
232										$renderer->doc .= '</td>';
233									}
234									$renderer->doc .= "</tr>\n";
235									if ($this->table_class != "sortable") $count++;
236								}
237								$renderer->doc .= '</tbody></table>';
238								$this->_sqlRaw__drop_temp_db ($db, &$renderer);
239							} else {
240    							//
241    							// Display horizontal table
242    							//
243								foreach ($result as $row) {
244									$renderer->doc .= '<table '.$id_string.'class="'.$this->table_class.'">';
245								    if ($this->title != '')
246								        $renderer->doc .= '<caption class="sqlplugin__title">'.$this->title.'</caption><tbody>';
247									foreach ($row as $name => $cell) {
248										$renderer->doc .= '<tr>';
249										$renderer->doc .= "<th class='row0'>$name</th>";
250										$renderer->doc .= '<td>';
251										$renderer->cdata($cell);
252										$renderer->doc .= '</td>';
253										$renderer->doc .= "</tr>\n";
254									}
255									$renderer->doc .= '</tbody></table>';
256								    $this->_sqlRaw__drop_temp_db ($db, &$renderer);
257								}
258							}
259						}
260					}
261				}
262			}
263            return true;
264        }
265        return false;
266    }
267
268    //
269    // Function: sqlRaw__handleLink
270    // Purpose:  Determine where the data will come from and process
271    // Input:
272    //   $url           - url to csv or table
273    //   $source        - what is being processed a 'csvfile' or 'scrapeUrl'
274    //   $startMarker   - a marker of text to start looking for the next table (optional)
275    //   $tableNumber   - the number of the table to scrape on a page (eg 1,2,3...) (optional)
276    //   $dbfile        - debug file to write the csv to when scraping a table
277    //   $disallow      - Character(s) that will not be allowed for column headings
278    //   $use           - Character(s) that will replace the corresponding disallowed characters
279    //   $restrictNames - Boolean denotes if disallow characters will be replaced (1=replace)
280    // Returns:
281    //   $myResult - multidimensional array of table headings, rows of data, and size of each cell
282    //   false on error
283    //
284    function _sqlRaw__handleLink($url, &$renderer, $source, $startMarker, $tableNumber, $dbfile, $disallow, $use, $restrictNames, $caption, $fixTable){
285        global $ID;
286        global $colCount;
287        $delim = ',';
288        $opt = array('content' => '');
289        if ($source == 'csvfile') {
290            //
291            // Process a csv file
292            //
293            if(strpos($url, 'http') !==false) {
294                $http = new DokuHTTPClient();
295                $opt['content'] = $http->get($url);
296            } else {
297                //
298                // load the file from a local dokuwiki namespace
299                //
300                $opt['file'] = cleanID($url);
301                if(!strlen(getNS($opt['file'])))
302                      $opt['file'] = $INFO['namespace'].':'.$opt['file'];
303                if (auth_quickaclcheck(getNS($opt['file']).':*') < AUTH_READ) {
304                    $renderer->cdata('Access denied to CSV data');
305                    return true;
306                } else {
307                    $file = mediaFN($opt['file']);
308                    $opt['content'] = io_readFile($file);
309                    // if not valid UTF-8 is given we assume ISO-8859-1
310                    if(!utf8_check($opt['content'])) $opt['content'] = utf8_encode($opt['content']);
311                }
312            }
313            //
314            // if nothing there print error message and quit
315            //
316            if(!$opt['content']){
317                printf("Failed to fetch remote CSV data.\n");
318                return false;
319            }
320            $content =& $opt['content'];
321
322        } elseif ($source == 'scrapeUrl') {
323            //
324            // Scrape a Table
325            //
326            $content =& $this->_scrapeTable(strtolower($url), $startMarker,  $tableNumber, $dbfile, $disallow, $use, $restrictNames, $caption, $fixTable);
327            if ($content == false) {
328                msg("No data for the requested page of ".$url."\n",-1);
329                return false;
330            }
331
332        } else {
333            //
334            // Neither is an error
335            //
336            msg("No valid source url provided.\n");
337            return false;
338        }
339        //
340        // clear any trailing or leading empty lines from the data set
341        //
342        $content = preg_replace("/[\r\n]*$/","",$content);
343        $content = preg_replace("/^\s*[\r\n]*/","",$content);
344        if($content == "") {
345            printf("No csv data found.\n");
346            return false;
347        }
348        //
349        // get each row
350        //
351        $rows = array();
352        $maxcol=0;
353        $maxrow=0;
354        while($content != "") {
355          $thisrow = $this->_sqlRaw__csv_explode_row($content,$delim);
356          if($maxcol < count($thisrow))
357              $maxcol = count($thisrow);
358          array_push($rows, $thisrow);
359          $maxrow++;
360        }
361        //
362        // process headers and determine max field sizes
363        //
364        $row = 1;
365        foreach($rows as $fields) {
366    	  if ($row === 1) {
367            $colCount=0;
368    		foreach ($fields as $field) {
369        		if ($restrictNames)
370        		    $field = str_replace($disallow, $use, $field);
371    			$headers[] = strtolower(str_ireplace(' ', '_', $field));
372    			$colCount++;
373    		}
374		  } else {
375			foreach ($fields as $key=>$value) {
376				if (!isset($max_field_lengths[$key]))
377					$max_field_lengths[$key] = 0;
378				if (strlen($value) > $max_field_lengths[$key])
379					$max_field_lengths[$key] = strlen($value);
380				if ($max_field_lengths[$key] == 0)
381					$max_field_lengths[$key] = 1;
382				$field++;
383			}
384		  }
385		  $row++;
386        }
387        //
388        // Set up return data as multidimensional array of headers, data and sizes
389        //
390        $myResult['headers'] = $headers;
391        $myResult['rows'] = $rows;
392        $myResult['lengths'] = $max_field_lengths;
393        return $myResult;
394    }
395
396    //
397    // Function: scrapeTable
398    // Purpose:  Scrape a webpage to obtain only a specific table
399    // Input:
400    //   $url            - the web page as either a url or a dokuwiki page id
401    //   $startMarker    - (optional) marker of text to start looking after for the table.
402    //                     If null it will take the first table,
403    //   $tableNumber    - (optional) the table number to scrape.
404    //                     If null it will take the first table,
405    //   $dbfile         - (optional) A filepath and filename to write the table to as csv.
406    //                     If null the table is not saved to a file
407    //   $specialChars   - Character(s) that will not be allowed for column headings
408    //   $specialReplace - Character(s) that will replace the corresponding specialChar for column headiings
409    //   $restrictNames  - Boolean denotes if specialChars will be replaced (1=replace)
410    // Returns:
411    //   $csv_data - a string of the table in csv format containing only headings and cell content
412    //   false     - if a dokuwiki id was supplied and no data was obtained from that page
413    // Notes
414    //
415    function _scrapeTable($url, $startMarker, $tableNumber, $dbfile, $specialChars, $specialReplace, $restrictNames, $caption, $fixTable) {
416    require_once('test2.php');
417    global $colCount;
418
419    $csv_data = '';
420    if(preg_match('/^(http|https)?:\/\//i',$url)){
421        $raw = file_get_contents($url);
422    } else {
423        $raw = $this->_pullInWikiPage($url);
424        if ($raw == false)
425            return false;
426    }
427    $newlines = array("\t","\n","\r","\x20\x20","\0","\x0B","<br />");
428    $spaceCodes = array("&nbsp;");
429    $numberStuff = array(",","+");
430    $numbs       = array(",",".","+","-","0","1","2","3","4","5","6","7","8","9");
431
432    $content = str_replace($newlines, "", $raw);
433    $content = str_replace($spaceCodes, "_", ($content));
434    $content = preg_replace("/&#?[a-z0-9]+;/i","",$content);
435
436    $debug = FALSE;
437    if ($dbfile != '') {
438      $debug = TRUE;
439      $fp    = fopen($dbfile, 'w');
440    }
441
442    if ($startMarker != '') {
443        // Start looking at the marker
444        $start = strpos($content,$startMarker);
445        $content = substr($content,$start);
446    }
447
448    $end = 0;
449    for ($x = 1; $x <= $tableNumber; $x++) {
450    // Pull out the table
451        $start = strpos($content,'<table ', $end);
452    $end = strpos($content,'</table>',$start) + 8;
453    $table = '';
454    $table = substr($content,$start,$end-$start);
455    }
456
457    // Get column count and fix any missing </td>s
458    $table = $this->_fixTable($table);
459
460    // Handle row/col spans and captions
461    if ($fixTable == 1){
462        $mdTable = table2csv($table,'tom.csv',false, $caption);
463        foreach ($mdTable as $key => $row) {
464            //clean the data
465            array_walk($mdTable[$key], "cleanCell");
466        	if ($debug == TRUE)
467        	  fputcsv($fp, $mdTable[$key]);
468        	$csv_data .= $this->_strputcsv($row, $colCount-1);
469        }
470    } else {
471      // Simple table
472    // Pull out the rows
473    preg_match_all("|<tr(.*)</tr>|U",$table,$rows);
474
475    $row_index=0;
476    $numHeadings = 0;
477    foreach ($rows[0] as $row){
478	  $newCells = false;
479      if ($row_index!=0 || $caption==0) {
480
481        if ($restrictNames && ($row_index==0))
482            //
483            // clean the headings
484            //
485            $row = str_replace($specialChars, $specialReplace, $row);
486
487        if (strpos($row,'<th')===false)  {
488          //
489          // pull out the cells
490          //
491          preg_match_all("|<td(.*)</td>|U",$row,$cells);
492		  $newCells = true;
493        }
494        else
495          //
496          // pull out the cells and count the number of them
497          //
498          if ( $numHeadings == 0) {
499		      $numHeadings = preg_match_all("|<t(.*)</t(.*)>|U",$row,$cells);
500		      $newCells = true;
501	      }
502
503    	if (($row_index == 0) || ($row_index == 1 && $caption==1))
504    	  //
505    	  // the 1st row gives the number of columns
506    	  //
507    	  $numCols = $numHeadings;
508    	      $numCols = $colCount;
509
510    	//
511    	// store the cells by [row][cell] after you clean it
512		//
513		if ($newCells === true) {
514		$cell_index=0;
515		foreach ($cells[0] as $cell) {
516            $test = strip_tags(trim(str_replace($numbs, "", $cell)));
517    		if (strlen($test)==0)
518    		  //
519    		  // if all number remove extraneous characters ('+', ',')
520    		  //
521    		  $cell = str_replace($numberStuff, '', $cell);
522    		//
523    		// strip html and php tags
524    		//    (Test for table error of too many cells)
525    	    if ($cell_index < $numHeadings) {
526    		  $mycells[$row_index][$cell_index] = trim(strip_tags($cell));
527    		  ++$cell_index;
528    	    }
529    	  }
530	    }
531    	if ($mycells[$row_index] != '') {
532        	if ($debug == TRUE)
533        	  fputcsv($fp, $mycells[$row_index]);
534        	$csv_data .= $this->_strputcsv($mycells[$row_index], $numCols-1);
535        }
536        }
537        //
538        // repeat for each row
539        //
540    	++$row_index;
541    }
542    if ($debug == TRUE)
543      fclose($fp);
544   }
545   $csv_data = strip_tags($csv_data);
546   $csv_data = ltrim($csv_data, " ,");
547    return $csv_data;
548}
549
550    //
551    // The rest are helper functions
552    //
553
554    //
555    // Function: fixTable
556    // Purpose:  Correct errors in table if possible
557    // Input:
558    //   $tableIn - input table
559    // Returns:
560    //   $tableIn - fixed table
561    //
562    function _fixTable ($tableIn) {
563        global $colCount;
564        $tempColCount = 0;
565        $colCount = 0;
566
567        // Add missing </td>s
568        $length = strlen ($tableIn);
569        $pos = 0;
570        while($pos <= $length-1) {
571            $tdPosOpen     = stripos($tableIn, '<td', $pos);
572            $tdPosClose    = stripos($tableIn, '</td>',$tdPosOpen+4);
573            $tdPosOpenNext = stripos($tableIn, '<td', $tdPosOpen+4);
574            if ($tdPosOpenNext < $pos) {
575                $pos = $length;
576            } else {
577                if ($tdPosOpenNext !== false && $tdPosOpenNext < $tdPosClose) {
578                    $tableIn = substr_replace($tableIn, '</td>', $tdPosOpenNext-1, 0);
579                    $length += 5;
580                }
581                $pos = $tdPosClose+5;
582            }
583        }
584
585        // Get the column count
586        // we have to iterate through the rows and pull out the max found
587        $html = str_get_html(trim($tableIn));
588        foreach ($html->find('tr') as $element) {
589            $tempColCount = 0;
590            foreach ($element->find('th') as $cell) {
591                $tempColCount++;
592            }
593            if ($tempColCount > $colCount) $colCount = $tempColCount;
594        }
595        return $tableIn;
596    }
597
598
599    //
600    // Function: pullInWikiPage
601    // Purpose:  Read a dokuwiki page
602    // Input:
603    //   $dokuPageId - the dokuwiki page id
604    // Returns:
605    //   $html - the rendered html for the page
606    //
607    function _pullInWikiPage ($dokuPageId) {
608        $file = wikiFN($dokuPageId);
609        $data = io_readWikiPage($file, $dokuPageId, $rev=false);
610        $html = p_render('xhtml',p_get_instructions($data),$info);
611        return $html;
612    }
613
614    //
615    // Function: propertyRaw
616    // Purpose:  search a string for the parameter value
617    // Input:
618    //   $prop - the parameter name
619    //   $xml  - the string to search
620    // Returns:
621    //   $match - the matched parameter value
622    //   false  - on no match found
623    // Notes
624    //   It will attempt the search looking for single or double quotes
625    //      surrounding the parameter value
626    //
627    function _propertyRaw($prop, $xml) {
628	$pattern = $prop ."='([^']*)'";
629	if (ereg($pattern, $xml, $matches))
630		return $matches[1];
631	$pattern = $prop .'="([^"]*)"';
632	if (ereg($pattern, $xml, $matches))
633		return $matches[1];
634    return FALSE;
635}
636
637    //
638    // Function: strputcsv
639    // Purpose:  converts array elements into a csv string
640    // Input:
641    //   $fields      - array of elements
642    //   $numheadings - number of column headings
643    //   $delimiter   - (optional) the delimiter to use. Defaults to a comma.
644    //   $enclosure   - (optional) the enclosure character to use. Defaults to a double quote.
645    // Returns:
646    //   $csvline - the csv string
647    //
648    function _strputcsv($fields = array(), $numheadings, $delimiter = ',', $enclosure = '"') {
649        $i = 0;
650        $csvline = '';
651        $escape_char = '\\';
652        $field_cnt = count($fields)-1;
653        $enc_is_quote = in_array($enclosure, array('"',"'"));
654        reset($fields);
655
656        foreach( $fields AS $field ) {
657            /* enclose a field that contains a delimiter, an enclosure character, or a newline */
658            if( is_string($field) && (
659                strpos($field, $delimiter)!==false ||
660                strpos($field, $enclosure)!==false ||
661                strpos($field, $escape_char)!==false ||
662                strpos($field, "\n")!==false ||
663                strpos($field, "\r")!==false ||
664                strpos($field, "\t")!==false ||
665                strpos($field, ' ')!==false ) ) {
666
667                $field_len = strlen($field);
668                $escaped = 0;
669                $csvline .= $enclosure;
670                for( $ch = 0; $ch < $field_len; $ch++ )    {
671                    if( $field[$ch] == $escape_char && $field[$ch+1] == $enclosure && $enc_is_quote ) {
672                        continue;
673                    }elseif( $field[$ch] == $escape_char ) {
674                        $escaped = 1;
675                    }elseif( !$escaped && $field[$ch] == $enclosure ) {
676                        $csvline .= $enclosure;
677                    }else{
678                        $escaped = 0;
679                    }
680                    $csvline .= $field[$ch];
681                }
682                $csvline .= $enclosure;
683            } else {
684                $csvline .= $field;
685            }
686            if( $i++ != $field_cnt ) {
687                $csvline .= $delimiter;
688            }
689        }
690		if ($field_cnt < $numheadings) {
691    		for ($i=$field_cnt+1; $i<=$numheadings;  $i++) {
692        		$csvline .= $delimiter;
693    		}
694		}
695        $csvline .= "\n";
696        return $csvline;
697    }
698
699    //
700    // Function: sqlRaw__csv_explode_row
701    // Purpose:  Explode CSV string, consuming it as we go
702    // History:
703    //   Dokuwiki CSV Plugin function csv_explode_row
704    // Author:
705    //   Steven Danz <steven-danz@kc.rr.com>
706    //   Andreas Gohr <gohr@cosmocode.de>
707    //   Jerry G. Geiger <JerryGeiger@web.de>
708    // Input:
709    //   $database - database identifier
710    // Returns:
711    //   $out - an array of the csv string elements
712    // Notes:
713    // Careful, there could be both embedded newlines, commas and quotes
714    // One thing to remember is that a row must end with a newline
715    //   RFC 4180 claims that a CSV is allowed to have a cell enclosed in ""
716    //     that embeds a newline. Convert those newlines to \\ (trying to keep
717    //     to the DokuWiki syntax) which we will key off of later in render()
718    //     as an embedded newline.
719    function _sqlRaw__csv_explode_row(&$str, $delim = ',', $qual = "\"") {
720        $len = strlen($str);
721        $inside = false;
722        $word = '';
723        for ($i = 0; $i < $len; ++$i) {
724            $next = $i+1;
725            if ($str[$i]==$delim && !$inside) {
726                $out[] = $word;
727                $word = '';
728            } elseif ($str[$i] == $qual && (!$inside || $next == $len || $str[$next] == $delim || $str[$next] == "\r" || $str[$next] == "\n")) {
729                $inside = !$inside;
730            } elseif ($str[$i] == $qual && $next != $len && $str[$next] == $qual) {
731                $word .= $str[$i];
732                $i++;
733            } elseif ($str[$i] == "\n") {
734                if ($inside) {
735                    $word .= '\\\\';
736                } else {
737                    $str = substr($str, $next);
738                    $out[] = $word;
739                    return $out;
740                }
741            } else {
742                $word .= $str[$i];
743            }
744        }
745        $str = substr($str, $next);
746        $out[] = $word;
747        return $out;
748    }
749
750    //
751    // Function: sqlRaw__drop_temp_db
752    // Purpose:  Drop a temporary database table
753    // Input:
754    //   $database - database identifier
755    // Returns:
756    //   true on success
757    //   false on error
758    //
759    function _sqlRaw__drop_temp_db($database, &$renderer) {
760        $table = 'temptable';
761        $query = 'DROP TEMPORARY TABLE IF EXISTS '.$table;
762        $result =& $database->query ($query);
763        if (DB::isError ($result)) {
764			$renderer->doc .= '<div class="error">DROP TABLE failed for query: '. $query .'the error: '. $result->getMessage() .'</div>';
765			$db->disconnect();
766            return false;
767        }
768        return true;
769    }
770
771    //
772    // Function: sqlRaw__create_temp_db
773    // Purpose:  Create a temporary database table
774    // Input:
775    //   $database - database identifier
776    //   $headers  - array of column headings
777    //   $rows     - array of data records
778    //   $max_field_lengths - array of maximum field sizes
779    // Returns:
780    //   true on success
781    //   false on error
782    //
783    function _sqlRaw__create_temp_db($database, $headers, $rows, $max_field_lengths, &$renderer) {
784        global $colCount;
785        global $db;
786        $badChars = array(".", ":", "-","/");
787        $table = 'temptable';
788
789        // Create the table
790        $query = 'CREATE TEMPORARY TABLE '.$table . ' (';
791
792        foreach ($headers as $key=>$header) {
793            if ($header != "")
794            $query .= str_replace($badChars,'_',trim($header)).' VARCHAR('.$max_field_lengths[$key].'), ';
795        }
796        $query = rtrim($query,', ');
797        $query .= ') DEFAULT CHARACTER SET \'utf8\'';
798        $result =& $database->query ($query);
799        if (DB::isError ($result)) {
800			$renderer->doc .= '<div class="error">CREATE TABLE failed for query: '. $query .'the error: '. $result->getMessage() .'</div>';
801            return false;
802        }
803
804        // Insert the records
805        $row = 1;
806        foreach($rows as $fields) {
807    	  if ($row !== 1) {
808        	if ($this->caption==0 || ($this->caption==1 && $row > 2)) {
809			$sql = 'INSERT INTO `'.$table.'` VALUES(';
810            $col_index=0;
811			foreach ($fields as $field) {
812              if ($col_index < $colCount) {
813				$sql .= '\''.$database->escapeSimple($field).'\', ';
814				$col_index++ ;
815	          }
816	        }
817			$sql = rtrim($sql, ', ');
818			$sql .= ');';
819			$result =& $database->query ($sql);
820			if (DB::isError ($result)) {
821			    $renderer->doc .= '<div class="error">INSERT INTO TABLE failed for query: '. $sql .'the error: '. $result->getMessage() .'</div>';
822			    $db->disconnect();
823			    return false;
824			}
825		  }
826	  }
827		  $row++;
828		}
829		return true;
830    }
831}